Created_tmp_tables is incremented with derived_merge=ON

[Sergey Petrunia]

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),(2),(3),(4),(5),(6),(7),(8),(9);
alter table t1 add b int;
create table t2 as select * from t1;

set optimizer_switch='derived_merge=off';
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_tmp_disk_tables | 0 |
| Created_tmp_files | 0 |
| Created_tmp_tables | 1 |
+-------------------------+-------+
3 rows in set (0.00 sec)

set optimizer_switch='derived_merge=on';
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_tmp_disk_tables | 0 |
| Created_tmp_files | 0 |
| Created_tmp_tables | 1 |
+-------------------------+-------+
3 rows in set (0.00 sec)

[Sergey Petrunia (sergefp)]

The user will see Created_tmp_tables=1 in both cases, which is mispresentation of what has happened.

[Sergey Petrunia (sergefp)]

Ok, Created_tmp_tables is incremented at the start of create_tmp_table().

When derived_merge=ON, we still get calls like this:

create_tmp_table (thd=0x9ed6fa8, param=0x9f3d6a0, fields=..., group=0x0, distinct=false, save_sum_fields=true, select_options=2416204288, rows_limit=18446744073709551615, table_alias=0x9f3d1d0 "T", do_not_open=true)

note that do_not_open==TRUE. I think such calls should not be counted.