Hi Timour, Ok to push.
On Mon, Jun 27, 2011 at 05:40:25PM +0300, [email protected] wrote: > At file:///home/tsk/mprog/src/5.3-mwl89/ > > ------------------------------------------------------------ > revno: 3062 > revision-id: [email protected] > parent: [email protected] > fixes bug(s): https://launchpad.net/bugs/800679 > committer: [email protected] > 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 > [email protected] > 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 _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
