From: Ole John Aske Date: February 1 2011 2:19pm Subject: bzr commit into mysql-5.5 branch (ole.john.aske:3294) Bug#58490 List-Archive: http://lists.mysql.com/commits/130129 X-Bug: 58490 Message-Id: <20110201141938.74808223@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============3818219925593113213==" --===============3818219925593113213== 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:ole.john.aske@stripped 3294 Ole John Aske 2011-02-01 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 includes test cases 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' predicate term in 'tab->select_cond'. Furthermore, '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 2011-01-13 08:33:30 +0000 +++ b/mysql-test/r/join_outer.result 2011-02-01 14:19:34 +0000 @@ -1502,4 +1502,146 @@ WHERE 7; col_datetime_key NULL DROP TABLE BB; +# +# 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 2011-01-13 08:33:30 +0000 +++ b/mysql-test/t/join_outer.test 2011-02-01 14:19:34 +0000 @@ -1092,4 +1092,128 @@ FROM BB table1 RIGHT JOIN BB table2 DROP TABLE BB; + +--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 2011-02-01 12:23:28 +0000 +++ b/sql/sql_select.cc 2011-02-01 14:19:34 +0000 @@ -11767,17 +11767,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' + predicate 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 rows of 'tab' for this partial result. + Any found 'tab' matches are known to evaluate to 'false'. + Returning .._NO_MORE_ROWS will skip rem. 'tab' rows. + */ + return NESTED_LOOP_NO_MORE_ROWS; + } + if (tab == join_tab) found= 0; else --===============3818219925593113213== 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\ # ppx2z4tn6vv0u8q9 # target_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\ # /mysql-5.5/ # testament_sha1: 6d46ced5b2e4afe9b63991e77fe46e7dc9be2cf2 # timestamp: 2011-02-01 15:19:38 +0100 # source_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\ # /mysql-5.1/ # base_revision_id: ole.john.aske@stripped\ # hdrlhd5cgbxq3zgd # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWdiNnDEACGl/gHVwIAB59/// f/f/gL////pgDxy2+HRd6ObxnFoaAQF7lBilFd7Z3d6cvQPQA66aPWsJJJTJsmRPU9TTE0aB6j1N GhkBoAANAaaGmglITBNNJg0gAmpqbamU8SYjQNAAAAANJpkJop6mmT1MmIaNNpDQAAANAAAAIkiI Rqm2psmlPTU9NNT9UeUHtSeoeoHqNHqZPUGgZBkHMJoyNDQyGEaGQ00aADEZMgGEAwCRIgCZAAKb TSZqNBT0GTEaJ5DKeppoaA0ZhE1vE9SRScYUPGUWKZUGrFA2hVdyhsT/saNBMNVSWgXybOjBmEP2 /z04+aZfegZz6R8MphYJ2vh9YY780HlitZsWOJxQitRmeJ9EjDwnnMhhMh8oktt3DOfoyOAoXZuZ p/f8vHXMgBeBgpGaLZWj5vQ2T0cGa/yarLytGS4c/v1snf69GjwFSVQ1nky2FmLIWOMCkLAGSTKL Q5hktKBlYWvg3FdRiiCvd4eFqM7FDFRgrRaPS6M1Gl1R2yo/zbaP7OYuAtOnlkSSJtJJECtQC2DA kIN/eBn5VZOn7ZqiJ9kE8Olhl64r/Y7u6uzOHrBjDQVfoJaSvuNR1HElzIzULAUYRUheoLSXaV+V YFspmZmBmWbaC5NZnyMm10dOpk5SlBGuuI75kMz53l6ywEqwOGLS9ALcHFUgQLkLaJcVQfVSqWk/ QTKRL9WA2J4kAPoSx5hpGbATFBlzWCXYWDUNiTBX1BIFQUSN8wqDJKp7DCYYUgShEDxVB4NYM6UL yhgOJETIxQpqdx8h0ozd2sqPS3AYvsh21P7lRGXUJZNRTcAsYHg22tcyaLQ9oglsSgpShckiHorC oVJKfW7QVBnBJ0/1mprV0KrUlOyat7mYHAPzFlw2uGtd5JHWK2PqxoVYGh45Wb1vtPQFxc2HRLXm APJ6oUvTxSN7LrX24ixissK0qZOt1Z2OYf1PZMINkHrNFlSmma6uWFM9JzFpIPzBM+sFIkC2Apkw mTCYKYDGM3kyAkM8wMTC+226HmFOeXZHqdWmDWyc4qmog5psDccTZLEShM1BhphCao2qZzRKSslS v038HXp0dXePn9zbWbrdpucw2gvObCEXolSRqVJDwgbbHPzbK8EjJvwlyVyS1qq/LyXKjfLukQkT topkKilBBhBh8eNkOYYMD7SCQgjGJH1EhI/J2tGTfCVvsaROejHgc7MOB1mBAoDeSPSMDiWBsPcE yYxkFZMO644UaLh3C1CQAb8RmCfP9ErLBJ9tYl3ePe14IJ7DItIFwZCMMioqGCxpiKHjj1nnKhxE x3gdDzBAA6xpgcg8QNw+4Ljizq7Q5GLcxDceDXGRsYrbAV33x23665lPsIUoNZ3OMcvVLIgMi4hQ HgCA3oDlTpINxiFFDQNA4BJBk4zxWjIZED1F1YtYvvJ93dnWXhauS8P5BShc2GtujZjtA8hPWVRs DoVhbwPDIuKQQxiICKAYlOIJ3nZKsouHwJkiy44CmpDKvpGSlWM/cRnyi9YdZBW4ErdIKCgGCYvX cWn4MSg+dh5TZoPIykAcoHqJqEIImohHCI16oBAFyXdmm1zlGcyJ1oToVMCIxD7sZF4O8KRWcO6V nd5yRSI85lJGK03EsVWQUa5i1nMVFV3GZPKcBLgDIOJzGDAiBSJgMSWYEaxLVXo6wgCwNkbMxu3l vAsNFTgLQrOdvBitJnlNhkP+M/iS9wjet6BpErdgOSgNLJ6XAuzeCg0DATKyk2B1GoNBSdZQIcjl qNtWFJdtovjGZv4/RZvPQf9DabRpmOtSGsVpyxIlhNyA6wZzNwLaIxoCgCkOTOjSWq7ZvmGWO0nI 8E5B9oc6hheCzsXil+5bt/8I06wNYm9xYZR4XnNoL8wnN90V4hu1GnroSLSQWEjp1iyHMZuLLJTo GlK3kx44iGWSpJyOm6TODVkMbEFolC/ee2cu8QiVqvs4iZg0wLokH5ERxOh5+ekwpawmo9RatK1a zkuGpDUWEhdRUSGuwVQoUC2SwaS4JorKPFLaEHClGFsdDyRaSaHUIS2FqKC4GC00EVGOZqYFYhMq UNwHx3PLerTfsI349yrqxx4Gw10x19yzl6TOyoKXrtovDFT3LBqkRrEFOyW4IiV1v1LVf2OpzXAo +6ihJE3QeZqpzJA0JZtBi2MlWmk6w58DoLgFm8qFsa64UCA5EIWISpgY/lFrfJiUSRh/CI+i+ES4 tAH10FHjPzd0WB7IBAIQggQXcoAsQF4wCg+0A5Jfwl4mSoUBACUrj9An40BiApU574HnAGwthAqC FQ7oIFSRCRqaBAekEfYALAu6IDrNYuYApv3lAsBAIAwIDM1pBbtgB7UWxAN2QGJKX6BgjecAvrQH eALC4j/YUUNEKkIQ0Fwzn7utXALtOvAcm+ieoWzYD1gHgDOqUlNiWUQaQNymAXCW+dJgXgsBK8Eu AFYlgkqAokLJUCDuBgMLRK43BSJcKcNIlwoEAsgUJLgqSBMG5JkD46ia8vpOBzdIp7seZ+EUlCkH yglQuNSvvwbrgYfQfIkEnICkD3+0sn8PafgYFR8sgFKZjfgfNoD35PWXFIP2e6ioVq9n5p1q0Gan +KeCqjFjsjW0cJ5s62C2+JRh+hXTm2Q5UC/Bk7IWrNDF3SEx0SnlXxwJlZhkn4B5QgYM/E2n4lp8 eqpzH8T4kyk0hIZYZlQvlOPZCVYM8ALRyZcczKeAynMmREdpadY+Amclx6ux6TSYhZZU7CRyAkym ImSQGBuDYYJ6iIFBr60qR6plmROHUW7jjMVztIbq0wVShKTCFJ6k1QWbtpYbRlBn7QsLERQoLaiK qOhWkD1KNbzemMVtYNrN3PEPKFErTDgHWAyAWLlicU2HBmiZP7b53eGvZfFDdBbySEyMHneRpGwi l5a5ujRkZfhMdZVaEmmxsbJki9JBVhrpPKbWmsdpFluvuimKUQDWNuWL0IyQmR1GTcfqAYXsStQN QIlGM0FWm9btsV6IX1nUdkqRVxxpvizIWQ26Q2aPgntf1RqE17nBxBsxqnRjMmQM7FtMbGFUclgV BmgGfBzgqhJN8PEyBbQbCVRai5G2wgI2ChiAKeUH25lcHcTF3nlAPJalUdQkZO8yj58/CLqIl5bd nTrk4tUCYyKd7mZnrZOREZQQ3zVeuqqoUPmknCwNWDNDQ32GQtqEdrExG+KykPEGbv62LjSeO/y3 4JbusgaYnkHBEYkeICZNdwVhAlopI+jpVQr8MtnC+QrMY4MhoELgVib3E/iQiSD1AsGDQEBesCCY hDfo1WArXwmmR22rfn8iWgjdQWdvVcEzOUoNFTPpISDaXYkBp2QmA5FhtEk3wJzwSR31E4TyYpXE lOgiGvycWvEmJfccNcXA00xthtFoF3AEup7gNKjz0mk+F0wDBPX9nJAcrf4QNm76zs9R5TRWQCYo Mw/kY8mgMtwkeSwgU+lp55eYwWlDdyYGkkFAsxPbO8al4xIqXcZ1Uffeu8mgKkBIWNqCjwPmTDqD ADNVfBYyqExdrKZgQBiZYUlgYHbLlXf30mIDx4EAFGJCmUDhFi4R0pJNAXOeB57LB2WAPCMEXTh5 OuBYseKZIoSmFJuFAiAI0WEMmoYgqQEXCGsR0JUXqFKqAsWE5tQyYnS1OV1U3IEXsnxPFtfd11yE SPGtnlLLTsMRQJGBCho8KxYYk1lPbfv2AbCpNJ7fdKgluyo1Kj5fqIXWJHgF+h5gaj0TLEwC4YYD NF7GMNzR1TEeVldPUyDaARoEfugNAqpHTUoUhdChP54oC48nX32HHDEVN+SwtfwIIWmepBZtaaGe 0mGKMdh0D0iZmKjvCvUP0x4nd0VPiEVeTFFeWMXVYgNZcIvDVee8RxzakvLXeYmc6GEmJqUKFxYi 03E0kccTWVzYqhwNscMz2cBUZCn3mE0GMGSmA3p/2e7dAWl+6F9IPKmZ5r7Lw1c1ZILRWCxduZ79 WwKakL0Z50VjkSqvRVnrqgu1n4b5IZIYqlx6JSspiDaUpWAFm/hjXWregRj8867NvE1d9t1GSRId zaFaaDYNgYiNTOtAG+5RDVpKygOPBPBSlKVl4RBBovZ2RG5rMxWPSKSS1EgJZPeWascpepO2UrZU J/XTZUFWSLKa6GM11SpWAHMTAD1DR+CYxiPnC4UBcgDYliO0ILcguB2atFgsoBegWB5xPkj9shzK TJSrRFbsHJy1pdVM35FpIQPalZJLwtI3Z4gaKKV91ldJk4nRAaiViqVXUe8ReLgwqFpVmki2aSyS RLKvGV4EzV8BD5j3UqimGOjhN7xTJhk4jIwLtECNPlYo3xp2c3JV/A9BBQbGNWAZ8hidBFRuVO0g KVnIJ5vcosPEeM7dgmnEJiaDNG54iuwiljGtJZ3jlEy36C5AcAaXzZQxPQQLQRQQs5MRnNxqzGXi rJeEPZYA8MhgpQUqxWJj/F3JFOFCQ2I2cMQ= --===============3818219925593113213==--