#At file:///home/tkatchaounov/mysql/bzr/6.0-b37896/
2694 Timour Katchaounov 2008-10-17
BUG#37896 Assertion on entry of Item_in_subselect::exec on subquery with AND NOT
Problem:
The assertion failure is due to a shortcut in JOIN::optimize where if opt_sum_query
detects that:
a) a MIN/MAX function can be computed and substituted by an index lookup, and
b) the WHERE clause doesn't reference any table in the FROM clause,
then it does constant substitution, sets JOIN::tables_list = 0, and returns.
As a result JOIN::optimize never calls setup_subquery_materialization, and never
creates the correct subselect_engine. JOIN::exec still needs to execute the
table-independent WHERE clause to check whether there is any result at all.
This is when the assertion detects this inconsistent state.
Solution:
Call setup_subquery_materialization after the call to opt_sum_query detected that
(a) and (b) above hold true.
Note:
This fix makes the behavior the same as that of subquery execution via the IN=>EXISTS
transformation, thus the same as that of 5.x. However, as BUG#40037 explains, this
behavior is incorrect because MIN/MAX should return NULL, if the query result is
empty. This problem is filed as a separate bug since it is not related to materialization
and is present in older versions.
modified:
mysql-test/r/subselect_mat.result
mysql-test/t/subselect_mat.test
sql/item_subselect.cc
sql/sql_select.cc
per-file messages:
mysql-test/r/subselect_mat.result
Test result for BUG#37896.
mysql-test/t/subselect_mat.test
Test result for BUG#37896.
sql/item_subselect.cc
The fact that a query has no tables, or all tables were optimized away is expressed
in one more way, so we add this extra condition.
sql/sql_select.cc
Call setup_subquery_materialization() before RETURN-ing prematurely, because if there
are IN predicates, they still might be executed and need to be setup.
=== modified file 'mysql-test/r/subselect_mat.result'
--- a/mysql-test/r/subselect_mat.result 2008-04-24 23:59:38 +0000
+++ b/mysql-test/r/subselect_mat.result 2008-10-17 09:46:06 +0000
@@ -1145,3 +1145,33 @@ t2.a = 3 and not t2.a not in (select t2.
1
1
drop table t2;
+create table t1 (a1 int key);
+create table t2 (b1 int);
+insert into t1 values (5);
+explain select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
+select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
+min(a1)
+set @@optimizer_switch='no_materialization';
+explain select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
+select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
+min(a1)
+set @@optimizer_switch='no_semijoin';
+explain select min(a1) from t1 where 7 in (select b1 from t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+2 SUBQUERY t2 system NULL NULL NULL NULL 0 const row not found
+select min(a1) from t1 where 7 in (select b1 from t2);
+min(a1)
+set @@optimizer_switch='no_materialization';
+explain select min(a1) from t1 where 7 in (select b1 from t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+select min(a1) from t1 where 7 in (select b1 from t2);
+min(a1)
+NULL
=== modified file 'mysql-test/t/subselect_mat.test'
--- a/mysql-test/t/subselect_mat.test 2008-04-24 23:59:38 +0000
+++ b/mysql-test/t/subselect_mat.test 2008-10-17 09:46:06 +0000
@@ -806,3 +806,29 @@ select 1 from t2 where
t2.a = 3 and not t2.a not in (select t2.b from t2);
drop table t2;
+#
+# BUG#37896 Assertion on entry of Item_in_subselect::exec on subquery with AND NOT
+#
+create table t1 (a1 int key);
+create table t2 (b1 int);
+insert into t1 values (5);
+
+# Query with group by, executed via materialization
+explain select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
+select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
+# Query with group by, executed via IN=>EXISTS
+set @@optimizer_switch='no_materialization';
+explain select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
+select min(a1) from t1 where 7 in (select b1 from t2 group by b1);
+
+# Executed with materialization
+set @@optimizer_switch='no_semijoin';
+explain select min(a1) from t1 where 7 in (select b1 from t2);
+select min(a1) from t1 where 7 in (select b1 from t2);
+# Executed with semi-join. Notice, this time we get a different result (NULL).
+# This is the only correct result of all four queries. This difference is
+# filed as BUG#40037.
+set @@optimizer_switch='no_materialization';
+explain select min(a1) from t1 where 7 in (select b1 from t2);
+select min(a1) from t1 where 7 in (select b1 from t2);
+
=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc 2008-07-24 10:00:56 +0000
+++ b/sql/item_subselect.cc 2008-10-17 09:46:06 +0000
@@ -1896,7 +1896,11 @@ bool Item_in_subselect::init_left_expr_c
bool use_result_field= FALSE;
outer_join= unit->outer_select()->join;
- if (!outer_join || !outer_join->tables)
+ /*
+ An IN predicate might be evaluated in a query for which all tables have
+ been optimzied away.
+ */
+ if (!outer_join || !outer_join->tables || !outer_join->tables_list)
return TRUE;
/*
If we use end_[send | write]_group to handle complete rows of the outer
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2008-09-13 14:16:20 +0000
+++ b/sql/sql_select.cc 2008-10-17 09:46:06 +0000
@@ -1555,6 +1555,9 @@ JOIN::optimize()
QT_ORDINARY););
conds= table_independent_conds;
}
+ /* Create all structures needed for materialized subquery execution. */
+ if (setup_subquery_materialization())
+ DBUG_RETURN(1);
}
}
if (!tables_list)
| Thread |
|---|
| • bzr commit into mysql-6.0-opt branch (timour:2694) Bug#37896 | Timour Katchaounov | 17 Oct |
