Hi Timour, Ok to push. On Mon, Jun 27, 2011 at 05:40:25PM +0300, timour@askmonty.org wrote:

At file:///home/tsk/mprog/src/5.3-mwl89/

------------------------------------------------------------ revno: 3062 revision-id: timour@askmonty.org-20110627144014-r14ahro6uzf77423 parent: igor@askmonty.org-20110625210227-kdneawayskx5jl4q fixes bug(s): https://launchpad.net/bugs/800679 committer: timour@askmonty.org branch nick: 5.3-mwl89 timestamp: Mon 2011-06-27 17:40:14 +0300 message: Fixed bug lp:800679

Analysis: The failed assert ensured that the choice of subquery strategy is performed only for queries with at least one table. If there is a LIMIT 0 clause all tables are removed, and the subquery is neither optimized, nor executed during actual optimization. However, if the query is EXPLAIN-ed, the EXPLAIN execution path doesn't remove the query tables if there is a LIMIT 0 clause. As a result, the subquery optimization code is called, which violates the ASSERT condition.

Solution: Transform the assert into a condition, and if the outer query has no tables assume that there will be at most one subquery execution.

There is potentially a better solution by reengineering the EXPLAIN/optimize code, so that subquery optimization is not done if not needed. Such a solution would be a lot bigger and more complex than a bug fix.

=== modified file 'mysql-test/r/subselect_mat_cost_bugs.result' --- a/mysql-test/r/subselect_mat_cost_bugs.result 2011-06-05 02:56:06 +0000 +++ b/mysql-test/r/subselect_mat_cost_bugs.result 2011-06-27 14:40:14 +0000 @@ -295,3 +295,24 @@ id select_type table type possible_keys select c1 from t1 where c1 in (select kp1 from t2 where kp2 = 10 and c2 = 4) or c1 > 7; c1 drop table t1, t2; +# +# LP BUG#800679: Assertion `outer_join->table_count > 0' failed in +# JOIN::choose_subquery_plan() with materialization=on,semijoin=off +# +CREATE TABLE t1 ( f1 int); +insert into t1 values (1),(2); +CREATE TABLE t2 ( f1 int); +insert into t2 values (1),(2); +SET @@optimizer_switch='materialization=on,semijoin=off'; +EXPLAIN +SELECT * FROM t1 +WHERE (f1) IN (SELECT f1 FROM t2) +LIMIT 0; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where +SELECT * FROM t1 +WHERE (f1) IN (SELECT f1 FROM t2) +LIMIT 0; +f1 +drop table t1, t2;

=== modified file 'mysql-test/t/subselect_mat_cost_bugs.test' --- a/mysql-test/t/subselect_mat_cost_bugs.test 2011-05-23 07:56:05 +0000 +++ b/mysql-test/t/subselect_mat_cost_bugs.test 2011-06-27 14:40:14 +0000 @@ -326,3 +326,26 @@ select c1 from t1 where c1 in (select kp select c1 from t1 where c1 in (select kp1 from t2 where kp2 = 10 and c2 = 4) or c1 > 7;

drop table t1, t2; + +--echo # +--echo # LP BUG#800679: Assertion `outer_join->table_count > 0' failed in +--echo # JOIN::choose_subquery_plan() with materialization=on,semijoin=off +--echo # + +CREATE TABLE t1 ( f1 int); +insert into t1 values (1),(2); +CREATE TABLE t2 ( f1 int); +insert into t2 values (1),(2); + +SET @@optimizer_switch='materialization=on,semijoin=off'; + +EXPLAIN +SELECT * FROM t1 +WHERE (f1) IN (SELECT f1 FROM t2) +LIMIT 0; + +SELECT * FROM t1 +WHERE (f1) IN (SELECT f1 FROM t2) +LIMIT 0; + +drop table t1, t2;

=== modified file 'sql/opt_subselect.cc' --- a/sql/opt_subselect.cc 2011-06-21 13:00:41 +0000 +++ b/sql/opt_subselect.cc 2011-06-27 14:40:14 +0000 @@ -4358,7 +4358,7 @@ bool JOIN::choose_subquery_plan(table_ma by the IN predicate. */ outer_join= unit->outer_select() ? unit->outer_select()->join : NULL; - if (outer_join) + if (outer_join && outer_join->table_count > 0) { /* The index of the last JOIN_TAB in the outer JOIN where in_subs is @@ -4371,7 +4371,6 @@ bool JOIN::choose_subquery_plan(table_ma JOIN_TAB, and their join_tab_idx remains MAX_TABLES. Such predicates are evaluated for each complete row of the outer join. */ - DBUG_ASSERT(outer_join->table_count > 0); max_outer_join_tab_idx= (in_subs->get_join_tab_idx() == MAX_TABLES) ? outer_join->table_count - 1: in_subs->get_join_tab_idx();

_______________________________________________ commits mailing list commits@mariadb.org https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits

-- BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog