From: Roy Lyseng Date: January 24 2011 11:57am Subject: bzr push into mysql-trunk branch (roy.lyseng:3326 to 3327) Bug#57431 List-Archive: http://lists.mysql.com/commits/129435 X-Bug: 57431 Message-Id: <20110124115753.652A81F2@tyr67.norway.sun.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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 3326 Jorgen Loland 2011-01-13 Recorded test results after merge trunk->opt-backporting modified: mysql-test/r/innodb_icp_all.result mysql-test/r/join_outer_jcl6.result mysql-test/r/myisam_icp_all.result mysql-test/r/select_all_jcl6.result mysql-test/r/select_icp_mrr_jcl6.result mysql-test/r/select_none_jcl6.result mysql-test/r/subquery_all.result mysql-test/r/subquery_all_jcl6.result mysql-test/r/subquery_nomat_nosj_jcl6.result mysql-test/r/subquery_none_jcl6.result mysql-test/r/subquery_sj_mat_nosj.result mysql-test/r/subquery_sj_none_jcl6.result mysql-test/r/subquery_sj_none_jcl7.result === 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 No bundle (reason: useless for push emails).