List:Commits« Previous MessageNext Message »
From:oystein.grovlen Date:September 9 2010 11:30am
Subject:bzr push into mysql-next-mr-bugfixing branch (oystein.grovlen:3238 to 3239)
Bug#56367
View as plain text  
 3239 oystein.grovlen@stripped	2010-09-08
      Bug#56367 - Assertion exec_method != EXEC_MATERIALIZATION... 
                  on subquery in FROM
      
      The reported bug was fixed by 52344.  This patch only contains a reduced 
      test case for the scenario reported in Bug#56367.
     @ mysql-test/include/subquery_mat.inc
        Added test case for Bug#56367.
     @ mysql-test/r/subquery_mat.result
        Added test case for Bug#56367.
     @ mysql-test/r/subquery_mat_all.result
        Added test case for Bug#56367.
     @ mysql-test/r/subquery_mat_none.result
        Added test case for Bug#56367.

    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
 3238 Oystein Grovlen	2010-09-08
      BUG#52344 - Subquery materialization:
                  Assertion if subquery in on-clause of outer join
      
      Problem: If tables of an outer join are 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-09-08 08:19:03 +0000
+++ b/mysql-test/include/subquery_mat.inc	2010-09-08 14:39:38 +0000
@@ -794,5 +794,24 @@ DROP TABLE t1,t2,t3;
 
 --echo # End BUG#54511
 
+--echo #
+--echo # BUG#56367 - Assertion exec_method != EXEC_MATERIALIZATION...
+--echo #             on subquery in FROM
+--echo #
 
+CREATE TABLE t1 (a INTEGER);
+
+CREATE TABLE t2 (b INTEGER);
+INSERT INTO t2 VALUES (1);
+
+let $query =
+SELECT a FROM (
+  SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a > 3 OR t2.b IN (SELECT a FROM t1)
+) table1;
+eval explain $query;
+eval $query;
+
+DROP TABLE t1, t2;
+
+--echo # End BUG#56367
 

=== modified file 'mysql-test/r/subquery_mat.result'
--- a/mysql-test/r/subquery_mat.result	2010-09-08 08:19:03 +0000
+++ b/mysql-test/r/subquery_mat.result	2010-09-08 14:39:38 +0000
@@ -1077,4 +1077,24 @@ IN (SELECT t2.c FROM (t2 JOIN t3));
 COUNT( i )	i
 DROP TABLE t1,t2,t3;
 # End BUG#54511
+#
+# BUG#56367 - Assertion exec_method != EXEC_MATERIALIZATION...
+#             on subquery in FROM
+#
+CREATE TABLE t1 (a INTEGER);
+CREATE TABLE t2 (b INTEGER);
+INSERT INTO t2 VALUES (1);
+explain SELECT a FROM (
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a > 3 OR t2.b IN (SELECT a FROM t1)
+) table1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	0	const row not found
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+3	SUBQUERY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+SELECT a FROM (
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a > 3 OR t2.b IN (SELECT a FROM t1)
+) table1;
+a
+DROP TABLE t1, t2;
+# End BUG#56367
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_mat_all.result'
--- a/mysql-test/r/subquery_mat_all.result	2010-09-08 08:19:03 +0000
+++ b/mysql-test/r/subquery_mat_all.result	2010-09-08 14:39:38 +0000
@@ -1076,4 +1076,24 @@ IN (SELECT t2.c FROM (t2 JOIN t3));
 COUNT( i )	i
 DROP TABLE t1,t2,t3;
 # End BUG#54511
+#
+# BUG#56367 - Assertion exec_method != EXEC_MATERIALIZATION...
+#             on subquery in FROM
+#
+CREATE TABLE t1 (a INTEGER);
+CREATE TABLE t2 (b INTEGER);
+INSERT INTO t2 VALUES (1);
+explain SELECT a FROM (
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a > 3 OR t2.b IN (SELECT a FROM t1)
+) table1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	0	const row not found
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+3	SUBQUERY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+SELECT a FROM (
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a > 3 OR t2.b IN (SELECT a FROM t1)
+) table1;
+a
+DROP TABLE t1, t2;
+# End BUG#56367
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_mat_none.result'
--- a/mysql-test/r/subquery_mat_none.result	2010-09-08 08:19:03 +0000
+++ b/mysql-test/r/subquery_mat_none.result	2010-09-08 14:39:38 +0000
@@ -1076,4 +1076,24 @@ IN (SELECT t2.c FROM (t2 JOIN t3));
 COUNT( i )	i
 DROP TABLE t1,t2,t3;
 # End BUG#54511
+#
+# BUG#56367 - Assertion exec_method != EXEC_MATERIALIZATION...
+#             on subquery in FROM
+#
+CREATE TABLE t1 (a INTEGER);
+CREATE TABLE t2 (b INTEGER);
+INSERT INTO t2 VALUES (1);
+explain SELECT a FROM (
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a > 3 OR t2.b IN (SELECT a FROM t1)
+) table1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	0	const row not found
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+3	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+SELECT a FROM (
+SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.a > 3 OR t2.b IN (SELECT a FROM t1)
+) table1;
+a
+DROP TABLE t1, t2;
+# End BUG#56367
 set optimizer_switch=default;


Attachment: [text/bzr-bundle] bzr/oystein.grovlen@sun.com-20100908143938-xx2f95rgjgk8rpxx.bundle
Thread
bzr push into mysql-next-mr-bugfixing branch (oystein.grovlen:3238 to 3239)Bug#56367oystein.grovlen9 Sep