Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
5.3.12, 10.0.17, 5.5(EOL), 10.0(EOL)
Description
When derived tables or VIEWs are resolved with subquery merge, they still
increment Created_tmp_tables status variable and CREATED_TMP_TABLES in
performance_schema.events_statements_* tables.
Example:
create table t2 (a int);
|
insert into t2 values (1),(2),(3);
|
create view v2 as select a from t2;
|
flush status;
|
select * from v2;
|
show status like '%Created_tmp%';
|
+-------------------------+-------+
|
| Variable_name | Value |
|
+-------------------------+-------+
|
| Created_tmp_disk_tables | 0 |
|
| Created_tmp_files | 0 |
|
| Created_tmp_tables | 1 |
|
+-------------------------+-------+
|
To be sure it's merged, let's check the query plan:
explain select * from v2;
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
|
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 3 | |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
|
The same thing happens when using a derived table:
select * from (select * from t2) T1;
|
