#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;
Attachment: [text/bzr-bundle] bzr/jorgen.loland@sun.com-20100813124950-wm792z59kzgcam39.bundle
