Semi-join optimizations code cleanup part 2:

- Make EXPLAIN display "Start temporary" at the start of the fanout (it used to display
  at the first table whose rowid gets into temp. table which is not that useful for
  the user)
- Updated test results (all checked)

4 files changed, 93 insertions, 75 deletions

diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index c7d07c21746..79eb0cb9551 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -2168,7 +2168,8 @@ bool find_eq_ref_candidate(TABLE *table, table_map sj_inner_tables)
     See setup_semijoin_dups_elimination() for a description of what kinds of
     join prefixes each strategy can handle.
 */
-bool is_multiple_semi_joins(POSITION *prefix, uint idx, table_map inner_tables)
+
+bool is_multiple_semi_joins(JOIN *join, POSITION *prefix, uint idx, table_map inner_tables)
 {
   for (int i= (int)idx; i >= 0; i--)
   {
@@ -2176,7 +2177,8 @@ bool is_multiple_semi_joins(POSITION *prefix, uint idx, table_map inner_tables)
     if ((emb_sj_nest= prefix[i].table->emb_sj_nest))
     {
       if (inner_tables & emb_sj_nest->sj_inner_tables)
-        return !test(inner_tables == emb_sj_nest->sj_inner_tables);
+        return !test(inner_tables == (emb_sj_nest->sj_inner_tables &
+                                      ~join->const_table_map));
     }
   }
   return FALSE;
@@ -2206,6 +2208,7 @@ void advance_sj_state(JOIN *join, table_map remaining_tables, uint idx,
        - attempts to build semi-join strategies here will confuse
          the optimizer, so bail out.
     */
+    pos->sj_strategy= SJ_OPT_NONE;
     return;
   }
 
@@ -2290,7 +2293,7 @@ void advance_sj_state(JOIN *join, table_map remaining_tables, uint idx,
           join->cur_dups_producing_tables &= ~handled_fanout;
           //TODO: update bitmap of semi-joins that were handled together with
           // others.
-          if (is_multiple_semi_joins(join->positions, idx, handled_fanout))
+          if (is_multiple_semi_joins(join, join->positions, idx, handled_fanout))
             pos->inner_tables_handled_with_other_sjs |= handled_fanout;
         }
         else
@@ -3906,6 +3909,80 @@ int do_sj_dups_weedout(THD *thd, SJ_TMP_TABLE *sjtbl)
 }
 
 
+int init_dups_weedout(JOIN *join, uint first_table, int first_fanout_table, uint n_tables)
+{
+  THD *thd= join->thd;
+  DBUG_ENTER("init_dups_weedout");
+  SJ_TMP_TABLE::TAB sjtabs[MAX_TABLES];
+  SJ_TMP_TABLE::TAB *last_tab= sjtabs;
+  uint jt_rowid_offset= 0; // # tuple bytes are already occupied (w/o NULL bytes)
+  uint jt_null_bits= 0;    // # null bits in tuple bytes
+  /*
+    Walk through the range and remember
+     - tables that need their rowids to be put into temptable
+     - the last outer table
+  */
+  for (JOIN_TAB *j=join->join_tab + first_table; 
+       j < join->join_tab + first_table + n_tables; j++)
+  {
+    if (sj_table_is_included(join, j))
+    {
+      last_tab->join_tab= j;
+      last_tab->rowid_offset= jt_rowid_offset;
+      jt_rowid_offset += j->table->file->ref_length;
+      if (j->table->maybe_null)
+      {
+        last_tab->null_byte= jt_null_bits / 8;
+        last_tab->null_bit= jt_null_bits++;
+      }
+      last_tab++;
+      j->table->prepare_for_position();
+      j->keep_current_rowid= TRUE;
+    }
+  }
+
+  SJ_TMP_TABLE *sjtbl;
+  if (jt_rowid_offset) /* Temptable has at least one rowid */
+  {
+    size_t tabs_size= (last_tab - sjtabs) * sizeof(SJ_TMP_TABLE::TAB);
+    if (!(sjtbl= (SJ_TMP_TABLE*)thd->alloc(sizeof(SJ_TMP_TABLE))) ||
+        !(sjtbl->tabs= (SJ_TMP_TABLE::TAB*) thd->alloc(tabs_size)))
+      DBUG_RETURN(TRUE); /* purecov: inspected */
+    memcpy(sjtbl->tabs, sjtabs, tabs_size);
+    sjtbl->is_degenerate= FALSE;
+    sjtbl->tabs_end= sjtbl->tabs + (last_tab - sjtabs);
+    sjtbl->rowid_len= jt_rowid_offset;
+    sjtbl->null_bits= jt_null_bits;
+    sjtbl->null_bytes= (jt_null_bits + 7)/8;
+    sjtbl->tmp_table= 
+      create_duplicate_weedout_tmp_table(thd, 
+                                         sjtbl->rowid_len + 
+                                         sjtbl->null_bytes,
+                                         sjtbl);
+    join->sj_tmp_tables.push_back(sjtbl->tmp_table);
+  }
+  else
+  {
+    /* 
+      This is a special case where the entire subquery predicate does 
+      not depend on anything at all, ie this is 
+        WHERE const IN (uncorrelated select)
+    */
+    if (!(sjtbl= (SJ_TMP_TABLE*)thd->alloc(sizeof(SJ_TMP_TABLE))))
+      DBUG_RETURN(TRUE); /* purecov: inspected */
+    sjtbl->tmp_table= NULL;
+    sjtbl->is_degenerate= TRUE;
+    sjtbl->have_degenerate_row= FALSE;
+  }
+
+  sjtbl->next_flush_table= join->join_tab[first_table].flush_weedout_table;
+  join->join_tab[first_table].flush_weedout_table= sjtbl;
+  join->join_tab[first_fanout_table].first_weedout_table= sjtbl;
+  join->join_tab[first_table + n_tables - 1].check_weed_out_table= sjtbl;
+  DBUG_RETURN(0);
+}
+
+
 /*
   Setup the strategies to eliminate semi-join duplicates.
   
@@ -4006,9 +4083,9 @@ int setup_semijoin_dups_elimination(JOIN *join, ulonglong options,
                                     uint no_jbuf_after)
 {
   uint i;
-  THD *thd= join->thd;
   DBUG_ENTER("setup_semijoin_dups_elimination");
-  
+
+
   POSITION *pos= join->best_positions + join->const_tables;
   for (i= join->const_tables ; i < join->top_join_tab_count; )
   {
@@ -4049,6 +4126,7 @@ int setup_semijoin_dups_elimination(JOIN *join, ulonglong options,
           forwards, but do not destroy other duplicate elimination methods.
         */
         uint first_table= i;
+
         uint join_cache_level= join->thd->variables.join_cache_level;
         for (uint j= i; j < i + pos->n_sj_tables; j++)
         {
@@ -4072,70 +4150,7 @@ int setup_semijoin_dups_elimination(JOIN *join, ulonglong options,
           }
         }
 
-        SJ_TMP_TABLE::TAB sjtabs[MAX_TABLES];
-        SJ_TMP_TABLE::TAB *last_tab= sjtabs;
-        uint jt_rowid_offset= 0; // # tuple bytes are already occupied (w/o NULL bytes)
-        uint jt_null_bits= 0;    // # null bits in tuple bytes
-        /*
-          Walk through the range and remember
-           - tables that need their rowids to be put into temptable
-           - the last outer table
-        */
-        for (JOIN_TAB *j=join->join_tab + first_table; 
-             j < join->join_tab + i + pos->n_sj_tables; j++)
-        {
-          if (sj_table_is_included(join, j))
-          {
-            last_tab->join_tab= j;
-            last_tab->rowid_offset= jt_rowid_offset;
-            jt_rowid_offset += j->table->file->ref_length;
-            if (j->table->maybe_null)
-            {
-              last_tab->null_byte= jt_null_bits / 8;
-              last_tab->null_bit= jt_null_bits++;
-            }
-            last_tab++;
-            j->table->prepare_for_position();
-            j->keep_current_rowid= TRUE;
-          }
-        }
-
-        SJ_TMP_TABLE *sjtbl;
-        if (jt_rowid_offset) /* Temptable has at least one rowid */
-        {
-          size_t tabs_size= (last_tab - sjtabs) * sizeof(SJ_TMP_TABLE::TAB);
-          if (!(sjtbl= (SJ_TMP_TABLE*)thd->alloc(sizeof(SJ_TMP_TABLE))) ||
-              !(sjtbl->tabs= (SJ_TMP_TABLE::TAB*) thd->alloc(tabs_size)))
-            DBUG_RETURN(TRUE); /* purecov: inspected */
-          memcpy(sjtbl->tabs, sjtabs, tabs_size);
-          sjtbl->is_degenerate= FALSE;
-          sjtbl->tabs_end= sjtbl->tabs + (last_tab - sjtabs);
-          sjtbl->rowid_len= jt_rowid_offset;
-          sjtbl->null_bits= jt_null_bits;
-          sjtbl->null_bytes= (jt_null_bits + 7)/8;
-          sjtbl->tmp_table= 
-            create_duplicate_weedout_tmp_table(thd, 
-                                               sjtbl->rowid_len + 
-                                               sjtbl->null_bytes,
-                                               sjtbl);
-          join->sj_tmp_tables.push_back(sjtbl->tmp_table);
-        }
-        else
-        {
-          /* 
-            This is a special case where the entire subquery predicate does 
-            not depend on anything at all, ie this is 
-              WHERE const IN (uncorrelated select)
-          */
-          if (!(sjtbl= (SJ_TMP_TABLE*)thd->alloc(sizeof(SJ_TMP_TABLE))))
-            DBUG_RETURN(TRUE); /* purecov: inspected */
-          sjtbl->tmp_table= NULL;
-          sjtbl->is_degenerate= TRUE;
-          sjtbl->have_degenerate_row= FALSE;
-        }
-        join->join_tab[first_table].flush_weedout_table= sjtbl;
-        join->join_tab[i + pos->n_sj_tables - 1].check_weed_out_table= sjtbl;
-
+        init_dups_weedout(join, first_table, i, i + pos->n_sj_tables - first_table);
         i+= pos->n_sj_tables;
         pos+= pos->n_sj_tables;
         break;
diff --git a/sql/opt_subselect.h b/sql/opt_subselect.h
index 7d560588995..0e7fa22cfe7 100644
--- a/sql/opt_subselect.h
+++ b/sql/opt_subselect.h
@@ -358,8 +358,7 @@ public:
   ENGINE_COLUMNDEF *start_recinfo;
   ENGINE_COLUMNDEF *recinfo;
 
-  /* Pointer to next table (next->start_idx > this->end_idx) */
-  SJ_TMP_TABLE *next; 
+  SJ_TMP_TABLE *next_flush_table; 
 };
 
 int setup_semijoin_dups_elimination(JOIN *join, ulonglong options, 
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index ce20dfea32e..f56cb920a3d 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -15087,10 +15087,12 @@ sub_select(JOIN *join,JOIN_TAB *join_tab,bool end_of_records)
   int error;
   enum_nested_loop_state rc= NESTED_LOOP_OK;
   READ_RECORD *info= &join_tab->read_record;
-
-  if (join_tab->flush_weedout_table)
+   
+  for (SJ_TMP_TABLE *flush_dups_table= join_tab->flush_weedout_table;
+       flush_dups_table;
+       flush_dups_table= flush_dups_table->next_flush_table)
   {
-    do_sj_reset(join_tab->flush_weedout_table);
+    do_sj_reset(flush_dups_table);
   }
 
   if (!join_tab->preread_init_done && join_tab->preread_init())
@@ -21013,7 +21015,7 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order,
           extra.append(STRING_WITH_LEN("; LooseScan"));
         }
 
-        if (tab->flush_weedout_table)
+        if (tab->first_weedout_table)
           extra.append(STRING_WITH_LEN("; Start temporary"));
         if (tab->check_weed_out_table)
           extra.append(STRING_WITH_LEN("; End temporary"));
diff --git a/sql/sql_select.h b/sql/sql_select.h
index 21c5dc7d65e..2b307c20dbe 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -341,6 +341,8 @@ typedef struct st_join_table {
   /* Variables for semi-join duplicate elimination */
   SJ_TMP_TABLE  *flush_weedout_table;
   SJ_TMP_TABLE  *check_weed_out_table;
+  /* for EXPLAIN only: */
+  SJ_TMP_TABLE  *first_weedout_table;
   
   /*
     If set, means we should stop join enumeration after we've got the first