← Back to team overview

maria-developers team mailing list archive

Review request: SHOW EXPLAIN

 

Hi Sergei,

Please find attached the combined SHOW EXPLAIN patch. It is ready for review of
the concurrency part.

BR
 Sergei
-- 
Sergei Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog
=== modified file '.bzrignore'
--- .bzrignore	2012-05-22 09:04:32 +0000
+++ .bzrignore	2012-06-12 19:04:11 +0000
@@ -1137,6 +1137,7 @@ plugin/handler_socket/perl-Net-HandlerSo
 libmysqld/libmysqld_exports_file.cc
 libmysqld/gcalc_slicescan.cc
 libmysqld/gcalc_tools.cc
+libmysqld/my_apc.cc
 sql/share/errmsg.sys
 sql/share/mysql
 install_manifest.txt

=== modified file 'client/mysqltest.cc'
--- client/mysqltest.cc	2012-05-21 18:54:41 +0000
+++ client/mysqltest.cc	2012-06-12 19:04:11 +0000
@@ -84,6 +84,8 @@ static my_bool non_blocking_api_enabled=
 #define QUERY_SEND_FLAG  1
 #define QUERY_REAP_FLAG  2
 
+#define QUERY_PRINT_ORIGINAL_FLAG 4
+
 #ifndef HAVE_SETENV
 static int setenv(const char *name, const char *value, int overwrite);
 #endif
@@ -342,7 +344,8 @@ enum enum_commands {
   Q_ERROR,
   Q_SEND,		    Q_REAP,
   Q_DIRTY_CLOSE,	    Q_REPLACE, Q_REPLACE_COLUMN,
-  Q_PING,		    Q_EVAL,
+  Q_PING,		    Q_EVAL, 
+  Q_EVALP,
   Q_EVAL_RESULT,
   Q_ENABLE_QUERY_LOG, Q_DISABLE_QUERY_LOG,
   Q_ENABLE_RESULT_LOG, Q_DISABLE_RESULT_LOG,
@@ -408,6 +411,7 @@ const char *command_names[]=
   "replace_column",
   "ping",
   "eval",
+  "evalp",
   "eval_result",
   /* Enable/disable that the _query_ is logged to result file */
   "enable_query_log",
@@ -8269,7 +8273,8 @@ void run_query(struct st_connection *cn,
   /*
     Evaluate query if this is an eval command
   */
-  if (command->type == Q_EVAL || command->type == Q_SEND_EVAL)
+  if (command->type == Q_EVAL || command->type == Q_SEND_EVAL || 
+      command->type == Q_EVALP)
   {
     init_dynamic_string(&eval_query, "", command->query_len+256, 1024);
     do_eval(&eval_query, command->query, command->end, FALSE);
@@ -8301,10 +8306,20 @@ void run_query(struct st_connection *cn,
   */
   if (!disable_query_log && (flags & QUERY_SEND_FLAG))
   {
-    replace_dynstr_append_mem(ds, query, query_len);
+    char *print_query= query;
+    int print_len= query_len;
+    if (flags & QUERY_PRINT_ORIGINAL_FLAG)
+    {
+      print_query= command->query;
+      print_len= command->end - command->query;
+    }
+    replace_dynstr_append_mem(ds, print_query, print_len);
     dynstr_append_mem(ds, delimiter, delimiter_length);
     dynstr_append_mem(ds, "\n", 1);
   }
+  
+  /* We're done with this flag */
+  flags &= ~QUERY_PRINT_ORIGINAL_FLAG;
 
   /*
     Write the command to the result file before we execute the query
@@ -9162,6 +9177,7 @@ int main(int argc, char **argv)
       case Q_EVAL_RESULT:
         die("'eval_result' command  is deprecated");
       case Q_EVAL:
+      case Q_EVALP:
       case Q_QUERY_VERTICAL:
       case Q_QUERY_HORIZONTAL:
 	if (command->query == command->query_buf)
@@ -9189,6 +9205,9 @@ int main(int argc, char **argv)
           flags= QUERY_REAP_FLAG;
         }
 
+        if (command->type == Q_EVALP)
+          flags |= QUERY_PRINT_ORIGINAL_FLAG;
+
         /* Check for special property for this query */
         display_result_vertically|= (command->type == Q_QUERY_VERTICAL);
 

=== modified file 'libmysqld/CMakeLists.txt'
--- libmysqld/CMakeLists.txt	2012-05-22 09:04:32 +0000
+++ libmysqld/CMakeLists.txt	2012-06-12 19:04:11 +0000
@@ -95,6 +95,7 @@ SET(SQL_EMBEDDED_SOURCES emb_qcache.cc l
            ../sql/create_options.cc ../sql/rpl_utility.cc
            ../sql/rpl_reporting.cc
            ../sql/sql_expression_cache.cc
+           ../sql/my_apc.cc ../sql/my_apc.h
            ${GEN_SOURCES}
            ${MYSYS_LIBWRAP_SOURCE}
 )

=== modified file 'mysql-test/include/index_merge2.inc'
--- mysql-test/include/index_merge2.inc	2012-01-16 19:16:35 +0000
+++ mysql-test/include/index_merge2.inc	2012-06-12 19:04:11 +0000
@@ -343,6 +343,7 @@ alter table t1 add index i3(key3);
 update t1 set key2=key1,key3=key1;
 
 # to test the bug, the following must use "sort_union":
+--replace_column 9 ROWS
 explain select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
 select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
 drop table t1;

=== modified file 'mysql-test/r/index_merge_innodb.result'
--- mysql-test/r/index_merge_innodb.result	2012-06-04 15:26:11 +0000
+++ mysql-test/r/index_merge_innodb.result	2012-06-12 19:04:11 +0000
@@ -313,7 +313,7 @@ alter table t1 add index i3(key3);
 update t1 set key2=key1,key3=key1;
 explain select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index_merge	i2,i3	i3,i2	4,4	NULL	9	Using sort_union(i3,i2); Using where
+1	SIMPLE	t1	index_merge	i2,i3	i3,i2	4,4	NULL	ROWS	Using sort_union(i3,i2); Using where
 select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
 key1	key2	key3
 31	31	31

=== modified file 'mysql-test/r/index_merge_myisam.result'
--- mysql-test/r/index_merge_myisam.result	2012-02-15 17:08:08 +0000
+++ mysql-test/r/index_merge_myisam.result	2012-06-12 19:04:11 +0000
@@ -1146,7 +1146,7 @@ alter table t1 add index i3(key3);
 update t1 set key2=key1,key3=key1;
 explain select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index_merge	i2,i3	i3,i2	4,4	NULL	11	Using sort_union(i3,i2); Using where
+1	SIMPLE	t1	index_merge	i2,i3	i3,i2	4,4	NULL	ROWS	Using sort_union(i3,i2); Using where
 select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
 key1	key2	key3
 31	31	31

=== added file 'mysql-test/r/show_explain.result'
--- mysql-test/r/show_explain.result	1970-01-01 00:00:00 +0000
+++ mysql-test/r/show_explain.result	2012-06-19 09:51:33 +0000
@@ -0,0 +1,698 @@
+drop table if exists t0, t1, t2, t3, t4;
+drop view if exists v1;
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(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;
+alter table t1 add b int, add c int, add filler char(32);
+update t1 set b=a, c=a, filler='fooo';
+alter table t1 add key(a), add key(b);
+show explain for 2*1000*1000*1000;
+ERROR HY000: Unknown thread id: 2000000000
+show explain for (select max(a) from t0);
+ERROR 42000: This version of MariaDB doesn't yet support 'Usage of subqueries or stored function calls as part of this statement'
+show explain for $thr2;
+ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
+show explain for $thr1;
+ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+select count(*) from t1 where a < 100000;
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	a	a	5	NULL	1000	Using where; Using index
+Warnings:
+Note	1003	select count(*) from t1 where a < 100000
+count(*)
+1000
+select max(c) from t1 where a < 10;
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a	a	5	NULL	10	Using index condition
+Warnings:
+Note	1003	select max(c) from t1 where a < 10
+max(c)
+9
+# We can catch EXPLAIN, too.
+set @show_expl_tmp= @@optimizer_switch;
+set optimizer_switch='index_condition_pushdown=on,mrr=on,mrr_sort_keys=on';
+explain select max(c) from t1 where a < 10;
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a	a	5	NULL	10	Using index condition; Rowid-ordered scan
+Warnings:
+Note	1003	explain select max(c) from t1 where a < 10
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a	a	5	NULL	10	Using index condition; Rowid-ordered scan
+set optimizer_switch= @show_expl_tmp;
+# UNION, first branch 
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+explain select a from t0 A union select a+1 from t0 B;
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	
+2	UNION	B	ALL	NULL	NULL	NULL	NULL	10	
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	explain select a from t0 A union select a+1 from t0 B
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	
+2	UNION	B	ALL	NULL	NULL	NULL	NULL	10	
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
+# UNION, second branch
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+explain select a from t0 A union select a+1 from t0 B;
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	
+2	UNION	B	ALL	NULL	NULL	NULL	NULL	10	
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	explain select a from t0 A union select a+1 from t0 B
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	
+2	UNION	B	ALL	NULL	NULL	NULL	NULL	10	
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
+# Uncorrelated  subquery, select
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+select a, (select max(a) from t0 B) from t0 A where a<1;
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	Using where
+2	SUBQUERY	B	ALL	NULL	NULL	NULL	NULL	10	
+Warnings:
+Note	1003	select a, (select max(a) from t0 B) from t0 A where a<1
+a	(select max(a) from t0 B)
+0	9
+# Uncorrelated  subquery, explain
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+explain select a, (select max(a) from t0 B) from t0 A where a<1;
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	Using where
+2	SUBQUERY	B	ALL	NULL	NULL	NULL	NULL	10	
+Warnings:
+Note	1003	explain select a, (select max(a) from t0 B) from t0 A where a<1
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	Using where
+2	SUBQUERY	B	ALL	NULL	NULL	NULL	NULL	10	
+# correlated  subquery, select
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1;
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	10	Using where
+2	DEPENDENT SUBQUERY	b	ALL	NULL	NULL	NULL	NULL	10	Using where
+Warnings:
+Note	1003	select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1
+a	(select max(a) from t0 b where b.a+a.a<10)
+0	9
+# correlated  subquery, explain
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1;
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	10	Using where
+2	DEPENDENT SUBQUERY	b	ALL	NULL	NULL	NULL	NULL	10	Using where
+Warnings:
+Note	1003	select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1
+a	(select max(a) from t0 b where b.a+a.a<10)
+0	9
+# correlated  subquery, select, while inside the subquery
+set @show_explain_probe_select_id=2;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1;
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	10	Using where
+2	DEPENDENT SUBQUERY	b	ALL	NULL	NULL	NULL	NULL	10	Using where
+Warnings:
+Note	1003	select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1
+a	(select max(a) from t0 b where b.a+a.a<10)
+0	9
+# correlated  subquery, explain, while inside the subquery
+set @show_explain_probe_select_id=2;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1;
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	10	Using where
+2	DEPENDENT SUBQUERY	b	ALL	NULL	NULL	NULL	NULL	10	Using where
+Warnings:
+Note	1003	select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1
+a	(select max(a) from t0 b where b.a+a.a<10)
+0	9
+# correlated  subquery, explain, while inside the subquery
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_end';
+select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1;
+show explain for $thr2;
+ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
+a	(select max(a) from t0 b where b.a+a.a<10)
+0	9
+# Try to do SHOW EXPLAIN for a query that runs a  SET command:
+#   I've found experimentally that select_id==2 here...
+# 
+set @show_explain_probe_select_id=2;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+set @foo= (select max(a) from t0 where sin(a) >0);
+show explain for $thr2;
+ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
+#
+# Attempt SHOW EXPLAIN for an UPDATE
+#
+create table t2 as select a as a, a as dummy from t0 limit 2;
+set @show_explain_probe_select_id=2;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+update t2 set dummy=0 where (select max(a) from t0 where t2.a + t0.a <3) >3 ;
+show explain for $thr2;
+ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
+show explain for $thr2;
+ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
+drop table t2;
+#
+# Attempt SHOW EXPLAIN for a DELETE
+# 
+create table t2 as select a as a, a as dummy from t0 limit 2;
+set @show_explain_probe_select_id=2;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+delete from t2 where (select max(a) from t0 where t2.a + t0.a <3) >3 ;
+show explain for $thr2;
+ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
+show explain for $thr2;
+ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
+drop table t2;
+#
+# Multiple SHOW EXPLAIN calls for one select 
+# 
+create table t2 as select a as a, a as dummy from t0 limit 3;
+set @show_explain_probe_select_id=2;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2;
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	
+2	DEPENDENT SUBQUERY	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
+Warnings:
+Note	1003	select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Query plan already deleted
+Warnings:
+Note	1003	select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Query plan already deleted
+Warnings:
+Note	1003	select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2
+a	SUBQ
+0	0
+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_dbug='d,show_explain_probe_join_exec_start';
+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_dbug='d,show_explain_probe_join_exec_start';
+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_dbug='d,show_explain_probe_join_exec_start';
+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
+set debug_dbug='';
+#
+# MDEV-238: SHOW EXPLAIN: Server crashes in JOIN::print_explain with FROM subquery and GROUP BY
+#
+CREATE TABLE t2 ( a INT );
+INSERT INTO t2 VALUES (1),(2),(1),(4),(2);
+explain SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	Using temporary; Using filesort
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	Using join buffer (flat, BNL join)
+set debug_dbug='d,show_explain_in_find_all_keys';
+SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a;
+# FIXED by "conservative assumptions about when QEP is available" fix:
+#    NOTE: current code will not show "Using join buffer": 
+show explain for $thr2;
+ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
+a
+1
+2
+4
+set debug_dbug='';
+DROP TABLE t2;
+#
+# MDEV-239: Assertion `field_types == 0 ... ' failed in Protocol_text::store(double, uint32, String*) with 
+#          SHOW EXPLAIN over EXPLAIN EXTENDED
+#
+CREATE TABLE t2 (a INT);
+INSERT INTO t2 VALUES (1),(2),(1),(4),(2);
+EXPLAIN EXTENDED SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a ;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using temporary; Using filesort
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using join buffer (flat, BNL join)
+Warnings:
+Note	1003	select `test`.`t2`.`a` AS `a` from `test`.`t2` join `test`.`t2` group by `test`.`t2`.`a`
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_end';
+EXPLAIN EXTENDED SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a ;
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	Using temporary; Using filesort
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	Using join buffer (flat, BNL join)
+Warnings:
+Note	1003	EXPLAIN EXTENDED SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using temporary; Using filesort
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	100.00	Using join buffer (flat, BNL join)
+Warnings:
+Note	1003	select `test`.`t2`.`a` AS `a` from `test`.`t2` join `test`.`t2` group by `test`.`t2`.`a`
+set debug_dbug='';
+DROP TABLE t2;
+#
+# MDEV-240: SHOW EXPLAIN: Assertion `this->optimized == 2' failed in 
+# JOIN::print_explain on query with a JOIN, TEMPTABLE view,
+#          
+CREATE TABLE t3 (a INT);
+CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t3;
+INSERT INTO t3 VALUES (8);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (4),(5),(6),(7),(8),(9);
+explain SELECT * FROM v1, t2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	6	
+2	DERIVED	t3	system	NULL	NULL	NULL	NULL	1	
+set @show_explain_probe_select_id=2;
+set debug_dbug='d,show_explain_probe_join_exec_end';
+SELECT * FROM v1, t2;
+show explain for $thr2;
+ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
+a	b
+8	4
+8	5
+8	6
+8	7
+8	8
+8	9
+set debug_dbug='';
+DROP VIEW v1;
+DROP TABLE t2, t3;
+#
+# MDEV-267: SHOW EXPLAIN: Server crashes in JOIN::print_explain on most of queries
+#
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_end';
+select sleep(1);
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	select sleep(1)
+sleep(1)
+0
+set debug_dbug='';
+#
+# Same as above, but try another reason for JOIN to be degenerate
+#
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_end';
+select * from t0 where 1>10;
+show explain for $thr2;
+ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
+a
+set debug_dbug='';
+#
+# Same as above, but try another reason for JOIN to be degenerate (2)
+#
+create table t3(a int primary key);
+insert into t3 select a from t0;
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_end';
+select * from t0,t3 where t3.a=112233;
+show explain for $thr2;
+ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
+a	a
+set debug_dbug='';
+drop table t3;
+#
+# MDEV-270: SHOW EXPLAIN: server crashes in JOIN::print_explain on a query with 
+#           select tables optimized away
+#
+CREATE TABLE t2 (pk INT PRIMARY KEY, a INT ) ENGINE=MyISAM;
+INSERT INTO t2 VALUES 
+(1,4),(2,62),(3,7),(4,1),(5,0),(6,7),(7,7),(8,1),(9,7),(10,1),
+(11,5),(12,2),(13,0),(14,1),(15,8),(16,1),(17,1),(18,9),(19,1),(20,5) ;
+explain SELECT * FROM t2 WHERE a = 
+(SELECT MAX(a) FROM t2 
+WHERE pk= (SELECT MAX(pk) FROM t2 WHERE pk = 3)
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	20	Using where
+2	SUBQUERY	t2	const	PRIMARY	PRIMARY	4	const	1	Using where
+3	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+set @show_explain_probe_select_id=2;
+set debug_dbug='d,show_explain_probe_do_select';
+SELECT * FROM t2 WHERE a = 
+(SELECT MAX(a) FROM t2 
+WHERE pk= (SELECT MAX(pk) FROM t2 WHERE pk = 3)
+);
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	20	Using where
+2	SUBQUERY	t2	const	PRIMARY	PRIMARY	4	const	1	Using where
+3	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+Warnings:
+Note	1003	SELECT * FROM t2 WHERE a = 
+(SELECT MAX(a) FROM t2 
+WHERE pk= (SELECT MAX(pk) FROM t2 WHERE pk = 3)
+)
+pk	a
+3	7
+6	7
+7	7
+9	7
+set debug_dbug='';
+drop table t2;
+#
+# MDEV-273: SHOW EXPLAIN: server crashes in JOIN::print_explain on a query with impossible WHERE
+#
+CREATE TABLE t2 (a1 INT, KEY(a1)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+(4),(6),(7),(1),(0),(7),(7),(1),(7),(1),
+(5),(2),(0),(1),(8),(1),(1),(9),(1),(5);
+CREATE TABLE t3 (b1 INT) ENGINE=MyISAM;
+INSERT INTO t3 VALUES
+(4),(5),(8),(4),(8),(2),(9),(6),(4),(8),
+(3),(5),(9),(6),(8),(3),(2),(6),(3),(1),
+(4),(3),(1),(7),(0),(0),(9),(5),(9),(0),
+(2),(2),(5),(9),(1),(4),(8),(6),(5),(5),
+(1),(7),(2),(8),(9),(3),(2),(6),(6),(5),
+(4),(3),(2),(7),(4),(6),(0),(8),(5),(8),
+(2),(9),(7),(5),(7),(0),(4),(3),(1),(0),
+(6),(2),(8),(3),(7),(3),(5),(5),(1),(2),
+(1),(7),(1),(9),(9),(8),(3);
+CREATE TABLE t4 (c1 INT) ENGINE=MyISAM;
+EXPLAIN
+SELECT count(*) FROM t2, t3
+WHERE a1 < ALL ( 
+SELECT a1 FROM t2
+WHERE a1 IN ( SELECT a1 FROM t2, t4 )
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	index	NULL	a1	5	NULL	20	Using where; Using index
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	87	Using join buffer (flat, BNL join)
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_do_select';
+SELECT count(*) FROM t2, t3
+WHERE a1 < ALL ( 
+SELECT a1 FROM t2
+WHERE a1 IN ( SELECT a1 FROM t2, t4 )
+);
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	index	NULL	a1	5	NULL	20	Using where; Using index
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	87	Using join buffer (flat, BNL join)
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+Warnings:
+Note	1003	SELECT count(*) FROM t2, t3
+WHERE a1 < ALL ( 
+SELECT a1 FROM t2
+WHERE a1 IN ( SELECT a1 FROM t2, t4 )
+)
+count(*)
+1740
+set debug_dbug='';
+drop table t2, t3, t4;
+#
+# MDEV-275: SHOW EXPLAIN: server crashes in JOIN::print_explain with IN subquery and aggregate function
+#
+CREATE TABLE t2 ( `pk` INT NOT NULL PRIMARY KEY, `a1` INT NOT NULL, KEY(`a1`)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES 
+(1,5),(2,4),(3,6),(4,9),(5,2),(6,8),(7,4),(8,8),(9,0),(10,43),
+(11,23),(12,3),(13,45),(14,16),(15,2),(16,33),(17,2),(18,5),(19,9),(20,2);
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_end';
+SELECT * FROM t2 WHERE (5, 78) IN (SELECT `a1`, MAX(`a1`) FROM t2 GROUP BY `a1`);
+show explain for $thr2;
+ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
+pk	a1
+set debug_dbug='';
+DROP TABLE t2;
+DROP TABLE t1;
+#
+# MDEV-305: SHOW EXPLAIN: ref returned by SHOW EXPLAIN is different from the normal EXPLAIN ('const' vs empty string)
+#
+CREATE TABLE t1(a INT, KEY(a));
+INSERT INTO t1 VALUES (3),(1),(5),(1);
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+SELECT 'test' FROM t1 WHERE a=1;
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	a	a	5	const	1	Using index
+Warnings:
+Note	1003	SELECT 'test' FROM t1 WHERE a=1
+test
+test
+test
+set debug_dbug='';
+DROP TABLE t1;
+#
+# MDEV-299: SHOW EXPLAIN: Plan produced by SHOW EXPLAIN changes back and forth during query execution
+#
+create table t1 (key1 int, col1 int, col2 int, filler char(100), key(key1));
+insert into t1 select A.a+ 10 * B.a, 10, 10, 'filler-data' from t0 A, t0 B;
+update t1 set col1=3, col2=10 where key1=1;
+update t1 set col1=3, col2=1000 where key1=2;
+update t1 set col1=3, col2=10 where key1=3;
+update t1 set col1=3, col2=1000 where key1=4;
+set @tmp_mdev299_jcl= @@join_cache_level;
+set join_cache_level=0;
+explain select count(*) from t1 A, t1 B where B.key1 < A.col2 and A.col1=3 AND B.col2 + 1 < 100;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	A	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	SIMPLE	B	ALL	key1	NULL	NULL	NULL	100	Range checked for each record (index map: 0x1)
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_test_if_quick_select';
+select count(*) from t1 A, t1 B where B.key1 < A.col2 and A.col1=3 AND B.col2 + 1 < 100;
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	A	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	SIMPLE	B	ALL	key1	NULL	NULL	NULL	100	Range checked for each record (index map: 0x1)
+Warnings:
+Note	1003	select count(*) from t1 A, t1 B where B.key1 < A.col2 and A.col1=3 AND B.col2 + 1 < 100
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	A	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	SIMPLE	B	ALL	key1	NULL	NULL	NULL	100	Range checked for each record (index map: 0x1)
+Warnings:
+Note	1003	select count(*) from t1 A, t1 B where B.key1 < A.col2 and A.col1=3 AND B.col2 + 1 < 100
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	A	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	SIMPLE	B	ALL	key1	NULL	NULL	NULL	100	Range checked for each record (index map: 0x1)
+Warnings:
+Note	1003	select count(*) from t1 A, t1 B where B.key1 < A.col2 and A.col1=3 AND B.col2 + 1 < 100
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	A	ALL	NULL	NULL	NULL	NULL	100	Using where
+1	SIMPLE	B	ALL	key1	NULL	NULL	NULL	100	Range checked for each record (index map: 0x1)
+Warnings:
+Note	1003	select count(*) from t1 A, t1 B where B.key1 < A.col2 and A.col1=3 AND B.col2 + 1 < 100
+count(*)
+212
+set debug_dbug='';
+drop table t1;
+#
+# MDEV-297: SHOW EXPLAIN: Server gets stuck until timeout occurs while
+#           executing SHOW INDEX and SHOW EXPLAIN in parallel
+#
+CREATE TABLE t1(a INT, b INT, c INT, KEY(a), KEY(b), KEY(c));
+INSERT INTO t1 (a) VALUES (3),(1),(5),(1);
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+SHOW INDEX FROM t1;
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	STATISTICS	ALL	NULL	NULL	NULL	NULL	NULL	Skip_open_table; Scanned all databases
+Warnings:
+Note	1003	SHOW INDEX FROM t1
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
+t1	1	a	1	a	A	NULL	NULL	NULL	YES	BTREE		
+t1	1	b	1	b	A	NULL	NULL	NULL	YES	BTREE		
+t1	1	c	1	c	A	NULL	NULL	NULL	YES	BTREE		
+set debug_dbug='';
+DROP TABLE t1;
+#
+# MDEV-324: SHOW EXPLAIN: Plan produced by SHOW EXPLAIN for a query with TEMPTABLE view 
+#           loses 'DERIVED' line on the way without saying that the plan was already deleted
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1;
+EXPLAIN SELECT a + 1 FROM v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	
+set debug_dbug='d,show_explain_probe_join_tab_preread';
+set @show_explain_probe_select_id=1;
+SELECT a + 1 FROM v1;
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Query plan already deleted
+Warnings:
+Note	1003	SELECT a + 1 FROM v1
+a + 1
+2
+3
+set debug_dbug='';
+DROP VIEW v1;
+DROP TABLE t1;
+#
+# MDEV-323: SHOW EXPLAIN: Plan produced by SHOW EXPLAIN loses 
+#            'UNION RESULT' line on the way without saying that the plan was already deleted
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (4),(6);
+EXPLAIN 
+SELECT a FROM t1 WHERE a IN ( SELECT 1+SLEEP(0.01) UNION SELECT 2 );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+set debug_dbug='d,show_explain_probe_union_read';
+SELECT a FROM t1 WHERE a IN ( SELECT 1+SLEEP(0.01) UNION SELECT 2 );
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	SELECT a FROM t1 WHERE a IN ( SELECT 1+SLEEP(0.01) UNION SELECT 2 )
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	DEPENDENT UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+Warnings:
+Note	1003	SELECT a FROM t1 WHERE a IN ( SELECT 1+SLEEP(0.01) UNION SELECT 2 )
+a
+set debug_dbug='';
+DROP TABLE t1;
+#
+# MDEV-327: SHOW EXPLAIN: Different select_type in plans produced by SHOW EXPLAIN 
+#           and standard EXPLAIN: 'SUBQUERY' vs 'DEPENDENT SUBQUERY'
+#
+CREATE TABLE t1 (a INT) ENGINE=Aria;
+INSERT INTO t1 VALUES
+(4),(6),(3),(5),(3),(246),(2),(9),(3),(8),
+(1),(8),(8),(5),(7),(5),(1),(6),(2),(9);
+CREATE TABLE t2 (b INT) ENGINE=Aria;
+INSERT INTO t2 VALUES
+(1),(7),(4),(7),(0),(2),(9),(4),(0),(9),
+(1),(3),(8),(8),(18),(84),(6),(3),(6),(6);
+EXPLAIN
+SELECT * FROM t1, ( SELECT * FROM t2 ) AS alias
+WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	20	Using where
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	20	
+3	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	20	
+3	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	20	Using where
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+SELECT * FROM t1, ( SELECT * FROM t2 ) AS alias
+WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b );
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	20	Using where
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	20	
+3	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	20	
+3	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	20	Using where
+Warnings:
+Note	1003	SELECT * FROM t1, ( SELECT * FROM t2 ) AS alias
+WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b )
+a	b
+set debug_dbug='';
+DROP TABLE t1, t2;
+drop table t0;

=== added file 'mysql-test/t/show_explain.test'
--- mysql-test/t/show_explain.test	1970-01-01 00:00:00 +0000
+++ mysql-test/t/show_explain.test	2012-06-19 09:51:01 +0000
@@ -0,0 +1,706 @@
+#
+# Tests for SHOW EXPLAIN FOR functionality
+#
+--source include/have_debug.inc
+
+--disable_warnings
+drop table if exists t0, t1, t2, t3, t4;
+drop view if exists v1;
+--enable_warnings
+
+# 
+# Testcases in this file do not work with embedded server. The reason for this
+# is that we use the following commands for synchronization:
+#
+#    set @show_explain_probe_select_id=1;
+#    set debug_dbug='d,show_explain_probe_join_exec_start';
+#    send select count(*) from t1 where a < 100000;
+#
+# When ran with mysqltest_embedded, this translates into: 
+#
+#    Thread1> DBUG_PUSH("d,show_explain_probe_join_exec_start");
+#    Thread1> create another thread for doing "send ... reap"
+#    Thread2> mysql_parse("select count(*) from t1 where a < 100000");
+#
+# That is, "select count(*) ..." is ran in a thread for which DBUG_PUSH(...)
+# has not been called. As a result, show_explain_probe_join_exec_start does not fire, and
+# "select count(*) ..." does not wait till its SHOW EXPLAIN command, and the
+# test fails.
+#
+-- source include/not_embedded.inc
+
+
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(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;
+alter table t1 add b int, add c int, add filler char(32);
+update t1 set b=a, c=a, filler='fooo';
+alter table t1 add key(a), add key(b);
+
+# 
+# Try killing a non-existent thread
+# 
+--error ER_NO_SUCH_THREAD
+show explain for 2*1000*1000*1000;
+
+--error ER_NOT_SUPPORTED_YET
+show explain for (select max(a) from t0);
+
+# 
+# Setup two threads and their ids
+#
+let $thr1=`select connection_id()`;
+connect (con1, localhost, root,,);
+connection con1;
+let $thr2=`select connection_id()`;
+connection default;
+
+# SHOW EXPLAIN FOR <idle thread>
+--error ER_ERROR_WHEN_EXECUTING_COMMAND
+evalp show explain for $thr2;
+
+# SHOW EXPLAIN FOR <ourselves>
+--error ER_ERROR_WHEN_EXECUTING_COMMAND
+evalp show explain for $thr1;
+
+let $wait_condition= select State='show_explain_trap' from information_schema.processlist where id=$thr2;
+
+#
+# Test SHOW EXPLAIN for simple queries
+#
+connection con1;
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+send select count(*) from t1 where a < 100000;
+
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+
+
+send select max(c) from t1 where a < 10;
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+
+
+--echo # We can catch EXPLAIN, too.
+set @show_expl_tmp= @@optimizer_switch;
+set optimizer_switch='index_condition_pushdown=on,mrr=on,mrr_sort_keys=on';
+send explain select max(c) from t1 where a < 10;
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+set optimizer_switch= @show_expl_tmp;
+
+
+--echo # UNION, first branch 
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+send explain select a from t0 A union select a+1 from t0 B;
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+
+
+--echo # UNION, second branch
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+send explain select a from t0 A union select a+1 from t0 B;
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+
+
+--echo # Uncorrelated  subquery, select
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+send select a, (select max(a) from t0 B) from t0 A where a<1;
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+
+
+--echo # Uncorrelated  subquery, explain
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+send explain select a, (select max(a) from t0 B) from t0 A where a<1;
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+
+--echo # correlated  subquery, select
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+send select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1;
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+
+--echo # correlated  subquery, explain
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+send select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1;
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+
+--echo # correlated  subquery, select, while inside the subquery
+set @show_explain_probe_select_id=2; # <---
+set debug_dbug='d,show_explain_probe_join_exec_start';
+send select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1;
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+
+--echo # correlated  subquery, explain, while inside the subquery
+set @show_explain_probe_select_id=2;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+send select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1;
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+
+
+--echo # correlated  subquery, explain, while inside the subquery
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_end';
+send select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1;
+connection default;
+--source include/wait_condition.inc
+--error ER_ERROR_WHEN_EXECUTING_COMMAND
+evalp show explain for $thr2;
+connection con1;
+reap;
+
+# TODO: explain in the parent subuqery when the un-correlated child has been
+# run (and have done irreversible cleanups)
+#  ^^ Is this at all possible after 5.3? 
+#     Maybe, for 5.3 try this: 
+#       - run before/after the parent has invoked child's optimization
+#       - run after materialization 
+
+--echo # Try to do SHOW EXPLAIN for a query that runs a  SET command:
+--echo #   I've found experimentally that select_id==2 here...
+--echo # 
+set @show_explain_probe_select_id=2;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+send set @foo= (select max(a) from t0 where sin(a) >0);
+connection default;
+--source include/wait_condition.inc
+--error ER_ERROR_WHEN_EXECUTING_COMMAND
+evalp show explain for $thr2;
+connection con1;
+reap;
+
+--echo #
+--echo # Attempt SHOW EXPLAIN for an UPDATE
+--echo #
+create table t2 as select a as a, a as dummy from t0 limit 2;
+set @show_explain_probe_select_id=2;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+send update t2 set dummy=0 where (select max(a) from t0 where t2.a + t0.a <3) >3 ;
+connection default;
+--source include/wait_condition.inc
+--error ER_ERROR_WHEN_EXECUTING_COMMAND
+evalp show explain for $thr2;
+--error ER_ERROR_WHEN_EXECUTING_COMMAND
+evalp show explain for $thr2;
+connection con1;
+reap;
+drop table t2;
+
+--echo #
+--echo # Attempt SHOW EXPLAIN for a DELETE
+--echo # 
+create table t2 as select a as a, a as dummy from t0 limit 2;
+set @show_explain_probe_select_id=2;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+send delete from t2 where (select max(a) from t0 where t2.a + t0.a <3) >3 ;
+connection default;
+--source include/wait_condition.inc
+--error ER_ERROR_WHEN_EXECUTING_COMMAND
+evalp show explain for $thr2;
+--error ER_ERROR_WHEN_EXECUTING_COMMAND
+evalp show explain for $thr2;
+connection con1;
+reap;
+drop table t2;
+
+
+--echo #
+--echo # Multiple SHOW EXPLAIN calls for one select 
+--echo # 
+create table t2 as select a as a, a as dummy from t0 limit 3;
+set @show_explain_probe_select_id=2;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+send select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2;
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+evalp show explain for $thr2;
+evalp show explain for $thr2;
+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_dbug='d,show_explain_probe_join_exec_start';
+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_dbug='d,show_explain_probe_join_exec_start';
+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_dbug='d,show_explain_probe_join_exec_start';
+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;
+set debug_dbug='';
+
+--echo #
+--echo # MDEV-238: SHOW EXPLAIN: Server crashes in JOIN::print_explain with FROM subquery and GROUP BY
+--echo #
+CREATE TABLE t2 ( a INT ); 
+INSERT INTO t2 VALUES (1),(2),(1),(4),(2);
+explain SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a;
+
+set debug_dbug='d,show_explain_in_find_all_keys';
+send SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a;
+
+connection default;
+--source include/wait_condition.inc
+--echo # FIXED by "conservative assumptions about when QEP is available" fix:
+--echo #    NOTE: current code will not show "Using join buffer": 
+--error ER_ERROR_WHEN_EXECUTING_COMMAND
+evalp show explain for $thr2;
+connection con1;
+reap;
+set debug_dbug='';
+DROP TABLE t2;
+
+
+--echo #
+--echo # MDEV-239: Assertion `field_types == 0 ... ' failed in Protocol_text::store(double, uint32, String*) with 
+--echo #          SHOW EXPLAIN over EXPLAIN EXTENDED
+--echo #
+
+
+CREATE TABLE t2 (a INT); 
+INSERT INTO t2 VALUES (1),(2),(1),(4),(2);
+
+EXPLAIN EXTENDED SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a ;
+
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_end';
+send EXPLAIN EXTENDED SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a ;
+
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+set debug_dbug='';
+DROP TABLE t2;
+
+
+--echo #
+--echo # MDEV-240: SHOW EXPLAIN: Assertion `this->optimized == 2' failed in 
+--echo # JOIN::print_explain on query with a JOIN, TEMPTABLE view,
+--echo #          
+CREATE TABLE t3 (a INT); 
+CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t3;
+INSERT INTO t3 VALUES (8);
+CREATE TABLE t2 (b INT);
+INSERT INTO t2 VALUES (4),(5),(6),(7),(8),(9);
+explain SELECT * FROM v1, t2;
+
+set @show_explain_probe_select_id=2;
+set debug_dbug='d,show_explain_probe_join_exec_end';
+send SELECT * FROM v1, t2;
+
+connection default;
+--source include/wait_condition.inc
+--error ER_ERROR_WHEN_EXECUTING_COMMAND
+evalp show explain for $thr2;
+connection con1;
+reap;
+set debug_dbug='';
+DROP VIEW v1;
+DROP TABLE t2, t3;
+
+--echo #
+--echo # MDEV-267: SHOW EXPLAIN: Server crashes in JOIN::print_explain on most of queries
+--echo #
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_end';
+send select sleep(1);
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+set debug_dbug='';
+
+
+--echo #
+--echo # Same as above, but try another reason for JOIN to be degenerate
+--echo #
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_end';
+send select * from t0 where 1>10;
+connection default;
+--source include/wait_condition.inc
+--error ER_ERROR_WHEN_EXECUTING_COMMAND
+evalp show explain for $thr2;
+connection con1;
+reap;
+set debug_dbug='';
+
+--echo #
+--echo # Same as above, but try another reason for JOIN to be degenerate (2)
+--echo #
+create table t3(a int primary key);
+insert into t3 select a from t0;
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_end';
+send select * from t0,t3 where t3.a=112233;
+connection default;
+--source include/wait_condition.inc
+--error ER_ERROR_WHEN_EXECUTING_COMMAND
+evalp show explain for $thr2;
+connection con1;
+reap;
+set debug_dbug='';
+drop table t3;
+
+--echo #
+--echo # MDEV-270: SHOW EXPLAIN: server crashes in JOIN::print_explain on a query with 
+--echo #           select tables optimized away
+--echo #
+
+CREATE TABLE t2 (pk INT PRIMARY KEY, a INT ) ENGINE=MyISAM; 
+INSERT INTO t2 VALUES 
+  (1,4),(2,62),(3,7),(4,1),(5,0),(6,7),(7,7),(8,1),(9,7),(10,1),
+  (11,5),(12,2),(13,0),(14,1),(15,8),(16,1),(17,1),(18,9),(19,1),(20,5) ;
+
+explain SELECT * FROM t2 WHERE a = 
+       (SELECT MAX(a) FROM t2 
+        WHERE pk= (SELECT MAX(pk) FROM t2 WHERE pk = 3)
+       );
+
+set @show_explain_probe_select_id=2;
+set debug_dbug='d,show_explain_probe_do_select';
+send SELECT * FROM t2 WHERE a = 
+       (SELECT MAX(a) FROM t2 
+        WHERE pk= (SELECT MAX(pk) FROM t2 WHERE pk = 3)
+       );
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+set debug_dbug='';
+drop table t2;
+
+
+--echo #
+--echo # MDEV-273: SHOW EXPLAIN: server crashes in JOIN::print_explain on a query with impossible WHERE
+--echo #
+CREATE TABLE t2 (a1 INT, KEY(a1)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+  (4),(6),(7),(1),(0),(7),(7),(1),(7),(1),
+  (5),(2),(0),(1),(8),(1),(1),(9),(1),(5);
+
+CREATE TABLE t3 (b1 INT) ENGINE=MyISAM;
+INSERT INTO t3 VALUES
+  (4),(5),(8),(4),(8),(2),(9),(6),(4),(8),
+  (3),(5),(9),(6),(8),(3),(2),(6),(3),(1),
+  (4),(3),(1),(7),(0),(0),(9),(5),(9),(0),
+  (2),(2),(5),(9),(1),(4),(8),(6),(5),(5),
+  (1),(7),(2),(8),(9),(3),(2),(6),(6),(5),
+  (4),(3),(2),(7),(4),(6),(0),(8),(5),(8),
+  (2),(9),(7),(5),(7),(0),(4),(3),(1),(0),
+  (6),(2),(8),(3),(7),(3),(5),(5),(1),(2),
+  (1),(7),(1),(9),(9),(8),(3);
+CREATE TABLE t4 (c1 INT) ENGINE=MyISAM;
+
+EXPLAIN
+SELECT count(*) FROM t2, t3
+WHERE a1 < ALL ( 
+  SELECT a1 FROM t2
+  WHERE a1 IN ( SELECT a1 FROM t2, t4 )
+);
+
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_do_select';
+send
+SELECT count(*) FROM t2, t3
+WHERE a1 < ALL ( 
+  SELECT a1 FROM t2
+  WHERE a1 IN ( SELECT a1 FROM t2, t4 )
+);
+
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+set debug_dbug='';
+drop table t2, t3, t4;
+
+--echo #
+--echo # MDEV-275: SHOW EXPLAIN: server crashes in JOIN::print_explain with IN subquery and aggregate function
+--echo #
+CREATE TABLE t2 ( `pk` INT NOT NULL PRIMARY KEY, `a1` INT NOT NULL, KEY(`a1`)) ENGINE=MyISAM; 
+INSERT INTO t2 VALUES 
+  (1,5),(2,4),(3,6),(4,9),(5,2),(6,8),(7,4),(8,8),(9,0),(10,43),
+  (11,23),(12,3),(13,45),(14,16),(15,2),(16,33),(17,2),(18,5),(19,9),(20,2);
+
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_end';
+send
+  SELECT * FROM t2 WHERE (5, 78) IN (SELECT `a1`, MAX(`a1`) FROM t2 GROUP BY `a1`);
+connection default;
+--source include/wait_condition.inc
+--error ER_ERROR_WHEN_EXECUTING_COMMAND
+evalp show explain for $thr2;
+connection con1;
+reap;
+set debug_dbug='';
+
+DROP TABLE t2;
+
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-305: SHOW EXPLAIN: ref returned by SHOW EXPLAIN is different from the normal EXPLAIN ('const' vs empty string)
+--echo #
+CREATE TABLE t1(a INT, KEY(a));
+INSERT INTO t1 VALUES (3),(1),(5),(1);
+
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+
+send SELECT 'test' FROM t1 WHERE a=1;
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+set debug_dbug='';
+
+DROP TABLE t1;
+
+
+--echo #
+--echo # MDEV-299: SHOW EXPLAIN: Plan produced by SHOW EXPLAIN changes back and forth during query execution
+--echo #
+
+create table t1 (key1 int, col1 int, col2 int, filler char(100), key(key1));
+insert into t1 select A.a+ 10 * B.a, 10, 10, 'filler-data' from t0 A, t0 B;
+
+# Make matches 3 records 
+update t1 set col1=3, col2=10 where key1=1; # small range
+update t1 set col1=3, col2=1000 where key1=2; # big range 
+update t1 set col1=3, col2=10 where key1=3; # small range again
+update t1 set col1=3, col2=1000 where key1=4; # big range 
+
+set @tmp_mdev299_jcl= @@join_cache_level;
+set join_cache_level=0;
+
+explain select count(*) from t1 A, t1 B where B.key1 < A.col2 and A.col1=3 AND B.col2 + 1 < 100;
+
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_test_if_quick_select';
+
+send 
+select count(*) from t1 A, t1 B where B.key1 < A.col2 and A.col1=3 AND B.col2 + 1 < 100;
+
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+
+set debug_dbug='';
+drop table t1;
+
+--echo #
+--echo # MDEV-297: SHOW EXPLAIN: Server gets stuck until timeout occurs while
+--echo #           executing SHOW INDEX and SHOW EXPLAIN in parallel
+--echo #
+CREATE TABLE t1(a INT, b INT, c INT, KEY(a), KEY(b), KEY(c));
+INSERT INTO t1 (a) VALUES (3),(1),(5),(1);
+
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+
+send SHOW INDEX FROM t1;
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+set debug_dbug='';
+
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-324: SHOW EXPLAIN: Plan produced by SHOW EXPLAIN for a query with TEMPTABLE view 
+--echo #           loses 'DERIVED' line on the way without saying that the plan was already deleted
+--echo #
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1;
+
+EXPLAIN SELECT a + 1 FROM v1;
+
+set debug_dbug='d,show_explain_probe_join_tab_preread';
+set @show_explain_probe_select_id=1;
+
+send
+  SELECT a + 1 FROM v1;
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con1;
+reap;
+set debug_dbug='';
+
+DROP VIEW v1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # MDEV-323: SHOW EXPLAIN: Plan produced by SHOW EXPLAIN loses 
+--echo #            'UNION RESULT' line on the way without saying that the plan was already deleted
+--echo #
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (4),(6);
+
+EXPLAIN 
+SELECT a FROM t1 WHERE a IN ( SELECT 1+SLEEP(0.01) UNION SELECT 2 );
+
+set debug_dbug='d,show_explain_probe_union_read';
+send
+SELECT a FROM t1 WHERE a IN ( SELECT 1+SLEEP(0.01) UNION SELECT 2 );
+
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+
+connection con1;
+reap;
+
+set debug_dbug='';
+
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-327: SHOW EXPLAIN: Different select_type in plans produced by SHOW EXPLAIN 
+--echo #           and standard EXPLAIN: 'SUBQUERY' vs 'DEPENDENT SUBQUERY'
+--echo #
+CREATE TABLE t1 (a INT) ENGINE=Aria;
+INSERT INTO t1 VALUES
+(4),(6),(3),(5),(3),(246),(2),(9),(3),(8),
+(1),(8),(8),(5),(7),(5),(1),(6),(2),(9);
+
+CREATE TABLE t2 (b INT) ENGINE=Aria;
+INSERT INTO t2 VALUES
+(1),(7),(4),(7),(0),(2),(9),(4),(0),(9),
+(1),(3),(8),(8),(18),(84),(6),(3),(6),(6);
+
+EXPLAIN
+SELECT * FROM t1, ( SELECT * FROM t2 ) AS alias
+WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b );
+
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_join_exec_start';
+--send
+SELECT * FROM t1, ( SELECT * FROM t2 ) AS alias
+WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b );
+
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+
+connection con1;
+reap;
+
+set debug_dbug='';
+
+DROP TABLE t1, t2;
+
+## TODO: Test this: have several SHOW EXPLAIN requests be queued up for a
+##       thread and served together.
+
+## TODO: SHOW EXPLAIN while the primary query is running EXPLAIN EXTENDED/PARTITIONS
+##
+
+drop table t0;

=== modified file 'sql/CMakeLists.txt'
--- sql/CMakeLists.txt	2012-05-22 09:04:32 +0000
+++ sql/CMakeLists.txt	2012-06-12 19:04:11 +0000
@@ -85,6 +85,7 @@ SET (SQL_SOURCE
                gcalc_slicescan.cc gcalc_tools.cc
 			   threadpool_common.cc 
 			   ../sql-common/mysql_async.c
+               my_apc.cc my_apc.h
                ${GEN_SOURCES}
                ${MYSYS_LIBWRAP_SOURCE}
 			   )

=== modified file 'sql/filesort.cc'
--- sql/filesort.cc	2012-05-21 13:30:25 +0000
+++ sql/filesort.cc	2012-06-12 19:04:11 +0000
@@ -502,7 +502,6 @@ static ha_rows find_all_keys(SORTPARAM *
   my_off_t record;
   TABLE *sort_form;
   THD *thd= current_thd;
-  volatile killed_state *killed= &thd->killed;
   handler *file;
   MY_BITMAP *save_read_set, *save_write_set, *save_vcol_set;
   uchar *next_sort_key= sort_keys_buf;
@@ -523,6 +522,11 @@ static ha_rows find_all_keys(SORTPARAM *
   if (flag)
     ref_pos= &file->ref[0];
   next_pos=ref_pos;
+
+  DBUG_EXECUTE_IF("show_explain_in_find_all_keys", 
+                  dbug_serve_apcs(thd, 1);
+                 );
+
   if (!quick_select)
   {
     next_pos=(uchar*) 0;			/* Find records in sequence */
@@ -586,7 +590,7 @@ static ha_rows find_all_keys(SORTPARAM *
 	break;
     }
 
-    if (*killed)
+    if (thd->check_killed())
     {
       DBUG_PRINT("info",("Sort killed by user"));
       if (!quick_select)
@@ -1231,18 +1235,18 @@ int merge_buffers(SORTPARAM *param, IO_C
   void *first_cmp_arg;
   element_count dupl_count= 0;
   uchar *src;
-  killed_state not_killable;
   uchar *unique_buff= param->unique_buff;
-  volatile killed_state *killed= &current_thd->killed;
+  const bool killable= !param->not_killable;
+  THD* const thd=current_thd;
   DBUG_ENTER("merge_buffers");
 
-  status_var_increment(current_thd->status_var.filesort_merge_passes);
-  current_thd->query_plan_fsort_passes++;
-  if (param->not_killable)
+  status_var_increment(thd->status_var.filesort_merge_passes);
+  thd->query_plan_fsort_passes++;
+  /*if (param->not_killable)
   {
     killed= &not_killable;
     not_killable= NOT_KILLED;
-  }
+  }*/
 
   error=0;
   rec_length= param->rec_length;
@@ -1320,7 +1324,7 @@ int merge_buffers(SORTPARAM *param, IO_C
 
   while (queue.elements > 1)
   {
-    if (*killed)
+    if (killable && thd->check_killed())
     {
       error= 1; goto err;                        /* purecov: inspected */
     }

=== modified file 'sql/item_func.cc'
--- sql/item_func.cc	2012-03-26 10:33:49 +0000
+++ sql/item_func.cc	2012-06-12 19:04:11 +0000
@@ -4298,7 +4298,7 @@ longlong Item_func_sleep::val_int()
 
 #define extra_size sizeof(double)
 
-static user_var_entry *get_variable(HASH *hash, LEX_STRING &name,
+user_var_entry *get_variable(HASH *hash, LEX_STRING &name,
 				    bool create_if_not_exists)
 {
   user_var_entry *entry;

=== modified file 'sql/item_subselect.cc'
--- sql/item_subselect.cc	2012-06-04 15:26:11 +0000
+++ sql/item_subselect.cc	2012-06-19 09:27:55 +0000
@@ -1813,7 +1813,7 @@ bool Item_allany_subselect::is_maxmin_ap
     WHERE condition.
   */
   return (abort_on_null || (upper_item && upper_item->is_top_level_item())) &&
-      !join->select_lex->master_unit()->uncacheable && !func->eqne_op();
+      !(join->select_lex->master_unit()->uncacheable & ~UNCACHEABLE_EXPLAIN) && !func->eqne_op();
 }
 
 

=== added file 'sql/my_apc.cc'
--- sql/my_apc.cc	1970-01-01 00:00:00 +0000
+++ sql/my_apc.cc	2012-06-12 19:04:11 +0000
@@ -0,0 +1,377 @@
+/*
+  TODO: MP AB Copyright
+*/
+
+
+#ifdef MY_APC_STANDALONE
+
+#include <my_global.h>
+#include <my_pthread.h>
+#include <my_sys.h>
+
+#include "my_apc.h"
+
+#else
+
+#include "sql_priv.h"
+#include "sql_class.h"
+
+#endif
+
+
+/*
+  Standalone testing:
+    g++ -c -DMY_APC_STANDALONE -g -I.. -I../include -o my_apc.o my_apc.cc
+    g++ -L../mysys -L../dbug -L../strings my_apc.o -lmysys -ldbug -lmystrings -lpthread -lrt
+*/
+
+
+/* 
+  Initialize the target. 
+   
+  @note 
+  Initialization must be done prior to enabling/disabling the target, or making
+  any call requests to it.
+  Initial state after initialization is 'disabled'.
+*/
+void Apc_target::init(mysql_mutex_t *target_mutex)
+{
+  DBUG_ASSERT(!enabled);
+  LOCK_thd_data_ptr= target_mutex;
+#ifndef DBUG_OFF
+  n_calls_processed= 0;
+#endif
+}
+
+
+/* 
+  Destroy the target. The target must be disabled when this call is made.
+*/
+void Apc_target::destroy()
+{
+  DBUG_ASSERT(!enabled);
+}
+
+
+/* 
+  Enter ther state where the target is available for serving APC requests
+*/
+void Apc_target::enable()
+{
+  /* Ok to do without getting/releasing the mutex: */
+  enabled++;
+}
+
+
+/* 
+  Make the target unavailable for serving APC requests. 
+  
+  @note
+    This call will serve all requests that were already enqueued
+*/
+
+void Apc_target::disable()
+{
+  bool process= FALSE;
+  mysql_mutex_lock(LOCK_thd_data_ptr);
+  if (!(--enabled))
+    process= TRUE;
+  mysql_mutex_unlock(LOCK_thd_data_ptr);
+  if (process)
+    process_apc_requests();
+}
+
+
+/* [internal] Put request qe into the request list */
+
+void Apc_target::enqueue_request(Call_request *qe)
+{
+  //call_queue_size++;
+  if (apc_calls)
+  {
+    Call_request *after= apc_calls->prev;
+    qe->next= apc_calls;
+    apc_calls->prev= qe;
+     
+    qe->prev= after;
+    after->next= qe;
+  }
+  else
+  {
+    apc_calls= qe;
+    qe->next= qe->prev= qe;
+  }
+}
+
+
+/* 
+  [internal] Remove request qe from the request queue. 
+  
+  The request is not necessarily first in the queue.
+*/
+
+void Apc_target::dequeue_request(Call_request *qe)
+{
+  //call_queue_size--;
+  if (apc_calls == qe)
+  {
+    if ((apc_calls= apc_calls->next) == qe)
+    {
+      //DBUG_ASSERT(!call_queue_size);
+      apc_calls= NULL;
+    }
+  }
+
+  qe->prev->next= qe->next;
+  qe->next->prev= qe->prev;
+}
+
+
+/*
+  Make an APC (Async Procedure Call) to another thread. 
+
+  - The caller is responsible for making sure he's not calling to the same
+    thread.
+
+  - The caller should have locked target_thread_mutex.
+
+
+  psergey-todo: Should waits here be KILLable? (it seems one needs 
+  to use thd->enter_cond() calls to be killable)
+*/
+
+bool Apc_target::make_apc_call(apc_func_t func, void *func_arg, 
+                               int timeout_sec, bool *timed_out)
+{
+  bool res= TRUE;
+  *timed_out= FALSE;
+
+  if (enabled)
+  {
+    /* Create and post the request */
+    Call_request apc_request;
+    apc_request.func= func;
+    apc_request.func_arg= func_arg;
+    apc_request.processed= FALSE;
+    mysql_cond_init(0 /* do not track in PS */, &apc_request.COND_request, NULL);
+    enqueue_request(&apc_request);
+    apc_request.what="enqueued by make_apc_call";
+ 
+    struct timespec abstime;
+    const int timeout= timeout_sec;
+    set_timespec(abstime, timeout);
+
+    int wait_res= 0;
+    /* todo: how about processing other errors here? */
+    while (!apc_request.processed && (wait_res != ETIMEDOUT))
+    {
+      /* We own LOCK_thd_data_ptr */
+      wait_res= mysql_cond_timedwait(&apc_request.COND_request,
+                                     LOCK_thd_data_ptr, &abstime);
+                                      // &apc_request.LOCK_request, &abstime);
+    }
+
+    if (!apc_request.processed)
+    {
+      /* 
+        The wait has timed out. Remove the request from the queue (ok to do
+        because we own LOCK_thd_data_ptr.
+      */
+      apc_request.processed= TRUE;
+      dequeue_request(&apc_request);
+      *timed_out= TRUE;
+      res= TRUE;
+    }
+    else
+    {
+      /* Request was successfully executed and dequeued by the target thread */
+      res= FALSE;
+    }
+    mysql_mutex_unlock(LOCK_thd_data_ptr);
+
+    /* Destroy all APC request data */
+    mysql_cond_destroy(&apc_request.COND_request);
+  }
+  else
+  {
+    mysql_mutex_unlock(LOCK_thd_data_ptr);
+  }
+  return res;
+}
+
+
+/*
+  Process all APC requests.
+  This should be called periodically by the APC target thread.
+*/
+
+void Apc_target::process_apc_requests()
+{
+  if (!get_first_in_queue())
+    return;
+
+  while (1)
+  {
+    Call_request *request;
+ 
+    mysql_mutex_lock(LOCK_thd_data_ptr);
+    if (!(request= get_first_in_queue()))
+    {
+      /* No requests in the queue */
+      mysql_mutex_unlock(LOCK_thd_data_ptr);
+      break;
+    }
+
+    /* 
+      Remove the request from the queue (we're holding queue lock so we can be 
+      sure that request owner won't try to remove it)
+    */
+    request->what="dequeued by process_apc_requests";
+    dequeue_request(request);
+    request->processed= TRUE;
+
+    request->func(request->func_arg);
+    request->what="func called by process_apc_requests";
+
+#ifndef DBUG_OFF
+    n_calls_processed++;
+#endif
+    mysql_cond_signal(&request->COND_request);
+    mysql_mutex_unlock(LOCK_thd_data_ptr);
+  }
+}
+
+/*****************************************************************************
+ * Testing 
+ *****************************************************************************/
+#ifdef MY_APC_STANDALONE
+
+volatile bool started= FALSE;
+volatile bool service_should_exit= FALSE;
+volatile bool requestors_should_exit=FALSE;
+
+volatile int apcs_served= 0;
+volatile int apcs_missed=0;
+volatile int apcs_timed_out=0;
+
+Apc_target apc_target;
+mysql_mutex_t target_mutex;
+
+int int_rand(int size)
+{
+  return round (((double)rand() / RAND_MAX) * size);
+}
+
+/* An APC-serving thread */
+void *test_apc_service_thread(void *ptr)
+{
+  my_thread_init();
+  mysql_mutex_init(0, &target_mutex, MY_MUTEX_INIT_FAST);
+  apc_target.init(&target_mutex);
+  apc_target.enable();
+  started= TRUE;
+  fprintf(stderr, "# test_apc_service_thread started\n");
+  while (!service_should_exit)
+  {
+    //apc_target.disable();
+    usleep(10000);
+    //apc_target.enable();
+    for (int i = 0; i < 10 && !service_should_exit; i++)
+    {
+      apc_target.process_apc_requests();
+      usleep(int_rand(30));
+    }
+  }
+  apc_target.disable();
+  apc_target.destroy();
+  my_thread_end();
+  pthread_exit(0);
+}
+
+class Apc_order
+{
+public:
+  int value;   // The value 
+  int *where_to;  // Where to write it
+  Apc_order(int a, int *b) : value(a), where_to(b) {}
+};
+
+void test_apc_func(void *arg)
+{
+  Apc_order *order=(Apc_order*)arg;
+  usleep(int_rand(1000));
+  *(order->where_to) = order->value;
+  __sync_fetch_and_add(&apcs_served, 1);
+}
+
+void *test_apc_requestor_thread(void *ptr)
+{
+  my_thread_init();
+  fprintf(stderr, "# test_apc_requestor_thread started\n");
+  while (!requestors_should_exit)
+  {
+    int dst_value= 0;
+    int src_value= int_rand(4*1000*100);
+    /* Create APC to do  dst_value= src_value */
+    Apc_order apc_order(src_value, &dst_value);
+    bool timed_out;
+
+    bool res= apc_target.make_apc_call(test_apc_func, (void*)&apc_order, 60, &timed_out);
+    if (res)
+    {
+      if (timed_out)
+        __sync_fetch_and_add(&apcs_timed_out, 1);
+      else
+        __sync_fetch_and_add(&apcs_missed, 1);
+
+      if (dst_value != 0)
+        fprintf(stderr, "APC was done even though return value says it wasnt!\n");
+    }
+    else
+    {
+      if (dst_value != src_value)
+        fprintf(stderr, "APC was not done even though return value says it was!\n");
+    }
+    //usleep(300);
+  }
+  fprintf(stderr, "# test_apc_requestor_thread exiting\n");
+  my_thread_end();
+}
+
+const int N_THREADS=23;
+int main(int args, char **argv)
+{
+  pthread_t service_thr;
+  pthread_t request_thr[N_THREADS];
+  int i, j;
+  my_thread_global_init();
+
+  pthread_create(&service_thr, NULL, test_apc_service_thread, (void*)NULL);
+  while (!started)
+    usleep(1000);
+  for (i = 0; i < N_THREADS; i++)
+    pthread_create(&request_thr[i], NULL, test_apc_requestor_thread, (void*)NULL);
+  
+  for (i = 0; i < 15; i++)
+  {
+    usleep(500*1000);
+    fprintf(stderr, "# %d APCs served %d missed\n", apcs_served, apcs_missed);
+  }
+  fprintf(stderr, "# Shutting down requestors\n");
+  requestors_should_exit= TRUE;
+  for (i = 0; i < N_THREADS; i++)
+    pthread_join(request_thr[i], NULL);
+  
+  fprintf(stderr, "# Shutting down service\n");
+  service_should_exit= TRUE;
+  pthread_join(service_thr, NULL);
+  fprintf(stderr, "# Done.\n");
+  my_thread_end();
+  my_thread_global_end();
+  return 0;
+}
+
+#endif // MY_APC_STANDALONE
+
+
+

=== added file 'sql/my_apc.h'
--- sql/my_apc.h	1970-01-01 00:00:00 +0000
+++ sql/my_apc.h	2012-06-19 13:59:00 +0000
@@ -0,0 +1,108 @@
+/*
+  TODO: MP AB Copyright
+*/
+
+/*
+  Interface
+  ~~~~~~~~~
+   (
+    - This is an APC request queue
+    - We assume there is a particular owner thread which periodically calls
+      process_apc_requests() to serve the call requests.
+    - Other threads can post call requests, and block until they are exectued.
+  )
+
+  Implementation
+  ~~~~~~~~~~~~~~
+  - The target has a mutex-guarded request queue.
+
+  - After the request has been put into queue, the requestor waits for request
+    to be satisfied. The worker satisifes the request and signals the
+    requestor.
+*/
+
+/*
+  Target for asynchronous procedue calls (APCs).
+*/
+class Apc_target
+{
+  mysql_mutex_t *LOCK_thd_data_ptr;
+public:
+  Apc_target() : enabled(0), apc_calls(NULL) /*, call_queue_size(0)*/ {} 
+  ~Apc_target() { DBUG_ASSERT(!enabled && !apc_calls);}
+
+  void init(mysql_mutex_t *target_mutex);
+  void destroy();
+  void enable();
+  void disable();
+  
+  void process_apc_requests();
+
+  typedef void (*apc_func_t)(void *arg);
+  
+  /*
+    Make an APC call: schedule it for execution and wait until the target
+    thread has executed it. This function must not be called from a thread
+    that's different from the target thread.
+
+    @retval FALSE - Ok, the call has been made
+    @retval TRUE  - Call wasnt made (either the target is in disabled state or
+                    timeout occured)
+  */
+  bool make_apc_call(apc_func_t func, void *func_arg, 
+                     int timeout_sec, bool *timed_out);
+
+#ifndef DBUG_OFF
+  int n_calls_processed; /* Number of calls served by this target */
+#endif
+private:
+  class Call_request;
+
+  /* 
+    Non-zero value means we're enabled. It's an int, not bool, because one can
+    call enable() N times (and then needs to call disable() N times before the 
+    target is really disabled)
+  */
+  int enabled;
+
+  /* 
+    Circular, double-linked list of all enqueued call requests. 
+    We use this structure, because we 
+     - process requests sequentially (i.e. they are removed from the front)
+     - a thread that has posted a request may time out (or be KILLed) and 
+       cancel the request, which means we need a fast request-removal
+       operation.
+  */
+  Call_request *apc_calls;
+ 
+  class Call_request
+  {
+  public:
+    apc_func_t func; /* Function to call */
+    void *func_arg;  /* Argument to pass it */
+
+    /* The caller will actually wait for "processed==TRUE" */
+    bool processed;
+
+    /* Condition that will be signalled when the request has been served */
+    mysql_cond_t COND_request;
+    
+    /* Double linked-list linkage */
+    Call_request *next;
+    Call_request *prev;
+    
+    const char *what; /* (debug) state of the request */
+  };
+
+  void enqueue_request(Call_request *qe);
+  void dequeue_request(Call_request *qe);
+
+  /* return the first call request in queue, or NULL if there are none enqueued */
+  Call_request *get_first_in_queue()
+  {
+    return apc_calls;
+  }
+};
+
+///////////////////////////////////////////////////////////////////////
+

=== modified file 'sql/mysqld.cc'
--- sql/mysqld.cc	2012-06-08 09:18:56 +0000
+++ sql/mysqld.cc	2012-06-12 19:04:11 +0000
@@ -3339,6 +3339,7 @@ SHOW_VAR com_status_vars[]= {
   {"show_engine_status",   (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_ENGINE_STATUS]), SHOW_LONG_STATUS},
   {"show_events",          (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_EVENTS]), SHOW_LONG_STATUS},
   {"show_errors",          (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_ERRORS]), SHOW_LONG_STATUS},
+  {"show_explain",         (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_EXPLAIN]), SHOW_LONG_STATUS},
   {"show_fields",          (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_FIELDS]), SHOW_LONG_STATUS},
 #ifndef DBUG_OFF
   {"show_function_code",   (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_FUNC_CODE]), SHOW_LONG_STATUS},

=== modified file 'sql/opt_subselect.cc'
--- sql/opt_subselect.cc	2012-06-06 13:19:48 +0000
+++ sql/opt_subselect.cc	2012-06-12 19:04:11 +0000
@@ -1657,6 +1657,7 @@ static bool convert_subq_to_sj(JOIN *par
       parent_lex->ftfunc_list->push_front(ifm);
   }
 
+  parent_lex->have_merged_subqueries= TRUE;
   DBUG_RETURN(FALSE);
 }
 
@@ -1767,6 +1768,8 @@ static bool convert_subq_to_jtbm(JOIN *p
   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)

=== modified file 'sql/protocol.h'
--- sql/protocol.h	2012-01-13 14:50:02 +0000
+++ sql/protocol.h	2012-06-12 19:04:11 +0000
@@ -35,6 +35,7 @@ class Protocol
 protected:
   THD	 *thd;
   String *packet;
+  /* Used by net_store_data() for charset conversions */
   String *convert;
   uint field_pos;
 #ifndef DBUG_OFF
@@ -49,6 +50,10 @@ protected:
   MYSQL_FIELD *next_mysql_field;
   MEM_ROOT *alloc;
 #endif
+  /* 
+    The following two are low-level functions that are invoked from
+    higher-level store_xxx() funcs.  The data is stored into this->packet.
+  */
   bool net_store_data(const uchar *from, size_t length,
                       CHARSET_INFO *fromcs, CHARSET_INFO *tocs);
   bool store_string_aux(const char *from, size_t length,
@@ -73,6 +78,20 @@ public:
   virtual bool send_result_set_metadata(List<Item> *list, uint flags);
   bool send_result_set_row(List<Item> *row_items);
 
+  void get_packet(const char **start, size_t *length) 
+  {
+    *start= packet->ptr();
+    *length= packet->length(); 
+  }
+  void set_packet(const char *start, size_t len)
+  {
+    packet->length(0);
+    packet->append(start, len);
+#ifndef DBUG_OFF
+  field_pos= field_count - 1;
+#endif
+  }
+
   bool store(I_List<i_string> *str_list);
   bool store(const char *from, CHARSET_INFO *cs);
   String *storage_packet() { return packet; }

=== modified file 'sql/sp_head.cc'
--- sql/sp_head.cc	2012-05-21 18:54:41 +0000
+++ sql/sp_head.cc	2012-06-12 19:04:11 +0000
@@ -218,6 +218,7 @@ sp_get_flags_for_command(LEX *lex)
   case SQLCOM_SHOW_CREATE_TRIGGER:
   case SQLCOM_SHOW_DATABASES:
   case SQLCOM_SHOW_ERRORS:
+  case SQLCOM_SHOW_EXPLAIN:
   case SQLCOM_SHOW_FIELDS:
   case SQLCOM_SHOW_FUNC_CODE:
   case SQLCOM_SHOW_GRANTS:

=== modified file 'sql/sql_class.cc'
--- sql/sql_class.cc	2012-05-21 13:30:25 +0000
+++ sql/sql_class.cc	2012-06-19 14:02:19 +0000
@@ -1196,6 +1196,7 @@ void THD::init(void)
   /* Initialize the Debug Sync Facility. See debug_sync.cc. */
   debug_sync_init_thread(this);
 #endif /* defined(ENABLED_DEBUG_SYNC) */
+  apc_target.init(&LOCK_thd_data);
 }
 
  
@@ -1361,6 +1362,7 @@ void THD::cleanup(void)
     ull= NULL;
   }
 
+  apc_target.destroy();
   cleanup_done=1;
   DBUG_VOID_RETURN;
 }
@@ -2006,6 +2008,20 @@ CHANGED_TABLE_LIST* THD::changed_table_d
 int THD::send_explain_fields(select_result *result)
 {
   List<Item> field_list;
+  make_explain_field_list(field_list);
+  return (result->send_result_set_metadata(field_list,
+                                           Protocol::SEND_NUM_ROWS | 
+                                           Protocol::SEND_EOF));
+}
+
+
+/*
+  Populate the provided field_list with EXPLAIN output columns.
+  this->lex->describe has the EXPLAIN flags
+*/
+
+void THD::make_explain_field_list(List<Item> &field_list)
+{
   Item *item;
   CHARSET_INFO *cs= system_charset_info;
   field_list.push_back(item= new Item_return_int("id",3, MYSQL_TYPE_LONGLONG));
@@ -2044,10 +2060,9 @@ int THD::send_explain_fields(select_resu
   }
   item->maybe_null= 1;
   field_list.push_back(new Item_empty_string("Extra", 255, cs));
-  return (result->send_result_set_metadata(field_list,
-                                           Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF));
 }
 
+
 #ifdef SIGNAL_WITH_VIO_CLOSE
 void THD::close_active_vio()
 {
@@ -2151,6 +2166,21 @@ void THD::rollback_item_tree_changes()
 }
 
 
+/*
+  Check if the thread has been killed, and also process "APC requests"
+
+  @retval true  The thread is killed, execution should be interrupted
+  @retval false Not killed, continue execution
+*/
+
+bool THD::check_killed()
+{
+  if (killed)
+    return TRUE;
+  apc_target.process_apc_requests(); 
+  return FALSE;
+}
+
 /*****************************************************************************
 ** Functions to provide a interface to select results
 *****************************************************************************/
@@ -2279,6 +2309,90 @@ int select_send::send_data(List<Item> &i
   DBUG_RETURN(0);
 }
 
+//////////////////////////////////////////////////////////////////////////////
+
+/*
+  Save the data being sent in our internal buffer.
+*/
+
+int select_result_explain_buffer::send_data(List<Item> &items)
+{
+  List_iterator_fast<Item> li(items);
+  char buff[MAX_FIELD_WIDTH];
+  String buffer(buff, sizeof(buff), &my_charset_bin);
+  DBUG_ENTER("select_send::send_data");
+
+  protocol->prepare_for_resend();
+  Item *item;
+  while ((item=li++))
+  {
+    if (item->send(protocol, &buffer))
+    {
+      protocol->free();				// Free used buffer
+      my_message(ER_OUT_OF_RESOURCES, ER(ER_OUT_OF_RESOURCES), MYF(0));
+      break;
+    }
+    /*
+      Reset buffer to its original state, as it may have been altered in
+      Item::send().
+    */
+    buffer.set(buff, sizeof(buff), &my_charset_bin);
+  }
+
+  if (thd->is_error())
+  {
+    protocol->remove_last_row();
+    DBUG_RETURN(1);
+  }
+  /* 
+    Instead of calling protocol->write(), steal the packed and put it to our
+    buffer 
+  */
+  const char *packet_data;
+  size_t len;
+  protocol->get_packet(&packet_data, &len);
+
+  String *s= new (thd->mem_root) String;
+  s->append(packet_data, len);
+  data_rows.push_back(s);
+  protocol->remove_last_row(); // <-- this does nothing. Do we need it?
+                               // prepare_for_resend() will wipe out the packet
+  DBUG_RETURN(0);
+}
+
+
+/* Write the saved resultset to the client (via this->protocol) and free it. */
+
+void select_result_explain_buffer::flush_data()
+{
+  List_iterator<String> it(data_rows);
+  String *str;
+  while ((str= it++))
+  {
+    protocol->set_packet(str->ptr(), str->length());
+    protocol->write();
+    delete str;
+  }
+  data_rows.empty();
+}
+
+
+/* Free the accumulated resultset */
+
+void select_result_explain_buffer::discard_data()
+{
+  List_iterator<String> it(data_rows);
+  String *str;
+  while ((str= it++))
+  {
+    delete str;
+  }
+  data_rows.empty();
+}
+
+//////////////////////////////////////////////////////////////////////////////
+
+
 bool select_send::send_eof()
 {
   /* 
@@ -3172,6 +3286,10 @@ void THD::end_statement()
 }
 
 
+/*
+  Start using arena specified by @set. Current arena data will be saved to
+  *backup.
+*/
 void THD::set_n_backup_active_arena(Query_arena *set, Query_arena *backup)
 {
   DBUG_ENTER("THD::set_n_backup_active_arena");
@@ -3186,6 +3304,12 @@ void THD::set_n_backup_active_arena(Quer
 }
 
 
+/*
+  Stop using the temporary arena, and start again using the arena that is 
+  specified in *backup.
+  The temporary arena is returned back into *set.
+*/
+
 void THD::restore_active_arena(Query_arena *set, Query_arena *backup)
 {
   DBUG_ENTER("THD::restore_active_arena");
@@ -3198,6 +3322,42 @@ void THD::restore_active_arena(Query_are
   DBUG_VOID_RETURN;
 }
 
+
+/*
+  Produce EXPLAIN data.
+
+  This function is APC-scheduled to be run in the context of the thread that
+  we're producing EXPLAIN for.
+*/
+
+void Show_explain_request::get_explain_data(void *arg)
+{
+  Show_explain_request *req= (Show_explain_request*)arg;
+  //TODO: change mem_root to point to request_thd->mem_root.
+  //      Actually, change the ARENA, because we're going to allocate items!
+  Query_arena backup_arena;
+  THD *target_thd= req->target_thd;
+  bool printed_anything= FALSE;
+
+  target_thd->set_n_backup_active_arena((Query_arena*)req->request_thd,
+                                        &backup_arena);
+  
+  req->query_str.copy(target_thd->query(), 
+                      target_thd->query_length(),
+                      &my_charset_bin);
+
+  if (target_thd->lex->unit.print_explain(req->explain_buf, 0 /* explain flags*/,
+                                          &printed_anything))
+    req->failed_to_produce= TRUE;
+
+  if (!printed_anything)
+    req->failed_to_produce= TRUE;
+
+  target_thd->restore_active_arena((Query_arena*)req->request_thd, 
+                                   &backup_arena);
+}
+
+
 Statement::~Statement()
 {
 }

=== modified file 'sql/sql_class.h'
--- sql/sql_class.h	2012-05-21 13:30:25 +0000
+++ sql/sql_class.h	2012-06-19 14:08:12 +0000
@@ -43,7 +43,7 @@
 #include "violite.h"              /* vio_is_connected */
 #include "thr_lock.h"             /* thr_lock_type, THR_LOCK_DATA,
                                      THR_LOCK_INFO */
-
+#include "my_apc.h"
 
 class Reprepare_observer;
 class Relay_log_info;
@@ -1520,6 +1520,42 @@ private:
 
 extern "C" void my_message_sql(uint error, const char *str, myf MyFlags);
 
+class select_result_explain_buffer;
+
+
+/*
+  SHOW EXPLAIN request object. 
+  
+  The thread that runs SHOW EXPLAIN statement creates a Show_explain_request
+  object R, and then schedules APC call of
+  Show_explain_request::get_explain_data((void*)&R).
+
+*/
+
+class Show_explain_request
+{
+public:
+  THD *target_thd;  /* thd that we're running SHOW EXPLAIN for */
+  THD *request_thd; /* thd that run SHOW EXPLAIN command */
+  
+  /* If true, there was some error when producing EXPLAIN output. */
+  bool failed_to_produce;
+   
+  /* SHOW EXPLAIN will be stored here */
+  select_result_explain_buffer *explain_buf;
+  
+  /* Query that we've got SHOW EXPLAIN for */
+  String query_str;
+
+  static void get_explain_data(void *arg);
+};
+
+class THD;
+void mysqld_show_explain(THD *thd, ulong thread_id);
+#ifndef DBUG_OFF
+void dbug_serve_apcs(THD *thd, int n_calls);
+#endif 
+
 /**
   @class THD
   For each client connection we create a separate thread with THD serving as
@@ -2185,6 +2221,8 @@ public:
   */
   killed_state volatile killed;
 
+  bool check_killed();
+
   /* scramble - random string sent to client on handshake */
   char	     scramble[SCRAMBLE_LENGTH+1];
 
@@ -2383,10 +2421,20 @@ public:
   void close_active_vio();
 #endif
   void awake(killed_state state_to_set);
-
+ 
   /** Disconnect the associated communication endpoint. */
   void disconnect();
 
+
+  /*
+    Allows this thread to serve as a target for others to schedule Async 
+    Procedure Calls on.
+
+    It's possible to schedule arbitrary C++ function calls. Currently, only
+    Show_explain_request uses this.
+  */
+  Apc_target apc_target;
+
 #ifndef MYSQL_CLIENT
   enum enum_binlog_query_type {
     /* The query can be logged in row format or in statement format. */
@@ -2580,7 +2628,7 @@ public:
   void add_changed_table(const char *key, long key_length);
   CHANGED_TABLE_LIST * changed_table_dup(const char *key, long key_length);
   int send_explain_fields(select_result *result);
-
+  void make_explain_field_list(List<Item> &field_list);
   /**
     Clear the current error, if any.
     We do not clear is_fatal_error or is_fatal_sub_stmt_error since we
@@ -3190,10 +3238,42 @@ public:
 
 class JOIN;
 
-class select_result :public Sql_alloc {
+/* Pure interface for sending tabular data */
+class select_result_sink: public Sql_alloc
+{
+public:
+  /*
+    send_data returns 0 on ok, 1 on error and -1 if data was ignored, for
+    example for a duplicate row entry written to a temp table.
+  */
+  virtual int send_data(List<Item> &items)=0;
+  virtual ~select_result_sink() {};
+};
+
+
+/*
+  Interface for sending tabular data, together with some other stuff:
+
+  - Primary purpose seems to be seding typed tabular data:
+     = the DDL is sent with send_fields()
+     = the rows are sent with send_data()
+  Besides that,
+  - there seems to be an assumption that the sent data is a result of 
+    SELECT_LEX_UNIT *unit,
+  - nest_level is used by SQL parser
+*/
+
+class select_result :public select_result_sink 
+{
 protected:
   THD *thd;
+  /* 
+    All descendant classes have their send_data() skip the first 
+    unit->offset_limit_cnt rows sent.  Select_materialize
+    also uses unit->get_unit_column_types().
+  */
   SELECT_LEX_UNIT *unit;
+  /* Something used only by the parser: */
 public:
   select_result();
   virtual ~select_result() {};
@@ -3211,11 +3291,6 @@ public:
   virtual uint field_count(List<Item> &fields) const
   { return fields.elements; }
   virtual bool send_result_set_metadata(List<Item> &list, uint flags)=0;
-  /*
-    send_data returns 0 on ok, 1 on error and -1 if data was ignored, for
-    example for a duplicate row entry written to a temp table.
-  */
-  virtual int send_data(List<Item> &items)=0;
   virtual bool initialize_tables (JOIN *join=0) { return 0; }
   virtual void send_error(uint errcode,const char *err);
   virtual bool send_eof()=0;
@@ -3243,6 +3318,37 @@ public:
 
 
 /*
+  A select result sink that collects the sent data and then can flush it to
+  network when requested.
+
+  This class is targeted at collecting EXPLAIN output:
+  - Unoptimized data storage (can't handle big datasets)
+  - Unlike select_result class, we don't assume that the sent data is an 
+    output of a SELECT_LEX_UNIT (and so we dont apply "LIMIT x,y" from the
+    unit)
+*/
+
+class select_result_explain_buffer : public select_result_sink
+{
+public:
+  THD *thd;
+  Protocol *protocol;
+  select_result_explain_buffer(){};
+
+  /* The following is called in the child thread: */
+  int send_data(List<Item> &items);
+
+  /* this will be called in the parent thread: */
+  void flush_data();
+
+  void discard_data();
+
+  List<String> data_rows;
+};
+
+
+
+/*
   Base class for select_result descendands which intercept and
   transform result set rows. As the rows are not sent to the client,
   sending of result set metadata should be suppressed as well.
@@ -3812,6 +3918,8 @@ class user_var_entry
   DTCollation collation;
 };
 
+user_var_entry *get_variable(HASH *hash, LEX_STRING &name,
+				    bool create_if_not_exists);
 
 /*
    Unique -- class for unique (removing of duplicates).

=== modified file 'sql/sql_join_cache.cc'
--- sql/sql_join_cache.cc	2012-03-24 17:21:22 +0000
+++ sql/sql_join_cache.cc	2012-06-12 19:04:11 +0000
@@ -2236,7 +2236,7 @@ enum_nested_loop_state JOIN_CACHE::join_
   
   while (!(error= join_tab_scan->next()))   
   {
-    if (join->thd->killed)
+    if (join->thd->check_killed())
     {
       /* The user has aborted the execution of the query */
       join->thd->send_kill_message();
@@ -2506,7 +2506,7 @@ enum_nested_loop_state JOIN_CACHE::join_
 
   for ( ; cnt; cnt--)
   {
-    if (join->thd->killed)
+    if (join->thd->check_killed())
     {
       /* The user has aborted the execution of the query */
       join->thd->send_kill_message();
@@ -3356,7 +3356,7 @@ int JOIN_TAB_SCAN::next()
     update_virtual_fields(thd, table);
   while (!err && select && (skip_rc= select->skip_record(thd)) <= 0)
   {
-    if (thd->killed || skip_rc < 0) 
+    if (thd->check_killed() || skip_rc < 0) 
       return 1;
     /* 
       Move to the next record if the last retrieved record does not

=== modified file 'sql/sql_lex.cc'
--- sql/sql_lex.cc	2012-05-21 13:30:25 +0000
+++ sql/sql_lex.cc	2012-06-12 19:04:11 +0000
@@ -1873,6 +1873,7 @@ void st_select_lex::init_query()
   nest_level= 0;
   link_next= 0;
   is_prep_leaf_list_saved= FALSE;
+  have_merged_subqueries= FALSE;
   bzero((char*) expr_cache_may_be_used, sizeof(expr_cache_may_be_used));
   m_non_agg_field_used= false;
   m_agg_func_used= false;
@@ -3441,7 +3442,7 @@ bool st_select_lex::optimize_unflattened
         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;
         }
@@ -3831,9 +3832,10 @@ void SELECT_LEX::update_used_tables()
 
 /**
   Set the EXPLAIN type for this subquery.
+    psergey-todo: comments about 
 */
 
-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())
@@ -3855,6 +3857,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);
@@ -3907,10 +3912,15 @@ void st_select_lex::set_explain_type()
       else
       {
         type= is_uncacheable ? "UNCACHEABLE UNION": "UNION";
+        if (this == master_unit()->fake_select_lex)
+          type= "UNION RESULT";
+
       }
     }
   }
-  options|= SELECT_DESCRIBE;
+
+  if (!on_the_fly)
+    options|= SELECT_DESCRIBE;
 }
 
 
@@ -4057,6 +4067,115 @@ bool st_select_lex::is_merged_child_of(s
 }
 
 
+int print_explain_message_line(select_result_sink *result, 
+                               SELECT_LEX *select_lex,
+                               bool on_the_fly,
+                               uint8 options,
+                               const char *message);
+
+
+int st_select_lex::print_explain(select_result_sink *output, 
+                                 uint8 explain_flags,
+                                 bool *printed_anything)
+{
+  int res;
+  if (join && join->have_query_plan == JOIN::QEP_AVAILABLE)
+  {
+    /*
+      There is a number of reasons join can be marked as degenerate, so all
+      three conditions below can happen simultaneously, or individually:
+    */
+    *printed_anything= TRUE;
+    if (!join->table_count || !join->tables_list || join->zero_result_cause)
+    {
+      /* It's a degenerate join */
+      const char *cause= join->zero_result_cause ? join-> zero_result_cause : 
+                                                   "No tables used";
+      res= join->print_explain(output, explain_flags, TRUE, FALSE, FALSE, 
+                               FALSE, cause);
+    }
+    else
+    {
+      res= join->print_explain(output, explain_flags, TRUE,
+                               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;
+
+    for (SELECT_LEX_UNIT *unit= join->select_lex->first_inner_unit();
+         unit;
+         unit= unit->next_unit())
+    {
+      /* 
+        Display subqueries only if they are not parts of eliminated WHERE/ON
+        clauses.
+      */
+      if (!(unit->item && unit->item->eliminated))
+      {
+        if ((res= unit->print_explain(output, explain_flags, printed_anything)))
+          goto err;
+      }
+    }
+  }
+  else
+  {
+    const char *msg;
+    if (!join)
+      DBUG_ASSERT(0); /* Seems not to be possible */
+
+    /* Not printing anything useful, don't touch *printed_anything here */
+    if (join->have_query_plan == JOIN::QEP_NOT_PRESENT_YET)
+      msg= "Not yet optimized";
+    else
+    {
+      DBUG_ASSERT(join->have_query_plan == JOIN::QEP_DELETED);
+      msg= "Query plan already deleted";
+    }
+    res= print_explain_message_line(output, this, TRUE /* on_the_fly */,
+                                    0, msg);
+  }
+err:
+  return res;
+}
+
+
+int st_select_lex_unit::print_explain(select_result_sink *output, 
+                                      uint8 explain_flags, bool *printed_anything)
+{
+  int res= 0;
+  SELECT_LEX *first= first_select();
+  
+  if (first && !first->next_select() && !first->join)
+  {
+    /*
+      If there is only one child, 'first', and it has join==NULL, emit "not in
+      EXPLAIN state" error.
+    */
+    const char *msg="Query plan already deleted";
+    res= print_explain_message_line(output, first, TRUE /* on_the_fly */,
+                                    0, msg);
+    return 0;
+  }
+
+  for (SELECT_LEX *sl= first; sl; sl= sl->next_select())
+  {
+    if ((res= sl->print_explain(output, explain_flags, printed_anything)))
+      break;
+  }
+
+  /* Note: fake_select_lex->join may be NULL or non-NULL at this point */
+  if (fake_select_lex)
+  {
+    res= print_fake_select_lex_join(output, TRUE /* on the fly */,
+                                    fake_select_lex, explain_flags);
+  }
+  return res;
+}
+
+
 /**
   A routine used by the parser to decide whether we are specifying a full
   partitioning or if only partitions to add or to split.

=== modified file 'sql/sql_lex.h'
--- sql/sql_lex.h	2012-05-21 13:30:25 +0000
+++ sql/sql_lex.h	2012-06-12 19:04:11 +0000
@@ -193,6 +193,7 @@ enum enum_sql_command {
   SQLCOM_SHOW_RELAYLOG_EVENTS, 
   SQLCOM_SHOW_USER_STATS, SQLCOM_SHOW_TABLE_STATS, SQLCOM_SHOW_INDEX_STATS,
   SQLCOM_SHOW_CLIENT_STATS,
+  SQLCOM_SHOW_EXPLAIN,
 
   /*
     When a command is added here, be sure it's also added in mysqld.cc
@@ -355,6 +356,8 @@ typedef uchar index_clause_map;
 #define INDEX_HINT_MASK_ALL (INDEX_HINT_MASK_JOIN | INDEX_HINT_MASK_GROUP | \
                              INDEX_HINT_MASK_ORDER)
 
+class select_result_sink;
+
 /* Single element of an USE/FORCE/IGNORE INDEX list specified as a SQL hint  */
 class Index_hint : public Sql_alloc
 {
@@ -715,6 +718,8 @@ public:
   friend int subselect_union_engine::exec();
 
   List<Item> *get_unit_column_types();
+  int print_explain(select_result_sink *output, uint8 explain_flags,
+                    bool *printed_anything);
 };
 
 typedef class st_select_lex_unit SELECT_LEX_UNIT;
@@ -775,6 +780,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;
@@ -1004,7 +1015,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(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);
@@ -1027,8 +1038,10 @@ public:
 
   bool save_leaf_tables(THD *thd);
   bool save_prep_leaf_tables(THD *thd);
-  bool is_merged_child_of(st_select_lex *ancestor);
 
+  bool is_merged_child_of(st_select_lex *ancestor);
+  int print_explain(select_result_sink *output, uint8 explain_flags, 
+                    bool *printed_anything);
   /*
     For MODE_ONLY_FULL_GROUP_BY we need to maintain two flags:
      - Non-aggregated fields are used in this select.

=== modified file 'sql/sql_parse.cc'
--- sql/sql_parse.cc	2012-05-21 18:54:41 +0000
+++ sql/sql_parse.cc	2012-06-12 19:04:11 +0000
@@ -335,6 +335,7 @@ void init_update_queries(void)
   sql_command_flags[SQLCOM_SHOW_ENGINE_STATUS]= CF_STATUS_COMMAND;
   sql_command_flags[SQLCOM_SHOW_ENGINE_MUTEX]= CF_STATUS_COMMAND;
   sql_command_flags[SQLCOM_SHOW_ENGINE_LOGS]= CF_STATUS_COMMAND;
+  sql_command_flags[SQLCOM_SHOW_EXPLAIN]= CF_STATUS_COMMAND;
   sql_command_flags[SQLCOM_SHOW_PROCESSLIST]= CF_STATUS_COMMAND;
   sql_command_flags[SQLCOM_SHOW_GRANTS]=      CF_STATUS_COMMAND;
   sql_command_flags[SQLCOM_SHOW_CREATE_DB]=   CF_STATUS_COMMAND;
@@ -3127,6 +3128,32 @@ end_with_restore_list:
                            thd->security_ctx->priv_user),
                           lex->verbose);
     break;
+  case SQLCOM_SHOW_EXPLAIN:
+  {
+    /* Same security as SHOW PROCESSLIST (TODO check this) */
+    if (!thd->security_ctx->priv_user[0] &&
+        check_global_access(thd,PROCESS_ACL))
+      break;
+
+    Item *it= (Item *)lex->value_list.head();
+
+    if (lex->table_or_sp_used())
+    {
+      my_error(ER_NOT_SUPPORTED_YET, MYF(0), "Usage of subqueries or stored "
+               "function calls as part of this statement");
+      break;
+    }
+
+    if ((!it->fixed && it->fix_fields(lex->thd, &it)) || it->check_cols(1))
+    {
+      my_message(ER_SET_CONSTANTS_ONLY, ER(ER_SET_CONSTANTS_ONLY),
+		 MYF(0));
+      goto error;
+    }
+
+    mysqld_show_explain(thd, (ulong)it->val_int());
+    break;
+  }
   case SQLCOM_SHOW_AUTHORS:
     res= mysqld_show_authors(thd);
     break;

=== modified file 'sql/sql_prepare.cc'
--- sql/sql_prepare.cc	2012-05-21 18:54:41 +0000
+++ sql/sql_prepare.cc	2012-06-12 19:04:11 +0000
@@ -2134,6 +2134,7 @@ static bool check_prepared_statement(Pre
       Note that we don't need to have cases in this list if they are
       marked with CF_STATUS_COMMAND in sql_command_flags
     */
+  case SQLCOM_SHOW_EXPLAIN:
   case SQLCOM_DROP_TABLE:
   case SQLCOM_RENAME_TABLE:
   case SQLCOM_ALTER_TABLE:

=== modified file 'sql/sql_priv.h'
--- sql/sql_priv.h	2012-06-04 15:26:11 +0000
+++ sql/sql_priv.h	2012-06-12 19:04:11 +0000
@@ -331,6 +331,7 @@ enum enum_yes_no_unknown
   External variables
 */
 
+
 /* sql_yacc.cc */
 #ifndef DBUG_OFF
 extern void turn_parser_debug_on();

=== modified file 'sql/sql_select.cc'
--- sql/sql_select.cc	2012-06-04 15:26:11 +0000
+++ sql/sql_select.cc	2012-06-12 19:05:07 +0000
@@ -272,6 +272,53 @@ Item_equal *find_item_equal(COND_EQUAL *
 JOIN_TAB *first_depth_first_tab(JOIN* join);
 JOIN_TAB *next_depth_first_tab(JOIN* join, JOIN_TAB* tab);
 
+#ifndef DBUG_OFF
+// psergey:
+void dbug_serve_apcs(THD *thd, int n_calls)
+{
+  // TODO how do we signal that we're SHOW-EXPLAIN-READY? 
+  const char *save_proc_info= thd->proc_info;
+  thd_proc_info(thd, "show_explain_trap");
+  
+  int n_apcs= thd->apc_target.n_calls_processed + n_calls;
+  while (thd->apc_target.n_calls_processed < n_apcs)
+  {
+    my_sleep(300);
+    if (thd->check_killed())
+      break;
+  }
+  thd_proc_info(thd, save_proc_info);
+}
+
+
+/*
+  Debugging: check if @name=value, comparing as integer
+
+  Intended usage:
+  
+  DBUG_EXECUTE_IF("show_explain_probe_2", 
+                     if (dbug_user_var_equals_int(thd, "select_id", select_id)) 
+                        dbug_serve_apcs(thd, 1);
+                 );
+
+*/
+
+bool dbug_user_var_equals_int(THD *thd, const char *name, int value)
+{
+  user_var_entry *var;
+  LEX_STRING varname= {(char*)name, strlen(name)};
+  if ((var= get_variable(&thd->user_vars, varname, FALSE)))
+  {
+    bool null_value;
+    longlong var_value= var->val_int(&null_value);
+    if (!null_value && var_value == value)
+      return TRUE;
+  }
+  return FALSE;
+}
+#endif 
+
+
 /**
   This handles SELECT with and without UNION.
 */
@@ -928,6 +975,13 @@ err:
 }
 
 
+int JOIN::optimize()
+{
+  int res= optimize_inner();
+  if (!res)
+    have_query_plan= QEP_AVAILABLE;
+  return res;
+}
 /**
   global select optimisation.
 
@@ -941,7 +995,7 @@ err:
 */
 
 int
-JOIN::optimize()
+JOIN::optimize_inner()
 {
   ulonglong select_opts_for_readinfo;
   uint no_jbuf_after;
@@ -2142,6 +2196,32 @@ JOIN::save_join_tab()
 }
 
 
+void JOIN::exec()
+{
+  /*
+    Enable SHOW EXPLAIN only if we're in the top-level query.
+  */
+  thd->apc_target.enable();
+  DBUG_EXECUTE_IF("show_explain_probe_join_exec_start", 
+                  if (dbug_user_var_equals_int(thd, 
+                                               "show_explain_probe_select_id", 
+                                               select_lex->select_number))
+                        dbug_serve_apcs(thd, 1);
+                 );
+
+  exec_inner();
+
+  DBUG_EXECUTE_IF("show_explain_probe_join_exec_end", 
+                  if (dbug_user_var_equals_int(thd, 
+                                               "show_explain_probe_select_id", 
+                                               select_lex->select_number))
+                        dbug_serve_apcs(thd, 1);
+                 );
+
+  thd->apc_target.disable();
+}
+
+
 /**
   Exec select.
 
@@ -2153,8 +2233,8 @@ JOIN::save_join_tab()
   @todo
     When can we have here thd->net.report_error not zero?
 */
-void
-JOIN::exec()
+
+void JOIN::exec_inner()
 {
   List<Item> *columns_list= &fields_list;
   int      tmp_error;
@@ -2458,6 +2538,10 @@ JOIN::exec()
       DBUG_PRINT("info",("Creating group table"));
       
       /* Free first data from old join */
+      
+      /*
+        psergey-todo: this is the place of pre-mature JOIN::free call.
+      */
       curr_join->join_free();
       if (curr_join->make_simple_join(this, curr_tmp_table))
 	DBUG_VOID_RETURN;
@@ -3695,7 +3779,7 @@ make_join_statistics(JOIN *join, List<TA
     goto error;
 
   /* Generate an execution plan from the found optimal join order. */
-  DBUG_RETURN(join->thd->killed || get_best_combination(join));
+  DBUG_RETURN(join->thd->check_killed() || get_best_combination(join));
 
 error:
   /*
@@ -6743,7 +6827,7 @@ best_extension_by_limited_search(JOIN   
   DBUG_ENTER("best_extension_by_limited_search");
 
   THD *thd= join->thd;
-  if (thd->killed)  // Abort
+  if (thd->check_killed())  // Abort
     DBUG_RETURN(TRUE);
 
   DBUG_EXECUTE("opt", print_plan(join, idx, read_time, record_count, idx,
@@ -6900,7 +6984,7 @@ find_best(JOIN *join,table_map rest_tabl
 {
   DBUG_ENTER("find_best");
   THD *thd= join->thd;
-  if (thd->killed)
+  if (thd->check_killed())
     DBUG_RETURN(TRUE);
   if (!rest_tables)
   {
@@ -7214,28 +7298,36 @@ prev_record_reads(POSITION *positions, u
   return found;
 }
 
+enum enum_exec_or_opt {WALK_OPTIMIZATION_TABS , WALK_EXECUTION_TABS};
 
 /*
   Enumerate join tabs in breadth-first fashion, including const tables.
 */
 
-JOIN_TAB *first_breadth_first_tab(JOIN *join)
+JOIN_TAB *first_breadth_first_tab(JOIN *join, enum enum_exec_or_opt tabs_kind)
 {
-  return join->join_tab; /* There's always one (i.e. first) table */
+  /* There's always one (i.e. first) table */
+  return (tabs_kind == WALK_EXECUTION_TABS)? join->join_tab:
+                                             join->table_access_tabs;
 }
 
 
-JOIN_TAB *next_breadth_first_tab(JOIN *join, JOIN_TAB *tab)
+JOIN_TAB *next_breadth_first_tab(JOIN *join, enum enum_exec_or_opt tabs_kind,
+                                 JOIN_TAB *tab)
 {
+  JOIN_TAB* const first_top_tab= first_breadth_first_tab(join, tabs_kind);
+  const uint n_top_tabs_count= (tabs_kind == WALK_EXECUTION_TABS)? 
+                                  join->top_join_tab_count:
+                                  join->top_table_access_tabs_count;
   if (!tab->bush_root_tab)
   {
     /* We're at top level. Get the next top-level tab */
     tab++;
-    if (tab < join->join_tab + join->top_join_tab_count)
+    if (tab < first_top_tab + n_top_tabs_count)
       return tab;
 
     /* No more top-level tabs. Switch to enumerating SJM nest children */
-    tab= join->join_tab;
+    tab= first_top_tab;
   }
   else
   {
@@ -7259,7 +7351,7 @@ JOIN_TAB *next_breadth_first_tab(JOIN *j
     Ok, "tab" points to a top-level table, and we need to find the next SJM
     nest and enter it.
   */
-  for (; tab < join->join_tab + join->top_join_tab_count; tab++)
+  for (; tab < first_top_tab + n_top_tabs_count; tab++)
   {
     if (tab->bush_children)
       return tab->bush_children->start;
@@ -7283,7 +7375,7 @@ JOIN_TAB *first_top_level_tab(JOIN *join
 
 JOIN_TAB *next_top_level_tab(JOIN *join, JOIN_TAB *tab)
 {
-  tab= next_breadth_first_tab(join, tab);
+  tab= next_breadth_first_tab(join, WALK_EXECUTION_TABS, tab);
   if (tab && tab->bush_root_tab)
     tab= NULL;
   return tab;
@@ -7583,6 +7675,13 @@ get_best_combination(JOIN *join)
 
   join->top_join_tab_count= join->join_tab_ranges.head()->end - 
                             join->join_tab_ranges.head()->start;
+  /*
+    Save pointers to select join tabs for SHOW EXPLAIN
+  */
+  join->table_access_tabs= join->join_tab;
+  join->top_table_access_tabs_count= join->top_join_tab_count;
+
+
   update_depend_map(join);
   DBUG_RETURN(0);
 }
@@ -7826,6 +7925,7 @@ static bool create_ref_for_key(JOIN *joi
   j->ref.null_rejecting= 0;
   j->ref.disable_cache= FALSE;
   j->ref.null_ref_part= NO_REF_PART;
+  j->ref.const_ref_part_map= 0;
   keyuse=org_keyuse;
 
   store_key **ref_key= j->ref.key_copy;
@@ -7861,6 +7961,13 @@ static bool create_ref_for_key(JOIN *joi
       if (keyuse->null_rejecting) 
         j->ref.null_rejecting |= 1 << i;
       keyuse_uses_no_tables= keyuse_uses_no_tables && !keyuse->used_tables;
+      /*
+        Todo: we should remove this check for thd->lex->describe on the next
+        line. With SHOW EXPLAIN code, EXPLAIN printout code no longer depends
+        on it. However, removing the check caused change in lots of query
+        plans! Does the optimizer depend on the contents of
+        table_ref->key_copy ? If yes, do we produce incorrect EXPLAINs? 
+      */
       if (!keyuse->val->used_tables() && !thd->lex->describe)
       {					// Compare against constant
 	store_key_item tmp(thd, 
@@ -7873,6 +7980,7 @@ static bool create_ref_for_key(JOIN *joi
 	if (thd->is_fatal_error)
 	  DBUG_RETURN(TRUE);
 	tmp.copy();
+        j->ref.const_ref_part_map |= key_part_map(1) << i ;
       }
       else
 	*ref_key++= get_store_key(thd,
@@ -8015,6 +8123,7 @@ JOIN::make_simple_join(JOIN *parent, TAB
       !(parent->join_tab_reexec= (JOIN_TAB*) thd->alloc(sizeof(JOIN_TAB))))
     DBUG_RETURN(TRUE);                        /* purecov: inspected */
 
+  // psergey-todo: here, save the pointer for original join_tabs.
   join_tab= parent->join_tab_reexec;
   table= &parent->table_reexec[0]; parent->table_reexec[0]= temp_table;
   table_count= top_join_tab_count= 1;
@@ -10212,7 +10321,12 @@ void JOIN_TAB::cleanup()
   if (cache)
   {
     cache->free();
-    cache= 0;
+    cache= 0; // psergey: this is why we don't see "Using join cache" in SHOW EXPLAIN
+              //  when it is run for "Using temporary+filesort" queries while they 
+              //  are at reading-from-tmp-table phase.
+              //
+              //  TODO ask igor if this can be just moved to later phase
+              //  (JOIN_CACHE objects themselves are not big, arent they)
   }
   limit= 0;
   if (table)
@@ -10308,9 +10422,18 @@ bool JOIN_TAB::preread_init()
       mysql_handle_single_derived(join->thd->lex,
                                     derived, DT_CREATE | DT_FILL))
       return TRUE;
+
   preread_init_done= TRUE;
   if (select && select->quick)
     select->quick->replace_handler(table->file);
+
+  DBUG_EXECUTE_IF("show_explain_probe_join_tab_preread", 
+                  if (dbug_user_var_equals_int(join->thd, 
+                                               "show_explain_probe_select_id", 
+                                               join->select_lex->select_number))
+                        dbug_serve_apcs(join->thd, 1);
+                 );
+
   return FALSE;
 }
 
@@ -10518,6 +10641,11 @@ void JOIN::cleanup(bool full)
 {
   DBUG_ENTER("JOIN::cleanup");
   DBUG_PRINT("enter", ("full %u", (uint) full));
+  
+  /*
+    psergey: let's try without this first:
+  */
+  have_query_plan= QEP_DELETED;
 
   if (table)
   {
@@ -15421,7 +15549,7 @@ create_internal_tmp_table_from_heap2(THD
     DBUG_EXECUTE_IF("raise_error", write_err= HA_ERR_FOUND_DUPP_KEY ;);
     if (write_err)
       goto err;
-    if (thd->killed)
+    if (thd->check_killed())
     {
       thd->send_kill_message();
       goto err_killed;
@@ -15680,6 +15808,15 @@ do_select(JOIN *join,List<Item> *fields,
   else
   {
     DBUG_ASSERT(join->table_count);
+
+    THD *thd= join->thd;
+    DBUG_EXECUTE_IF("show_explain_probe_do_select", 
+                    if (dbug_user_var_equals_int(thd, 
+                                                 "show_explain_probe_select_id", 
+                                                 join->select_lex->select_number))
+                          dbug_serve_apcs(thd, 1);
+                   );
+
     if (join->outer_ref_cond && !join->outer_ref_cond->val_int())
       error= NESTED_LOOP_NO_MORE_ROWS;
     else
@@ -15810,7 +15947,7 @@ sub_select_cache(JOIN *join, JOIN_TAB *j
       rc= sub_select(join, join_tab, end_of_records);
     DBUG_RETURN(rc);
   }
-  if (join->thd->killed)
+  if (join->thd->check_killed())
   {
     /* The user has aborted the execution of the query */
     join->thd->send_kill_message();
@@ -16097,7 +16234,7 @@ evaluate_join_record(JOIN *join, JOIN_TA
     DBUG_RETURN(NESTED_LOOP_ERROR);
   if (error < 0)
     DBUG_RETURN(NESTED_LOOP_NO_MORE_ROWS);
-  if (join->thd->killed)			// Aborted by user
+  if (join->thd->check_killed())			// Aborted by user
   {
     join->thd->send_kill_message();
     DBUG_RETURN(NESTED_LOOP_KILLED);            /* purecov: inspected */
@@ -16841,6 +16978,14 @@ int read_first_record_seq(JOIN_TAB *tab)
 static int
 test_if_quick_select(JOIN_TAB *tab)
 {
+  DBUG_EXECUTE_IF("show_explain_probe_test_if_quick_select", 
+                  if (dbug_user_var_equals_int(tab->join->thd, 
+                                               "show_explain_probe_select_id", 
+                                               tab->join->select_lex->select_number))
+                        dbug_serve_apcs(tab->join->thd, 1);
+                 );
+
+
   delete tab->select->quick;
   tab->select->quick=0;
   return tab->select->test_quick_select(tab->join->thd, tab->keys,
@@ -17301,7 +17446,7 @@ end_write(JOIN *join, JOIN_TAB *join_tab
     }
   }
 end:
-  if (join->thd->killed)
+  if (join->thd->check_killed())
   {
     join->thd->send_kill_message();
     DBUG_RETURN(NESTED_LOOP_KILLED);             /* purecov: inspected */
@@ -17384,7 +17529,7 @@ end_update(JOIN *join, JOIN_TAB *join_ta
   }
   join->send_records++;
 end:
-  if (join->thd->killed)
+  if (join->thd->check_killed())
   {
     join->thd->send_kill_message();
     DBUG_RETURN(NESTED_LOOP_KILLED);             /* purecov: inspected */
@@ -17434,7 +17579,7 @@ end_unique_update(JOIN *join, JOIN_TAB *
       DBUG_RETURN(NESTED_LOOP_ERROR);            /* purecov: inspected */
     }
   }
-  if (join->thd->killed)
+  if (join->thd->check_killed())
   {
     join->thd->send_kill_message();
     DBUG_RETURN(NESTED_LOOP_KILLED);             /* purecov: inspected */
@@ -17512,7 +17657,7 @@ end_write_group(JOIN *join, JOIN_TAB *jo
   if (join->procedure)
     join->procedure->add();
 end:
-  if (join->thd->killed)
+  if (join->thd->check_killed())
   {
     join->thd->send_kill_message();
     DBUG_RETURN(NESTED_LOOP_KILLED);             /* purecov: inspected */
@@ -18997,7 +19142,7 @@ static int remove_dup_with_compare(THD *
   error= file->ha_rnd_next(record);
   for (;;)
   {
-    if (thd->killed)
+    if (thd->check_killed())
     {
       thd->send_kill_message();
       error=0;
@@ -19129,7 +19274,7 @@ static int remove_dup_with_hash_index(TH
   for (;;)
   {
     uchar *org_key_pos;
-    if (thd->killed)
+    if (thd->check_killed())
     {
       thd->send_kill_message();
       error=0;
@@ -21148,29 +21293,155 @@ void JOIN::clear()
   }
 }
 
+
+/*
+  Print an EXPLAIN line with all NULLs and given message in the 'Extra' column
+*/
+int print_explain_message_line(select_result_sink *result, 
+                               SELECT_LEX *select_lex,
+                               bool on_the_fly,
+                               uint8 options,
+                               const char *message)
+{
+  const CHARSET_INFO *cs= system_charset_info;
+  Item *item_null= new Item_null();
+  List<Item> item_list;
+
+  if (on_the_fly)
+    select_lex->set_explain_type(on_the_fly);
+
+  item_list.push_back(new Item_int((int32)
+                                   select_lex->select_number));
+  item_list.push_back(new Item_string(select_lex->type,
+                                      strlen(select_lex->type), cs));
+  for (uint i=0 ; i < 7; i++)
+    item_list.push_back(item_null);
+  if (options & DESCRIBE_PARTITIONS)
+    item_list.push_back(item_null);
+  if (options & DESCRIBE_EXTENDED)
+    item_list.push_back(item_null);
+
+  item_list.push_back(new Item_string(message,strlen(message),cs));
+
+  if (result->send_data(item_list))
+    return 1;
+  return 0;
+}
+
+
+int print_fake_select_lex_join(select_result_sink *result, bool on_the_fly,
+                               SELECT_LEX *select_lex, uint8 explain_flags)
+{
+  const CHARSET_INFO *cs= system_charset_info;
+  Item *item_null= new Item_null();
+  List<Item> item_list;
+  if (on_the_fly)
+    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
+    and no filesort will be actually done, but executing all selects in
+    the UNION to provide precise EXPLAIN information will hardly be
+    appreciated :)
+  */
+  char table_name_buffer[SAFE_NAME_LEN];
+  item_list.empty();
+  /* id */
+  item_list.push_back(new Item_null);
+  /* select_type */
+  item_list.push_back(new Item_string(select_lex->type,
+                                      strlen(select_lex->type),
+                                      cs));
+  /* table */
+  {
+    SELECT_LEX *sl= select_lex->master_unit()->first_select();
+    uint len= 6, lastop= 0;
+    memcpy(table_name_buffer, STRING_WITH_LEN("<union"));
+    for (; sl && len + lastop + 5 < NAME_LEN; sl= sl->next_select())
+    {
+      len+= lastop;
+      lastop= my_snprintf(table_name_buffer + len, NAME_LEN - len,
+                          "%u,", sl->select_number);
+    }
+    if (sl || len + lastop >= NAME_LEN)
+    {
+      memcpy(table_name_buffer + len, STRING_WITH_LEN("...>") + 1);
+      len+= 4;
+    }
+    else
+    {
+      len+= lastop;
+      table_name_buffer[len - 1]= '>';  // change ',' to '>'
+    }
+    item_list.push_back(new Item_string(table_name_buffer, len, cs));
+  }
+  /* partitions */
+  if (explain_flags & DESCRIBE_PARTITIONS)
+    item_list.push_back(item_null);
+  /* type */
+  item_list.push_back(new Item_string(join_type_str[JT_ALL],
+                                        strlen(join_type_str[JT_ALL]),
+                                        cs));
+  /* possible_keys */
+  item_list.push_back(item_null);
+  /* key*/
+  item_list.push_back(item_null);
+  /* key_len */
+  item_list.push_back(item_null);
+  /* ref */
+  item_list.push_back(item_null);
+  /* in_rows */
+  if (explain_flags & DESCRIBE_EXTENDED)
+    item_list.push_back(item_null);
+  /* rows */
+  item_list.push_back(item_null);
+  /* extra */
+  if (select_lex->master_unit()->global_parameters->order_list.first)
+    item_list.push_back(new Item_string("Using filesort",
+                                        14, cs));
+  else
+    item_list.push_back(new Item_string("", 0, cs));
+
+  if (result->send_data(item_list))
+    return 1;
+  return 0;
+}
+
+
 /**
   EXPLAIN handling.
 
-  Send a description about what how the select will be done to stdout.
+  Produce lines explaining execution of *this* select (not including children
+  selects)
+  @param on_the_fly TRUE <=> we're being executed on-the-fly, so don't make 
+                    modifications to any select's data structures
 */
 
-static void select_describe(JOIN *join, bool need_tmp_table, bool need_order,
-			    bool distinct,const char *message)
+int JOIN::print_explain(select_result_sink *result, uint8 explain_flags,
+                         bool on_the_fly,
+                         bool need_tmp_table, bool need_order,
+                         bool distinct, const char *message)
 {
   List<Item> field_list;
   List<Item> item_list;
+  JOIN *join= this; /* Legacy: this code used to be a non-member function */
   THD *thd=join->thd;
-  select_result *result=join->result;
   Item *item_null= new Item_null();
   CHARSET_INFO *cs= system_charset_info;
   int quick_type;
-  DBUG_ENTER("select_describe");
+  int error= 0;
+  DBUG_ENTER("JOIN::print_explain");
   DBUG_PRINT("info", ("Select 0x%lx, type %s, message %s",
 		      (ulong)join->select_lex, join->select_lex->type,
 		      message ? message : "NULL"));
+  DBUG_ASSERT(have_query_plan == QEP_AVAILABLE);
   /* Don't log this into the slow query log */
-  thd->server_status&= ~(SERVER_QUERY_NO_INDEX_USED | SERVER_QUERY_NO_GOOD_INDEX_USED);
-  join->unit->offset_limit_cnt= 0;
+
+  if (!on_the_fly)
+  {
+    thd->server_status&= ~(SERVER_QUERY_NO_INDEX_USED | SERVER_QUERY_NO_GOOD_INDEX_USED);
+    join->unit->offset_limit_cnt= 0;
+  }
 
   /* 
     NOTE: the number/types of items pushed into item_list must be in sync with
@@ -21178,101 +21449,32 @@ static void select_describe(JOIN *join, 
   */
   if (message)
   {
-    item_list.push_back(new Item_int((int32)
-				     join->select_lex->select_number));
-    item_list.push_back(new Item_string(join->select_lex->type,
-					strlen(join->select_lex->type), cs));
-    for (uint i=0 ; i < 7; i++)
-      item_list.push_back(item_null);
-    if (join->thd->lex->describe & DESCRIBE_PARTITIONS)
-      item_list.push_back(item_null);
-    if (join->thd->lex->describe & DESCRIBE_EXTENDED)
-      item_list.push_back(item_null);
-  
-    item_list.push_back(new Item_string(message,strlen(message),cs));
-    if (result->send_data(item_list))
-      join->error= 1;
+    if (print_explain_message_line(result, join->select_lex, on_the_fly, 
+                                   explain_flags, message))
+      error= 1;
+
   }
   else if (join->select_lex == join->unit->fake_select_lex)
   {
-    /* 
-      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
-      and no filesort will be actually done, but executing all selects in
-      the UNION to provide precise EXPLAIN information will hardly be
-      appreciated :)
-    */
-    char table_name_buffer[SAFE_NAME_LEN];
-    item_list.empty();
-    /* id */
-    item_list.push_back(new Item_null);
-    /* select_type */
-    item_list.push_back(new Item_string(join->select_lex->type,
-					strlen(join->select_lex->type),
-					cs));
-    /* table */
-    {
-      SELECT_LEX *sl= join->unit->first_select();
-      uint len= 6, lastop= 0;
-      memcpy(table_name_buffer, STRING_WITH_LEN("<union"));
-      for (; sl && len + lastop + 5 < NAME_LEN; sl= sl->next_select())
-      {
-        len+= lastop;
-        lastop= my_snprintf(table_name_buffer + len, NAME_LEN - len,
-                            "%u,", sl->select_number);
-      }
-      if (sl || len + lastop >= NAME_LEN)
-      {
-        memcpy(table_name_buffer + len, STRING_WITH_LEN("...>") + 1);
-        len+= 4;
-      }
-      else
-      {
-        len+= lastop;
-        table_name_buffer[len - 1]= '>';  // change ',' to '>'
-      }
-      item_list.push_back(new Item_string(table_name_buffer, len, cs));
-    }
-    /* partitions */
-    if (join->thd->lex->describe & DESCRIBE_PARTITIONS)
-      item_list.push_back(item_null);
-    /* type */
-    item_list.push_back(new Item_string(join_type_str[JT_ALL],
-					  strlen(join_type_str[JT_ALL]),
-					  cs));
-    /* possible_keys */
-    item_list.push_back(item_null);
-    /* key*/
-    item_list.push_back(item_null);
-    /* key_len */
-    item_list.push_back(item_null);
-    /* ref */
-    item_list.push_back(item_null);
-    /* in_rows */
-    if (join->thd->lex->describe & DESCRIBE_EXTENDED)
-      item_list.push_back(item_null);
-    /* rows */
-    item_list.push_back(item_null);
-    /* extra */
-    if (join->unit->global_parameters->order_list.first)
-      item_list.push_back(new Item_string("Using filesort",
-					  14, cs));
-    else
-      item_list.push_back(new Item_string("", 0, cs));
-
-    if (result->send_data(item_list))
-      join->error= 1;
+    if (print_fake_select_lex_join(result, on_the_fly, 
+                                   join->select_lex, 
+                                   explain_flags))
+      error= 1;
   }
   else if (!join->select_lex->master_unit()->derived ||
            join->select_lex->master_unit()->derived->is_materialized_derived())
   {
     table_map used_tables=0;
+    //if (!join->select_lex->type)
+    if (on_the_fly)
+      join->select_lex->set_explain_type(on_the_fly);
 
     bool printing_materialize_nest= FALSE;
     uint select_id= join->select_lex->select_number;
+    JOIN_TAB* const first_top_tab= first_breadth_first_tab(join, WALK_OPTIMIZATION_TABS);
 
-    for (JOIN_TAB *tab= first_breadth_first_tab(join); tab;
-         tab= next_breadth_first_tab(join, tab))
+    for (JOIN_TAB *tab= first_breadth_first_tab(join, WALK_OPTIMIZATION_TABS); tab;
+         tab= next_breadth_first_tab(join, WALK_OPTIMIZATION_TABS, tab))
     {
       if (tab->bush_root_tab)
       {
@@ -21304,6 +21506,7 @@ static void select_describe(JOIN *join, 
       tmp3.length(0);
       tmp4.length(0);
       quick_type= -1;
+      QUICK_SELECT_I *quick= NULL;
 
       /* Don't show eliminated tables */
       if (table->map & join->eliminated_tables)
@@ -21320,17 +21523,19 @@ static void select_describe(JOIN *join, 
                                                     join->select_lex->type;
       item_list.push_back(new Item_string(stype, strlen(stype), cs));
       
+      enum join_type tab_type= tab->type;
       if ((tab->type == JT_ALL || tab->type == JT_HASH) &&
-           tab->select && tab->select->quick)
+           tab->select && tab->select->quick && tab->use_quick != 2)
       {
+        quick= tab->select->quick;
         quick_type= tab->select->quick->get_type();
         if ((quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE) ||
             (quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_INTERSECT) ||
             (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT) ||
             (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION))
-          tab->type= tab->type == JT_ALL ? JT_INDEX_MERGE : JT_HASH_INDEX_MERGE;
+          tab_type= tab->type == JT_ALL ? JT_INDEX_MERGE : JT_HASH_INDEX_MERGE;
         else
-	  tab->type= tab->type == JT_ALL ? JT_RANGE : JT_HASH_RANGE;
+	  tab_type= tab->type == JT_ALL ? JT_RANGE : JT_HASH_RANGE;
       }
 
       /* table */
@@ -21360,7 +21565,7 @@ static void select_describe(JOIN *join, 
 					    cs));
       }
       /* "partitions" column */
-      if (join->thd->lex->describe & DESCRIBE_PARTITIONS)
+      if (explain_flags & DESCRIBE_PARTITIONS)
       {
 #ifdef WITH_PARTITION_STORAGE_ENGINE
         partition_info *part_info;
@@ -21379,8 +21584,8 @@ static void select_describe(JOIN *join, 
 #endif
       }
       /* "type" column */
-      item_list.push_back(new Item_string(join_type_str[tab->type],
-					  strlen(join_type_str[tab->type]),
+      item_list.push_back(new Item_string(join_type_str[tab_type],
+					  strlen(join_type_str[tab_type]),
 					  cs));
       /* Build "possible_keys" value and add it to item_list */
       if (!tab->keys.is_clear_all())
@@ -21404,7 +21609,7 @@ static void select_describe(JOIN *join, 
 	item_list.push_back(item_null);
 
       /* Build "key", "key_len", and "ref" values and add them to item_list */
-      if (tab->type == JT_NEXT)
+      if (tab_type == JT_NEXT)
       {
 	key_info= table->key_info+tab->index;
         key_len= key_info->key_length;
@@ -21423,22 +21628,30 @@ static void select_describe(JOIN *join, 
         length= (longlong10_to_str(key_len, keylen_str_buf, 10) - 
                  keylen_str_buf);
         tmp3.append(keylen_str_buf, length, cs);
-        if (tab->ref.key_parts)
+        if (tab->ref.key_parts && tab_type != JT_FT)
 	{
-	  for (store_key **ref=tab->ref.key_copy ; *ref ; ref++)
+          store_key **ref=tab->ref.key_copy;
+          for (uint kp= 0; kp < tab->ref.key_parts; kp++)
 	  {
 	    if (tmp4.length())
 	      tmp4.append(',');
-	    tmp4.append((*ref)->name(), strlen((*ref)->name()), cs);
+
+            if ((key_part_map(1) << kp) & tab->ref.const_ref_part_map)
+              tmp4.append("const");
+            else
+            {
+              tmp4.append((*ref)->name(), strlen((*ref)->name()), cs);
+              ref++;
+            }
           }
         }
       }
-      if (is_hj && tab->type != JT_HASH)
+      if (is_hj && tab_type != JT_HASH)
       {
         tmp2.append(':');
         tmp3.append(':');
       }
-      if (tab->type == JT_HASH_NEXT)
+      if (tab_type == JT_HASH_NEXT)
       {
         register uint length;
 	key_info= table->key_info+tab->index;
@@ -21453,9 +21666,9 @@ static void select_describe(JOIN *join, 
           tab->select && tab->select->quick)
 =======*/
       }         
-      if (tab->type != JT_CONST && tab->select && tab->select->quick)
+      if (tab->type != JT_CONST && tab->select && quick)
         tab->select->quick->add_keys_and_lengths(&tmp2, &tmp3);
-      if (key_info || (tab->select && tab->select->quick))
+      if (key_info || (tab->select && quick))
       {
         if (tmp2.length())
           item_list.push_back(new Item_string(tmp2.ptr(),tmp2.length(),cs));
@@ -21465,7 +21678,7 @@ static void select_describe(JOIN *join, 
           item_list.push_back(new Item_string(tmp3.ptr(),tmp3.length(),cs));
         else
           item_list.push_back(item_null);
-        if (key_info && tab->type != JT_NEXT)
+        if (key_info && tab_type != JT_NEXT)
           item_list.push_back(new Item_string(tmp4.ptr(),tmp4.length(),cs));
         else
           item_list.push_back(item_null);
@@ -21508,7 +21721,7 @@ static void select_describe(JOIN *join, 
           table_list->schema_table)
       {
         /* in_rows */
-        if (join->thd->lex->describe & DESCRIBE_EXTENDED)
+        if (explain_flags & DESCRIBE_EXTENDED)
           item_list.push_back(item_null);
         /* rows */
         item_list.push_back(item_null);
@@ -21516,9 +21729,9 @@ static void select_describe(JOIN *join, 
       else
       {
         ha_rows examined_rows;
-        if (tab->select && tab->select->quick)
-          examined_rows= tab->select->quick->records;
-        else if (tab->type == JT_NEXT || tab->type == JT_ALL || is_hj)
+        if (tab->select && quick)
+          examined_rows= quick->records;
+        else if (tab_type == JT_NEXT || tab_type == JT_ALL || is_hj)
         {
           if (tab->limit)
             examined_rows= tab->limit;
@@ -21545,7 +21758,7 @@ static void select_describe(JOIN *join, 
                                          MY_INT64_NUM_DECIMAL_DIGITS));
 
         /* Add "filtered" field to item_list. */
-        if (join->thd->lex->describe & DESCRIBE_EXTENDED)
+        if (explain_flags & DESCRIBE_EXTENDED)
         {
           float f= 0.0; 
           if (examined_rows)
@@ -21557,11 +21770,11 @@ static void select_describe(JOIN *join, 
 
       /* Build "Extra" field and add it to item_list. */
       key_read=table->key_read;
-      if ((tab->type == JT_NEXT || tab->type == JT_CONST) &&
+      if ((tab_type == JT_NEXT || tab_type == JT_CONST) &&
           table->covering_keys.is_set(tab->index))
 	key_read=1;
       if (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT &&
-          !((QUICK_ROR_INTERSECT_SELECT*)tab->select->quick)->need_to_fetch_row)
+          !((QUICK_ROR_INTERSECT_SELECT*)quick)->need_to_fetch_row)
         key_read=1;
         
       if (tab->info)
@@ -21589,8 +21802,8 @@ static void select_describe(JOIN *join, 
         uint keyno= MAX_KEY;
         if (tab->ref.key_parts)
           keyno= tab->ref.key;
-        else if (tab->select && tab->select->quick)
-          keyno = tab->select->quick->index;
+        else if (tab->select && quick)
+          keyno = quick->index;
 
         if (keyno != MAX_KEY && keyno == table->file->pushed_idx_cond_keyno &&
             table->file->pushed_idx_cond)
@@ -21629,7 +21842,7 @@ static void select_describe(JOIN *join, 
             {
               extra.append(STRING_WITH_LEN("; Using where with pushed "
                                            "condition"));
-              if (thd->lex->describe & DESCRIBE_EXTENDED)
+              if (explain_flags & DESCRIBE_EXTENDED)
               {
                 extra.append(STRING_WITH_LEN(": "));
                 ((COND *)pushed_cond)->print(&extra, QT_ORDINARY);
@@ -21722,7 +21935,7 @@ static void select_describe(JOIN *join, 
           extra.append(STRING_WITH_LEN("; End temporary"));
         else if (tab->do_firstmatch)
         {
-          if (tab->do_firstmatch == join->join_tab - 1)
+          if (tab->do_firstmatch == /*join->join_tab*/ first_top_tab - 1)
             extra.append(STRING_WITH_LEN("; FirstMatch"));
           else
           {
@@ -21772,9 +21985,28 @@ static void select_describe(JOIN *join, 
       // For next iteration
       used_tables|=table->map;
       if (result->send_data(item_list))
-	join->error= 1;
+	error= 1;
     }
   }
+  DBUG_RETURN(error);
+}
+
+
+/*
+  See st_select_lex::print_explain() for the SHOW EXPLAIN counterpart
+*/ 
+
+static void select_describe(JOIN *join, bool need_tmp_table, bool need_order,
+			    bool distinct,const char *message)
+{
+  THD *thd=join->thd;
+  select_result *result=join->result;
+  DBUG_ENTER("select_describe");
+  join->error= join->print_explain(result, thd->lex->describe, 
+                                   FALSE, /* Not on-the-fly */
+                                   need_tmp_table, need_order, distinct, 
+                                   message);
+
   for (SELECT_LEX_UNIT *unit= join->select_lex->first_inner_unit();
        unit;
        unit= unit->next_unit())
@@ -21817,7 +22049,7 @@ bool mysql_explain_union(THD *thd, SELEC
 
   for (SELECT_LEX *sl= first; sl; sl= sl->next_select())
   {
-    sl->set_explain_type();
+    sl->set_explain_type(FALSE); //psergey-todo: maybe remove this from here?
     sl->options|= SELECT_DESCRIBE;
   }
 

=== modified file 'sql/sql_select.h'
--- sql/sql_select.h	2012-05-21 18:54:41 +0000
+++ sql/sql_select.h	2012-06-12 19:04:11 +0000
@@ -101,6 +101,13 @@ typedef struct st_table_ref
   uchar         *key_buff;                ///< value to look for with key
   uchar         *key_buff2;               ///< key_buff+key_length
   store_key     **key_copy;               //
+
+  /*
+    Bitmap of key parts which refer to constants. key_copy only has copiers for
+    non-const key parts.
+  */
+  key_part_map  const_ref_part_map;
+
   Item          **items;                  ///< val()'s for each keypart
   /*  
     Array of pointers to trigger variables. Some/all of the pointers may be
@@ -896,6 +903,20 @@ protected:
 
 public:
   JOIN_TAB *join_tab, **best_ref;
+
+  /*
+    For "Using temporary+Using filesort" queries, JOIN::join_tab can point to
+    either: 
+    1. array of join tabs describing how to run the select, or
+    2. array of single join tab describing read from the temporary table.
+
+    SHOW EXPLAIN code needs to read/show #1. This is why two next members are
+    there for saving it.
+  */
+  JOIN_TAB *table_access_tabs;
+  uint     top_table_access_tabs_count;
+
+
   JOIN_TAB **map2table;    ///< mapping between table indexes and JOIN_TABs
   JOIN_TAB *join_tab_save; ///< saved join_tab for subquery reexecution
 
@@ -1161,8 +1182,14 @@ public:
   const char *zero_result_cause; ///< not 0 if exec must return zero result
   
   bool union_part; ///< this subselect is part of union 
+
+  enum join_optimization_state { NOT_OPTIMIZED=0,
+                                 OPTIMIZATION_IN_PROGRESS=1,
+                                 OPTIMIZATION_DONE=2};
   bool optimized; ///< flag to avoid double optimization in EXPLAIN
   bool initialized; ///< flag to avoid double init_execution calls
+  
+  enum { QEP_NOT_PRESENT_YET, QEP_AVAILABLE, QEP_DELETED} have_query_plan;
 
   /*
     Additional WHERE and HAVING predicates to be considered for IN=>EXISTS
@@ -1245,6 +1272,7 @@ public:
     ref_pointer_array_size= 0;
     zero_result_cause= 0;
     optimized= 0;
+    have_query_plan= QEP_NOT_PRESENT_YET;
     initialized= 0;
     cond_equal= 0;
     having_equal= 0;
@@ -1273,9 +1301,11 @@ public:
 	      SELECT_LEX_UNIT *unit);
   bool prepare_stage2();
   int optimize();
+  int optimize_inner();
   int reinit();
   int init_execution();
   void exec();
+  void exec_inner();
   int destroy();
   void restore_tmp();
   bool alloc_func_list();
@@ -1387,6 +1417,11 @@ public:
   {
     return (unit->item && unit->item->is_in_predicate());
   }
+
+  int print_explain(select_result_sink *result, uint8 explain_flags,
+                     bool on_the_fly,
+                     bool need_tmp_table, bool need_order,
+                     bool distinct,const char *message);
 private:
   /**
     TRUE if the query contains an aggregate function but has no GROUP
@@ -1738,6 +1773,9 @@ inline bool optimizer_flag(THD *thd, uin
   return (thd->variables.optimizer_switch & flag);
 }
 
+int print_fake_select_lex_join(select_result_sink *result, bool on_the_fly,
+                               SELECT_LEX *select_lex, uint8 select_options);
+
 uint get_index_for_order(ORDER *order, TABLE *table, SQL_SELECT *select,
                          ha_rows limit, bool *need_sort, bool *reverse);
 ORDER *simple_remove_const(ORDER *order, COND *where);

=== modified file 'sql/sql_show.cc'
--- sql/sql_show.cc	2012-05-21 18:54:41 +0000
+++ sql/sql_show.cc	2012-06-12 19:04:11 +0000
@@ -1998,6 +1998,116 @@ void mysqld_list_processes(THD *thd,cons
   DBUG_VOID_RETURN;
 }
 
+
+/*
+  SHOW EXPLAIN FOR command handler
+
+  @param  thd         Current thread's thd
+  @param  thread_id   Thread whose explain we need
+
+  @notes
+  - Attempt to do "SHOW EXPLAIN FOR <myself>" will properly produce "target not
+    running EXPLAINable command".
+  - todo: check how all this can/will work when using thread pools
+*/
+
+void mysqld_show_explain(THD *thd, ulong thread_id)
+{
+  THD *tmp;
+  Protocol *protocol= thd->protocol;
+  List<Item> field_list;
+  DBUG_ENTER("mysqld_show_explain");
+  
+  thd->make_explain_field_list(field_list);
+  if (protocol->send_result_set_metadata(&field_list, Protocol::SEND_NUM_ROWS |
+                                                      Protocol::SEND_EOF))
+    DBUG_VOID_RETURN;
+   
+  /* 
+    Find the thread we need EXPLAIN for. Thread search code was copied from
+    kill_one_thread()
+  */
+  mysql_mutex_lock(&LOCK_thread_count); // For unlink from list
+  I_List_iterator<THD> it(threads);
+  while ((tmp=it++))
+  {
+    if (tmp->command == COM_DAEMON)
+      continue;
+    if (tmp->thread_id == thread_id)
+    {
+      mysql_mutex_lock(&tmp->LOCK_thd_data);	// Lock from delete
+      break;
+    }
+  }
+  mysql_mutex_unlock(&LOCK_thread_count);
+  
+  if (tmp)
+  {
+    bool bres;
+    /* 
+      Ok we've found the thread of interest and it won't go away because 
+        we're holding its LOCK_thd data.
+      Post it an EXPLAIN request.
+      todo: where to get timeout from?
+    */
+    bool timed_out;
+    int timeout_sec= 30;
+    Show_explain_request explain_req;
+    select_result_explain_buffer *explain_buf;
+    
+    explain_buf= new select_result_explain_buffer;
+    explain_buf->thd=thd;
+    explain_buf->protocol= thd->protocol;
+
+    explain_req.explain_buf= explain_buf;
+    explain_req.target_thd= tmp;
+    explain_req.request_thd= thd;
+    explain_req.failed_to_produce= FALSE;
+    
+    /* Ok, we have a lock on target->LOCK_thd_data, can call: */
+    bres= tmp->apc_target.make_apc_call(Show_explain_request::get_explain_data,
+                                        (void*)&explain_req,
+                                        timeout_sec, &timed_out);
+
+    if (bres || explain_req.failed_to_produce)
+    {
+      /* TODO not enabled or time out */
+      if (timed_out)
+      {
+        my_error(ER_ERROR_WHEN_EXECUTING_COMMAND, MYF(0), 
+                 "SHOW EXPLAIN",
+                 "Timeout");
+      }
+      else
+      {
+        my_error(ER_ERROR_WHEN_EXECUTING_COMMAND, MYF(0), 
+                 "SHOW EXPLAIN",
+                 "Target is not running EXPLAINable command");
+      }
+      bres= TRUE;
+      explain_buf->discard_data();
+    }
+    else
+    {
+      push_warning(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
+                   ER_YES, explain_req.query_str.c_ptr_safe());
+    }
+    //mysql_mutex_unlock(&tmp->LOCK_thd_data);
+    if (!bres)
+    {
+      explain_buf->flush_data();
+      my_eof(thd);
+    }
+  }
+  else
+  {
+    my_error(ER_NO_SUCH_THREAD, MYF(0), thread_id);
+  }
+
+  DBUG_VOID_RETURN;
+}
+
+
 int fill_schema_processlist(THD* thd, TABLE_LIST* tables, COND* cond)
 {
   TABLE *table= tables->table;

=== modified file 'sql/sql_union.cc'
--- sql/sql_union.cc	2012-03-11 22:45:18 +0000
+++ sql/sql_union.cc	2012-06-12 19:04:11 +0000
@@ -720,6 +720,8 @@ bool st_select_lex_unit::exec()
     }
   }
 
+  DBUG_EXECUTE_IF("show_explain_probe_union_read", 
+                   dbug_serve_apcs(thd, 1););
   /* Send result to 'result' */
   saved_error= TRUE;
   {

=== modified file 'sql/sql_yacc.yy'
--- sql/sql_yacc.yy	2012-06-10 10:12:50 +0000
+++ sql/sql_yacc.yy	2012-06-12 19:04:11 +0000
@@ -11614,6 +11614,12 @@ show_param:
             Lex->spname= $3;
             Lex->sql_command = SQLCOM_SHOW_CREATE_EVENT;
           }
+        | describe_command FOR_SYM expr
+          {
+            Lex->sql_command= SQLCOM_SHOW_EXPLAIN;
+            Lex->value_list.empty();
+            Lex->value_list.push_front($3);
+          }
         ;
 
 show_engine_param:

=== modified file 'sql/table.h'
--- sql/table.h	2012-05-04 05:16:38 +0000
+++ sql/table.h	2012-06-12 19:04:11 +0000
@@ -1106,7 +1106,12 @@ public:
     See TABLE_LIST::process_index_hints().
   */
   bool force_index_group;
-  bool distinct,const_table,no_rows, used_for_duplicate_elimination;
+  /*
+    TRUE<=> this table was created with create_tmp_table(... distinct=TRUE..)
+    call
+  */
+  bool distinct;
+  bool const_table,no_rows, used_for_duplicate_elimination;
 
   /**
      If set, the optimizer has found that row retrieval should access index 


Follow ups