1# MDEV-7586 regression test.
2# Test if created_tmp_tables status variable is correctly incremented.
3--source include/have_perfschema.inc
4--source include/not_embedded.inc
5
6create table t2 (a int);
7insert into t2 values (1),(2),(3);
8create view v2 as select a from t2;
9
10flush status;
11select * from v2;
12--disable_ps_protocol
13show status like '%Created_tmp%';
14--enable_ps_protocol
15
16explain select * from v2;
17
18select * from (select * from t2) T1;
19--disable_ps_protocol
20show status like '%Created_tmp%';
21--enable_ps_protocol
22
23explain select * from (select * from t2) T1;
24
25
26drop view v2;
27drop table t2;
28
29
30--disable_ps_protocol
31
32CREATE TABLE t1(a int);
33INSERT INTO t1 values(1),(2);
34CREATE TABLE t2(a int);
35INSERT INTO t2 values(1),(2);
36
37EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a HAVING a > 1);
38truncate table performance_schema.events_statements_history_long;
39flush status;
40CREATE TABLE t3 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a HAVING a > 1);
41--echo # Performance schema should be the same as "Created_tmp_tables" variable below
42select sum(created_tmp_tables) from performance_schema.events_statements_history_long;
43show status like '%Created_tmp%';
44drop table t3;
45
46EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a);
47truncate table performance_schema.events_statements_history_long;
48flush status;
49CREATE TABLE t3 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a);
50--echo # Performance schema should be the same as "Created_tmp_tables" variable below
51select sum(created_tmp_tables) from performance_schema.events_statements_history_long;
52show status like '%Created_tmp%';
53
54drop table t1,t2,t3;
55
56truncate table performance_schema.events_statements_history_long;
57flush status;
58--echo # Performance schema should be the same as "Created_tmp_tables" variable below
59select sum(created_tmp_tables) from performance_schema.events_statements_history_long;
60show status like '%Created_tmp%';
61