#At file:///home/spetrunia/dev/mysql-6.0-bug37977/
2690 Sergey Petrunia 2008-09-06
BUG#36896: Server crash on SELECT FROM DUAL
- Disable semi-join and materialization if subquery's parent select is a
confluent SELECT ... FROM DUAL:
= Semi-join runtime cannot handle semi-joins w/o outer tables
= Materialization relies on parent select calling setup_subquery_materialization()
before trying to evaluate subquery items. SELECT ... FROM dual buypasses many
steps in join execution and doesn't meet this requirement.
And it's not worth making SELECT ... FROM dual able to work with materialization
because there's no benefit to use materialization if the subquery predicate will
be evaluated only once. (it is possible to construct cases where it will be evaluated
many times but they are not practially important)
modified:
mysql-test/r/ps_ddl.result
mysql-test/r/subselect3.result
mysql-test/t/ps_ddl.test
mysql-test/t/subselect3.test
sql/sql_select.cc
per-file messages:
mysql-test/r/ps_ddl.result
BUG#36896: Server crash on SELECT FROM DUAL
- Enable the testcase that was disabled because of this bug
mysql-test/r/subselect3.result
BUG#36896: Server crash on SELECT FROM DUAL
- Testcase
mysql-test/t/ps_ddl.test
BUG#36896: Server crash on SELECT FROM DUAL
- Enable the testcase that was disabled because of this bug
mysql-test/t/subselect3.test
BUG#36896: Server crash on SELECT FROM DUAL
- Testcase
sql/sql_select.cc
BUG#36896: Server crash on SELECT FROM DUAL
- Disable semi-join and materialization if subquery's parent select is a
confluent SELECT ... FROM DUAL:
= Semi-join runtime cannot handle semi-joins w/o outer tables
= Materialization relies on parent select calling setup_subquery_materialization()
before trying to evaluate subquery items. SELECT ... FROM dual buypasses many
steps in join execution and doesn't meet this requirement.
And it's not worth making SELECT ... FROM dual able to work with materialization
because there's no benefit to use materialization if the subquery predicate will
be evaluated only once. (it is possible to construct cases where it will be evaluated
many times but they are not practially important)
=== modified file 'mysql-test/r/ps_ddl.result'
--- a/mysql-test/r/ps_ddl.result 2008-07-10 23:29:27 +0000
+++ b/mysql-test/r/ps_ddl.result 2008-09-06 02:52:36 +0000
@@ -1610,6 +1610,18 @@ SQL statement where it is needed.
#
# SQLCOM_SELECT
#
+drop table if exists t1;
+create table t1 (a int);
+prepare stmt from "select 1 as res from dual where (1) in (select * from t1)";
+drop table t1;
+create table t1 (x int);
+execute stmt;
+res
+drop table t1;
+deallocate prepare stmt;
+call p_verify_reprepare_count(1);
+SUCCESS
+
#
# SQLCOM_CREATE_TABLE
#
=== modified file 'mysql-test/r/subselect3.result'
--- a/mysql-test/r/subselect3.result 2008-05-22 18:40:15 +0000
+++ b/mysql-test/r/subselect3.result 2008-09-06 02:52:36 +0000
@@ -789,3 +789,10 @@ a b
1 0.123
drop table t1;
End of 5.0 tests
+#
+# BUG#36896: Server crash on SELECT FROM DUAL
+#
+create table t1 (a int);
+select 1 as res from dual where (1) in (select * from t1);
+res
+drop table t1;
=== modified file 'mysql-test/t/ps_ddl.test'
--- a/mysql-test/t/ps_ddl.test 2008-07-10 23:29:27 +0000
+++ b/mysql-test/t/ps_ddl.test 2008-09-06 02:52:36 +0000
@@ -1417,17 +1417,17 @@ deallocate prepare stmt_sp;
--echo # SQLCOM_SELECT
--echo #
-#--disable_warnings
-#drop table if exists t1;
-#--enable_warnings
-#create table t1 (a int);
-#prepare stmt from "select 1 as res from dual where (1) in (select * from t1)";
-#drop table t1;
-#create table t1 (x int);
-#execute stmt;
-#drop table t1;
-#deallocate prepare stmt;
-#call p_verify_reprepare_count(1);
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+create table t1 (a int);
+prepare stmt from "select 1 as res from dual where (1) in (select * from t1)";
+drop table t1;
+create table t1 (x int);
+execute stmt;
+drop table t1;
+deallocate prepare stmt;
+call p_verify_reprepare_count(1);
--echo #
--echo # SQLCOM_CREATE_TABLE
=== modified file 'mysql-test/t/subselect3.test'
--- a/mysql-test/t/subselect3.test 2008-05-22 18:40:15 +0000
+++ b/mysql-test/t/subselect3.test 2008-09-06 02:52:36 +0000
@@ -631,3 +631,11 @@ select * from t1;
drop table t1;
--echo End of 5.0 tests
+
+--echo #
+--echo # BUG#36896: Server crash on SELECT FROM DUAL
+--echo #
+create table t1 (a int);
+select 1 as res from dual where (1) in (select * from t1);
+drop table t1;
+
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2008-09-05 19:23:07 +0000
+++ b/sql/sql_select.cc 2008-09-06 02:52:36 +0000
@@ -568,7 +568,7 @@ JOIN::prepare(Item ***rref_pointer_array
4. Subquery does not use aggregate functions or HAVING
5. Subquery predicate is at the AND-top-level of ON/WHERE clause
6. No execution method was already chosen (by a prepared statement).
-
+ 7. Parent SELECT is not a confluent "SELECT ... FROM DUAL" w/o tables
(*). We are not in a subquery of a single table UPDATE/DELETE that
doesn't have a JOIN (TODO: We should handle this at some
point by switching to multi-table UPDATE/DELETE)
@@ -584,7 +584,8 @@ JOIN::prepare(Item ***rref_pointer_array
select_lex->outer_select()->join && // (*)
select_lex->master_unit()->first_select()->leaf_tables && // (**)
do_semijoin &&
- in_subs->exec_method == Item_in_subselect::NOT_TRANSFORMED) // 6
+ in_subs->exec_method == Item_in_subselect::NOT_TRANSFORMED && // 6
+ select_lex->outer_select()->leaf_tables) // 7
{
DBUG_PRINT("info", ("Subquery is semi-join conversion candidate"));
@@ -632,6 +633,11 @@ JOIN::prepare(Item ***rref_pointer_array
2. Subquery is a single SELECT (not a UNION)
3. Subquery is not a table-less query. In this case there is no
point in materializing.
+ 3A The upper query is not a confluent SELECT ... FROM DUAL. We
+ can't do materialization for SELECT .. FROM DUAL because it
+ does not call setup_subquery_materialization(). We could make
+ SELECT ... FROM DUAL call that function but that doesn't seem
+ to be the case that is worth handling.
4. Subquery predicate is a top-level predicate
(this implies it is not negated)
TODO: this is a limitation that should be lifeted once we
@@ -658,7 +664,8 @@ JOIN::prepare(Item ***rref_pointer_array
in_subs && // 1
!select_lex->master_unit()->first_select()->next_select() && // 2
select_lex->master_unit()->first_select()->leaf_tables && // 3
- thd->lex->sql_command == SQLCOM_SELECT) // *
+ thd->lex->sql_command == SQLCOM_SELECT && // *
+ select_lex->outer_select()->leaf_tables) // 3A
{
if (in_subs->is_top_level_item() && // 4
!in_subs->is_correlated && // 5
| Thread |
|---|
| • bzr commit into mysql-6.0-opt branch (sergefp:2690) Bug#36896 | Sergey Petrunia | 8 Sep |
