Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.4, 5.5.32, 5.3.12
-
None
-
None
-
CentOS 6.4
Description
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