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

[24 Jul 2014 22:31] Luca Scomparin
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$$
[25 Jul 2014 19:41] Sveta Smirnova
Thank you for the report.

Verified as described. Bug is not repeatable with versions 5.5-
[16 Jul 2015 17:40] Markus Fantone
Is there any solution to this bug?
I'm facing the same problem on 5.6 MySql Instance.
[4 Apr 2023 16:29] Luca Scomparin
Verified also in 8.0
[4 Apr 2023 16:30] Luca Scomparin
As a workaround, we set

stored_program_cache 

to higher value from the standard 256.