From: Ole John Aske Date: December 2 2010 12:09pm Subject: bzr commit into mysql-5.1 branch (ole.john.aske:3477) Bug#58490 List-Archive: http://lists.mysql.com/commits/125802 X-Bug: 58490 Message-Id: <20101202120953.E6A60222@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============6882364132835124418==" --===============6882364132835124418== 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:georgi.kodinov@stripped 3477 Ole John Aske 2010-12-02 Updated fix for bug#58490, 'Incorrect result in multi level OUTER JOIN in combination with IS NULL' After some more testing, and discussion with Roy L. which has also tested my fix on the next-mr branch the DBUG_ASSERT(rc==NESTED_LOOP_OK) a line ~11553 was removed. This was replaced by 'if (rc == NESTED_LOOP_NO_MORE_ROWS) found= false;'. Furthermore testcases for the duplicated bug#49332 has been added. --- original description --- Qualifying an OUTER JOIN with ' IS NULL' where is declared as 'NOT NULL' causes the 'not_exists_optimize' to be enabled. In evaluate_join_record() the 'not_exists_optimize' caused 'NESTED_LOOP_NO_MORE_ROWS' to be returned immediately when a matching row was found. This happened before all 'first_unmatched->found' had been properly evaluated for all embedding outer joins. This may cause incorrect calls to evaluate_null_complemented_join_record() after we had returned back to sub_select(). This fix ensures that evaluate_join_record() completes the itteration of the 'first_unmatched-loop', evaluates all conditions for the unmatched JOIN_TAB's and set 'first_unmatched->found' properly before possibly returning NESTED_LOOP_NO_MORE_ROWS iff 'not_exists_optimize' was in effect. 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 2010-12-02 12:09:47 +0000 @@ -1427,4 +1427,86 @@ 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 +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 2010-12-02 12:09:47 +0000 @@ -1010,4 +1010,73 @@ 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; + +DROP TABLE t1,t2,t3,t4; + +## Bug#49332 & 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-10-29 08:23:06 +0000 +++ b/sql/sql_select.cc 2010-12-02 12:09:47 +0000 @@ -11473,6 +11473,7 @@ evaluate_join_record(JOIN *join, JOIN_TA return NESTED_LOOP_ERROR; } + enum enum_nested_loop_state rc= NESTED_LOOP_OK; if (!select_cond || select_cond_result) { /* @@ -11496,7 +11497,8 @@ evaluate_join_record(JOIN *join, JOIN_TA for (JOIN_TAB *tab= first_unmatched; tab <= join_tab; tab++) { if (tab->table->reginfo.not_exists_optimize) - return NESTED_LOOP_NO_MORE_ROWS; + rc= NESTED_LOOP_NO_MORE_ROWS; + /* Check all predicates that has just been activated. */ /* Actually all predicates non-guarded by first_unmatched->found @@ -11515,7 +11517,7 @@ evaluate_join_record(JOIN *join, JOIN_TA not to the last table of the current nest level. */ join->return_tab= tab; - return NESTED_LOOP_OK; + return rc; } } } @@ -11530,6 +11532,16 @@ evaluate_join_record(JOIN *join, JOIN_TA } /* + Setting NESTED_LOOP_NO_MORE_ROWS (if not_exists_optimize) + also implies a 'not found' condition. However we could not + set this inside the loop above as it would prematurely + have terminated the 'first_unmatched' / 'first_unmatched->found' + calculations above. + */ + if (rc == NESTED_LOOP_NO_MORE_ROWS) + found= false; + + /* It was not just a return to lower loop level when one of the newly activated predicates is evaluated as false (See above join->return_tab= tab). @@ -11541,7 +11553,6 @@ evaluate_join_record(JOIN *join, JOIN_TA if (found) { - enum enum_nested_loop_state rc; /* A match from join_tab is found for the current partial join. */ rc= (*join_tab->next_select)(join, join_tab+1, 0); if (rc != NESTED_LOOP_OK && rc != NESTED_LOOP_NO_MORE_ROWS) @@ -11569,7 +11580,7 @@ evaluate_join_record(JOIN *join, JOIN_TA join->thd->row_count++; join_tab->read_record.unlock_row(join_tab); } - return NESTED_LOOP_OK; + return rc; } --===============6882364132835124418== 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\ # ub7q353mmhjm4np3 # target_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\ # /mysql-5.1/ # testament_sha1: 6d02a2ecb5f0b8d6d59029b0d20128f800bb5400 # timestamp: 2010-12-02 13:09:53 +0100 # base_revision_id: georgi.kodinov@stripped\ # l819wohslm0k87fn # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWUhA27sABqvfgHFwWff//3// /4C////xYA0dbfXWXVpjaBrQCVH3RTJa9d2L1lVuc4WbrlabswlE0pD9KaYnqNM0jQ2U0BoMCBkA 0MgABKQ0hPJoGhU8KbSNPUYIADQAANAAHGRpkxNBkyYTTIGQ0BoDTJoYATQGEiQowjQA0lN6RslM T8ijTZJ6h6IMRoYgAIpEFM0SbGSh6AgyHlGRoaABppppoBsoEkgBAhMAIBpT1PIaRqe1T1PSADyg AaaYQPuYBpWwvC8+gkMJHsFf0Cy+LVpm2UZutt6YTlWsrKpknHX9e7TT4pYzTpx/nyxnJLpWeYkH jDDIwvMZ0e/RcaxuTsohAHU0SdUOqoL+ee8Eh94QZmzB64sxDcQKKm3Al0SFbbAqgg+awMgVleHU 3Fhn1maaCrIqwouPt6lU83eemvK2d2rtlSVjdhfxHH9GAWzwGxt6g5/fvtoVAC1iuEiwZIB0eADP izbNiXLB2hrtS83GWgUTwESuUjWK498ZMepEQIjxCT2fqH2L4Lkm22k2qeVK410iUGjPB0vxvpdH RxhibKL5+FKT2UJQc5tGhtpAFkeeZhQ9A2h5EwHC5C2iXBRPSMhvzkhH8xhMwNzutuNoMag7z5hU S3nAcNMSYYA2heXnSr9ec3nuGJjFqDkEgcUA4OsGvEtMS4eQKjAv4sjlw+6tYO/OtxR1Rw3UfWTj ndQ++I4z6uYtFcrp89uNxZIwP/PFsx0x5zeVJGD2cMVNT4OtvN8OcaYV0bNu63Uy4jTJNBwu1DWu dI+8MjxXXviXBEhWu5Y+ie73wzG71456qkza1mGuNIX0a6g7xFY9DE3GI02gw9AOHB0AUUFFBQUc NBpclLKxLmxz2rJk2zdib+jPmXioqN2l/JOQLKuzf24sf2YTz76o4wq/R17kTaS3niGND6YiIhtm 8COHO09DXlgl192AA9bKxjKlokBuExMO8YhjEMYkZyQg8+OA7qwuzn7cjC9h2lpAoDpHm0YD8Coa z5hAgMMOKEAreQxsNYWYUwH+W8J9bkElsqE4lBBN8xIkywMRHGIoo4aRM4xMkfE+JAkVPM0MNvQd xyCoB6kepYKgG8m88ne4NCm9jN5uiaBGY1w39LtrVa1/n7RuDjoOWfKaRRcTeDHAHBkgNK6B5AYc OHA4IiikeSiLSL8Puy7GWF9Vcur9pc00Biy4M3GPMDxHxkGk1h7q7IqAyFYR3sIeDODZARxgBQnF ZlMqSvVxYRIjAmKSod48vIaV3rJB8AhXiQynBVMWgEzCDBin2UAgYjvyKES8hq8Ms/XgWEBH5Mp0 ykhaxDBNcZrDRHXlTXStaHyQ4yCAPO5Y57ESsGFJSVMffu3cpOaQIJdYlZLKODxP4nAY58HmB08B zup4cQYfBsxyOwYGAYUSQDAbNYIoCOgRVmIGsgGSyyWBRMazfNKU4g72FYEKKRuYVJF5I6yCvKkT A/64ifI1azmV3GNsNRFhP385huBRMRgHqi0kKN0hA2nYXEC47fxEayJ2WOfHgrzG4zbiUBHT79/I 9pxOIbhwxxY0jjsB5f2nIFAIC6OKmdjLpK7zqNMTqKDx4VO2vWIgqB6hyeFzbwyHli8qYwJd5Hs3 CJ61vZtGwDYLoXHP19Q5axxdWGUhEXkGOvQ4Ne3W/7BNuCseT8sikgbF6RY4ZoeZyyDJIlAy0x1J S2qAcTHvNBTsFo3oGClFJNWEJxQatdzOU0tI7BJhSIYTKlvbhJRi3vLZyinkhy4cPHBSJGcFS5rx sxmNWIaTNsUzcimi+gYFY3I40hbZXAgPa8trI0RVhaEjUPTEXMNU7SAVEXGWZ5SpkT8+Fz8+hnGm 1MBk4+4zSemaGhxjTMnSc4ItF6Gk5OiExTm13xw430b7zoPXrKykkiLeU6NX80og3yLHEeC7prSm PgoplSVV2AdrkdbPGiTqG+p6UR6hdXGLr446a5kQwZAIXwnRomJQBEBAHE/IsEmF4gZAgQLekBMC AdsCQfsCSukXlArGwC+DZ/QKtQHUBUVgc4DWLMQJQJKDegRma6oygPECNgEwb6i2gWi1gQLGJQA0 DBOhW1ucMSoKYAvUIxNgxFCHGMCFFoulBrNgCkSsgg/JBiAoDCB9huVrCgZD6KQa8gPULMZgaQNs MBSSVmEt2cQyJgXCW/ZICZnwDISwBcAKiWSSiEXixoCDaDAMFwleBuCQlrlUWi5qFFwBbULmKyTF A7OUCQ/oC4b3JoDfh3mYpnkTFIGXBL3hUNMMvd7dZpH6QOLmGCSPiMvgUPUts/L5Y2Jk8bzGDkBa 8msv11c+MhWej2+7ApficZdqMK1yrGXrJnPXONCCJnxUM4XHsSOMmAvgaE17sdDoSyisX/JbHkld mJYxM8z64n7jpGPTHoiGQlMbPHEkCA4oqFgclCo9wsyhvoiSFTZUqdjFFFLrpyLgcW6Vidh++2n6 nMedtzNelmAgPlhJoFOAZjAmkhAk2+aTWxUkYy7mLipS3optmE2q2ihKUm2qlAaEyT8uyvdIstJa S/9JrU8cXIsjuHPGIpXnJc4l6teObP8WKcbdRspJzUJ3BEOSliYdJdGJqNmr7EO+rt7/Pzg+UVAn HY2PbXNYaMfYEiVkkvsjM5bKGz6d3kbqsW4G87TrJ1rDUXrUr0jwbtcKNSkZBPZdl6wNm1BVY1dV PUWVsiIJoqp1KbLsODOLHPJ7zS8o4IpyZuZo/DTFDG5lt7Z0FvRYoaLlCJF6iRYNti1bYLOFwWk5 x9ZSnTgixwVzw3kEOZa2mlMz2ejMaGaC7iAvEK1SmcAWlMIv5ZLnuE80beB3vLjuOZNFtgwV3eS2 Kq9UMqntWqSKhnj6IY/6fHPTIeRZfirRG8B82Mr52KxQKr3Lw2ePDLr0DAwnMM7AdzAgiHowjCIu 8e1R8z2lGcBHAFQC1VF01zn5hmEMZCQwXuAZcHgmNmvnm8Q8ocNP2pSdKvw8Md94Su6Dig9xYUzI YBkgYaqEVNIkTOUMZneVJPakoZCIYce9XUhEYxILbDIl9kmC0fwFG0lBdOkrZhfcnn4WwTfdxTy7 hEGVw7vEcRMgGB7hiM7LoMQOO+Kcd46zIXblWHkajKn1ayQUF2lNs8RqUKe8SKlTgaMrKzFI77lR AVpECtDyWF6aK5ygN9kPMp5dObAGBLOPcnox8Vkd3Xj3Gd0sQLcCoBeugcegho2PEVHWc0VJNkuU PCw8RRnBk3oIpeOldW1gmUlqcU0FQnEnLvrYeH0ZRT7hyUkhuSZZDSQSa5MFRlVOMUPExIcZF1Gd DbWQLCVBbiPsBpL0CL9ZVaUN5ciBIYNoxstJLrya9QDKN8bsBWxsdGZSpgTcFOk0BDtcNAhUQGCH YL7CkjwSN5w5WG4LU1IFo3mZaZXBNoHgqY7I99/OWiiVcBb1VVMMl9T6YENSCYh9tjJkfM/lMOaT pSfIdkO+IwwIGmBwgyFUVWxCx0g6OoiIX67Jpc8r8S4xbMns4ZbmQWMSCSNxDSYlj8WfcTDA7WWr mFLRdXJA5MNa/2DY9kQFoWp2xskQmZbEduM01Qq1ULSjDXJjbIjI7KES4skSnKTbbD12Pk+LjAvR 0eG7GlWorDZBfoBh+b5+yISV5fmaxCmu+1I6sx2NeFaSwzGXkuTbbzeaIINW1mto2oMwYhmJiEXI jpVSsfNrFVHAtTslaNIVczwiG4YB2DAGMDmJECJHddcFaBW2CYiYiGKHKIzchivLJ5nvWYmIedGK QsjDS6L6qpR1HUF+TpBczT2FwSFnWvoHPC9yKGezI6y8e0GT/Y0zCWbk+TRyudgvNLjuMWzYuJmV FK3bqc5kKtG2Gj3/KYyiLrUuuauNBMW1DwshTXMQZu1QLSXndlrT1AQNeYYvasZG5Yq7RKGL5GKu WF82ThiKbEcIxNGQymuJXAHDBamjQdpn/+LuSKcKEgkIG3dg --===============6882364132835124418==--