From: Ole John Aske Date: November 25 2010 1:59pm Subject: bzr commit into mysql-5.1 branch (ole.john.aske:3474) Bug#58490 List-Archive: http://lists.mysql.com/commits/125027 X-Bug: 58490 Message-Id: <20101125135916.45005222@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============4492650403362990867==" --===============4492650403362990867== 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:ole.john.aske@stripped 3474 Ole John Aske 2010-11-25 Fix for bug#58490, 'Incorrect result in multi level OUTER JOIN in combination with IS NULL' 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-06-01 07:54:06 +0000 +++ b/mysql-test/r/join_outer.result 2010-11-25 13:59:10 +0000 @@ -1397,4 +1397,48 @@ id select_type table type possible_keys Warnings: Note 1003 select straight_join `test`.`jt1`.`f1` AS `f1` from `test`.`t1` `jt6` left join (`test`.`t1` `jt3` join `test`.`t1` `jt4` left join `test`.`t1` `jt5` on(1) left join `test`.`t1` `jt2` on(1)) on((`test`.`jt6`.`f1` and 1)) left join `test`.`t1` `jt1` on(1) where 1 DROP TABLE t1; +# +# 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; End of 5.1 tests === modified file 'mysql-test/t/join_outer.test' --- a/mysql-test/t/join_outer.test 2010-06-01 07:54:06 +0000 +++ b/mysql-test/t/join_outer.test 2010-11-25 13:59:10 +0000 @@ -981,4 +981,45 @@ EXPLAIN EXTENDED SELECT STRAIGHT_JOIN jt DROP TABLE t1; + +--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; + --echo End of 5.1 tests === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-10-20 13:22:47 +0000 +++ b/sql/sql_select.cc 2010-11-25 13:59:10 +0000 @@ -11467,6 +11467,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) { /* @@ -11490,7 +11491,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 @@ -11509,7 +11511,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; } } } @@ -11535,7 +11537,7 @@ evaluate_join_record(JOIN *join, JOIN_TA if (found) { - enum enum_nested_loop_state rc; + DBUG_ASSERT(rc==NESTED_LOOP_OK); /* 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) @@ -11563,7 +11565,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; } --===============4492650403362990867== 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\ # nbc0xbj77w1q04q5 # target_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\ # /mysql-5.1/ # testament_sha1: fcf225c9863aa5eeca419a2834aaacf0c3f170f1 # timestamp: 2010-11-25 14:59:16 +0100 # base_revision_id: ole.john.aske@stripped\ # nbqqrw4mw1v30oop # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWRlRQIEABL5fgHFwWPf//3// /4C////wYAm/C9lNAAoBtqDfdwCgAB0ASSintKfqamIDaMRqAAGQAA9INAA0HDTTBDIaaZGTCAaa AMJo0yYAEDQ4aaYIZDTTIyYQDTQBhNGmTAAgaDTSTEm0p6m01BoM1NGgeoHpANGgAAaGgZUm1HqD IADRoAAAAAAAAAJIiaAE0wRNGpmphMjRGmImmE2oN6hNHiidC2rM0DQ8SAwQPiLzG/B92LjlPRXy myZOzD+X+/l5j89vn2mRtvb3GRxIECCgN7iefRpBMD7IvAx1B6kdbV2U05iehwHmCwdQi5SwlWwt XOVRRQY7tvZC/CzIltqj8bqsx0lmYszcPLhREkIWQqkjUYhAYcGKLxu1vqRYnlY7gJSclNAhiHFQ bSxKdMSEJQn4vACxJlugxRcKnKNJuuW6Q+mDIEKGxscRmMMGyJwV/I8QgBkBwXbA+oiI+Iwx0VZm QMaB5e4oA50zMAxtNpxWP3nxGF4WlpacCqjs/5PftGgbAOQu1mbciSUWGQyZuORsjkfLGuNqyuW2 8XhuxQR3S2HSv5levSm0lBXRVFSxu2ThTDToNx5DnUDH3hAgGQRIhEiEQiQHBykWHr53MWxN+Puh NtevNdWjuyP47hgHUbt9X5KbiMhklTQzDM1K4SXRNSAh+3CMLFAwxOtsAPcI+0YGGBh0HsYMJwyO xZGR4jicHCIR/b1iwAqDp6xVMIKioqKw4GAeIB6zI/IPgTfaHY/gEleI7Q+HfGYvPCGLU99XY1iS imOSSrKjM0GHHHBw5hTJK0W9UURVa3fkcWhOAZVTQZlbxA1cRCe8KDAJiOswojCIigMIgDWxDCsO 8zISRUMfIRIvKi6YjOfdEkKZTVE/EaPMRpCYRPgI2bh0wq6cRQF7IiLBBcwTsHE2+pjuiIrJJEZb dZBIRJT0xPVPLc0Q1pEUkHnRSEZI763CcGMZvhE82fnJytTGtxYsanF6xLzE9tVxuHYW8TCLgRIO YJgnhxBjSGyZCeRIHhE5h8egzgLecDsLCg+X1m/M5FWyvI0eqAm6tRQMxgMuOp9AsBjUdqtSAiyq Yu6ekSj4Iy4PM0cRPI4pKMFuc2CYmCUsMMMTSQG8TCvUDoZcdXKMSME7uUg2nW3RUblMSGIFAWHQ amscxg+hAp7eQeYR4ECPRVAOhk9017CGwGsa4tO417s5C1mDpqPuVBcMIkYn9OG6ao3GWEZ+kzoO G4sJ0w7zLNis17IX88SEhsCYvr788dEUIhcQNdkDwJqi7WQ1qGMjKB911xsLZuJ4CNKisYp2o1gY ESJgIgYFuN20eW0kDFtcJWFpsoMFNaw5PPWUlz3zFZWdBEurmZpFZO7EYAryFMa2uI6YGFdAxEpC ugsmt8GtJFtk5aMOowehix3JSaapwGlA1iAuaFIDwj3njirmGCWstUIkVTsDGAFwIKm3ZYYY4FcS gdya/gnF/bI0lwnGkfgGPpAy/SM2jMzMM4j95piIDh/lx0UgaCP0EUuEEMCnCAPkgQfoFSI1IDwX PFjO8ArgOAHYTscWEEC1IXpEUhEFQkLFGIE4OvkknBNNS3OzsBMogL8gUEk+IHzQExgExeMMfuMb ERLEfsskE4RTrKZKfaL/oEF8g4FqmQGwWqoCoDdpJFYWgVgpAWIIV3CKAKUahOBWTYAbopCqCKCZ 188EOv4RsU/t71fKUptEAHXXrRoRpRejDGjQnqQJ8IcAH4R/dsDCIcQ6VaElLcKhdORZskHh0Qb9 wh2XyEqLvxqUekBHIbyVajYT7AWGhkylasqUrjqbJ2BViw1GEoLs56mHPy5qh+fQRp9ki4zdXjr9 RFHNSFE/gJc3UxqZiNuF5ZsG5zQkciQ+85qSdF6v6VqbDdcQMBzLPzI84RIO6S+ZUgLFu+pySpC8 8joIoKmBmBplMoLamh0mO456TEwnRAkjgQilSdB0H98Ymwbqx3Rj1hfZfgXeWd/an/XteLr0ay4t 4KZAFd/XEzORzGMvAbvpKTic/P2KVq71okqdvB021QJxNj/gMNyUqGWko85Q2kVKJvV3LlzbmhNt 6nMC3q4Ln08G0/lzQNuGOU1+LaBncnRApAs3uU99qqUaIPyrdDYzCmudFhezQUBdyoSoH0ak4rxe NMlmnKShxrQtGQjDmJEWkPl2loK0v0TUG91OMefpHuhcWThzrz0nChJjwOW3z6dAWwKh+kDYphbI h3E48j+y4gEApJFmp+gXiGGHSGBfwAxaLHb3XPps60mnxnDsuNEHoJIl3DAZoIp8hbbfGyijHp30 WBiJpy5WurxeK3Bb6JTE/4RVSy9mYjmSXjuGY3qzh7DiXjWAFvr9B1lQu+XabSxNtHCIvSR0jUN6 hiM66i+c99agkuSScVAbqzNd6OnF23ZbIC2DupkewtOrj3F18qJwDYMnkdobHaeZVrhDrpIMrGlU eFE4RKpE1xWvUE+JLtqugH5srIWDhJJWjheNJBKxOqhKp6k9CgKeqhnziiiMg4nrWWRNTR0laB2R WxSQXp0zxBljXj8f3QLQPI9AMn8eoQeoZK8Q3ofpPaFuomWixX0SWIqlBDB2QTcyo65LOkWpXLXe geWTJke8mCIWMq6SYmJu3l1RqKoVLA94jDsEYsXbEuV/cG0NhYcnd82QcxBCJQtNp82+zMnCTUrc FYjOhfWvo5vbe7hUFLFSupR7d0slROGKpfIGVN8e+RZF2aK6ZTMc5aGXbzX0zmwMU+DngxFUFFwo L47El6bkcFkTipKiSTNWqhRazyguIZjaCWUoYE2RFKyeY20VNk7jlUohSHOMAfIYAYYDyIDiIHvq 2KhVfaJhRE7BCD8PSwt5DZ51gWibIHE6+ErdMzzKIUTbikLxbFozqp0TlyOkrIGH/tiKePv7/xJi zWcVigRIG1YdChk25HIqIqdL01HcesPxJjVE4ykrvaoC0Ow+F9Le0RkHOGaIDBacC2r7BEi0RGTU MNAa0tMrS1cxTanGCqrDA6j/4u5IpwoSAyooECA= --===============4492650403362990867==--