MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:kgeorge Date:August 29 2006 12:52pm
Subject:bk commit into 5.0 tree (gkodinov:1.2253) BUG#13311
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 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-08-29 15:51:38+03:00, gkodinov@stripped +7 -0
  Bug #13311: IN + binary lead to incorrect SELECT results
   It is not correct to substitute column references with constants 
   in comparisons, IN and BETWEEN if the collations used in the 
   equality comparison and comparison/IN/BETWEEN don't match.
  
   Extended the compare context of an item to contain collation.
   Extended the check for different contexts in const propagation
   to check that collations match.

  mysql-test/r/binary.result@stripped, 2006-08-29 15:51:25+03:00, gkodinov@stripped +41 -0
    Bug #13311: IN + binary lead to incorrect SELECT results
     - test case

  mysql-test/t/binary.test@stripped, 2006-08-29 15:51:26+03:00, gkodinov@stripped +21 -0
    Bug #13311: IN + binary lead to incorrect SELECT results
     - test case

  sql/item.cc@stripped, 2006-08-29 15:51:27+03:00, gkodinov@stripped +7 -3
    Bug #13311: IN + binary lead to incorrect SELECT results
     - extended the compare context of an item to cover collation.
     - added the nessesary checks 

  sql/item.h@stripped, 2006-08-29 15:51:28+03:00, gkodinov@stripped +2 -1
    Bug #13311: IN + binary lead to incorrect SELECT results
     - extended the compare context of an item to cover collation.

  sql/item_cmpfunc.cc@stripped, 2006-08-29 15:51:28+03:00, gkodinov@stripped +9 -6
    Bug #13311: IN + binary lead to incorrect SELECT results
     - extended the compare context of an item to cover collation.

  sql/item_func.cc@stripped, 2006-08-29 15:51:29+03:00, gkodinov@stripped +14 -0
    Bug #13311: IN + binary lead to incorrect SELECT results
     - extended the compare context of an item to cover collation.

  sql/item_func.h@stripped, 2006-08-29 15:51:30+03:00, gkodinov@stripped +1 -0
    Bug #13311: IN + binary lead to incorrect SELECT results
     - extended the compare context of an item to cover collation.

# 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/B13311-5.0-opt

--- 1.232/sql/item.cc	2006-08-29 15:51:58 +03:00
+++ 1.233/sql/item.cc	2006-08-29 15:51:58 +03:00
@@ -299,7 +299,8 @@ longlong Item::val_int_from_decimal()
 Item::Item():
   rsize(0), name(0), orig_name(0), name_length(0), fixed(0),
   is_autogenerated_name(TRUE),
-  collation(&my_charset_bin, DERIVATION_COERCIBLE)
+  collation(&my_charset_bin, DERIVATION_COERCIBLE),
+  cmp_collation(NULL)
 {
   marker= 0;
   maybe_null=null_value=with_sum_func=unsigned_flag=0;
@@ -345,7 +346,8 @@ Item::Item(THD *thd, Item *item):
   with_sum_func(item->with_sum_func),
   fixed(item->fixed),
   collation(item->collation),
-  cmp_context(item->cmp_context)
+  cmp_context(item->cmp_context),
+  cmp_collation(NULL)
 {
   next= thd->free_list;				// Put in free list
   thd->free_list= this;
@@ -3791,7 +3793,9 @@ Item *Item_field::equal_fields_propagato
     DATE/TIME represented as an int and as a string.
   */
   if (!item ||
-      (cmp_context != (Item_result)-1 && item->cmp_context != cmp_context))
+      (cmp_context != (Item_result)-1 && item->cmp_context != cmp_context) ||
+      (cmp_context == STRING_RESULT && 
+       cmp_collation && cmp_collation != item->cmp_collation))
     item= this;
   return item;
 }

--- 1.206/sql/item.h	2006-08-29 15:51:59 +03:00
+++ 1.207/sql/item.h	2006-08-29 15:51:59 +03:00
@@ -466,6 +466,7 @@ public:
                                            of its arguments is or contains a
                                            subselect */
   Item_result cmp_context;              /* Comparison context */
+  CHARSET_INFO *cmp_collation;          /* Comparison charset */
   // alloc & destruct is done as start of select using sql_alloc
   Item();
   /*
@@ -727,7 +728,7 @@ public:
   virtual Item *get_tmp_table_item(THD *thd) { return copy_or_same(thd); }
 
   static CHARSET_INFO *default_charset();
-  virtual CHARSET_INFO *compare_collation() { return NULL; }
+  virtual CHARSET_INFO *compare_collation() { return cmp_collation; }
 
   virtual bool walk(Item_processor processor, byte *arg)
   {

--- 1.216/sql/item_cmpfunc.cc	2006-08-29 15:51:59 +03:00
+++ 1.217/sql/item_cmpfunc.cc	2006-08-29 15:51:59 +03:00
@@ -404,9 +404,10 @@ void Item_bool_func2::fix_length_and_dec
       args[1]->result_type() == STRING_RESULT &&
       agg_arg_charsets(coll, args, 2, MY_COLL_CMP_CONV, 1))
     return;
-    
-  args[0]->cmp_context= args[1]->cmp_context=
-    item_cmp_type(args[0]->result_type(), args[1]->result_type());
+   
+  set_args_cmp_context (
+    item_cmp_type(args[0]->result_type(), args[1]->result_type()),
+    coll.collation);
   // Make a special case of compare with fields to get nicer DATE comparisons
 
   if (functype() == LIKE_FUNC)  // Disable conversion in case of LIKE function.
@@ -427,7 +428,7 @@ void Item_bool_func2::fix_length_and_dec
         {
           cmp.set_cmp_func(this, tmp_arg, tmp_arg+1,
                            INT_RESULT);		// Works for all types.
-          args[0]->cmp_context= args[1]->cmp_context= INT_RESULT;
+          set_args_cmp_context(INT_RESULT, NULL);
           return;
         }
       }
@@ -442,7 +443,7 @@ void Item_bool_func2::fix_length_and_dec
         {
           cmp.set_cmp_func(this, tmp_arg, tmp_arg+1,
                            INT_RESULT); // Works for all types.
-          args[0]->cmp_context= args[1]->cmp_context= INT_RESULT;
+          set_args_cmp_context(INT_RESULT, NULL);
           return;
         }
       }
@@ -1220,10 +1221,11 @@ void Item_func_between::fix_length_and_d
   if (!args[0] || !args[1] || !args[2])
     return;
   agg_cmp_type(thd, &cmp_type, args, 3);
-  args[0]->cmp_context= args[1]->cmp_context= args[2]->cmp_context= cmp_type;
 
   if (cmp_type == STRING_RESULT)
       agg_arg_charsets(cmp_collation, args, 3, MY_COLL_CMP_CONV, 1);
+
+  set_args_cmp_context(cmp_type, cmp_collation.collation);
 }
 
 
@@ -2501,6 +2503,7 @@ void Item_func_in::fix_length_and_dec()
       agg_arg_charsets(cmp_collation, args, arg_count, MY_COLL_CMP_CONV, 1))
     return;
 
+  set_args_cmp_context(cmp_type, cmp_collation.collation);
   for (arg=args+1, arg_end=args+arg_count; arg != arg_end ; arg++)
   {
     if (!arg[0]->const_item())

--- 1.299/sql/item_func.cc	2006-08-29 15:51:59 +03:00
+++ 1.300/sql/item_func.cc	2006-08-29 15:51:59 +03:00
@@ -553,6 +553,20 @@ Item *Item_func::get_tmp_table_item(THD 
   return copy_or_same(thd);
 }
 
+
+void Item_func::set_args_cmp_context(Item_result cmp_type, 
+                                     CHARSET_INFO *cmp_collation)
+{
+  Item **arg, **arg_end;
+  for (arg=args, arg_end=args+arg_count; arg != arg_end ; arg++)
+  {
+    arg[0]->cmp_context= cmp_type;
+    if (cmp_type == STRING_RESULT)
+      arg[0]->cmp_collation= cmp_collation;
+  }
+}
+
+
 String *Item_int_func::val_str(String *str)
 {
   DBUG_ASSERT(fixed == 1);

--- 1.148/sql/item_func.h	2006-08-29 15:51:59 +03:00
+++ 1.149/sql/item_func.h	2006-08-29 15:51:59 +03:00
@@ -191,6 +191,7 @@ public:
                      void * arg, traverse_order order);
   bool is_expensive_processor(byte *arg);
   virtual bool is_expensive() { return 0; }
+  void set_args_cmp_context(Item_result res_type, CHARSET_INFO *collation);
 };
 
 

--- 1.22/mysql-test/r/binary.result	2006-08-29 15:51:59 +03:00
+++ 1.23/mysql-test/r/binary.result	2006-08-29 15:51:59 +03:00
@@ -160,3 +160,44 @@ hex(col1)
 62000000000000000000
 62200000000000000000
 drop table t1;
+create table t1( a char(20), b char(20));
+insert into t1 values ("john","doe"),("John","Doe");
+select * from t1 where a='John' and a in ( binary 'john');
+a	b
+john	doe
+select * from t1 where a='john' and a in ( binary 'John');
+a	b
+John	Doe
+select * from t1 where a='John' and a in ( binary 'john', binary 'john');
+a	b
+john	doe
+select * from t1 where a='john' and a in ( binary 'John', binary 'John');
+a	b
+John	Doe
+select * from t1 where a='John' and a in ( binary 'john', 'doe');
+a	b
+john	doe
+select * from t1 where a='john' and a in ( binary 'John', 'Doe');
+a	b
+John	Doe
+select * from t1 where a='John' and binary 'john' in (a);
+a	b
+john	doe
+select * from t1 where a='john' and binary 'John' in (a);
+a	b
+John	Doe
+select * from t1 where a='John' and binary 'john' in (a,b);
+a	b
+john	doe
+select * from t1 where a='john' and binary 'John' in (a,b);
+a	b
+John	Doe
+select * from t1 where a='John' and a in ('john', 'john');
+a	b
+john	doe
+John	Doe
+select * from t1 where a='John' and a in ('john', 'doe');
+a	b
+john	doe
+John	Doe
+drop table t1;

--- 1.17/mysql-test/t/binary.test	2006-08-29 15:51:59 +03:00
+++ 1.18/mysql-test/t/binary.test	2006-08-29 15:51:59 +03:00
@@ -101,3 +101,24 @@ select hex(col1) from t1;
 insert into t1 values ('b'),('b ');
 select hex(col1) from t1;
 drop table t1;
+
+#
+# Bug #13311: IN + binary lead to incorrect SELECT results
+#
+create table t1( a char(20), b char(20));
+insert into t1 values ("john","doe"),("John","Doe");
+
+select * from t1 where a='John' and a in ( binary 'john');
+select * from t1 where a='john' and a in ( binary 'John');
+select * from t1 where a='John' and a in ( binary 'john', binary 'john');
+select * from t1 where a='john' and a in ( binary 'John', binary 'John');
+select * from t1 where a='John' and a in ( binary 'john', 'doe');
+select * from t1 where a='john' and a in ( binary 'John', 'Doe');
+select * from t1 where a='John' and binary 'john' in (a);
+select * from t1 where a='john' and binary 'John' in (a);
+select * from t1 where a='John' and binary 'john' in (a,b);
+select * from t1 where a='john' and binary 'John' in (a,b);
+
+select * from t1 where a='John' and a in ('john', 'john');
+select * from t1 where a='John' and a in ('john', 'doe');
+drop table t1;
Thread
bk commit into 5.0 tree (gkodinov:1.2253) BUG#13311kgeorge29 Aug