Bug #38795: Automatic search depth and nested join's results in server crash

      
The greedy optimizer tracks the current level of nested joins and the position
inside these by setting and maintaining a state that's global for the whole FROM
clause.
This state was correctly maintained inside the selection of the next partial plan
table (in best_extension_by_limited_search()). 
greedy_search() also moves the current position by adding the last partial match 
table when there's not enough tables in the partial plan found by 
best_extension_by_limited_search().
This may require update of the global state variables that describe the current
position in the plan if the last table placed by greedy_search is not a top-level 
join table.
Fixed by updating the state after placing the partial plan table in greedy_search()
in the same way this is done on entering the best_extension_by_limited_search().
Fixed the signature of the function called to update the state : 
check_interleaving_with_nj

1 files changed, 18 insertions, 9 deletions

diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 9723dd8c4e4..c49b0f0b723 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -100,7 +100,7 @@ static COND* substitute_for_best_equal_field(COND *cond,
                                              void *table_join_idx);
 static COND *simplify_joins(JOIN *join, List<TABLE_LIST> *join_list,
                             COND *conds, bool top);
-static bool check_interleaving_with_nj(JOIN_TAB *last, JOIN_TAB *next);
+static bool check_interleaving_with_nj(JOIN_TAB *next);
 static void restore_prev_nj_state(JOIN_TAB *last);
 static void reset_nj_counters(List<TABLE_LIST> *join_list);
 static uint build_bitmap_for_nested_joins(List<TABLE_LIST> *join_list,
@@ -4705,6 +4705,18 @@ greedy_search(JOIN      *join,
     */
     join->positions[idx]= best_pos;
 
+    /*
+      Update the interleaving state after extending the current partial plan
+      with a new table.
+      We are doing this here because best_extension_by_limited_search reverts
+      the interleaving state to the one of the non-extended partial plan 
+      on exit.
+    */
+    IF_DBUG(bool is_interleave_error= )
+    check_interleaving_with_nj (best_table);
+    /* This has been already checked by best_extension_by_limited_search */
+    DBUG_ASSERT(!is_interleave_error);
+
     /* find the position of 'best_table' in 'join->best_ref' */
     best_idx= idx;
     JOIN_TAB *pos= join->best_ref[best_idx];
@@ -4722,7 +4734,7 @@ greedy_search(JOIN      *join,
     --size_remain;
     ++idx;
 
-    DBUG_EXECUTE("opt", print_plan(join, join->tables,
+    DBUG_EXECUTE("opt", print_plan(join, idx,
                                    record_count, read_time, read_time,
                                    "extended"););
   } while (TRUE);
@@ -4873,7 +4885,7 @@ best_extension_by_limited_search(JOIN      *join,
     table_map real_table_bit= s->table->map;
     if ((remaining_tables & real_table_bit) && 
         !(remaining_tables & s->dependent) && 
-        (!idx || !check_interleaving_with_nj(join->positions[idx-1].table, s)))
+        (!idx || !check_interleaving_with_nj(s)))
     {
       double current_record_count, current_read_time;
 
@@ -5018,7 +5030,7 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count,
   {
     table_map real_table_bit=s->table->map;
     if ((rest_tables & real_table_bit) && !(rest_tables & s->dependent) &&
-        (!idx|| !check_interleaving_with_nj(join->positions[idx-1].table, s)))
+        (!idx|| !check_interleaving_with_nj(s)))
     {
       double records, best;
       best_access_path(join, s, thd, rest_tables, idx, record_count, 
@@ -8380,9 +8392,6 @@ static void reset_nj_counters(List<TABLE_LIST> *join_list)
 
   SYNOPSIS
     check_interleaving_with_nj()
-      join       Join being processed
-      last_tab   Last table in current partial join order (this function is
-                 not called for empty partial join orders)
       next_tab   Table we're going to extend the current partial join with
 
   DESCRIPTION
@@ -8467,10 +8476,10 @@ static void reset_nj_counters(List<TABLE_LIST> *join_list)
     TRUE   Requested join order extension not allowed.
 */
 
-static bool check_interleaving_with_nj(JOIN_TAB *last_tab, JOIN_TAB *next_tab)
+static bool check_interleaving_with_nj(JOIN_TAB *next_tab)
 {
   TABLE_LIST *next_emb= next_tab->table->pos_in_table_list->embedding;
-  JOIN *join= last_tab->join;
+  JOIN *join= next_tab->join;
 
   if (join->cur_embedding_map & ~next_tab->embedding_map)
   {