From: Ole John Aske
Date: January 13 2011 8:35am
Subject: bzr push into mysql-5.5 branch (ole.john.aske:3233 to 3234) Bug#57034
List-Archive: http://lists.mysql.com/commits/128597
X-Bug: 57034
Message-Id: <20110113083505.124DC223@fimafeng09.norway.sun.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

 3234 Ole John Aske	2011-01-13
      Fix for Bug#57034 incorrect OUTER JOIN result when joined on unique key
      
      Item_equal::val_int() checked for NULL-values by checking Item::null_value
      *before* the respective ::store_value() and ::cmp(Item*) metods where called.
      
      As Item::null_value is set by these metods, the value of 'null_value' 
      is not valid until *after* ::store_value() or ::cmp() has
      been called for the Item object.
            
      Fix is to 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
 3233 Martin Hansson	2011-01-13 [merge]
      Merge of fix for Bug#58165.

    modified:
      mysql-test/r/func_str.result
      mysql-test/t/func_str.test
      sql/item_strfunc.cc
      sql/item_strfunc.h
      sql/sql_string.cc
      sql/sql_string.h
=== modified file 'mysql-test/r/join_outer.result'
--- a/mysql-test/r/join_outer.result	2010-12-17 11:11:34 +0000
+++ b/mysql-test/r/join_outer.result	2011-01-13 08:33:30 +0000
@@ -1432,4 +1432,74 @@ 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;
+#
+# Bug#48046 Server incorrectly processing JOINs on NULL values
+#
+CREATE TABLE `BB` (
+`pk` int(11) NOT NULL AUTO_INCREMENT,
+`time_key` time DEFAULT NULL,
+`varchar_key` varchar(1) DEFAULT NULL,
+`varchar_nokey` varchar(1) DEFAULT NULL,
+PRIMARY KEY (`pk`),
+KEY `time_key` (`time_key`),
+KEY `varchar_key` (`varchar_key`)
+) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
+INSERT INTO `BB` VALUES (10,'18:27:58',NULL,NULL);
+SELECT table1.time_key AS field1, table2.pk 
+FROM BB table1  LEFT JOIN BB table2 
+ON table2.varchar_nokey = table1.varchar_key
+HAVING field1;
+field1	pk
+18:27:58	NULL
+DROP TABLE BB;
+#
+# Bug#49600 Server incorrectly processing RIGHT JOIN with 
+#           constant WHERE clause and no index
+#
+CREATE TABLE `BB` (
+`col_datetime_key` datetime DEFAULT NULL,
+`col_varchar_key` varchar(1) DEFAULT NULL,
+`col_varchar_nokey` varchar(1) DEFAULT NULL,
+KEY `col_datetime_key` (`col_datetime_key`),
+KEY `col_varchar_key` (`col_varchar_key`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+INSERT INTO `BB` VALUES ('1900-01-01 00:00:00',NULL,NULL);
+SELECT table1.col_datetime_key  
+FROM BB table1 RIGHT JOIN BB table2 
+ON table2 .col_varchar_nokey = table1.col_varchar_key
+WHERE 7;
+col_datetime_key
+NULL
+ALTER TABLE BB DISABLE KEYS;
+SELECT table1.col_datetime_key  
+FROM BB table1 RIGHT JOIN BB table2
+ON table2 .col_varchar_nokey = table1.col_varchar_key
+WHERE 7;
+col_datetime_key
+NULL
+DROP TABLE BB;
 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	2011-01-13 08:33:30 +0000
@@ -1010,4 +1010,86 @@ 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 #
+--echo # Bug#48046 Server incorrectly processing JOINs on NULL values
+--echo #
+
+# bug#48046 is a duplicate of bug#57034
+
+CREATE TABLE `BB` (
+  `pk` int(11) NOT NULL AUTO_INCREMENT,
+  `time_key` time DEFAULT NULL,
+  `varchar_key` varchar(1) DEFAULT NULL,
+  `varchar_nokey` varchar(1) DEFAULT NULL,
+  PRIMARY KEY (`pk`),
+  KEY `time_key` (`time_key`),
+  KEY `varchar_key` (`varchar_key`)
+) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
+
+INSERT INTO `BB` VALUES (10,'18:27:58',NULL,NULL);
+
+SELECT table1.time_key AS field1, table2.pk 
+FROM BB table1  LEFT JOIN BB table2 
+ ON table2.varchar_nokey = table1.varchar_key
+ HAVING field1;
+
+DROP TABLE BB;
+
+--echo #
+--echo # Bug#49600 Server incorrectly processing RIGHT JOIN with 
+--echo #           constant WHERE clause and no index
+--echo #
+
+# bug#49600 is a duplicate of bug#57034
+
+CREATE TABLE `BB` (
+  `col_datetime_key` datetime DEFAULT NULL,
+  `col_varchar_key` varchar(1) DEFAULT NULL,
+  `col_varchar_nokey` varchar(1) DEFAULT NULL,
+  KEY `col_datetime_key` (`col_datetime_key`),
+  KEY `col_varchar_key` (`col_varchar_key`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1;
+
+INSERT INTO `BB` VALUES ('1900-01-01 00:00:00',NULL,NULL);
+
+SELECT table1.col_datetime_key  
+FROM BB table1 RIGHT JOIN BB table2 
+ ON table2 .col_varchar_nokey = table1.col_varchar_key
+ WHERE 7;
+
+# Disable keys, and we get incorrect result for the same query
+ALTER TABLE BB DISABLE KEYS;
+
+SELECT table1.col_datetime_key  
+FROM BB table1 RIGHT JOIN BB table2
+ ON table2 .col_varchar_nokey = table1.col_varchar_key
+ WHERE 7;
+
+DROP TABLE BB;
+
 --echo End of 5.1 tests

=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc	2010-12-29 00:26:31 +0000
+++ b/sql/item_cmpfunc.cc	2011-01-13 08:33:30 +0000
@@ -5643,15 +5643,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;
     }
   }

No bundle (reason: useless for push emails).