List:Commits« Previous MessageNext Message »
From:Oystein Grovlen Date:July 26 2010 12:11pm
Subject:bzr commit into mysql-next-mr-opt-backporting branch (oystein.grovlen:3217)
Bug#52344
View as plain text  
#At file:///home/og136792/mysql/mysql-next-mr-opt-backporting/ based on revid:roy.lyseng@stripped

 3217 Oystein Grovlen	2010-07-26
      BUG#52344 - Subquery materialization:
                  Assertion if subquery in on-clause of outer join
      
      Problem: If tables of an outer join is constant tables,
      the associated on-clause will be evaluated in the optimization
      phase.  If the on-clause contains a query that is to be
      executed with subquery materialization, this will not work
      since the infrastructure for such execution is not yet set up.
      
      Solution: If a subquery is executed before subquery
      materialization is properly set up, revert to traditional
      execution of this subquery.
     @ mysql-test/include/subquery_mat.inc
        Added test case for BUG#52344.
     @ mysql-test/r/subquery_mat.result
        Updated result file with test case for BUG#52344.
     @ mysql-test/r/subquery_mat_all.result
        Updated result file with test case for BUG#52344.
     @ mysql-test/r/subquery_mat_none.result
        Updated result file with test case for BUG#52344.
     @ sql/item_subselect.cc
        If subquery materialization is to be used, but the proper
        execution engine has not been set up when the subquery is
        executed, revert to traditional exeuction based on IN=>EXISTS
        transformation.
        
        Moved the code to revert to IN=>EXISTS transformation to
        a new method, revert_to_exists_transformation(), in order to
        be able to re-use this code for the scenario in this bug.
     @ sql/item_subselect.h
        Added new method revert_to_exists_transformation() to be used
        when it is detected that one has to revert the decision to do
        subquery materialization for IN-subquery.  IN=>EXISTS
        transformation will be used for the query instead.

    modified:
      mysql-test/include/subquery_mat.inc
      mysql-test/r/subquery_mat.result
      mysql-test/r/subquery_mat_all.result
      mysql-test/r/subquery_mat_none.result
      sql/item_subselect.cc
      sql/item_subselect.h
=== modified file 'mysql-test/include/subquery_mat.inc'

=== modified file 'mysql-test/include/subquery_mat.inc'
--- a/mysql-test/include/subquery_mat.inc	2010-06-30 06:35:23 +0000
+++ b/mysql-test/include/subquery_mat.inc	2010-07-26 12:11:18 +0000
@@ -1008,6 +1008,33 @@
 --echo #
 DROP TABLE IF EXISTS t1,t2,t3,empty1;
 
+
+--echo #
+--echo # BUG#52344 - Subquery materialization: 
+--echo #  	     Assertion if subquery in on-clause of outer join
+--echo #
+
+CREATE TABLE t1 (i INTEGER);
+INSERT INTO t1 VALUES (10);
+
+CREATE TABLE t2 (j INTEGER);
+INSERT INTO t2 VALUES (5);
+
+CREATE TABLE t3 (k INTEGER);
+
+EXPLAIN
+SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);
+SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);
+
+EXPLAIN
+SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3);
+SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3);
+
+DROP TABLE t1, t2, t3;
+
+--echo # End BUG#52344
+
+
 #
 # Bug #52538 Valgrind bug: Item_in_subselect::init_left_expr_cache()
 #
@@ -1113,3 +1140,5 @@
 
 --echo # End BUG#54511
 
+
+

=== modified file 'mysql-test/r/subquery_mat.result'
--- a/mysql-test/r/subquery_mat.result	2010-06-30 06:35:23 +0000
+++ b/mysql-test/r/subquery_mat.result	2010-07-26 12:11:18 +0000
@@ -1355,6 +1355,35 @@
 # Cleanup for BUG#46680
 #
 DROP TABLE IF EXISTS t1,t2,t3,empty1;
+#
+# BUG#52344 - Subquery materialization: 
+#  	     Assertion if subquery in on-clause of outer join
+#
+CREATE TABLE t1 (i INTEGER);
+INSERT INTO t1 VALUES (10);
+CREATE TABLE t2 (j INTEGER);
+INSERT INTO t2 VALUES (5);
+CREATE TABLE t3 (k INTEGER);
+EXPLAIN
+SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);
+i
+10
+EXPLAIN
+SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
+2	DEPENDENT SUBQUERY	t3	system	NULL	NULL	NULL	NULL	0	const row not found
+SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3);
+i
+10
+DROP TABLE t1, t2, t3;
+# End BUG#52344
 CREATE TABLE t1 (
 pk INTEGER AUTO_INCREMENT,
 col_int_nokey INTEGER,

=== modified file 'mysql-test/r/subquery_mat_all.result'
--- a/mysql-test/r/subquery_mat_all.result	2010-06-30 06:35:23 +0000
+++ b/mysql-test/r/subquery_mat_all.result	2010-07-26 12:11:18 +0000
@@ -1354,6 +1354,35 @@
 # Cleanup for BUG#46680
 #
 DROP TABLE IF EXISTS t1,t2,t3,empty1;
+#
+# BUG#52344 - Subquery materialization: 
+#  	     Assertion if subquery in on-clause of outer join
+#
+CREATE TABLE t1 (i INTEGER);
+INSERT INTO t1 VALUES (10);
+CREATE TABLE t2 (j INTEGER);
+INSERT INTO t2 VALUES (5);
+CREATE TABLE t3 (k INTEGER);
+EXPLAIN
+SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);
+i
+10
+EXPLAIN
+SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
+2	DEPENDENT SUBQUERY	t3	system	NULL	NULL	NULL	NULL	0	const row not found
+SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3);
+i
+10
+DROP TABLE t1, t2, t3;
+# End BUG#52344
 CREATE TABLE t1 (
 pk INTEGER AUTO_INCREMENT,
 col_int_nokey INTEGER,

=== modified file 'mysql-test/r/subquery_mat_none.result'
--- a/mysql-test/r/subquery_mat_none.result	2010-07-13 17:29:44 +0000
+++ b/mysql-test/r/subquery_mat_none.result	2010-07-26 12:11:18 +0000
@@ -1354,6 +1354,35 @@
 # Cleanup for BUG#46680
 #
 DROP TABLE IF EXISTS t1,t2,t3,empty1;
+#
+# BUG#52344 - Subquery materialization: 
+#  	     Assertion if subquery in on-clause of outer join
+#
+CREATE TABLE t1 (i INTEGER);
+INSERT INTO t1 VALUES (10);
+CREATE TABLE t2 (j INTEGER);
+INSERT INTO t2 VALUES (5);
+CREATE TABLE t3 (k INTEGER);
+EXPLAIN
+SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT k FROM t3);
+i
+10
+EXPLAIN
+SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+SELECT i FROM t1 LEFT JOIN t2 ON (j) IN (SELECT max(k) FROM t3);
+i
+10
+DROP TABLE t1, t2, t3;
+# End BUG#52344
 CREATE TABLE t1 (
 pk INTEGER AUTO_INCREMENT,
 col_int_nokey INTEGER,

=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc	2010-07-16 12:21:31 +0000
+++ b/sql/item_subselect.cc	2010-07-26 12:11:18 +0000
@@ -326,6 +326,26 @@
 bool Item_in_subselect::exec()
 {
   DBUG_ENTER("Item_in_subselect::exec");
+
+  if (exec_method == EXEC_MATERIALIZATION &&
+      engine->engine_type() == subselect_engine::SINGLE_SELECT_ENGINE)
+  {
+    /*
+      We arrive here if a subquery is executed before setup_engine()
+      has been called. This may happen if a one tries to execute
+      subqueries during the optimization phase.  One scenario is
+      evaluation of the subquery in an on-clause of an outer join
+      between constant tables.  Since the subquery will only be
+      executed once in such cases, there is no reason to do
+      materialization and we might as well use IN=>EXISTS
+      transformation instead.
+    */
+
+    DBUG_RETURN(
+      revert_to_exists_transformation(
+        ((subselect_single_select_engine *)engine)->join));
+  }
+
   DBUG_ASSERT(exec_method != EXEC_MATERIALIZATION ||
               (exec_method == EXEC_MATERIALIZATION &&
                engine->engine_type() == subselect_engine::HASH_SJ_ENGINE));
@@ -1181,7 +1201,7 @@
 
 
 /**
-  Transofrm an IN predicate into EXISTS via predicate injection.
+  Transform an IN predicate into EXISTS via predicate injection.
 
   @details The transformation injects additional predicates into the subquery
   (and makes the subquery correlated) as follows.
@@ -1883,13 +1903,7 @@
       */
       delete new_engine;
       new_engine= NULL;
-      exec_method= EXEC_UNSPECIFIED;
-      if (left_expr->cols() == 1)
-        trans_res= single_value_in_to_exists_transformer(old_engine->join,
-                                                         &eq_creator);
-      else
-        trans_res= row_value_in_to_exists_transformer(old_engine->join);
-      res= (trans_res != Item_subselect::RES_OK);
+      res= revert_to_exists_transformation(old_engine->join);
     }
     if (new_engine)
       engine= new_engine;
@@ -3401,3 +3415,16 @@
            "<the access method for lookups is not yet created>"
          ));
 }
+
+bool Item_in_subselect::revert_to_exists_transformation(JOIN *join)
+{
+      exec_method= EXEC_UNSPECIFIED;
+
+      Item_subselect::trans_res trans_res;
+      if (left_expr->cols() == 1)
+        trans_res= single_value_in_to_exists_transformer(join, &eq_creator);
+      else
+        trans_res= row_value_in_to_exists_transformer(join);
+
+      return trans_res != Item_subselect::RES_OK;
+}

=== modified file 'sql/item_subselect.h'
--- a/sql/item_subselect.h	2010-07-16 12:21:31 +0000
+++ b/sql/item_subselect.h	2010-07-26 12:11:18 +0000
@@ -412,6 +412,19 @@
   friend class Item_in_optimizer;
   friend class subselect_indexsubquery_engine;
   friend class subselect_hash_sj_engine;
+
+private:
+
+  /**
+     Revert the decision to do subquery materialization and instead do
+     IN => EXISTS transformation for traditional subquery execution.
+
+     @param join  Join object of the subquery (i.e. 'child' join).
+
+     @retval TRUE  if error
+     @retval FALSE otherwise
+  */
+  bool revert_to_exists_transformation(JOIN *join);
 };
 
 


Attachment: [text/bzr-bundle] bzr/oystein.grovlen@oracle.com-20100726121118-4w6po6kmvscypmr7.bundle
Thread
bzr commit into mysql-next-mr-opt-backporting branch (oystein.grovlen:3217)Bug#52344Oystein Grovlen26 Jul