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.

3 files changed, 23 insertions, 11 deletions

diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index 6c5e177fe1d..b06d80860c5 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -4641,17 +4641,12 @@ bool JOIN::choose_subquery_plan(table_map join_tables)
         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;
     }
 
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 54c1215164e..d01c54d1f22 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -21547,6 +21547,19 @@ void JOIN::save_query_plan(Join_plan_state *save_to)
 
 
 /**
+  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_state *restore_from)
 
   @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, table_map join_tables,
 
   if (save_to)
     save_query_plan(save_to);
+  else
+    reset_query_plan();
 
   if (!keyuse.buffer &&
       my_init_dynamic_array(&keyuse, sizeof(KEYUSE), 20, 64))
diff --git a/sql/sql_select.h b/sql/sql_select.h
index bbf390aaf7e..9b8e60e9cc1 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -698,6 +698,7 @@ protected:
   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();