From: Ole John Aske Date: January 11 2011 9:00am Subject: bzr commit into mysql-5.1 branch (ole.john.aske:3534) Bug#58490 List-Archive: http://lists.mysql.com/commits/128398 X-Bug: 58490 Message-Id: <20110111090007.4C241223@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============0124565784101733580==" --===============0124565784101733580== 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:nirbhay.choubey@stripped 3534 Ole John Aske 2011-01-11 Fix for bug#58490, 'Incorrect result in multi level OUTER JOIN in combination with IS NULL' As this bug is a duplicate of bug#49322, it also include tescases covering this bugreport Qualifying an OUTER JOIN with the condition 'WHERE IS NULL', where is declared as 'NOT NULL' causes the 'not_exists_optimize' to be enabled by the optimizer. In evaluate_join_record() the 'not_exists_optimize' caused 'NESTED_LOOP_NO_MORE_ROWS' to be returned immediately when a matching row was found. However, as the 'not_exists_optimize' is derived from 'JOIN_TAB::select_cond', the usual rules for condition guards also applies for 'not_exist_optimize'. It is therefore incorrect to check 'not_exists_optimize' without ensuring that all guards protecting it is 'open'. This fix uses the fact that 'not_exists_optimize' is derived from a 'is_null' condition term in 'tab->select_cond'. Futhrermore, 'is_null' will evaluate to 'false' for any 'non-null' rows once all guards protecting the is_null is open. We can use this knowledge as an implicit guard check for the 'not_exists_optimize' by moving 'if (...not_exists_optimize)' inside the handling of 'select_cond==false'. It will then not take effect before its guards are open. We also add an assert which requires that a 'not_exists_optimize' always comes together with a select_cond. (containing 'is_null'). modified: mysql-test/r/join_outer.result mysql-test/t/join_outer.test sql/sql_select.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 2011-01-11 09:00:00 +0000 @@ -1427,4 +1427,146 @@ 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#58490: Incorrect result in multi level OUTER JOIN +# in combination with IS NULL +# +CREATE TABLE t1 (i INT NOT NULL); +INSERT INTO t1 VALUES (0), (2),(3),(4); +CREATE TABLE t2 (i INT NOT NULL); +INSERT INTO t2 VALUES (0),(1), (3),(4); +CREATE TABLE t3 (i INT NOT NULL); +INSERT INTO t3 VALUES (0),(1),(2), (4); +CREATE TABLE t4 (i INT NOT NULL); +INSERT INTO t4 VALUES (0),(1),(2),(3) ; +SELECT * FROM +t1 LEFT JOIN +( t2 LEFT JOIN +( t3 LEFT JOIN +t4 +ON t4.i = t3.i +) +ON t3.i = t2.i +) +ON t2.i = t1.i +; +i i i i +0 0 0 0 +2 NULL NULL NULL +3 3 NULL NULL +4 4 4 NULL +SELECT * FROM +t1 LEFT JOIN +( t2 LEFT JOIN +( t3 LEFT JOIN +t4 +ON t4.i = t3.i +) +ON t3.i = t2.i +) +ON t2.i = t1.i +WHERE t4.i IS NULL; +i i i i +2 NULL NULL NULL +3 3 NULL NULL +4 4 4 NULL +SELECT * FROM +t1 LEFT JOIN +( ( t2 LEFT JOIN +t3 +ON t3.i = t2.i +) +) +ON t2.i = t1.i +WHERE t3.i IS NULL; +i i i +2 NULL NULL +3 3 NULL +SELECT * FROM +t1 LEFT JOIN +( ( t2 LEFT JOIN +t3 +ON t3.i = t2.i +) +JOIN t4 +ON t4.i=t2.i +) +ON t2.i = t1.i +WHERE t3.i IS NULL; +i i i i +2 NULL NULL NULL +3 3 NULL 3 +4 NULL NULL NULL +SELECT * FROM +t1 LEFT JOIN +( ( t2 LEFT JOIN +t3 +ON t3.i = t2.i +) +JOIN (t4 AS t4a JOIN t4 AS t4b ON t4a.i=t4b.i) +ON t4a.i=t2.i +) +ON t2.i = t1.i +WHERE t3.i IS NULL; +i i i i i +2 NULL NULL NULL NULL +3 3 NULL 3 3 +4 NULL NULL NULL NULL +SELECT * FROM +t1 LEFT JOIN +( ( t2 LEFT JOIN +t3 +ON t3.i = t2.i +) +JOIN (t4 AS t4a, t4 AS t4b) +ON t4a.i=t2.i +) +ON t2.i = t1.i +WHERE t3.i IS NULL; +i i i i i +2 NULL NULL NULL NULL +3 3 NULL 3 0 +3 3 NULL 3 1 +3 3 NULL 3 2 +3 3 NULL 3 3 +4 NULL NULL NULL NULL +DROP TABLE t1,t2,t3,t4; +# +# Bug#49322(Duplicate): Server is adding extra NULL row +# on processing a WHERE clause +# +CREATE TABLE h (pk INT NOT NULL, col_int_key INT); +INSERT INTO h VALUES (1,NULL),(4,2),(5,2),(3,4),(2,8); +CREATE TABLE m (pk INT NOT NULL, col_int_key INT); +INSERT INTO m VALUES (1,2),(2,7),(3,5),(4,7),(5,5),(6,NULL),(7,NULL),(8,9); +CREATE TABLE k (pk INT NOT NULL, col_int_key INT); +INSERT INTO k VALUES (1,9),(2,2),(3,5),(4,2),(5,7),(6,0),(7,5); +SELECT TABLE1.pk FROM k TABLE1 +RIGHT JOIN h TABLE2 ON TABLE1.col_int_key=TABLE2.col_int_key +RIGHT JOIN m TABLE4 ON TABLE2.col_int_key=TABLE4.col_int_key; +pk +2 +4 +2 +4 +NULL +NULL +NULL +NULL +NULL +NULL +NULL +SELECT TABLE1.pk FROM k TABLE1 +RIGHT JOIN h TABLE2 ON TABLE1.col_int_key=TABLE2.col_int_key +RIGHT JOIN m TABLE4 ON TABLE2.col_int_key=TABLE4.col_int_key +WHERE TABLE1.pk IS NULL; +pk +NULL +NULL +NULL +NULL +NULL +NULL +NULL +DROP TABLE h,m,k; 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-11 09:00:00 +0000 @@ -1010,4 +1010,128 @@ GROUP BY t2.f1, t2.f2; DROP TABLE t1,t2; + +--echo # +--echo # Bug#58490: Incorrect result in multi level OUTER JOIN +--echo # in combination with IS NULL +--echo # + +CREATE TABLE t1 (i INT NOT NULL); +INSERT INTO t1 VALUES (0), (2),(3),(4); +CREATE TABLE t2 (i INT NOT NULL); +INSERT INTO t2 VALUES (0),(1), (3),(4); +CREATE TABLE t3 (i INT NOT NULL); +INSERT INTO t3 VALUES (0),(1),(2), (4); +CREATE TABLE t4 (i INT NOT NULL); +INSERT INTO t4 VALUES (0),(1),(2),(3) ; + +SELECT * FROM + t1 LEFT JOIN + ( t2 LEFT JOIN + ( t3 LEFT JOIN + t4 + ON t4.i = t3.i + ) + ON t3.i = t2.i + ) + ON t2.i = t1.i + ; + +SELECT * FROM + t1 LEFT JOIN + ( t2 LEFT JOIN + ( t3 LEFT JOIN + t4 + ON t4.i = t3.i + ) + ON t3.i = t2.i + ) + ON t2.i = t1.i + WHERE t4.i IS NULL; + + +# Most simplified testcase to reproduce the bug. +# (Has to be at least a two level nested outer join) +SELECT * FROM + t1 LEFT JOIN + ( ( t2 LEFT JOIN + t3 + ON t3.i = t2.i + ) + ) + ON t2.i = t1.i + WHERE t3.i IS NULL; + + +# Extended testing: +# We then add some equi-join inside the query above: +# (There Used to be some problems here with first +# proposed patch for this bug) +SELECT * FROM + t1 LEFT JOIN + ( ( t2 LEFT JOIN + t3 + ON t3.i = t2.i + ) + JOIN t4 + ON t4.i=t2.i + ) + ON t2.i = t1.i + WHERE t3.i IS NULL; + +SELECT * FROM + t1 LEFT JOIN + ( ( t2 LEFT JOIN + t3 + ON t3.i = t2.i + ) + JOIN (t4 AS t4a JOIN t4 AS t4b ON t4a.i=t4b.i) + ON t4a.i=t2.i + ) + ON t2.i = t1.i + WHERE t3.i IS NULL; + +SELECT * FROM + t1 LEFT JOIN + ( ( t2 LEFT JOIN + t3 + ON t3.i = t2.i + ) + JOIN (t4 AS t4a, t4 AS t4b) + ON t4a.i=t2.i + ) + ON t2.i = t1.i + WHERE t3.i IS NULL; + + +DROP TABLE t1,t2,t3,t4; + +## Bug#49322 & bug#58490 are duplicates. However, we include testcases +## for both. +--echo # +--echo # Bug#49322(Duplicate): Server is adding extra NULL row +--echo # on processing a WHERE clause +--echo # + +CREATE TABLE h (pk INT NOT NULL, col_int_key INT); +INSERT INTO h VALUES (1,NULL),(4,2),(5,2),(3,4),(2,8); + +CREATE TABLE m (pk INT NOT NULL, col_int_key INT); +INSERT INTO m VALUES (1,2),(2,7),(3,5),(4,7),(5,5),(6,NULL),(7,NULL),(8,9); +CREATE TABLE k (pk INT NOT NULL, col_int_key INT); +INSERT INTO k VALUES (1,9),(2,2),(3,5),(4,2),(5,7),(6,0),(7,5); + +# Baseline query wo/ 'WHERE ... IS NULL' - was correct +SELECT TABLE1.pk FROM k TABLE1 +RIGHT JOIN h TABLE2 ON TABLE1.col_int_key=TABLE2.col_int_key +RIGHT JOIN m TABLE4 ON TABLE2.col_int_key=TABLE4.col_int_key; + +# Adding 'WHERE ... IS NULL' -> incorrect result +SELECT TABLE1.pk FROM k TABLE1 +RIGHT JOIN h TABLE2 ON TABLE1.col_int_key=TABLE2.col_int_key +RIGHT JOIN m TABLE4 ON TABLE2.col_int_key=TABLE4.col_int_key +WHERE TABLE1.pk IS NULL; + +DROP TABLE h,m,k; + --echo End of 5.1 tests === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-12-28 23:47:05 +0000 +++ b/sql/sql_select.cc 2011-01-11 09:00:00 +0000 @@ -11500,17 +11500,40 @@ evaluate_join_record(JOIN *join, JOIN_TA first_unmatched->found= 1; for (JOIN_TAB *tab= first_unmatched; tab <= join_tab; tab++) { - if (tab->table->reginfo.not_exists_optimize) - return NESTED_LOOP_NO_MORE_ROWS; /* Check all predicates that has just been activated. */ /* Actually all predicates non-guarded by first_unmatched->found will be re-evaluated again. It could be fixed, but, probably, it's not worth doing now. */ + /* + not_exists_optimize has been created from a + select_cond containing 'is_null'. This 'is_null' + condition is still present on any 'tab' with + 'not_exists_optimize'. Furthermore, the usual rules + for condition guards also applies for + 'not_exists_optimize' -> When 'is_null==false' we + know all cond. guards are open and we can apply + the 'not_exists_optimize'. + */ + DBUG_ASSERT(!(tab->table->reginfo.not_exists_optimize && + !tab->select_cond)); + if (tab->select_cond && !tab->select_cond->val_int()) { /* The condition attached to table tab is false */ + + if (tab->table->reginfo.not_exists_optimize) + { + /* + When not_exists_optimize is set: No need to further + explore more records of 'tab' for this partial result. + Any found 'tab' matches are known to evaluate to 'false'. + Returning .._NO_MORE_ROWS will skip rem. 'tab' records. + */ + return NESTED_LOOP_NO_MORE_ROWS; + } + if (tab == join_tab) found= 0; else --===============0124565784101733580== 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\ # dh3r0ik30u9mh6pg # target_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\ # /mysql-5.1/ # testament_sha1: bc1117917c4b4e2d880be9b0b6dc0edf7f39a9ad # timestamp: 2011-01-11 10:00:07 +0100 # base_revision_id: nirbhay.choubey@stripped\ # kl5tqdbqh70k9cu1 # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWc/bG9EACQh/gGVQIAB59/// f/f/gL////pgD19XqqX1n31NvvD172yvtgKeX156So2p2yfTu7hkCW29W22vsyctBoiCnkan5Caj 9I9U8p6j0TIABoBoPUNGgABKSaaZqeqejSniU9JkGgA9JkYgNAAAyDTQaNENRT9KnjSn5Q9FNAZM nqDIAGgAAAASFBJom00jT1NIYmSaeqMh4jFPQNT9KYmjQ9QNARSIRNo0E9RHgoGhkZG1MmgNAAAA 2UEihCYKejJpoTAmU00xNTEnqeSNPU0AyDEA3ZhYiGJCXFH/tkCsfbfoi3LWflEIKjlx60JzszZL B7/VdZgRjGCUm/8ZZoV+ODv/MM0X9EgPxYVjMCgISGEijBoYmj6Cr3XfThb78DkMhZtjq/L+3u2W mYDQi9ipuy2ZGG3D48Iu9mKnZwomM7Faod/JC+7VXb0EbkFCptwEnMoissgKIrAZFMmqDgFkSQyg MXxblLmSIYaeXjVG9ijFb6Tv1i5t9LzV31fmrb/NdI5+Itgr79+cEAJiusYzSxoaDIIIGyRi30RB qMY6OEOdIMxCNBgYiDp9YFG+y/r36qCEuUCV/Wk08YZvxOnTNTeH6AYwwKPvEtJm6mJtNhHoaDqc ArsF2C/AF0X3Gz5L8V0TbbBtadwZ+fqvRnO+DDnwLUtQh36rWlXHHv3vd1mILWBlsheIHphmagJD pHpC52k46mqFjiKC4l+bAbFuGEYT5Cm7gHQM0BMUM9+IlyKRqDYkwVlAYBiK1HuPzVVBkf1GJjFg HcFoRMgiHQNWOJoMS8nKyk0mCMdc/Dmd+Md3gzjjleGO/HC9OJbAvUdJWbJGYErYPFyM01rawIht YYRkj1ZKLKFjkzsSbpNZeVhpkluf+fqhCrMruRp6Ju502vG8B8wRnErY3ToEbSVgZQ+nAPeM1KUO MGMw5AMDCEu4l0S8Il2qWd6bqLg+l0bsSoe7LYtSt6wU7cHpd/J9GYLYweIvWBg0n3LXkbgmRUe4 EceEEePBMgUiQSJBIFIBjGbSRAIjPMGJhZq2qqt4R7tG2e4tMCZZHMNJkImxUNgK3K74P0XHo4Ii 2tMiEW4SglcXo5MfPilXvZ44xtX32EtDlJBwyIqxUDBkXncbajjbKgfPQ+Ts7ZsbTpNzGSeNykle rx1UZXVsGgbR74RNkaRAYmMTPVeiDsExMOIxDGIYxI9JQSPB62jbpEtf3NIrXm3aHXjlxPpMiCgf UUPWMD4GIdB2hUqMZCRWHYY2oYsOYTUJICHoNIJ7Pa2jQJPu2iU1kYwiCF9xwMjUZhwFQ4FSpQhY 5hmBc9p6CpcxMN4HrPAMwB1Ji85BadBHoBvKZHKHd9gYLmY4U7LzHNGTLqmF7FTv24w4n4QUYjEc CnpZ1NUQVAzryB2B1hA8ZcGkeW5VcBiuJoIQUBQFQDbQ2bjnFthmSPiWZhZC/YS7+/BZ7qltWAjm aOUDrShXNEiGbWB4kpqUjSxERGoKDkSF8p5rFB7EmhMFAZeweQ7ioSxXVVZlliozRqHERMRKxRTK iJSpYGsRWswiu+4XxDgSqgxzF2wYKQgGBxc1gWI3SXPC/CgTuG9wWQqiGItMALXlkmGE3nPHhbZK Y9Hci6pvGfZZmSqCfrP1ga/EyzXO1du2B2LOf84YEMndOKBVZw38i3GeIst5HQTRBbwZMcDkMGBC AokgGJLUCNXA3hWjJPVf2t7oXDOxGfSZphrtYrC08SxH+5fQsovB61AI3WyRkgaCYnYuNuQKJiMB VYF5aIo4aQ2k+i2eRwql6HTA5ku0jTZXaMnhu9XoIt3HoX4raNM2g1AyYHIGcztBRCIGzOcmtAxX skuG0N+rYH6QnKDmFgLbmyiKV5WdDT9OotoBoKdWSi7M6He+B5ibe2y5ZUNXGcbnQGJrJGS+17KC uBZNxJ51kZU6K9rcIPENJJZK252BKZRI99IdtlOgtN0s+pFWc3FZComGeR5dRGvQcBagyRhYpPOd G2sjwQguOL8ZNkTHGqmu2SEShMYyHEokTAz2L8zMgKC267R5XiXhgwoaClC2I6ymmcJzCk3Z5uxX hYQ1aGxECBoKImyQZ40lB23lQ+KWXA7c1zMlJFAqBC9W7iUKt0SrcEikAzabVG86xIJ8VCiSHNpi RoCoVNL+C029HQ5LaTfKaZJEnN4sLkUEwhFyWuJg3XQss0toDynfA08zd4SOOQ6ytTZefIiPQLjA 2cosrSLrJhg+6I/mimEqJQA+uBD1H5LYJMPEAMBjGIBiV6QBMgF0gEg/iBJXWL8C430C8C78j/AV bEDoAsw1bQH0AGYsxAlAhKD3wgEbYhtR5iAXoBC2gFAS2CAWhoJYgEVchAUAaBgJgwTxo2JVVABk esCuIBhIhFkviGBGiNwXWgOEAoJYC/0EIKDI2MZmVDkP7LRJWCqi0sNzIidAs2YGsA0hkalaIFxC FoGVoAKxc1FRYXwbBb4LnAui2K0hTMcFwEOAIAgLwtZlCoXPVZaLnpEBwBSrnaiAmDcUyA92JJfd +ZiqfQSPBrmvaJDgJDF7wRGVFHP42VUsjL6D7SgUdALgeo+A8dll51Ae/r+oiXWdh97QrbJBZ86S U4qV8f2p5lSDL39ae1YTwY8ZwZwGuc+/alQKcoiF/iT5tfazqSK6Ye5lJiy+wY78KV3r3Hw3FjE2 LdlbqHvCDBn3wytCAnpsDRQO4Pi6HlsAsBicmlYJyUXhpPptEzVcIDuBW9GXDOs+BcfzKTqU9wRM c6zFGJl3ZZHQhkd2HE354vMoYgblWF5LFEK5YVBMR+g0BA4exEbXxf0PFPUjxcSn4HMXS2tIf81N XXDVSJUYQUXvTVW7WVGp6iuWOZFJtqRScimeNCUJHdzN50WB8mcSkyqlAvMfEQ+oa1QibEhrAq5l R7NviP2lKfDYcuXZvUtcziWT2ItsULFOFQZrnaBpFyivMsCwUbqwk02NjZMSLUJU7x0Llo4nnUSD YWz9x41GY8PqqWpa0jccNMCGCjaLzuljsOQEjbJFTCdAgE6UqDl6VD1MCFZ0bNiFqTONs10UyOzB AgwXAGLtHR8oOYRpr4dIdl5Fnd4YcrW1NKrwoXkPJQXGSoefFJzEqTlb4xAucS1BqONVgk7aw6Aq MVcATwI8uhKg6khegB5UpTmIKkYjcrfWZx8Rbzb09RCB7JETVAt+z7W955Ziio8EdgV8Ye9alaig 3qAve1mGeTQ3WeJV/csKRHRigEBDW2NRfo4MU+yJMeZ34+VmhLy5kBpieA4EIdgjiI9yvJvWBQUL uTEv69Nynp75GT8dVC1cyay9k2aAqpE5DA5qcAS/ITE0vKwaYxEBL2AMRUGaQ40AnTl5B7Yw4Yea WkhvmKfDrWEjqIwKEGnMzeQSDaWZyCNjTAmUA1iry8zstwVZ8ZFua3GLfUrt1CRt8OU1AFotFZct 2qNgNNMbYbBYC5gEeL2AYoh0nMT99cgC5PLr6Gc5pHOz5QGzt6eZ7DTQQBMUC88hEPUv6F/c0jDl aJH12F5EU+deFx8ZXF6yQ32JgZEQmF7zuU2+a4aj4iRmXgZKg+lq8iSFgkUFnrQW7nzlQ2gb77Ia mXiYv1MvUCAZG6Ki2nzmR+uvmx23uZAPz9ZAC24iqWHF6RlS4w40HyESggLXNnPfXWOqoKCTLJ85 Qp+JNU6DZZa6SIkjexw7lUcpk5RnIIoiC4BtY73Pq6XjxSx3KKmyBtWytWoyondorTLCroCMWV3L ke3e/HrTERE6Ld6FVZ6jOlASLiCg0d6zsLiSxnmv6cwGguMLGj0TpJ5sSbjT8uuCdkU+pxhDp5ZF KYBWMOwZhY0xo2NHCQL2s86aOLIGwAlaJrRtG6zOHdZMx4Tq1MvnjQL5ZV3tDeIswihfk24mt/Ah OFedBr4tNDPAysHaZop8wjpO8PmJl5SeIVZD+cPB2WqefsCe6FrmoQGJp2hkrrV7urFFZYYi/KBh lJk5oXUJDXfMpUoTk2HMY2aG3gSDbHGZ69h4hXv4FaIM4cvJvoHFP8XllAKwtygGdMvivnWGPFZo hSFIux1UmiESYijZWHsfyy+CJKwlF65Aqpyca3Y2WLOeij1hVUbJ1ooigC6+pRRUbtBVT6YpwbPT NPetaCTeMWSD4VK47SbMYCmWVSRN9eK1s1lRiE57l7m23d2wgQMLGdYbIrQxUvSKKSxIgegS7Kcb s5YouqNULJ+u2q4YbZjfVZjNMKO6yA7hMAPaNHgmJpoX5hUIBUYCoOg16AYlUYlY+zhniLOAVoF2 H1CfFH52nMnLZ1UBmdI4uOpLfPJ+S0kED1pUxS6NI2Xwg0TVUL4VU0Fvad6AxI1IobnUL45oCodt u7zaTvUq4JpPG75tWFV5sLhk5xKzZqXcdt3yRycRyfFhghkVlJhLQNWAC1pJBYWfgON74rN+7aQJ jWxqkCbCYtM5MbE8iASWghXP61KC5j9p799ge41NUOsn1DSKFpylMjDHDYAvvZJidxAVxDYQKlIh cQQri+4ZPsXaSjcARGizNZgdDqf/F3JFOFCQz9sb0Q== --===============0124565784101733580==--