bk commit into 5.0 tree (gshchepa:1.2564) BUG#28076

[<gshchepa]

Below is the list of changes that have just been committed into a local
5.0 repository of uchum. When uchum 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, 2007-11-10 23:44:48+04:00, gshchepa@stripped +9 -0
  Fixed bug #28076: inconsistent binary/varbinary comparison.
  
  After adding an index the <VARBINARY> IN (SELECT <BINARY> ...)
  clause returned a wrong result: the VARBINARY value was illegally padded
  with zero bytes to the length of the BINARY column for the index search.
  (<VARBINARY>, ...) IN (SELECT <BINARY>, ... ) clauses are affected too.

  mysql-test/r/subselect.result@stripped, 2007-11-10 23:44:29+04:00, gshchepa@stripped +49 -0
    Added test case for bug #28076.

  mysql-test/t/subselect.test@stripped, 2007-11-10 23:44:16+04:00, gshchepa@stripped +42 -0
    Added test case for bug #28076.

  sql/item.cc@stripped, 2007-11-10 23:44:09+04:00, gshchepa@stripped +12 -4
    Fixed bug #28076.
    The Item_cache_str::save_in_field method has been overloaded
    to check cached values for an illegal padding before the saving
    into a field.

  sql/item.h@stripped, 2007-11-10 23:44:11+04:00, gshchepa@stripped +11 -3
    Fixed bug #28076.
    The Item_cache_str::is_varbinary flag has been added and the
    Item_cache_str::save_in_field method has been overloaded to prevent
    cached values from an illegal padding when saving in fields.
    The signature of the Item_cache::get_cache method has been
    changed to accept pointers to Item instead of Item_result
    values.

  sql/item_cmpfunc.cc@stripped, 2007-11-10 23:44:12+04:00, gshchepa@stripped +1 -1
    Fixed bug #28076.
    The Item_in_optimizer::fix_left method has been modified to
    to call Item_cache::get_cache in a new manner.

  sql/item_subselect.cc@stripped, 2007-11-10 23:44:13+04:00, gshchepa@stripped +12 -1
    Fixed bug #28076.
    The subselect_indexsubquery_engine::exec method has been
    modified to take into account field conversion errors
    (copy&paste from subselect_uniquesubquery_engine::exec).

  sql/sp_rcontext.cc@stripped, 2007-11-10 23:44:14+04:00, gshchepa@stripped +3 -3
    Fixed bug #28076.
    The sp_rcontext::create_case_expr_holder method has been
    modified to call Item_cache::get_cache in a new manner.

  sql/sp_rcontext.h@stripped, 2007-11-10 23:44:15+04:00, gshchepa@stripped +1 -1
    Fixed bug #28076.
    The sp_rcontext::create_case_expr_holder method signature
    has been modified to pass Item pointers to the
    Item_cache::get_cache method.

  sql/sql_class.cc@stripped, 2007-11-10 23:44:15+04:00, gshchepa@stripped +1 -1
    Fixed bug #28076.
    The select_max_min_finder_subselect::send_data method has been
    modified to call Item_cache::get_cache in a new manner.

diff -Nrup a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
--- a/mysql-test/r/subselect.result	2007-10-30 16:27:19 +04:00
+++ b/mysql-test/r/subselect.result	2007-11-10 23:44:29 +04:00
@@ -4150,4 +4150,53 @@ SELECT ((a1,a2) IN (SELECT * FROM t2 WHE
 0
 0
 DROP TABLE t1, t2;
+CREATE TABLE t1 (s1 BINARY(5), s2 VARBINARY(5));
+INSERT INTO t1 VALUES (0x41,0x41), (0x42,0x42), (0x43,0x43);
+SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1);
+s1	s2
+SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1);
+s1	s2
+CREATE INDEX I1 ON t1 (s1);
+CREATE INDEX I2 ON t1 (s2);
+SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1);
+s1	s2
+SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1);
+s1	s2
+TRUNCATE t1;
+INSERT INTO t1 VALUES (0x41,0x41);
+SELECT * FROM t1 WHERE s1 = (SELECT s2 FROM t1);
+s1	s2
+DROP TABLE t1;
+CREATE TABLE t1 (a1 VARBINARY(2) NOT NULL DEFAULT '0', PRIMARY KEY (a1));
+CREATE TABLE t2 (a2 BINARY(2) default '0', INDEX (a2));
+CREATE TABLE t3 (a3 BINARY(2) default '0');
+INSERT INTO t1 VALUES (1),(2),(3),(4);
+INSERT INTO t2 VALUES (1),(2),(3);
+INSERT INTO t3 VALUES (1),(2),(3);
+SELECT LEFT(t2.a2, 1) FROM t2,t3 WHERE t3.a3=t2.a2;
+LEFT(t2.a2, 1)
+1
+2
+3
+SELECT t1.a1, t1.a1 in (SELECT t2.a2 FROM t2,t3 WHERE t3.a3=t2.a2) FROM t1;
+a1	t1.a1 in (SELECT t2.a2 FROM t2,t3 WHERE t3.a3=t2.a2)
+1	0
+2	0
+3	0
+4	0
+DROP TABLE t1,t2,t3;
+CREATE TABLE t1 (a1 BINARY(3) PRIMARY KEY, b1 VARBINARY(3));
+CREATE TABLE t2 (a2 VARBINARY(3) PRIMARY KEY);
+CREATE TABLE t3 (a3 VARBINARY(3) PRIMARY KEY);
+INSERT INTO t1 VALUES (1,10), (2,20), (3,30), (4,40);
+INSERT INTO t2 VALUES (2), (3), (4), (5);
+INSERT INTO t3 VALUES (10), (20), (30);
+SELECT LEFT(t1.a1,1) FROM t1,t3 WHERE t1.b1=t3.a3;
+LEFT(t1.a1,1)
+1
+2
+3
+SELECT a2 FROM t2 WHERE t2.a2 IN (SELECT t1.a1 FROM t1,t3 WHERE t1.b1=t3.a3);
+a2
+DROP TABLE t1, t2, t3;
 End of 5.0 tests.
diff -Nrup a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
--- a/mysql-test/t/subselect.test	2007-10-30 16:27:19 +04:00
+++ b/mysql-test/t/subselect.test	2007-11-10 23:44:16 +04:00
@@ -3002,4 +3002,46 @@ INSERT INTO t2 VALUES (103, 203);
 SELECT ((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL FROM t1;
 DROP TABLE t1, t2;
 
+#
+# Bug #28076: inconsistent binary/varbinary comparison
+#
+
+CREATE TABLE t1 (s1 BINARY(5), s2 VARBINARY(5));
+INSERT INTO t1 VALUES (0x41,0x41), (0x42,0x42), (0x43,0x43);
+
+SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1);
+SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1);
+
+CREATE INDEX I1 ON t1 (s1);
+CREATE INDEX I2 ON t1 (s2);
+
+SELECT s1, s2 FROM t1 WHERE s2 IN (SELECT s1 FROM t1);
+SELECT s1, s2 FROM t1 WHERE (s2, 10) IN (SELECT s1, 10 FROM t1);
+
+TRUNCATE t1;
+INSERT INTO t1 VALUES (0x41,0x41);
+SELECT * FROM t1 WHERE s1 = (SELECT s2 FROM t1);
+
+DROP TABLE t1;
+
+CREATE TABLE t1 (a1 VARBINARY(2) NOT NULL DEFAULT '0', PRIMARY KEY (a1));
+CREATE TABLE t2 (a2 BINARY(2) default '0', INDEX (a2));
+CREATE TABLE t3 (a3 BINARY(2) default '0');
+INSERT INTO t1 VALUES (1),(2),(3),(4);
+INSERT INTO t2 VALUES (1),(2),(3);
+INSERT INTO t3 VALUES (1),(2),(3);
+SELECT LEFT(t2.a2, 1) FROM t2,t3 WHERE t3.a3=t2.a2;
+SELECT t1.a1, t1.a1 in (SELECT t2.a2 FROM t2,t3 WHERE t3.a3=t2.a2) FROM t1;
+DROP TABLE t1,t2,t3;
+
+CREATE TABLE t1 (a1 BINARY(3) PRIMARY KEY, b1 VARBINARY(3));
+CREATE TABLE t2 (a2 VARBINARY(3) PRIMARY KEY);
+CREATE TABLE t3 (a3 VARBINARY(3) PRIMARY KEY);
+INSERT INTO t1 VALUES (1,10), (2,20), (3,30), (4,40);
+INSERT INTO t2 VALUES (2), (3), (4), (5);
+INSERT INTO t3 VALUES (10), (20), (30);
+SELECT LEFT(t1.a1,1) FROM t1,t3 WHERE t1.b1=t3.a3;
+SELECT a2 FROM t2 WHERE t2.a2 IN (SELECT t1.a1 FROM t1,t3 WHERE t1.b1=t3.a3);
+DROP TABLE t1, t2, t3;
+
 --echo End of 5.0 tests.
diff -Nrup a/sql/item.cc b/sql/item.cc
--- a/sql/item.cc	2007-10-23 20:51:36 +05:00
+++ b/sql/item.cc	2007-11-10 23:44:09 +04:00
@@ -6259,9 +6259,9 @@ bool field_is_equal_to_item(Field *field
   return result == field->val_real();
 }
 
-Item_cache* Item_cache::get_cache(Item_result type)
+Item_cache* Item_cache::get_cache(const Item *item)
 {
-  switch (type) {
+  switch (item->result_type()) {
   case INT_RESULT:
     return new Item_cache_int();
   case REAL_RESULT:
@@ -6269,7 +6269,7 @@ Item_cache* Item_cache::get_cache(Item_r
   case DECIMAL_RESULT:
     return new Item_cache_decimal();
   case STRING_RESULT:
-    return new Item_cache_str();
+    return new Item_cache_str(item);
   case ROW_RESULT:
     return new Item_cache_row();
   default:
@@ -6447,6 +6447,14 @@ my_decimal *Item_cache_str::val_decimal(
 }
 
 
+int Item_cache_str::save_in_field(Field *field, bool no_conversions)
+{
+  int res= Item_cache::save_in_field(field, no_conversions);
+  return (is_varbinary && field->type() == MYSQL_TYPE_STRING &&
+          value->length() < field->field_length) ? 1 : res;
+}
+
+
 bool Item_cache_row::allocate(uint num)
 {
   item_count= num;
@@ -6465,7 +6473,7 @@ bool Item_cache_row::setup(Item * item)
   {
     Item *el= item->element_index(i);
     Item_cache *tmp;
-    if (!(tmp= values[i]= Item_cache::get_cache(el->result_type())))
+    if (!(tmp= values[i]= Item_cache::get_cache(el)))
       return 1;
     tmp->setup(el);
   }
diff -Nrup a/sql/item.h b/sql/item.h
--- a/sql/item.h	2007-11-08 12:46:55 +04:00
+++ b/sql/item.h	2007-11-10 23:44:11 +04:00
@@ -2469,7 +2469,7 @@ public:
   };
   virtual void store(Item *)= 0;
   enum Type type() const { return CACHE_ITEM; }
-  static Item_cache* get_cache(Item_result type);
+  static Item_cache* get_cache(const Item *item);
   table_map used_tables() const { return used_table_map; }
   virtual void keep_array() {}
   // to prevent drop fixed flag (no need parent cleanup call)
@@ -2531,9 +2531,16 @@ class Item_cache_str: public Item_cache
 {
   char buffer[STRING_BUFFER_USUAL_SIZE];
   String *value, value_buff;
+  bool is_varbinary;
+  
 public:
-  Item_cache_str(): Item_cache(), value(0) { }
-
+  Item_cache_str(const Item *item) :
+    Item_cache(), value(0),
+    is_varbinary(item->type() == FIELD_ITEM &&
+                 ((const Item_field *) item)->field->type() ==
+                   MYSQL_TYPE_VARCHAR &&
+                 !((const Item_field *) item)->field->has_charset())
+  {}
   void store(Item *item);
   double val_real();
   longlong val_int();
@@ -2541,6 +2548,7 @@ public:
   my_decimal *val_decimal(my_decimal *);
   enum Item_result result_type() const { return STRING_RESULT; }
   CHARSET_INFO *charset() const { return value->charset(); };
+  int save_in_field(Field *field, bool no_conversions);
 };
 
 class Item_cache_row: public Item_cache
diff -Nrup a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
--- a/sql/item_cmpfunc.cc	2007-10-13 11:12:12 +05:00
+++ b/sql/item_cmpfunc.cc	2007-11-10 23:44:12 +04:00
@@ -1373,7 +1373,7 @@ longlong Item_func_truth::val_int()
 bool Item_in_optimizer::fix_left(THD *thd, Item **ref)
 {
   if (!args[0]->fixed && args[0]->fix_fields(thd, args) ||
-      !cache && !(cache= Item_cache::get_cache(args[0]->result_type())))
+      !cache && !(cache= Item_cache::get_cache(args[0])))
     return 1;
 
   cache->setup(args[0]);
diff -Nrup a/sql/item_subselect.cc b/sql/item_subselect.cc
--- a/sql/item_subselect.cc	2007-06-29 12:39:15 +05:00
+++ b/sql/item_subselect.cc	2007-11-10 23:44:13 +04:00
@@ -1717,7 +1717,7 @@ void subselect_engine::set_row(List<Item
     item->decimals= sel_item->decimals;
     item->unsigned_flag= sel_item->unsigned_flag;
     maybe_null= sel_item->maybe_null;
-    if (!(row[i]= Item_cache::get_cache(res_type)))
+    if (!(row[i]= Item_cache::get_cache(sel_item)))
       return;
     row[i]->setup(sel_item);
   }
@@ -2178,6 +2178,7 @@ int subselect_indexsubquery_engine::exec
   ((Item_in_subselect *) item)->value= 0;
   empty_result_set= TRUE;
   null_keypart= 0;
+  table->status= 0;
 
   if (check_null)
   {
@@ -2189,6 +2190,16 @@ int subselect_indexsubquery_engine::exec
   /* Copy the ref key and check for nulls... */
   if (copy_ref_key())
     DBUG_RETURN(1);
+
+  if (table->status)
+  {
+    /* 
+      We know that there will be no rows even if we scan. 
+      Can be set in copy_ref_key.
+    */
+    ((Item_in_subselect *) item)->value= 0;
+    DBUG_RETURN(0);
+  }
 
   if (null_keypart)
     DBUG_RETURN(scan_table());
diff -Nrup a/sql/sp_rcontext.cc b/sql/sp_rcontext.cc
--- a/sql/sp_rcontext.cc	2007-07-30 18:13:55 +05:00
+++ b/sql/sp_rcontext.cc	2007-11-10 23:44:14 +04:00
@@ -503,14 +503,14 @@ sp_cursor::fetch(THD *thd, List<struct s
 */
 
 Item_cache *
-sp_rcontext::create_case_expr_holder(THD *thd, Item_result result_type)
+sp_rcontext::create_case_expr_holder(THD *thd, const Item *item)
 {
   Item_cache *holder;
   Query_arena current_arena;
 
   thd->set_n_backup_active_arena(thd->spcont->callers_arena,
&current_arena);
 
-  holder= Item_cache::get_cache(result_type);
+  holder= Item_cache::get_cache(item);
 
   thd->restore_active_arena(thd->spcont->callers_arena, &current_arena);
 
@@ -559,7 +559,7 @@ sp_rcontext::set_case_expr(THD *thd, int
         case_expr_item->result_type())
   {
     m_case_expr_holders[case_expr_id]=
-      create_case_expr_holder(thd, case_expr_item->result_type());
+      create_case_expr_holder(thd, case_expr_item);
   }
 
   m_case_expr_holders[case_expr_id]->store(case_expr_item);
diff -Nrup a/sql/sp_rcontext.h b/sql/sp_rcontext.h
--- a/sql/sp_rcontext.h	2007-07-30 18:07:50 +05:00
+++ b/sql/sp_rcontext.h	2007-11-10 23:44:15 +04:00
@@ -261,7 +261,7 @@ private:
   bool init_var_table(THD *thd);
   bool init_var_items();
 
-  Item_cache *create_case_expr_holder(THD *thd, Item_result result_type);
+  Item_cache *create_case_expr_holder(THD *thd, const Item *item);
 
   int set_variable(THD *thd, Field *field, Item **value);
 }; // class sp_rcontext : public Sql_alloc
diff -Nrup a/sql/sql_class.cc b/sql/sql_class.cc
--- a/sql/sql_class.cc	2007-10-26 17:55:14 +05:00
+++ b/sql/sql_class.cc	2007-11-10 23:44:15 +04:00
@@ -1553,7 +1553,7 @@ bool select_max_min_finder_subselect::se
   {
     if (!cache)
     {
-      cache= Item_cache::get_cache(val_item->result_type());
+      cache= Item_cache::get_cache(val_item);
       switch (val_item->result_type())
       {
       case REAL_RESULT: