bk commit into 4.1 tree (gkodinov:1.2583) BUG#15881

[kgeorge]

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-22 10:44:24+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 additional check of the result types of the 
   expression and the constant and if they differ the 
   expression don't get removed from the group by list.

  mysql-test/r/distinct.result@stripped, 2006-12-22 10:44:18+02:00, gkodinov@stripped +23
-0
    Bug #15881: cast problems
     - test case

  mysql-test/t/distinct.test@stripped, 2006-12-22 10:44:19+02:00, gkodinov@stripped +14 -0
    Bug #15881: cast problems
     - test case

  sql/sql_select.cc@stripped, 2006-12-22 10:44:19+02:00, gkodinov@stripped +34 -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-22 10:44:19 +02:00
@@ -4896,6 +4896,38 @@ 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_const_with_same_type()
+      l          the left comparison argument
+      r          the right comparison argument
+  
+  DESCRIPTION    
+    Checks if the args of equal comparison 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 consideration 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_const_with_same_type(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()) ||
+       (l->type() == Item::FUNC_ITEM &&
+        ((Item_func *)l)->result_as_longlong())) &&
+      r->result_type() == INT_RESULT));
+}
+
 /*
   Return 1 if the item is a const value in all the WHERE clause
 */
@@ -4932,7 +4964,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_const_with_same_type (left_item, right_item))
       {
 	if (*const_item)
 	  return right_item->eq(*const_item, 1);
@@ -4942,7 +4974,7 @@ const_expression_in_where(COND *cond, It
     }
     else if (right_item->eq(comp_item,1))
     {
-      if (left_item->const_item())
+      if (is_const_with_same_type (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-22 10:44:18 +02:00
@@ -625,3 +625,26 @@ 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;
+CREATE TABLE t1 (a DATE);
+INSERT INTO t1 VALUES ('1972-07-29'), ('1972-02-06');
+EXPLAIN SELECT (SELECT DISTINCT a FROM t1 WHERE a = '2002-08-03');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+EXPLAIN SELECT (SELECT DISTINCT ADDDATE(a,1) FROM t1 
+WHERE ADDDATE(a,1) = '2002-08-03');
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+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-22 10:44:19 +02:00
@@ -454,4 +454,18 @@ 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;
+CREATE TABLE t1 (a DATE);
+INSERT INTO t1 VALUES ('1972-07-29'), ('1972-02-06');
+EXPLAIN SELECT (SELECT DISTINCT a FROM t1 WHERE a = '2002-08-03');
+EXPLAIN SELECT (SELECT DISTINCT ADDDATE(a,1) FROM t1 
+                WHERE ADDDATE(a,1) = '2002-08-03');
+DROP TABLE t1;
+
 # End of 4.1 tests