Bug #117249 Stored procedures error reading temporary table twice Error Code: 1137 Can't reopen table:

• Submitted: 20 Jan 15:47
• Modified: 20 Jan 16:56
• Reporter: Alessandro Falivene
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.36-u1-cloud
• OS: Any
• CPU Architecture: Any

[20 Jan 15:47] Alessandro Falivene

Description:
Calling a stored procedure with a stored function in the calling parameter

CALL TEST_Error(TestFunction1());

makes the stored procedure error with Error Code: 1137 - Can't reopen table: 'alias1'.

How to repeat:
First, create these stored procedures and function:

CREATE PROCEDURE TestCreate1()
BEGIN
DROP TEMPORARY TABLE IF EXISTS o_table1;      

CREATE TEMPORARY TABLE o_table1(column1 INT, KEY(column1));  
   
END

CREATE FUNCTION TestFunction1() RETURNS CHAR(10) 
BEGIN
RETURN '1';
END

CREATE PROCEDURE TEST_Error(_param1 CHAR(10))
BEGIN
                                                         
SELECT 1 FROM o_table1 AS alias1;        
                                                   
SELECT 1 FROM o_table1 AS alias2;
                              
END

---- ----- 
Run these 2 command:

CALL TestCreate1();
CALL TEST_Error(TestFunction1());

Error Code: 1137
Can't reopen table: 'alias1'

-- ----
If you call the stored procedure TEST_Error without the stored function TestFunction1 in the calling parameter works correctly:

CALL TestCreate1();
CALL TEST_Error('');

[20 Jan 16:56] MySQL Verification Team

Hi Mr. Falivene,

Thank you for your bug report.

However, it is not a bug.

According to the latest SQL standard, temporary table is visible only in the stored routine in which it is created.

Hence, the error message.

Not a bug.