bzr commit into mysql-6.0-opt branch (sergefp:2690) Bug#36896

[Sergey Petrunia]

#At file:///home/spetrunia/dev/mysql-6.0-bugs/

 2690 Sergey Petrunia	2008-09-10
      BUG#36896: Server crash on SELECT FROM DUAL
      - Disable semi-join and materialization if subquery's parent select is a
        confluent SELECT ... FROM DUAL:
         = Semi-join runtime cannot handle semi-joins w/o outer tables
         = Materialization relies on parent select calling
setup_subquery_materialization()
           before trying to evaluate subquery items. SELECT ... FROM dual buypasses many
           steps in join execution and doesn't meet this requirement.
         And it's not worth making SELECT ... FROM dual able to work with materialization
         because there's no benefit to use materialization if the subquery predicate will
         be evaluated only once. (it is possible to construct cases where it will be
evaluated
         many times but they are not practially important)
modified:
  mysql-test/r/ps_ddl.result
  mysql-test/r/subselect3.result
  mysql-test/t/ps_ddl.test
  mysql-test/t/subselect3.test
  sql/sql_select.cc

per-file messages:
  mysql-test/r/ps_ddl.result
    BUG#36896: Server crash on SELECT FROM DUAL
    - Enable the testcase that was disabled because of this bug
  mysql-test/r/subselect3.result
    BUG#36896: Server crash on SELECT FROM DUAL
    - Testcase
  mysql-test/t/ps_ddl.test
    BUG#36896: Server crash on SELECT FROM DUAL
    - Enable the testcase that was disabled because of this bug
  mysql-test/t/subselect3.test
    BUG#36896: Server crash on SELECT FROM DUAL
    - Testcase
  sql/sql_select.cc
    BUG#36896: Server crash on SELECT FROM DUAL
    - Disable semi-join and materialization if subquery's parent select is a
      confluent SELECT ... FROM DUAL:
       = Semi-join runtime cannot handle semi-joins w/o outer tables
       = Materialization relies on parent select calling setup_subquery_materialization()
         before trying to evaluate subquery items. SELECT ... FROM dual buypasses many
         steps in join execution and doesn't meet this requirement.
       And it's not worth making SELECT ... FROM dual able to work with materialization
       because there's no benefit to use materialization if the subquery predicate will
       be evaluated only once. (it is possible to construct cases where it will be
evaluated
       many times but they are not practially important)
=== modified file 'mysql-test/r/ps_ddl.result'
--- a/mysql-test/r/ps_ddl.result	2008-07-10 23:29:27 +0000
+++ b/mysql-test/r/ps_ddl.result	2008-09-10 10:08:37 +0000
@@ -1610,6 +1610,18 @@ SQL statement where it is needed.
 #
 # SQLCOM_SELECT
 #
+drop table if exists t1;
+create table t1 (a int);
+prepare stmt from "select 1 as res from dual where (1) in (select * from t1)";
+drop table t1;
+create table t1 (x int);
+execute stmt;
+res
+drop table t1;
+deallocate prepare stmt;
+call p_verify_reprepare_count(1);
+SUCCESS
+
 #
 # SQLCOM_CREATE_TABLE
 #

=== modified file 'mysql-test/r/subselect3.result'
--- a/mysql-test/r/subselect3.result	2008-05-22 18:40:15 +0000
+++ b/mysql-test/r/subselect3.result	2008-09-10 10:08:37 +0000
@@ -789,3 +789,10 @@ a	b
 1	0.123
 drop table t1;
 End of 5.0 tests
+#  
+# BUG#36896: Server crash on SELECT FROM DUAL
+# 
+create table t1 (a int);
+select 1 as res from dual where (1) in (select * from t1);
+res
+drop table t1;

=== modified file 'mysql-test/t/ps_ddl.test'
--- a/mysql-test/t/ps_ddl.test	2008-07-10 23:29:27 +0000
+++ b/mysql-test/t/ps_ddl.test	2008-09-10 10:08:37 +0000
@@ -1417,17 +1417,17 @@ deallocate prepare stmt_sp;
 --echo # SQLCOM_SELECT
 --echo #
 
-#--disable_warnings
-#drop table if exists t1;
-#--enable_warnings
-#create table t1 (a int);
-#prepare stmt from "select 1 as res from dual where (1) in (select * from t1)";
-#drop table t1;
-#create table t1 (x int);
-#execute stmt;
-#drop table t1;
-#deallocate prepare stmt;
-#call p_verify_reprepare_count(1);
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+create table t1 (a int);
+prepare stmt from "select 1 as res from dual where (1) in (select * from t1)";
+drop table t1;
+create table t1 (x int);
+execute stmt;
+drop table t1;
+deallocate prepare stmt;
+call p_verify_reprepare_count(1);
 
 --echo #
 --echo # SQLCOM_CREATE_TABLE

=== modified file 'mysql-test/t/subselect3.test'
--- a/mysql-test/t/subselect3.test	2008-05-22 18:40:15 +0000
+++ b/mysql-test/t/subselect3.test	2008-09-10 10:08:37 +0000
@@ -631,3 +631,11 @@ select * from t1;
 drop table t1;
 
 --echo End of 5.0 tests
+
+--echo #  
+--echo # BUG#36896: Server crash on SELECT FROM DUAL
+--echo # 
+create table t1 (a int);
+select 1 as res from dual where (1) in (select * from t1);
+drop table t1;
+

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2008-09-05 19:23:07 +0000
+++ b/sql/sql_select.cc	2008-09-10 10:08:37 +0000
@@ -568,7 +568,7 @@ JOIN::prepare(Item ***rref_pointer_array
           4. Subquery does not use aggregate functions or HAVING
           5. Subquery predicate is at the AND-top-level of ON/WHERE clause
           6. No execution method was already chosen (by a prepared statement).
-
+          7. Parent SELECT is not a confluent "SELECT ... FROM DUAL" w/o tables
           (*). We are not in a subquery of a single table UPDATE/DELETE that 
                doesn't have a JOIN (TODO: We should handle this at some
                point by switching to multi-table UPDATE/DELETE)
@@ -584,7 +584,8 @@ JOIN::prepare(Item ***rref_pointer_array
           select_lex->outer_select()->join &&                           //
(*)
           select_lex->master_unit()->first_select()->leaf_tables &&    
// (**) 
           do_semijoin &&
-          in_subs->exec_method == Item_in_subselect::NOT_TRANSFORMED)   // 6
+          in_subs->exec_method == Item_in_subselect::NOT_TRANSFORMED && // 6
+          select_lex->outer_select()->leaf_tables)                     // 7
       {
         DBUG_PRINT("info", ("Subquery is semi-join conversion candidate"));
 
@@ -632,6 +633,11 @@ JOIN::prepare(Item ***rref_pointer_array
           2. Subquery is a single SELECT (not a UNION)
           3. Subquery is not a table-less query. In this case there is no
              point in materializing.
+          3A The upper query is not a confluent SELECT ... FROM DUAL. We
+             can't do materialization for SELECT .. FROM DUAL because it
+             does not call setup_subquery_materialization(). We could make 
+             SELECT ... FROM DUAL call that function but that doesn't seem
+             to be the case that is worth handling.
           4. Subquery predicate is a top-level predicate
              (this implies it is not negated)
              TODO: this is a limitation that should be lifeted once we
@@ -658,7 +664,8 @@ JOIN::prepare(Item ***rref_pointer_array
             in_subs  &&                                                   // 1
             !select_lex->master_unit()->first_select()->next_select() &&
 // 2
             select_lex->master_unit()->first_select()->leaf_tables &&   
 // 3
-            thd->lex->sql_command == SQLCOM_SELECT)                       // *
+            thd->lex->sql_command == SQLCOM_SELECT &&                    
// *
+            select_lex->outer_select()->leaf_tables)                      // 3A
         {
           if (in_subs->is_top_level_item() &&                             // 4
               !in_subs->is_correlated &&                                  // 5