From: Date: December 13 2006 1:34pm
Subject: bk commit into 4.1 tree (gkodinov:1.2583) BUG#15881
List-Archive: http://lists.mysql.com/commits/16879
X-Bug: 15881
Message-Id: <20061213123431.C61B2A6997F@macbook.gmz>

Below is the list of changes that have just been committed into a local
4.1 repository of kgeorge. When kgeorge does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2006-12-13 14:34:19+02:00, gkodinov@stripped +3 -0
  Bug #15881: cast problems
   The optimizer removes expressions from GROUP BY/DISTINCT
   if they happen to participate in a <expression> = <const>
   predicates of the WHERE clause (the idea being that if
   it's always equal to a constant it can't have multiple 
   values).
   However for predicates where the expression and the 
   constant item are of different result type this is not
   valid (e.g. a string column compared to 0).
   Fixed by disabling the usage of predicates that compare 
   expressions and constants of different result type when 
   simplifying GROUP BY/DISTINCT.

  mysql-test/r/distinct.result@stripped, 2006-12-13 14:34:12+02:00, gkodinov@stripped +11 -0
    Bug #15881: cast problems
     - test case

  mysql-test/t/distinct.test@stripped, 2006-12-13 14:34:13+02:00, gkodinov@stripped +8 -0
    Bug #15881: cast problems
     - test case

  sql/sql_select.cc@stripped, 2006-12-13 14:34:14+02:00, gkodinov@stripped +32 -2
    Bug #15881: cast problems
     - can't use <expr>=<const> to remove GROUP BY/DISTINCT cols
       if they're not of the same type.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	gkodinov
# Host:	macbook.gmz
# Root:	/Users/kgeorge/mysql/work/B15881-4.1-opt

--- 1.465/sql/sql_select.cc	2006-11-03 00:20:32 +02:00
+++ 1.466/sql/sql_select.cc	2006-12-13 14:34:14 +02:00
@@ -4896,6 +4896,36 @@ remove_eq_conds(THD *thd, COND *cond, It
   return cond;					// Point at next and level
 }
 
+/* 
+  Check if comparison args can be used for removal of GROUP BY/DISTINCT
+  
+  SYNOPSIS
+    is_permutable_const_expression_args()
+      l          the left comparison argument
+      l          the right comparison argument
+  
+  DESCRIPTION    
+    Checks if the args of equal comparioson can be used to take 
+    away DISTINCT/GROUP BY : generally <expr> == <const>.
+    Arguments must be of the same type because e.g. 
+    <string_field> = <int_const> may match more than 1 row. 
+    We must take into coinsideration and the optimization done for various 
+    string constants when compared to dates etc (see Item_int_with_ref)
+  
+  RETURN VALUE  
+    TRUE    can be used
+    FALSE   cannot be used
+*/
+static bool
+is_permutable_const_expression_args(Item *l, Item *r)
+{
+  return r->const_item() &&
+    (r->result_type() == l->result_type() ||
+     (l->type() == Item::FIELD_ITEM && 
+      ((Item_field *)l)->field->can_be_compared_as_longlong() &&
+      r->result_type() == INT_RESULT));
+}
+
 /*
   Return 1 if the item is a const value in all the WHERE clause
 */
@@ -4932,7 +4962,7 @@ const_expression_in_where(COND *cond, It
     Item *right_item= ((Item_func*) cond)->arguments()[1];
     if (left_item->eq(comp_item,1))
     {
-      if (right_item->const_item())
+      if (is_permutable_const_expression_args (left_item, right_item))
       {
 	if (*const_item)
 	  return right_item->eq(*const_item, 1);
@@ -4942,7 +4972,7 @@ const_expression_in_where(COND *cond, It
     }
     else if (right_item->eq(comp_item,1))
     {
-      if (left_item->const_item())
+      if (is_permutable_const_expression_args (right_item, left_item))
       {
 	if (*const_item)
 	  return left_item->eq(*const_item, 1);

--- 1.35/mysql-test/r/distinct.result	2006-11-29 00:21:37 +02:00
+++ 1.36/mysql-test/r/distinct.result	2006-12-13 14:34:12 +02:00
@@ -625,3 +625,14 @@ fruit_id	fruit_name
 2	APPLE
 DROP TABLE t1;
 DROP TABLE t2;
+CREATE TABLE t1 (a CHAR(1));
+INSERT INTO t1 VALUES('A'), (0);
+SELECT a FROM t1 WHERE a=0;
+a
+A
+0
+SELECT DISTINCT a FROM t1 WHERE a=0;
+a
+A
+0
+DROP TABLE t1;

--- 1.20/mysql-test/t/distinct.test	2006-11-29 00:21:37 +02:00
+++ 1.21/mysql-test/t/distinct.test	2006-12-13 14:34:13 +02:00
@@ -454,4 +454,12 @@ SELECT * FROM t2;
 DROP TABLE t1;
 DROP TABLE t2;
 
+#
+# Bug #15881: cast problems
+#
+CREATE TABLE t1 (a CHAR(1)); INSERT INTO t1 VALUES('A'), (0);
+SELECT a FROM t1 WHERE a=0;
+SELECT DISTINCT a FROM t1 WHERE a=0;
+DROP TABLE t1;
+
 # End of 4.1 tests