1 files changed, 478 insertions, 0 deletions
diff --git a/mysql-test/main/show_analyze.result b/mysql-test/main/show_analyze.result
new file mode 100644
index 00000000..77cfbc58
--- /dev/null
+++ b/mysql-test/main/show_analyze.result
@@ -0,0 +1,478 @@
+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	50.00	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.00	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;
+#
+# MDEV-28124: Server crashes in Explain_aggr_filesort::print_json_members
+# upon SHOW ANALYZE/EXPLAIN FORMAT=JSON
+#
+connection con1;
+set @show_explain_probe_query= 'SELECT count(*) - count(*) FROM sys.waits_by_user_by_latency';
+set debug_dbug='+d,explain_notify_tables_are_closed';
+SELECT count(*) - count(*) FROM sys.waits_by_user_by_latency;
+connection default;
+SHOW ANALYZE FOR $thr2;
+ERROR HY000: Target is not executing an operation with a query plan
+connection con1;
+count(*) - count(*)
+0
+# End
+connection default;
+disconnect con1;
+set debug_sync='RESET';
+#
+# MDEV-28201: Server crashes upon SHOW ANALYZE/EXPLAIN FORMAT=JSON
+#
+CREATE TABLE t1 ( a varchar(1));
+INSERT INTO t1 VALUES ('a'),('b');
+ANALYZE format=json 
+SELECT 1 FROM t1 GROUP BY convert_tz('1969-12-31 22:00:00',a,'+10:00');
+ANALYZE
+{
+  "query_optimization": {
+    "r_total_time_ms": "REPLACED"
+  },
+  "query_block": {
+    "select_id": 1,
+    "r_loops": 1,
+    "r_total_time_ms": "REPLACED",
+    "filesort": {
+      "sort_key": "convert_tz('1969-12-31 22:00:00',t1.a,'+10:00')",
+      "r_loops": 1,
+      "r_total_time_ms": "REPLACED",
+      "r_used_priority_queue": false,
+      "r_output_rows": 1,
+      "r_buffer_size": "REPLACED",
+      "r_sort_mode": "sort_key,rowid",
+      "temporary_table": {
+        "nested_loop": [
+          {
+            "table": {
+              "table_name": "t1",
+              "access_type": "ALL",
+              "r_loops": 1,
+              "rows": 2,
+              "r_rows": 2,
+              "r_table_time_ms": "REPLACED",
+              "r_other_time_ms": "REPLACED",
+              "r_engine_stats": REPLACED,
+              "filtered": 100,
+              "r_filtered": 100
+            }
+          }
+        ]
+      }
+    }
+  }
+}
+DROP TABLE t1;
+#
+# MDEV-31432 tmp_table field accessed after free
+# testing for the above (MDEV-28201) caused use after free error
+#
+create table t1 (x int) engine=myisam;
+insert into t1 values(1);
+set @tmp=@@optimizer_trace;
+set @@optimizer_trace=1;
+SELECT
+1 IN
+((
+SELECT
+1 IN (SELECT 1 AS x0
+FROM
+(
+SELECT *
+FROM (SELECT 1 AS x) AS x5
+GROUP BY x,x
+HAVING
+x IN (
+SELECT *
+FROM t1 AS x1
+WHERE
+x IN (SELECT 1 AS x
+FROM t1 AS x3
+GROUP BY x
+HAVING
+x IN (SELECT 0 FROM t1 AS x4)
+)
+)
+) AS x6
+)
+FROM
+t1
+)) as VAL;
+VAL
+0
+set optimizer_trace=@tmp;
+drop table t1;