#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#52344 | Oystein Grovlen | 26 Jul |
