From: Ole John Aske Date: November 25 2010 2:13pm Subject: bzr commit into mysql-5.1-telco-7.0-spj-scan-vs-scan branch (ole.john.aske:3378) Bug#58490 List-Archive: http://lists.mysql.com/commits/125030 X-Bug: 58490 Message-Id: <20101125141327.6A3FE222@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============7936204309334229241==" --===============7936204309334229241== 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-telco-7.0-spj-scan-scan/ based on revid:jan.wedvik@stripped 3378 Ole John Aske 2010-11-25 SPJ-scan-scan: Cherry picked fix for bug#58490 into SPJ branch 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 14:13:23 +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 14:13:23 +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-11-19 10:51:30 +0000 +++ b/sql/sql_select.cc 2010-11-25 14:13:23 +0000 @@ -11586,6 +11586,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) { DBUG_PRINT("info", (" Condition passed")); @@ -11610,7 +11611,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 @@ -11629,7 +11631,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; } } } @@ -11656,7 +11658,7 @@ evaluate_join_record(JOIN *join, JOIN_TA if (found) { DBUG_PRINT("info", (" found match")); - 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) @@ -11685,7 +11687,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; } --===============7936204309334229241== 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\ # o5nx3j14qaolwxnp # target_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\ # /mysql-5.1-telco-7.0-spj-scan-scan/ # testament_sha1: aa93760d3246d53fbe595190d160a1f8ee36efc7 # timestamp: 2010-11-25 15:13:27 +0100 # source_branch: bzr+ssh://oaske@stripped/bzrroot/server\ # /mysql-5.1-telco-7.0-spj/ # base_revision_id: jan.wedvik@stripped # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWSxLotsABGbfgHFwWHf//3// 34C////wYAhfOvVIRQAayodUKaBnZyAklCj0yTQempmU09T0mTxTQaBoBo00GgA0OMmTRiGmhgJo YmjTJiBkYTRpphBkwlKaniYSnmigNDQPU0ADTQA0AAABIkgTCapsmk9EYg9JnqTR5E0Gg000NGjJ oHGTJoxDTQwE0MTRpkxAyMJo00wgyYSSCaAAEBNGiGQNFHtUyZNlPRqeJPU021RJCwWZoGh6h4wP PiLkbcHKXGjnDFk5mHfT6n2/n4X2XGA61vAwOA8ePTxvAlLJpgiDroOeY5g6kWpG+9+SIpygGBQL Li33AQ8ahrS/mIuil09lLnMqsMEliXszJwhdYpkjEYeAxtBhx+QFBgWl5KcoPeHkXQsqbYm0315w K6+yiyaDXcO57DmndJiGyh0Ijx0U9guub/UwKzgevzhADIDoHEB4jnGVaxcDMgaicDvjAZeXnWbP tPmMVhYVGhR7/41K/+YL6/f+PF/A6jo4FR1qxlO8qkWRDMvPmOOAMfgDx4doQIBAgEAgPHA4mFSb dnZ39/fccBr05tKws3ciJmks2ZsbfYmQMYwOIRyDBjBkIN9hXOFpaWlpAoCAkHVoFpAxBpAVJSUm IMhlDeAMDC1cwaijkDcjsDA48Yj0dtJw56ajXlUefnjzTYgVyZxNNIsKDOVjIIIBwZCiCqFJb008 F2Lf+h0trOAbGrZmHW+sD3xjeGZkEkqIDxhJ48YSeDVBT3Gwpjdm4YadOGJPJ7JD4EwtU9f2qNpS oU0T3XiKto5XvHiarIKRew4q1aUDAL64MTJ90y8Lzj6aBKsxJDhKeIQQqXabM41NjGpZiLHFR/1E REFDTbdyEWZVteFqROOo1ccoEZcxxcpholip1cLoJq8ijcZsLTeJwi0EP+6mCYReOVFMzxD9qzJD 3hB3EzeLN8jgOd5Ck8pqbs/Av3bDmXV7Cdz3ip57hPNBGpW8B5LlAsgsorawjxHGDbJDjo68J2MT cPJr4EDac87vwEdPsu6D+pxOIby1htrhscGecBRThNiYETpZdTAs5uI4hCZcnFXLf5WrOCo8VOUl QXDqzhDYMdhnRQI9Ay5ce1P0kInpHqJ5euQPyzuMvxUytxOdBVuMymPSeoRskZ1YlJWzh9g+4dOM xZxfxbf2DjZVlqikvcW1W0HoK1pWb8SeuBiPM8ugwkYk3EpNMxyYlmjVPGNcDOYmHkSZVTWQ0caw yiFkoyeVmdIlAalhMOYpdA8kqtpAZaskx8ylEIukolURZ9U+BCNpgaQImVpArgEjwvKyJTWFip9U FGEFd1EljqdElbIKWSkh0zkMKhU1Pmm1XnHj43PAlNhQwicYLc8GNjGZFtJRR69H1W3OC0mlcoj+ AeP5AXywTJmG/cR9oxIAOB3zePVAGgj4iMUBJMFtBB2kIO4DoVNgYgU39OkStAnEj5DJJ5WAvSIn CAKcBYrECQO8RJwKO9kEIAL9ATwWIH7CUS8IFwwx/sYuUCpfXICRB3viaDLEP5Ad9w4FsUBiG24K gKbK9J1YFoFgKcC0B84iQFC1JgMANjgFOSQWHP6JOSrUoqUyCm+8J7H14k6LMzoHTh5X4egxMZ2P kVMCmoLvxX5kIi+mCv8D6pojfMRmUWIP3BalDQmSM0S7AdwV7NtO69s9i3kxjmU8CAiKMLUiUszF Yon4GvURKz5LA0KL1QisOKqKSw+tvIiZjjoOqwHuGBcKkBoayLj5lZSihjBdZtxMpGB+tjh5hahx OjmPglX0/Q41VRLzaUu3nWFuBZ9KF+u8PKyQtSqsv7S4jA/appeScmHTeYoXlVgK+5rmHXKgXn29 HbJSG2VlpWrEBmUM92aRrsMWmO0XCfQfbU+3DVvqyFZMiggK7g7tkt1nX0TIbCpLcMKCXEjtFsMj TsbN5rzf54nYakhxzDEMvFclvCY1cufa1JwXVebtEmPOdOHPRXtjkVkeIC7mnJHAhThQbxZwrYPo F4hjISGBfYBi1GWJs33/BJpugLEaoPQQO44o0FAzFhDDluhQbRUlYth5nFsV3ZcRHfWtwLv1GY39 xysG8isQnXem07DoF3ynKi8YHi7TLphSN3jEl5CI/3Uq8F1gnCguo7enHFkVlzjvOqoRyfB4BWMe dn9qcVrpmN9EeqaYJSIVF5HkR62CRQqCAKtDk9mCIp0w8+A4ToYvo4l4jDuLORsCdkzJSHLWeEVR xA9LYEWq97LzCCT1KQZOYJBFcAwDBEBVPYuNUo84KMTigN11P35xSEyDjlmQP3rTJkYBgTsg2d5O Uar4KpIJHAIVTEIolsbo6au1AuixB7CsxPFvbkbypBA9q/87X1OC0LrVZsPNsjlMSGLWJBtJX+XZ R2hTUr09gcpjYJy+OgL0VnsZWmRgsG23caoggvStd4mjeRF2+WDysZTbMtkNoYB3DAGMDqJECJHL oVlSr1CalEa3hFm8avKxNvGPTT09Wg8ftPTfcJlq07Iq3HMx5ngomfT7u78zQvvRWWjhZbTsJjQX okes7yBOaEVX7MExAWJqe64n5L2nSVm6wRukIdTIo/okWJPXFOGtLTmpG5cS5i0LKbcTmf/F3JFO FCQLEui2wA== --===============7936204309334229241==--