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-10 17:13:26+03:00, gkodinov@stripped +5 -0
  Bug #21159: Optimizer: wrong result after AND with different data types
    Must not propagate hex string constants through result types:
    e.g. <int_col> = <bin_col> AND <bin_col> = <bin_lit> must not
be
    transformed into <int_col> = <bin_lit> AND <bin_col> =
<bin_lit>.
    This is because of asimetry of the hex string literal implemnetation:
    <hex_string>->val_int() != CAST (<hex_string>->val_str() as
VARBINARY)->val_str()

  mysql-test/r/compare.result@stripped, 2006-08-10 17:13:18+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-10 17:13:18+03:00, gkodinov@stripped +8 -0
    Bug #21159: Optimizer: wrong result after AND with different data types
     - test case

  sql/item_cmpfunc.h@stripped, 2006-08-10 17:13:19+03:00, gkodinov@stripped +4 -0
    Bug #21159: Optimizer: wrong result after AND with different data types
     call the const propagation contious transform variant

  sql/item_func.cc@stripped, 2006-08-10 17:13:20+03:00, gkodinov@stripped +63 -0
    Bug #21159: Optimizer: wrong result after AND with different data types
     define the const propagation contions transform() variant

  sql/item_func.h@stripped, 2006-08-10 17:13:20+03:00, gkodinov@stripped +2 -0
    Bug #21159: Optimizer: wrong result after AND with different data types
     declare the const propagation contions transform() variant

# 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.129/sql/item_cmpfunc.h	2006-08-10 17:13:44 +03:00
+++ 1.130/sql/item_cmpfunc.h	2006-08-10 17:13:44 +03:00
@@ -240,6 +240,10 @@ public:
   }
   Item *neg_transformer(THD *thd);
   virtual Item *negated_item();
+  Item *transform(Item_transformer transformer, byte *arg)
+  {
+    return const_contious_transform (transformer, arg);
+  }
 };
 
 class Item_func_not :public Item_bool_func

--- 1.295/sql/item_func.cc	2006-08-10 17:13:44 +03:00
+++ 1.296/sql/item_func.cc	2006-08-10 17:13:44 +03:00
@@ -273,6 +273,69 @@ Item *Item_func::transform(Item_transfor
   return (this->*transformer)(argument);
 }
 
+/*
+  same of transform() taking into consideration additional rules 
+  imposed by const propagation.
+  
+  SYNOPSIS
+    const_contious_transform()
+      transformer        the actual transformation function.
+      argument           argument to transformer
+  
+  DESCRIPTION
+    Rules are :
+      1. Never propagate hex string constants as they have asymmetric
+         val_str()/val_int() behavior : <hex_string>->val_int() !=
+         CAST (<hex_string>->val_str() as VARBINARY)->val_str()
+  
+  RETURN VALUE
+    Item *               the result of the transformation function
+*/
+Item *Item_func::const_contious_transform(Item_transformer transformer, 
+                                          byte *argument)
+{
+  if (arg_count)
+  {
+    Item **arg,**arg_end;
+    bool diverse_result_types;
+    Item_result res_type;
+
+    if (transformer == &Item::equal_fields_propagator)
+    {
+      res_type = args[0]->result_type();
+      diverse_result_types= FALSE;
+      for (arg= args, arg_end= args+arg_count; arg != arg_end; arg++)
+        if ((*arg)->result_type() !=  res_type)
+        {
+          diverse_result_types= TRUE;
+          break;
+        }
+    }
+    for (arg= args, arg_end= args+arg_count; arg != arg_end; arg++)
+    {
+      Item *new_item= (*arg)->transform(transformer, argument);
+      if (!new_item)
+	return 0;
+      if (*arg != new_item)
+      {
+        /*
+          Must not propagate hex string constants through result types:
+          e.g. <int_col> = <bin_col> AND <bin_col> = <bin_lit>
must not be
+          transformed into <int_col> = <bin_lit> AND <bin_col> =
<bin_lit>.
+          This is because of asimetry of the hex string literal impl:
+          <hex_string>->val_int() !=
+          CAST (<hex_string>->val_str() as VARBINARY)->val_str()
+         */
+        if (transformer != &Item::equal_fields_propagator ||
+            !diverse_result_types ||
+            new_item->type() != VARBIN_ITEM)
+          current_thd->change_item_tree(arg, new_item);
+      }
+    }
+  }
+  return (this->*transformer)(argument);
+}
+
 
 /* See comments in Item_cmp_func::split_sum_func() */
 

--- 1.144/sql/item_func.h	2006-08-10 17:13:44 +03:00
+++ 1.145/sql/item_func.h	2006-08-10 17:13:44 +03:00
@@ -191,6 +191,8 @@ public:
                      void * arg, traverse_order order);
   bool is_expensive_processor(byte *arg);
   virtual bool is_expensive() { return 0; }
+protected:
+  Item *const_contious_transform(Item_transformer transformer, byte *argument);
 };
 
 

--- 1.10/mysql-test/r/compare.result	2006-08-10 17:13:44 +03:00
+++ 1.11/mysql-test/r/compare.result	2006-08-10 17:13:44 +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-10 17:13:44 +03:00
+++ 1.10/mysql-test/t/compare.test	2006-08-10 17:13:44 +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