From: Date: August 4 2006 3:15pm
Subject: bk commit into 5.0 tree (gkodinov:1.2235) BUG#21180
List-Archive: http://lists.mysql.com/commits/10050
X-Bug: 21180
Message-Id: <20060804131558.CA61B305BBF@macbook.gmz>

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:15:42+03:00, gkodinov@stripped +5 -0
  Bug #21180: Subselect with index for both WHERE and ORDER BY produces empty 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".

  mysql-test/r/compare.result@stripped, 2006-08-04 16:15:34+03:00, gkodinov@stripped +7 -0
    Bug #21180: Subselect with index for both WHERE and ORDER BY produces empty result
     - test case

  mysql-test/t/compare.test@stripped, 2006-08-04 16:15:34+03:00, gkodinov@stripped +8 -0
    Bug #21180: Subselect with index for both WHERE and ORDER BY produces empty result
     - test case

  sql/item.cc@stripped, 2006-08-04 16:15:35+03:00, gkodinov@stripped +9 -0
    Bug #21180: Subselect with index for both WHERE and ORDER BY produces empty result
     - disable const substitution for string/num comparisons

  sql/item.h@stripped, 2006-08-04 16:15:36+03:00, gkodinov@stripped +2 -0
    Bug #21180: Subselect with index for both WHERE and ORDER BY produces empty result
     - disable const substitution for string/num comparisons

  sql/item_cmpfunc.cc@stripped, 2006-08-04 16:15:36+03:00, gkodinov@stripped +19 -0
    Bug #21180: Subselect with index for both WHERE and ORDER BY produces empty result
     - 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:15:57 +03:00
+++ 1.230/sql/item.cc	2006-08-04 16:15:57 +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:15:57 +03:00
+++ 1.206/sql/item.h	2006-08-04 16:15:57 +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:15:58 +03:00
+++ 1.214/sql/item_cmpfunc.cc	2006-08-04 16:15: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:15:58 +03:00
+++ 1.11/mysql-test/r/compare.result	2006-08-04 16:15: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:15:58 +03:00
+++ 1.10/mysql-test/t/compare.test	2006-08-04 16:15: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