[Commits] Rev 3170: Fix for bug lp:834492 in file:///home/tsk/mprog/src/5.3/

timour at askmonty.org timour at askmonty.org
Wed Aug 31 17:25:14 EEST 2011


At file:///home/tsk/mprog/src/5.3/

------------------------------------------------------------
revno: 3170
revision-id: timour at askmonty.org-20110831142507-hg8qd8499iiitytq
parent: monty at askmonty.org-20110829151618-7h6ory0tfyjt43ed
fixes bug(s): https://launchpad.net/bugs/834492
committer: timour at askmonty.org
branch nick: 5.3
timestamp: Wed 2011-08-31 17:25:07 +0300
message:
  Fix for bug lp:834492
  
  Analysis:
  In the test query semi-join merges the inner-most subquery
  into the outer subquery, and the optimization of the merged
  subquery finds some new index access methods. Later the
  IN-EXISTS transformation is applied to the unmerged subquery.
  Since the optimizer is instructed to not consider
  materialization, it reoptimizes the plan in-place to take into
  account the new IN-EXISTS conditions. Just before reoptimization
  JOIN::choose_subquery_plan resets the query plan, which also
  resets the access methods found during the semi-join merge.
  Then reoptimization discovers there are no new access methods,
  but it leaves the query plan in its reset state. Later semi-join
  crashes because it assumes these access methods are present.
  
  Solution:
  When reoptimizing in-place, reset the query plan only after new
  access methods were discovered. If no new access methods were
  discovered, leave the current plan as it was.
-------------- next part --------------
=== modified file 'mysql-test/r/subselect_mat_cost_bugs.result'
--- a/mysql-test/r/subselect_mat_cost_bugs.result	2011-07-21 22:55:08 +0000
+++ b/mysql-test/r/subselect_mat_cost_bugs.result	2011-08-31 14:25:07 +0000
@@ -318,4 +318,39 @@ SELECT * FROM t1
 WHERE (f1) IN (SELECT f1 FROM t2)
 LIMIT 0;
 f1
+set @@optimizer_switch='default';
 drop table t1, t2;
+#
+# LP BUG#834492: Crash in fix_semijoin_strategies_for_picked_join_order
+# with nested subqueries and LooseScan=ON
+#
+CREATE TABLE t3 (b int) ;
+INSERT INTO t3 VALUES (0),(0);
+CREATE TABLE t4 (a int, b int, c int, d int, PRIMARY KEY (a)) ;
+INSERT INTO t4 VALUES (28,0,0,0),(29,3,0,0);
+CREATE TABLE t5 (a int, b int, c int, d int, KEY (c,b)) ;
+INSERT INTO t5 VALUES (28,0,0,0),(29,3,0,0);
+SET @@optimizer_switch='semijoin=ON,loosescan=ON,firstmatch=OFF,materialization=OFF';
+EXPLAIN SELECT *
+FROM t3
+WHERE t3.b > ALL (
+SELECT c
+FROM t4
+WHERE t4.a >= t3.b
+AND a = SOME (SELECT b FROM t5));
+id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1       PRIMARY t3      ALL     NULL    NULL    NULL    NULL    2       Using where
+2       DEPENDENT SUBQUERY      t5      index   c       c       10      NULL    2       Using where; Using index; LooseScan
+2       DEPENDENT SUBQUERY      t4      eq_ref  PRIMARY PRIMARY 4       test.t5.b       1       Using where
+SELECT *
+FROM t3
+WHERE t3.b > ALL (
+SELECT c
+FROM t4
+WHERE t4.a >= t3.b
+AND a = SOME (SELECT b FROM t5));
+b
+0
+0
+set @@optimizer_switch='default';
+drop table t3, t4, t5;

=== modified file 'mysql-test/t/subselect_mat_cost_bugs.test'
--- a/mysql-test/t/subselect_mat_cost_bugs.test	2011-06-28 12:48:44 +0000
+++ b/mysql-test/t/subselect_mat_cost_bugs.test	2011-08-31 14:25:07 +0000
@@ -348,4 +348,40 @@ SELECT * FROM t1
 WHERE (f1) IN (SELECT f1 FROM t2)
 LIMIT 0;
 
+set @@optimizer_switch='default';
 drop table t1, t2;
+
+--echo #
+--echo # LP BUG#834492: Crash in fix_semijoin_strategies_for_picked_join_order
+--echo # with nested subqueries and LooseScan=ON
+--echo #
+
+CREATE TABLE t3 (b int) ;
+INSERT INTO t3 VALUES (0),(0);
+
+CREATE TABLE t4 (a int, b int, c int, d int, PRIMARY KEY (a)) ;
+INSERT INTO t4 VALUES (28,0,0,0),(29,3,0,0);
+
+CREATE TABLE t5 (a int, b int, c int, d int, KEY (c,b)) ;
+INSERT INTO t5 VALUES (28,0,0,0),(29,3,0,0);
+
+SET @@optimizer_switch='semijoin=ON,loosescan=ON,firstmatch=OFF,materialization=OFF';
+
+EXPLAIN SELECT *
+FROM t3
+WHERE t3.b > ALL (
+        SELECT c
+        FROM t4
+        WHERE t4.a >= t3.b
+        AND a = SOME (SELECT b FROM t5));
+
+SELECT *
+FROM t3
+WHERE t3.b > ALL (
+        SELECT c
+        FROM t4
+        WHERE t4.a >= t3.b
+        AND a = SOME (SELECT b FROM t5));
+
+set @@optimizer_switch='default';
+drop table t3, t4, t5;

=== modified file 'sql/opt_subselect.cc'
--- a/sql/opt_subselect.cc	2011-07-21 15:14:34 +0000
+++ b/sql/opt_subselect.cc	2011-08-31 14:25:07 +0000
@@ -4641,17 +4641,12 @@ bool JOIN::choose_subquery_plan(table_ma
         const_tables != table_count)
     {
       /*
-        The subquery was not reoptimized either because the user allowed only
-        the IN-EXISTS strategy, or because materialization was not possible
-        based on semantic analysis. Cleanup the original plan and reoptimize.
+        The subquery was not reoptimized with the newly injected IN-EXISTS
+        conditions either because the user allowed only the IN-EXISTS strategy,
+        or because materialization was not possible based on semantic analysis.
       */
-      for (uint i= 0; i < table_count; i++)
-      {
-        join_tab[i].keyuse= NULL;
-        join_tab[i].checked_keys.clear_all();
-      }
-      if ((reopt_result= reoptimize(in_to_exists_where, join_tables, NULL)) ==
-          REOPT_ERROR)
+      reopt_result= reoptimize(in_to_exists_where, join_tables, NULL);
+      if (reopt_result == REOPT_ERROR)
         return TRUE;
     }
 

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-08-26 21:40:29 +0000
+++ b/sql/sql_select.cc	2011-08-31 14:25:07 +0000
@@ -21547,6 +21547,19 @@ void JOIN::save_query_plan(Join_plan_sta
 
 
 /**
+  Reset a query execution plan so that it can be reoptimized in-place.
+*/
+void JOIN::reset_query_plan()
+{
+  for (uint i= 0; i < table_count; i++)
+  {
+    join_tab[i].keyuse= NULL;
+    join_tab[i].checked_keys.clear_all();
+  }
+}
+
+
+/**
   Restore a query execution plan previously saved by the caller.
 
   @param The object from which the current query plan state is restored.
@@ -21579,7 +21592,8 @@ void JOIN::restore_query_plan(Join_plan_
 
   @param added_where  An extra conjunct to the WHERE clause to reoptimize with
   @param join_tables  The set of tables to reoptimize
-  @param save_to      If != NULL, save here the state of the current query plan
+  @param save_to      If != NULL, save here the state of the current query plan,
+                      otherwise reuse the existing query plan structures.
 
   @notes
   Given a query plan that was already optimized taking into account some WHERE
@@ -21623,6 +21637,8 @@ JOIN::reoptimize(Item *added_where, tabl
 
   if (save_to)
     save_query_plan(save_to);
+  else
+    reset_query_plan();
 
   if (!keyuse.buffer &&
       my_init_dynamic_array(&keyuse, sizeof(KEYUSE), 20, 64))

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2011-07-19 20:19:10 +0000
+++ b/sql/sql_select.h	2011-08-31 14:25:07 +0000
@@ -698,6 +698,7 @@ class JOIN :public Sql_alloc
   enum_reopt_result reoptimize(Item *added_where, table_map join_tables,
                                Join_plan_state *save_to);
   void save_query_plan(Join_plan_state *save_to);
+  void reset_query_plan();
   void restore_query_plan(Join_plan_state *restore_from);
   /* Choose a subquery plan for a table-less subquery. */
   bool choose_tableless_subquery_plan();



More information about the commits mailing list