#At file:///export/home/jl208045/mysql/mysql-next-mr-opt-backporting-54641-2/ based on revid:tor.didriksen@stripped
3287 Jorgen Loland 2010-11-24
Bug#54641: semijoin loosescan gives duplicate rows
The loosescan semijoin strategy does not handle join buffering
on any of the tables in the loosescan range. Prior to this bug
fix, check_join_cache_usage() would not do join buffering on a
join table if the loosescan_match_tab pointer was set for it.
However, loosescan_match_tab is a pointer from the first to the
last join table handled by the loosescan strategy. Join
buffering could therefore be used on all join tables except
the first in the loosescan range.
The fix is to skip join buffering if the join table is in
the loosescan range, i.e., if
join_tab->get_sj_strategy() == SJ_OPT_LOOSE_SCAN,
instead of checking loosescan_match_tab.
@ mysql-test/include/subquery_sj.inc
Add EXPLAIN to query that failed in bug#54641
@ mysql-test/r/subquery_sj_all.result
Add EXPLAIN to query that failed in bug#54641
@ mysql-test/r/subquery_sj_all_jcl6.result
Add EXPLAIN to query that failed in bug#54641
@ mysql-test/r/subquery_sj_all_jcl7.result
Add EXPLAIN to query that failed in bug#54641
@ mysql-test/r/subquery_sj_dupsweed.result
Add EXPLAIN to query that failed in bug#54641
@ mysql-test/r/subquery_sj_dupsweed_jcl6.result
Add EXPLAIN to query that failed in bug#54641
@ mysql-test/r/subquery_sj_dupsweed_jcl7.result
Add EXPLAIN to query that failed in bug#54641
@ mysql-test/r/subquery_sj_firstmatch.result
Add EXPLAIN to query that failed in bug#54641
@ mysql-test/r/subquery_sj_firstmatch_jcl6.result
Add EXPLAIN to query that failed in bug#54641
@ mysql-test/r/subquery_sj_firstmatch_jcl7.result
Add EXPLAIN to query that failed in bug#54641
@ mysql-test/r/subquery_sj_loosescan.result
Updated test result after fixing BUG#54641
@ mysql-test/r/subquery_sj_loosescan_jcl6.result
Updated test result after fixing BUG#54641
@ mysql-test/r/subquery_sj_loosescan_jcl7.result
Updated test result after fixing BUG#54641
@ mysql-test/r/subquery_sj_mat.result
Add EXPLAIN to query that failed in bug#54641
@ mysql-test/r/subquery_sj_mat_jcl6.result
Add EXPLAIN to query that failed in bug#54641
@ mysql-test/r/subquery_sj_mat_jcl7.result
Add EXPLAIN to query that failed in bug#54641
@ mysql-test/r/subquery_sj_mat_nosj.result
Add EXPLAIN to query that failed in bug#54641
@ mysql-test/r/subquery_sj_none.result
Add EXPLAIN to query that failed in bug#54641
@ mysql-test/r/subquery_sj_none_jcl6.result
Add EXPLAIN to query that failed in bug#54641
@ mysql-test/r/subquery_sj_none_jcl7.result
Add EXPLAIN to query that failed in bug#54641
@ sql/sql_select.cc
In check_join_cache_usage: skip join buffering if the join
table is in the loosescan range. This was previously done by
checking if the join table's loosescan_match_tab was set, but
is now done by checking if it's semijoin strategy is
SJ_OPT_LOOSE_SCAN.
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_select.cc
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc 2010-11-01 15:11:10 +0000
+++ b/mysql-test/include/subquery_sj.inc 2010-11-24 14:06:22 +0000
@@ -1494,8 +1494,6 @@ FROM t0
WHERE varchar_nokey IN (
SELECT t1 .varchar_key from t1
);
-# the two queries below show wrong results with loosescan,
-# this is tracked as BUG#54641
SELECT t0.int_key
FROM t0
WHERE t0.varchar_nokey IN (
@@ -1503,6 +1501,22 @@ WHERE t0.varchar_nokey IN (
FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
);
+EXPLAIN
+SELECT t0.int_key
+FROM t0
+WHERE t0.varchar_nokey IN (
+ SELECT t1_1 .varchar_key
+ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
+);
+
+SELECT t0.int_key
+FROM t0, t2
+WHERE t0.varchar_nokey IN (
+ SELECT t1_1 .varchar_key
+ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
+);
+
+EXPLAIN
SELECT t0.int_key
FROM t0, t2
WHERE t0.varchar_nokey IN (
=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result 2010-11-01 15:11:10 +0000
+++ b/mysql-test/r/subquery_sj_all.result 2010-11-24 14:06:22 +0000
@@ -3496,6 +3496,17 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1
int_key
9
7
+EXPLAIN
+SELECT t0.int_key
+FROM t0
+WHERE t0.varchar_nokey IN (
+SELECT t1_1 .varchar_key
+FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1_1 index varchar_key varchar_key 9 NULL 2 Using where; Using index; Start materialize; Scan
+1 PRIMARY t1_2 index NULL int_key 5 NULL 2 Using index; End materialize
+1 PRIMARY t0 ALL NULL NULL NULL NULL 20 Using where; Using join buffer (BNL, regular buffers)
SELECT t0.int_key
FROM t0, t2
WHERE t0.varchar_nokey IN (
@@ -3505,6 +3516,18 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1
int_key
9
7
+EXPLAIN
+SELECT t0.int_key
+FROM t0, t2
+WHERE t0.varchar_nokey IN (
+SELECT t1_1 .varchar_key
+FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
+);
+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_1 index varchar_key varchar_key 9 NULL 2 Using where; Using index; Start materialize; Scan
+1 PRIMARY t1_2 index NULL int_key 5 NULL 2 Using index; End materialize
+1 PRIMARY t0 ALL NULL NULL NULL NULL 20 Using where; Using join buffer (BNL, regular buffers)
DROP TABLE t0, t1, t2;
# End of bug#46550
=== modified file 'mysql-test/r/subquery_sj_all_jcl6.result'
--- a/mysql-test/r/subquery_sj_all_jcl6.result 2010-11-01 15:11:10 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl6.result 2010-11-24 14:06:22 +0000
@@ -3500,6 +3500,17 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1
int_key
9
7
+EXPLAIN
+SELECT t0.int_key
+FROM t0
+WHERE t0.varchar_nokey IN (
+SELECT t1_1 .varchar_key
+FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1_1 index varchar_key varchar_key 9 NULL 2 Using where; Using index; Start materialize; Scan
+1 PRIMARY t1_2 index NULL int_key 5 NULL 2 Using index; End materialize
+1 PRIMARY t0 ALL NULL NULL NULL NULL 20 Using where; Using join buffer (BNL, incremental buffers)
SELECT t0.int_key
FROM t0, t2
WHERE t0.varchar_nokey IN (
@@ -3509,6 +3520,18 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1
int_key
9
7
+EXPLAIN
+SELECT t0.int_key
+FROM t0, t2
+WHERE t0.varchar_nokey IN (
+SELECT t1_1 .varchar_key
+FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
+);
+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_1 index varchar_key varchar_key 9 NULL 2 Using where; Using index; Start materialize; Scan
+1 PRIMARY t1_2 index NULL int_key 5 NULL 2 Using index; End materialize
+1 PRIMARY t0 ALL NULL NULL NULL NULL 20 Using where; Using join buffer (BNL, incremental buffers)
DROP TABLE t0, t1, t2;
# End of bug#46550
=== modified file 'mysql-test/r/subquery_sj_all_jcl7.result'
--- a/mysql-test/r/subquery_sj_all_jcl7.result 2010-11-01 15:11:10 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl7.result 2010-11-24 14:06:22 +0000
@@ -3500,6 +3500,17 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1
int_key
9
7
+EXPLAIN
+SELECT t0.int_key
+FROM t0
+WHERE t0.varchar_nokey IN (
+SELECT t1_1 .varchar_key
+FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1_1 index varchar_key varchar_key 9 NULL 2 Using where; Using index; Start materialize; Scan
+1 PRIMARY t1_2 index NULL int_key 5 NULL 2 Using index; End materialize
+1 PRIMARY t0 ALL NULL NULL NULL NULL 20 Using where; Using join buffer (BNL, regular buffers)
SELECT t0.int_key
FROM t0, t2
WHERE t0.varchar_nokey IN (
@@ -3509,6 +3520,18 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1
int_key
9
7
+EXPLAIN
+SELECT t0.int_key
+FROM t0, t2
+WHERE t0.varchar_nokey IN (
+SELECT t1_1 .varchar_key
+FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
+);
+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_1 index varchar_key varchar_key 9 NULL 2 Using where; Using index; Start materialize; Scan
+1 PRIMARY t1_2 index NULL int_key 5 NULL 2 Using index; End materialize
+1 PRIMARY t0 ALL NULL NULL NULL NULL 20 Using where; Using join buffer (BNL, regular buffers)
DROP TABLE t0, t1, t2;
# End of bug#46550
=== modified file 'mysql-test/r/subquery_sj_dupsweed.result'
--- a/mysql-test/r/subquery_sj_dupsweed.result 2010-11-01 15:11:10 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result 2010-11-24 14:06:22 +0000
@@ -3495,6 +3495,17 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1
int_key
9
7
+EXPLAIN
+SELECT t0.int_key
+FROM t0
+WHERE t0.varchar_nokey IN (
+SELECT t1_1 .varchar_key
+FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1_1 index varchar_key varchar_key 9 NULL 2 Using where; Using index; Start temporary
+1 PRIMARY t1_2 index NULL int_key 5 NULL 2 Using index; Using join buffer (BNL, regular buffers)
+1 PRIMARY t0 ALL NULL NULL NULL NULL 20 Using where; End temporary; Using join buffer (BNL, regular buffers)
SELECT t0.int_key
FROM t0, t2
WHERE t0.varchar_nokey IN (
@@ -3504,6 +3515,18 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1
int_key
9
7
+EXPLAIN
+SELECT t0.int_key
+FROM t0, t2
+WHERE t0.varchar_nokey IN (
+SELECT t1_1 .varchar_key
+FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
+);
+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_1 index varchar_key varchar_key 9 NULL 2 Using where; Using index; Start temporary
+1 PRIMARY t1_2 index NULL int_key 5 NULL 2 Using index; Using join buffer (BNL, regular buffers)
+1 PRIMARY t0 ALL NULL NULL NULL NULL 20 Using where; End temporary; Using join buffer (BNL, regular buffers)
DROP TABLE t0, t1, t2;
# End of bug#46550
=== modified file 'mysql-test/r/subquery_sj_dupsweed_jcl6.result'
--- a/mysql-test/r/subquery_sj_dupsweed_jcl6.result 2010-11-01 15:11:10 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl6.result 2010-11-24 14:06:22 +0000
@@ -3499,6 +3499,17 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1
int_key
9
7
+EXPLAIN
+SELECT t0.int_key
+FROM t0
+WHERE t0.varchar_nokey IN (
+SELECT t1_1 .varchar_key
+FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1_1 index varchar_key varchar_key 9 NULL 2 Using where; Using index; Start temporary
+1 PRIMARY t1_2 index NULL int_key 5 NULL 2 Using index; Using join buffer (BNL, incremental buffers)
+1 PRIMARY t0 ALL NULL NULL NULL NULL 20 Using where; End temporary; Using join buffer (BNL, incremental buffers)
SELECT t0.int_key
FROM t0, t2
WHERE t0.varchar_nokey IN (
@@ -3508,6 +3519,18 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1
int_key
9
7
+EXPLAIN
+SELECT t0.int_key
+FROM t0, t2
+WHERE t0.varchar_nokey IN (
+SELECT t1_1 .varchar_key
+FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
+);
+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_1 index varchar_key varchar_key 9 NULL 2 Using where; Using index; Start temporary
+1 PRIMARY t1_2 index NULL int_key 5 NULL 2 Using index; Using join buffer (BNL, incremental buffers)
+1 PRIMARY t0 ALL NULL NULL NULL NULL 20 Using where; End temporary; Using join buffer (BNL, incremental buffers)
DROP TABLE t0, t1, t2;
# End of bug#46550
=== modified file 'mysql-test/r/subquery_sj_dupsweed_jcl7.result'
--- a/mysql-test/r/subquery_sj_dupsweed_jcl7.result 2010-11-01 15:11:10 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl7.result 2010-11-24 14:06:22 +0000
@@ -3499,6 +3499,17 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1
int_key
9
7
+EXPLAIN
+SELECT t0.int_key
+FROM t0
+WHERE t0.varchar_nokey IN (
+SELECT t1_1 .varchar_key
+FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1_1 index varchar_key varchar_key 9 NULL 2 Using where; Using index; Start temporary
+1 PRIMARY t1_2 index NULL int_key 5 NULL 2 Using index; Using join buffer (BNL, regular buffers)
+1 PRIMARY t0 ALL NULL NULL NULL NULL 20 Using where; End temporary; Using join buffer (BNL, regular buffers)
SELECT t0.int_key
FROM t0, t2
WHERE t0.varchar_nokey IN (
@@ -3508,6 +3519,18 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1
int_key
9
7
+EXPLAIN
+SELECT t0.int_key
+FROM t0, t2
+WHERE t0.varchar_nokey IN (
+SELECT t1_1 .varchar_key
+FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
+);
+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_1 index varchar_key varchar_key 9 NULL 2 Using where; Using index; Start temporary
+1 PRIMARY t1_2 index NULL int_key 5 NULL 2 Using index; Using join buffer (BNL, regular buffers)
+1 PRIMARY t0 ALL NULL NULL NULL NULL 20 Using where; End temporary; Using join buffer (BNL, regular buffers)
DROP TABLE t0, t1, t2;
# End of bug#46550
=== modified file 'mysql-test/r/subquery_sj_firstmatch.result'
--- a/mysql-test/r/subquery_sj_firstmatch.result 2010-11-01 15:11:10 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result 2010-11-24 14:06:22 +0000
@@ -3496,6 +3496,17 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1
int_key
9
7
+EXPLAIN
+SELECT t0.int_key
+FROM t0
+WHERE t0.varchar_nokey IN (
+SELECT t1_1 .varchar_key
+FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1_1 index varchar_key varchar_key 9 NULL 2 Using where; Using index; Start temporary
+1 PRIMARY t1_2 index NULL int_key 5 NULL 2 Using index; Using join buffer (BNL, regular buffers)
+1 PRIMARY t0 ALL NULL NULL NULL NULL 20 Using where; End temporary; Using join buffer (BNL, regular buffers)
SELECT t0.int_key
FROM t0, t2
WHERE t0.varchar_nokey IN (
@@ -3505,6 +3516,18 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1
int_key
9
7
+EXPLAIN
+SELECT t0.int_key
+FROM t0, t2
+WHERE t0.varchar_nokey IN (
+SELECT t1_1 .varchar_key
+FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
+);
+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_1 index varchar_key varchar_key 9 NULL 2 Using where; Using index; Start temporary
+1 PRIMARY t1_2 index NULL int_key 5 NULL 2 Using index; Using join buffer (BNL, regular buffers)
+1 PRIMARY t0 ALL NULL NULL NULL NULL 20 Using where; End temporary; Using join buffer (BNL, regular buffers)
DROP TABLE t0, t1, t2;
# End of bug#46550
=== modified file 'mysql-test/r/subquery_sj_firstmatch_jcl6.result'
--- a/mysql-test/r/subquery_sj_firstmatch_jcl6.result 2010-11-01 15:11:10 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl6.result 2010-11-24 14:06:22 +0000
@@ -3500,6 +3500,17 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1
int_key
9
7
+EXPLAIN
+SELECT t0.int_key
+FROM t0
+WHERE t0.varchar_nokey IN (
+SELECT t1_1 .varchar_key
+FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1_1 index varchar_key varchar_key 9 NULL 2 Using where; Using index; Start temporary
+1 PRIMARY t1_2 index NULL int_key 5 NULL 2 Using index; Using join buffer (BNL, incremental buffers)
+1 PRIMARY t0 ALL NULL NULL NULL NULL 20 Using where; End temporary; Using join buffer (BNL, incremental buffers)
SELECT t0.int_key
FROM t0, t2
WHERE t0.varchar_nokey IN (
@@ -3509,6 +3520,18 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1
int_key
9
7
+EXPLAIN
+SELECT t0.int_key
+FROM t0, t2
+WHERE t0.varchar_nokey IN (
+SELECT t1_1 .varchar_key
+FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
+);
+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_1 index varchar_key varchar_key 9 NULL 2 Using where; Using index; Start temporary
+1 PRIMARY t1_2 index NULL int_key 5 NULL 2 Using index; Using join buffer (BNL, incremental buffers)
+1 PRIMARY t0 ALL NULL NULL NULL NULL 20 Using where; End temporary; Using join buffer (BNL, incremental buffers)
DROP TABLE t0, t1, t2;
# End of bug#46550
=== modified file 'mysql-test/r/subquery_sj_firstmatch_jcl7.result'
--- a/mysql-test/r/subquery_sj_firstmatch_jcl7.result 2010-11-01 15:11:10 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl7.result 2010-11-24 14:06:22 +0000
@@ -3500,6 +3500,17 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1
int_key
9
7
+EXPLAIN
+SELECT t0.int_key
+FROM t0
+WHERE t0.varchar_nokey IN (
+SELECT t1_1 .varchar_key
+FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1_1 index varchar_key varchar_key 9 NULL 2 Using where; Using index; Start temporary
+1 PRIMARY t1_2 index NULL int_key 5 NULL 2 Using index; Using join buffer (BNL, regular buffers)
+1 PRIMARY t0 ALL NULL NULL NULL NULL 20 Using where; End temporary; Using join buffer (BNL, regular buffers)
SELECT t0.int_key
FROM t0, t2
WHERE t0.varchar_nokey IN (
@@ -3509,6 +3520,18 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1
int_key
9
7
+EXPLAIN
+SELECT t0.int_key
+FROM t0, t2
+WHERE t0.varchar_nokey IN (
+SELECT t1_1 .varchar_key
+FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
+);
+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_1 index varchar_key varchar_key 9 NULL 2 Using where; Using index; Start temporary
+1 PRIMARY t1_2 index NULL int_key 5 NULL 2 Using index; Using join buffer (BNL, regular buffers)
+1 PRIMARY t0 ALL NULL NULL NULL NULL 20 Using where; End temporary; Using join buffer (BNL, regular buffers)
DROP TABLE t0, t1, t2;
# End of bug#46550
=== modified file 'mysql-test/r/subquery_sj_loosescan.result'
--- a/mysql-test/r/subquery_sj_loosescan.result 2010-11-01 15:11:10 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result 2010-11-24 14:06:22 +0000
@@ -3495,9 +3495,18 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1
);
int_key
9
-9
-7
7
+EXPLAIN
+SELECT t0.int_key
+FROM t0
+WHERE t0.varchar_nokey IN (
+SELECT t1_1 .varchar_key
+FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1_1 index varchar_key varchar_key 9 NULL 2 Using where; Using index; LooseScan
+1 PRIMARY t1_2 index NULL int_key 5 NULL 2 Using index; FirstMatch(t1_1)
+1 PRIMARY t0 ALL NULL NULL NULL NULL 20 Using where; Using join buffer (BNL, regular buffers)
SELECT t0.int_key
FROM t0, t2
WHERE t0.varchar_nokey IN (
@@ -3506,9 +3515,19 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1
);
int_key
9
-9
-7
7
+EXPLAIN
+SELECT t0.int_key
+FROM t0, t2
+WHERE t0.varchar_nokey IN (
+SELECT t1_1 .varchar_key
+FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
+);
+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_1 index varchar_key varchar_key 9 NULL 2 Using where; Using index; LooseScan
+1 PRIMARY t1_2 index NULL int_key 5 NULL 2 Using index; FirstMatch(t1_1)
+1 PRIMARY t0 ALL NULL NULL NULL NULL 20 Using where; Using join buffer (BNL, regular buffers)
DROP TABLE t0, t1, t2;
# End of bug#46550
=== modified file 'mysql-test/r/subquery_sj_loosescan_jcl6.result'
--- a/mysql-test/r/subquery_sj_loosescan_jcl6.result 2010-11-01 15:11:10 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl6.result 2010-11-24 14:06:22 +0000
@@ -3499,9 +3499,18 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1
);
int_key
9
-9
-7
7
+EXPLAIN
+SELECT t0.int_key
+FROM t0
+WHERE t0.varchar_nokey IN (
+SELECT t1_1 .varchar_key
+FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1_1 index varchar_key varchar_key 9 NULL 2 Using where; Using index; LooseScan
+1 PRIMARY t1_2 index NULL int_key 5 NULL 2 Using index; FirstMatch(t1_1)
+1 PRIMARY t0 ALL NULL NULL NULL NULL 20 Using where; Using join buffer (BNL, incremental buffers)
SELECT t0.int_key
FROM t0, t2
WHERE t0.varchar_nokey IN (
@@ -3510,9 +3519,19 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1
);
int_key
9
-9
-7
7
+EXPLAIN
+SELECT t0.int_key
+FROM t0, t2
+WHERE t0.varchar_nokey IN (
+SELECT t1_1 .varchar_key
+FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
+);
+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_1 index varchar_key varchar_key 9 NULL 2 Using where; Using index; LooseScan
+1 PRIMARY t1_2 index NULL int_key 5 NULL 2 Using index; FirstMatch(t1_1)
+1 PRIMARY t0 ALL NULL NULL NULL NULL 20 Using where; Using join buffer (BNL, incremental buffers)
DROP TABLE t0, t1, t2;
# End of bug#46550
=== modified file 'mysql-test/r/subquery_sj_loosescan_jcl7.result'
--- a/mysql-test/r/subquery_sj_loosescan_jcl7.result 2010-11-01 15:11:10 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl7.result 2010-11-24 14:06:22 +0000
@@ -3499,9 +3499,18 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1
);
int_key
9
-9
-7
7
+EXPLAIN
+SELECT t0.int_key
+FROM t0
+WHERE t0.varchar_nokey IN (
+SELECT t1_1 .varchar_key
+FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1_1 index varchar_key varchar_key 9 NULL 2 Using where; Using index; LooseScan
+1 PRIMARY t1_2 index NULL int_key 5 NULL 2 Using index; FirstMatch(t1_1)
+1 PRIMARY t0 ALL NULL NULL NULL NULL 20 Using where; Using join buffer (BNL, regular buffers)
SELECT t0.int_key
FROM t0, t2
WHERE t0.varchar_nokey IN (
@@ -3510,9 +3519,19 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1
);
int_key
9
-9
-7
7
+EXPLAIN
+SELECT t0.int_key
+FROM t0, t2
+WHERE t0.varchar_nokey IN (
+SELECT t1_1 .varchar_key
+FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
+);
+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_1 index varchar_key varchar_key 9 NULL 2 Using where; Using index; LooseScan
+1 PRIMARY t1_2 index NULL int_key 5 NULL 2 Using index; FirstMatch(t1_1)
+1 PRIMARY t0 ALL NULL NULL NULL NULL 20 Using where; Using join buffer (BNL, regular buffers)
DROP TABLE t0, t1, t2;
# End of bug#46550
=== modified file 'mysql-test/r/subquery_sj_mat.result'
--- a/mysql-test/r/subquery_sj_mat.result 2010-11-01 15:11:10 +0000
+++ b/mysql-test/r/subquery_sj_mat.result 2010-11-24 14:06:22 +0000
@@ -3496,6 +3496,17 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1
int_key
9
7
+EXPLAIN
+SELECT t0.int_key
+FROM t0
+WHERE t0.varchar_nokey IN (
+SELECT t1_1 .varchar_key
+FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1_1 index varchar_key varchar_key 9 NULL 2 Using where; Using index; Start materialize; Scan
+1 PRIMARY t1_2 index NULL int_key 5 NULL 2 Using index; End materialize
+1 PRIMARY t0 ALL NULL NULL NULL NULL 20 Using where; Using join buffer (BNL, regular buffers)
SELECT t0.int_key
FROM t0, t2
WHERE t0.varchar_nokey IN (
@@ -3505,6 +3516,18 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1
int_key
9
7
+EXPLAIN
+SELECT t0.int_key
+FROM t0, t2
+WHERE t0.varchar_nokey IN (
+SELECT t1_1 .varchar_key
+FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
+);
+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_1 index varchar_key varchar_key 9 NULL 2 Using where; Using index; Start materialize; Scan
+1 PRIMARY t1_2 index NULL int_key 5 NULL 2 Using index; End materialize
+1 PRIMARY t0 ALL NULL NULL NULL NULL 20 Using where; Using join buffer (BNL, regular buffers)
DROP TABLE t0, t1, t2;
# End of bug#46550
=== modified file 'mysql-test/r/subquery_sj_mat_jcl6.result'
--- a/mysql-test/r/subquery_sj_mat_jcl6.result 2010-11-01 15:11:10 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl6.result 2010-11-24 14:06:22 +0000
@@ -3500,6 +3500,17 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1
int_key
9
7
+EXPLAIN
+SELECT t0.int_key
+FROM t0
+WHERE t0.varchar_nokey IN (
+SELECT t1_1 .varchar_key
+FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1_1 index varchar_key varchar_key 9 NULL 2 Using where; Using index; Start materialize; Scan
+1 PRIMARY t1_2 index NULL int_key 5 NULL 2 Using index; End materialize
+1 PRIMARY t0 ALL NULL NULL NULL NULL 20 Using where; Using join buffer (BNL, incremental buffers)
SELECT t0.int_key
FROM t0, t2
WHERE t0.varchar_nokey IN (
@@ -3509,6 +3520,18 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1
int_key
9
7
+EXPLAIN
+SELECT t0.int_key
+FROM t0, t2
+WHERE t0.varchar_nokey IN (
+SELECT t1_1 .varchar_key
+FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
+);
+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_1 index varchar_key varchar_key 9 NULL 2 Using where; Using index; Start materialize; Scan
+1 PRIMARY t1_2 index NULL int_key 5 NULL 2 Using index; End materialize
+1 PRIMARY t0 ALL NULL NULL NULL NULL 20 Using where; Using join buffer (BNL, incremental buffers)
DROP TABLE t0, t1, t2;
# End of bug#46550
=== modified file 'mysql-test/r/subquery_sj_mat_jcl7.result'
--- a/mysql-test/r/subquery_sj_mat_jcl7.result 2010-11-01 15:11:10 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl7.result 2010-11-24 14:06:22 +0000
@@ -3500,6 +3500,17 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1
int_key
9
7
+EXPLAIN
+SELECT t0.int_key
+FROM t0
+WHERE t0.varchar_nokey IN (
+SELECT t1_1 .varchar_key
+FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1_1 index varchar_key varchar_key 9 NULL 2 Using where; Using index; Start materialize; Scan
+1 PRIMARY t1_2 index NULL int_key 5 NULL 2 Using index; End materialize
+1 PRIMARY t0 ALL NULL NULL NULL NULL 20 Using where; Using join buffer (BNL, regular buffers)
SELECT t0.int_key
FROM t0, t2
WHERE t0.varchar_nokey IN (
@@ -3509,6 +3520,18 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1
int_key
9
7
+EXPLAIN
+SELECT t0.int_key
+FROM t0, t2
+WHERE t0.varchar_nokey IN (
+SELECT t1_1 .varchar_key
+FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
+);
+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_1 index varchar_key varchar_key 9 NULL 2 Using where; Using index; Start materialize; Scan
+1 PRIMARY t1_2 index NULL int_key 5 NULL 2 Using index; End materialize
+1 PRIMARY t0 ALL NULL NULL NULL NULL 20 Using where; Using join buffer (BNL, regular buffers)
DROP TABLE t0, t1, t2;
# End of bug#46550
=== modified file 'mysql-test/r/subquery_sj_mat_nosj.result'
--- a/mysql-test/r/subquery_sj_mat_nosj.result 2010-11-01 15:11:10 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result 2010-11-24 14:06:22 +0000
@@ -3718,6 +3718,17 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1
int_key
9
7
+EXPLAIN
+SELECT t0.int_key
+FROM t0
+WHERE t0.varchar_nokey IN (
+SELECT t1_1 .varchar_key
+FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t0 ALL NULL NULL NULL NULL 20 Using where
+2 SUBQUERY t1_1 index NULL varchar_key 9 NULL 2 Using where; Using index
+2 SUBQUERY t1_2 index NULL int_key 5 NULL 2 Using index; Using join buffer (BNL, regular buffers)
SELECT t0.int_key
FROM t0, t2
WHERE t0.varchar_nokey IN (
@@ -3727,6 +3738,18 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1
int_key
9
7
+EXPLAIN
+SELECT t0.int_key
+FROM t0, t2
+WHERE t0.varchar_nokey IN (
+SELECT t1_1 .varchar_key
+FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 system NULL NULL NULL NULL 1
+1 PRIMARY t0 ALL NULL NULL NULL NULL 20 Using where
+2 SUBQUERY t1_1 index NULL varchar_key 9 NULL 2 Using where; Using index
+2 SUBQUERY t1_2 index NULL int_key 5 NULL 2 Using index; Using join buffer (BNL, regular buffers)
DROP TABLE t0, t1, t2;
# End of bug#46550
=== modified file 'mysql-test/r/subquery_sj_none.result'
--- a/mysql-test/r/subquery_sj_none.result 2010-11-01 15:11:10 +0000
+++ b/mysql-test/r/subquery_sj_none.result 2010-11-24 14:06:22 +0000
@@ -3644,6 +3644,17 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1
int_key
9
7
+EXPLAIN
+SELECT t0.int_key
+FROM t0
+WHERE t0.varchar_nokey IN (
+SELECT t1_1 .varchar_key
+FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t0 ALL NULL NULL NULL NULL 20 Using where
+2 DEPENDENT SUBQUERY t1_1 ref varchar_key varchar_key 4 func 1 Using where; Using index
+2 DEPENDENT SUBQUERY t1_2 index NULL int_key 5 NULL 2 Using index; Using join buffer (BNL, regular buffers)
SELECT t0.int_key
FROM t0, t2
WHERE t0.varchar_nokey IN (
@@ -3653,6 +3664,18 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1
int_key
9
7
+EXPLAIN
+SELECT t0.int_key
+FROM t0, t2
+WHERE t0.varchar_nokey IN (
+SELECT t1_1 .varchar_key
+FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 system NULL NULL NULL NULL 1
+1 PRIMARY t0 ALL NULL NULL NULL NULL 20 Using where
+2 DEPENDENT SUBQUERY t1_1 ref varchar_key varchar_key 4 func 1 Using where; Using index
+2 DEPENDENT SUBQUERY t1_2 index NULL int_key 5 NULL 2 Using index; Using join buffer (BNL, regular buffers)
DROP TABLE t0, t1, t2;
# End of bug#46550
=== modified file 'mysql-test/r/subquery_sj_none_jcl6.result'
--- a/mysql-test/r/subquery_sj_none_jcl6.result 2010-11-01 15:11:10 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl6.result 2010-11-24 14:06:22 +0000
@@ -3648,6 +3648,17 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1
int_key
9
7
+EXPLAIN
+SELECT t0.int_key
+FROM t0
+WHERE t0.varchar_nokey IN (
+SELECT t1_1 .varchar_key
+FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t0 ALL NULL NULL NULL NULL 20 Using where
+2 DEPENDENT SUBQUERY t1_1 ref varchar_key varchar_key 4 func 1 Using where; Using index
+2 DEPENDENT SUBQUERY t1_2 index NULL int_key 5 NULL 2 Using index; Using join buffer (BNL, incremental buffers)
SELECT t0.int_key
FROM t0, t2
WHERE t0.varchar_nokey IN (
@@ -3657,6 +3668,18 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1
int_key
9
7
+EXPLAIN
+SELECT t0.int_key
+FROM t0, t2
+WHERE t0.varchar_nokey IN (
+SELECT t1_1 .varchar_key
+FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 system NULL NULL NULL NULL 1
+1 PRIMARY t0 ALL NULL NULL NULL NULL 20 Using where
+2 DEPENDENT SUBQUERY t1_1 ref varchar_key varchar_key 4 func 1 Using where; Using index
+2 DEPENDENT SUBQUERY t1_2 index NULL int_key 5 NULL 2 Using index; Using join buffer (BNL, incremental buffers)
DROP TABLE t0, t1, t2;
# End of bug#46550
=== modified file 'mysql-test/r/subquery_sj_none_jcl7.result'
--- a/mysql-test/r/subquery_sj_none_jcl7.result 2010-11-01 15:11:10 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl7.result 2010-11-24 14:06:22 +0000
@@ -3648,6 +3648,17 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1
int_key
9
7
+EXPLAIN
+SELECT t0.int_key
+FROM t0
+WHERE t0.varchar_nokey IN (
+SELECT t1_1 .varchar_key
+FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t0 ALL NULL NULL NULL NULL 20 Using where
+2 DEPENDENT SUBQUERY t1_1 ref varchar_key varchar_key 4 func 1 Using where; Using index
+2 DEPENDENT SUBQUERY t1_2 index NULL int_key 5 NULL 2 Using index; Using join buffer (BNL, regular buffers)
SELECT t0.int_key
FROM t0, t2
WHERE t0.varchar_nokey IN (
@@ -3657,6 +3668,18 @@ FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1
int_key
9
7
+EXPLAIN
+SELECT t0.int_key
+FROM t0, t2
+WHERE t0.varchar_nokey IN (
+SELECT t1_1 .varchar_key
+FROM t1 AS t1_1 JOIN t1 AS t1_2 ON t1_1 .int_key
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 system NULL NULL NULL NULL 1
+1 PRIMARY t0 ALL NULL NULL NULL NULL 20 Using where
+2 DEPENDENT SUBQUERY t1_1 ref varchar_key varchar_key 4 func 1 Using where; Using index
+2 DEPENDENT SUBQUERY t1_2 index NULL int_key 5 NULL 2 Using index; Using join buffer (BNL, regular buffers)
DROP TABLE t0, t1, t2;
# End of bug#46550
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2010-11-16 16:17:25 +0000
+++ b/sql/sql_select.cc 2010-11-24 14:06:22 +0000
@@ -10509,9 +10509,13 @@ uint check_join_cache_usage(JOIN_TAB *ta
goto no_join_cache;
/* No join buffering if prevented by no_jbuf_after */
- if (!(i <= no_jbuf_after) || tab->loosescan_match_tab)
+ if (i > no_jbuf_after)
goto no_join_cache;
+ /* No join buffering if this semijoin nest is handled by loosescan */
+ if (tab_sj_strategy == SJ_OPT_LOOSE_SCAN)
+ goto no_join_cache;
+
/* Neither if semijoin Materialization */
if (sj_is_materialize_strategy(tab_sj_strategy))
goto no_join_cache;
Attachment: [text/bzr-bundle] bzr/jorgen.loland@oracle.com-20101124140622-37sja38n7qb9ehj6.bundle
