get_allowed_nj_tables.diff

diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 5f4881b2f54..31badd10746 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -117,6 +117,7 @@ enum enum_best_search {
   SEARCH_OK= 0,
   SEARCH_FOUND_EDGE=1
 };
+static table_map get_allowed_nj_tables(JOIN *join, uint idx);
 static enum_best_search
 best_extension_by_limited_search(JOIN *join,
                                  table_map remaining_tables,
@@ -2136,6 +2137,82 @@ JOIN::optimize_inner()
       thd->restore_active_arena(arena, &backup);
   }
 
+  // psergey-new:
+  /*
+    Compute 
+      JOIN::allowed_top_level_tables - a bitmap of tables one can put into the
+        join order if the last table in the join prefix is not inside any outer
+        join nest.
+
+      NESTED_JOIN::direct_children_map - a bitmap of tabhes ... if the last
+      table in the join prefix is inside the join nest.
+
+    Note: it looks like a sensible way to do this is a top-down descent on
+    JOIN::join_list, but apparently that list is missing I_S tables.
+    e.g. for SHOW TABLES WHERE col IN (SELECT ...) it will just have a
+    semi-join nest.
+  */
+  {
+    TABLE_LIST *tl;
+    List_iterator<TABLE_LIST> ti(select_lex->leaf_tables);
+    while ((tl= ti++))
+    {
+      table_map map;
+      if (tl->table)
+        map= tl->table->map;
+      else
+      {
+        DBUG_ASSERT(tl->jtbm_subselect);
+        map= table_map(1) << tl->jtbm_table_no;
+      }
+
+      TABLE_LIST *embedding= tl->embedding;
+
+      if (!embedding)
+      {
+        allowed_top_level_tables |= map;
+        continue;
+      }
+
+      // Walk out of any semi-join nests
+      while (embedding && !embedding->on_expr)
+      {
+        // DBUG_ASSERT(embedding->sj_on_expr);
+        // semi-join nest or an INSERT-INTO view...
+        embedding->nested_join->direct_children_map |= map;
+        embedding= embedding->embedding;
+      }
+
+      // Ok we are in the parent nested outer join nest.
+      if (!embedding)
+      {
+        allowed_top_level_tables |= map;
+        continue;
+      }
+      embedding->nested_join->direct_children_map |= map;
+
+      // Walk to grand-parent join nest.
+      embedding= embedding->embedding;
+
+      // Walk out of any semi-join nests
+      while (embedding && !embedding->on_expr)
+      {
+        DBUG_ASSERT(embedding->sj_on_expr);
+        embedding->nested_join->direct_children_map |= map;
+        embedding= embedding->embedding;
+      }
+
+      if (embedding)
+      {
+        DBUG_ASSERT(embedding->on_expr);
+        embedding->nested_join->direct_children_map |= map;
+      }
+      else
+        allowed_top_level_tables |= map;
+    }
+  }
+////
+
   if (optimize_constant_subqueries())
     DBUG_RETURN(1);
 
@@ -9939,6 +10016,8 @@ best_extension_by_limited_search(JOIN      *join,
   if (join->emb_sjm_nest)
     allowed_tables= join->emb_sjm_nest->sj_inner_tables & ~join->const_table_map;
 
+  table_map dbug_allowed_tables= get_allowed_nj_tables(join, idx);
+
   for (JOIN_TAB **pos= join->best_ref + idx ; (s= *pos) ; pos++)
   {
     table_map real_table_bit= s->table->map;
@@ -9953,6 +10032,12 @@ best_extension_by_limited_search(JOIN      *join,
       POSITION loose_scan_pos;
       Json_writer_object trace_one_table(thd);
 
+      if (!(s->table->map & dbug_allowed_tables))
+      {
+        fprintf(stderr, "booom! %s\n", thd->query());
+        //DBUG_ASSERT(0);
+      }
+
       if (unlikely(thd->trace_started()))
       {
         trace_plan_prefix(join, idx, remaining_tables);
@@ -17393,7 +17478,31 @@ static void restore_prev_nj_state(JOIN_TAB *last)
   }
 }
 
-
+static table_map get_allowed_nj_tables(JOIN *join, uint idx)
+{
+  if (idx == join->const_tables)
+    return join->allowed_top_level_tables;
+ 
+  DBUG_ASSERT(idx > 0);
+  // Then, copy.
+  TABLE_LIST *last_emb= join->positions[idx-1].table->table->pos_in_table_list->embedding;
+  
+  for (;last_emb != NULL && last_emb != join->emb_sjm_nest;
+       last_emb= last_emb->embedding)
+  {
+    if (!last_emb->sj_on_expr)
+    {
+      NESTED_JOIN *nest= last_emb->nested_join;
+      if (!nest->is_fully_covered())
+      {
+        // Return tables that are direct members of this join nest
+        return nest->direct_children_map;
+      }
+    }
+  }
+ // Return bitmap of tables not in any join nest;
+  return join->allowed_top_level_tables;
+}
 
 /*
   Change access methods not to use join buffering and adjust costs accordingly
diff --git a/sql/sql_select.h b/sql/sql_select.h
index 4a2929207a5..c5f8b4e2622 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -1250,6 +1250,8 @@ class JOIN :public Sql_alloc
   table_map outer_join;
   /* Bitmap of tables used in the select list items */
   table_map select_list_used_tables;
+
+  table_map allowed_top_level_tables;
   ha_rows  send_records,found_records,join_examined_rows, accepted_rows;
 
   /*
diff --git a/sql/table.h b/sql/table.h
index 8d609fbf1bb..1fddcdcf786 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -3081,6 +3081,7 @@ typedef struct st_nested_join
      2. All child join nest nodes are fully covered.
    */
   bool is_fully_covered() const { return n_tables == counter; }
+  table_map         direct_children_map;
 } NESTED_JOIN;