Created_tmp_tables is incremented with derived_merge=ON
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
New
|
Low
|
Michael Widenius |
Bug Description
Created_tmp_tables is incremented even when derived_merge=ON, when temp.tables should not be created.
One can observe this as follows:
create table t1 (a int);
insert into t1 values (0),(1)
alter table t1 add b int;
create table t2 as select * from t1;
set optimizer_
explain select * from (select t1.a, t2.b from t1, t2) T;
+----+-
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 100 | |
| 2 | DERIVED | t1 | ALL | NULL | NULL | NULL | NULL | 10 | |
| 2 | DERIVED | t2 | ALL | NULL | NULL | NULL | NULL | 10 | Using join buffer (flat, BNL join) |
+----+-
3 rows in set (0.01 sec)
flush status;
select * from (select t1.a, t2.b from t1, t2) T;
show status like 'created%';
+------
| Variable_name | Value |
+------
| Created_
| Created_tmp_files | 0 |
| Created_tmp_tables | 1 |
+------
3 rows in set (0.00 sec)
set optimizer_
explain select * from (select t1.a, t2.b from t1, t2) T;
+----+-
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 10 | |
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 10 | Using join buffer (flat, BNL join) |
+----+-
2 rows in set (0.00 sec)
flush status;
select * from (select t1.a, t2.b from t1, t2) T;
show status like 'created%';
+------
| Variable_name | Value |
+------
| Created_
| Created_tmp_files | 0 |
| Created_tmp_tables | 1 |
+------
3 rows in set (0.00 sec)
Changed in maria: | |
importance: | Undecided → Low |
Changed in maria: | |
assignee: | Igor Babaev (igorb-seattle) → Michael Widenius (monty) |
The user will see Created_ tmp_tables= 1 in both cases, which is mispresentation of what has happened.