bzr commit into mysql-6.0-opt-subqueries branch (sergefp:2733) WL#3985

[Sergey Petrunia]

#At file:///home/spetrunia/dev/mysql-6.0-subq-r17/

 2733 Sergey Petrunia	2008-11-24
      WL#3985: Subqueries: Smart choice between semi-join and materialization
      - More purecov annotations
      - Fixed a crash in fix_semijoin_strategies_for_picked_join_order() which occurred
for certain join orders
        when SJ-Materialization was applied to a range of tables in the middle of the join
order 
      - More test coverage
modified:
  mysql-test/r/subselect3.result
  mysql-test/t/subselect3.test
  sql/sql_select.cc

per-file messages:
  mysql-test/r/subselect3.result
    WL#3985: Subqueries: Smart choice between semi-join and materialization
    - More test coverage
  mysql-test/t/subselect3.test
    WL#3985: Subqueries: Smart choice between semi-join and materialization
    - More test coverage
  sql/sql_select.cc
    WL#3985: Subqueries: Smart choice between semi-join and materialization
    - More purecov annotations
    - Fixed a crash in fix_semijoin_strategies_for_picked_join_order() which occurred for
certain join orders
      when SJ-Materialization was applied to a range of tables in the middle of the join
order
=== modified file 'mysql-test/r/subselect3.result'
--- a/mysql-test/r/subselect3.result	2008-11-22 00:44:45 +0000
+++ b/mysql-test/r/subselect3.result	2008-11-23 22:59:21 +0000
@@ -954,4 +954,84 @@ 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 t0, t1, t3;
+drop table t1, t3;
+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';
+set @@max_heap_table_size= 16384;
+explain select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	E	ALL	NULL	NULL	NULL	NULL	5	Start temporary
+1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	Using join buffer
+1	PRIMARY	B	ALL	NULL	NULL	NULL	NULL	10	Using join buffer
+1	PRIMARY	C	ALL	NULL	NULL	NULL	NULL	10	Using join buffer
+1	PRIMARY	D	ALL	NULL	NULL	NULL	NULL	10	Using where; End temporary; Using join buffer
+flush status;
+select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E);
+count(*)
+4999
+show status like 'Created_tmp_disk_tables';
+Variable_name	Value
+Created_tmp_disk_tables	1
+set @save_max_heap_table_size=@@max_heap_table_size;
+set @@optimizer_switch=default;
+drop table t0, t1;
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2(a int);
+insert into t2 values (1),(2);
+create table t3 ( a int , filler char(100), key(a));
+insert into t3 select A.a + 10*B.a, 'filler' from t0 A, t0 B;
+explain select * from t3 where a in (select a from t2) and (a > 5 or a < 10);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Materialize; Scan
+1	PRIMARY	t3	ref	a	a	5	test.t2.a	1	
+select * from t3 where a in (select a from t2);
+a	filler
+1	filler
+2	filler
+drop table t0, t2, t3;
+set @@optimizer_switch='no_firstmatch,no_materialization';
+create table t1 (a date);
+insert into t1 values ('2008-01-01'),('2008-01-01'),('2008-02-01'),('2008-02-01');
+create table t2 (a int);
+insert into t2 values (1),(2);
+create table t3 (a char(10));
+insert into t3 select * from t1;
+insert into t3 values (1),(2);
+explain select * from t2 where a in (select a from t1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where; End temporary; Using join buffer
+explain select * from t2 where a in (select a from t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer
+explain select * 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	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary; Using join buffer
+explain select * from t1 where a in (select a from t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Start temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	6	Using where; End temporary; Using join buffer
+drop table t1, t2, t3;
+create table t1 (a decimal);
+insert into t1 values (1),(2);
+explain select * from t1 where a in (select a from t1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer
+drop table t1;
+set @@optimizer_switch=default;
+create table t1 (a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2 as select * from t1;
+create table t3 (a int, filler char(100), key(a));
+insert into t3 select A.a + 10*B.a, 'filler' from t1 A, t1 B, t1 C;
+explain select * from t1, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a
>30) and t1.a =3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	10	Using where
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	10	Using where; Materialize; Scan
+1	PRIMARY	t3	ref	a	a	5	test.t2.a	10	
+drop table t1,t2,t3;

=== modified file 'mysql-test/t/subselect3.test'
--- a/mysql-test/t/subselect3.test	2008-11-22 00:44:45 +0000
+++ b/mysql-test/t/subselect3.test	2008-11-23 22:59:21 +0000
@@ -778,4 +778,70 @@ 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 t0, t1, t3;
+drop table t1, t3;
+
+#
+# Test if we handle duplicate elimination temptable overflowing to disk
+#
+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';
+set @@max_heap_table_size= 16384;
+
+explain select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E);
+flush status;
+select count(*) from t0 A, t0 B, t0 C, t0 D where D.a in (select a from t1 E);
+show status like 'Created_tmp_disk_tables';
+set @save_max_heap_table_size=@@max_heap_table_size;
+set @@optimizer_switch=default;
+drop table t0, t1;
+
+#
+# Materialize + Scan + ref access to the subsequent table based on scanned
+# value
+# 
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2(a int);
+insert into t2 values (1),(2);
+create table t3 ( a int , filler char(100), key(a)); 
+insert into t3 select A.a + 10*B.a, 'filler' from t0 A, t0 B;
+explain select * from t3 where a in (select a from t2) and (a > 5 or a < 10); 
+select * from t3 where a in (select a from t2); 
+
+drop table t0, t2, t3;
+
+#
+# DATETIME type checks
+#
+set @@optimizer_switch='no_firstmatch,no_materialization';
+create table t1 (a date);
+insert into t1 values ('2008-01-01'),('2008-01-01'),('2008-02-01'),('2008-02-01');
+create table t2 (a int);
+insert into t2 values (1),(2);
+create table t3 (a char(10));
+insert into t3 select * from t1;
+insert into t3 values (1),(2);
+explain select * from t2 where a in (select a from t1);
+explain select * from t2 where a in (select a from t2);
+explain select * from t2 where a in (select a from t3);
+explain select * from t1 where a in (select a from t3);
+drop table t1, t2, t3;
+create table t1 (a decimal);
+insert into t1 values (1),(2);
+explain select * from t1 where a in (select a from t1);
+drop table t1;
+set @@optimizer_switch=default;
+
+#
+# SJ-Materialization-scan for non-first table
+#
+create table t1 (a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2 as select * from t1;
+create table t3 (a int, filler char(100), key(a));
+insert into t3 select A.a + 10*B.a, 'filler' from t1 A, t1 B, t1 C;
+explain select * from t1, t3 where t3.a in (select a from t2) and (t3.a < 10 or t3.a
>30) and t1.a =3;
+drop table t1,t2,t3;
+

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2008-11-22 00:30:49 +0000
+++ b/sql/sql_select.cc	2008-11-23 22:59:21 +0000
@@ -630,7 +630,7 @@ JOIN::prepare(Item ***rref_pointer_array
         thd->where= save_where;
         in_subs->emb_on_expr_nest= thd->thd_marker.emb_on_expr_nest;
         if (failure)
-          DBUG_RETURN(-1);
+          DBUG_RETURN(-1); /* purecov: deadcode */
         /*
           Check if the left and right expressions have the same # of
           columns, i.e. we don't have a case like 
@@ -1371,7 +1371,7 @@ static int clear_sj_tmp_tables(JOIN *joi
   while ((table= it++))
   {
     if ((res= table->file->ha_delete_all_rows()))
-      return res;
+      return res; /* purecov: inspected */
   }
 
   SJ_MATERIALIZATION_INFO *sjm;
@@ -3947,7 +3947,7 @@ static uint get_tmp_table_rec_length(Lis
       break;
     case ROW_RESULT:
     default:
-      DBUG_ASSERT(0);
+      DBUG_ASSERT(0); /* purecov: deadcode */
       break;
     }
   }
@@ -7856,7 +7856,7 @@ static void fix_semijoin_strategies_for_
       join->best_positions[first].n_sj_tables= tablenr - first + 1;
     }
     
-    uint i_end=join->best_positions[first].n_sj_tables;
+    uint i_end= first + join->best_positions[first].n_sj_tables;
     for (uint i= first; i < i_end; i++)
       handled_tabs |= join->best_positions[i].table->table->map;
 
@@ -9338,7 +9338,7 @@ end_sj_materialize(JOIN *join, JOIN_TAB 
     }
     fill_record(thd, table->field, sjm->sjm_table_cols, 1);
     if (thd->is_error())
-      DBUG_RETURN(NESTED_LOOP_ERROR);
+      DBUG_RETURN(NESTED_LOOP_ERROR); /* purecov: inspected */
     if ((error= table->file->ha_write_row(table->record[0])))
     {
       /* create_myisam_from_heap will generate error if needed */
@@ -9346,7 +9346,7 @@ end_sj_materialize(JOIN *join, JOIN_TAB 
           create_internal_tmp_table_from_heap(thd, table,
                                               sjm->sjm_table_param.start_recinfo, 
                                               &sjm->sjm_table_param.recinfo,
error, 1))
-        DBUG_RETURN(NESTED_LOOP_ERROR);
+        DBUG_RETURN(NESTED_LOOP_ERROR); /* purecov: inspected */
     }
   }
   DBUG_RETURN(NESTED_LOOP_OK);
@@ -9417,8 +9417,9 @@ Item *create_subq_in_equalities(THD *thd
   Item *res= NULL;
   if (subq_pred->left_expr->cols() == 1)
   {
-    res= new Item_func_eq(subq_pred->left_expr, 
-                          new Item_field(sjm->table->field[0]));
+    if (!(res= new Item_func_eq(subq_pred->left_expr,
+                                new Item_field(sjm->table->field[0]))))
+      return NULL; /* purecov: inspected */
   }
   else
   {
@@ -9428,7 +9429,7 @@ Item *create_subq_in_equalities(THD *thd
       if (!(conj= new Item_func_eq(subq_pred->left_expr->element_index(i), 
                                    new Item_field(sjm->table->field[i]))) ||
           !(res= and_items(res, conj)))
-        return NULL;
+        return NULL; /* purecov: inspected */
     }
   }
   if (res->fix_fields(thd, &res))
@@ -15588,7 +15589,7 @@ sub_select_sjm(JOIN *join, JOIN_TAB *joi
   {
     /* Do index lookup in the materialized table */
     if ((res= join_read_key2(join_tab, sjm->table, sjm->tab_ref)) == 1)
-      return NESTED_LOOP_ERROR;
+      return NESTED_LOOP_ERROR; /* purecov: inspected */
     if (res || !sjm->in_equality->val_int())
       return NESTED_LOOP_NO_MORE_ROWS;
   }