Hi Timour, Ok to push.
On Thu, May 23, 2013 at 11:19:36PM +0300, Timour Katchaounov wrote: > Sergey, > > Please approve the following patch we already discussed. > > Timour > > > ------------------------------------------------------------ > revno: 3549 > revision-id: [email protected] > parent: [email protected] > fixes bug: https://mariadb.atlassian.net/browse/MDEV-4407 > committer: [email protected] > branch nick: 10.0-md83 > timestamp: Thu 2013-05-23 23:16:38 +0300 > message: > Fix bug MDEV-4407 SQ pushdown: Wrong result (extra rows) with IN and EXISTS > subqueries, optimizer_use_condition_selectivity>1 > (Part of MDEV-83) > > Analysis: > MDEV-83 takes into account the cost of subqueries, which results > in a materialized semi-join where the materialized table is on > the right side of the join. At the same time, the WHERE clause > has an EXISTS predicate which is moved to the second table in > the join because of smaller join cardinality. > > The semi-join startup function setup_sj_materialization_part2 > didn't take into account that in addition to the injected > IN-EXISTS conditions, there may be other conditions, such as > ones moved by pushdown of subquery predicates in mdev-83. So > setup_sj_materialization_part2 just set the condition of the > semi-join to be the IN-EXISTS equality, this removing the > subquery moved by the subquery pushdown logic. > > Solution: > And the conditions instead of just setting them. > > > > === modified file 'mysql-test/r/subselect4.result' > --- a/mysql-test/r/subselect4.result 2013-05-20 19:06:22 +0000 > +++ b/mysql-test/r/subselect4.result 2013-05-23 20:16:38 +0000 > @@ -2393,5 +2393,31 @@ COUNT(b) > 1 > 2 > drop table t1, t2, t3; > +# > +# MDEV-4407 SQ pushdown: Wrong result (extra rows) with IN and EXISTS > subqueries, optimizer_use_condition_selectivity>1 > +# > +SET optimizer_switch='expensive_pred_static_pushdown=on'; > +SET optimizer_use_condition_selectivity=3; > +SET use_stat_tables=PREFERABLY; > +CREATE TABLE t1 (a INT, b INT, KEY(b)) ENGINE=MyISAM; > +INSERT INTO t1 VALUES (1,1),(2,2),(1,1),(0,0); > +ANALYZE TABLE t1; > +Table Op Msg_type Msg_text > +test.t1 analyze status OK > +FLUSH TABLES; > +EXPLAIN > +SELECT * FROM t1 AS outer_t1 WHERE b IN ( SELECT a FROM t1 WHERE b != 1 ) > AND EXISTS ( SELECT 1 FROM t1 WHERE b < outer_t1.a ); > +id select_type table type possible_keys key key_len ref > rows Extra > +1 PRIMARY outer_t1 ALL b NULL NULL NULL 4 > > +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 > func 1 Using where > +2 MATERIALIZED t1 ALL b NULL NULL NULL 4 > Using where > +3 DEPENDENT SUBQUERY t1 index b b 5 NULL > 4 Using where; Using index > +SELECT * FROM t1 AS outer_t1 WHERE b IN ( SELECT a FROM t1 WHERE b != 1 ) > AND EXISTS ( SELECT 1 FROM t1 WHERE b < outer_t1.a ); > +a b > +2 2 > +drop table t1; > +SET optimizer_switch=@@global.optimizer_switch; > +SET optimizer_use_condition_selectivity=default; > +SET use_stat_tables=default; > SET optimizer_switch= @@global.optimizer_switch; > set @@tmp_table_size= @@global.tmp_table_size; > > === modified file 'mysql-test/t/subselect4.test' > --- a/mysql-test/t/subselect4.test 2013-05-20 19:06:22 +0000 > +++ b/mysql-test/t/subselect4.test 2013-05-23 20:16:38 +0000 > @@ -1917,5 +1917,29 @@ GROUP BY a; > > drop table t1, t2, t3; > > +--echo # > +--echo # MDEV-4407 SQ pushdown: Wrong result (extra rows) with IN and EXISTS > subqueries, optimizer_use_condition_selectivity>1 > +--echo # > + > +SET optimizer_switch='expensive_pred_static_pushdown=on'; > +SET optimizer_use_condition_selectivity=3; > +SET use_stat_tables=PREFERABLY; > + > +CREATE TABLE t1 (a INT, b INT, KEY(b)) ENGINE=MyISAM; > +INSERT INTO t1 VALUES (1,1),(2,2),(1,1),(0,0); > +ANALYZE TABLE t1; > +FLUSH TABLES; > + > +EXPLAIN > +SELECT * FROM t1 AS outer_t1 WHERE b IN ( SELECT a FROM t1 WHERE b != 1 ) > AND EXISTS ( SELECT 1 FROM t1 WHERE b < outer_t1.a ); > +SELECT * FROM t1 AS outer_t1 WHERE b IN ( SELECT a FROM t1 WHERE b != 1 ) > AND EXISTS ( SELECT 1 FROM t1 WHERE b < outer_t1.a ); > + > +drop table t1; > + > +SET optimizer_switch=@@global.optimizer_switch; > +SET optimizer_use_condition_selectivity=default; > +SET use_stat_tables=default; > + > + > SET optimizer_switch= @@global.optimizer_switch; > set @@tmp_table_size= @@global.tmp_table_size; > > === modified file 'sql/opt_subselect.cc' > --- a/sql/opt_subselect.cc 2013-04-01 10:36:05 +0000 > +++ b/sql/opt_subselect.cc 2013-05-23 20:16:38 +0000 > @@ -3620,7 +3620,10 @@ bool setup_sj_materialization_part2(JOIN > > emb_sj_nest->sj_subq_pred))) > DBUG_RETURN(TRUE); /* purecov: inspected */ > sjm_tab->type= JT_EQ_REF; > - sjm_tab->select_cond= sjm->in_equality; > + remove_sj_conds(&sjm_tab->select_cond); > + sjm_tab->select_cond= and_items(sjm_tab->select_cond, sjm->in_equality); > + if (!sjm_tab->select_cond->fixed) > + sjm_tab->select_cond->fix_fields(thd, &sjm_tab->select_cond); > } > else > { > > > _______________________________________________ > commits mailing list > [email protected] > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits -- BR Sergei -- Sergei 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
