bzr push into mysql-6.0-opt-subqueries branch (sergefp:2742 to 2743) WL#3985

[Sergey Petrunia]

 2743 Sergey Petrunia	2008-12-13
      WL#3985: Subquery optimization: smart choice between semi-join and materialization
      - In fix_semijoin_strategies_for_picked_join_order(), do set 
        join->cur_sj_inner_tables before calling best_access_path(). Failure to do so causes
        best_access_path not to follow what it did on join optimization.
modified:
  mysql-test/r/subselect3.result
  mysql-test/t/subselect3.test
  sql/sql_select.cc

 2742 Sergey Petrunia	2008-12-12
      Fix share/errmsg.txt. kdiff3 has converted it into utf8 during merge
modified:
  sql/share/errmsg.txt

=== modified file 'mysql-test/r/subselect3.result'
--- a/mysql-test/r/subselect3.result	2008-11-26 21:23:50 +0000
+++ b/mysql-test/r/subselect3.result	2008-12-12 22:07:06 +0000
@@ -970,7 +970,15 @@ explain select * from t3 where a in (sel
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	range	kp1	kp1	5	NULL	48	Using where; Using index; LooseScan
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	100	Using where
-drop table t1, t3;
+create table t4 (pk int primary key);
+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	t1	range	kp1	kp1	5	NULL	48	Using index condition; Using MRR; LooseScan
+1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t1.c	1	Using index; FirstMatch(t1)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	100	Using where
+drop table t1, t3, t4;
 create table t1 (a int) as select * from t0 where a < 5;
 set @save_max_heap_table_size=@@max_heap_table_size;
 set @@optimizer_switch='no_firstmatch,no_materialization';
@@ -1085,7 +1093,7 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t0	system	NULL	NULL	NULL	NULL	1	
 1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	10	Using where; Materialize; Scan
 1	PRIMARY	t3	ref	a	a	5	test.t4.x	10	Using where
-drop table t0,t1,t2,t3;
+drop table t0,t1,t2,t3,t4;
 create table t0 (a int);
 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 create table t1 (a int, b int, filler char(100), key(a,b));
@@ -1109,5 +1117,38 @@ explain select * from t1 where (a,b) in 
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	100	Materialize
+set @save_optimizer_search_depth=@@optimizer_search_depth;
+set @@optimizer_search_depth=63;
+explain select * from t1 where (a,b) in (select a,b from t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	100	Materialize
+set @@optimizer_search_depth=@save_optimizer_search_depth;
 set @@optimizer_switch='';
 drop table t0, t1, t2;
+create table t0 (a decimal(4,2));
+insert into t0 values (10.24), (22.11);
+create table t1 as select * from t0;
+insert into t1 select * from t0;
+explain select * from t0 where a in (select a from t1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where; FirstMatch(t0)
+select * from t0 where a in (select a from t1);
+a
+10.24
+22.11
+drop table t0, t1;
+create table t0(a date);
+insert into t0 values ('2008-01-01'),('2008-02-02');
+create table t1 as select * from t0;
+insert into t1 select * from t0;
+explain select * from t0 where a in (select a from t1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where; FirstMatch(t0)
+select * from t0 where a in (select a from t1);
+a
[phone]
[phone]
+drop table t0, t1;

=== modified file 'mysql-test/t/subselect3.test'
--- a/mysql-test/t/subselect3.test	2008-11-26 21:23:50 +0000
+++ b/mysql-test/t/subselect3.test	2008-12-12 22:07:06 +0000
@@ -800,7 +800,13 @@ insert into t3 select A.a + 10*B.a from 
 
 explain select * from t3 where a in (select kp1 from t1 where kp1<20);
 
-drop table t1, t3;
+create table t4 (pk int primary key);
+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);
+
+drop table t1, t3, t4;
 
 #
 # Test if we handle duplicate elimination temptable overflowing to disk
@@ -883,7 +889,7 @@ insert into t0 values(1,1);
 explain select * from t0, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a >30);
 create table t4 as select a as x, a as y from t1;
 explain select * from t0, t3 where (t3.a, t3.b) in (select x,y from t4) and (t3.a < 10 or t3.a >30);
-drop table t0,t1,t2,t3;
+drop table t0,t1,t2,t3,t4;
 
 #
 # LooseScan with ref access
@@ -900,7 +906,7 @@ explain select * from t2 where (b,a) in 
 drop table t1,t2;
 
 #
-# Multi-column sj-materialzation with lookups
+# Multi-column sj-materialization with lookups
 #
 create table t1 (a int, b int);
 insert into t1 select a,a from t0;
@@ -909,6 +915,32 @@ insert into t2 select A.a + 10*B.a, A.a 
 
 set @@optimizer_switch='no_firstmatch';
 explain select * from t1 where (a,b) in (select a,b from t2);
+
+# A smallish test if find_best() still works for semi-join optimization:
+set @save_optimizer_search_depth=@@optimizer_search_depth;
+set @@optimizer_search_depth=63;
+explain select * from t1 where (a,b) in (select a,b from t2);
+set @@optimizer_search_depth=@save_optimizer_search_depth;
 set @@optimizer_switch='';
 
 drop table t0, t1, t2;
+
+
+#
+# Primitive SJ-Materialization tests for DECIMAL and DATE
+#
+create table t0 (a decimal(4,2));
+insert into t0 values (10.24), (22.11);
+create table t1 as select * from t0;
+insert into t1 select * from t0;
+explain select * from t0 where a in (select a from t1);
+select * from t0 where a in (select a from t1);
+drop table t0, t1;
+
+create table t0(a date);
+insert into t0 values ('2008-01-01'),('2008-02-02');
+create table t1 as select * from t0;
+insert into t1 select * from t0;
+explain select * from t0 where a in (select a from t1);
+select * from t0 where a in (select a from t1);
+drop table t0, t1;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2008-12-12 20:36:12 +0000
+++ b/sql/sql_select.cc	2008-12-12 22:07:06 +0000
@@ -7774,6 +7774,7 @@ static void fix_semijoin_strategies_for_
         rem_tables |= join->best_positions[i].table->table->map;
 
       POSITION dummy;
+      join->cur_sj_inner_tables= 0;
       for (i= first + sjm->tables; i <= tablenr; i++)
       {
         best_access_path(join, join->best_positions[i].table, rem_tables, i, FALSE,
@@ -7800,6 +7801,7 @@ static void fix_semijoin_strategies_for_
         Re-run best_access_path to produce best access methods that do not use
         join buffering
       */ 
+      join->cur_sj_inner_tables= 0;
       for (idx= first; idx <= tablenr; idx++)
       {
         if (join->best_positions[idx].use_join_buffer)
@@ -7829,6 +7831,7 @@ static void fix_semijoin_strategies_for_
         Re-run best_access_path to produce best access methods that do not use
         join buffering
       */ 
+      join->cur_sj_inner_tables= 0;
       for (idx= first; idx <= tablenr; idx++)
       {
         if (join->best_positions[idx].use_join_buffer || (idx == first))