Reviewers: I'll commit a mini-refactoring patch once I have a 'go' on this fix.
It will rename a few variables in setup_sj...() like 'i', 'j' etc to names with
meaning.
On 08/13/2010 02:49 PM, Jorgen Loland wrote:
> #At file:///export/home/jl208045/mysql/mysql-next-mr-opt-backporting-49129/ based on
> revid:guilhem.bichot@stripped
>
> 3228 Jorgen Loland 2010-08-13
> Bug#49129 - Wrong result with IN-subquery with join_cache_level=6
> and firstmatch=off
>
> Patch based on contribution from Sergey Petrunia.
>
> Consider the query:
>
> SELECT * FROM t0 WHERE t0.a IN (
> SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
>
> With join cache level 6, this query only returns the first tuple
> from t0 that has a match in the subquery. Consider the relevant part
> of EXPLAIN:
>
> t0 | Using where |
> t1 | Start temporary; Using join buffer |
> t2 | Using where; End temporary; Using join buffer |
>
> When the optimizer decides to use join buffers, temporary tables
> created for duplicate weedout should extend to the first table
> after const tables. I.e., Start temporary should be printed for
> t0 above.
>
> The reason for the bug is that setup_semijoin_dups_elimination()
> is called before the final decision is made in
> check_join_cache_usage() on whether or not to use join buffering.
> In this case, use_join_buffer==false for t1 and t2 during
> setup_semijoin_dups_elimination(), and the range of tables to
> buffer is therefore not extended to t0.
>
> Since check_join_cache_usage() needs to know if duplicate weedout
> is used, so moving setup_semijoin_dups_elimination() from before
> check_join_cache_usage() to after it is not possible.
>
> The temporary fix of this patch is to use a rough estimate of
> whether join buffering will be used in
> setup_semijoin_dups_elimination(). This rough test covers more
> cases than actually end up with join buffering, and in these cases
> we now extend the temporary table to store rowids for more tables
> than strictly required, i.e., the first table up to the start of
> the semijoin. A proper (but much more costly to do) fix would be to
> merge the loops of setup_semijoin_dups_elimination() and
> make_join_readinfo() (which calls check_join_cache_usage()).
> @ mysql-test/include/subquery_sj.inc
> Added test for BUG#49129
> @ mysql-test/r/subquery_sj_all.result
> Added test for BUG#49129 and recorded new explain output for queries using
> duplicate weedout.
> @ mysql-test/r/subquery_sj_all_jcl6.result
> Added test for BUG#49129 and recorded new explain output for queries using
> duplicate weedout.
> @ mysql-test/r/subquery_sj_all_jcl7.result
> Added test for BUG#49129 and recorded new explain output for queries using
> duplicate weedout.
> @ mysql-test/r/subquery_sj_dupsweed.result
> Added test for BUG#49129 and recorded new explain output for queries using
> duplicate weedout.
> @ mysql-test/r/subquery_sj_dupsweed_jcl6.result
> Added test for BUG#49129 and recorded new explain output for queries using
> duplicate weedout.
> @ mysql-test/r/subquery_sj_dupsweed_jcl7.result
> Added test for BUG#49129 and recorded new explain output for queries using
> duplicate weedout.
> @ mysql-test/r/subquery_sj_firstmatch.result
> Added test for BUG#49129 and recorded new explain output for queries using
> duplicate weedout.
> @ mysql-test/r/subquery_sj_firstmatch_jcl6.result
> Added test for BUG#49129 and recorded new explain output for queries using
> duplicate weedout.
> @ mysql-test/r/subquery_sj_firstmatch_jcl7.result
> Added test for BUG#49129 and recorded new explain output for queries using
> duplicate weedout.
> @ mysql-test/r/subquery_sj_loosescan.result
> Added test for BUG#49129 and recorded new explain output for queries using
> duplicate weedout.
> @ mysql-test/r/subquery_sj_loosescan_jcl6.result
> Added test for BUG#49129 and recorded new explain output for queries using
> duplicate weedout.
> @ mysql-test/r/subquery_sj_loosescan_jcl7.result
> Added test for BUG#49129 and recorded new explain output for queries using
> duplicate weedout.
> @ mysql-test/r/subquery_sj_mat.result
> Added test for BUG#49129 and recorded new explain output for queries using
> duplicate weedout.
> @ mysql-test/r/subquery_sj_mat_jcl6.result
> Added test for BUG#49129 and recorded new explain output for queries using
> duplicate weedout.
> @ mysql-test/r/subquery_sj_mat_jcl7.result
> Added test for BUG#49129 and recorded new explain output for queries using
> duplicate weedout.
> @ mysql-test/r/subquery_sj_mat_nosj.result
> Added test for BUG#49129 and recorded new explain output for queries using
> duplicate weedout.
> @ mysql-test/r/subquery_sj_none.result
> Added test for BUG#49129 and recorded new explain output for queries using
> duplicate weedout.
> @ mysql-test/r/subquery_sj_none_jcl6.result
> Added test for BUG#49129 and recorded new explain output for queries using
> duplicate weedout.
> @ mysql-test/r/subquery_sj_none_jcl7.result
> Added test for BUG#49129 and recorded new explain output for queries using
> duplicate weedout.
> @ sql/sql_select.cc
> setup_semijoin_dups_elimination() relied on
> best_position[i].use_join_buffer when checking whether temporary
> tables for duplicate weedout should be extended to the first
> table of the execution plan. However, use_join_buffer is not
> reliable at this stage, so setup_semijoin_dups_elimination()
> needs to use a rough estimate instead.
>
> modified:
> mysql-test/include/subquery_sj.inc
> mysql-test/r/subquery_sj_all.result
> mysql-test/r/subquery_sj_all_jcl6.result
> mysql-test/r/subquery_sj_all_jcl7.result
> mysql-test/r/subquery_sj_dupsweed.result
> mysql-test/r/subquery_sj_dupsweed_jcl6.result
> mysql-test/r/subquery_sj_dupsweed_jcl7.result
> mysql-test/r/subquery_sj_firstmatch.result
> mysql-test/r/subquery_sj_firstmatch_jcl6.result
> mysql-test/r/subquery_sj_firstmatch_jcl7.result
> mysql-test/r/subquery_sj_loosescan.result
> mysql-test/r/subquery_sj_loosescan_jcl6.result
> mysql-test/r/subquery_sj_loosescan_jcl7.result
> mysql-test/r/subquery_sj_mat.result
> mysql-test/r/subquery_sj_mat_jcl6.result
> mysql-test/r/subquery_sj_mat_jcl7.result
> mysql-test/r/subquery_sj_mat_nosj.result
> mysql-test/r/subquery_sj_none.result
> mysql-test/r/subquery_sj_none_jcl6.result
> mysql-test/r/subquery_sj_none_jcl7.result
> sql/sql_join_cache.cc
> sql/sql_select.cc
> === modified file 'mysql-test/include/subquery_sj.inc'
> --- a/mysql-test/include/subquery_sj.inc 2010-08-12 09:02:11 +0000
> +++ b/mysql-test/include/subquery_sj.inc 2010-08-13 12:49:50 +0000
> @@ -2790,3 +2790,30 @@ eval explain $query;
> eval $query;
>
> drop table t1,t2,t3;
> +
> +--echo #
> +--echo # BUG#49129: Wrong result with IN-subquery with join_cache_level=6
> +--echo # and firstmatch=off
> +--echo #
> +
> +CREATE TABLE t0 (a INT);
> +CREATE TABLE t1 (a INT, b INT, KEY(a));
> +CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
> +
> +INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
> +INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(3,11);
> +INSERT INTO t2 VALUES (0,0),(1,1),(2,2),(3,11),(4,4);
> +
> +--echo
> +--echo # Should contain records 0..3
> +SELECT * FROM t0 WHERE t0.a IN
> + (SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
> +
> +--echo
> +EXPLAIN
> +SELECT * FROM t0 WHERE t0.a IN
> + (SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
> +
> +DROP TABLE t0, t1, t2;
> +
> +--echo # End BUG#49129
>
> === modified file 'mysql-test/r/subquery_sj_all.result'
> --- a/mysql-test/r/subquery_sj_all.result 2010-08-12 11:48:10 +0000
> +++ b/mysql-test/r/subquery_sj_all.result 2010-08-13 12:49:50 +0000
> @@ -2577,8 +2577,8 @@ explain select *
> from t0 where a in
> (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t0 ALL NULL NULL NULL NULL 10
> -1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Start temporary; Using join buffer
> (BNL, regular buffers)
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Start temporary
> +1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Using join buffer (BNL, regular
> buffers)
> 1 PRIMARY t2 ref a a 5 test.t1.a 1 Using index
> 1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; End temporary
> drop table t0, t1,t2,t3;
> @@ -4507,4 +4507,33 @@ a
> 1
> 1
> drop table t1,t2,t3;
> +#
> +# BUG#49129: Wrong result with IN-subquery with join_cache_level=6
> +# and firstmatch=off
> +#
> +CREATE TABLE t0 (a INT);
> +CREATE TABLE t1 (a INT, b INT, KEY(a));
> +CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
> +INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
> +INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(3,11);
> +INSERT INTO t2 VALUES (0,0),(1,1),(2,2),(3,11),(4,4);
> +
> +# Should contain records 0..3
> +SELECT * FROM t0 WHERE t0.a IN
> +(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
> +a
> +0
> +1
> +2
> +3
> +
> +EXPLAIN
> +SELECT * FROM t0 WHERE t0.a IN
> +(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 5 Using where
> +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1
> +1 PRIMARY t1 ref a a 5 test.t2.a 2 Using index condition; Using where;
> FirstMatch(t2)
> +DROP TABLE t0, t1, t2;
> +# End BUG#49129
> set optimizer_switch=default;
>
> === modified file 'mysql-test/r/subquery_sj_all_jcl6.result'
> --- a/mysql-test/r/subquery_sj_all_jcl6.result 2010-08-12 11:48:10 +0000
> +++ b/mysql-test/r/subquery_sj_all_jcl6.result 2010-08-13 12:49:50 +0000
> @@ -2581,8 +2581,8 @@ explain select *
> from t0 where a in
> (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t0 ALL NULL NULL NULL NULL 10
> -1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Start temporary; Using join buffer
> (BNL, incremental buffers)
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Start temporary
> +1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Using join buffer (BNL, incremental
> buffers)
> 1 PRIMARY t2 ref a a 5 test.t1.a 1 Using index
> 1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; End temporary
> drop table t0, t1,t2,t3;
> @@ -4052,8 +4052,8 @@ explain extended select * from t0
> where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and
> t1.b=t2.b);
> id select_type table type possible_keys key key_len ref rows filtered Extra
> -1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00 Using where
> -1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00 Start temporary; Using join buffer (BKA,
> incremental buffers)
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00 Using where; Start temporary
> +1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00 Using join buffer (BKA, incremental
> buffers)
> 1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 100.00 Using where; End
> temporary; Using join buffer (BKA, incremental buffers)
> Warnings:
> Note 1276 Field or reference 'test.t0.a' of SELECT #2 was resolved in SELECT #1
> @@ -4064,11 +4064,12 @@ create temporary table tmp select * from
> (select t1.a from t1, t2 where t2.a=t0.a and t1.b=t2.b);
> create temporary table tmp_as_ref (a int);
> insert into tmp_as_ref values(0),(1),(2),(3);
> -
> -# The result below is wrong due to Bug#49129
> select * from tmp;
> a
> 0
> +1
> +2
> +3
> drop table t0, t1, t2, tmp, tmp_as_ref;
> CREATE TABLE t1 (
> id int(11) NOT NULL,
> @@ -4510,6 +4511,35 @@ a
> 1
> 1
> drop table t1,t2,t3;
> +#
> +# BUG#49129: Wrong result with IN-subquery with join_cache_level=6
> +# and firstmatch=off
> +#
> +CREATE TABLE t0 (a INT);
> +CREATE TABLE t1 (a INT, b INT, KEY(a));
> +CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
> +INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
> +INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(3,11);
> +INSERT INTO t2 VALUES (0,0),(1,1),(2,2),(3,11),(4,4);
> +
> +# Should contain records 0..3
> +SELECT * FROM t0 WHERE t0.a IN
> +(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
> +a
> +0
> +1
> +2
> +3
> +
> +EXPLAIN
> +SELECT * FROM t0 WHERE t0.a IN
> +(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 5 Using where
> +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 Using join buffer (BKA,
> incremental buffers)
> +1 PRIMARY t1 ref a a 5 test.t2.a 2 Using index condition; Using where;
> FirstMatch(t2); Using join buffer (BKA, incremental buffers)
> +DROP TABLE t0, t1, t2;
> +# End BUG#49129
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
> show variables like 'optimizer_join_cache_level';
>
> === modified file 'mysql-test/r/subquery_sj_all_jcl7.result'
> --- a/mysql-test/r/subquery_sj_all_jcl7.result 2010-08-12 11:48:10 +0000
> +++ b/mysql-test/r/subquery_sj_all_jcl7.result 2010-08-13 12:49:50 +0000
> @@ -2581,8 +2581,8 @@ explain select *
> from t0 where a in
> (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t0 ALL NULL NULL NULL NULL 10
> -1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Start temporary; Using join buffer
> (BNL, regular buffers)
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Start temporary
> +1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Using join buffer (BNL, regular
> buffers)
> 1 PRIMARY t2 ref a a 5 test.t1.a 1 Using index
> 1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; End temporary
> drop table t0, t1,t2,t3;
> @@ -4052,8 +4052,8 @@ explain extended select * from t0
> where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and
> t1.b=t2.b);
> id select_type table type possible_keys key key_len ref rows filtered Extra
> -1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00 Using where
> -1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00 Start temporary; Using join buffer
> (BKA_UNIQUE, regular buffers)
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00 Using where; Start temporary
> +1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00 Using join buffer (BKA_UNIQUE, regular
> buffers)
> 1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 100.00 Using where; End
> temporary; Using join buffer (BKA_UNIQUE, regular buffers)
> Warnings:
> Note 1276 Field or reference 'test.t0.a' of SELECT #2 was resolved in SELECT #1
> @@ -4064,11 +4064,12 @@ create temporary table tmp select * from
> (select t1.a from t1, t2 where t2.a=t0.a and t1.b=t2.b);
> create temporary table tmp_as_ref (a int);
> insert into tmp_as_ref values(0),(1),(2),(3);
> -
> -# The result below is wrong due to Bug#49129
> select * from tmp;
> a
> 0
> +1
> +2
> +3
> drop table t0, t1, t2, tmp, tmp_as_ref;
> CREATE TABLE t1 (
> id int(11) NOT NULL,
> @@ -4510,6 +4511,35 @@ a
> 1
> 1
> drop table t1,t2,t3;
> +#
> +# BUG#49129: Wrong result with IN-subquery with join_cache_level=6
> +# and firstmatch=off
> +#
> +CREATE TABLE t0 (a INT);
> +CREATE TABLE t1 (a INT, b INT, KEY(a));
> +CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
> +INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
> +INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(3,11);
> +INSERT INTO t2 VALUES (0,0),(1,1),(2,2),(3,11),(4,4);
> +
> +# Should contain records 0..3
> +SELECT * FROM t0 WHERE t0.a IN
> +(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
> +a
> +0
> +1
> +2
> +3
> +
> +EXPLAIN
> +SELECT * FROM t0 WHERE t0.a IN
> +(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 5 Using where
> +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 Using join buffer (BKA_UNIQUE,
> regular buffers)
> +1 PRIMARY t1 ref a a 5 test.t2.a 2 Using index condition; Using where;
> FirstMatch(t2); Using join buffer (BKA_UNIQUE, regular buffers)
> +DROP TABLE t0, t1, t2;
> +# End BUG#49129
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
> show variables like 'optimizer_join_cache_level';
>
> === modified file 'mysql-test/r/subquery_sj_dupsweed.result'
> --- a/mysql-test/r/subquery_sj_dupsweed.result 2010-08-12 11:48:10 +0000
> +++ b/mysql-test/r/subquery_sj_dupsweed.result 2010-08-13 12:49:50 +0000
> @@ -1686,8 +1686,8 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
> (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t2 system NULL NULL NULL NULL 1
> -1 PRIMARY t1 ALL NULL NULL NULL NULL 2
> -1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary
> +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Start temporary
> +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; End temporary
> SELECT * FROM t1 WHERE (t1.i) IN
> (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
> i
> @@ -1727,8 +1727,8 @@ EXPLAIN SELECT * FROM t1 WHERE (11) IN
> (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t2 system NULL NULL NULL NULL 1
> -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
> -1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary
> +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; Start temporary
> +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; End temporary
> SELECT * FROM t1 WHERE (11) IN
> (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
> i
> @@ -1833,8 +1833,8 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
> (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t3 system NULL NULL NULL NULL 1
> -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
> -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary
> +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; Start temporary
> +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; End temporary
> SELECT * FROM t1 WHERE (t1.i) IN
> (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
> i
> @@ -2576,8 +2576,8 @@ explain select *
> from t0 where a in
> (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t0 ALL NULL NULL NULL NULL 10
> -1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Start temporary; Using join buffer
> (BNL, regular buffers)
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Start temporary
> +1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Using join buffer (BNL, regular
> buffers)
> 1 PRIMARY t2 ref a a 5 test.t1.a 1 Using index
> 1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; End temporary
> drop table t0, t1,t2,t3;
> @@ -4506,4 +4506,33 @@ a
> 1
> 1
> drop table t1,t2,t3;
> +#
> +# BUG#49129: Wrong result with IN-subquery with join_cache_level=6
> +# and firstmatch=off
> +#
> +CREATE TABLE t0 (a INT);
> +CREATE TABLE t1 (a INT, b INT, KEY(a));
> +CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
> +INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
> +INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(3,11);
> +INSERT INTO t2 VALUES (0,0),(1,1),(2,2),(3,11),(4,4);
> +
> +# Should contain records 0..3
> +SELECT * FROM t0 WHERE t0.a IN
> +(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
> +a
> +0
> +1
> +2
> +3
> +
> +EXPLAIN
> +SELECT * FROM t0 WHERE t0.a IN
> +(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 5 Using where; Start temporary
> +1 PRIMARY t1 ALL a NULL NULL NULL 5 Using where; Using join buffer (BNL, regular
> buffers)
> +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 Using where; End temporary
> +DROP TABLE t0, t1, t2;
> +# End BUG#49129
> set optimizer_switch=default;
>
> === modified file 'mysql-test/r/subquery_sj_dupsweed_jcl6.result'
> --- a/mysql-test/r/subquery_sj_dupsweed_jcl6.result 2010-08-12 11:48:10 +0000
> +++ b/mysql-test/r/subquery_sj_dupsweed_jcl6.result 2010-08-13 12:49:50 +0000
> @@ -59,8 +59,8 @@ a
> 4
> explain extended select * from t2 where t2.a in (select a from t1);
> id select_type table type possible_keys key key_len ref rows filtered Extra
> -1 PRIMARY t2 index a a 5 NULL 4 100.00 Using where; Using index
> -1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using index; Start temporary; End
> temporary
> +1 PRIMARY t2 index a a 5 NULL 4 100.00 Using where; Using index; Start temporary
> +1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using index; End temporary
> Warnings:
> Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`)
> where (`test`.`t1`.`a` = `test`.`t2`.`a`)
> select * from t2 where t2.a in (select a from t1 where t1.b<> 30);
> @@ -69,8 +69,8 @@ a
> 4
> explain extended select * from t2 where t2.a in (select a from t1 where
> t1.b<> 30);
> id select_type table type possible_keys key key_len ref rows filtered Extra
> -1 PRIMARY t2 index a a 5 NULL 4 100.00 Using where; Using index
> -1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using where; Using index; Start
> temporary; End temporary
> +1 PRIMARY t2 index a a 5 NULL 4 100.00 Using where; Using index; Start temporary
> +1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using where; Using index; End temporary
> Warnings:
> Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`)
> where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b`<> 30))
> select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
> @@ -96,8 +96,8 @@ a
> 4
> explain extended select * from t2 where t2.a in (select a from t1 where
> t1.b<> 30);
> id select_type table type possible_keys key key_len ref rows filtered Extra
> -1 PRIMARY t2 index a a 5 NULL 4 100.00 Using where; Using index
> -1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using where; Using index; Start
> temporary; End temporary
> +1 PRIMARY t2 index a a 5 NULL 4 100.00 Using where; Using index; Start temporary
> +1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using where; Using index; End temporary
> Warnings:
> Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`)
> where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b`<> 30))
> drop table t0, t1, t2, t3;
> @@ -1690,8 +1690,8 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
> (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t2 system NULL NULL NULL NULL 1
> -1 PRIMARY t1 ALL NULL NULL NULL NULL 2
> -1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary;
> Using join buffer (BNL, incremental buffers)
> +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Start temporary
> +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer
> (BNL, incremental buffers)
> SELECT * FROM t1 WHERE (t1.i) IN
> (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
> i
> @@ -1731,8 +1731,8 @@ EXPLAIN SELECT * FROM t1 WHERE (11) IN
> (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t2 system NULL NULL NULL NULL 1
> -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
> -1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary;
> Using join buffer (BNL, incremental buffers)
> +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; Start temporary
> +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer
> (BNL, incremental buffers)
> SELECT * FROM t1 WHERE (11) IN
> (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
> i
> @@ -1837,8 +1837,8 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
> (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t3 system NULL NULL NULL NULL 1
> -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
> -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary;
> Using join buffer (BNL, incremental buffers)
> +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; Start temporary
> +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer
> (BNL, incremental buffers)
> SELECT * FROM t1 WHERE (t1.i) IN
> (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
> i
> @@ -2179,8 +2179,8 @@ create table t3 (a int);
> insert into t3 select A.a + 10*B.a from t0 A, t0 B;
> explain select * from t3 where a in (select kp1 from t1 where kp1<20);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where
> -1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 1 Using index; Start temporary; End temporary
> +1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Start temporary
> +1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 1 Using index; End temporary
> select * from t3 where a in (select kp1 from t1 where kp1<20);
> a
> 0
> @@ -2208,8 +2208,8 @@ insert into t4 select a from t3;
> explain select * from t3 where a in
> (select t1.kp1 from t1,t4 where kp1<20 and t4.pk=t1.c);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where
> -1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 1 Using where; Start temporary
> +1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Start temporary
> +1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 1 Using where
> 1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index; End temporary
> select * from t3 where a in
> (select t1.kp1 from t1,t4 where kp1<20 and t4.pk=t1.c);
> @@ -2370,12 +2370,12 @@ insert into t1 select A.a, B.a, 'filler'
> create table t2 as select * from t1;
> explain select * from t2 where a in (select b from t1 where a=3);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where
> -1 PRIMARY t1 ref a a 10 const,test.t2.a 1 Using index; Start temporary; End
> temporary
> +1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Start temporary
> +1 PRIMARY t1 ref a a 10 const,test.t2.a 1 Using index; End temporary
> explain select * from t2 where (b,a) in (select a,b from t1 where a=3);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where
> -1 PRIMARY t1 ref a a 10 const,test.t2.a 1 Using index; Start temporary; End
> temporary
> +1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Start temporary
> +1 PRIMARY t1 ref a a 10 const,test.t2.a 1 Using index; End temporary
> drop table t1,t2;
> create table t1 (a int, b int);
> insert into t1 select a,a from t0;
> @@ -2580,8 +2580,8 @@ explain select *
> from t0 where a in
> (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t0 ALL NULL NULL NULL NULL 10
> -1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Start temporary; Using join buffer
> (BNL, incremental buffers)
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Start temporary
> +1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Using join buffer (BNL, incremental
> buffers)
> 1 PRIMARY t2 ref a a 5 test.t1.a 1 Using index
> 1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; End temporary
> drop table t0, t1,t2,t3;
> @@ -4029,8 +4029,8 @@ EXPLAIN SELECT Name FROM t1
> WHERE t1.Code IN (
> SELECT t2.CountryCode FROM t2 WHERE Population> 5000000);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 31
> -1 PRIMARY t2 ref CountryCode CountryCode 3 test.t1.Code 18 Using where; Start
> temporary; End temporary
> +1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 31 Start temporary
> +1 PRIMARY t2 ref CountryCode CountryCode 3 test.t1.Code 18 Using where; End
> temporary
> SELECT Name FROM t1
> WHERE t1.Code IN (
> SELECT t2.CountryCode FROM t2 WHERE Population> 5000000);
> @@ -4051,8 +4051,8 @@ explain extended select * from t0
> where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and
> t1.b=t2.b);
> id select_type table type possible_keys key key_len ref rows filtered Extra
> -1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00 Using where
> -1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00 Start temporary
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00 Using where; Start temporary
> +1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00
> 1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 100.00 Using where; End
> temporary
> Warnings:
> Note 1276 Field or reference 'test.t0.a' of SELECT #2 was resolved in SELECT #1
> @@ -4307,8 +4307,8 @@ alter table t3 add primary key(id), add
> The following must use loose index scan over t3, key a:
> explain select count(a) from t2 where a in ( SELECT a FROM t3);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t2 index a a 5 NULL 1000 Using where; Using index
> -1 PRIMARY t3 ref a a 5 test.t2.a 30 Using index; Start temporary; End temporary
> +1 PRIMARY t2 index a a 5 NULL 1000 Using where; Using index; Start temporary
> +1 PRIMARY t3 ref a a 5 test.t2.a 30 Using index; End temporary
> select count(a) from t2 where a in ( SELECT a FROM t3);
> count(a)
> 1000
> @@ -4510,6 +4510,35 @@ a
> 1
> 1
> drop table t1,t2,t3;
> +#
> +# BUG#49129: Wrong result with IN-subquery with join_cache_level=6
> +# and firstmatch=off
> +#
> +CREATE TABLE t0 (a INT);
> +CREATE TABLE t1 (a INT, b INT, KEY(a));
> +CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
> +INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
> +INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(3,11);
> +INSERT INTO t2 VALUES (0,0),(1,1),(2,2),(3,11),(4,4);
> +
> +# Should contain records 0..3
> +SELECT * FROM t0 WHERE t0.a IN
> +(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
> +a
> +0
> +1
> +2
> +3
> +
> +EXPLAIN
> +SELECT * FROM t0 WHERE t0.a IN
> +(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 5 Using where; Start temporary
> +1 PRIMARY t1 ALL a NULL NULL NULL 5 Using where; Using join buffer (BNL, incremental
> buffers)
> +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 Using where; End temporary
> +DROP TABLE t0, t1, t2;
> +# End BUG#49129
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
> show variables like 'optimizer_join_cache_level';
>
> === modified file 'mysql-test/r/subquery_sj_dupsweed_jcl7.result'
> --- a/mysql-test/r/subquery_sj_dupsweed_jcl7.result 2010-08-12 11:48:10 +0000
> +++ b/mysql-test/r/subquery_sj_dupsweed_jcl7.result 2010-08-13 12:49:50 +0000
> @@ -59,8 +59,8 @@ a
> 4
> explain extended select * from t2 where t2.a in (select a from t1);
> id select_type table type possible_keys key key_len ref rows filtered Extra
> -1 PRIMARY t2 index a a 5 NULL 4 100.00 Using where; Using index
> -1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using index; Start temporary; End
> temporary
> +1 PRIMARY t2 index a a 5 NULL 4 100.00 Using where; Using index; Start temporary
> +1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using index; End temporary
> Warnings:
> Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`)
> where (`test`.`t1`.`a` = `test`.`t2`.`a`)
> select * from t2 where t2.a in (select a from t1 where t1.b<> 30);
> @@ -69,8 +69,8 @@ a
> 4
> explain extended select * from t2 where t2.a in (select a from t1 where
> t1.b<> 30);
> id select_type table type possible_keys key key_len ref rows filtered Extra
> -1 PRIMARY t2 index a a 5 NULL 4 100.00 Using where; Using index
> -1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using where; Using index; Start
> temporary; End temporary
> +1 PRIMARY t2 index a a 5 NULL 4 100.00 Using where; Using index; Start temporary
> +1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using where; Using index; End temporary
> Warnings:
> Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`)
> where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b`<> 30))
> select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
> @@ -96,8 +96,8 @@ a
> 4
> explain extended select * from t2 where t2.a in (select a from t1 where
> t1.b<> 30);
> id select_type table type possible_keys key key_len ref rows filtered Extra
> -1 PRIMARY t2 index a a 5 NULL 4 100.00 Using where; Using index
> -1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using where; Using index; Start
> temporary; End temporary
> +1 PRIMARY t2 index a a 5 NULL 4 100.00 Using where; Using index; Start temporary
> +1 PRIMARY t1 ref a a 5 test.t2.a 101 100.00 Using where; Using index; End temporary
> Warnings:
> Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`)
> where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b`<> 30))
> drop table t0, t1, t2, t3;
> @@ -1690,8 +1690,8 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
> (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t2 system NULL NULL NULL NULL 1
> -1 PRIMARY t1 ALL NULL NULL NULL NULL 2
> -1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary;
> Using join buffer (BNL, regular buffers)
> +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Start temporary
> +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer
> (BNL, regular buffers)
> SELECT * FROM t1 WHERE (t1.i) IN
> (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
> i
> @@ -1731,8 +1731,8 @@ EXPLAIN SELECT * FROM t1 WHERE (11) IN
> (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t2 system NULL NULL NULL NULL 1
> -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
> -1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary;
> Using join buffer (BNL, regular buffers)
> +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; Start temporary
> +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer
> (BNL, regular buffers)
> SELECT * FROM t1 WHERE (11) IN
> (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
> i
> @@ -1837,8 +1837,8 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
> (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t3 system NULL NULL NULL NULL 1
> -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
> -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary;
> Using join buffer (BNL, regular buffers)
> +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; Start temporary
> +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer
> (BNL, regular buffers)
> SELECT * FROM t1 WHERE (t1.i) IN
> (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
> i
> @@ -2179,8 +2179,8 @@ create table t3 (a int);
> insert into t3 select A.a + 10*B.a from t0 A, t0 B;
> explain select * from t3 where a in (select kp1 from t1 where kp1<20);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where
> -1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 1 Using index; Start temporary; End temporary
> +1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Start temporary
> +1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 1 Using index; End temporary
> select * from t3 where a in (select kp1 from t1 where kp1<20);
> a
> 0
> @@ -2208,8 +2208,8 @@ insert into t4 select a from t3;
> explain select * from t3 where a in
> (select t1.kp1 from t1,t4 where kp1<20 and t4.pk=t1.c);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where
> -1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 1 Using where; Start temporary
> +1 PRIMARY t3 ALL NULL NULL NULL NULL 100 Using where; Start temporary
> +1 PRIMARY t1 ref kp1 kp1 5 test.t3.a 1 Using where
> 1 PRIMARY t4 eq_ref PRIMARY PRIMARY 4 test.t1.c 1 Using index; End temporary
> select * from t3 where a in
> (select t1.kp1 from t1,t4 where kp1<20 and t4.pk=t1.c);
> @@ -2370,12 +2370,12 @@ insert into t1 select A.a, B.a, 'filler'
> create table t2 as select * from t1;
> explain select * from t2 where a in (select b from t1 where a=3);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where
> -1 PRIMARY t1 ref a a 10 const,test.t2.a 1 Using index; Start temporary; End
> temporary
> +1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Start temporary
> +1 PRIMARY t1 ref a a 10 const,test.t2.a 1 Using index; End temporary
> explain select * from t2 where (b,a) in (select a,b from t1 where a=3);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where
> -1 PRIMARY t1 ref a a 10 const,test.t2.a 1 Using index; Start temporary; End
> temporary
> +1 PRIMARY t2 ALL NULL NULL NULL NULL 100 Using where; Start temporary
> +1 PRIMARY t1 ref a a 10 const,test.t2.a 1 Using index; End temporary
> drop table t1,t2;
> create table t1 (a int, b int);
> insert into t1 select a,a from t0;
> @@ -2580,8 +2580,8 @@ explain select *
> from t0 where a in
> (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t0 ALL NULL NULL NULL NULL 10
> -1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Start temporary; Using join buffer
> (BNL, regular buffers)
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Start temporary
> +1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Using join buffer (BNL, regular
> buffers)
> 1 PRIMARY t2 ref a a 5 test.t1.a 1 Using index
> 1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; End temporary
> drop table t0, t1,t2,t3;
> @@ -4029,8 +4029,8 @@ EXPLAIN SELECT Name FROM t1
> WHERE t1.Code IN (
> SELECT t2.CountryCode FROM t2 WHERE Population> 5000000);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 31
> -1 PRIMARY t2 ref CountryCode CountryCode 3 test.t1.Code 18 Using where; Start
> temporary; End temporary
> +1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 31 Start temporary
> +1 PRIMARY t2 ref CountryCode CountryCode 3 test.t1.Code 18 Using where; End
> temporary
> SELECT Name FROM t1
> WHERE t1.Code IN (
> SELECT t2.CountryCode FROM t2 WHERE Population> 5000000);
> @@ -4051,8 +4051,8 @@ explain extended select * from t0
> where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and
> t1.b=t2.b);
> id select_type table type possible_keys key key_len ref rows filtered Extra
> -1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00 Using where
> -1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00 Start temporary
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00 Using where; Start temporary
> +1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00
> 1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 100.00 Using where; End
> temporary
> Warnings:
> Note 1276 Field or reference 'test.t0.a' of SELECT #2 was resolved in SELECT #1
> @@ -4307,8 +4307,8 @@ alter table t3 add primary key(id), add
> The following must use loose index scan over t3, key a:
> explain select count(a) from t2 where a in ( SELECT a FROM t3);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t2 index a a 5 NULL 1000 Using where; Using index
> -1 PRIMARY t3 ref a a 5 test.t2.a 30 Using index; Start temporary; End temporary
> +1 PRIMARY t2 index a a 5 NULL 1000 Using where; Using index; Start temporary
> +1 PRIMARY t3 ref a a 5 test.t2.a 30 Using index; End temporary
> select count(a) from t2 where a in ( SELECT a FROM t3);
> count(a)
> 1000
> @@ -4510,6 +4510,35 @@ a
> 1
> 1
> drop table t1,t2,t3;
> +#
> +# BUG#49129: Wrong result with IN-subquery with join_cache_level=6
> +# and firstmatch=off
> +#
> +CREATE TABLE t0 (a INT);
> +CREATE TABLE t1 (a INT, b INT, KEY(a));
> +CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
> +INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
> +INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(3,11);
> +INSERT INTO t2 VALUES (0,0),(1,1),(2,2),(3,11),(4,4);
> +
> +# Should contain records 0..3
> +SELECT * FROM t0 WHERE t0.a IN
> +(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
> +a
> +0
> +1
> +2
> +3
> +
> +EXPLAIN
> +SELECT * FROM t0 WHERE t0.a IN
> +(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 5 Using where; Start temporary
> +1 PRIMARY t1 ALL a NULL NULL NULL 5 Using where; Using join buffer (BNL, regular
> buffers)
> +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 Using where; End temporary
> +DROP TABLE t0, t1, t2;
> +# End BUG#49129
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
> show variables like 'optimizer_join_cache_level';
>
> === modified file 'mysql-test/r/subquery_sj_firstmatch.result'
> --- a/mysql-test/r/subquery_sj_firstmatch.result 2010-08-12 11:48:10 +0000
> +++ b/mysql-test/r/subquery_sj_firstmatch.result 2010-08-13 12:49:50 +0000
> @@ -2577,8 +2577,8 @@ explain select *
> from t0 where a in
> (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t0 ALL NULL NULL NULL NULL 10
> -1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Start temporary; Using join buffer
> (BNL, regular buffers)
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Start temporary
> +1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Using join buffer (BNL, regular
> buffers)
> 1 PRIMARY t2 ref a a 5 test.t1.a 1 Using index
> 1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; End temporary
> drop table t0, t1,t2,t3;
> @@ -4507,4 +4507,33 @@ a
> 1
> 1
> drop table t1,t2,t3;
> +#
> +# BUG#49129: Wrong result with IN-subquery with join_cache_level=6
> +# and firstmatch=off
> +#
> +CREATE TABLE t0 (a INT);
> +CREATE TABLE t1 (a INT, b INT, KEY(a));
> +CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
> +INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
> +INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(3,11);
> +INSERT INTO t2 VALUES (0,0),(1,1),(2,2),(3,11),(4,4);
> +
> +# Should contain records 0..3
> +SELECT * FROM t0 WHERE t0.a IN
> +(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
> +a
> +0
> +1
> +2
> +3
> +
> +EXPLAIN
> +SELECT * FROM t0 WHERE t0.a IN
> +(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 5 Using where
> +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1
> +1 PRIMARY t1 ref a a 5 test.t2.a 2 Using where; FirstMatch(t2)
> +DROP TABLE t0, t1, t2;
> +# End BUG#49129
> set optimizer_switch=default;
>
> === modified file 'mysql-test/r/subquery_sj_firstmatch_jcl6.result'
> --- a/mysql-test/r/subquery_sj_firstmatch_jcl6.result 2010-08-12 11:48:10 +0000
> +++ b/mysql-test/r/subquery_sj_firstmatch_jcl6.result 2010-08-13 12:49:50 +0000
> @@ -2581,8 +2581,8 @@ explain select *
> from t0 where a in
> (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t0 ALL NULL NULL NULL NULL 10
> -1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Start temporary; Using join buffer
> (BNL, incremental buffers)
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Start temporary
> +1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Using join buffer (BNL, incremental
> buffers)
> 1 PRIMARY t2 ref a a 5 test.t1.a 1 Using index
> 1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; End temporary
> drop table t0, t1,t2,t3;
> @@ -4052,8 +4052,8 @@ explain extended select * from t0
> where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and
> t1.b=t2.b);
> id select_type table type possible_keys key key_len ref rows filtered Extra
> -1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00 Using where
> -1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00 Start temporary
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00 Using where; Start temporary
> +1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00
> 1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 100.00 Using where; End
> temporary
> Warnings:
> Note 1276 Field or reference 'test.t0.a' of SELECT #2 was resolved in SELECT #1
> @@ -4511,6 +4511,35 @@ a
> 1
> 1
> drop table t1,t2,t3;
> +#
> +# BUG#49129: Wrong result with IN-subquery with join_cache_level=6
> +# and firstmatch=off
> +#
> +CREATE TABLE t0 (a INT);
> +CREATE TABLE t1 (a INT, b INT, KEY(a));
> +CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
> +INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
> +INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(3,11);
> +INSERT INTO t2 VALUES (0,0),(1,1),(2,2),(3,11),(4,4);
> +
> +# Should contain records 0..3
> +SELECT * FROM t0 WHERE t0.a IN
> +(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
> +a
> +0
> +1
> +2
> +3
> +
> +EXPLAIN
> +SELECT * FROM t0 WHERE t0.a IN
> +(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 5 Using where
> +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1
> +1 PRIMARY t1 ref a a 5 test.t2.a 2 Using where; FirstMatch(t2)
> +DROP TABLE t0, t1, t2;
> +# End BUG#49129
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
> show variables like 'optimizer_join_cache_level';
>
> === modified file 'mysql-test/r/subquery_sj_firstmatch_jcl7.result'
> --- a/mysql-test/r/subquery_sj_firstmatch_jcl7.result 2010-08-12 11:48:10 +0000
> +++ b/mysql-test/r/subquery_sj_firstmatch_jcl7.result 2010-08-13 12:49:50 +0000
> @@ -2581,8 +2581,8 @@ explain select *
> from t0 where a in
> (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t0 ALL NULL NULL NULL NULL 10
> -1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Start temporary; Using join buffer
> (BNL, regular buffers)
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Start temporary
> +1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Using join buffer (BNL, regular
> buffers)
> 1 PRIMARY t2 ref a a 5 test.t1.a 1 Using index
> 1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; End temporary
> drop table t0, t1,t2,t3;
> @@ -4052,8 +4052,8 @@ explain extended select * from t0
> where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and
> t1.b=t2.b);
> id select_type table type possible_keys key key_len ref rows filtered Extra
> -1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00 Using where
> -1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00 Start temporary
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00 Using where; Start temporary
> +1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00
> 1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 100.00 Using where; End
> temporary
> Warnings:
> Note 1276 Field or reference 'test.t0.a' of SELECT #2 was resolved in SELECT #1
> @@ -4511,6 +4511,35 @@ a
> 1
> 1
> drop table t1,t2,t3;
> +#
> +# BUG#49129: Wrong result with IN-subquery with join_cache_level=6
> +# and firstmatch=off
> +#
> +CREATE TABLE t0 (a INT);
> +CREATE TABLE t1 (a INT, b INT, KEY(a));
> +CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
> +INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
> +INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(3,11);
> +INSERT INTO t2 VALUES (0,0),(1,1),(2,2),(3,11),(4,4);
> +
> +# Should contain records 0..3
> +SELECT * FROM t0 WHERE t0.a IN
> +(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
> +a
> +0
> +1
> +2
> +3
> +
> +EXPLAIN
> +SELECT * FROM t0 WHERE t0.a IN
> +(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 5 Using where
> +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1
> +1 PRIMARY t1 ref a a 5 test.t2.a 2 Using where; FirstMatch(t2)
> +DROP TABLE t0, t1, t2;
> +# End BUG#49129
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
> show variables like 'optimizer_join_cache_level';
>
> === modified file 'mysql-test/r/subquery_sj_loosescan.result'
> --- a/mysql-test/r/subquery_sj_loosescan.result 2010-08-12 11:48:10 +0000
> +++ b/mysql-test/r/subquery_sj_loosescan.result 2010-08-13 12:49:50 +0000
> @@ -1687,8 +1687,8 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
> (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t2 system NULL NULL NULL NULL 1
> -1 PRIMARY t1 ALL NULL NULL NULL NULL 2
> -1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary
> +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Start temporary
> +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; End temporary
> SELECT * FROM t1 WHERE (t1.i) IN
> (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
> i
> @@ -1728,8 +1728,8 @@ EXPLAIN SELECT * FROM t1 WHERE (11) IN
> (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t2 system NULL NULL NULL NULL 1
> -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
> -1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary
> +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; Start temporary
> +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; End temporary
> SELECT * FROM t1 WHERE (11) IN
> (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
> i
> @@ -1834,8 +1834,8 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
> (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t3 system NULL NULL NULL NULL 1
> -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
> -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary
> +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; Start temporary
> +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; End temporary
> SELECT * FROM t1 WHERE (t1.i) IN
> (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
> i
> @@ -2577,8 +2577,8 @@ explain select *
> from t0 where a in
> (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t0 ALL NULL NULL NULL NULL 10
> -1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Start temporary; Using join buffer
> (BNL, regular buffers)
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Start temporary
> +1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Using join buffer (BNL, regular
> buffers)
> 1 PRIMARY t2 ref a a 5 test.t1.a 1 Using index
> 1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; End temporary
> drop table t0, t1,t2,t3;
> @@ -4511,4 +4511,33 @@ a
> 1
> 1
> drop table t1,t2,t3;
> +#
> +# BUG#49129: Wrong result with IN-subquery with join_cache_level=6
> +# and firstmatch=off
> +#
> +CREATE TABLE t0 (a INT);
> +CREATE TABLE t1 (a INT, b INT, KEY(a));
> +CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
> +INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
> +INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(3,11);
> +INSERT INTO t2 VALUES (0,0),(1,1),(2,2),(3,11),(4,4);
> +
> +# Should contain records 0..3
> +SELECT * FROM t0 WHERE t0.a IN
> +(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
> +a
> +0
> +1
> +2
> +3
> +
> +EXPLAIN
> +SELECT * FROM t0 WHERE t0.a IN
> +(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 5 Using where; Start temporary
> +1 PRIMARY t1 ALL a NULL NULL NULL 5 Using where; Using join buffer (BNL, regular
> buffers)
> +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 Using where; End temporary
> +DROP TABLE t0, t1, t2;
> +# End BUG#49129
> set optimizer_switch=default;
>
> === modified file 'mysql-test/r/subquery_sj_loosescan_jcl6.result'
> --- a/mysql-test/r/subquery_sj_loosescan_jcl6.result 2010-08-12 09:02:11 +0000
> +++ b/mysql-test/r/subquery_sj_loosescan_jcl6.result 2010-08-13 12:49:50 +0000
> @@ -1691,8 +1691,8 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
> (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t2 system NULL NULL NULL NULL 1
> -1 PRIMARY t1 ALL NULL NULL NULL NULL 2
> -1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary;
> Using join buffer (BNL, incremental buffers)
> +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Start temporary
> +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer
> (BNL, incremental buffers)
> SELECT * FROM t1 WHERE (t1.i) IN
> (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
> i
> @@ -1732,8 +1732,8 @@ EXPLAIN SELECT * FROM t1 WHERE (11) IN
> (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t2 system NULL NULL NULL NULL 1
> -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
> -1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary;
> Using join buffer (BNL, incremental buffers)
> +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; Start temporary
> +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer
> (BNL, incremental buffers)
> SELECT * FROM t1 WHERE (11) IN
> (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
> i
> @@ -1838,8 +1838,8 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
> (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t3 system NULL NULL NULL NULL 1
> -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
> -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary;
> Using join buffer (BNL, incremental buffers)
> +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; Start temporary
> +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer
> (BNL, incremental buffers)
> SELECT * FROM t1 WHERE (t1.i) IN
> (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
> i
> @@ -2581,8 +2581,8 @@ explain select *
> from t0 where a in
> (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t0 ALL NULL NULL NULL NULL 10
> -1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Start temporary; Using join buffer
> (BNL, incremental buffers)
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Start temporary
> +1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Using join buffer (BNL, incremental
> buffers)
> 1 PRIMARY t2 ref a a 5 test.t1.a 1 Using index
> 1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; End temporary
> drop table t0, t1,t2,t3;
> @@ -4034,8 +4034,8 @@ EXPLAIN SELECT Name FROM t1
> WHERE t1.Code IN (
> SELECT t2.CountryCode FROM t2 WHERE Population> 5000000);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 31
> -1 PRIMARY t2 ref CountryCode CountryCode 3 test.t1.Code 18 Using where; Start
> temporary; End temporary
> +1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 31 Start temporary
> +1 PRIMARY t2 ref CountryCode CountryCode 3 test.t1.Code 18 Using where; End
> temporary
> SELECT Name FROM t1
> WHERE t1.Code IN (
> SELECT t2.CountryCode FROM t2 WHERE Population> 5000000);
> @@ -4056,8 +4056,8 @@ explain extended select * from t0
> where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and
> t1.b=t2.b);
> id select_type table type possible_keys key key_len ref rows filtered Extra
> -1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00 Using where
> -1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00 Start temporary
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00 Using where; Start temporary
> +1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00
> 1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 100.00 Using where; End
> temporary
> Warnings:
> Note 1276 Field or reference 'test.t0.a' of SELECT #2 was resolved in SELECT #1
> @@ -4515,6 +4515,35 @@ a
> 1
> 1
> drop table t1,t2,t3;
> +#
> +# BUG#49129: Wrong result with IN-subquery with join_cache_level=6
> +# and firstmatch=off
> +#
> +CREATE TABLE t0 (a INT);
> +CREATE TABLE t1 (a INT, b INT, KEY(a));
> +CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
> +INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
> +INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(3,11);
> +INSERT INTO t2 VALUES (0,0),(1,1),(2,2),(3,11),(4,4);
> +
> +# Should contain records 0..3
> +SELECT * FROM t0 WHERE t0.a IN
> +(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
> +a
> +0
> +1
> +2
> +3
> +
> +EXPLAIN
> +SELECT * FROM t0 WHERE t0.a IN
> +(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 5 Using where; Start temporary
> +1 PRIMARY t1 ALL a NULL NULL NULL 5 Using where; Using join buffer (BNL, incremental
> buffers)
> +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 Using where; End temporary
> +DROP TABLE t0, t1, t2;
> +# End BUG#49129
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
> show variables like 'optimizer_join_cache_level';
>
> === modified file 'mysql-test/r/subquery_sj_loosescan_jcl7.result'
> --- a/mysql-test/r/subquery_sj_loosescan_jcl7.result 2010-08-12 09:02:11 +0000
> +++ b/mysql-test/r/subquery_sj_loosescan_jcl7.result 2010-08-13 12:49:50 +0000
> @@ -1691,8 +1691,8 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
> (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t2 system NULL NULL NULL NULL 1
> -1 PRIMARY t1 ALL NULL NULL NULL NULL 2
> -1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary;
> Using join buffer (BNL, regular buffers)
> +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Start temporary
> +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer
> (BNL, regular buffers)
> SELECT * FROM t1 WHERE (t1.i) IN
> (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
> i
> @@ -1732,8 +1732,8 @@ EXPLAIN SELECT * FROM t1 WHERE (11) IN
> (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t2 system NULL NULL NULL NULL 1
> -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
> -1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary;
> Using join buffer (BNL, regular buffers)
> +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; Start temporary
> +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer
> (BNL, regular buffers)
> SELECT * FROM t1 WHERE (11) IN
> (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
> i
> @@ -1838,8 +1838,8 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
> (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t3 system NULL NULL NULL NULL 1
> -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
> -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary;
> Using join buffer (BNL, regular buffers)
> +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; Start temporary
> +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer
> (BNL, regular buffers)
> SELECT * FROM t1 WHERE (t1.i) IN
> (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
> i
> @@ -2581,8 +2581,8 @@ explain select *
> from t0 where a in
> (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t0 ALL NULL NULL NULL NULL 10
> -1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Start temporary; Using join buffer
> (BNL, regular buffers)
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Start temporary
> +1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Using join buffer (BNL, regular
> buffers)
> 1 PRIMARY t2 ref a a 5 test.t1.a 1 Using index
> 1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; End temporary
> drop table t0, t1,t2,t3;
> @@ -4034,8 +4034,8 @@ EXPLAIN SELECT Name FROM t1
> WHERE t1.Code IN (
> SELECT t2.CountryCode FROM t2 WHERE Population> 5000000);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 31
> -1 PRIMARY t2 ref CountryCode CountryCode 3 test.t1.Code 18 Using where; Start
> temporary; End temporary
> +1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 31 Start temporary
> +1 PRIMARY t2 ref CountryCode CountryCode 3 test.t1.Code 18 Using where; End
> temporary
> SELECT Name FROM t1
> WHERE t1.Code IN (
> SELECT t2.CountryCode FROM t2 WHERE Population> 5000000);
> @@ -4056,8 +4056,8 @@ explain extended select * from t0
> where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and
> t1.b=t2.b);
> id select_type table type possible_keys key key_len ref rows filtered Extra
> -1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00 Using where
> -1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00 Start temporary
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00 Using where; Start temporary
> +1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00
> 1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 100.00 Using where; End
> temporary
> Warnings:
> Note 1276 Field or reference 'test.t0.a' of SELECT #2 was resolved in SELECT #1
> @@ -4515,6 +4515,35 @@ a
> 1
> 1
> drop table t1,t2,t3;
> +#
> +# BUG#49129: Wrong result with IN-subquery with join_cache_level=6
> +# and firstmatch=off
> +#
> +CREATE TABLE t0 (a INT);
> +CREATE TABLE t1 (a INT, b INT, KEY(a));
> +CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
> +INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
> +INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(3,11);
> +INSERT INTO t2 VALUES (0,0),(1,1),(2,2),(3,11),(4,4);
> +
> +# Should contain records 0..3
> +SELECT * FROM t0 WHERE t0.a IN
> +(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
> +a
> +0
> +1
> +2
> +3
> +
> +EXPLAIN
> +SELECT * FROM t0 WHERE t0.a IN
> +(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 5 Using where; Start temporary
> +1 PRIMARY t1 ALL a NULL NULL NULL 5 Using where; Using join buffer (BNL, regular
> buffers)
> +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 Using where; End temporary
> +DROP TABLE t0, t1, t2;
> +# End BUG#49129
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
> show variables like 'optimizer_join_cache_level';
>
> === modified file 'mysql-test/r/subquery_sj_mat.result'
> --- a/mysql-test/r/subquery_sj_mat.result 2010-08-12 09:02:11 +0000
> +++ b/mysql-test/r/subquery_sj_mat.result 2010-08-13 12:49:50 +0000
> @@ -1728,8 +1728,8 @@ EXPLAIN SELECT * FROM t1 WHERE (11) IN
> (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t2 system NULL NULL NULL NULL 1
> -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
> -1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary
> +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; Start temporary
> +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; End temporary
> SELECT * FROM t1 WHERE (11) IN
> (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
> i
> @@ -2577,8 +2577,8 @@ explain select *
> from t0 where a in
> (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t0 ALL NULL NULL NULL NULL 10
> -1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Start temporary; Using join buffer
> (BNL, regular buffers)
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Start temporary
> +1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Using join buffer (BNL, regular
> buffers)
> 1 PRIMARY t2 ref a a 5 test.t1.a 1 Using index
> 1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; End temporary
> drop table t0, t1,t2,t3;
> @@ -4507,4 +4507,33 @@ a
> 1
> 1
> drop table t1,t2,t3;
> +#
> +# BUG#49129: Wrong result with IN-subquery with join_cache_level=6
> +# and firstmatch=off
> +#
> +CREATE TABLE t0 (a INT);
> +CREATE TABLE t1 (a INT, b INT, KEY(a));
> +CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
> +INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
> +INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(3,11);
> +INSERT INTO t2 VALUES (0,0),(1,1),(2,2),(3,11),(4,4);
> +
> +# Should contain records 0..3
> +SELECT * FROM t0 WHERE t0.a IN
> +(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
> +a
> +0
> +1
> +2
> +3
> +
> +EXPLAIN
> +SELECT * FROM t0 WHERE t0.a IN
> +(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 5 Using where; Start temporary
> +1 PRIMARY t1 ALL a NULL NULL NULL 5 Using where; Using join buffer (BNL, regular
> buffers)
> +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 Using where; End temporary
> +DROP TABLE t0, t1, t2;
> +# End BUG#49129
> set optimizer_switch=default;
>
> === modified file 'mysql-test/r/subquery_sj_mat_jcl6.result'
> --- a/mysql-test/r/subquery_sj_mat_jcl6.result 2010-08-12 09:02:11 +0000
> +++ b/mysql-test/r/subquery_sj_mat_jcl6.result 2010-08-13 12:49:50 +0000
> @@ -1732,8 +1732,8 @@ EXPLAIN SELECT * FROM t1 WHERE (11) IN
> (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t2 system NULL NULL NULL NULL 1
> -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
> -1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary;
> Using join buffer (BNL, incremental buffers)
> +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; Start temporary
> +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer
> (BNL, incremental buffers)
> SELECT * FROM t1 WHERE (11) IN
> (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
> i
> @@ -2581,8 +2581,8 @@ explain select *
> from t0 where a in
> (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t0 ALL NULL NULL NULL NULL 10
> -1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Start temporary; Using join buffer
> (BNL, incremental buffers)
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Start temporary
> +1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Using join buffer (BNL, incremental
> buffers)
> 1 PRIMARY t2 ref a a 5 test.t1.a 1 Using index
> 1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; End temporary
> drop table t0, t1,t2,t3;
> @@ -4052,8 +4052,8 @@ explain extended select * from t0
> where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and
> t1.b=t2.b);
> id select_type table type possible_keys key key_len ref rows filtered Extra
> -1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00 Using where
> -1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00 Start temporary
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00 Using where; Start temporary
> +1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00
> 1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 100.00 Using where; End
> temporary
> Warnings:
> Note 1276 Field or reference 'test.t0.a' of SELECT #2 was resolved in SELECT #1
> @@ -4511,6 +4511,35 @@ a
> 1
> 1
> drop table t1,t2,t3;
> +#
> +# BUG#49129: Wrong result with IN-subquery with join_cache_level=6
> +# and firstmatch=off
> +#
> +CREATE TABLE t0 (a INT);
> +CREATE TABLE t1 (a INT, b INT, KEY(a));
> +CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
> +INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
> +INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(3,11);
> +INSERT INTO t2 VALUES (0,0),(1,1),(2,2),(3,11),(4,4);
> +
> +# Should contain records 0..3
> +SELECT * FROM t0 WHERE t0.a IN
> +(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
> +a
> +0
> +1
> +2
> +3
> +
> +EXPLAIN
> +SELECT * FROM t0 WHERE t0.a IN
> +(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 5 Using where; Start temporary
> +1 PRIMARY t1 ALL a NULL NULL NULL 5 Using where; Using join buffer (BNL, incremental
> buffers)
> +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 Using where; End temporary
> +DROP TABLE t0, t1, t2;
> +# End BUG#49129
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
> show variables like 'optimizer_join_cache_level';
>
> === modified file 'mysql-test/r/subquery_sj_mat_jcl7.result'
> --- a/mysql-test/r/subquery_sj_mat_jcl7.result 2010-08-12 09:02:11 +0000
> +++ b/mysql-test/r/subquery_sj_mat_jcl7.result 2010-08-13 12:49:50 +0000
> @@ -1732,8 +1732,8 @@ EXPLAIN SELECT * FROM t1 WHERE (11) IN
> (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
> id select_type table type possible_keys key key_len ref rows Extra
> 1 PRIMARY t2 system NULL NULL NULL NULL 1
> -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
> -1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; Start temporary; End temporary;
> Using join buffer (BNL, regular buffers)
> +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; Start temporary
> +1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer
> (BNL, regular buffers)
> SELECT * FROM t1 WHERE (11) IN
> (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
> i
> @@ -2581,8 +2581,8 @@ explain select *
> from t0 where a in
> (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
> id select_type table type possible_keys key key_len ref rows Extra
> -1 PRIMARY t0 ALL NULL NULL NULL NULL 10
> -1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Start temporary; Using join buffer
> (BNL, regular buffers)
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Start temporary
> +1 PRIMARY t1 index NULL a 5 NULL 10 Using index; Using join buffer (BNL, regular
> buffers)
> 1 PRIMARY t2 ref a a 5 test.t1.a 1 Using index
> 1 PRIMARY t3 ref a a 5 test.t1.a 1 Using where; Using index; End temporary
> drop table t0, t1,t2,t3;
> @@ -4052,8 +4052,8 @@ explain extended select * from t0
> where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and
> t1.b=t2.b);
> id select_type table type possible_keys key key_len ref rows filtered Extra
> -1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00 Using where
> -1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00 Start temporary
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 5 100.00 Using where; Start temporary
> +1 PRIMARY t1 ref a a 5 test.t0.a 1 100.00
> 1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 100.00 Using where; End
> temporary
> Warnings:
> Note 1276 Field or reference 'test.t0.a' of SELECT #2 was resolved in SELECT #1
> @@ -4511,6 +4511,35 @@ a
> 1
> 1
> drop table t1,t2,t3;
> +#
> +# BUG#49129: Wrong result with IN-subquery with join_cache_level=6
> +# and firstmatch=off
> +#
> +CREATE TABLE t0 (a INT);
> +CREATE TABLE t1 (a INT, b INT, KEY(a));
> +CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
> +INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
> +INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(3,11);
> +INSERT INTO t2 VALUES (0,0),(1,1),(2,2),(3,11),(4,4);
> +
> +# Should contain records 0..3
> +SELECT * FROM t0 WHERE t0.a IN
> +(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
> +a
> +0
> +1
> +2
> +3
> +
> +EXPLAIN
> +SELECT * FROM t0 WHERE t0.a IN
> +(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 5 Using where; Start temporary
> +1 PRIMARY t1 ALL a NULL NULL NULL 5 Using where; Using join buffer (BNL, regular
> buffers)
> +1 PRIMARY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 Using where; End temporary
> +DROP TABLE t0, t1, t2;
> +# End BUG#49129
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
> show variables like 'optimizer_join_cache_level';
>
> === modified file 'mysql-test/r/subquery_sj_mat_nosj.result'
> --- a/mysql-test/r/subquery_sj_mat_nosj.result 2010-08-12 09:02:11 +0000
> +++ b/mysql-test/r/subquery_sj_mat_nosj.result 2010-08-13 12:49:50 +0000
> @@ -4729,4 +4729,33 @@ a
> 1
> 1
> drop table t1,t2,t3;
> +#
> +# BUG#49129: Wrong result with IN-subquery with join_cache_level=6
> +# and firstmatch=off
> +#
> +CREATE TABLE t0 (a INT);
> +CREATE TABLE t1 (a INT, b INT, KEY(a));
> +CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
> +INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
> +INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(3,11);
> +INSERT INTO t2 VALUES (0,0),(1,1),(2,2),(3,11),(4,4);
> +
> +# Should contain records 0..3
> +SELECT * FROM t0 WHERE t0.a IN
> +(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
> +a
> +0
> +1
> +2
> +3
> +
> +EXPLAIN
> +SELECT * FROM t0 WHERE t0.a IN
> +(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 5 Using where
> +2 DEPENDENT SUBQUERY t1 ref a a 5 func 2
> +2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 Using where
> +DROP TABLE t0, t1, t2;
> +# End BUG#49129
> set optimizer_switch=default;
>
> === modified file 'mysql-test/r/subquery_sj_none.result'
> --- a/mysql-test/r/subquery_sj_none.result 2010-08-12 09:02:11 +0000
> +++ b/mysql-test/r/subquery_sj_none.result 2010-08-13 12:49:50 +0000
> @@ -4655,4 +4655,33 @@ a
> 1
> 1
> drop table t1,t2,t3;
> +#
> +# BUG#49129: Wrong result with IN-subquery with join_cache_level=6
> +# and firstmatch=off
> +#
> +CREATE TABLE t0 (a INT);
> +CREATE TABLE t1 (a INT, b INT, KEY(a));
> +CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
> +INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
> +INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(3,11);
> +INSERT INTO t2 VALUES (0,0),(1,1),(2,2),(3,11),(4,4);
> +
> +# Should contain records 0..3
> +SELECT * FROM t0 WHERE t0.a IN
> +(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
> +a
> +0
> +1
> +2
> +3
> +
> +EXPLAIN
> +SELECT * FROM t0 WHERE t0.a IN
> +(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 5 Using where
> +2 DEPENDENT SUBQUERY t1 ref a a 5 func 2
> +2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 Using where
> +DROP TABLE t0, t1, t2;
> +# End BUG#49129
> set optimizer_switch=default;
>
> === modified file 'mysql-test/r/subquery_sj_none_jcl6.result'
> --- a/mysql-test/r/subquery_sj_none_jcl6.result 2010-08-12 09:02:11 +0000
> +++ b/mysql-test/r/subquery_sj_none_jcl6.result 2010-08-13 12:49:50 +0000
> @@ -4659,6 +4659,35 @@ a
> 1
> 1
> drop table t1,t2,t3;
> +#
> +# BUG#49129: Wrong result with IN-subquery with join_cache_level=6
> +# and firstmatch=off
> +#
> +CREATE TABLE t0 (a INT);
> +CREATE TABLE t1 (a INT, b INT, KEY(a));
> +CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
> +INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
> +INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(3,11);
> +INSERT INTO t2 VALUES (0,0),(1,1),(2,2),(3,11),(4,4);
> +
> +# Should contain records 0..3
> +SELECT * FROM t0 WHERE t0.a IN
> +(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
> +a
> +0
> +1
> +2
> +3
> +
> +EXPLAIN
> +SELECT * FROM t0 WHERE t0.a IN
> +(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 5 Using where
> +2 DEPENDENT SUBQUERY t1 ref a a 5 func 2
> +2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 Using where
> +DROP TABLE t0, t1, t2;
> +# End BUG#49129
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
> show variables like 'optimizer_join_cache_level';
>
> === modified file 'mysql-test/r/subquery_sj_none_jcl7.result'
> --- a/mysql-test/r/subquery_sj_none_jcl7.result 2010-08-12 09:02:11 +0000
> +++ b/mysql-test/r/subquery_sj_none_jcl7.result 2010-08-13 12:49:50 +0000
> @@ -4659,6 +4659,35 @@ a
> 1
> 1
> drop table t1,t2,t3;
> +#
> +# BUG#49129: Wrong result with IN-subquery with join_cache_level=6
> +# and firstmatch=off
> +#
> +CREATE TABLE t0 (a INT);
> +CREATE TABLE t1 (a INT, b INT, KEY(a));
> +CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
> +INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
> +INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(3,11);
> +INSERT INTO t2 VALUES (0,0),(1,1),(2,2),(3,11),(4,4);
> +
> +# Should contain records 0..3
> +SELECT * FROM t0 WHERE t0.a IN
> +(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
> +a
> +0
> +1
> +2
> +3
> +
> +EXPLAIN
> +SELECT * FROM t0 WHERE t0.a IN
> +(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 PRIMARY t0 ALL NULL NULL NULL NULL 5 Using where
> +2 DEPENDENT SUBQUERY t1 ref a a 5 func 2
> +2 DEPENDENT SUBQUERY t2 eq_ref PRIMARY PRIMARY 4 test.t0.a 1 Using where
> +DROP TABLE t0, t1, t2;
> +# End BUG#49129
> set optimizer_switch=default;
> set optimizer_join_cache_level=default;
> show variables like 'optimizer_join_cache_level';
>
> === modified file 'sql/sql_join_cache.cc'
> --- a/sql/sql_join_cache.cc 2010-07-23 17:51:11 +0000
> +++ b/sql/sql_join_cache.cc 2010-08-13 12:49:50 +0000
> @@ -612,7 +612,12 @@ int JOIN_CACHE_BKA::init()
> copy_end= cache->field_descr+cache->fields;
> for (copy= cache->field_descr+cache->flag_fields; copy< copy_end;
> copy++)
> {
> - if (copy->field->table == tab->table&&
> + /*
> + (1) - when we store rowids for DuplicateWeedout, they have
> + copy->field==NULL
> + */
> + if (copy->field&& // (1)
> + copy->field->table == tab->table&&
> bitmap_is_set(key_read_set, copy->field->field_index))
> {
> *copy_ptr++= copy;
>
> === modified file 'sql/sql_select.cc'
> --- a/sql/sql_select.cc 2010-08-12 09:02:11 +0000
> +++ b/sql/sql_select.cc 2010-08-13 12:49:50 +0000
> @@ -1430,10 +1430,38 @@ int setup_semijoin_dups_elimination(JOIN
> forwards, but do not destroy other duplicate elimination methods.
> */
> uint first_table= i;
> + uint join_cache_level=
> join->thd->variables.optimizer_join_cache_level;
> for (uint j= i; j< i + pos->n_sj_tables; j++)
> {
> - if (join->best_positions[j].use_join_buffer&& j<=
> no_jbuf_after)
> + /*
> + The final decision on whether or not join buffering will
> + be used is done in check_join_cache_usage(), which is
> + called from make_join_readinfo()'s main loop.
> + check_join_cache_usage() needs to know if duplicate
> + weedout is used, so moving
> + setup_semijoin_dups_elimination() from before the main
> + loop to after it is not possible. I.e.,
> + join->best_positions[j].use_join_buffer is not
> + trustworthy at this point.
> +
> + TODO: merge make_join_readinfo() and
> + setup_semijoin_dups_elimination() loops and change the
> + following 'if' to
> +
> + "if (join->best_positions[j].use_join_buffer&&
> + j<= no_jbuf_after)".
> +
> + For now, use a rough criteria:
> + */
> + JOIN_TAB *sj_tab=join->join_tab + j;
> + if (j != join->const_tables&&
> + sj_tab->use_quick != 2&&
> + j<= no_jbuf_after&&
> + ((sj_tab->type == JT_ALL&& join_cache_level != 0) ||
> + (join_cache_level> 4&& (tab->type == JT_REF ||
> + tab->type == JT_EQ_REF))))
> {
> + /* Looks like we'll be using join buffer */
> first_table= join->const_tables;
> break;
> }
> @@ -8080,6 +8108,10 @@ void calc_used_field_length(THD *thd, JO
> (join_tab->table->s->reclength- rec_length));
> rec_length+=(uint) max(4,blob_length);
> }
> + /*
> + psergey-todo: why we don't count here rowid that we might need to store
> + when using DuplicateElimination?
> + */
> join_tab->used_fields=fields;
> join_tab->used_fieldlength=rec_length;
> join_tab->used_blobs=blobs;
>
>
>
>
>
--
Jørgen Løland | Senior Software Engineer | +47 73842138
Oracle MySQL
Trondheim, Norway
