List:Commits« Previous MessageNext Message »
From:Jorgen Loland Date:September 2 2010 7:34am
Subject:bzr push into mysql-next-mr-bugfixing branch (jorgen.loland:3233 to 3234)
Bug#49129
View as plain text  
 3234 Jorgen Loland	2010-09-02
      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 non-const 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/r/subquery_sj_all.result
        Recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_all_jcl6.result
        Recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_all_jcl7.result
        Recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_dupsweed.result
        Recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_dupsweed_jcl6.result
        Recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_dupsweed_jcl7.result
        Recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_firstmatch.result
        Recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_firstmatch_jcl6.result
        Recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_firstmatch_jcl7.result
        Recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_loosescan.result
        Recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_loosescan_jcl6.result
        Recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_loosescan_jcl7.result
        Recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_mat.result
        Recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_mat_jcl6.result
        Recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_mat_jcl7.result
        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.
     @ sql/sql_select.h
        Made st_join_table::use_quick an enum
     @ sql/sql_test.cc
        Made st_join_table::use_quick an enum

    modified:
      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
      sql/sql_join_cache.cc
      sql/sql_select.cc
      sql/sql_select.h
      sql/sql_test.cc
 3233 Evgeny Potemkin	2010-09-01 [merge]
      Auto-merge.

    modified:
      mysql-test/r/join_outer.result
      mysql-test/r/join_outer_jcl6.result
      mysql-test/t/join_outer.test
      sql/sql_select.cc
=== 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-09-02 07:34:10 +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;

=== 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-09-02 07:34:10 +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,

=== 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-09-02 07:34:10 +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,

=== 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-09-02 07:34:10 +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;

=== 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-09-02 07:34:10 +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

=== 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-09-02 07:34:10 +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

=== 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-09-02 07:34:10 +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;

=== 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-09-02 07:34:10 +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

=== 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-09-02 07:34:10 +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

=== 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-09-02 07:34:10 +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;

=== 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-09-02 07:34:10 +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

=== 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-09-02 07:34:10 +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

=== 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-09-02 07:34:10 +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;

=== 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-09-02 07:34:10 +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

=== 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-09-02 07:34:10 +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

=== 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-09-02 07:34:10 +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-09-01 14:00:12 +0000
+++ b/sql/sql_select.cc	2010-09-02 07:34:10 +0000
@@ -1236,6 +1236,39 @@ static bool sj_table_is_included(JOIN *j
   return TRUE;
 }
 
+/**
+   Check if the optimizer might choose to use join buffering for this
+   join. If that is the case, and if duplicate weedout semijoin
+   strategy is used, the duplicate generating range must be extended
+   to the first non-const table. 
+
+   This function is called from setup_semijoin_dups_elimination()
+   before the final decision is made on whether or not buffering is
+   used. It is therefore only a rough test that covers all cases where
+   join buffering might be used, but potentially also some cases where
+   join buffering will not be used.
+
+   @param join_cache_level     The join cache level
+   @param sj_tab               Table that might be joined by BNL/BKA
+
+   @return                     
+      true if join buffering might be used, false otherwise
+
+ */
+bool might_do_join_buffering(uint join_cache_level, 
+                             const JOIN_TAB *sj_tab) 
+{
+  /* 
+     (1) sj_tab is not a const table
+  */
+  return (sj_tab-sj_tab->join->join_tab != sj_tab->join->const_tables && // (1)
+          sj_tab->use_quick != QS_DYNAMIC_RANGE && 
+          ((join_cache_level != 0 && sj_tab->type == JT_ALL) ||
+           (join_cache_level > 4 && 
+            (sj_tab->type == JT_REF || 
+             sj_tab->type == JT_EQ_REF || 
+             sj_tab->type == JT_CONST))));
+}
 
 /**
   Setup the strategies to eliminate semi-join duplicates.
@@ -1243,8 +1276,8 @@ static bool sj_table_is_included(JOIN *j
   @param join           Join to process
   @param options        Join options (needed to see if join buffering will be 
                         used or not)
-  @param no_jbuf_after  Another bit of information re where join buffering will
-                        be used.
+  @param no_jbuf_after  Do not use join buffering after the table with this 
+                        number
 
   @retval FALSE  OK 
   @retval TRUE   Out of memory error
@@ -1368,32 +1401,32 @@ static bool sj_table_is_included(JOIN *j
 int setup_semijoin_dups_elimination(JOIN *join, ulonglong options,
                                     uint no_jbuf_after)
 {
-  uint i;
+  uint tableno;
   THD *thd= join->thd;
   DBUG_ENTER("setup_semijoin_dups_elimination");
 
-  for (i= join->const_tables ; i < join->tables; )
+  for (tableno= join->const_tables ; tableno < join->tables; )
   {
-    JOIN_TAB *tab=join->join_tab + i;
-    POSITION *pos= join->best_positions + i;
+    JOIN_TAB *tab=join->join_tab + tableno;
+    POSITION *pos= join->best_positions + tableno;
     uint keylen, keyno;
     if (pos->sj_strategy == SJ_OPT_NONE)
     {
-      i++;  // nothing to do
+      tableno++;  // nothing to do
       continue;
     }
-    JOIN_TAB *tab_end= tab + pos->n_sj_tables - 1;
+    JOIN_TAB *last_sj_tab= tab + pos->n_sj_tables - 1;
     switch (pos->sj_strategy) {
       case SJ_OPT_MATERIALIZE_LOOKUP:
       case SJ_OPT_MATERIALIZE_SCAN:
         /* Do nothing */
-        i+= pos->n_sj_tables;
+        tableno+= pos->n_sj_tables;
         break;
       case SJ_OPT_LOOSE_SCAN:
       {
         DBUG_ASSERT(tab->emb_sj_nest != NULL); // First table must be inner
         /* We jump from the last table to the first one */
-        tab->loosescan_match_tab= tab_end;
+        tab->loosescan_match_tab= last_sj_tab;
 
         /* For LooseScan, duplicate elimination is based on rows being sorted 
            on key. We need to make sure that range select keep the sorted index
@@ -1415,22 +1448,100 @@ int setup_semijoin_dups_elimination(JOIN
 
         tab->loosescan_key_len= keylen;
         if (pos->n_sj_tables > 1) 
-          tab_end->do_firstmatch= tab;
-        i+= pos->n_sj_tables;
+          last_sj_tab->do_firstmatch= tab;
+        tableno+= pos->n_sj_tables;
         break;
       }
       case SJ_OPT_DUPS_WEEDOUT:
       {
         DBUG_ASSERT(tab->emb_sj_nest != NULL); // First table must be inner
         /*
-          Check for join buffering. If there is one, move the first table
-          forwards, but do not destroy other duplicate elimination methods.
+          Consider a semijoin of one outer and one inner table, both
+          with two rows. The inner table is assumed to be confluent
+          (See sj_opt_materialize_lookup)
+
+          If normal nested loop execution is used, we do not need to
+          include semi-join outer table rowids in the duplicate
+          weedout temp table since NL guarantees that outer table rows
+          are encountered only consecutively and because all rows in
+          the temp table are deleted for every new outer table
+          combination (example is with a confluent inner table):
+
+            ot1.row1|it1.row1 
+                 '-> temp table's have_confluent_row == FALSE 
+                   |-> output ot1.row1
+                   '-> set have_confluent_row= TRUE
+            ot1.row1|it1.row2
+                 |-> temp table's have_confluent_row == TRUE
+                 | '-> do not output ot1.row1
+                 '-> no more join matches - set have_confluent_row= FALSE
+            ot1.row2|it1.row1 
+                 '-> temp table's have_confluent_row == FALSE 
+                   |-> output ot1.row2
+                   '-> set have_confluent_row= TRUE
+              ...                 
+
+          Note: not having outer table rowids in the temp table and
+          then emptying the temp table when a new outer table row
+          combinition is encountered is an optimization. Including
+          outer table rowids in the temp table is not harmful but
+          wastes memory.
+
+          Now consider the join buffering algorithms (BNL/BKA). These
+          algorithms join each inner row with outer rows in "reverse"
+          order compared to NL. Effectively, this means that outer
+          table rows may be encountered multiple times in a
+          non-consecutive manner:
+
+            NL:                 BNL/BKA:
+            ot1.row1|it1.row1   ot1.row1|it1.row1
+            ot1.row1|it1.row2   ot1.row2|it1.row1
+            ot1.row2|it1.row1   ot1.row1|it1.row2
+            ot1.row2|it1.row2   ot1.row2|it1.row2
+
+          It is clear from the above that there is no place we can
+          empty the temp table like we do in NL to avoid storing outer
+          table rowids. 
+
+          Below we check if join buffering might be used. If so, set
+          first_table to the first non-constant table so that outer
+          table rowids are included in the temp table. Do not destroy
+          other duplicate elimination methods. 
         */
-        uint first_table= i;
-        for (uint j= i; j < i + pos->n_sj_tables; j++)
+        uint first_table= tableno;
+        uint join_cache_level= join->thd->variables.optimizer_join_cache_level;
+        for (uint sj_tableno= tableno; 
+             sj_tableno < tableno + pos->n_sj_tables; 
+             sj_tableno++)
         {
-          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 taken 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[sj_tableno].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[sj_tableno].use_join_buffer && 
+                 sj_tableno <= no_jbuf_after)".
+
+            For now, use a rough criteria:
+          */
+
+          if (sj_tableno <= no_jbuf_after &&
+              might_do_join_buffering(join_cache_level, 
+                                      join->join_tab + sj_tableno))
+
           {
+            /* Join buffering will probably be used */
             first_table= join->const_tables;
             break;
           }
@@ -1445,21 +1556,23 @@ int setup_semijoin_dups_elimination(JOIN
            - tables that need their rowids to be put into temptable
            - the last outer table
         */
-        for (JOIN_TAB *j=join->join_tab + first_table; j <= tab_end; j++)
+        for (JOIN_TAB *tab_in_range= join->join_tab + first_table; 
+             tab_in_range <= last_sj_tab; 
+             tab_in_range++)
         {
-          if (sj_table_is_included(join, j))
+          if (sj_table_is_included(join, tab_in_range))
           {
-            last_tab->join_tab= j;
+            last_tab->join_tab= tab_in_range;
             last_tab->rowid_offset= jt_rowid_offset;
-            jt_rowid_offset += j->table->file->ref_length;
-            if (j->table->maybe_null)
+            jt_rowid_offset += tab_in_range->table->file->ref_length;
+            if (tab_in_range->table->maybe_null)
             {
               last_tab->null_byte= jt_null_bits / 8;
               last_tab->null_bit= jt_null_bits++;
             }
             last_tab++;
-            j->table->prepare_for_position();
-            j->keep_current_rowid= TRUE;
+            tab_in_range->table->prepare_for_position();
+            tab_in_range->keep_current_rowid= TRUE;
           }
         }
 
@@ -1497,24 +1610,26 @@ int setup_semijoin_dups_elimination(JOIN
           sjtbl->have_confluent_row= FALSE;
         }
         join->join_tab[first_table].flush_weedout_table= sjtbl;
-        tab_end->check_weed_out_table= sjtbl;
+        last_sj_tab->check_weed_out_table= sjtbl;
 
-        i+= pos->n_sj_tables;
+        tableno+= pos->n_sj_tables;
         break;
       }
       case SJ_OPT_FIRST_MATCH:
       {
         JOIN_TAB *jump_to= tab - 1;
         DBUG_ASSERT(tab->emb_sj_nest != NULL); // First table must be inner
-        for (JOIN_TAB *j= tab; j <= tab_end; j++)
+        for (JOIN_TAB *tab_in_range= tab; 
+             tab_in_range <= last_sj_tab; 
+             tab_in_range++)
         {
-          if (!j->emb_sj_nest)
+          if (!tab_in_range->emb_sj_nest)
           {
             /*
               Let last non-correlated table be jump target for
               subsequent inner tables.
             */
-            jump_to= j;
+            jump_to= tab_in_range;
           }
           else
           {
@@ -1522,11 +1637,11 @@ int setup_semijoin_dups_elimination(JOIN
               Assign jump target for last table in a consecutive range of 
               inner tables.
             */
-            if (j == tab_end || !(j+1)->emb_sj_nest)
-              j->do_firstmatch= jump_to;
+            if (tab_in_range == last_sj_tab || !(tab_in_range+1)->emb_sj_nest)
+              tab_in_range->do_firstmatch= jump_to;
           }
         }
-        i+= pos->n_sj_tables;
+        tableno+= pos->n_sj_tables;
         break;
       }
     }
@@ -1537,11 +1652,13 @@ int setup_semijoin_dups_elimination(JOIN
     JOIN_TAB *last_sj_inner=
       (pos->sj_strategy == SJ_OPT_DUPS_WEEDOUT) ?
       /* Range may end with non-inner table so cannot set last_sj_inner_tab */
-      NULL : tab_end;
-    for (JOIN_TAB *j= tab; j <= tab_end; j++)
+      NULL : last_sj_tab;
+    for (JOIN_TAB *tab_in_range= tab; 
+         tab_in_range <= last_sj_tab; 
+         tab_in_range++)
     {
-      j->first_sj_inner_tab= tab;
-      j->last_sj_inner_tab=  last_sj_inner;
+      tab_in_range->first_sj_inner_tab= tab;
+      tab_in_range->last_sj_inner_tab=  last_sj_inner;
     }
   }
   DBUG_RETURN(FALSE);
@@ -8047,6 +8164,10 @@ void calc_used_field_length(THD *thd, JO
 			     (join_tab->table->s->reclength- rec_length));
     rec_length+=(uint) max(4,blob_length);
   }
+  /**
+    @todo why don't we count the rowids 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;
@@ -9247,7 +9368,7 @@ static bool make_join_select(JOIN *join,
 	/* Range uses longer key;  Use this instead of ref on key */
 	tab->type=JT_ALL;
 	use_quick_range=1;
-	tab->use_quick=1;
+	tab->use_quick=QS_RANGE;
         tab->ref.key= -1;
 	tab->ref.key_parts=0;		// Don't use ref key.
 	join->best_positions[i].records_read= rows2double(tab->quick->records);
@@ -9440,10 +9561,10 @@ static bool make_join_select(JOIN *join,
 			     (sel->quick_keys.is_clear_all() ||
 			      (sel->quick &&
 			       (sel->quick->records >= 100L)))) ?
-	      2 : 1;
+	      QS_DYNAMIC_RANGE : QS_RANGE;
 	    sel->read_tables= used_tables & ~current_map;
 	  }
-	  if (i != join->const_tables && tab->use_quick != 2 &&
+	  if (i != join->const_tables && tab->use_quick != QS_DYNAMIC_RANGE &&
               !tab->first_inner)
 	  {					/* Read with cache */
 	    if (cond &&
@@ -10165,7 +10286,7 @@ uint check_join_cache_usage(JOIN_TAB *ta
     psergey-todo: why the below when execution code seems to handle the
     "range checked for each record" case?
   */
-  if (tab->use_quick == 2)
+  if (tab->use_quick == QS_DYNAMIC_RANGE)
     goto no_join_cache;
   
   /*
@@ -10235,6 +10356,7 @@ uint check_join_cache_usage(JOIN_TAB *ta
          !tab->cache->init()))
     {
       *icp_other_tables_ok= FALSE;
+      DBUG_ASSERT(might_do_join_buffering(cache_level, tab));
       return JOIN_CACHE::ALG_BNL | force_unlinked_cache;
     }
     goto no_join_cache;
@@ -10258,6 +10380,7 @@ uint check_join_cache_usage(JOIN_TAB *ta
             (tab->cache= new JOIN_CACHE_BKA_UNIQUE(join, tab, flags, prev_cache)))
            ) && !tab->cache->init())))
     {
+      DBUG_ASSERT(might_do_join_buffering(cache_level, tab));
       if (cache_level <= 6)
         return JOIN_CACHE::ALG_BKA | force_unlinked_cache;
       return JOIN_CACHE::ALG_BKA_UNIQUE | force_unlinked_cache;
@@ -10735,7 +10858,7 @@ make_join_readinfo(JOIN *join, ulonglong
         tab[-1].next_select=sub_select_cache;
       }
       /* These init changes read_record */
-      if (tab->use_quick == 2)
+      if (tab->use_quick == QS_DYNAMIC_RANGE)
       {
 	join->thd->server_status|=SERVER_QUERY_NO_GOOD_INDEX_USED;
 	tab->read_first_record= join_init_quick_read_record;
@@ -17856,7 +17979,8 @@ test_if_quick_select(JOIN_TAB *tab)
 static 
 bool test_if_use_dynamic_range_scan(JOIN_TAB *join_tab)
 {
-    return (join_tab->use_quick == 2 && test_if_quick_select(join_tab) > 0);
+    return (join_tab->use_quick == QS_DYNAMIC_RANGE && 
+            test_if_quick_select(join_tab) > 0);
 }
 
 int join_init_read_record(JOIN_TAB *tab)
@@ -19549,7 +19673,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
           */
           DBUG_ASSERT(tab->select->quick);
           tab->type=JT_ALL;
-          tab->use_quick=1;
+          tab->use_quick=QS_RANGE;
           tab->ref.key= -1;
           tab->ref.key_parts=0;		// Don't use ref key.
           tab->read_first_record= join_init_read_record;
@@ -22360,7 +22484,7 @@ void select_describe(JOIN *join, bool ne
         }
         if (tab->select)
 	{
-	  if (tab->use_quick == 2)
+	  if (tab->use_quick == QS_DYNAMIC_RANGE)
 	  {
             /* 4 bits per 1 hex digit + terminating '\0' */
             char buf[MAX_KEY / 4 + 1];

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2010-08-19 07:10:58 +0000
+++ b/sql/sql_select.h	2010-09-02 07:34:10 +0000
@@ -179,6 +179,11 @@ inline bool sj_is_materialize_strategy(u
   return strategy >= SJ_OPT_MATERIALIZE_LOOKUP;
 }
 
+/** 
+    Bits describing quick select type
+*/
+enum quick_type { QS_NONE, QS_RANGE, QS_DYNAMIC_RANGE};
+
 typedef struct st_join_table : public Sql_alloc
 {
   st_join_table();
@@ -244,12 +249,13 @@ typedef struct st_join_table : public Sq
   ha_rows       read_time;
   
   table_map	dependent,key_dependent;
-  uint		use_quick,index;
+  uint		index;
   uint		status;				///< Save status for cache
   uint		used_fields,used_fieldlength,used_blobs;
   uint          used_null_fields;
   uint          used_rowid_fields;
   uint          used_uneven_bit_fields;
+  enum quick_type use_quick;
   enum join_type type;
   bool		cached_eq_ref_table,eq_ref_table,not_used_in_distinct;
   /* TRUE <=> index-based access method must return records in order */
@@ -417,7 +423,6 @@ st_join_table::st_join_table()
 
     dependent(0),
     key_dependent(0),
-    use_quick(0),
     index(0),
     status(0),
     used_fields(0),
@@ -426,6 +431,7 @@ st_join_table::st_join_table()
     used_null_fields(0),
     used_rowid_fields(0),
     used_uneven_bit_fields(0),
+    use_quick(QS_NONE),
     type(JT_UNKNOWN),
     cached_eq_ref_table(FALSE),
     eq_ref_table(FALSE),

=== modified file 'sql/sql_test.cc'
--- a/sql/sql_test.cc	2010-08-23 12:05:47 +0000
+++ b/sql/sql_test.cc	2010-09-02 07:34:10 +0000
@@ -215,7 +215,7 @@ TEST_join(JOIN *join)
     if (tab->select)
     {
       char buf[MAX_KEY/8+1];
-      if (tab->use_quick == 2)
+      if (tab->use_quick == QS_DYNAMIC_RANGE)
 	fprintf(DBUG_FILE,
 		"                  quick select checked for each record (keys: %s)\n",
 		tab->select->quick_keys.print(buf));


Attachment: [text/bzr-bundle] bzr/jorgen.loland@oracle.com-20100902073410-mgm68fppqfsvn5ju.bundle
Thread
bzr push into mysql-next-mr-bugfixing branch (jorgen.loland:3233 to 3234)Bug#49129Jorgen Loland2 Sep