[Commits] Rev 4435: MDEV-7356: Subquery gives incorrect results in file:///home/psergey/dev2/5.5/

Sergey Petrunya psergey at askmonty.org
Tue Feb 17 14:04:11 EET 2015


At file:///home/psergey/dev2/5.5/

------------------------------------------------------------
revno: 4435
revision-id: psergey at askmonty.org-20150217120409-tw5b7o3ayc33t65p
parent: holyfoot at askmonty.org-20150215160831-dfnfjccan7bscua3
committer: Sergey Petrunya <psergey at askmonty.org>
branch nick: 5.5
timestamp: Tue 2015-02-17 15:04:09 +0300
message:
  MDEV-7356: Subquery gives incorrect results
  
  The bug was in remove_sj_conds(), which is used by sj-materialization strategy.
  The check used by the function didn't take into account that the subquery could
  have another ("grandchild") subquery which is merged into the child subquery.
  In that case, grandchild's IN-expression could be incorrectly removed. 
  
  Fixed by using more robust logic in remove_sj_conds.
=== modified file 'mysql-test/r/subselect_sj2.result'
--- a/mysql-test/r/subselect_sj2.result	2014-05-28 13:32:43 +0000
+++ b/mysql-test/r/subselect_sj2.result	2015-02-17 12:04:09 +0000
@@ -1178,5 +1178,19 @@ id	nombre
 2	row 2
 3	row 3
 DROP TABLE t1, t2;
+#
+# MDEV-7356: Subquery gives incorrect results
+#
+CREATE TABLE t1 (way_id int(20), v varchar(255)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (99979604,'living_street'),(99979604,'avenue');
+CREATE TABLE t2 (type varchar(32)) ENGINE=InnoDB;
+INSERT INTO t2 VALUES ('motorway');
+select * from t1 where way_id in (
+select way_id from t1 WHERE v in ( 
+select type from t2
+)
+);
+way_id	v
+DROP TABLE t1, t2;
 # This must be the last in the file:
 set optimizer_switch=@subselect_sj2_tmp;

=== modified file 'mysql-test/r/subselect_sj2_jcl6.result'
--- a/mysql-test/r/subselect_sj2_jcl6.result	2014-05-28 13:32:43 +0000
+++ b/mysql-test/r/subselect_sj2_jcl6.result	2015-02-17 12:04:09 +0000
@@ -1193,6 +1193,20 @@ id	nombre
 2	row 2
 3	row 3
 DROP TABLE t1, t2;
+#
+# MDEV-7356: Subquery gives incorrect results
+#
+CREATE TABLE t1 (way_id int(20), v varchar(255)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (99979604,'living_street'),(99979604,'avenue');
+CREATE TABLE t2 (type varchar(32)) ENGINE=InnoDB;
+INSERT INTO t2 VALUES ('motorway');
+select * from t1 where way_id in (
+select way_id from t1 WHERE v in ( 
+select type from t2
+)
+);
+way_id	v
+DROP TABLE t1, t2;
 # This must be the last in the file:
 set optimizer_switch=@subselect_sj2_tmp;
 #

=== modified file 'mysql-test/r/subselect_sj2_mat.result'
--- a/mysql-test/r/subselect_sj2_mat.result	2014-05-28 13:32:43 +0000
+++ b/mysql-test/r/subselect_sj2_mat.result	2015-02-17 12:04:09 +0000
@@ -1180,6 +1180,20 @@ id	nombre
 2	row 2
 3	row 3
 DROP TABLE t1, t2;
+#
+# MDEV-7356: Subquery gives incorrect results
+#
+CREATE TABLE t1 (way_id int(20), v varchar(255)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (99979604,'living_street'),(99979604,'avenue');
+CREATE TABLE t2 (type varchar(32)) ENGINE=InnoDB;
+INSERT INTO t2 VALUES ('motorway');
+select * from t1 where way_id in (
+select way_id from t1 WHERE v in ( 
+select type from t2
+)
+);
+way_id	v
+DROP TABLE t1, t2;
 # This must be the last in the file:
 set optimizer_switch=@subselect_sj2_tmp;
 set optimizer_switch=default;

=== modified file 'mysql-test/t/subselect_sj2.test'
--- a/mysql-test/t/subselect_sj2.test	2014-05-28 13:32:43 +0000
+++ b/mysql-test/t/subselect_sj2.test	2015-02-17 12:04:09 +0000
@@ -1320,5 +1320,23 @@ SELECT * FROM t1 WHERE id in (select dis
 
 DROP TABLE t1, t2;
 
+--echo #
+--echo # MDEV-7356: Subquery gives incorrect results
+--echo #
+
+CREATE TABLE t1 (way_id int(20), v varchar(255)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (99979604,'living_street'),(99979604,'avenue');
+
+CREATE TABLE t2 (type varchar(32)) ENGINE=InnoDB;
+INSERT INTO t2 VALUES ('motorway');
+
+select * from t1 where way_id in (
+  select way_id from t1 WHERE v in ( 
+    select type from t2
+  )
+); 
+
+DROP TABLE t1, t2;
+
 --echo # This must be the last in the file:
 set optimizer_switch=@subselect_sj2_tmp;

=== modified file 'sql/opt_subselect.cc'
--- a/sql/opt_subselect.cc	2014-02-22 21:51:20 +0000
+++ b/sql/opt_subselect.cc	2015-02-17 12:04:09 +0000
@@ -460,8 +460,8 @@ void best_access_path(JOIN *join, JOIN_T
 
 static Item *create_subq_in_equalities(THD *thd, SJ_MATERIALIZATION_INFO *sjm, 
                                 Item_in_subselect *subq_pred);
-static void remove_sj_conds(Item **tree);
-static bool is_cond_sj_in_equality(Item *item);
+static void remove_sj_conds(table_map inner_table_map, Item **tree);
+static bool is_cond_sj_in_equality(table_map inner_table_map, Item *item);
 static bool sj_table_is_included(JOIN *join, JOIN_TAB *join_tab);
 static Item *remove_additional_cond(Item* conds);
 static void remove_subq_pushed_predicates(JOIN *join, Item **where);
@@ -3616,6 +3616,12 @@ bool setup_sj_materialization_part2(JOIN
     tab_ref->key_parts= tmp_key_parts;
     sjm->tab_ref= tab_ref;
 
+    table_map inner_table_map= 0;
+    for (JOIN_TAB *jt= sjm_tab->bush_children->start;
+         jt!= sjm_tab->bush_children->end; jt++)
+    {
+      inner_table_map |= jt->table->map;
+    }
     /*
       Remove the injected semi-join IN-equalities from join_tab conds. This
       needs to be done because the IN-equalities refer to columns of
@@ -3624,9 +3630,9 @@ bool setup_sj_materialization_part2(JOIN
     */
     for (i= 0; i < sjm->tables; i++)
     {
-      remove_sj_conds(&tab[i].select_cond);
+      remove_sj_conds(inner_table_map, &tab[i].select_cond);
       if (tab[i].select)
-        remove_sj_conds(&tab[i].select->cond);
+        remove_sj_conds(inner_table_map, &tab[i].select->cond);
     }
     if (!(sjm->in_equality= create_subq_in_equalities(thd, sjm,
                                                       emb_sj_nest->sj_subq_pred)))
@@ -3790,13 +3796,16 @@ static Item *create_subq_in_equalities(T
 }
 
 
+/*
+  @param
+    inner_table_map  Tables which are inner wrt the semi-join.
+*/
 
-
-static void remove_sj_conds(Item **tree)
+static void remove_sj_conds(table_map inner_table_map, Item **tree)
 {
   if (*tree)
   {
-    if (is_cond_sj_in_equality(*tree))
+    if (is_cond_sj_in_equality(inner_table_map, *tree))
     {
       *tree= NULL;
       return;
@@ -3807,21 +3816,36 @@ static void remove_sj_conds(Item **tree)
       List_iterator<Item> li(*(((Item_cond*)*tree)->argument_list()));
       while ((item= li++))
       {
-        if (is_cond_sj_in_equality(item))
+        if (is_cond_sj_in_equality(inner_table_map, item))
           li.replace(new Item_int(1));
       }
     }
   }
 }
 
+
 /* Check if given Item was injected by semi-join equality */
-static bool is_cond_sj_in_equality(Item *item)
+
+static bool is_cond_sj_in_equality(table_map inner_table_map, Item *item)
 {
   if (item->type() == Item::FUNC_ITEM &&
       ((Item_func*)item)->functype()== Item_func::EQ_FUNC)
   {
     Item_func_eq *item_eq= (Item_func_eq*)item;
-    return test(item_eq->in_equality_no != UINT_MAX);
+    if (item_eq->in_equality_no != UINT_MAX)
+    {
+      /*
+        Check if the equality has form "inner_expr = outer_expr"
+      */
+      table_map map1= item_eq->arguments()[0]->used_tables() &
+                      ~PSEUDO_TABLE_BITS;
+      table_map map2= item_eq->arguments()[1]->used_tables() &
+                      ~PSEUDO_TABLE_BITS;
+      if (((map1 & inner_table_map) && (map2 & ~inner_table_map)) ||
+          ((map2 & inner_table_map) && (map1 & ~inner_table_map)))
+      return true;
+    }
+    return false;
   }
   return FALSE;
 }



More information about the commits mailing list