From: Roy Lyseng Date: January 24 2011 11:57am Subject: bzr commit into mysql-trunk branch (roy.lyseng:3327) Bug#57431 List-Archive: http://lists.mysql.com/commits/129434 X-Bug: 57431 Message-Id: <20110124115714.540591F2@tyr67.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============2267914973253346943==" --===============2267914973253346943== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///home/rl136806/mysql/repo/mysql-review/ based on revid:jorgen.loland@stripped 3327 Roy Lyseng 2011-01-24 Bug#57431: subquery returns wrong result (semijoin=on) with pred AND The test case contains an outer query with a single table and an IN subquery with two outerjoined tables. The MaterializeScan semijoin strategy is selected, meaning that an outerjoin operation is first performed over join_tabs 0 and 1. After the outerjoin, sub_select_sjm() is called to perform a semijoin between the result of the outerjoin in join_tab 1 and the outer table in join_tab 2. However, join_tab 1 still contains some reminiscent data from the outerjoin operation, hence this operation also behaves like an outerjoin. Fixed by clearing the outerjoin information from the join_tab that represents the result of the outerjoin operation. mysql-test/include/subquery_sj_innodb.inc Test case for Bug#57431. The problem can only be reproduced with 1 row in the inner table, hence Innodb is needed to prevent 'const table' optimization. mysql-test/r/subquery_sj_innodb_all.result mysql-test/r/subquery_sj_innodb_all_jcl6.result mysql-test/r/subquery_sj_innodb_all_jcl7.result mysql-test/r/subquery_sj_innodb_none.result mysql-test/r/subquery_sj_innodb_none_jcl6.result mysql-test/r/subquery_sj_innodb_none_jcl7.result Updated test results for Bug#57431. sql/sql_select.cc In sub_select_sjm(), clear the last_inner field of the first join_tab to take part in the semijoin operation. modified: mysql-test/include/subquery_sj_innodb.inc mysql-test/r/subquery_sj_innodb_all.result mysql-test/r/subquery_sj_innodb_all_jcl6.result mysql-test/r/subquery_sj_innodb_all_jcl7.result mysql-test/r/subquery_sj_innodb_none.result mysql-test/r/subquery_sj_innodb_none_jcl6.result mysql-test/r/subquery_sj_innodb_none_jcl7.result sql/sql_select.cc === modified file 'mysql-test/include/subquery_sj_innodb.inc' --- a/mysql-test/include/subquery_sj_innodb.inc 2010-07-13 08:14:01 +0000 +++ b/mysql-test/include/subquery_sj_innodb.inc 2011-01-24 11:56:54 +0000 @@ -114,3 +114,24 @@ explain select 1 from t2 where c2 in (select 1 from t3, t2) and c1 in (select convert(c6,char(1)) from t2); drop table t2, t3; +--echo # +--echo # BUG#57431: subquery returns wrong result (semijoin=on) with pred AND +--echo # +CREATE TABLE t1 ( + i INT +) ENGINE=InnoDB; +INSERT INTO t1 VALUES (2),(4); + +CREATE TABLE t2 ( + i INT, + vc VARCHAR(1) +) ENGINE=InnoDB; +INSERT INTO t2 VALUES (8,NULL); + +SELECT i +FROM t1 +WHERE i IN (SELECT innr.i + FROM t2 LEFT JOIN t2 innr ON innr.vc) + AND i = 2; + +DROP TABLE t1, t2; === modified file 'mysql-test/r/subquery_sj_innodb_all.result' --- a/mysql-test/r/subquery_sj_innodb_all.result 2010-11-30 13:55:22 +0000 +++ b/mysql-test/r/subquery_sj_innodb_all.result 2011-01-24 11:56:54 +0000 @@ -141,4 +141,23 @@ id select_type table type possible_keys 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where 1 PRIMARY t3 ALL NULL NULL NULL NULL 2 FirstMatch(t2) drop table t2, t3; +# +# BUG#57431: subquery returns wrong result (semijoin=on) with pred AND +# +CREATE TABLE t1 ( +i INT +) ENGINE=InnoDB; +INSERT INTO t1 VALUES (2),(4); +CREATE TABLE t2 ( +i INT, +vc VARCHAR(1) +) ENGINE=InnoDB; +INSERT INTO t2 VALUES (8,NULL); +SELECT i +FROM t1 +WHERE i IN (SELECT innr.i +FROM t2 LEFT JOIN t2 innr ON innr.vc) +AND i = 2; +i +DROP TABLE t1, t2; set optimizer_switch=default; === modified file 'mysql-test/r/subquery_sj_innodb_all_jcl6.result' --- a/mysql-test/r/subquery_sj_innodb_all_jcl6.result 2010-11-30 13:55:22 +0000 +++ b/mysql-test/r/subquery_sj_innodb_all_jcl6.result 2011-01-24 11:56:54 +0000 @@ -145,5 +145,24 @@ id select_type table type possible_keys 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where 1 PRIMARY t3 ALL NULL NULL NULL NULL 2 FirstMatch(t2) drop table t2, t3; +# +# BUG#57431: subquery returns wrong result (semijoin=on) with pred AND +# +CREATE TABLE t1 ( +i INT +) ENGINE=InnoDB; +INSERT INTO t1 VALUES (2),(4); +CREATE TABLE t2 ( +i INT, +vc VARCHAR(1) +) ENGINE=InnoDB; +INSERT INTO t2 VALUES (8,NULL); +SELECT i +FROM t1 +WHERE i IN (SELECT innr.i +FROM t2 LEFT JOIN t2 innr ON innr.vc) +AND i = 2; +i +DROP TABLE t1, t2; set optimizer_switch=default; set optimizer_join_cache_level=default; === modified file 'mysql-test/r/subquery_sj_innodb_all_jcl7.result' --- a/mysql-test/r/subquery_sj_innodb_all_jcl7.result 2010-11-30 13:55:22 +0000 +++ b/mysql-test/r/subquery_sj_innodb_all_jcl7.result 2011-01-24 11:56:54 +0000 @@ -145,5 +145,24 @@ id select_type table type possible_keys 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where 1 PRIMARY t3 ALL NULL NULL NULL NULL 2 FirstMatch(t2) drop table t2, t3; +# +# BUG#57431: subquery returns wrong result (semijoin=on) with pred AND +# +CREATE TABLE t1 ( +i INT +) ENGINE=InnoDB; +INSERT INTO t1 VALUES (2),(4); +CREATE TABLE t2 ( +i INT, +vc VARCHAR(1) +) ENGINE=InnoDB; +INSERT INTO t2 VALUES (8,NULL); +SELECT i +FROM t1 +WHERE i IN (SELECT innr.i +FROM t2 LEFT JOIN t2 innr ON innr.vc) +AND i = 2; +i +DROP TABLE t1, t2; set optimizer_switch=default; set optimizer_join_cache_level=default; === modified file 'mysql-test/r/subquery_sj_innodb_none.result' --- a/mysql-test/r/subquery_sj_innodb_none.result 2010-11-29 13:30:18 +0000 +++ b/mysql-test/r/subquery_sj_innodb_none.result 2011-01-24 11:56:54 +0000 @@ -141,4 +141,23 @@ id select_type table type possible_keys 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1 2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (BNL, incremental buffers) drop table t2, t3; +# +# BUG#57431: subquery returns wrong result (semijoin=on) with pred AND +# +CREATE TABLE t1 ( +i INT +) ENGINE=InnoDB; +INSERT INTO t1 VALUES (2),(4); +CREATE TABLE t2 ( +i INT, +vc VARCHAR(1) +) ENGINE=InnoDB; +INSERT INTO t2 VALUES (8,NULL); +SELECT i +FROM t1 +WHERE i IN (SELECT innr.i +FROM t2 LEFT JOIN t2 innr ON innr.vc) +AND i = 2; +i +DROP TABLE t1, t2; set optimizer_switch=default; === modified file 'mysql-test/r/subquery_sj_innodb_none_jcl6.result' --- a/mysql-test/r/subquery_sj_innodb_none_jcl6.result 2010-11-29 13:04:34 +0000 +++ b/mysql-test/r/subquery_sj_innodb_none_jcl6.result 2011-01-24 11:56:54 +0000 @@ -145,5 +145,24 @@ id select_type table type possible_keys 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1 2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (BNL, incremental buffers) drop table t2, t3; +# +# BUG#57431: subquery returns wrong result (semijoin=on) with pred AND +# +CREATE TABLE t1 ( +i INT +) ENGINE=InnoDB; +INSERT INTO t1 VALUES (2),(4); +CREATE TABLE t2 ( +i INT, +vc VARCHAR(1) +) ENGINE=InnoDB; +INSERT INTO t2 VALUES (8,NULL); +SELECT i +FROM t1 +WHERE i IN (SELECT innr.i +FROM t2 LEFT JOIN t2 innr ON innr.vc) +AND i = 2; +i +DROP TABLE t1, t2; set optimizer_switch=default; set optimizer_join_cache_level=default; === modified file 'mysql-test/r/subquery_sj_innodb_none_jcl7.result' --- a/mysql-test/r/subquery_sj_innodb_none_jcl7.result 2010-11-29 13:04:34 +0000 +++ b/mysql-test/r/subquery_sj_innodb_none_jcl7.result 2011-01-24 11:56:54 +0000 @@ -145,5 +145,24 @@ id select_type table type possible_keys 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1 2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (BNL, regular buffers) drop table t2, t3; +# +# BUG#57431: subquery returns wrong result (semijoin=on) with pred AND +# +CREATE TABLE t1 ( +i INT +) ENGINE=InnoDB; +INSERT INTO t1 VALUES (2),(4); +CREATE TABLE t2 ( +i INT, +vc VARCHAR(1) +) ENGINE=InnoDB; +INSERT INTO t2 VALUES (8,NULL); +SELECT i +FROM t1 +WHERE i IN (SELECT innr.i +FROM t2 LEFT JOIN t2 innr ON innr.vc) +AND i = 2; +i +DROP TABLE t1, t2; set optimizer_switch=default; set optimizer_join_cache_level=default; === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2011-01-13 14:47:29 +0000 +++ b/sql/sql_select.cc 2011-01-24 11:56:54 +0000 @@ -17078,6 +17078,9 @@ sub_select_sjm(JOIN *join, JOIN_TAB *joi last_tab->read_record.copy_field_end= sjm->copy_field + sjm->table_cols.elements; last_tab->read_record.read_record= rr_sequential_and_unpack; + + // Clear possible outer join information from earlier use of this join tab + last_tab->last_inner= NULL; } } else --===============2267914973253346943== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/roy.lyseng@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: roy.lyseng@stripped # target_branch: file:///home/rl136806/mysql/repo/mysql-review/ # testament_sha1: 223ed2293ba898da5cb06a97fec8a166f8e7b3aa # timestamp: 2011-01-24 12:57:14 +0100 # base_revision_id: jorgen.loland@stripped\ # w51w9ht4de4svr2p # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWTMf+eQAB8xfgBAwWOf//3/3 34C////wYA2vfUoJAAAAAEBAANAAD6AAoAGVJpoyYjTTQxBpk0AAAA0AADjJk00wmRkDAjE0YIwg 0aYABDjJk00wmRkDAjE0YIwg0aYABBp6pQJjQTAAIyMCMBNMBMgwEG9UoSPQQ0NNJ6E0aA00NGg0 0NDEaaMgSRBACaaaAAp6BDUaNBT9qg9JqPEjyREsNldCs1OBn0wkSgOdA59EQR1KLuyyynwbbDni JYRg+DnjbjlMJr3XWbdZ0rBGzlt8Vssq7NeaXeitgkrqgJOwmpS/y4ktfcx8E5wpFbKhyYY7+W8F Cu8pbaauG67cNhwQqLUlUqVW+rZz1FRzQ+JbaB3A5gZAMMmGBgZl/F+9VlE6e1rfWEWZC2mQOFNQ tYUu/xSeLUUyo/1JKsCznrS/g+PYSjponlhp71hrHdbr4j3nOUX4pWwYTQdfFubO7w2aK8elNNNd R2oq/eMsooKBvWisqNaXqlmlqKb6P5vcjmlUl3S4dr8catFd8nlG3uBDCBKkiy+6DsmZNrnVSBKN MdwEIwY7ANK4ChBut9Zs8LT5gZfVLal+iTCTpOlBLJKdeF7acYvgAZ6af3blVRGUXjpnCqgpzo2R 17QGrA6ev3ny9Y8LuPh1iD7RMejI10+ADaWLJjx6k4ZF9Qk44zSpFbjnNFo1Z0lh4ChNfKVGtihy b8krOZemoqOHBUb2tbv7+oYDIlat1vHYNCGg1HELQuFBJL48Tj+HzNYI+igWqJnpPwLb9aXhKw2U GHhR4iglaULIVqqZr/F5a8AqDQCVSWRWF6EYGpM03oaEIQhcfymKkvw+nYC5K220DirnqUEyYu11 IAZlpwaH9EYnI9CYGIMxzKtcEGsYUhgFEdJKxhbVvJjzCJZP8aYkygtgTKh5jkiAwqRRHRQbj+iR EjMhYQIloiS4l5zIF5aZjmJVKBoP6P57BhmjiJMrR0nWMgu80pJXSm2X2xQGAC/GIsSJEoTHI3ZH ymAtRaOjBkqTaCK9ZXZMEXFpSr+Uq8JUcIRwnfrMsK0xW4TIMl+qoaimsRiEUaXAoKj+tNRe1C4l VAIibi4hcWlpEcNV7qgVVJYc6sSguIB1gLgUAU2taBpAwMcBt0RXmRyOpYulYr8A2pXGgdCI80pm 1UdAkhIpDMjrHrMqS5SOpwMCOwEV2HAUCzhdI+4iSJktnIiHFgWuxhMLUFhlIgKnua5FnDsRKdhc vM95Z6EjqCMsd2FjBbmKCZjx8X0l1BxIhUQAqLicismDo0UHE5cjVuIlppKl3NVJBX3mUIrmKovI y1TUhsBy4uKdpliTORTApOmOIsbEPbd1SYESGWIK8EPLTohq1uWUhYI2ligizN4GiBnQWEC0oArI DhaWFp3UyqbWvggzIGA5A6phUbCgtN5SLv2pX/BlRljmMW6hzQXnAoy9lmrLC0dFmNlQIzTxgOi4 mhFiVsj/AigtL9VTHWAVmJR5CoKxiZ77yZSUC8+5gol9z2ouHGNAsbcDVuGLCBgMLSVjhmVESR3m R46ARSRMzo4TNFAzl+m55DTxutBkDKJfiVmJaYFhZorh7ikqVIVsD2sXChExtBi1TYVhTLI2FXL8 nwa4U6hF5jcF0jVUUG4+wiS0lptLtIhzSMfE1lJeSKhy4kcltJlPmCPQEXGkqszLfJKwewY8dhQR KGyDYJzTMekORWVBcUlMwoJl1ZWZzwpgSKjMoKnMxj4KsU7Jschx2VBZIiFkBiVxiWHfraXmRXMu KTHFNgOSJUGBHzgVXl623gaKZAwxDNwejKKeTBoZBQyogIajB5oJpFNLP8mZhfe4tQkwoC+5BzA9 fjdpZhmQyN4G8FBDJkx7CUCTv8kk8PDdY3mnJxInuRO2Gskyx2FpqQuuNWqiVPtDrRLjBU4OY0oX Qu+o44XGSGkbqJghZDHaidEL4apZDthhP1UZCrTFDsO4wyTB0nRCyFqHTCpJqSTxomOFpjkmaFmG FdpYVJLRUMEKiJ1GDNDuNqFpReXwvMsO6GgthoqGucptQ0UY4XmGFpkRMcLIWL8hLJKIERIil+Yu 3kBaAy/0CPefnEZx3GBTBSF9f7yCJEBfUEMVIZvJ7R/n+JQfgRjWIiwH275e2B7FJ+pI/L5LBUXn 5n6xS+hInAV1tJEpP+/oCVMiQw9ZtkkfoWLA/4L9Nu4CTwGOdLriFatV7FhgkDjVE1u+jNsLP4g4 wxpbMVCo9z3BUw7uxyGc4DHUbcfUrOMOp/YgVCDidCuZQTJm6ttjWEDYWagRebWPZooS6vKADFVO /nySD9EowjzFFJzYaTpmaSo7G40eB3UzaR3DBwLjL2ZfuFEzUbDN9/FSfzBOCVRI/zWAHH/ci1eG otSWVbgMx3ZOwf6A8CR4HbKJ8MN+0qNfbkQpOB2NAjoXiHiRVSAgHU6FRt8fG9hmEFZMr9y7HAzz uW79jtrOwkaEHAW8u3nobdqzLj9tOxunNgHd3Tu4ot8OjkWm57mBKsYB1kadH7nY2iyMKSJqNmD1 1OR18S0VS8lIiXdy85conwA2Abr/ewDx1gCiAwSEhCqq7As0JRhXfKJJfAEDybDMhxJFSuJth5Tb zb9zvl4mRd7qp3+TR/lynxSWRq3qCRtto5hIkJHrtoQOoOI5MaKpMO1sLesZa0t4oVsB1jESVOiu zjT/dkA8EAam3MCnJAS94cxcTccT3mY/uOESHvUw8jyKhiZ5noUlTHhCZqKFvQOY5IN/n/QHmA4i oW1ukCDHaBDeRJ/jPA4GRUkdV2GdCIHmeilRcJkhgpyKfiCM6S1brcUjyvGvEECNG0h3YQzDVeCz ORImVrwH8Rd15lp4nb7eQrknE4J0kwmYVqkCMd1JvDU26HzQDajc1eNiDukRgSAXNT1AhlzMTAGY TDCODCyL/4ikR1TMdOWNUmQhkIsPbvyrwRUyYRpKY7nZrpakjgkXufR4gbD7HA+IvBwRkC1KAIge kLmBx3HAcwZQ0h6nyO7IHGAfyPjVvxnoPiQ0JHqKgyyQmG8QyQrxRYXgKOgZRCYdA1fL/6CpKNto DGLFoJQAPZySStkSXA3XayLG9kefkGwhABZAWPcdhWdYVIRgRbJbfsLCCSXM0Ah5qhD3gYw6TL6A WMIQcpqqoQUx1giGRYwd0rzNkUPwiuHAhJwkMcCMJ+kg8kw4Z8kWC9OBFAzCtAYkUuMmSGSiJ4vs UFKADjoQ1k3iyrSTIETSArFg6EcrJDAaymxIYZRZI7DrWeR8BbHUvP0W+8SuZCGNQeZnr4pC6hxV C6ABc3DIP/AGSVwoTAumndDNpOQJYpFwQS9IJjaxiUAqf9/aCPrBCNhsyEvpTmwDCGmftNBuV6JD ceBojy4HnVECLDWxFblZmYkDfdgeW9QFqEHNkmPEy94GlkIqLyxLSqBkzL0DyFES+8UT4JpnmPSk gr6wRyLwL6Ug+DwvQ4DQdwGQQz3yigwqAtBjcY2fRRAdpJT961cQL8W4riBbC/YeyBgNPz9TnxPg cTPPPAZxDDAjjroMmiyhBgIVEqyXPishVU2muGYVgMZrh+AHMSKtdRY6ObBUBfXake0RIORilsZZ nhTxA4szM12ILKMYkUtO7q4zVsI2AmIzOiy2ZDwmTzwAAAAADyTnBBZJp4qwsvyYLmAYchGKgmAY huB0rQSiyQHuVEwJIOABQIeLKeYmQ0x4bKJ0NltXTZGAFRxM0sEoADdTgkQQH5U2ZaMzGQFCikaU zWNQwivWgn3Nh+JtLygykplpsZCDiXkzxF5iS/wvTyXGoQsxcQMsUKfxNZSUKwrBFtXuFzS21K16 vkJ2GSMTIxXQ3FLrrwVOctRoSIXnEgBWeZrOpvFeLE6HS8EXH/WNZA9xpQFlDXaOTJEUIbRpHBHk fMpmfo6EdEg+8Y5epq0Aj2oICORHTgLocD/8XckU4UJAzH/nkA== --===============2267914973253346943==--