MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:oystein.grovlen Date:September 8 2010 2:41pm
Subject:bzr commit into mysql-next-mr-bugfixing branch (oystein.grovlen:3239)
Bug#56367
View as plain text  
#At file:///home/oysteing/mysql-new/mysql-next-mr-opt-backporting-2/ based on revid:oystein.grovlen@stripped

 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
=== 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 commit into mysql-next-mr-bugfixing branch (oystein.grovlen:3239)Bug#56367oystein.grovlen8 Sep