After calling a SP which uses a temporary table in a sub query, I am unable to call the same stored procedure a second (or any time after) time. It appears that the query engine thinks the temporary table is in fact a real table and it reports an error when it is unable to find it.

Using this stored procedure:

CREATE PROCEDURE `util`.`tmp_table_tester_sub` ()

BEGIN

drop temporary table if exists tt_table;

create temporary table if not exists tt_table(id int not null);

select A.*

from

(

	select tt.*

	from tt_table tt

)A

;

END

I can call it once and get a valid result set. On the second (and all subsequent) calls I get this error: "Error Code: 1146. Table 'util.tt_table' doesn't exist" - note that it's trying to find the temporary table in the schema of the procedure.

Without the subselect I can run the SP many times without problems.

Resetting the connection (by reconnecting in workbench) allows one execution to complete successfully, then the same exception.

I can verify that this stored procedure works in Percona Server 5.5.27-28.1. I'm downloading MariaDB 5.5.27 for comparison, but it's going so slowly that I'll have to wait until tomorrow. In either event, I don't see a reason that a subquery should impact the rest of the session.

Please let me know if any other information would be useful,

Jeff