bzr commit into mysql-next-mr-bugfixing branch (oystein.grovlen:3227) Bug#52344

[Oystein Grovlen]

#At file:///home/og136792/mysql/mysql-next-mr-opt-backporting/ based on revid:epotemkin@stripped

 3227 Oystein Grovlen	2010-08-17
      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: Do not evaluate on-clause in optimization phase if
      is_expensive() returns true for this clause.  This is how the
      problem is currently avoided for where-clauses.  This works
      because, Item_in_subselect::is_expensive_processor returns true
      if query is to be executed with subquery materialization.
     @ 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/sql_select.cc
        Do not evaluate on-clause in optimization phase if
        is_expensive() returns true for this clause. This prevents
        executing materialized subqueries in optimization phase. 
        (Proper setup for such execution has not been done at this
        stage.)

    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/sql_select.cc
=== 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-08-17 11:49:48 +0000
@@ -1008,6 +1008,33 @@ FROM t2);
 --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 @@ DROP TABLE t1,t2,t3;
 
 --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-08-17 11:49:48 +0000
@@ -1355,6 +1355,35 @@ id	select_type	table	type	possible_keys	
 # 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	SUBQUERY	t3	system	NULL	NULL	NULL	NULL	0	const row not found
+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	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-08-17 11:49:48 +0000
@@ -1354,6 +1354,35 @@ id	select_type	table	type	possible_keys	
 # 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	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-08-17 11:49:48 +0000
@@ -1354,6 +1354,35 @@ id	select_type	table	type	possible_keys	
 # 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/sql_select.cc'
--- a/sql/sql_select.cc	2010-08-13 19:19:23 +0000
+++ b/sql/sql_select.cc	2010-08-17 11:49:48 +0000
@@ -17452,7 +17452,14 @@ join_read_const_table(JOIN_TAB *tab, POS
 	DBUG_RETURN(error);
     }
   }
-  if (*tab->on_expr_ref && !table->null_row)
+  /* We will evaluate on-expressions here only if it is not considered
+     expensive.  This also prevents executing materialized subqueries
+     in optimization phase.  This is necessary since proper setup for
+     such execution has not been done at this stage.  (See comment in
+     internal_remove_eq_conds() tagged DontEvaluateMaterializedSubqueryTooEarly).
+  */
+  if (*tab->on_expr_ref && !table->null_row && 
+      !(*tab->on_expr_ref)->is_expensive())
   {
     if ((table->null_row= test((*tab->on_expr_ref)->val_int() == 0)))
       mark_as_null_row(table);  


Attachment: [text/bzr-bundle] bzr/[e-mail]