Bug #113659 explain output "Using temporary" but query dosent create temporary table

• Submitted: 17 Jan 2024 7:55
• Modified: 18 Jan 2024 2:30
• Reporter: Demon Chen
• Status: Verified
• Category: MySQL Server: Performance Schema
• Severity: S3 (Non-critical)
• Version: 8.0
• OS: Any
• CPU Architecture: Any

[17 Jan 2024 7:55] Demon Chen

Description:
Hi,
the table structure is:
CREATE TABLE `user_account` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL COMMENT '用户id',
  `balance` decimal(16,3) NOT NULL DEFAULT '0.000' COMMENT '用户余额',
  PRIMARY KEY (`id`),
  KEY `idx_userid_balance` (`user_id`,`balance`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

INSERT INTO user_account VALUES(1, 500, 1000);
INSERT INTO user_account VALUES(2, 600, 2000);

when you explain UPDATE user_account SET balance = balance -1 WHERE user_id = 500 and balance > 1;
the output is
+----+-------------+--------------+------------+-------+--------------------+--------------------+---------+-------------+------+----------+------------------------------+
| id | select_type | table        | partitions | type  | possible_keys      | key                | key_len | ref         | rows | filtered | Extra                        |
+----+-------------+--------------+------------+-------+--------------------+--------------------+---------+-------------+------+----------+------------------------------+
|  1 | UPDATE      | user_account | NULL       | range | idx_userid_balance | idx_userid_balance | 12      | const,const |    1 |   100.00 | Using where; Using temporary |
+----+-------------+--------------+------------+-------+--------------------+--------------------+---------+-------------+------+----------+------------------------------+

flush status;
show status like '%tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 0     |
+-------------------------+-------+

UPDATE user_account SET balance = balance -1 WHERE user_id = 500 and balance >1;

show status like '%tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 0     |
+-------------------------+-------+

it's seems when you executes this sql, mysql does not create tmp tables.

the explain output does not match the true exectuing path. 

How to repeat:
see before

[17 Jan 2024 11:27] MySQL Verification Team

Hi Mr. Chen,

Thank you very much for your bug report.

We have managed to repeat it fully:

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t1	NULL	range	idx_userid_balance	idx_userid_balance	12	const,const	1	100.00	Using where; Using temporary
Variable_name	Value
Created_tmp_disk_tables	0
Created_tmp_files	0
Created_tmp_tables	0
Variable_name	Value
Created_tmp_disk_tables	0
Created_tmp_files	0
Created_tmp_tables	0

This is a truly insignificant bug in the SHOW STATUS, but sitll a bug.

This is now a verified bug report for the version 8.0 and higher.

[17 Jan 2024 11:39] MySQL Verification Team

Hi,

Actually, we had to change the category to Performance_schema, since that is where the bug originated from:

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	t1	NULL	range	idx_userid_balance	idx_userid_balance	12	const,const	1	100.00	Using where; Using temporary
VARIABLE_NAME	VARIABLE_VALUE
Created_tmp_disk_tables	0
Created_tmp_files	0
Created_tmp_tables	0
THREAD_ID	VARIABLE_NAME	VARIABLE_VALUE
49	Created_tmp_disk_tables	0
49	Created_tmp_tables	0
VARIABLE_NAME	VARIABLE_VALUE
Created_tmp_disk_tables	0
Created_tmp_files	0
Created_tmp_tables	0
THREAD_ID	VARIABLE_NAME	VARIABLE_VALUE
49	Created_tmp_disk_tables	0
49	Created_tmp_tables	0

Verified.

[18 Jan 2024 2:30] Demon Chen

hi,
you mean it definitely uses temporary table but show command do not output this ?

[18 Jan 2024 11:22] MySQL Verification Team

Hi Mr. Chen,

Yes, that is our finding .......

If your read our Reference Manual, you will notice that there is an EXPLAIN variant that shows how was query resolved AFTER it was ran .....