From: Roy Lyseng Date: January 24 2011 11:26am Subject: Re: bzr commit into mysql-trunk branch (roy.lyseng:3327) Bug#57431 List-Archive: http://lists.mysql.com/commits/129428 Message-Id: <4D3D61D0.5020100@oracle.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Hi Tor, thank you for the review. On 24.01.11 12.17, Tor Didriksen wrote: > 2011/1/21 Roy Lyseng > >> #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. >> > > This comment is not quite correct anymore? Right... I validated the file commit message, but forgot about this :( > > >> >> 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. >> + */ >> > > Consider using a // comment instead. Ok. > > >> + last_tab->last_inner= NULL; >> } >> } >> else >> >> >> >> -- >> MySQL Code Commits Mailing List >> For list archives: http://lists.mysql.com/commits >> To unsubscribe: >> http://lists.mysql.com/commits?unsub=didrik@stripped >> > Roy