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;
|
Attachments
Issue Links
Activity
Checked where do_not_open parameter of create_tmp_table() comes from.
it was introduced by this revision:
igor@askmonty.org-20100526201818-en2jqo1p1t3hi3ra
Igor Babaev <igor@askmonty.org>
maria-5.3-mwl106
Wed 2010-05-26 13:18:18 -0700
MWL#106: Backport optimizations for derived tables and views.
The main consolidated patch.
According to the documented code:
table->created == TRUE if and only if tmp table was actually created.
This flag gets set in 2 places:
In create_internal_tmp_table(). (which is perfectly reasonable)
In open_tmp_table() function. (which is again reasonable)
To me, it makes sense to increase the status variable whenever this flag change occurs.
However, currently, the only place where this happens is in create_tmp_table()
and create_sj_weedout_tmp_table. In order to not litter the code with status var increments, I've modified
open_tmp_table and create_internal_tmp_table to do this instead.
create_tmp_table with do_not_open that does not have a subsequent open_tmp_table call:
- create_dummy_tmp_table in opt_subselect.cc. (I don't know if the table actually gets opened)
- There is a very possible bug in sql_class.cc:3676 as the parameter keep_row_order is passed as do_not_open! sql_union.cc:156 does not have this bug!
There is also an extra prototype of create_internal_tmp_table with a my_bool big_tables argument. There is no implementation or call to this function in the code anywhere, thus I've removed it.
cvicentiu, can you make a patch that does #1 ?