Bug #21159: Optimizer: wrong result after AND with different data types

Disable const propagation for Item_hex_string.
This must be done because Item_hex_string->val_int() is not
the same as (Item_hex_string->val_str() in BINARY column)->val_int().
We cannot simply disable the replacement in a particular context (
e.g. <bin_col> = <int_col> AND <bin_col> = <hex_string>) since
Items don't know the context they are in and there are functions like 
IF (<hex_string>, 'yes', 'no').
Note that this will disable some valid cases as well 
(e.g. : <bin_col> = <hex_string> AND <bin_col2> = <bin_col>) but 
there's no way to distinguish the valid cases without having the
Item's parent say something like : Item->set_context(Item::STRING_RESULT)
and have all the Items that contain other Items do that consistently.


mysql-test/r/compare.result:
  Bug #21159: Optimizer: wrong result after AND with different data types
   - test case
mysql-test/t/compare.test:
  Bug #21159: Optimizer: wrong result after AND with different data types
   - test case
sql/sql_select.cc:
  Bug #21159: Optimizer: wrong result after AND with different data types
   - disable const propagation for Item_hex_string.

3 files changed, 32 insertions, 1 deletions

diff --git a/mysql-test/r/compare.result b/mysql-test/r/compare.result
index 6f667aabac0..da0ca8ddba1 100644
--- a/mysql-test/r/compare.result
+++ b/mysql-test/r/compare.result
@@ -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;
diff --git a/mysql-test/t/compare.test b/mysql-test/t/compare.test
index a42ba5ac88a..337035a8095 100644
--- a/mysql-test/t/compare.test
+++ b/mysql-test/t/compare.test
@@ -37,3 +37,12 @@ SELECT CHAR(31) = '', '' = CHAR(31);
 SELECT CHAR(30) = '', '' = CHAR(30);
 
 # End of 4.1 tests
+
+#
+#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;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 2f16b350d04..eb4637b471a 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -6498,8 +6498,23 @@ static bool check_equality(Item *item, COND_EQUAL *cond_equal)
         field_item= (Item_field*) right_item;
         const_item= left_item;
       }
+      /* 
+        Disable const propagation for Item_hex_string.
+        This must be done because Item_hex_string->val_int() is not
+        the same as (Item_hex_string->val_str() in BINARY column)->val_int().
+        We cannot simply disable the replacement in a particular context (
+        e.g. <bin_col> = <int_col> AND <bin_col> = <hex_string>) since
+        Items don't know the context they are in and there are functions like 
+        IF (<hex_string>, 'yes', 'no').
+        Note that this will disable some valid cases as well 
+        (e.g. : <bin_col> = <hex_string> AND <bin_col2> = <bin_col>) but 
+        there's no way to distinguish the valid cases without having the
+        Item's parent say something like : Item->set_context(Item::STRING_RESULT)
+        and have all the Items that contain other Items do that consistently.
+      */
       if (const_item &&
-          field_item->result_type() == const_item->result_type())
+          field_item->result_type() == const_item->result_type() &&
+          const_item->type() != Item::VARBIN_ITEM)
       {
         bool copyfl;