MDEV-27021 Implement SHOW ANALYZE commandbb-10.8-mdev27021

13 files changed, 794 insertions, 24 deletions

diff --git a/mysql-test/main/show_analyze.result b/mysql-test/main/show_analyze.result
new file mode 100644
index 00000000000..8c37876fa50
--- /dev/null
+++ b/mysql-test/main/show_analyze.result
@@ -0,0 +1,379 @@
+drop table if exists t0, t1, t2, t3, t4;
+drop view if exists v1;
+SET @old_debug= @@session.debug;
+set debug_sync='RESET';
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (a int);
+alter table t1 add b int, add c int, add filler char(32);
+insert into t1 select A.a, 10*B.a, 100*C.a, 'foo filler' from t0 A, t0 B, t0 C;
+alter table t1 add key(a), add key(b);
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	Table is already up to date
+show analyze for 1001;
+ERROR HY000: Unknown thread id: 1001
+show analyze for (select a from t0 limit 1);
+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 analyze for $thr2;
+ERROR HY000: Target is not executing an operation with a query plan
+show analyze for $thr1;
+ERROR HY000: Target is not executing an operation with a query plan
+connection con1;
+set @show_explain_probe_select_id=1;
+SET debug_dbug='+d,show_explain_probe_join_exec_end';
+select count(*) from t1 where c < 500;
+connection default;
+show analyze for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000	1000.00	59.38	50.00	Using where
+Warnings:
+Note	1003	select count(*) from t1 where c < 500
+connection con1;
+count(*)
+500
+select max(c) from t1 where c < 10;
+connection default;
+show analyze for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000	1000.00	10.16	10.00	Using where
+Warnings:
+Note	1003	select max(c) from t1 where c < 10
+connection con1;
+max(c)
+0
+# We can catch ANALYZE too.
+analyze select max(c) from t1 where a < 10;
+connection default;
+show analyze for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	1000	1000.00	99.90	100.00	Using where
+Warnings:
+Note	1003	analyze select max(c) from t1 where a < 10
+connection con1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	1000	1000.00	99.90	100.00	Using where
+SET debug_dbug=@old_debug;
+# UNION, select, first branch
+set @show_explain_probe_select_id=1;
+SET debug_dbug='+d,show_explain_probe_join_exec_end';
+select max(a) from t0 A where a<=5 union select max(a+1) from t0 B where a>=9;
+connection default;
+show analyze for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	60.00	Using where
+2	UNION	B	ALL	NULL	NULL	NULL	NULL	10	NULL	100.00	NULL	Using where
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	0.00	NULL	NULL	
+Warnings:
+Note	1003	select max(a) from t0 A where a<=5 union select max(a+1) from t0 B where a>=9
+connection con1;
+max(a)
+5
+10
+# UNION, select, second branch
+set @show_explain_probe_select_id=2;
+SET debug_dbug='+d,show_explain_probe_join_exec_end';
+select max(a) from t0 A where a<=5 union select max(a+1) from t0 B where a>=9;
+connection default;
+show analyze for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	60.00	Using where
+2	UNION	B	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	10.00	Using where
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	0.00	NULL	NULL	
+Warnings:
+Note	1003	select max(a) from t0 A where a<=5 union select max(a+1) from t0 B where a>=9
+connection con1;
+max(a)
+5
+10
+# UNION, analyze, first branch
+set @show_explain_probe_select_id=1;
+SET debug_dbug='+d,show_explain_probe_join_exec_end';
+analyze select a from t0 A where a<=5 union select a+1 from t0 B where a>=9;
+connection default;
+show analyze for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	60.00	Using where
+2	UNION	B	ALL	NULL	NULL	NULL	NULL	10	NULL	100.00	NULL	Using where
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	0.00	NULL	NULL	
+Warnings:
+Note	1003	analyze select a from t0 A where a<=5 union select a+1 from t0 B where a>=9
+connection con1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	60.00	Using where
+2	UNION	B	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	10.00	Using where
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	7.00	NULL	NULL	
+# UNION, analyze, second branch
+set @show_explain_probe_select_id=2;
+analyze select a from t0 A where a<=5 union select a+1 from t0 B where a>=9;
+connection default;
+show analyze for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	60.00	Using where
+2	UNION	B	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	10.00	Using where
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	0.00	NULL	NULL	
+Warnings:
+Note	1003	analyze select a from t0 A where a<=5 union select a+1 from t0 B where a>=9
+connection con1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	60.00	Using where
+2	UNION	B	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	10.00	Using where
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	7.00	NULL	NULL	
+SET debug_dbug=@old_debug;
+# Uncorrelated  subquery, select
+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 a>6) from t0 A where a<2;
+connection default;
+show analyze for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	20.00	Using where
+2	SUBQUERY	B	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	30.00	Using where
+Warnings:
+Note	1003	select a, (select max(a) from t0 B where a>6) from t0 A where a<2
+connection con1;
+a	(select max(a) from t0 B where a>6)
+0	9
+1	9
+SET debug_dbug=@old_debug;
+# Uncorrelated  subquery, analyze
+set @show_explain_probe_select_id=1;
+SET debug_dbug='+d,show_explain_probe_join_exec_end';
+analyze select a, (select max(a) from t0 B where a>6) from t0 A where a<2;
+connection default;
+show analyze for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	20.00	Using where
+2	SUBQUERY	B	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	30.00	Using where
+Warnings:
+Note	1003	analyze select a, (select max(a) from t0 B where a>6) from t0 A where a<2
+connection con1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	A	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	20.00	Using where
+2	SUBQUERY	B	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	30.00	Using where
+SET debug_dbug=@old_debug;
+# correlated subquery, select, before execution start
+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<2;
+connection default;
+show analyze for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	10	NULL	100.00	NULL	Using where
+2	DEPENDENT SUBQUERY	b	ALL	NULL	NULL	NULL	NULL	10	NULL	100.00	NULL	Using where
+Warnings:
+Note	1003	select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<2
+connection con1;
+a	(select max(a) from t0 b where b.a+a.a<10)
+0	9
+1	8
+SET debug_dbug=@old_debug;
+# correlated subquery, select, after execution
+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<2;
+connection default;
+show analyze for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	20.00	Using where
+2	DEPENDENT SUBQUERY	b	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	95.00	Using where
+Warnings:
+Note	1003	select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<2
+connection con1;
+a	(select max(a) from t0 b where b.a+a.a<10)
+0	9
+1	8
+SET debug_dbug=@old_debug;
+# correlated subquery, analyze
+set @show_explain_probe_select_id=1;
+SET debug_dbug='+d,show_explain_probe_join_exec_end';
+analyze select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<2;
+connection default;
+show analyze for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	20.00	Using where
+2	DEPENDENT SUBQUERY	b	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	95.00	Using where
+Warnings:
+Note	1003	analyze select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<2
+connection con1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	20.00	Using where
+2	DEPENDENT SUBQUERY	b	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	95.00	Using where
+SET debug_dbug=@old_debug;
+# Try to do SHOW ANALYZE for a query that runs a  SET command:
+#
+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);
+connection default;
+show analyze for $thr2;
+ERROR HY000: Target is not executing an operation with a query plan
+kill query $thr2;
+connection con1;
+ERROR 70100: Query execution was interrupted
+SET debug_dbug=@old_debug;
+#
+# Attempt SHOW ANALYZE 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 ;
+connection default;
+show analyze for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	1.00	100.00	0.00	Using where
+2	DEPENDENT SUBQUERY	t0	ALL	NULL	NULL	NULL	NULL	10	NULL	100.00	NULL	Using where
+Warnings:
+Note	1003	update t2 set dummy=0 where (select max(a) from t0 where t2.a + t0.a <3) >3
+show analyze for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	2.00	100.00	0.00	Using where
+2	DEPENDENT SUBQUERY	t0	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	30.00	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;
+#
+# Attempt SHOW ANALYZE 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;
+show analyze for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	1.00	100.00	0.00	Using where
+2	DEPENDENT SUBQUERY	t0	ALL	NULL	NULL	NULL	NULL	10	NULL	100.00	NULL	Using where
+Warnings:
+Note	1003	delete from t2 where (select max(a) from t0 where t2.a + t0.a <3) >3
+show analyze for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	2.00	100.00	0.00	Using where
+2	DEPENDENT SUBQUERY	t0	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	30.00	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 ANALYZE 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;
+show analyze for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	1.00	100.00	100.00	
+2	DEPENDENT SUBQUERY	t0	ALL	NULL	NULL	NULL	NULL	10	NULL	100.00	NULL	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 analyze for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	2.00	100.00	100.00	
+2	DEPENDENT SUBQUERY	t0	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	30.00	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 analyze for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	3.00	100.00	100.00	
+2	DEPENDENT SUBQUERY	t0	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	25.00	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
+SET debug_dbug=@old_debug;
+drop table t2;
+#
+# SHOW ANALYZE for SELECT ... ORDER BY with "Using filesort"
+#
+SET debug_dbug='+d,show_explain_probe_join_exec_end';
+set @show_explain_probe_select_id=1;
+select * from t0 order by a;
+connection default;
+show analyze for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	100.00	Using filesort
+Warnings:
+Note	1003	select * from t0 order by a
+connection con1;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+SET debug_dbug=@old_debug;
+#
+# SHOW ANALYZE for SELECT ... with "Using temporary"
+#
+connection con1;
+SET debug_dbug='+d,show_explain_probe_join_exec_end';
+set @show_explain_probe_select_id=1;
+select distinct a from t0;
+connection default;
+show analyze for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	100.00	Using temporary
+Warnings:
+Note	1003	select distinct a from t0
+connection con1;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+SET debug_dbug=@old_debug;
+#
+# SHOW ANALYZE for SELECT ... with "Using temporary; Using filesort"
+#
+SET debug_dbug='+d,show_explain_probe_join_exec_end';
+set @show_explain_probe_select_id=1;
+select distinct a from t0;
+connection default;
+show analyze for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	10	10.00	100.00	100.00	Using temporary
+Warnings:
+Note	1003	select distinct a from t0
+connection con1;
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+SET debug_dbug=@old_debug;
+drop table t0,t1;
+# End
+connection default;
+disconnect con1;
+set debug_sync='RESET';
diff --git a/mysql-test/main/show_analyze.test b/mysql-test/main/show_analyze.test
new file mode 100644
index 00000000000..01e86945461
--- /dev/null
+++ b/mysql-test/main/show_analyze.test
@@ -0,0 +1,332 @@
+#
+# Tests for SHOW ANALYZE FOR functionality
+#
+--source include/have_debug.inc
+--source include/have_debug_sync.inc
+--source include/have_innodb.inc
+# Using valgrind can cause 'reap' to fail. See comment below
+--source include/not_valgrind.inc
+
+--disable_warnings
+drop table if exists t0, t1, t2, t3, t4;
+drop view if exists v1;
+--enable_warnings
+SET @old_debug= @@session.debug;
+
+#
+# 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 ANALYZE command, and the
+# test fails.
+#
+-- source include/not_embedded.inc
+
+set debug_sync='RESET';
+
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (a int);
+alter table t1 add b int, add c int, add filler char(32);
+insert into t1 select A.a, 10*B.a, 100*C.a, 'foo filler' from t0 A, t0 B, t0 C;
+alter table t1 add key(a), add key(b);
+analyze table t1;
+
+#
+# Try SHOW ANALYZE for a non-existent thread
+#
+--error ER_NO_SUCH_THREAD
+show analyze for 1001;
+
+--error ER_SET_CONSTANTS_ONLY
+show analyze for (select a from t0 limit 1);
+
+#
+# Setup two threads and their ids
+#
+let $thr1=`select connection_id()`;
+connect (con1, localhost, root,,);
+connection con1;
+let $thr2=`select connection_id()`;
+SET @old_debug= @@session.debug;
+connection default;
+
+# SHOW ANALYZE FOR <idle thread>
+--error ER_TARGET_NOT_EXPLAINABLE
+evalp show analyze for $thr2;
+
+# SHOW ANALYZE FOR <ourselves>
+--error ER_TARGET_NOT_EXPLAINABLE
+evalp show analyze for $thr1;
+
+let $wait_condition= select State='show_explain_trap' from information_schema.processlist where id=$thr2;
+
+#
+# Test SHOW ANALYZE for simple queries
+#
+connection con1;
+set @show_explain_probe_select_id=1;
+SET debug_dbug='+d,show_explain_probe_join_exec_end';
+send select count(*) from t1 where c < 500;
+
+connection default;
+--source include/wait_condition.inc
+evalp show analyze for $thr2;
+connection con1;
+reap;
+
+
+send select max(c) from t1 where c < 10;
+connection default;
+--source include/wait_condition.inc
+evalp show analyze for $thr2;
+connection con1;
+reap;
+
+
+--echo # We can catch ANALYZE too.
+send analyze select max(c) from t1 where a < 10;
+connection default;
+--source include/wait_condition.inc
+evalp show analyze for $thr2;
+connection con1;
+reap;
+SET debug_dbug=@old_debug;
+
+
+--echo # UNION, select, first branch
+set @show_explain_probe_select_id=1;
+SET debug_dbug='+d,show_explain_probe_join_exec_end';
+send select max(a) from t0 A where a<=5 union select max(a+1) from t0 B where a>=9;
+connection default;
+--source include/wait_condition.inc
+evalp show analyze for $thr2;
+connection con1;
+reap;
+
+
+--echo # UNION, select, second branch
+set @show_explain_probe_select_id=2; # <--- Second branch
+SET debug_dbug='+d,show_explain_probe_join_exec_end';
+send select max(a) from t0 A where a<=5 union select max(a+1) from t0 B where a>=9;
+connection default;
+--source include/wait_condition.inc
+evalp show analyze for $thr2;
+connection con1;
+reap;
+
+
+--echo # UNION, analyze, first branch
+set @show_explain_probe_select_id=1;
+SET debug_dbug='+d,show_explain_probe_join_exec_end';
+send analyze select a from t0 A where a<=5 union select a+1 from t0 B where a>=9;
+connection default;
+--source include/wait_condition.inc
+evalp show analyze for $thr2;
+connection con1;
+reap;
+
+
+--echo # UNION, analyze, second branch
+set @show_explain_probe_select_id=2;
+send analyze select a from t0 A where a<=5 union select a+1 from t0 B where a>=9;
+connection default;
+--source include/wait_condition.inc
+evalp show analyze for $thr2;
+connection con1;
+reap;
+SET debug_dbug=@old_debug;
+
+
+--echo # Uncorrelated  subquery, select
+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 a>6) from t0 A where a<2;
+connection default;
+--source include/wait_condition.inc
+evalp show analyze for $thr2;
+connection con1;
+reap;
+SET debug_dbug=@old_debug;
+
+
+--echo # Uncorrelated  subquery, analyze
+set @show_explain_probe_select_id=1;
+SET debug_dbug='+d,show_explain_probe_join_exec_end';
+send analyze select a, (select max(a) from t0 B where a>6) from t0 A where a<2;
+connection default;
+--source include/wait_condition.inc
+evalp show analyze for $thr2;
+connection con1;
+reap;
+SET debug_dbug=@old_debug;
+
+
+--echo # correlated subquery, select, before execution start
+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<2;
+connection default;
+--source include/wait_condition.inc
+evalp show analyze for $thr2;
+connection con1;
+reap;
+SET debug_dbug=@old_debug;
+
+
+--echo # correlated subquery, select, after execution
+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<2;
+connection default;
+--source include/wait_condition.inc
+evalp show analyze for $thr2;
+connection con1;
+reap;
+SET debug_dbug=@old_debug;
+
+
+--echo # correlated subquery, analyze
+set @show_explain_probe_select_id=1;
+SET debug_dbug='+d,show_explain_probe_join_exec_end';
+send analyze select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<2;
+connection default;
+--source include/wait_condition.inc
+evalp show analyze for $thr2;
+connection con1;
+reap;
+SET debug_dbug=@old_debug;
+
+
+--echo # Try to do SHOW ANALYZE for a query that runs a  SET command:
+--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_TARGET_NOT_EXPLAINABLE
+evalp show analyze for $thr2;
+evalp kill query $thr2;
+connection con1;
+--error ER_QUERY_INTERRUPTED
+reap;
+SET debug_dbug=@old_debug;
+
+
+--echo #
+--echo # Attempt SHOW ANALYZE 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
+evalp show analyze for $thr2;
+--source include/wait_condition.inc
+evalp show analyze for $thr2;
+connection con1;
+reap;
+drop table t2;
+SET debug_dbug=@old_debug;
+
+
+--echo #
+--echo # Attempt SHOW ANALYZE 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 show analyze for $thr2;
+--source include/wait_condition.inc
+evalp show analyze for $thr2;
+connection con1;
+reap;
+drop table t2;
+SET debug_dbug=@old_debug;
+
+
+--echo #
+--echo # Multiple SHOW ANALYZE 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 analyze for $thr2;
+--source include/wait_condition.inc
+evalp show analyze for $thr2;
+--source include/wait_condition.inc
+evalp show analyze for $thr2;
+connection con1;
+reap;
+SET debug_dbug=@old_debug;
+drop table t2;
+
+
+--echo #
+--echo # SHOW ANALYZE for SELECT ... ORDER BY with "Using filesort"
+--echo #
+SET debug_dbug='+d,show_explain_probe_join_exec_end';
+set @show_explain_probe_select_id=1;
+send select * from t0 order by a;
+connection default;
+--source include/wait_condition.inc
+evalp show analyze for $thr2;
+connection con1;
+reap;
+SET debug_dbug=@old_debug;
+
+
+--echo #
+--echo # SHOW ANALYZE for SELECT ... with "Using temporary"
+--echo #
+connection con1;
+SET debug_dbug='+d,show_explain_probe_join_exec_end';
+set @show_explain_probe_select_id=1;
+send select distinct a from t0;
+connection default;
+--source include/wait_condition.inc
+evalp show analyze for $thr2;
+connection con1;
+reap;
+SET debug_dbug=@old_debug;
+
+
+--echo #
+--echo # SHOW ANALYZE for SELECT ... with "Using temporary; Using filesort"
+--echo #
+SET debug_dbug='+d,show_explain_probe_join_exec_end';
+set @show_explain_probe_select_id=1;
+send select distinct a from t0;
+connection default;
+--source include/wait_condition.inc
+evalp show analyze for $thr2;
+connection con1;
+reap;
+SET debug_dbug=@old_debug;
+
+
+drop table t0,t1;
+--echo # End
+connection default;
+disconnect con1;
+set debug_sync='RESET';
\ No newline at end of file
diff --git a/mysql-test/main/show_explain.result b/mysql-test/main/show_explain.result
index 317a12ef311..32faec467db 100644
--- a/mysql-test/main/show_explain.result
+++ b/mysql-test/main/show_explain.result
@@ -18,9 +18,9 @@ connection con1;
 SET @old_debug= @@session.debug;
 connection default;
 show explain for $thr2;
-ERROR HY000: Target is not running an EXPLAINable command
+ERROR HY000: Target is not executing an operation with a query plan
 show explain for $thr1;
-ERROR HY000: Target is not running an EXPLAINable command
+ERROR HY000: Target is not executing an operation with a query plan
 connection con1;
 set @show_explain_probe_select_id=1;
 SET debug_dbug='+d,show_explain_probe_join_exec_start';
@@ -209,7 +209,7 @@ SET debug_dbug='+d,show_explain_probe_join_exec_start';
 set @foo= (select max(a) from t0 where sin(a) >0);
 connection default;
 show explain for $thr2;
-ERROR HY000: Target is not running an EXPLAINable command
+ERROR HY000: Target is not executing an operation with a query plan
 kill query $thr2;
 connection con1;
 ERROR 70100: Query execution was interrupted
@@ -457,7 +457,7 @@ SET debug_dbug='+d,show_explain_probe_join_exec_end';
 SELECT * FROM v1, t2;
 connection default;
 show explain for $thr2;
-ERROR HY000: Target is not running an EXPLAINable command
+ERROR HY000: Target is not executing an operation with a query plan
 kill query $thr2;
 connection con1;
 ERROR 70100: Query execution was interrupted
diff --git a/sql/handler.h b/sql/handler.h
index fe61666bf20..082000ac234 100644
--- a/sql/handler.h
+++ b/sql/handler.h
@@ -1007,6 +1007,7 @@ enum enum_schema_tables
   SCH_ENGINES,
   SCH_EVENTS,
   SCH_EXPLAIN,
+  SCH_ANALYZE,
   SCH_FILES,
   SCH_GLOBAL_STATUS,
   SCH_GLOBAL_VARIABLES,
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index 808e590435a..f02ea57bcba 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -3492,6 +3492,7 @@ SHOW_VAR com_status_vars[]= {
   {"show_errors",          STMT_STATUS(SQLCOM_SHOW_ERRORS)},
   {"show_events",          STMT_STATUS(SQLCOM_SHOW_EVENTS)},
   {"show_explain",         STMT_STATUS(SQLCOM_SHOW_EXPLAIN)},
+  {"show_analyze",         STMT_STATUS(SQLCOM_SHOW_ANALYZE)},
   {"show_fields",          STMT_STATUS(SQLCOM_SHOW_FIELDS)},
 #ifndef DBUG_OFF
   {"show_function_code",   STMT_STATUS(SQLCOM_SHOW_FUNC_CODE)},
diff --git a/sql/privilege.h b/sql/privilege.h
index c1233102522..7676a9ac5e0 100644
--- a/sql/privilege.h
+++ b/sql/privilege.h
@@ -634,6 +634,7 @@ constexpr privilege_t PRIV_STMT_DROP_SERVER= FEDERATED_ADMIN_ACL | SUPER_ACL;
 
 /* Privileges related to processes */
 constexpr privilege_t PRIV_COM_PROCESS_INFO= PROCESS_ACL;
+// This privilege applies both for SHOW EXPLAIN and SHOW ANALYZE
 constexpr privilege_t PRIV_STMT_SHOW_EXPLAIN= PROCESS_ACL;
 constexpr privilege_t PRIV_STMT_SHOW_ENGINE_STATUS= PROCESS_ACL;
 constexpr privilege_t PRIV_STMT_SHOW_ENGINE_MUTEX= PROCESS_ACL;
diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt
index f31f21faeb0..cb4f0470b33 100644
--- a/sql/share/errmsg-utf8.txt
+++ b/sql/share/errmsg-utf8.txt
@@ -7224,7 +7224,7 @@ ER_NO_SUCH_TABLE_IN_ENGINE 42S02
         hindi "टेबल '%-.192s.%-.192s' इंजन में मौजूद नहीं है"
         swe "Det finns ingen tabell som heter '%-.192s.%-.192s' i handlern"
 ER_TARGET_NOT_EXPLAINABLE
-        eng "Target is not running an EXPLAINable command"
+        eng "Target is not executing an operation with a query plan"
 ER_CONNECTION_ALREADY_EXISTS
         eng "Connection '%.*s' conflicts with existing connection '%.*s'"
 ER_MASTER_LOG_PREFIX
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index 641c88de826..dcf6233afb6 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -221,6 +221,7 @@ sp_get_flags_for_command(LEX *lex)
   case SQLCOM_SHOW_DATABASES:
   case SQLCOM_SHOW_ERRORS:
   case SQLCOM_SHOW_EXPLAIN:
+  case SQLCOM_SHOW_ANALYZE:
   case SQLCOM_SHOW_FIELDS:
   case SQLCOM_SHOW_FUNC_CODE:
   case SQLCOM_SHOW_GENERIC:
diff --git a/sql/sql_cmd.h b/sql/sql_cmd.h
index 430afadb491..1a01caa77dd 100644
--- a/sql/sql_cmd.h
+++ b/sql/sql_cmd.h
@@ -88,7 +88,8 @@ enum enum_sql_command {
   SQLCOM_SHOW_RELAYLOG_EVENTS,
   SQLCOM_GET_DIAGNOSTICS,
   SQLCOM_SLAVE_ALL_START, SQLCOM_SLAVE_ALL_STOP,
-  SQLCOM_SHOW_EXPLAIN, SQLCOM_SHUTDOWN,
+  SQLCOM_SHOW_EXPLAIN,
+  SQLCOM_SHOW_ANALYZE, SQLCOM_SHUTDOWN,
   SQLCOM_CREATE_ROLE, SQLCOM_DROP_ROLE, SQLCOM_GRANT_ROLE, SQLCOM_REVOKE_ROLE,
   SQLCOM_COMPOUND,
   SQLCOM_SHOW_GENERIC,
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index b9d3eec5a60..613de8105af 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -665,6 +665,7 @@ void init_update_queries(void)
   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_ANALYZE]= 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_USER]= CF_STATUS_COMMAND;
@@ -3864,6 +3865,7 @@ mysql_execute_command(THD *thd, bool is_called_from_prepared_stmt)
     break;
   }
   case SQLCOM_SHOW_EXPLAIN:
+  case SQLCOM_SHOW_ANALYZE:
   {
     if (!thd->security_ctx->priv_user[0] &&
         check_global_access(thd, PRIV_STMT_SHOW_EXPLAIN))
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index d01f84fe7d1..b64b93997a7 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -2998,7 +2998,7 @@ void Show_explain_request::call_in_target_thread()
   DBUG_ASSERT(current_thd == target_thd);
   set_current_thd(request_thd);
   if (target_thd->lex->print_explain(explain_buf, 0 /* explain flags*/,
-                                     false /*TODO: analyze? */, &printed_anything))
+                                     is_analyze, &printed_anything))
   {
     failed_to_produce= TRUE;
   }
@@ -3115,16 +3115,13 @@ void select_result_text_buffer::save_to(String *res)
 }
 
 
-/*
-  Store the SHOW EXPLAIN output in the temporary table.
-*/
-
-int fill_show_explain(THD *thd, TABLE_LIST *table, COND *cond)
+int fill_show_explain_or_analyze(THD *thd, TABLE_LIST *table, COND *cond,
+                                 bool is_analyze)
 {
   const char *calling_user;
   THD *tmp;
   my_thread_id  thread_id;
-  DBUG_ENTER("fill_show_explain");
+  DBUG_ENTER("fill_show_explain_or_analyze");
 
   DBUG_ASSERT(cond==NULL);
   thread_id= thd->lex->value_list.head()->val_int();
@@ -3136,10 +3133,10 @@ int fill_show_explain(THD *thd, TABLE_LIST *table, COND *cond)
     Security_context *tmp_sctx= tmp->security_ctx;
     /*
       If calling_user==NULL, calling thread has SUPER or PROCESS
-      privilege, and so can do SHOW EXPLAIN on any user.
+      privilege, and so can do SHOW EXPLAIN/SHOW ANALYZE on any user.
       
-      if calling_user!=NULL, he's only allowed to view SHOW EXPLAIN on
-      his own threads.
+      if calling_user!=NULL, he's only allowed to view
+      SHOW EXPLAIN/SHOW ANALYZE on his own threads.
     */
     if (calling_user && (!tmp_sctx->user || strcmp(calling_user, 
                                                    tmp_sctx->user)))
@@ -3159,7 +3156,7 @@ int fill_show_explain(THD *thd, TABLE_LIST *table, COND *cond)
     bool bres;
     /* 
       Ok we've found the thread of interest and it won't go away because 
-      we're holding its LOCK_thd_kill. Post it a SHOW EXPLAIN request.
+      we're holding its LOCK_thd_kill. Post it a SHOW EXPLAIN/SHOW ANALYZE request.
     */
     bool timed_out;
     int timeout_sec= 30;
@@ -3168,6 +3165,7 @@ int fill_show_explain(THD *thd, TABLE_LIST *table, COND *cond)
     
     explain_buf= new select_result_explain_buffer(thd, table->table);
 
+    explain_req.is_analyze= is_analyze;
     explain_req.explain_buf= explain_buf;
     explain_req.target_thd= tmp;
     explain_req.request_thd= thd;
@@ -3226,6 +3224,26 @@ int fill_show_explain(THD *thd, TABLE_LIST *table, COND *cond)
 }
 
 
+/*
+  Store the SHOW EXPLAIN output in the temporary table.
+*/
+
+int fill_show_explain(THD *thd, TABLE_LIST *table, COND *cond)
+{
+  return fill_show_explain_or_analyze(thd, table, cond, FALSE);
+}
+
+
+/*
+  Store the SHOW ANALYZE output in the temporary table.
+*/
+
+int fill_show_analyze(THD *thd, TABLE_LIST *table, COND *cond)
+{
+  return fill_show_explain_or_analyze(thd, table, cond, TRUE);
+}
+
+
 struct processlist_callback_arg
 {
   processlist_callback_arg(THD *thd_arg, TABLE *table_arg):
@@ -9631,6 +9649,28 @@ ST_FIELD_INFO show_explain_fields_info[]=
 };
 
 
+ST_FIELD_INFO show_analyze_fields_info[]=
+{
+  Column("id",            SLonglong(3),                  NULLABLE, "id"),
+  Column("select_type",   Varchar(19),                   NOT_NULL, "select_type"),
+  Column("table",         Name(),                        NULLABLE, "table"),
+  Column("type",          Varchar(15),                   NULLABLE, "type"),
+  Column("possible_keys",Varchar(NAME_CHAR_LEN*MAX_KEY), NULLABLE, "possible_keys"),
+  Column("key",          Varchar(NAME_CHAR_LEN*MAX_KEY), NULLABLE, "key"),
+  Column("key_len",      Varchar(NAME_CHAR_LEN*MAX_KEY), NULLABLE, "key_len"),
+  Column("ref",     Varchar(NAME_CHAR_LEN*MAX_REF_PARTS),NULLABLE, "ref"),
+  Column("rows",          SLonglong(10),                 NULLABLE, "rows"),
+  Column("r_rows",        Varchar(NAME_CHAR_LEN),        NULLABLE, "r_rows"),
+
+  /* Fields of type DECIMAL(5,2) to represent percentage.
+  See Show::Type::decimal_precision() and Show::Type::decimal_scale() to learn
+  how 502 converts to precision and scale (5 and 2)*/
+  Column("filtered",      Decimal(502),                  NULLABLE, "filtered"),
+  Column("r_filtered",    Decimal(502),                  NULLABLE, "r_filtered"),
+  Column("Extra",         Varchar(255),                  NOT_NULL, "Extra"),
+  CEnd()
+};
+
 ST_FIELD_INFO check_constraints_fields_info[]=
 {
   Column("CONSTRAINT_CATALOG", Catalog(), NOT_NULL, OPEN_FULL_TABLE),
@@ -9693,6 +9733,8 @@ ST_SCHEMA_TABLE schema_tables[]=
 #endif
   {"EXPLAIN", Show::show_explain_fields_info, 0, fill_show_explain,
   make_old_format, 0, -1, -1, TRUE /*hidden*/ , 0},
+  {"ANALYZE", Show::show_analyze_fields_info, 0, fill_show_analyze,
+   make_old_format, 0, -1, -1, TRUE /*hidden*/, 0},
   {"FILES", Show::files_fields_info, 0,
    hton_fill_schema_table, 0, 0, -1, -1, 0, 0},
   {"GLOBAL_STATUS", Show::variables_fields_info, 0,
diff --git a/sql/sql_show.h b/sql/sql_show.h
index 3d7a4d1146c..89fbcd1a547 100644
--- a/sql/sql_show.h
+++ b/sql/sql_show.h
@@ -155,28 +155,31 @@ THD *find_thread_by_id(longlong id, bool query_id= false);
 
 class select_result_explain_buffer;
 /*
-  SHOW EXPLAIN request object. 
+  SHOW EXPLAIN/SHOW ANALYZE request object.
 */
 
 class Show_explain_request : public Apc_target::Apc_call
 {
 public:
-  THD *target_thd;  /* thd that we're running SHOW EXPLAIN for */
-  THD *request_thd; /* thd that run SHOW EXPLAIN command */
+  THD *target_thd;  /* thd that we're running SHOW EXPLAIN/ANALYZE for */
+  THD *request_thd; /* thd that run SHOW EXPLAIN/ANALYZE command */
   
+  /* FALSE for SHOW EXPLAIN, TRUE - for SHOW ANALYZE*/
+  bool is_analyze;
+
   /* If true, there was some error when producing EXPLAIN output. */
   bool failed_to_produce;
    
-  /* SHOW EXPLAIN will be stored here */
+  /* SHOW EXPLAIN/ANALYZE will be stored here */
   select_result_explain_buffer *explain_buf;
   
-  /* Query that we've got SHOW EXPLAIN for */
+  /* Query that we've got SHOW EXPLAIN/ANALYZE for */
   String query_str;
   
-  /* Overloaded virtual function */
-  void call_in_target_thread();
+  void call_in_target_thread() override;
 };
 
+
 /**
   Condition pushdown used for INFORMATION_SCHEMA / SHOW queries.
   This structure is to implement an optimization when
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 442644eddd6..1b1edd4e522 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -13951,6 +13951,13 @@ show_param:
               MYSQL_YYABORT;
             add_value_to_list(thd, $3);
           }
+        | ANALYZE_SYM FOR_SYM expr
+          {
+            Lex->sql_command= SQLCOM_SHOW_ANALYZE;
+            if (unlikely(prepare_schema_table(thd, Lex, 0, SCH_ANALYZE)))
+              MYSQL_YYABORT;
+            add_value_to_list(thd, $3);
+          }
         | IDENT_sys remember_tok_start wild_and_where
            {
              LEX *lex= Lex;