MySQL Bugs: #83287: created_tmp_tables statistic is wrong

Bug #83287	created_tmp_tables statistic is wrong
Submitted:	6 Oct 2016 12:54	Modified:	16 Nov 2016 19:02
Reporter:	Guilhem Bichot	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
http://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html#statvar_Created_tmp_ta...

"The number of internal temporary tables created by the server while executing statements.
You can compare the number of internal on-disk temporary tables created to the total number of internal temporary tables created by comparing the values of the Created_tmp_disk_tables and Created_tmp_tables variables."

To manage an internal tmp table: 1) we create a C++ object of class TABLE, and then 2) we create the table physically in a storage engine (either MEMORY or InnoDB or MyISAM).
Before we introduced:
- late materialization of derived tables
- UNION ALL without tmp table
the 2 steps were always done together, and created_tmp_tables was updated by step 1.
But now, in the two cases above, (1) (which the user doesn't care about, performance-wise) can be done and (2) can be skipped. As we still increment created_tmp_tables in step 1, this ends up telling the user: "we created a tmp table" while we did not.

How to repeat:
mysql> flush status;
Query OK, 0 rows affected (0,00 sec)

mysql> select 1 union all select 2;
+---+
| 1 |
+---+
| 1 |
| 2 |
+---+
2 rows in set (4,02 sec)

mysql> show status like 'Created_tmp_%';
...
| Created_tmp_tables      | 1     |

Whereas if you observe the SELECT with gdb you will see no table was created in any storage engine (no call to heap_create(), mi_create(), ha_innobase::create()).

Suggested fix:
increment created_tmp_tables in step 2 instead; that means: in instantiate_tmp_table().
This way, created_tmp_tables will mean, as it used to mean: total numbers of internal tmp tables created in all storage engines; while the on-disk ones will - no change - still be in Created_tmp_disk_tables.

Posted by developer:
 
http://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html#statvar_Created_tmp_ta...
" Each invocation of the SHOW STATUS statement uses an internal temporary table and increments the global Created_tmp_tables value. "

is wrong, at least in 8.0 (older versions should be tested).

Posted by developer:
 
Noted in 8.0.1 changelog.

The Created_tmp_tables status variable was incremented in some cases
when no temporary table was created.