MySQL Bugs: #114419: SHOW COLUMNS on a temporary table in a procedure returns table doesn't exist

Bug #114419	SHOW COLUMNS on a temporary table in a procedure returns table doesn't exist
Submitted:	20 Mar 2024 0:48	Modified:	20 Mar 2024 8:42
Reporter:	Ryan Brothers	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	8.0.36	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I am running into an issue with MySQL 8.0.36 where in a procedure, if I run SHOW COLUMNS on a temporary table, I receive an error message that the table doesn't exist, even though it does.  It works correctly in MySQL 5.7.

How to repeat:
1) Run SQL:

CREATE DATABASE IF NOT EXISTS test;
USE test;
DROP PROCEDURE IF EXISTS test123;
DELIMITER $$
CREATE PROCEDURE test123 ()
BEGIN
  DROP TEMPORARY TABLE IF EXISTS abc;
  CREATE TEMPORARY TABLE IF NOT EXISTS abc (
    a int
  );

  SHOW COLUMNS FROM abc;
END$$
DELIMITER ;

2) Create a new connection and run:

CALL test.test123();

When I do this, I receive an error:

ERROR 1146 (42S02): Table 'test.abc' doesn't exist

If it works for you, try closing your connection and opening up a new connection and trying the CALL line again.

From my testing, the CALL line works correctly if I have a connection already open when I run the sql to create the procedure, but if I close that connection and open a new connection, then the error comes up.

Hello Ryan Brothers,

Thank you for the report and test case.
Verified as described.

Thanks,
Umesh