maria-developers team mailing list archive

[Sergey Petrunya <psergey@xxxxxxxxxxxx>]

Hi Timour,

Ok to push.

On Mon, Jun 27, 2011 at 05:40:25PM +0300, timour@xxxxxxxxxxxx wrote:
> At file:///home/tsk/mprog/src/5.3-mwl89/
> 
> ------------------------------------------------------------
> revno: 3062
> revision-id: timour@xxxxxxxxxxxx-20110627144014-r14ahro6uzf77423
> parent: igor@xxxxxxxxxxxx-20110625210227-kdneawayskx5jl4q
> fixes bug(s): https://launchpad.net/bugs/800679
> committer: timour@xxxxxxxxxxxx
> 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@xxxxxxxxxxx
> 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