MDEV-10000 Add EXPLAIN FOR CONNECTION syntax support

3 files changed, 231 insertions, 10 deletions

diff --git a/mysql-test/main/show_explain.result b/mysql-test/main/show_explain.result
index 317a12ef311..f487b0d9a95 100644
--- a/mysql-test/main/show_explain.result
+++ b/mysql-test/main/show_explain.result
@@ -9,18 +9,30 @@ 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;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
+explain for connection;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
 show explain for 2000000000;
 ERROR HY000: Unknown thread id: 2000000000
+explain for connection 2000000000;
+ERROR HY000: Unknown thread id: 2000000000
 show explain for (select max(a) from t0);
 ERROR HY000: You may only use constant expressions in this statement
+explain for connection (select max(a) from t0);
+ERROR HY000: You may only use constant expressions in this statement
 connect  con1, localhost, root,,;
 connection con1;
 SET @old_debug= @@session.debug;
 connection default;
 show explain for $thr2;
 ERROR HY000: Target is not running an EXPLAINable command
+explain for connection $thr2;
+ERROR HY000: Target is not running an EXPLAINable command
 show explain for $thr1;
 ERROR HY000: Target is not running an EXPLAINable command
+explain for connection $thr1;
+ERROR HY000: Target is not running an EXPLAINable command
 connection con1;
 set @show_explain_probe_select_id=1;
 SET debug_dbug='+d,show_explain_probe_join_exec_start';
@@ -31,6 +43,11 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	a	a	5	NULL	999	Using where; Using index
 Warnings:
 Note	1003	select count(*) from t1 where a < 100000
+explain for connection $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a	a	5	NULL	999	Using where; Using index
+Warnings:
+Note	1003	select count(*) from t1 where a < 100000
 connection con1;
 count(*)
 1000
@@ -44,6 +61,16 @@ Note	1003	select max(c) from t1 where a < 10
 connection con1;
 max(c)
 9
+select max(c) from t1 where a < 10;
+connection default;
+explain for connection $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
+connection con1;
+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';
@@ -57,6 +84,17 @@ Note	1003	explain select max(c) from t1 where a < 10
 connection con1;
 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
+# Same for EXPLAIN FOR CONNECTION
+explain select max(c) from t1 where a < 10;
+connection default;
+explain for connection $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
+connection con1;
+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;
 SET debug_dbug=@old_debug;
 # UNION, first branch 
@@ -78,7 +116,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
 SET debug_dbug=@old_debug;
 # UNION, second branch
-set @show_explain_probe_select_id=1;
+set @show_explain_probe_select_id=2;
 SET debug_dbug='+d,show_explain_probe_join_exec_start';
 explain select a from t0 A union select a+1 from t0 B;
 connection default;
@@ -144,17 +182,18 @@ SET debug_dbug=@old_debug;
 # 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;
+explain select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1;
 connection default;
 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
+Note	1003	explain select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1
 connection con1;
-a	(select max(a) from t0 b where b.a+a.a<10)
-0	9
+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
 SET debug_dbug=@old_debug;
 # correlated  subquery, select, while inside the subquery
 set @show_explain_probe_select_id=2;
@@ -235,6 +274,26 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 Warnings:
 Note	1003	update t2 set dummy=0 where (select max(a) from t0 where t2.a + t0.a <3) >3
 connection con1;
+#
+# EXPLAIN FOR CONNECTION for an UPDATE
+#
+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 ;
+connection default;
+explain for connection $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
+Warnings:
+Note	1003	update t2 set dummy=0 where (select max(a) from t0 where t2.a + t0.a <3) >3
+explain for connection $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
+Warnings:
+Note	1003	update t2 set dummy=0 where (select max(a) from t0 where t2.a + t0.a <3) >3
+connection con1;
 drop table t2;
 SET debug_dbug=@old_debug;
 #
@@ -261,6 +320,29 @@ connection con1;
 drop table t2;
 SET debug_dbug=@old_debug;
 #
+# Attempt EXPLAIN FOR CONNECTION 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 ;
+connection default;
+explain for connection $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
+Warnings:
+Note	1003	delete from t2 where (select max(a) from t0 where t2.a + t0.a <3) >3
+explain for connection $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
+Warnings:
+Note	1003	delete from t2 where (select max(a) from t0 where t2.a + t0.a <3) >3
+connection con1;
+drop table t2;
+SET debug_dbug=@old_debug;
+#
 # Multiple SHOW EXPLAIN calls for one select 
 # 
 create table t2 as select a as a, a as dummy from t0 limit 3;
@@ -294,6 +376,39 @@ a	SUBQ
 drop table t2;
 SET debug_dbug=@old_debug;
 #
+# Multiple EXPLAIN FOR CONNECTION 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;
+connection default;
+explain for connection $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
+explain for connection $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
+explain for connection $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
+connection con1;
+a	SUBQ
+0	0
+1	0
+2	0
+drop table t2;
+SET debug_dbug=@old_debug;
+#
 # SHOW EXPLAIN for SELECT ... ORDER BY with "Using filesort"
 #
 explain select * from t0 order by a;
diff --git a/mysql-test/main/show_explain.test b/mysql-test/main/show_explain.test
index 6cbd92ce44b..c8ce804de64 100644
--- a/mysql-test/main/show_explain.test
+++ b/mysql-test/main/show_explain.test
@@ -44,15 +44,34 @@ 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 to call SHOW EXPLAIN with a missing thread ID
+# 
+--error ER_PARSE_ERROR
+show explain for;
+
+# 
+# Also test MySQL-compatible syntax EXPLAIN FOR CONNECTION which was introduced for MDEV-10000
 # 
-# Try killing a non-existent thread
+--error ER_PARSE_ERROR
+explain for connection;
+
+# 
+# Try to call SHOW EXPLAIN/EXPLAIN FOR CONNECTION for a non-existent thread
 # 
 --error ER_NO_SUCH_THREAD
 show explain for 2000000000;
 
+--error ER_NO_SUCH_THREAD
+explain for connection 2000000000;
+
 --error ER_SET_CONSTANTS_ONLY
 show explain for (select max(a) from t0);
 
+--error ER_SET_CONSTANTS_ONLY
+explain for connection (select max(a) from t0);
+
 # 
 # Setup two threads and their ids
 #
@@ -67,14 +86,20 @@ connection default;
 --error ER_TARGET_NOT_EXPLAINABLE
 evalp show explain for $thr2;
 
+--error ER_TARGET_NOT_EXPLAINABLE
+evalp explain for connection $thr2;
+
 # SHOW EXPLAIN FOR <ourselves>
 --error ER_TARGET_NOT_EXPLAINABLE
 evalp show explain for $thr1;
 
+--error ER_TARGET_NOT_EXPLAINABLE
+evalp explain for connection $thr1;
+
 let $wait_condition= select State='show_explain_trap' from information_schema.processlist where id=$thr2;
 
 #
-# Test SHOW EXPLAIN for simple queries
+# Test SHOW EXPLAIN/EXPLAIN FOR CONNECTION for simple queries
 #
 connection con1;
 set @show_explain_probe_select_id=1;
@@ -84,6 +109,7 @@ send select count(*) from t1 where a < 100000;
 connection default;
 --source include/wait_condition.inc
 evalp show explain for $thr2;
+evalp explain for connection $thr2;
 connection con1;
 reap;
 
@@ -96,6 +122,14 @@ connection con1;
 reap;
 
 
+send select max(c) from t1 where a < 10;
+connection default;
+--source include/wait_condition.inc
+evalp explain for connection $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';
@@ -103,6 +137,15 @@ send explain select max(c) from t1 where a < 10;
 connection default;
 --source include/wait_condition.inc
 evalp show explain for $thr2;
+# evalp explain for connection $thr2;
+connection con1;
+reap;
+
+--echo # Same for EXPLAIN FOR CONNECTION
+send explain select max(c) from t1 where a < 10;
+connection default;
+--source include/wait_condition.inc
+evalp explain for connection $thr2;
 connection con1;
 reap;
 set optimizer_switch= @show_expl_tmp;
@@ -121,7 +164,7 @@ SET debug_dbug=@old_debug;
 
 
 --echo # UNION, second branch
-set @show_explain_probe_select_id=1;
+set @show_explain_probe_select_id=2;
 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;
@@ -169,7 +212,7 @@ SET debug_dbug=@old_debug;
 --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;
+send explain 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;
@@ -249,9 +292,25 @@ evalp show explain for $thr2;
 evalp show explain for $thr2;
 connection con1;
 reap;
+
+
+--echo #
+--echo # EXPLAIN FOR CONNECTION for an UPDATE
+--echo #
+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
+evalp explain for connection $thr2;
+--source include/wait_condition.inc
+evalp explain for connection $thr2;
+connection con1;
+reap;
 drop table t2;
 SET debug_dbug=@old_debug;
 
+
 --echo #
 --echo # Attempt SHOW EXPLAIN for a DELETE (UPD: now works)
 --echo # 
@@ -272,6 +331,23 @@ drop table t2;
 SET debug_dbug=@old_debug;
 
 --echo #
+--echo # Attempt EXPLAIN FOR CONNECTION 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
+evalp explain for connection $thr2;
+--source include/wait_condition.inc
+evalp explain for connection $thr2;
+connection con1;
+reap;
+drop table t2;
+SET debug_dbug=@old_debug;
+
+--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;
@@ -290,6 +366,26 @@ reap;
 drop table t2;
 SET debug_dbug=@old_debug;
 
+
+--echo #
+--echo # Multiple EXPLAIN FOR CONNECTION 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 explain for connection $thr2;
+--source include/wait_condition.inc
+evalp explain for connection $thr2;
+--source include/wait_condition.inc
+evalp explain for connection $thr2;
+connection con1;
+reap;
+drop table t2;
+SET debug_dbug=@old_debug;
+
 --echo #
 --echo # SHOW EXPLAIN for SELECT ... ORDER BY with "Using filesort"
 --echo #
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 442644eddd6..74757970411 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -211,7 +211,6 @@ void _CONCAT_UNDERSCORED(turn_parser_debug_on,yyparse)()
     }  \
   } while(0)
 
-
 %}
 %union {
   int  num;
@@ -1699,6 +1698,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize);
         normal_key_options normal_key_opts all_key_opt 
         spatial_key_options fulltext_key_options normal_key_opt 
         fulltext_key_opt spatial_key_opt fulltext_key_opts spatial_key_opts
+        explain_for_connection
 	keep_gcc_happy
         key_using_alg
         part_column_list
@@ -1948,6 +1948,7 @@ verb_clause:
         | do
         | drop
         | execute
+        | explain_for_connection
         | flush
         | get_diagnostics
         | grant
@@ -14121,6 +14122,15 @@ opt_describe_column:
           }
         ;
 
+explain_for_connection:
+          describe_command FOR_SYM CONNECTION_SYM expr
+          {
+            Lex->sql_command= SQLCOM_SHOW_EXPLAIN;
+            if (unlikely(prepare_schema_table(thd, Lex, 0, SCH_EXPLAIN)))
+              MYSQL_YYABORT;
+            add_value_to_list(thd, $4);
+          }
+		;
 
 /* flush things */