Created_tmp_tables is incremented with derived_merge=ON

Bug #903169 reported by Sergey Petrunia on 2011-12-12
6
This bug affects 1 person
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),(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) wrote :

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

Sergey Petrunia (sergefp) wrote :

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.

Changed in maria:
milestone: none → 5.2
milestone: 5.2 → 5.3
assignee: nobody → Igor Babaev (igorb-seattle)
Changed in maria:
importance: Undecided → Low
Michael Widenius (monty) on 2012-03-21
Changed in maria:
assignee: Igor Babaev (igorb-seattle) → Michael Widenius (monty)
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers