From: Ole John Aske Date: January 14 2011 8:54am Subject: bzr commit into mysql-5.1-telco-7.0 branch (ole.john.aske:4106) Bug#57034 List-Archive: http://lists.mysql.com/commits/128700 X-Bug: 57034 Message-Id: <20110114085454.AAFAE223@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============7517159898718202688==" --===============7517159898718202688== 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-telco-7.0/ based on revid:jonas@stripped 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 === 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; } } --===============7517159898718202688== 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\ # lpa53iyi8klji1g4 # target_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\ # /mysql-5.1-telco-7.0/ # testament_sha1: 13293ea8e0ec68122424499d23f8aa919ccece40 # timestamp: 2011-01-14 09:54:54 +0100 # base_revision_id: jonas@stripped # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWQUHLRUABf/fgFDyWPf//3// /+D////0YA07ur5qnLu1VqUoAGsAAZd3c2vQOkr1p7Ztq2228NSbVHqDIAGjTTQAAAAAAAAAEkiY g0TBNBCeI0NRo9U0niQeUAMgGg00CUimwiYk9T1Gmg8oaNNDQAANAD0QGgASJCaRqaeU02pNT9Kb 1Jv1Gknqb1Gmo9E9JhD1ADRkyNBxkyaNAaNMRkaGIYE0aYgxGgwgAMCpIhBMJkAnpT9UyMRPMpo0 noQaNG0R6jaQ9AjgJzEbXP24mZbM9TXMMI+slFLIYvx0CvWqrcIDVMwURkQkRbt8JPNVyNf9WlP8 v6DI59NY28g0Yjg5oG1TCbUxkzh50/7bRDWsFdSa/Z6UBbXZZUWzmpfy8uFwVcub1v6dBcNvSyo2 40C8XgupRtF3EEzgLW5Wekwi1tm3UkjdSMy0kCkPgM8UqK1jg3lHnu39tXKFsqKXhNbyNb8HBiXY MXdgTnKDTHBUNyg+BHj9h9qyk0lzzKMM37bFn3H/fbgemWnbKVTaG+moz2o2cm7jtr3nEMsrI2TR bEb1KSnPpChM/P5vk9XRbYUcUop3vuc2pZPOljwmqnYY9FV6C5tptv1FkBeGW0LAwDOBHu3cMxfu mKZTlPiN5YpZymdsZ3OonNk2zZLYGc1ODsbXsNiUpmPI95oWampqZj2G9elSlUpzt5kF20WRkVlr qKZCwcJIax4EKcbyOiuyOEbdR1IbBNYKKa8s/WVlLRYSqhvJKV/lkltZAMR/0tr0YucHE11Gl8tX Y+I99Z95nLFFLt4+NPIWKUskA5DExaujUX7zC/YsoUZeskBChSJmny6+uuP0pPf1+qMuVSlm40Ta S5luTTBttttZJDFozeVvjOwrDHx7as4DBMFGFEwWxXjonWaPQNRTIO69edVFpLCl8uQ5yUukJnBe GesaLMq4JqGoTVqXtVVVzhrVqNS6lmZSi71FNCKYOOs4g3wDrXQQsWfeIpuDI2yhdQrhFp9fgi80 8xww08OGl9CujbI6DUdZQoozGDpNEjaqIiRC1RZOB5vpUJSXip38pITmRcrGPAcMJy/VPrdbaaWR nPcyZ2fwGdlWZZgvEtEuNSTFnsKC5iCUEtRIKFSniHkT5uB4oKZWYEyMJah0PocFvvymlg1NI6JY OKB4UAxzAea2hcwZmtmLiiijvxYzbU0ROncu1U4J+m3T0JkkLqyFOmpyeE23Ftd7UMzXLcWOKhH5 UPVRjDZ7bpzFptHVE6YbvM5MbD5SNNvIZhzjNeEy5vnX23duCUj3+M6fFZbtlFlHvLM+rwnhMBkG sEZqmWBYcIA15T2RlxVQ4oQ1O9hTKjYeZYf8+wq5ncbTl+C56zUEs8h9e/ePgxTrH/iZkUoqlzDy 7ytsW6sqKKVA2pxV6OEdn5Ny1sdTjrKojixk5KotQpsQGTYrtIUJgTi4vHjmgoFchEiHrUnHzUDP bSULj43kypcQrH+N7zQ2xz7fV1NuN2LdhXejBkfjqLi8ReQkIgVj68TYqC2krcKuIbW0PmMti3YF UneFJfcRKL9xjhiUh2DixM4mNPEyLAq3RoGuJHCjxtdhNFlemUS2HU9DxuzrtBdg/LWMVpM8+vCB rMDArWnLftIE9SkrNEUBXoQ3OHDh8TzJFJjnk4dXQKGCsU2MTXqdlERS9nG/AmrGvuy8hDzVZvYi Wqka8LoXEaFEiUj7nlT41yaqgjrWQ1iJwoT8kx91x5plZbAtwVz9VjTmmZwQinRoQ0ojmGgVittc YW0jqlmIa9UE88WSlJUGFGWEl1BfimEuLlQmXBbEvidofMHBw3LlvZEvaq0j3FIsVI9Tc/IYG8/E /qLOUfiFjBmt2JNV6pUpVi6H8h7UP1YDjpykcjQPsMEUYIXStr1lJ+ZTeKLqKKZFhsuj1hSb0YGc 5DPG5GI0ijkfkR9GwLpoLJZsZnuSx7jLH9EfUm1JqdL/Mk6jE8DONBGLkOh9Jh9lTKoiqqqqqR1I PYcEOdGCc6E2k1JSjnGDEs9ZUjAopKTEpMDIyXguqUodM5FMD7SiUlYPAPzg6kYMylKqycy8xOOt P1cWploo7Wo2SSxLpzDxI605jIUKFPYzpoRyjcanIajqMB4ZrqopuYEzjJKGszhrHTMmIsav1MB2 MUGQZjTLFhSmZRTQ1qUlIb2R8Z9RKnf6J5Zgg+8spQpSlFCpHisjFMfN67CZpmipI3GcUU+VUkql PBIw5iFEEQ3BCiCFEERvQZwOs+rsOvzEj7f1EaZVJ/Z6P+gjGrADVeZ9UJFNBjnx7ZHssqL9POjt +1aQx7Zy5uyLPbva/AQ0EOaHG+me2Rdrf0ERcIO71mAYQFq0VEp5YF29FXjy3KLhheIBS3OEsvBN 6tHG4LVqKPDnxH5mZMuO0ORoMxjkDGqNgILVkIpOFEhpGpe/tDvHcN8PzF2EAf7NuQSF71VjBKKV BnxP0bRtdrbI2fA3ZMJSi1tjwzDJebONmhi7nFp0cjkjTHBhfGTT0ugXM4kiyVI2GDy1jmGG8k1p 2qBqzk1B5+xwHmDB89bC+F0UvalSPoLbVdNBmdUC5zPAg5/ZAljuNIYH+Nx1cx0PRErlUsVokeng y0deoAhpizRpM5AwOYgOyFiAUJ6wZYiEeXMhpBVuwEyTjRYB0n1M4hCUZY/eToSDKOigag4JkeXw eFUaknepz+E0/f/UKA7L5W8KApQ56VXCgSoKD4+Y4VxBLsAUhkkpZzOjJhwxxE7wOnj5Tcew6fDr SLyGLQrN/qOmgfinJy8iR3HnQGTFuDWB3ERSSNugjspSqce878vBe3pxLnjd4DAgroDB69yezRIn 3cgURGixLpG1BUuojYbcBGd3sHbqn19qUcX6JW95ofg4PQioRQ+R6pdE87RaOxLDqSlpy32FYbZJ Sg8/w90pCzSCw9xUQe5fb2irLKBhumRQ/Ye3H+B4CL12Zmmx4jmkXXDMWrRcPWckHM1nIeBneChn 8rNipUOKUvRLKUqRft/cumZxdyO/kwr4+yTmLp6XZ5cOZXzqd29EqgqZIsGPmf7NbNAhw+epOgkU JD08AGTQqMqyw5COmgh43ETkmheTnacpD53l89Ga9GcKjbFlpVSfD4pADwUCFZVIrQifqZIPeCwh eMjIm/wTEjnl6MwvUsXkb2N6PKsWTTLTA2YCopNqmcygyVcUggcCk2iGy7y6qsTCUxlMGALHK1Ry vy+mW7pjOHLzI1uh1OvfuMFWj8PSRmRmRJp9zvKm35tcyDvU5I5U5J3YJsY7jjI3JeUIaoBykk+C mvLYbBYG93eNFEGQjNi00PsKB0IznQEDx6nvOAMH0CBZzOOul9cA1P2FpgtNn5nlnX0Qts9D4vDI bfAv2SotVlhbtoeaVck15mUHn2sSHQwzUnEdWMNUcJ+zs44Gg5ehctGspuxDhti+0DhHXCIQ1t79 0yn41JD/Ia+l2Vk4krdkendnfPgVjmw2KP1ODNVlejBMVCyvSN/wxMqnwved876qqrn3dYtwo3FJ Tmh6F5O5fp6DIWLAXJiW5n6rKAlFOHGt8dtYkMwB959k4qn9aVnuGfODCZMt78OCqipRVPl4p2O2 bmglcKKkBpfRSWrDGxtizDOkYxtvuFloaszDeVFJmkWKGbqmRdXI7zvNm/X7rzi5aMhMyYS9HWmL 1u6lSpdtRw54UcgdByMN3B42ZdltLxSLzzkzk4lXQNoTPmYGyQ2GwbbTFA4bb7DFdgZ/CwVh9eZr u/YyWUzHuP0WF5yNV3U+JFL7WU5qg9+2YtVrq8L8S8Sb/TNaOgweQeNz7bEAyiInxX+LuSKcKEgC g5aKgA== --===============7517159898718202688==--