bk commit into 4.1 tree (evgen:1.2468) BUG#18360

[eugene]

Below is the list of changes that have just been committed into a local
4.1 repository of evgen. When evgen 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
  1.2468 06/05/06 20:12:33 evgen@stripped +3 -0
  Fixed bug#18360: Incorrect type coercion in IN() results in false comparison
  
  The IN() function uses agg_cmp_type() to aggregate all types of its arguments
  to find out some general type for comparison purposes. In this particular case
  the char() and the int was aggregated to double because char() can contain value
  like '1.5'. But all strings which doesnt start from a digit are converted to
  0. thus 'a' and 'z' become equal. 
  This behaviour is reasonable when all function arguments are constants. But 
  when there is a field or an expression this can lead to false comparisons. In
  this case it make more sense to coerce constants to the type of field argument. 
  
  The agg_cmp_type() function now aggregates types of constant and non-constant
  items separately. If some non-constant items will be found then their
  aggregated type will be returned. Thus later constants will be coerced to the 
  aggregated type. 

  sql/item_cmpfunc.cc
    1.208 06/05/06 20:07:29 evgen@stripped +37 -2
    Fixed bug#18360: Incorrect type coercion in IN() results in false comparison.
    The agg_cmp_type() function now aggregates types of constant and non-constant
    items separately. If some non-constant items will be found then their
    aggregated type will be returned. Thus later constants will be coerced to the
    aggregated type.

  mysql-test/r/func_in.result
    1.17 06/05/06 20:07:16 evgen@stripped +21 -0
    Added test case for bug#18360: Incorrect type coercion in IN() results in false comparison.

  mysql-test/t/func_in.test
    1.17 06/05/06 20:06:56 evgen@stripped +12 -0
    Added test case for bug#18360: Incorrect type coercion in IN() results in false comparison.

# 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:	evgen
# Host:	moonbone.local
# Root:	/work/18630-bug-4.1-mysql

--- 1.207/sql/item_cmpfunc.cc	2006-04-21 10:47:53 +04:00
+++ 1.208/sql/item_cmpfunc.cc	2006-05-06 20:07:29 +04:00
@@ -58,12 +58,47 @@
   }
 }
 
+
+/*
+  Aggregates result types from the array of items.
+
+  SYNOPSIS:
+    agg_cmp_type()
+    type   [out] the aggregated type
+    items        array of items to aggreagte the type from
+    nitems       number of items in the array
+
+  DESCRIPTION
+    This function aggregates result types from the array of items. Found type
+    supposed to be used later for comparison of values of these items.
+    Aggregation itself is performed by the item_cmp_type() function.
+
+  NOTES
+    Aggregation rules:
+    If all items are constants the type will be aggregated from all items.
+    If there are some non-constant items then only types of non-constant
+      items will be aggregated and all constant items will be skiped. If a
+      non-constant item isn't the first item in the array then the aggregated
+      type of previous constant items will be lost.
+*/
 static void agg_cmp_type(Item_result *type, Item **items, uint nitems)
 {
   uint i;
   type[0]= items[0]->result_type();
-  for (i=1 ; i < nitems ; i++)
-    type[0]= item_cmp_type(type[0], items[i]->result_type());
+  /* Reset to 0 on first occurence of non-const item. 1 otherwise */
+  bool is_const= items[0]->const_item();
+
+  for (i= 1 ; i < nitems ; i++)
+  {
+    if (!items[i]->const_item())
+    {
+      type[0]= is_const ? items[i]->result_type() :
+                 item_cmp_type(type[0], items[i]->result_type());
+      is_const= 0;
+    }
+    else if (is_const)
+      type[0]= item_cmp_type(type[0], items[i]->result_type());
+  }
 }
 
 static void my_coll_agg_error(DTCollation &c1, DTCollation &c2,

--- 1.16/mysql-test/r/func_in.result	2005-09-23 13:43:16 +04:00
+++ 1.17/mysql-test/r/func_in.result	2006-05-06 20:07:16 +04:00
@@ -202,3 +202,24 @@
 count(*)
 1
 drop table t1;
+create table t1 (f1 char(1), f2 int);
+insert into t1 values (1,0),('a',1),('z',2);
+select f1 from t1 where f1 in (1,'z');
+f1
+1
+z
+select f2 from t1 where f2 in (1,'z');
+f2
+0
+1
+select f1 from t1 where 'z' in (1,f1);
+f1
+z
+select * from t1 where 'z' in (f2,f1);
+f1	f2
+z	2
+select * from t1 where 1 in (f2,f1);
+f1	f2
+1	0
+a	1
+drop table t1;

--- 1.16/mysql-test/t/func_in.test	2005-09-23 13:43:16 +04:00
+++ 1.17/mysql-test/t/func_in.test	2006-05-06 20:06:56 +04:00
@@ -109,4 +109,16 @@
 select count(*) from t1 where id not in (1,2);
 drop table t1;
 
+#
+# Bug#18360 Incorrect type coercion in IN() results in false comparison
+#
+create table t1 (f1 char(1), f2 int);
+insert into t1 values (1,0),('a',1),('z',2);
+select f1 from t1 where f1 in (1,'z');
+select f2 from t1 where f2 in (1,'z');
+select f1 from t1 where 'z' in (1,f1);
+select * from t1 where 'z' in (f2,f1);
+select * from t1 where 1 in (f2,f1);
+drop table t1;
+
 # End of 4.1 tests