MWL#182: Explain running statements:

Make SHOW EXPLAIN work for queries that do "Using temporary" and/or "Using filesort"
- Patch#1: Don't lose "Using temporary/filesort" in the SHOW EXPLAIN output.

3 files changed, 124 insertions, 3 deletions

diff --git a/mysql-test/r/show_explain.result b/mysql-test/r/show_explain.result
index e451ef3108b..74def21585f 100644
--- a/mysql-test/r/show_explain.result
+++ b/mysql-test/r/show_explain.result
@@ -209,4 +209,79 @@ a	SUBQ
 1	0
 2	0
 drop table t2;
+#
+# SHOW EXPLAIN for SELECT ... ORDER BY with "Using filesort"
+#
+explain select * from t0 order by a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	Using filesort
+set debug='d,show_explain_probe_1';
+set @show_explain_probe_select_id=1;
+select * from t0 order by a;
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	Using filesort
+Warnings:
+Note	1003	select * from t0 order by a
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+#
+# SHOW EXPLAIN for SELECT ... with "Using temporary"
+#
+explain select distinct a from t0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	Using temporary
+set debug='d,show_explain_probe_1';
+set @show_explain_probe_select_id=1;
+select distinct a from t0;
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	Using temporary
+Warnings:
+Note	1003	select distinct a from t0
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+#
+# SHOW EXPLAIN for SELECT ... with "Using temporary; Using filesort"
+#
+explain select distinct a from t0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	Using temporary
+set debug='d,show_explain_probe_1';
+set @show_explain_probe_select_id=1;
+select distinct a from t0;
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	Using temporary
+Warnings:
+Note	1003	select distinct a from t0
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
 drop table t0,t1;
diff --git a/mysql-test/t/show_explain.test b/mysql-test/t/show_explain.test
index 717949d5cc5..4fc6aaad932 100644
--- a/mysql-test/t/show_explain.test
+++ b/mysql-test/t/show_explain.test
@@ -255,6 +255,52 @@ connection con1;
 reap;
 drop table t2;
 
+--echo #
+--echo # SHOW EXPLAIN for SELECT ... ORDER BY with "Using filesort"
+--echo #
+explain select * from t0 order by a;
+
+set debug='d,show_explain_probe_1';
+set @show_explain_probe_select_id=1;
+send select * from t0 order by a; 
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+
+--echo #
+--echo # SHOW EXPLAIN for SELECT ... with "Using temporary"
+--echo #
+connection default;
+explain select distinct a from t0;
+connection con1;
+
+set debug='d,show_explain_probe_1';
+set @show_explain_probe_select_id=1;
+send select distinct a from t0; 
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+
+--echo #
+--echo # SHOW EXPLAIN for SELECT ... with "Using temporary; Using filesort"
+--echo #
+connection default;
+explain select distinct a from t0;
+connection con1;
+
+set debug='d,show_explain_probe_1';
+set @show_explain_probe_select_id=1;
+send select distinct a from t0; 
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+
 ## TODO: Test this: have several SHOW EXPLAIN requests be queued up for a
 ##       thread and served together.
 
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 661ca2b4383..a47ba9b5024 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -3752,9 +3752,9 @@ int st_select_lex::print_explain(select_result_sink *output)
   if (join && join->optimized == 2)
   {
     res= join->print_explain(output, TRUE,
-                             FALSE, // need_tmp_table, 
-                             FALSE, // bool need_order,
-                             FALSE, // bool distinct,
+                             join->need_tmp, // need_tmp_table
+                             (join->order != 0 && !join->skip_sort_order), // bool need_order
+                             join->select_distinct, // bool distinct
                              NULL); //const char *message
     if (res)
       goto err;