-Make show_explain.test stable

- Fix st_select_lex::set_explain_type() to allow producing exactly the 
  same EXPLAINs as it did before. SHOW EXPLAIN output may produce 
  select_type=SIMPLE instead or select_type=PRIMARY or vice versa (which
  is ok because values of select_type weren't self-consistent in this 
  regard to begin with)

6 files changed, 45 insertions, 12 deletions

diff --git a/mysql-test/r/show_explain.result b/mysql-test/r/show_explain.result
index 5aee9221d50..4ff89069c5c 100644
--- a/mysql-test/r/show_explain.result
+++ b/mysql-test/r/show_explain.result
@@ -5,9 +5,9 @@ create table t1 (a int);
 insert into t1 select A.a + 10*B.a + 100*C.a from t0 A, t0 B, t0 C;
 show explain for 2*1000*1000*1000;
 ERROR HY000: Unknown thread id: 2000000000
-show explain for 3;
+SHOW explain for thr2
 ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
-show explain for 2;
+SHOW explain for thr1
 ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
 select get_lock('optimizer_done', 10);
 get_lock('optimizer_done', 10)
@@ -16,11 +16,11 @@ select count(*) from t1 where a < 100000 and sleep(a*0 + release_lock('optimizer
 select get_lock('optimizer_done', 100);
 get_lock('optimizer_done', 100)
 1
-show explain for 3;
+SHOW explain for thr2
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000	Using where
 select release_lock('optimizer_done');
 release_lock('optimizer_done')
 1
-kill query 3;
+kill query thr2
 drop table t0,t1;
diff --git a/mysql-test/t/show_explain.test b/mysql-test/t/show_explain.test
index e8f4a646a8a..fc83613ae0e 100644
--- a/mysql-test/t/show_explain.test
+++ b/mysql-test/t/show_explain.test
@@ -24,12 +24,18 @@ let $thr2=`select connection_id()`;
 connection default;
 
 # SHOW EXPLAIN FOR <idle thread>
+echo SHOW explain for thr2;
+--disable_query_log
 --error ER_ERROR_WHEN_EXECUTING_COMMAND
 eval show explain for $thr2;
+--enable_query_log
 
 # SHOW EXPLAIN FOR <ourselves>
+echo SHOW explain for thr1;
+--disable_query_log
 --error ER_ERROR_WHEN_EXECUTING_COMMAND
 eval show explain for $thr1;
+--enable_query_log
 
 # SHOW EXPLAIN FOR <running thread>
 connection con1;
@@ -37,9 +43,15 @@ select get_lock('optimizer_done', 10);
 send select count(*) from t1 where a < 100000 and sleep(a*0 + release_lock('optimizer_done') +1);
 connection default;
 select get_lock('optimizer_done', 100);
+echo SHOW explain for thr2;
+--disable_query_log
 eval show explain for $thr2;
+--enable_query_log
 select release_lock('optimizer_done');
+--disable_query_log
 eval kill query $thr2;
+--enable_query_log
+echo kill query thr2;
 
 #insert into t1 values ('one'),('two'),('three');
 
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index 6c5e177fe1d..f8b6f80eb14 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -1346,7 +1346,8 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred)
     while ((ifm= li++))
       parent_lex->ftfunc_list->push_front(ifm);
   }
-
+  
+  parent_lex->have_merged_subqueries= TRUE;
   DBUG_RETURN(FALSE);
 }
 
@@ -1458,6 +1459,8 @@ static bool convert_subq_to_jtbm(JOIN *parent_join,
   create_subquery_temptable_name(tbl_alias, hash_sj_engine->materialize_join->
                                               select_lex->select_number);
   jtbm->alias= tbl_alias;
+
+  parent_lex->have_merged_subqueries= TRUE;
 #if 0
   /* Inject sj_on_expr into the parent's WHERE or ON */
   if (emb_tbl_nest)
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 82b171789bc..a9f07c337fa 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -1640,7 +1640,8 @@ void st_select_lex::init_query()
   link_next= 0;
   lock_option= TL_READ_DEFAULT;
   is_prep_leaf_list_saved= FALSE;
-
+  
+  have_merged_subqueries= FALSE;
   bzero((char*) expr_cache_may_be_used, sizeof(expr_cache_may_be_used));
 }
 
@@ -3119,7 +3120,7 @@ bool st_select_lex::optimize_unflattened_subqueries()
         if (options & SELECT_DESCRIBE)
         {
           /* Optimize the subquery in the context of EXPLAIN. */
-          sl->set_explain_type();
+          sl->set_explain_type(FALSE);
           sl->options|= SELECT_DESCRIBE;
           inner_join->select_options|= SELECT_DESCRIBE;
         }
@@ -3482,7 +3483,7 @@ void SELECT_LEX::update_used_tables()
   Set the EXPLAIN type for this subquery.
 */
 
-void st_select_lex::set_explain_type()
+void st_select_lex::set_explain_type(bool on_the_fly)
 {
   bool is_primary= FALSE;
   if (next_select())
@@ -3504,6 +3505,9 @@ void st_select_lex::set_explain_type()
     }
   }
 
+  if (on_the_fly && !is_primary && have_merged_subqueries)
+    is_primary= TRUE;
+
   SELECT_LEX *first= master_unit()->first_select();
   /* drop UNCACHEABLE_EXPLAIN, because it is for internal usage only */
   uint8 is_uncacheable= (uncacheable & ~UNCACHEABLE_EXPLAIN);
@@ -3521,6 +3525,10 @@ void st_select_lex::set_explain_type()
            "DEPENDENT UNION":
            is_uncacheable ? "UNCACHEABLE UNION":
            (this == master_unit()->fake_select_lex)? "UNION RESULT" : "UNION")));
+
+  if (this == master_unit()->fake_select_lex)
+    type= "UNION RESULT";
+
   options|= SELECT_DESCRIBE;
 }
 
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index d5dc4dc3a1f..b5131ded136 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -646,6 +646,12 @@ public:
     those converted to jtbm nests. The list is emptied when conversion is done.
   */
   List<Item_in_subselect> sj_subselects;
+  
+  /*
+    Needed to correctly generate 'PRIMARY' or 'SIMPLE' for select_type column
+    of EXPLAIN
+  */
+  bool have_merged_subqueries;
 
   List<TABLE_LIST> leaf_tables;
   List<TABLE_LIST> leaf_tables_exec;
@@ -888,7 +894,7 @@ public:
   */
   bool optimize_unflattened_subqueries();
   /* Set the EXPLAIN type for this subquery. */
-  void set_explain_type();
+  void set_explain_type(bool on_the_fly);
   bool handle_derived(struct st_lex *lex, uint phases);
   void append_table_to_list(TABLE_LIST *TABLE_LIST::*link, TABLE_LIST *table);
   bool get_free_table_map(table_map *map, uint *tablenr);
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 55ca42f797b..2a895bb2798 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -20430,7 +20430,9 @@ int JOIN::print_explain(select_result_sink *result, bool on_the_fly,
   }
   else if (join->select_lex == join->unit->fake_select_lex)
   {
-    join->select_lex->set_explain_type(); //psergey
+    //if (!join->select_lex->type)
+    if (on_the_fly)
+      join->select_lex->set_explain_type(on_the_fly); //psergey
     /* 
       here we assume that the query will return at least two rows, so we
       show "filesort" in EXPLAIN. Of course, sometimes we'll be wrong
@@ -20503,7 +20505,9 @@ int JOIN::print_explain(select_result_sink *result, bool on_the_fly,
            join->select_lex->master_unit()->derived->is_materialized_derived())
   {
     table_map used_tables=0;
-    join->select_lex->set_explain_type(); //psergey-todo: this adds SELECT_DESCRIBE to options! bad for on-the-fly 
+    //if (!join->select_lex->type)
+    if (on_the_fly)
+      join->select_lex->set_explain_type(on_the_fly); //psergey-todo: this adds SELECT_DESCRIBE to options! bad for on-the-fly 
 
     bool printing_materialize_nest= FALSE;
     uint select_id= join->select_lex->select_number;
@@ -21054,7 +21058,7 @@ bool mysql_explain_union(THD *thd, SELECT_LEX_UNIT *unit, select_result *result)
 
   for (SELECT_LEX *sl= first; sl; sl= sl->next_select())
   {
-    sl->set_explain_type(); //psergey-todo: maybe remove this from here?
+    sl->set_explain_type(FALSE); //psergey-todo: maybe remove this from here?
     sl->options|= SELECT_DESCRIBE;
   }