Details
Major
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
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Sorry, I should have included that as well. Here's the other version of it without the subselect:
CREATE PROCEDURE `util`.`tmp_table_tester` ()
BEGIN
drop temporary table if exists tt_table;
create temporary table tt_table(id int not null);
select tt.* from tt_table tt;
END
Just to follow up on my comment from last night, I was able to reproduce this with MariaDB 5.5.27.
Reproducible as described. MTR-formatted test case:
--delimiter |
CREATE PROCEDURE p ()
BEGIN
select A.* from ( select tt.* from t1 tt ) A ;
END |
--delimiter ;
drop temporary table if exists t1;
create temporary table if not exists t1 (id int not null);
CALL p();
CALL p();
- End of the test case
It fails on the 2nd CALL p() with
query 'CALL p()' failed: 1146: Table 'test.t1' doesn't exist
MariaDB 5.2.14, MySQL 5.5.32, Percona 5.5.29 all return the correct result.
Remarkably, it also fails with a prepared statement, even on the first execution of one:
drop temporary table if exists t1;
create temporary table if not exists t1 (id int not null);
prepare stmt from "select A.* from ( select tt.* from t1 tt ) A ";
execute stmt;
- end of test
At line 6: query 'execute stmt' failed: 1146: Table 'test.t1' doesn't exist
It also works all right on MySQL.
TABLE_LIST::skip_temporary is set on the PS execution.
SELECT_LEX::mark_as_belong_to_derived set the flag (Why?)
The patch committed to review.
Pushed to 5.3
What do you mean "without the subselect", how does the stored procedure look in this case?