bzr commit into mysql-6.0-opt-subqueries branch (sergefp:2747)

[Sergey Petrunia]

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

 2747 Sergey Petrunia	2008-12-22 [merge]
      Merge
modified:
  mysql-test/r/subselect3.result
  mysql-test/t/subselect3.test
  sql/sql_select.cc

=== modified file 'mysql-test/r/subselect3.result'
--- a/mysql-test/r/subselect3.result	2008-12-22 19:03:25 +0000
+++ b/mysql-test/r/subselect3.result	2008-12-22 20:10:35 +0000
@@ -1152,6 +1152,18 @@ 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;
 
 BUG#40118 Crash when running Batched Key Access and requiring one match for each key
 
@@ -1177,4 +1189,3 @@ a
 8
 9
 set join_cache_level=@save_join_cache_level;
-drop table t0, t1;

=== modified file 'mysql-test/t/subselect3.test'
--- a/mysql-test/t/subselect3.test	2008-12-13 20:01:27 +0000
+++ b/mysql-test/t/subselect3.test	2008-12-22 20:10:35 +0000
@@ -946,6 +946,20 @@ explain select * from t0 where a in (sel
 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;
+
 --echo 
 --echo BUG#40118 Crash when running Batched Key Access and requiring one match for each key
 --echo
@@ -961,4 +975,4 @@ set @save_join_cache_level=@@join_cache_
 set join_cache_level=6;
 select * from t0 where t0.a in (select t1.a from t1 where t1.b=0); 
 set join_cache_level=@save_join_cache_level;
-drop table t0, t1;
\ No newline at end of file
+drop table t0, t1;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2008-12-22 20:00:22 +0000
+++ b/sql/sql_select.cc	2008-12-22 20:10:35 +0000
@@ -9663,7 +9663,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,