maria-developers team mailing list archive

[Sergei Petrunia <psergey@xxxxxxxxxxxx>]

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: timour@xxxxxxxxxxxx-20130523201638-4vj8cx0yjuww29nt
> parent: timour@xxxxxxxxxxxx-20130520190622-rdrru0c2gbdgtjy5
> fixes bug: https://mariadb.atlassian.net/browse/MDEV-4407
> committer: timour@xxxxxxxxxxxx
> 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
> commits@xxxxxxxxxxx
> 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