2011/1/21 Roy Lyseng <roy.lyseng@stripped>
> #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?
>
> 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.
> + 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=1
>
