I create a table t1 and a temporary table t1.

SET sql_mode=DEFAULT;

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a INT, b VARCHAR(10));

INSERT INTO t1 VALUES (10,20);

CREATE TEMPORARY TABLE t1 (x INT);

INSERT INTO t1 VALUES (10);

Now I create and call a procedure that queries t1:

DROP PROCEDURE IF EXISTS p1;

DELIMITER $$

CREATE PROCEDURE p1()

BEGIN

  SELECT * FROM t1;

END;

$$

DELIMITER ;

CALL p1();

It uses the temporary table t1 (rather than the permanent table) and returns this result:

+------+

| x    |

+------+

|   10 |

+------+

So far so good.

Now I drop the temporary table and call the procedure again.

DROP TEMPORARY TABLE t1;

CALL p1();

It returns an error:

ERROR 1054 (42S22): Unknown column 'test.t1.x' in 'field list'

Notice, it still tries to use the column x which belonged to the temporary table and which does not exist in the permanent table. This looks wrong.

Note, if I now run a stand-alone SELECT query outside of a routine, it works fine:

SELECT * FROM t1;

+------+------+

| a    | b    |

+------+------+

|   10 | 20   |

+------+------+

The second CALL is expected to return the same result.