From: Ole John Aske Date: January 14 2011 8:55am Subject: bzr push into mysql-5.1-telco-7.0 branch (ole.john.aske:4105 to 4106) Bug#57034 List-Archive: http://lists.mysql.com/commits/128701 X-Bug: 57034 Message-Id: <20110114085533.3AC56223@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 4106 Ole John Aske 2011-01-14 Backport to mysql-5.1-telco-7.0: 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 4105 jonas oreland 2011-01-12 ndb - rename trp_client::forceSend() to do_forceSend() to avoid nasty clash with TransporterSendBufferHandle::forceSend() modified: storage/ndb/src/ndbapi/NdbDictionaryImpl.cpp storage/ndb/src/ndbapi/Ndbif.cpp storage/ndb/src/ndbapi/SignalSender.cpp storage/ndb/src/ndbapi/trp_client.cpp storage/ndb/src/ndbapi/trp_client.hpp === modified file 'mysql-test/r/join_outer.result' --- a/mysql-test/r/join_outer.result 2010-06-01 07:54:06 +0000 +++ b/mysql-test/r/join_outer.result 2011-01-14 08:54:47 +0000 @@ -1397,4 +1397,74 @@ id select_type table type possible_keys Warnings: Note 1003 select straight_join `test`.`jt1`.`f1` AS `f1` from `test`.`t1` `jt6` left join (`test`.`t1` `jt3` join `test`.`t1` `jt4` left join `test`.`t1` `jt5` on(1) left join `test`.`t1` `jt2` on(1)) on((`test`.`jt6`.`f1` and 1)) left join `test`.`t1` `jt1` on(1) where 1 DROP TABLE t1; +# +# 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-06-01 07:54:06 +0000 +++ b/mysql-test/t/join_outer.test 2011-01-14 08:54:47 +0000 @@ -981,4 +981,86 @@ EXPLAIN EXTENDED SELECT STRAIGHT_JOIN jt DROP TABLE t1; +--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-10-06 10:06:47 +0000 +++ b/sql/item_cmpfunc.cc 2011-01-14 08:54:47 +0000 @@ -5525,15 +5525,25 @@ longlong Item_equal::val_int() return 0; List_iterator_fast it(fields); Item *item= const_item ? const_item : it++; +#ifndef MCP_BUG57034 + eval_item->store_value(item); + if ((null_value= item->null_value)) + return 0; +#else if ((null_value= item->null_value)) return 0; eval_item->store_value(item); +#endif while ((item_field= it++)) { /* Skip fields of non-const tables. They haven't been read yet */ if (item_field->field->table->const_table) { +#ifndef MCP_BUG57034 + if (eval_item->cmp(item_field) || (null_value= item_field->null_value)) +#else if ((null_value= item_field->null_value) || eval_item->cmp(item_field)) +#endif return 0; } } No bundle (reason: useless for push emails).