[MDEV-5005] Subquery in Procedure somehow affecting temporary table Created: 2013-09-10  Updated: 2013-09-18  Due: 2013-10-10  Resolved: 2013-09-18

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.4, 5.5.32, 5.3.12
Fix Version/s: 10.0.5, 5.5.33a, 5.3.13

Type: Bug Priority: Major
Reporter: Jeff Scott Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: None
Environment:

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

 Comments   
Comment by Sergei Golubchik [ 2013-09-10 ]

What do you mean "without the subselect", how does the stored procedure look in this case?

Comment by Jeff Scott [ 2013-09-10 ]

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

Comment by Jeff Scott [ 2013-09-10 ]

Just to follow up on my comment from last night, I was able to reproduce this with MariaDB 5.5.27.

Comment by Elena Stepanova [ 2013-09-10 ]

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();

  1. 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;

  1. end of test

At line 6: query 'execute stmt' failed: 1146: Table 'test.t1' doesn't exist

It also works all right on MySQL.

Comment by Oleksandr Byelkin [ 2013-09-12 ]

TABLE_LIST::skip_temporary is set on the PS execution.

Comment by Oleksandr Byelkin [ 2013-09-12 ]

SELECT_LEX::mark_as_belong_to_derived set the flag (Why?)

Comment by Oleksandr Byelkin [ 2013-09-12 ]

The patch committed to review.

Comment by Oleksandr Byelkin [ 2013-09-13 ]

Pushed to 5.3

Generated at Thu Feb 08 07:00:55 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.