bzr commit into mysql-6.0-opt branch (sergefp:2730) Bug#42740

[Sergey Petrunia]

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

 2730 Sergey Petrunia	2009-03-21
      BUG#42740: crash in optimize_semijoin_nests
      - Run pull_out_semijoin_tables() after range analysis, not before it. We need to
        do it in this order because range analysis may mark tables as constant, and then
        pull_out_semijoin_nests() will remove semi-join nests that contain only constant 
        tables. This removal operation is crucial as setup_sj_materialization() cannot deal 
        with semi-join nests that have only constant tables.
modified:
  mysql-test/r/subselect_sj2.result
  mysql-test/t/subselect_sj2.test
  sql/sql_select.cc

per-file messages:
  mysql-test/r/subselect_sj2.result
    BUG#42740: crash in optimize_semijoin_nests
    - Testcase
  mysql-test/t/subselect_sj2.test
    BUG#42740: crash in optimize_semijoin_nests
    - Testcase
  sql/sql_select.cc
    BUG#42740: crash in optimize_semijoin_nests
    - Run pull_out_semijoin_tables() after range analysis, not before it. We need to
      do it in this order because range analysis may mark tables as constant, and then
      pull_out_semijoin_nests() will remove semi-join nests that contain only constant 
      tables. This removal operation is crucial as setup_sj_materialization() cannot deal 
      with semi-join nests that have only constant tables.
=== modified file 'mysql-test/r/subselect_sj2.result'
--- a/mysql-test/r/subselect_sj2.result	2009-02-24 20:44:01 +0000
+++ b/mysql-test/r/subselect_sj2.result	2009-03-21 15:31:38 +0000
@@ -680,3 +680,12 @@ select count(a) from t2 where a in ( SEL
 count(a)
 1000
 drop table t0,t1,t2,t3;
+
+BUG#42740: crash in optimize_semijoin_nests
+
+create table t1 (c6 timestamp,key (c6)) engine=innodb;
+create table t2 (c2 double) engine=innodb;
+explain select 1 from t2 where c2 = any (select log10(null) from t1 where c6 <null)  ;
+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
+drop table t1, t2;

=== modified file 'mysql-test/t/subselect_sj2.test'
--- a/mysql-test/t/subselect_sj2.test	2009-02-24 20:44:01 +0000
+++ b/mysql-test/t/subselect_sj2.test	2009-03-21 15:31:38 +0000
@@ -863,3 +863,12 @@ explain select count(a) from t2 where a 
 select count(a) from t2 where a in ( SELECT  a FROM t3);
 
 drop table t0,t1,t2,t3;
+
+--echo
+--echo BUG#42740: crash in optimize_semijoin_nests
+--echo
+create table t1 (c6 timestamp,key (c6)) engine=innodb;
+create table t2 (c2 double) engine=innodb;
+explain select 1 from t2 where c2 = any (select log10(null) from t1 where c6 <null)  ;
+drop table t1, t2;
+

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2009-03-20 13:47:48 +0000
+++ b/sql/sql_select.cc	2009-03-21 15:31:38 +0000
@@ -4341,9 +4341,6 @@ make_join_statistics(JOIN *join, TABLE_L
     }
   }
 
-  if (pull_out_semijoin_tables(join))
-    DBUG_RETURN(TRUE);
-
   /* Calc how many (possible) matched records in each table */
 
   for (s=stat ; s < stat_end ; s++)
@@ -4426,6 +4423,9 @@ make_join_statistics(JOIN *join, TABLE_L
     }
   }
 
+  if (pull_out_semijoin_tables(join))
+    DBUG_RETURN(TRUE);
+
   join->join_tab=stat;
   join->map2table=stat_ref;
   join->all_tables= table_vector;
@@ -4500,6 +4500,9 @@ static bool optimize_semijoin_nests(JOIN
   {
     while ((sj_nest= sj_list_it++))
     {
+      /* semi-join nests with only constant tables are not valid */
+      DBUG_SASSERT(sj_nest->sj_inner_tables & ~join->const_tables);
+
       sj_nest->sj_mat_info= NULL;
       if (sj_nest->sj_inner_tables && /* not everything was pulled out */
           !sj_nest->sj_subq_pred->is_correlated &&