MySQL Bugs: #73381: Can't find temp table created in stored proc. using CREATE TEMPORARY TABLE AS

Bug #73381	Can't find temp table created in stored proc. using CREATE TEMPORARY TABLE AS
Submitted:	24 Jul 2014 22:31	Modified:	4 Apr 2023 16:30
Reporter:	Luca Scomparin	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S1 (Critical)
Version:	5.6.19, 5.6.21, 5.7.5, 8.0	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	Can't find table stored procedure create as, regression

Description:
Calling more than once a procedure that:

1) creates a temporary table 

2) tries to create a second temporary table using "CREATE TEMPORARY TABLE .. AS" using the first temporary table previously created in 1) in a subquery and calling a stored function in the top level of the select query

raise the error "can't find temporary table" while we can access it just after the failing call.

How to repeat:
As an example:

call test(); -- OK
call test(); -- table "ttest" doesn't exists

STORED PROCEDURE TEXT:

CREATE PROCEDURE `test`()
BEGIN
   DROP TEMPORARY TABLE IF EXISTS ttest;
   CREATE TEMPORARY TABLE ttest AS
       SELECT 1 AS col_a; 

   DROP TEMPORARY TABLE IF EXISTS testb;
   CREATE TEMPORARY TABLE testb AS
     SELECT test2() AS col_b
     FROM (SELECT 1 FROM ttest) z;

END$$

CREATE FUNCTION `test2`() RETURNS DOUBLE
BEGIN
   RETURN 0; 
END$$

Thank you for the report.

Verified as described. Bug is not repeatable with versions 5.5-

Is there any solution to this bug?
I'm facing the same problem on 5.6 MySql Instance.

Verified also in 8.0

As a workaround, we set

stored_program_cache 

to higher value from the standard 256.