List:Commits« Previous MessageNext Message »
From:Jorgen Loland Date:November 24 2010 2:06pm
Subject:bzr commit into mysql-trunk branch (jorgen.loland:3287) Bug#54641
View as plain text  
#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
Thread
bzr commit into mysql-trunk branch (jorgen.loland:3287) Bug#54641Jorgen Loland24 Nov
  • Re: bzr commit into mysql-trunk branch (jorgen.loland:3287) Bug#54641Øystein Grøvlen24 Nov