[MDEV-7586] Merged derived tables/VIEWs increment created_tmp_tables Created: 2015-02-14  Updated: 2015-03-06  Resolved: 2015-03-04

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.3.12, 10.0.17, 5.5, 10.0
Fix Version/s: 10.0.18

Type: Bug Priority: Critical
Reporter: Sergei Petrunia Assignee: Vicențiu Ciorbaru
Resolution: Fixed Votes: 0
Labels: optimizer

Issue Links:
Relates
relates to MDEV-710 LP:903169 - Created_tmp_tables is inc... Closed
relates to MDEV-7662 Possible perf issue: handler objects ... Open
relates to MDEV-7669 tmp_table_count-7586 fails in ps and ... Closed

 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;



 Comments   
Comment by Sergei Petrunia [ 2015-02-14 ]

Code-wise the problem happens when create_tmp_table() is called with the parameter do_not_open=true. We create a temporary but we don't open it. The problems with this are

  • (BIG) Incrementing the counter causes confusion for the users.
  • (Smaller) Do we really need this un-opened tmp. table? Creating it looks like waste of CPU/space.
Comment by Sergei Petrunia [ 2015-02-14 ]

MySQL 5.6.20 and MySQL 5.6.10 do not have this problem.

Comment by Sergei Petrunia [ 2015-02-14 ]

MDEV-710 is a very similar ancient bug

Comment by Sergei Petrunia [ 2015-02-14 ]

Need to discuss this with igor.

Comment by Sergei Petrunia [ 2015-02-19 ]

Discussion notes:
1. We must not increment the counter at create_tmp_table() invoked with do_not_open=true. It should be incremented when the table is actually opened.

2. Need to check out where the do_not_open=true code comes from.

Comment by Sergei Petrunia [ 2015-02-19 ]

cvicentiu, can you make a patch that does #1 ?

Comment by Sergei Petrunia [ 2015-02-19 ]

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.

Comment by Vicențiu Ciorbaru [ 2015-02-22 ]

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!
Comment by Vicențiu Ciorbaru [ 2015-02-22 ]

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.

Comment by Sergei Petrunia [ 2015-03-04 ]

The part about possible slowdown is branched off into MDEV-7662.

Generated at Thu Feb 08 07:20:44 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.