[Commits] Rev 3487: MDEV-3904 fix. Problem with late SJ convertion attempt in file:///home/bell/maria/bzr/work-maria-10.0-base-exists2in/

sanja at askmonty.org sanja at askmonty.org
Wed Feb 6 10:47:58 EET 2013


At file:///home/bell/maria/bzr/work-maria-10.0-base-exists2in/

------------------------------------------------------------
revno: 3487
revision-id: sanja at askmonty.org-20130206084755-7bsuj86imqx6dfcp
parent: sanja at askmonty.org-20130125101850-zz9as77sn3k5sgfs
committer: sanja at askmonty.org
branch nick: work-maria-10.0-base-exists2in
timestamp: Wed 2013-02-06 10:47:55 +0200
message:
  MDEV-3904 fix. Problem with late SJ convertion attempt
-------------- next part --------------
=== modified file 'mysql-test/r/subselect_exists2in.result'
--- a/mysql-test/r/subselect_exists2in.result	2013-01-25 10:18:50 +0000
+++ b/mysql-test/r/subselect_exists2in.result	2013-02-06 08:47:55 +0000
@@ -804,4 +804,25 @@ a
 1
 7
 drop table t1;
+#
+# MDEV-3904: Assertion `in_subs->has_strategy()' failed in
+# JOIN::choose_subquery_plan on 2nd execution of PS with
+# exists_to_in+semijoin, EXISTS subquery, MERGE view or FROM subquery
+#
+SET optimizer_switch='in_to_exists=on,semijoin=on,exists_to_in=on';
+CREATE TABLE t1 (a INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (b INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (4),(6);
+CREATE ALGORITHM=MERGE VIEW v AS 
+SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t2 WHERE b = a );
+PREPARE stmt FROM ' SELECT * FROM v ';
+EXECUTE stmt;
+a
+EXECUTE stmt;
+a
+drop view v;
+drop table t1,t2;
+set optimizer_switch=default;
+set optimizer_switch='exists_to_in=on';
 set optimizer_switch=default;

=== modified file 'mysql-test/t/subselect_exists2in.test'
--- a/mysql-test/t/subselect_exists2in.test	2013-01-25 10:18:50 +0000
+++ b/mysql-test/t/subselect_exists2in.test	2013-02-06 08:47:55 +0000
@@ -662,5 +662,32 @@ EXECUTE stmt;
 
 drop table t1;
 
+--echo #
+--echo # MDEV-3904: Assertion `in_subs->has_strategy()' failed in
+--echo # JOIN::choose_subquery_plan on 2nd execution of PS with
+--echo # exists_to_in+semijoin, EXISTS subquery, MERGE view or FROM subquery
+--echo #
+SET optimizer_switch='in_to_exists=on,semijoin=on,exists_to_in=on';
+
+CREATE TABLE t1 (a INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(2);
+
+CREATE TABLE t2 (b INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (4),(6);
+
+CREATE ALGORITHM=MERGE VIEW v AS 
+SELECT * FROM t1 WHERE EXISTS ( SELECT * FROM t2 WHERE b = a );
+
+PREPARE stmt FROM ' SELECT * FROM v ';
+
+EXECUTE stmt;
+EXECUTE stmt;
+
+drop view v;
+drop table t1,t2;
+set optimizer_switch=default;
+set optimizer_switch='exists_to_in=on';
+
+
 #restore defaults
 set optimizer_switch=default;

=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc	2013-01-25 10:18:50 +0000
+++ b/sql/item_subselect.cc	2013-02-06 08:47:55 +0000
@@ -1354,6 +1354,7 @@ Item_in_subselect::Item_in_subselect(Ite
   upper_item(0)
 {
   DBUG_ENTER("Item_in_subselect::Item_in_subselect");
+  DBUG_PRINT("info", ("in_strategy: %u", (uint)in_strategy));
   left_expr= left_exp;
   func= &eq_creator;
   init(select_lex, new select_exists_subselect(this));

=== modified file 'sql/item_subselect.h'
--- a/sql/item_subselect.h	2013-01-25 10:18:50 +0000
+++ b/sql/item_subselect.h	2013-02-06 08:47:55 +0000
@@ -573,7 +573,11 @@ public:
     exists_transformed(0), in_strategy(SUBS_NOT_TRANSFORMED),
     pushed_cond_guards(NULL), func(NULL), is_jtbm_merged(FALSE),
     is_jtbm_const_tab(FALSE), upper_item(0)
-    {}
+    {
+      DBUG_ENTER("Item_in_subselect::Item_in_subselect");
+      DBUG_PRINT("info", ("in_strategy: %u", (uint)in_strategy));
+      DBUG_VOID_RETURN;
+    }
   void cleanup();
   subs_type substype() { return IN_SUBS; }
   void reset() 
@@ -648,6 +652,9 @@ public:
 
   void add_strategy (uchar strategy)
   {
+    DBUG_ENTER("Item_in_subselect::add_strategy");
+    DBUG_PRINT("enter", ("current: %u  add: %u",
+                         (uint) in_strategy, (uint) strategy));
     DBUG_ASSERT(strategy != SUBS_NOT_TRANSFORMED);
     DBUG_ASSERT(!(strategy & SUBS_STRATEGY_CHOSEN));
     /*
@@ -657,16 +664,25 @@ public:
       DBUG_ASSERT(!(in_strategy & SUBS_STRATEGY_CHOSEN));
     */
     in_strategy|= strategy;
+    DBUG_VOID_RETURN;
   }
 
   void reset_strategy(uchar strategy)
   {
+    DBUG_ENTER("Item_in_subselect::reset_strategy");
+    DBUG_PRINT("enter", ("current: %u  new: %u",
+                         (uint) in_strategy, (uint) strategy));
     DBUG_ASSERT(strategy != SUBS_NOT_TRANSFORMED);
     in_strategy= strategy;
+    DBUG_VOID_RETURN;
   }
 
   void set_strategy(uchar strategy)
   {
+    DBUG_ENTER("Item_in_subselect::set_strategy");
+    DBUG_PRINT("enter", ("current: %u  set: %u",
+                         (uint) in_strategy,
+                         (uint) (SUBS_STRATEGY_CHOSEN | strategy)));
     /* Check that only one strategy is set for execution. */
     DBUG_ASSERT(strategy == SUBS_SEMI_JOIN ||
                 strategy == SUBS_IN_TO_EXISTS ||
@@ -676,6 +692,7 @@ public:
                 strategy == SUBS_MAXMIN_INJECTED ||
                 strategy == SUBS_MAXMIN_ENGINE);
     in_strategy= (SUBS_STRATEGY_CHOSEN | strategy);
+    DBUG_VOID_RETURN;
   }
   bool exists2in_processor(uchar *opt_arg __attribute__((unused)))
   {

=== modified file 'sql/opt_subselect.cc'
--- a/sql/opt_subselect.cc	2013-01-23 15:16:14 +0000
+++ b/sql/opt_subselect.cc	2013-02-06 08:47:55 +0000
@@ -666,6 +666,9 @@ int check_and_do_in_subquery_rewrites(JO
         8. No execution method was already chosen (by a prepared statement)
         9. Parent select is not a table-less select
         10. Neither parent nor child select have STRAIGHT_JOIN option.
+        11. It is first optimisation (the subquery could be moved from ON
+        clause during first optimisation and then be considered for SJ
+        on the second when it is too late)
     */
     if (optimizer_flag(thd, OPTIMIZER_SWITCH_SEMIJOIN) &&
         in_subs &&                                                    // 1
@@ -679,7 +682,8 @@ int check_and_do_in_subquery_rewrites(JO
         select_lex->outer_select()->leaf_tables.elements &&           // 9
         !((join->select_options |                                     // 10
            select_lex->outer_select()->join->select_options)          // 10
-          & SELECT_STRAIGHT_JOIN))                                    // 10
+          & SELECT_STRAIGHT_JOIN) &&                                  // 10
+        select_lex->first_cond_optimization)                          // 11
     {
       DBUG_PRINT("info", ("Subquery is semi-join conversion candidate"));
 

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2013-01-25 10:18:50 +0000
+++ b/sql/sql_select.cc	2013-02-06 08:47:55 +0000
@@ -1043,14 +1043,6 @@ JOIN::optimize_inner()
   if (select_lex->handle_derived(thd->lex, DT_OPTIMIZE))
     DBUG_RETURN(1);
 
-  /*
-    In fact we transform underlying subqueries after their 'prepare' phase and
-    before 'optimize' from upper query 'optimize' to allow semijoin
-    conversion happened (which done in the same way.
-  */
-  if(conds && conds->walk(&Item::exists2in_processor, 0, (uchar *)thd))
-    DBUG_RETURN(1);
-
   if (select_lex->first_cond_optimization)
   {
     //Do it only for the first execution
@@ -1060,6 +1052,22 @@ JOIN::optimize_inner()
     table_count= select_lex->leaf_tables.elements;
     select_lex->update_used_tables();
   }
+  /*
+    In fact we transform underlying subqueries after their 'prepare' phase and
+    before 'optimize' from upper query 'optimize' to allow semijoin
+    conversion happened (which done in the same way.
+  */
+  if(conds && conds->walk(&Item::exists2in_processor, 0, (uchar *)thd))
+    DBUG_RETURN(1);
+  /*
+TODO: make view to decide if it is possible to write to WHERE directly or make Semi-Joins able to process ON condition if it is possible
+  for (TABLE_LIST *tbl= tables_list; tbl; tbl= tbl->next_local)
+  {
+    if (tbl->on_expr &&
+        tbl->on_expr->walk(&Item::exists2in_processor, 0, (uchar *)thd))
+      DBUG_RETURN(1);
+  }
+  */
 
   if (transform_max_min_subquery())
     DBUG_RETURN(1); /* purecov: inspected */
@@ -6019,6 +6027,7 @@ static void choose_initial_table_order(J
   TABLE_LIST *emb_subq;
   JOIN_TAB **tab= join->best_ref + join->const_tables;
   JOIN_TAB **tabs_end= tab + join->table_count - join->const_tables;
+  DBUG_ENTER("choose_initial_table_order");
   /* Find where the top-level JOIN_TABs end and subquery JOIN_TABs start */
   for (; tab != tabs_end; tab++)
   {
@@ -6028,7 +6037,7 @@ static void choose_initial_table_order(J
   uint n_subquery_tabs= tabs_end - tab;
 
   if (!n_subquery_tabs)
-    return;
+    DBUG_VOID_RETURN;
 
   /* Copy the subquery JOIN_TABs to a separate array */
   JOIN_TAB *subquery_tabs[MAX_TABLES];
@@ -6083,6 +6092,7 @@ static void choose_initial_table_order(J
       subq_tab += n_subquery_tables - 1;
     }
   }
+  DBUG_VOID_RETURN;
 }
 
 



More information about the commits mailing list