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