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

[Sergey Petrunia]

#At file:///home/spetrunia/dev/mysql-6.0-subq-r17/ based on
revid:sergefp@stripped

 2744 Sergey Petrunia	2008-12-17
      WL#3985: Subquery optimization: smart choice between semi-join and materialization
      -  fix a typo crash in multi-table sj-materializaion
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: Subquery optimization: smart choice between semi-join and materialization
    -  fix a typo crash in multi-table sj-materializaion
  mysql-test/t/subselect3.test
    WL#3985: Subquery optimization: smart choice between semi-join and materialization
    -  fix a typo crash in multi-table sj-materializaion
  sql/sql_select.cc
    WL#3985: Subquery optimization: smart choice between semi-join and materialization
    -  fix a typo crash in multi-table sj-materializaion
=== modified file 'mysql-test/r/subselect3.result'
--- a/mysql-test/r/subselect3.result	2008-12-12 22:07:06 +0000
+++ b/mysql-test/r/subselect3.result	2008-12-17 16:51:20 +0000
@@ -1152,3 +1152,15 @@ a
 2008-01-01
 2008-02-02
 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 t1 as select a as a, a as b, a as c from t0 where a < 3;
+create table t2 as select a as a, a as b from t0 where a < 3;
+insert into t2 select * from t2;
+explain select * from t1 where (a,b,c) in (select X.a, Y.a, Z.a from t2 X, t2 Y, t2 Z
where X.b=33);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	PRIMARY	X	ALL	NULL	NULL	NULL	NULL	6	Using where; Start materialize
+1	PRIMARY	Y	ALL	NULL	NULL	NULL	NULL	6	Using join buffer
+1	PRIMARY	Z	ALL	NULL	NULL	NULL	NULL	6	End materialize; Using join buffer
+drop table t0,t1,t2;

=== modified file 'mysql-test/t/subselect3.test'
--- a/mysql-test/t/subselect3.test	2008-12-12 22:07:06 +0000
+++ b/mysql-test/t/subselect3.test	2008-12-17 16:51:20 +0000
@@ -944,3 +944,17 @@ 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;
+
+#
+# Fix a trivial crash with SJ-Materialization lookup, multiple tables in the
+# subquery, and a condition on some of inner tables but not others
+#
+create table t0(a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 as select a as a, a as b, a as c from t0 where a < 3;
+create table t2 as select a as a, a as b from t0 where a < 3;
+insert into t2 select * from t2;
+
+explain select * from t1 where (a,b,c) in (select X.a, Y.a, Z.a from t2 X, t2 Y, t2 Z
where X.b=33);
+
+drop table t0,t1,t2;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2008-12-12 22:07:06 +0000
+++ b/sql/sql_select.cc	2008-12-17 16:51:20 +0000
@@ -9563,7 +9563,7 @@ bool setup_sj_materialization(JOIN_TAB *
     for (i= 0; i < sjm->tables; i++)
     {
       remove_sj_conds(&tab[i].select_cond);
-      if (tab->select)
+      if (tab[i].select)
         remove_sj_conds(&tab[i].select->cond);
     }
     if (!(sjm->in_equality= create_subq_in_equalities(thd, sjm,