MWL#182: Explain running statements

- Code cleanup

2 files changed, 124 insertions, 5 deletions

diff --git a/mysql-test/r/show_explain.result b/mysql-test/r/show_explain.result
index 6742c6eacfe..07ccab1419e 100644
--- a/mysql-test/r/show_explain.result
+++ b/mysql-test/r/show_explain.result
@@ -1,4 +1,4 @@
-drop table if exists t0, t1;
+drop table if exists t0, t1, t2;
 create table t0 (a int);
 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 create table t1 (a int);
@@ -125,4 +125,60 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 2	DEPENDENT SUBQUERY	b	ALL	NULL	NULL	NULL	NULL	10	Using where
 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='d,show_explain_probe_1';
+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='d,show_explain_probe_1';
+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='d,show_explain_probe_1';
+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='d,show_explain_probe_1';
+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
+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
+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
+a	SUBQ
+0	0
+1	0
+2	0
+drop table t2;
 drop table t0,t1;
diff --git a/mysql-test/t/show_explain.test b/mysql-test/t/show_explain.test
index 5ed78be1ea4..717949d5cc5 100644
--- a/mysql-test/t/show_explain.test
+++ b/mysql-test/t/show_explain.test
@@ -4,7 +4,7 @@
 --source include/have_debug.inc
 
 --disable_warnings
-drop table if exists t0, t1;
+drop table if exists t0, t1, t2;
 --enable_warnings
 
 # 
@@ -186,12 +186,75 @@ 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='d,show_explain_probe_1';
+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;
 
-# TODO: hit JOIN::optimize for non-select commands: UPDATE/DELETE, SET.
+--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='d,show_explain_probe_1';
+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='d,show_explain_probe_1';
+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='d,show_explain_probe_1';
+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;
 
-## TODO: Test this: multiple SHOW EXPLAIN calls in course of running of one select
-## 
 ## TODO: Test this: have several SHOW EXPLAIN requests be queued up for a
 ##       thread and served together.