bzr push into mysql-6.0-opt branch (timour:2699 to 2700) Bug#37896

[Timour Katchaounov]

 2700 Timour Katchaounov	2008-11-19 [merge]
      BUG#37896: Assertion on entry of Item_in_subselect::exec on subquery with AND NOT
      
      Merge with mysql-6.0-opt branch.
modified:
  mysql-test/r/subselect_mat.result
  mysql-test/t/subselect_mat.test
  sql/item_subselect.cc
  sql/sql_select.cc

 2699 Sergey Petrunia	2008-11-01 [merge]
      mysql-6.0 -> mysql-6.0-opt merge
modified:
  .bzrignore
  BUILD/compile-dist
  configure.in
  zlib/gzio.c

=== modified file 'mysql-test/r/subselect_mat.result'
--- a/mysql-test/r/subselect_mat.result	2008-04-24 23:59:38 +0000
+++ b/mysql-test/r/subselect_mat.result	2008-10-17 09:46:06 +0000
@@ -1145,3 +1145,33 @@ t2.a = 3 and not t2.a not in (select t2.
 1
 1
 drop table t2;
+create table t1 (a1 int key);
+create table t2 (b1 int);
+insert into t1 values (5);
+explain select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
+min(a1)
+set @@optimizer_switch='no_materialization';
+explain select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+2	DEPENDENT SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
+min(a1)
+set @@optimizer_switch='no_semijoin';
+explain select min(a1) from t1 where 7 in (select b1 from t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+2	SUBQUERY	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+select min(a1) from t1 where 7 in (select b1 from t2);
+min(a1)
+set @@optimizer_switch='no_materialization';
+explain select min(a1) from t1 where 7 in (select b1 from t2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const
tables
+select min(a1) from t1 where 7 in (select b1 from t2);
+min(a1)
+NULL

=== modified file 'mysql-test/t/subselect_mat.test'
--- a/mysql-test/t/subselect_mat.test	2008-04-24 23:59:38 +0000
+++ b/mysql-test/t/subselect_mat.test	2008-10-17 09:46:06 +0000
@@ -806,3 +806,29 @@ select 1 from t2 where  
     t2.a = 3 and not t2.a not in (select t2.b from t2);
 drop table t2;
 
+#
+# BUG#37896 Assertion on entry of Item_in_subselect::exec on subquery with AND NOT
+#
+create table t1 (a1 int key);
+create table t2 (b1 int);
+insert into t1 values (5);
+
+# Query with group by, executed via materialization
+explain select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
+select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
+# Query with group by, executed via IN=>EXISTS
+set @@optimizer_switch='no_materialization';
+explain select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
+select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
+
+# Executed with materialization
+set @@optimizer_switch='no_semijoin';
+explain select min(a1) from t1 where 7 in (select b1 from t2);
+select min(a1) from t1 where 7 in (select b1 from t2);
+# Executed with semi-join. Notice, this time we get a different result (NULL).
+# This is the only correct result of all four queries. This difference is
+# filed as BUG#40037.
+set @@optimizer_switch='no_materialization';
+explain select min(a1) from t1 where 7 in (select b1 from t2);
+select min(a1) from t1 where 7 in (select b1 from t2);
+

=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc	2008-10-20 09:16:47 +0000
+++ b/sql/item_subselect.cc	2008-11-19 12:55:52 +0000
@@ -1896,7 +1896,11 @@ bool Item_in_subselect::init_left_expr_c
   bool use_result_field= FALSE;
 
   outer_join= unit->outer_select()->join;
-  if (!outer_join || !outer_join->tables)
+  /*
+    An IN predicate might be evaluated in a query for which all tables have
+    been optimzied away.
+  */ 
+  if (!outer_join || !outer_join->tables || !outer_join->tables_list)
     return TRUE;
   /*
     If we use end_[send | write]_group to handle complete rows of the outer

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2008-10-30 18:02:08 +0000
+++ b/sql/sql_select.cc	2008-11-19 12:55:52 +0000
@@ -1557,6 +1557,9 @@ JOIN::optimize()
                                  QT_ORDINARY););
         conds= table_independent_conds;
       }
+      /* Create all structures needed for materialized subquery execution. */
+      if (setup_subquery_materialization())
+        DBUG_RETURN(1);
     }
   }
   if (!tables_list)