bk commit into 5.0 tree (gkodinov:1.2235) BUG#21159

[kgeorge]

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-04 16:23:41+03:00, gkodinov@stripped +5 -0
  Bug #21159: Optimizer: wrong result after AND with different data types
  Disable const propagation for conditions that compare 
  strings with numbers because they may cause wrong 
  comparison. example : f1_num=const_str AND f2_str=f1_num
  where e.g. f1_num=5, const_str="5a", f2_str="5b".

  mysql-test/r/compare.result@stripped, 2006-08-04 16:23:33+03:00, gkodinov@stripped +7 -0
    Bug #21159: Optimizer: wrong result after AND with different data types
     - test case

  mysql-test/t/compare.test@stripped, 2006-08-04 16:23:34+03:00, gkodinov@stripped +8 -0
    Bug #21159: Optimizer: wrong result after AND with different data types
     - test case

  sql/item.cc@stripped, 2006-08-04 16:23:34+03:00, gkodinov@stripped +9 -0
    Bug #21159: Optimizer: wrong result after AND with different data types
     - disable const substitution for string/num comparisons

  sql/item.h@stripped, 2006-08-04 16:23:35+03:00, gkodinov@stripped +2 -0
    Bug #21159: Optimizer: wrong result after AND with different data types
     - disable const substitution for string/num comparisons

  sql/item_cmpfunc.cc@stripped, 2006-08-04 16:23:36+03:00, gkodinov@stripped +19 -0
    Bug #21159: Optimizer: wrong result after AND with different data types
     - disable const substitution for string/num comparisons

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

--- 1.229/sql/item.cc	2006-08-04 16:23:58 +03:00
+++ 1.230/sql/item.cc	2006-08-04 16:23:58 +03:00
@@ -3794,6 +3794,15 @@ Item *Item_field::set_no_const_sub(byte 
   return this;
 }
 
+/*
+  Mark the item to not be part of substitution
+  See comments in Arg_comparator::set_compare_func() for details
+*/
+Item *Item_field::always_set_no_const_sub(byte *arg)
+{
+  no_const_subst=1;
+  return this;
+}
 
 /*
   Replace an Item_field for an equal Item_field that evaluated earlier

--- 1.205/sql/item.h	2006-08-04 16:23:58 +03:00
+++ 1.206/sql/item.h	2006-08-04 16:23:58 +03:00
@@ -756,6 +756,7 @@ public:
 
   virtual Item *equal_fields_propagator(byte * arg) { return this; }
   virtual Item *set_no_const_sub(byte *arg) { return this; }
+  virtual Item *always_set_no_const_sub(byte *arg) { return this; }
   virtual Item *replace_equal_field(byte * arg) { return this; }
 
   /*
@@ -1256,6 +1257,7 @@ public:
   Item_equal *find_item_equal(COND_EQUAL *cond_equal);
   Item *equal_fields_propagator(byte *arg);
   Item *set_no_const_sub(byte *arg);
+  Item *always_set_no_const_sub(byte *arg);
   Item *replace_equal_field(byte *arg);
   inline uint32 max_disp_length() { return field->max_length(); }
   Item_field *filed_for_view_update() { return this; }

--- 1.213/sql/item_cmpfunc.cc	2006-08-04 16:23:58 +03:00
+++ 1.214/sql/item_cmpfunc.cc	2006-08-04 16:23:58 +03:00
@@ -524,8 +524,27 @@ int Arg_comparator::set_compare_func(Ite
     break;
   }
   case DECIMAL_RESULT:
+    break;
   case REAL_RESULT:
+  {
+    /* 
+      Disable const propagation for conditions that compare 
+      strings with numbers because they may cause wrong 
+      comparison. example : f1_num=const_str AND f2_str=f1_num
+      where e.g. f1_num=5, const_str="5a", f2_str="5b".
+      Note that this depends on item_cmp_type() returning
+      REAL_RESULT for non-equal types.
+    */
+    Item_result a_t= (*a)->result_type(), b_t= (*b)->result_type();
+    if (a_t != b_t && 
+        ((a_t == STRING_RESULT && (b_t != ROW_RESULT)) || 
+         (b_t == STRING_RESULT && (a_t != ROW_RESULT))))
+    {
+      (*a)->transform(&Item::always_set_no_const_sub, (byte*) 0);
+      (*b)->transform(&Item::always_set_no_const_sub, (byte*) 0);
+    }
     break;
+  }
   default:
     DBUG_ASSERT(0);
   }

--- 1.10/mysql-test/r/compare.result	2006-08-04 16:23:58 +03:00
+++ 1.11/mysql-test/r/compare.result	2006-08-04 16:23:58 +03:00
@@ -42,3 +42,10 @@ CHAR(31) = ''	'' = CHAR(31)
 SELECT CHAR(30) = '', '' = CHAR(30);
 CHAR(30) = ''	'' = CHAR(30)
 0	0
+create table t1 (a tinyint(1),b binary(1));
+insert into t1 values (0x01,0x01);
+select * from t1 where a=b;
+a	b
+select * from t1 where a=b and b=0x01;
+a	b
+drop table if exists t1;

--- 1.9/mysql-test/t/compare.test	2006-08-04 16:23:58 +03:00
+++ 1.10/mysql-test/t/compare.test	2006-08-04 16:23:58 +03:00
@@ -36,4 +36,12 @@ SELECT CHAR(31) = '', '' = CHAR(31);
 # Extra test
 SELECT CHAR(30) = '', '' = CHAR(30);
 
+#
+#Bug #21159: Optimizer: wrong result after AND with different data types
+#
+create table t1 (a tinyint(1),b binary(1));
+insert into t1 values (0x01,0x01);
+select * from t1 where a=b;
+select * from t1 where a=b and b=0x01;
+drop table if exists t1;
 # End of 4.1 tests