[Commits] Rev 3687: Fix bug MDEV-4407 SQ pushdown: Wrong result (extra rows) with IN and EXISTS subqueries, optimizer_use_condition_selectivity>1 in file:///home/tsk/mprog/src/10.0-md83-lp/

timour at askmonty.org timour at askmonty.org
Thu Aug 22 13:46:52 EEST 2013


At file:///home/tsk/mprog/src/10.0-md83-lp/

------------------------------------------------------------
revno: 3687
revision-id: timour at askmonty.org-20130523201638-wisnot99b122a30y
parent: timour at askmonty.org-20130520190622-850ovu24uhhfc30x
fixes bug: https://mariadb.atlassian.net/browse/MDEV-4407
committer: timour at 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.
-------------- next part --------------
=== 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
   {



More information about the commits mailing list