bzr commit into mysql-5.1 branch (ole.john.aske:3477) Bug#57034

[Ole John Aske]

#At file:///net/fimafeng09/export/home/tmp/oleja/mysql/mysql-5.1/ based on revid:georgi.kodinov@stripped

 3477 Ole John Aske	2010-12-03
      Fix for bug#57034: incorrect OUTER JOIN result when joined on unique key.
      
      The value of 'null_value' is not valid until *after* ::store_value() or ::cmp() has 
      been called for the Item object.
      
      Fix is to check swap order of ::store_value()/::cmp() and checking of Item::null_value.
      This pattern is widely used other places inside item_cmpfunc.cc

    modified:
      mysql-test/r/join_outer.result
      mysql-test/t/join_outer.test
      sql/item_cmpfunc.cc
=== modified file 'mysql-test/r/join_outer.result'
--- a/mysql-test/r/join_outer.result	2010-10-29 08:23:06 +0000
+++ b/mysql-test/r/join_outer.result	2010-12-03 16:55:30 +0000
@@ -1427,4 +1427,24 @@ WHERE t1.f1 = 4 AND t2.f1 IS NOT NULL AN
 GROUP BY t2.f1, t2.f2;
 f1	f1	f2
 DROP TABLE t1,t2;
+#
+# Bug#57034 incorrect OUTER JOIN result when joined on unique key
+#
+CREATE TABLE t1 (pk INT PRIMARY KEY, col_int INT, col_int_unique INT UNIQUE KEY);
+INSERT INTO t1 VALUES (1,NULL,2), (2,0,0);
+CREATE TABLE t2 (pk INT PRIMARY KEY, col_int INT, col_int_unique INT UNIQUE KEY);
+INSERT INTO t2 VALUES (1,0,1), (2,0,2);
+EXPLAIN
+SELECT * FROM t1 LEFT JOIN t2
+ON t1.col_int_unique = t2.col_int_unique AND t1.col_int = t2.col_int 
+WHERE t1.pk=1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
+1	SIMPLE	t2	const	col_int_unique	col_int_unique	5	const	1	
+SELECT * FROM t1 LEFT JOIN t2
+ON t1.col_int_unique = t2.col_int_unique AND t1.col_int = t2.col_int 
+WHERE t1.pk=1;
+pk	col_int	col_int_unique	pk	col_int	col_int_unique
+1	NULL	2	NULL	NULL	NULL
+DROP TABLE t1,t2;
 End of 5.1 tests

=== modified file 'mysql-test/t/join_outer.test'
--- a/mysql-test/t/join_outer.test	2010-10-29 08:23:06 +0000
+++ b/mysql-test/t/join_outer.test	2010-12-03 16:55:30 +0000
@@ -1010,4 +1010,24 @@ GROUP BY t2.f1, t2.f2;
 
 DROP TABLE t1,t2;
 
+--echo #
+--echo # Bug#57034 incorrect OUTER JOIN result when joined on unique key
+--echo #
+
+CREATE TABLE t1 (pk INT PRIMARY KEY, col_int INT, col_int_unique INT UNIQUE KEY);
+INSERT INTO t1 VALUES (1,NULL,2), (2,0,0);
+CREATE TABLE t2 (pk INT PRIMARY KEY, col_int INT, col_int_unique INT UNIQUE KEY);
+INSERT INTO t2 VALUES (1,0,1), (2,0,2);
+
+EXPLAIN
+SELECT * FROM t1 LEFT JOIN t2
+  ON t1.col_int_unique = t2.col_int_unique AND t1.col_int = t2.col_int 
+  WHERE t1.pk=1;
+
+SELECT * FROM t1 LEFT JOIN t2
+  ON t1.col_int_unique = t2.col_int_unique AND t1.col_int = t2.col_int 
+  WHERE t1.pk=1;
+
+DROP TABLE t1,t2;
+
 --echo End of 5.1 tests

=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc	2010-09-09 12:48:06 +0000
+++ b/sql/item_cmpfunc.cc	2010-12-03 16:55:30 +0000
@@ -5604,15 +5604,15 @@ longlong Item_equal::val_int()
     return 0;
   List_iterator_fast<Item_field> it(fields);
   Item *item= const_item ? const_item : it++;
+  eval_item->store_value(item);
   if ((null_value= item->null_value))
     return 0;
-  eval_item->store_value(item);
   while ((item_field= it++))
   {
     /* Skip fields of non-const tables. They haven't been read yet */
     if (item_field->field->table->const_table)
     {
-      if ((null_value= item_field->null_value) || eval_item->cmp(item_field))
+      if (eval_item->cmp(item_field) || (null_value= item_field->null_value))
         return 0;
     }
   }


Attachment: [text/bzr-bundle] bzr/[e-mail]