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
What do you mean "without the subselect", how does the stored procedure look in this case?