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#56367 | oystein.grovlen | 9 Sep |
