From: Roy Lyseng Date: January 21 2011 4:02pm Subject: bzr commit into mysql-trunk branch (roy.lyseng:3327) Bug#57431 List-Archive: http://lists.mysql.com/commits/129354 X-Bug: 57431 Message-Id: <20110121160252.4EBE51F2@tyr67.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============0590727768008353247==" --===============0590727768008353247== 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-21 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 temporarily deleting the outerjoin information from the join_tab representing 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-21 16:02:32 +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-21 16:02:32 +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-21 16:02:32 +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-21 16:02:32 +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-21 16:02:32 +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-21 16:02:32 +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-21 16:02:32 +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-21 16:02:32 +0000 @@ -17078,6 +17078,10 @@ 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 --===============0590727768008353247== 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: 8f8bba2ba4567c484b287aa3bac6bdc819bc89b7 # timestamp: 2011-01-21 17:02:52 +0100 # base_revision_id: jorgen.loland@stripped\ # w51w9ht4de4svr2p # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWUq/IgsAB+JfgBQwWPf//3/3 34C////wYA2vHvt9m9GkUKAAA9gCsA06BrlI20Ui2GTCUkaRk00hmTJiTNAEZGTARpkZAGmhw00y MRhNMBDAJphGCYmQ0yNDQEKjT1NigNNGg0yZGTIAAAaGmTIGgkSiajanlB5Q00GTIAaaDQAAAACK QiZDI0TTJpkSejTEj1MjI9NIyYyj1NHlAkiBAI0xACaNRiEg8p6g2kNNDRobSLQjSkq1VfEg6xpE UcYU06pRnW45YeCvLHukkJfre8MDo8I6x7FtXClJc0a0iodO+fnmSimqxa9oxoUI75iygYsX5SFd mww4mRx2DlqxSKww5+TaDPHeXWusJW0ptXQnnFgSakQwRHKxp4YGB7RXMlJQBsAXAKwGNMYMG18Z OipzGmx9ah98kWZC3mQOFVYtoVO/2JPFt1cqfhJK0C7ruS/I7CzrRPQLXukt6+U89q+5fisLZjIw W1eJfDBaqKqqUzpXClKcwMmiQMRMggCISoiHAwKCOIRrCNUC0UfzwcjolWl3S5dsMca9eEbZRu7g Q1QJVEWXyg7JmTbZ11ASjVHgBCMGvbsA08AKkHC/4TZ4XnzAy/suxfosCyWSzXgWvHnvjToz1Tzg GG63wi2udVUlstMz5w/BPhW7EAqqBYv48iuxcj0tyysGIhFGBSELbgEVyyY8eWnSDECh1jpIBjif kuyatx3LJeRYVM+ZYHNjF9m8CNS5+6Bgf17OrFA78J8vLmWpb1vdt+/8CJz0HCcgZQuGar17jksB Pd7T4LkFAzxPmW36JecuRuoavOnoKCVxRZCuVeLvq9H2wzhjDAFtW0tC9EzHAxFcsUTnOc7TxYLV 7eTmAuXLlygY3LK1mwwarEAiHPOJ+RShuKgMAZjmY6QQZjCkZuApEEkrWFtW8oPMIkwRNyJY5XEq ITGqGGFaD2giJ8ZIsLj6lREjUQvIF8i8RNcSZccyJQvMhz4FkoGJT6nj5gy6k4xYbiSycKgv6VqW +quNProgZwHXQWwiRKa2IQORwzP2SAWheOjWyVRXkCLSy2YIvLipYe2UuUnfXx1z1ZYVpitwkQZL /Cm1KxGIQRscCZUfXHIwvqXMiXlV4wItLSI6XAV9g0z6UiTHDtcXRFyIAVbm2AbANRgcNZETYGRv PMZHlxjfmDYtxnJIlORaygJrcVvKGJFKw2nEnoCKysrW07FCoEal1stNBNccnKa/iaiZQv3dSIX8 3BbcGEwtAwGFV3OXiayvcWr0PcV+pI2Q3962DQTpmPbxfgUmciIVkANRaJESwtB0YTOZr0Kt5aVL ud6jkRWu3Ayeat6ipiNjIg1M4EOg5rLjWbzIkaykCh4bKxbOmSLI3dkpmC2bWESzrgFQjgTII26P YOaUJkDQmBWQHDabTA7qZjO5taChAwHHgdkwsChhtrLzkWi7+Ni/QZUrbSwY1tmMazA5mX0WzRWF 5ci3ZNWouMrUrtR/QRnYYGuus5QDA2HmLtQuJnu1lxMnQXp3MVcRBFoIvg6LxxjYK6wiCMTaWEQM SQ4bTLQkbC+RQpNzMgCPCAQiaugzmLaomN2AMgZYSGxKGJYYFxaTIWWR8is4lFWFrA8AKndi8TgR kWBqU2FaPVHDcZG8s6fwtgbTUKl4jWTHwUTTIiS3n6iRPI4Xmxy/MRmQPgaFZeRkWG/GJuxKzqsC suwMimZh7Eqh6hjvzMSJRswxE5GRQ3kqyQT+F5I0qCRQ0tH0zvNtWTlCZIkOTKnNcfgrnFfnexUQ qiRZOVlIhc4xLAtKDbvC4wMyauKGOKbUOSJmoYvvkxevdeBCUnCt3jo4PjsinkwYMgqZUgIalTzQ TSKqnHA2xccCrEmKQXCg1AMG7TU2NoaLYFsGaQwwfAWYG/2gevppJ3uIPUie1E8RcyGcfss1uFpR b4gSD5C+CJRaoPb2rWLQXv+KvsFotwta7kSoWQuWaJ9RcguZZC+Itq/1A3LE1xi/ZfJbQKu5fqLI WaL3CwBcB6ETILNcgGoWVoseKyWAJqwLULAid61cAvkuoWawti2C2LnF8hdqzF2wLgvvXULxQuQW xbRZrlRMgshZNmkXStAKClF7B5ugDKBD9QT1HZQiRKRANYNQ/P41BQoA/MEgtSHtRQRimMRFkQYb dPIWCDpHB5k5hVK5znPORKcskhqKys7+wFrqKj4yLTXWp9DG3H/D89ewCrtBEIjHWt1zC1a1rYuM UgpB6yi+3i20p+UHGGMm4CoqeT3qth3cYl1PvhEuOZqIHicyhWdOx9w5aIOh95ItBFZMmcLGLBzc WXm9j6NBCXk++ADFdWXh0SD+yUYR6iik5uN4I2Hbad2sO5xN3tJm8gcDQORgZ/RlqP7hM0N5tfl0 Un9QTglXI86wA6f8kYr2aFySzscBmO7J2D8AOhI7bIHvw3FDPmTNxcIh0LREOJhMqQEQ6lhn7PZ5 MMwgsO3Y8V2OJt23rGl/4nbadjFB5i1Griepu3LQ1H45lp3bjAiIgiIFK927BK5oL7BKcYEKsqy/ YvGBLiLI1yKxjMs3a4WsQ29jAVa81IiXnY7mo7dpHvA4AaavcwDx3AbwxSPKZwuu5XaFh5XngCJk WiJMBasudVSdI0zA7bIi5dgHY5lfA9dbHx86LdBsELdXNtX5uWeaSwWnJQSOGBTwCRISPs4UQQgn EdWMa4sO10LvGMt6XEULGA8YxElVrst6VfcyAeCBSYDe29gVJoCfuDkLoew7ZnDmN0O/qqIoekyF ZEoP7j1KHCwjU49RmVqaBzXq2IOHp9QPQBxFgtzdYEGO0CHAiT+c8jibisF4LsM6EQPU9VKl4mSG CrMq0oWrjbrSPZeNeIIRpvIHkyBsdm+s0uFgmJ1fI1hX1sFBrl7DpipSgUAoSTCZhXLHfUcAzbfD 5IBpgjM4NXjqQd0iMCoB3teSHeZzMEQMECccDoM3hRSmms0WmnPbVCJCJkO3m347UthgTAspslEZ atKnGpfI98ogbT4OB7xd3BGQLenPR72Bx3HAc1DENAdJzQRznVZxZ8x1Er1OsazRoRgjmDQjcNIH mGmYhoFYcgaev/Jthi15QIMIMoLMA7ZVKV0h5Lib79CLHBke32huIQAWQFz3nYVvhbGxCMCTZLf8 i4gkl0GkpofADGHW4woCxhCDlVddEEqpRBEdhYwfoHql8SdLXZ2YYHYGTfg1rJQG9UThqQHiKVAE qBsVADLBjwNNJC0GVJcLNqmBJkiRkrlSHGrCCVqBYLm6EdLZDAbSq1IYYIsI8R1tPM94tzqXp0u2 8W6ESDgDoNXDuUfMG5xPKAF0cekO4CFbhnWBdWykkRoN4Lgp03hRZTYNkGBiBs8PlJE4Th0C+/Fw QBAkVnfWhsbkqI49pVmlz4nnKIEWGuiK7ZbmYkDhfqPbwUBZiDoyTHsNPSBohEtLzIuhxEMQ9Ac4 0F9g0Opis9JKxUMccs03l4F9ihBK5JAR1SkBCE9W2qiDVWBaDHAxt/kogO0kp+qz5gYYtzXMC6GG 4+1AwGz4/A68z3HM0001DOIYYEc9tDJosoIHrJWE+vNZCrquNsNAsAY0XL5AdRIr21lr9WAtAvx5 VO2gobzBdcOo9Fm4DdEREXYA6aUoUSqt6sDc7EWhidKjIrSHQxFFDbbbbbbbMzXA7s00CnirS3DJ gV45CMVBMAxDgDpXAlFkgPJUmBJByAKCHiy0FQTTHhupOjZT3rw3SgBE5miWpKAA3Y5JEAD51W5a 9DHblQCtTSIJmuqcRbRBPubj9xvMChlJTLjcyENxeVnOPQK/u9Ppd1ojqHcBpwRr7DhLDE5DLZzD yLssckrPWMoIUwNJneU4jnBLZsgT7tyr04gimhsSI6zoQAuPQ3HicxaxcgRmdjtefxysIHczQFuT GPgwGtlFCGxyHBHofIqmfe6EVpBxDLuAzemYgRdKqsoV40j/F3JFOFCQSr8iCw== --===============0590727768008353247==--