From: Ole John Aske Date: January 13 2011 8:33am Subject: bzr commit into mysql-5.5 branch (ole.john.aske:3234) Bug#57034 List-Archive: http://lists.mysql.com/commits/128594 X-Bug: 57034 Message-Id: <20110113083334.33C66223@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============2432269348844214577==" --===============2432269348844214577== 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:martin.hansson@stripped 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 === 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 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; } } --===============2432269348844214577== 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\ # q6jqxffv9d1syew0 # target_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\ # /mysql-5.5/ # testament_sha1: 8f49f4efb7ca3d06b2f503262eab5df5d1a410e8 # timestamp: 2011-01-13 09:33:34 +0100 # base_revision_id: martin.hansson@stripped\ # bu92czxpaw9nht9k # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWd18GG8ABb/fgFCyWPf//3// /+D////0YAzbqvsR6e7UoZOuzT3r3g9sAs9we7l2taPet69de97vVOvhJKZAJo9VP1GmaJtDSMmk wgGQaA00aAABJRMkwGgp5NQxE0aep6E0aaHqBoGQaDQYgSiGjUyqfpT9KeRNozVNMgGjIBkGhoNA Gg0BIUQVPBpNU/VPT0NGqNvUTCTTTEB6IxNNGgYmmDmE0BoDRowjQYjTEyYmgwjQMgGTAKoiAhoB MmmkyZNMiT1HlPU000aBoYQxGNT1OlQFOY73n5OZthsVnRj5DxTTqa6vPSS/bl5eAkdk0Cm3eUol kYaq5+rgdhyQ/zLvYnv0dHRPn5to0YDhjBtV+aTLAwLc98m1kzJmDLOlGKu6U+O7jkC7brFL7RKi quDLRtzMLudqtW0PeCheWt0r2GeXNt67y413iqmagFVRyUxYoke71Q6ekZXnJnnLVEC43Bz6nFaV WLXrsGskjM4JHSmEdqHo+0/sMwQO6MK3fHQQ9Iez+6h9ai502xttHeuP9FtN+2rL5NrLJwt0NshV 2Ul9K94Ke/v9Xh1zQDDjEwZ0npNWUgukUDcGZmwLNLfWZW2m2/IWwMDu8C41mYU/5691iP8xKLFv e8TaUJUazSvb0kbrWqNMUsLzO8zm0vtMkSleeDzsVGhoaF59psVlMSmUt7a3PHpcFubhdf5uzJqc hT4DoJVyyFGed9D+8o9AcJZAz6Jqbm+5qaFLOBVE38dl30WLYv/Kl2EW02l7Z5RcVu28nkdqj6jA oSSq2j1I9JQlKiLTuS0I1eqbNfU1cY3Wbt3rJQI3qUvG908u2iTyzHPzeuK4LqNVgnsJbFyJkwMz bba5UjRZ4G+A+8uSs5tLhtAqKowqwVZE4cjH2jIlmOyeea9HJct1V63lWvtLGzUZr6ZW8t2lNRqJ qMpG26Bx5x5gzFBkLhjCh8pLFCVjm6DIP2J3T13x/RC6r53pr70YO3D92SHlbs9Dqryz9WD0aYhZ vMjiSJJMDBdKFniiRjaWhmEwhw3q6+9ZlzHPaIKLtVCGJRKKNiFbbY4x9LpaTMxK2L13IvuSxiYR IUJeB+KtYxdTPCeN8SrVpsi39qsUlCi+jAuRWKTqs3V/i88dXPdEaZ4JsZ1GZisLSXpFXFiqWMG+ MMCwSSSey5xvjNCM6jpmKXF7NJ3SclQnQ5XITspNPejAvNfdiyunQvRS4gsiGHKvkqfeCfeY36Z7 NhcxoJYNu47RmIoitaitaS2TNtWo3OXwuQ7e6c0udNdVeVaFUlKO3uO4qLTgQti6XVQnrU8mfU3q Y8tO3fxX90ZlkR3zdjo80itHko/JgdeotTyLzmQpu1mcy0RgqYIVgxpJe4nWoquQ4IVeKY+nbZcy oq8tGhosaDLQ18/fs4O+fRrNWvCmikwGm9IHCyjMoy9unBLlYOHYuS7JEmmQLjAwz3j3FfZ23YlT FXXjCqd3FtXG+4uLLN6cIJTzOMuCKUYKgqKRlsphuxSDji4H0ajPjSyZjFE1hoievopfvarMxMwx So8fHhmW4Nu2FZs6uixknBn2aBHGXmXaoheToaOlwVZ67YEY+ssvqlMxC5QCh6GwBxGQHmNJVKaR 5NXbJAY4koBymVQ6qJzBDlzlSuijFC01FwLJBckpdGtiY1i0DIYps3FS+SK+4lmVBNGVdxq7TSIg t9c6qDBVSnX0x2YjWmV+ljLoTRHWtaHQpEIhOiQpJBWhCYXiTR3WGejQ8EbiNfnCrskKjCrLCi8I Yb7EtcKtjW5caX2nUfQUPDlHq6pdbGM0Q+OCNqUPmbH5iw2n4H9RRrH4BQsX06URoViYmZoVQfyH 3oKjCyIWj+pVCSqCqJyfUSj8yWsSVSSSuKDPVD/ASjWhYYGswhqQtGYSZ35EPm0hVGJRHfXU/FEn 4iN3YI6RViVBedKS/wPDaJAJQQ8nDA9AO/Vq1oQ2222I2CA/oOIQaxFRdsQLAI0IlJvFpL7CYhUk lEosJRYWlysCqYlI4xnSsPrJIlE2OY+EDihYvSlM0RuVi06skfF2NC7GTOZREdZFUbh3IdKNxcJE iX3MEYoaxsNDOaDiWDzxlMwlsWEYC5EjIwDIcIuWiho+JYLEC0LjGKFBKVySWDQlKJQWnqPnImPL 2x4xZAfUUSkSlKSRMQ7qIWot+v76CL4vJiDvMBIZ4BpJsbwSMMSKQkbhFIRSEmaHYfiUAayrzHwO SxzAA9xZQfhgA/Xqvh1PjF8dYff5DRJ+2oZMEXqI5+a06HggmBBcg3FF0pDSokEopgk9O447R6Ls xadfZ2v6xaXsO0FriNMdjvjw6ZjtyIHdXl6OMdqOeYie+Xebne2cXFi0sjU9Eeam0louzkGEdCFt lIqqG2Py/ubmR9iPnUHwsmalRWszQsRJKYF9p8GkaXJpiGXpVjXctiUlKeD2RZSNPOivFDBc6nN3 Z8nNlDtvXNMM7m7lLb80R49bmRpWOjVe26HpOzfLCc7ti1rzidrggBgCh257JhBO42lUi0N4d0tI S5aACarZSISz79/hM0l0tvUbpApqmVikSI6GK1taeCGTCsCvRv8QhyeH4lrcFTs+dWOp4WxavtO1 0dL5Cx9JZNhZr/HEakoaLElXHhUIMtKZFnA4J5J0os0Trbw6z5A8LK8lOhwSIbElLU4SceTfjFyi IEaVoCkYklJu8tMnWPJjbGHh7bHbbVYX4nvQ49Tv3H/opFI0rDS4KmODY1AZS6AibWwqEjHloSop dxNzaqduO0gdwvihBmsM9YzxWZwXvr7SLkNWVzZHycOkZ91hiO2KtI2WSKbMlHZFglP27wNiQcI0 QtJi1LAVB0IlSNNdBONuUREpDy7urDBG2IND1sVtYl15BUUj+jdHuwm3c9pgWPdIdGLj4dSRCAzG Bc4jPyHdB4cXepUbfemIVpw7Pl0c4lMHeiVZIolKYhXhuqjF53j0Zfq5N5VHQ5+Fm9MfJsQiZCVq KBZ7X+jUviOz+GmKWBhEKxUExNuLbmZTu6xQYG8W0EuvHqYZKNZwBmchE2lxLkW+wXAoISXSLja7 fzg7hAKuNeNul7b1SLqkAokarFTggXYlUqC0q0NFAGhpGyyXMXKpdEGx2MXUhN/DubK5RoRREUTG yEhnpFJhadflbxaY1wWZRFRjt22BkcNiR9s4IyCM4hL9nCJjX7rA9qWMNcOcT7LEdulduPiGxFa0 OVIlA2Zo1FXK/XfNArJKs+REiCfCyZDeRdJGGsllqqtLnr9z2u8k+gtZeLffTGw114NDd6kOPDae rL3HFuSDR2zERsViKOEAm1oICWa4sEjdgWEG9ZfMPMOFsGiHKOPOpgwnTvVjRZ+jcbL6ZUAylMiR IIKm5jtSG5KERfaKnhWdIwL9dtjaz4WcVleA9gZrlKS3sCcTlZakctYWM3CRbZqWpjGMZdDeUVjB BJhMVoOVyXE7D4hFQSirYDAmJvAzqal1sUUdFbZ6cyIeulA+97u+Mr4zYInGoY4gFVaW+va2NMGz 6twuI51vF4lkFLiBqfRZTj1dF0zKMUvYlKZmeldGBuxk5scG5DPFu6mMo0ZMTE3+G7Zbv9K69Ryx bGhNpoPHIrT0FzGmpXWjr3iTSHAzrMvFcot0o/TVZ36meqFzn1MckyJemR2VTJ2JmZiUUTSZ55o6 Td7syJjZ5d4cIuWEObZak4u0GQrO4Qq4zERYsZEudiWwyB20xKTSs633FYRG5DexlV6I71d9G1Fc 93K5PkHmF3JFOFCQ3XwYbw== --===============2432269348844214577==--