MySQL Bugs: #118147: Error When Reopening Temporary Table

Bug #118147	Error When Reopening Temporary Table
Submitted:	9 May 3:18	Modified:	9 Jun 13:10
Reporter:	Chenglin Tian	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	9.2.0	OS:	Linux
Assigned to:		CPU Architecture:	x86

Description:
I encountered an error while executing a SQL query that attempts to reopen a temporary table (temp_table) in MySQL. The error message states: "Can't reopen table: 'temp_table'" when trying to run a SELECT statement that counts the rows and calculates the variance of string lengths. 

How to repeat:
CREATE TEMPORARY TABLE temp_table AS SELECT LOWER(CONCAT('Test_', REPLACE('String with punctuation!@#$%^&*()', ' ', '_'))) AS transformed_string;

SHOW CREATE TABLE temp_table;

INSERT INTO temp_table VALUES (NULL), ('ExtremeValue1'), ('BoundaryValue2');

INSERT INTO temp_table VALUES ('Some_Value_123'), ('Another_Value_456!@#');

Error executing SQL:
SELECT (SELECT COUNT(*) FROM temp_table) AS count_of_rows, (SELECT VAR_SAMP(LENGTH(transformed_string)) FROM temp_table) AS variance_of_length
Error Message:(1137, "Can't reopen table: 'temp_table'")

Hello Chenglin Tian,

Thank you for the bug report.
Imho this is not a bug, quoting from the manual - 

You cannot refer to a TEMPORARY table more than once in the same query. 

For more info please see https://dev.mysql.com/doc/refman/9.2/en/temporary-table-problems.html

Regards,
Ashwini Patil