From: Ole John Aske Date: December 20 2010 1:16pm Subject: bzr commit into mysql-5.1 branch (ole.john.aske:3529) Bug#57034 List-Archive: http://lists.mysql.com/commits/127305 X-Bug: 57034 Message-Id: <20101220131605.BDB60223@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============2163991168979558102==" --===============2163991168979558102== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///net/fimafeng09/export/home/tmp/oleja/mysql/mysql-5.1/ based on revid:georgi.kodinov@stripped 3529 Ole John Aske 2010-12-20 Updated fix for bug#57034 'incorrect OUTER JOIN result when joined on unique key' after review: Added testcase for bug#48046 & bug#49600 which are duplicates. ............. 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-20 13:16:01 +0000 @@ -1427,4 +1427,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 2010-12-20 13:16:01 +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-16 14:40:52 +0000 +++ b/sql/item_cmpfunc.cc 2010-12-20 13:16:01 +0000 @@ -5605,15 +5605,15 @@ longlong Item_equal::val_int() return 0; List_iterator_fast 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; } } --===============2163991168979558102== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/ole.john.aske@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: ole.john.aske@stripped\ # 29uiq8gh5205xpkn # target_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\ # /mysql-5.1/ # testament_sha1: 26af1bbfb54f347715f86e27133cb396e1b4f9a7 # timestamp: 2010-12-20 14:16:05 +0100 # base_revision_id: georgi.kodinov@stripped\ # 9imm43geck5u55qw # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWX8QGVkABcffgBiyWff//3// /+D////0YAyDr7I7s6rQAdVltzhoBmWU3dbim7Btl27spMJKaBBMmFPQJppqTymn6ptR+qPU9Q9R hGBPUMhkYQSSgp5U/aDQFPIphqY0jYpiDIADQAAAcZMmjQGjTEZGhiGBNGmIMRoMIADBIkQJT9GR qU/ZRipmmo2k8p4ao9QaGgPao9Qeo00AcZMmjQGjTEZGhiGBNGmIMRoMIADAqiTQJpoCm0GgAiek yag3qgHqBoyeoNBk0SSuxPacfF5/bHXHndKcT+Ch65t0aavhODv67u77SBwvdsHDcLSEEjF9vVsd E3sf3XnPfj4K8a918vn2jkXDmOlhzlRqhFzGBw+FMI6FYsgjKUCmc0/H97t1oU75vVmt5UOc72Uj bi0VcpNo0hI0FDcqPqM8VNt1FJhUNrhIClymQkOeJ+8NV3FotRLjOL76kio6Cg9hkRLFVqyIbCYi MYepI1phHYhq6vH5H1DIQHOMJ2f9cQcA9kg7F4r302xttHpqP4FdtxqoZZENk+SRJbmuJ0Tiyc/h CX992Hpzgw3CYM9BwbtCiPYih2I5iu2Z+ZomZiZmfiuoaju8C41mgU2UR8olH+le55GwoSo1ma9u SRVnGmKWFxocznZvkaUSleeDrYqNLS0rz5A8hYZMMwxzFdhp7W7keLEhx5OiosOcn7L9Z7CJSt2N o39+OUeoleFxpuN/e67crPKyYOKtKv9hZ32IEVf/zSwi2m8v3fPFbivJxfOeCj6zAoSSsbh7keZQ lKiLTyS2aUZuht4Rbu3bvevFtY44ZOHV6bvJT6rDm+977dK6vJ7ckTaS511ppg2222vOhRjfm9Jn i/SuHiudIsRvSFlaQ5uJjuGolojZZSLeUFGiW49EpbwmX6ES887k1DUJqGShtuR1ZMTFRKi9KSx5 ktiEqul6jUFSO02p/I7eecRZQ87Tc/rFQffuboNfjno5nmWWHJakZChuJEknExga0xDxLVpqRJR0 XxjhhFI/TUi+UyaSku9RKKLOU/a5mkwXFVy5zrUqIXLkojEhQmiHeetYXw31J+bCi9DnYJrGbGxd 05GKLY0SsvTT03Nz4ueOnq9URvnXgpayUW0mclS0lv8hVytCwtXkerAsEkklHutWmSURzSU72N7q 9rB79eviwxyIbY0xnZnhhu2WoZcIYsMzbZ7dWBc6UOOjVMS78ub1u+XaucObqTKlG+OvwalrfHa4 7nVutRyP+u7txl00opx30LElKO47jssFpykOlCqGLViiOmSe9VyO62LNGPOrypd8XWnROLkXb9iH XYjcvcW/oZNSUvY2vv9GHo63Zy5M2O9sQqzeI8jFlgsoyiTTOUzRBQUOHJtvllvOQ0WDxsWJh2aT ZrbFi2MovsuiT1FmbjXdzfqNSSsVMfbq61URfy16naq16wxc4azak2COvtGm4ZbkFKVwWwxF6NaP Css85WMXdjvwXq8Tujiqho5daYuqxaTKlwiUlFxMgMLZokXvOnvHTgStJNhnRXF2zkYmZ8Nx4DZj VtdnyKStD6yao/K++BKjTgFKqCb0MSCymXC9zMGHNOvZRqxNBZkrp1L1vTLXjzzXCM519qGi6lqR xIQmA+CgcUyDxzpIxwM4qhhnKa2soMzjeUMwjsqBq8Fcofto6HD8nYu2lrGVVQNWzBGTSgh1UEUh aKkrrfuVUR2ysIa+mCboiCYwmygkt4aNjEuWFNNeVZJfGbA+cIDmyWzjZ/osyRSLSMh6Nj7hU2n2 nxFGofaFCq+nKiMlkTEzNCxB+sf4QWDCqFo+JYhJYgsROb6iUfcS2CSxJJK4oMrEP4BKNiFTA2GE NaFo0CTJ/wh6agsRSDC9ec9wnB7gjL3iO4VwlmMjuSXxCIbCgGJC1kcH6CsRC2ZmZmUOWA/qdCDe hVHCBGojJEpN4tJfYTELCSUSipKKlpcsgWJiUjhGSVT6ySJRV0D74HBC1elKZojcsi0+GaPwdbSu xkyM4iOJFiN47kOVG8uEiRL+zBGKGwbTSyNJylR2xnMwltVIwFyJGZgGY4RctFDsfgVFYC0LjGKH JYKJa0ktDUlKJQXHrP1ES930R64qDvCBjAYxjBgNI5YEUCo4cIAVVSaQcwVgMGdg0k2N3JF36EKI IhuCFEEJmGbMx91YH4HotgeLw9rm+WEnAB+tBhYexyAlyRKrFd/kpi7V6/SwiGtZ5ZAx6P0LzDSC CoEE8UD7fFI3EFjYlFgU+s/GR+ZM827EsMiXf4eHhb8EFJKxY99Hy5UDqw5Yjk7CJdcus8Gt8q9r v+zK1l35xydhsxblH9lE03mtRhtnWQaXkhbJWbFkC49veGsuDwF6iAP8m3IJASk3ATEwY0C+0/61 DU3NSGfc2XViUwpTydsTGrqo6TQ5FzR1LWSuV3Rph0LWcO1XvXsIj2PLpcSLmp7/pw3O06bCVYc4 aJtYt6iapP63AXg4OqPKWsm+u6Sg7pJZSaIUaSVowglYSu5TRun2rkbjxM5HqIinKqdaVrSLM7WE aVQCHaLWuLrEOScg9lEBXGvauPj6jiODc287Q8NF7pWumbNqgNCVVViRZHcqA5kyMAgCSEwG2Ny1 Juf+QgtcLdrwehnWJWMJdnLBKdhFLSIvTERGG3xvk6Ed8seVdWOn4ePe3OvqXKnzNRzfM8+U/CKR Q83F4WI17U4mxqetfDCEc+HTKN0aDKr/SEnwePJ5sO1sdqE+Akgyq+zOr2xWaSZL5gUhF5ZI1AU5 gcaTnwzcFwfqphVzJTvfklp1fh4llYPnUQjyjW5rDgij7phwRKyNusrkW7ERKQ8/DjhgjtQ4RBqf O0RSX18iMtKvnyq2YmrHqqWe87mfYXHEkVVDMUrf5zsIOEvGXANCA8Fh3YrMNB5hMiUApDGNIlsv JCs2mYR16hp+jg3FiPg5/CuKY2IQ9y0oFf1v7mtfEdPx1RSsDCIWRYCYm3HdojJ5eopI2rrEPc9v hJoslmEs1HGLKSjpj8S0xJIecr9FIw+G+L7yhjct0re9p8l3tyVxmKRaTWITSOdceKpRHfHIa7BJ MQrfRGVsQbHWwc6E3HJ7+9tinEwlQMVgwBPAoGRs712uNWV3iMKTKx4SA4R+uwEUiNghL+TpBq7t mAdgyxF4umQrSu0/9A2osmOBWLh5Vidd3R49jsRzLjPq5y6DH1TEwn9D67TxXs86lz2fB9DtJP2H 70LmflHJXGaYXyxM5xbRGXPLLEJZ+0qSC3Wb1ityJMggBtrkDAlZMikjjaRBBiQZy5gMnoMwa1PD jQzjAnV6lkaa+7ebb6Z3javpMJjv6+jbauTPUMoo4oB10fHmspllHNTcLY1amJrWCcUS8syRmeu6 zymw6F0Ntu7WsONheDEx6gOqEoxwCgV7DOJfjvsxsLrVFG+y3hiiEyH2vTK6MPYicLAwwBUuXT/X niZR7nSjmW+MbGixESil6jb66I5LuKltisGdAxjbesrCvAMivBBmU8ItaNWPc7nDnq5OyNbs84g5 R9NKLnn9SZTCXohYq2HVyREmoOBpiuvWuUW60e6xVrVRxXNERIjbA2G0bbTFA4bzrILt9aXvHdyk tVuhNK8u5DKo2numjP0mIilLgxamgHSiIY9XIeITQlaZDEBvreLWHidHjV/cP+LuSKcKEg/iAysg --===============2163991168979558102==--