From: Ole John Aske Date: November 25 2010 2:10pm Subject: bzr commit into mysql-5.1-telco-7.0-spj-scan-vs-scan branch (ole.john.aske:3377) Bug#58490 List-Archive: http://lists.mysql.com/commits/125028 X-Bug: 58490 Message-Id: <20101125141035.F403A222@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============8693232415282523620==" --===============8693232415282523620== 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:ole.john.aske@stripped 3377 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:10:28 +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:10:28 +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:10:28 +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; } --===============8693232415282523620== 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\ # xug18df08zzwgmsn # target_branch: file:///net/fimafeng09/export/home/tmp/oleja/mysql\ # /mysql-5.1-telco-7.0-spj-scan-scan/ # testament_sha1: fee967a137bb87718310913af18bdde7e56ef268 # timestamp: 2010-11-25 15:10:35 +0100 # source_branch: bzr+ssh://oaske@stripped/bzrroot/server\ # /mysql-5.1-telco-7.0-spj/ # base_revision_id: ole.john.aske@stripped\ # pln4ozgkfol812mk # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWWAjyCQABGjfgHFwWHf//3// 34C////wYAg/HlSIAAEKlAFACQOMmTRiGmhgJoYmjTJiBkYTRpphBkxxkyaMQ00MBNDE0aZMQMjC aNNMIMmOMmTRiGmhgJoYmjTJiBkYTRpphBkwVJITU0ybUZI09KAPRqfqgeEjRtRkeptTTEyMg4yZ NGIaaGAmhiaNMmIGRhNGmmEGTCSQjQAE0yGiaaTE0I9IeojabVGmaaRox6kihXrYZBke4eMDz8Rc jS9yjrP1wwZOZh3z/b+PP/Zd0uMC61vIwNR48enjeRGOxpQkB10HPMcgdQjsantnn2idM4CQKiA+ E7wR8rSWU7tyhXZNHGyZ8MdjXJNjhqqlkTmTNI1qXCm0pZ2DvuJk4F2DM/Q5p2zZKqkqpVcu0Z9/ 3ZtF1ngq1cFclXyOixDSd0JB46RPYLbW/OUFXqe7wCAGIHIcQLkedTe4nAU1nSwHIoUyZPW2/k/Z SaGgpMif4/+2lX7Xr5/z/30fwOZx4FJ0VbKZ5TErkDYXHyHHAGPuB48O8IEAgQCAQHjgcSioNM+z x8fHJucePe35u0fLxRfUk4+OqVVe2UspSh9qPGopRS0OejfwNLS0tKyWLFx6eZOgZzoExYsWc1th 4Q3N3YdLDxngt7Tc6tk+73YnhpGY2PxPZvEeXbI+hJWpj2pKonNxSMOHDgcFwpElULgppoJy3/gV NlEA2tSzMOr9wHxkktDEwCITwHjCTx4wk8GpDvNpPBI/djbkOYmLZVAnqzEQJCIpVSpCZPhYIzGV kw8TSXieXsHtnJBgD7Z6BDydjqMfY24b8pBKgnSGhHCQIIVLrs8pKXJ0NtKJz90PEVhC3PS2mFFg VKLAMaT7ZDj7REpG+foPL1260Exem0cLkQt3lGpowreInCLQQ+WULhlNPtUsBEJYFA+URvRaowuN jxTGpL2lRsNx04/YUK2fadZCvabHPeKnjoJ5tGA2kvU5Vj1p6DF2Lq7za816ZSmJKUj43SEhmRsk 369VnA95wOAaFjDZs4dhUzzcKCcK8uJFxY5MC48adhkSyo07IO5VG9RiVkQsKyp+0Y9C/07l4FK5 cdMphGHOms7zPuqB4R0z3Eun3KZZ4Mf231GJoRdJzJcTZRkRKWcPuoIWDpxmKt79W17BxpVhmihF ri2J7DOdS8GHzeyQvLlWpPdsLXkCTkNSQsxzKiQcT4IzeMXECA4eVT1vYxe9IoYKYTqqCpNpWUzq VVOsHlErqicsdVIcDSAh0e2UrEXzSM5xTVcxQPYdSOfYWUGzljEmKyQpeE55Wj6SQtCbrrIWuCNU tzLerNpsY1GF0VjgtRvJjvV2X9WTr1ddYWSX/Zmotgon0fFqpSmucsls3k5+6fHhJOp+KP9Hw7Bl m4VVSqV60f3hgvFi3puXTOONHajPYulE2ln+LQ947ZjoM5L/g9CTSMEjuVInlQC8BEwQBTALBYAR B3oJOBSbmQQgJ5iXE1j0JMHCXslKexTKXtE9WwYoO8ZDIZYB/ADv5DgWSIDANLQpAorqymVYWAVg pgLAHzCIgTraSgXgZuAUxFBW701y22ZeHnnyQ8X03pf5vz/CXJ6quF/l8jrp/1O5vVJM0pX8B8Bf OtVe9QGsI0IPNJXEWmPjgSnCcwD7Ph8nfAy2fgfMrxiecnmTmB09rdglPwY1/Iuu1PMgVlhiW3kU VBHVbxxYfpVyJDIccTqvB7hklrSgNpnEtPzMChE7GC1N2JsiSXn+K3EDTC1DyhGo+CVfyKKHlpoN SI3nWFd5T+f67g9TIA2j5cDXtLR51/jGl3qmKB3aULtUySqya1h1qmF35H6B1kinxowOrlul73dU 0KR/bmOv3MfuxZ2K8IpkdpAXq3O74rPr56yoa2lLcMKCXAk3CrEdC/MZjI5m2UgPOnUFwX+i5LQI mzVPXq7mqN6FJeZ4pMcF6zj+ojHn0q4jPKh3zjLy4Lm4MDM32jvu01IpS0iiewUrRjgZ77fNJpeQ VGSD1kDuNUbBQMRXwv5aQnMxUFQtp6nFki7sfp0SX0ZDMbvUa0jUVDJJzj1rnUdDUXdN3FRcMDxd 5h1QoOpO9wxMvpJR/10rBJTJJwoLmevlhWsHIrLXGw5aufWAVjHgz+5OK11ym/VUS+EYhNMQpEVG BLyOBHowTlJAGpJElaPgr1I8IioTDzzHCdCXZCl5Z6yvqNwRZMyUo5X9+zAH8M6M/x/ZCJloH6nc DJ3hQAeAwWgd+te5qf6SV4nKY6PUvTUWR4r7M9yCPXsZMjyJAhRJLOSnNV9k9DrTndaUUpddHQub DrQbiCEeJUYno3vxN5Sgge9f07b7XBYFtirzO7OTGUiMWMRDQjd25z7wopV0XUtMzkS07e9JPr0x 0cCNTY3TdVVVanTayzhk4K4UqPEtq52hXHSmOm+bTlUHvUFKHzHjhDzyoip/qEye513AW3mrSkTb xjyo5dWwePzPbdaJlk0zIp0OBhzIGPH6+74GgjIuuRUWDljodZKZC9cT3H0ECYyJFV9KeLE2n12E ZRHicig+3dVRKT/VWlARqnDWFhKbVvK2KgvVdF94LmYiP/i7kinChIMBHkEg --===============8693232415282523620==--