From: Ole John Aske Date: January 13 2011 8:24am Subject: bzr commit into mysql-5.5 branch (ole.john.aske:3233) Bug#57034 List-Archive: http://lists.mysql.com/commits/128591 X-Bug: 57034 Message-Id: <20110113082407.77B37223@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============9070395711080246390==" --===============9070395711080246390== 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.5/ based on revid:bjorn.munch@stripped 3233 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 === 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:24:01 +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:24: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-29 00:26:31 +0000 +++ b/sql/item_cmpfunc.cc 2011-01-13 08:24:01 +0000 @@ -5643,15 +5643,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; } } --===============9070395711080246390== 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\ # somb5hixjjh8avbp # target_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\ # /mysql-5.5/ # testament_sha1: 71267cbaf6eef610fc3ee6ecd6fc8051e579c812 # timestamp: 2011-01-13 09:24:07 +0100 # base_revision_id: bjorn.munch@stripped\ # 3zw6jvo3jywfqsva # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWW60xDUABbNfgFCyWPf//3// /+D////0YAzO6vsR0t6940uaaB2cAAWzrpte2dVaKo1tNbbUMkmTRqbVGTRoeo9qmnpDRo00AHqN AGgDQAJJCA0TE0xBqaMU2SA0mhkGQAAAbUCUU0yaJP0oemkDQ0GgDQaDID1AGhoAAkRI0U2g0mSn 6PU1Gp4jEZJpk0DQDTQaMg0BzCaA0Bo0YRoMRpiZMTQYRoGQDJgFSSCaAQ0A1NJ+iYRNJmoDTQGh 6aZIaZMnqZTQEGMX6uvGzOZBI2sHiJIjo0Mld+NWnrixYAsXRnhBbeTKXAw10z+Ljdh3oPbm9xr9 +js642+DlGjSODpgbVOGNqxGMa/5CNuQlKUhQ6SRpM96SYLdqcJdGZiQrMJhVXSy0bcYC6L6UbQ8 QkuLW5p9Rnirbey4qbLhVSvIBMqPI3CsRGXNILmeNV7o4ntjcwB5bHGE52BcvYPXeNpRNDmUOhUT gjw+B/gaAgOwYUr/2gg9Yfz7aB8aheBNsbbR5Kn8CyOK2jLojJlkdzcmUQUdkxdNPIE+/y+L5Nka AYbxMGdh6zXmIF2CgO+GDNwWam/GZm2m2/rLYDSdveYnEaBb8PVuvT7pSXsO54nIWKWcRsZnKom7 BtmyWvMxpdje2Pga0pTMd7gzrNTU1Mx8DjXUqUqlOVyNzx6XOw0c+Obs8+ttbofrjvEp35BNRWZM zUMcANkWMkUQc+yrqUVAPS8cgO+l6+6y9hM31WxymFuQzOPymJdjydTyPkWfYZFiilzkHpTwLFKW TA7VNSbfTV/F1tvRN1+7d6g0AMWWiYWuW/sOjqkGDbqVTA01rk4K0BN07RQoAiNttroSNN/lb5D5 iqVl+pwcoFBUGFGCpEIx2BR3gVgxSG9t1MwthKSWPzOj394QLrApmdXb0V1JqGoTUMmG25DfnHgG BIyCoxhJwgpKIKRlksBAA4LCLFSubUIOiL5qMuiSFqF7ysi/k9Dfd16N/Ll6NkjDmLzpKFFGRkxp JrqR33TFiakos35TDLKds8cENkz09Cyksu6J/Z0tZkxLr2LHqOlGWZTRKiUFinKH9EyuAtMyHxRV QRJuv3V9A6CIL4L+ALhUVq5a/k4Tfp5Bs51r2dZqZ15gU8Bc52dcXsmaka8i8UUUfNiwmeJoWdFS 3c09mPysiqFOnulCnjLr8rFYmjCY5oxyxpURmk1LjRMDNPVXCoN1GbFvtsUdvM4sdhbRe1OzrGYc 4kWBlqXYS56SAtR79/Bp2NzrHj9j8nhAYHuN/AOwHgSBiR45syptm+xXYtg18HctI0ZTbyW6XRM6 4X6HUwrSBEfrP97isd6yU9hmYP5sRiI0DjU4rGMHjjaMvrygVOlOnKMR0CIRMfkWfq05HoILhqNO qx7tDtI03QRqAyZbYELAZcpdFyWESeld+uYlRcmnDYZGW/EkkkLPf4X7CxrGuuwttqh55ubywsVs q5UhoS8++BkiUxUTx3lZZQZcpJqCQ010CPLPtqMqWrZl1OYrTYQdePnBmNTKQYeTdOlu8ljSX3oh LQ/bjdKMSl2v1rvMTLC624qRa8kjadumwkLTGUzMlVPBDohNISkSUoqH0HcSFNWysZ5QEQgpLSGi qRhFpOUlNkrqSYjOxdBiKobR6hFVsXEhR1Fbo2m6grLHPaweCyko6FCx5RAkBz4nosEXwpNZFStK 0elaKytuvDCK1mQstUVoh2RFoZxWq65xqrZHmm8hr7IKOyIKDCjLCV5w08jEuaCjY1vWSXoNwekI O7qnl109ThNMj45JuUj6XI/IXm4+8+sWcQ+8LF7NbqSal0qVVWLkP5j/aFwyvkYD6y5FFyFyVrfY Un5FOIUXKKKYlhpuR7QpOJF5kcRlG1GA0CjS/6R+7YFyZyyei7a/FKPxMb/zR8E4kmpzPhJPiYHW yGcjBpOd9Jf7amNRFVVVVUjpQf5N6HKi9OhCbSakpRyjAp7ypFxRSUl5SXmBiuguVKUOiaVLz7Si UlXt4/ODoRezKUqrJuXTA69afF52pjno0muSeYlybh2o6U3GIoUKf6ZJnRxDjNTSajoLxwmuqinG vJkMUoazINY55iwFjV8S8XoMAxM8sWFKYqKZNSlJSGB6T6iVPn9k8ZfA9xZShSlKKFSO2yMEw+31 2EzTMVIdxkKKexUSbG9KRp6SFEEQ3BCiCFEERhB6T6DBGZq8T8RzVF2hqgBUV5zCoA2/sz8fKtCz +z5RMDQ9O4skw7Jx5umLPd/Xc9vWQ0EOaFvfI7mxkktkY/Xy3Pe9qMHhwdr7t7PRpR27KcFL3u9+ i9w8OHbRKfnyW4QHGG3MRJ1iuqBNxY4hU6su4nPYYFWutxtPEc2rc711W5inGZbSGiZkYX2ly4NU /v1BtM4fmL5yAP8o3ISBMtwFBMGNAVwPzbBsb2yRr8nFivlKLW8XVL+F029nZcw0szpYNGp5mqPD Fva47HY4mbRJ3eZvJsXubbmZ3gb72JmpOtSGMrzh3uAsBg8cu0scvJJmyfI7yk86jAV7qALXxkCW XcU346Z9x1GBmuWha0izmaxjQrAQ1ArQ16a9vZ5xEFKH9UXvCx8/Utb+OXH+LGtkjGYe4aD8ulM2 8oUk2NKck2WWjXrobZQie5EBLHKSqhUUJhOPsCgZaq5+/QLkRKV+iglQ83TaLQWJYgKrSSm09tbB uF2sMQU1Bnu6/QZhCIZbTheHgnJy0Gw7Hhax5e5WOiG48U/awqUjZuoSoi+Lebjh0y3ltgijgKIa bzVdNUuqsmL3elHAmZHHszMOfpBT1sayGqvmO1zvpzSlufilHw4A56DsHCF2mtwc6WHMlLTZdqKz 4a5JSg8O3ryyTkkNTyZyR7l920VMSHr1EH4Hdf9hERYteot+PLycRHsEdOaRu3KpyTKcPY+aemd8 PLe8FrhzEt1cP16/PKVDvJksFAxjSJ3bJFcc518WHdkbQkWw3+GnCNePjRKoKYJYL/3vuNrNJv/h slrwykXS4FSsM7k0NL1cqWUONjck/R5fNRouqawprWWlVJ2SyO+8yKI7K8yvPpzcOUyyLGjJhnMl bzmi3qzE3tQEpHDRTyWhXoKBIsVUNLUhaKkdWFs1JWEhxvOydaKx5+DZdoSkmpU1IYBmhQ0Ynv88 dvhtXix4hGg3WrLXiF1hEo9m8RkjsRJ+D5Spxey8PoVvRWiORPXcnn2MOU+IcaXXWOq045kPLsxl t2V/jtcE68dm18lHWVKzQ0c9Sor6V53s2ObXrxMz0/O8naUfxMGvwNUzqIBa/EqL8cNIc6vQbVu3 pBo1mSghzOHAO3VCxJqxYJHVsYENy/NUbxzYQ0x0zn67guLno2ErCkLs4QxrGewDErA0Nc2/koY/ fUkf4DL0tLM9xXjJwz1WxnGa3Ek8zgvVBRgwRFBXaUj4NSxZzGAjI611ttvhpxKONhqBiZrQdspd U9K4toVFrYGlMu+JzmkLbFBBtmx7rxI80QAe0+jVjM+ZKyuDLIF8xY/DfVKlFU8vOnW8ZxLhLgFF SAxfXZPLp2WtsV4zxjGNt/AXBw3sNpPMXiKVJe6pkaKeRyMdk3SXHdccHLJkJmTIPVa0wf3eulSp a7iTzcoo45tDnNTDZ4MyzDaXbJT5dRhaXrLkODONgM8DA70jYd4bbTFAzmasRuitgavTEUTb5F8P IlV1vt5LN6VphwNBpORdj5CgjK1L1NLa0BzxcEOJeg/cJQlsLmQc68RrUNLysT4zbZF4Yf+LuSKc KEg3WmIagA== --===============9070395711080246390==--