[MDEV-5005] Subquery in Procedure somehow affecting temporary table - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.0.4, 5.5.32, 5.3.12
Fix Version/s: 10.0.5, 5.5.33a, 5.3.13
Component/s: None
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

Attachments

Activity

Ascending order - Click to sort in descending order

Jeff Scott created issue - 2013-09-10 04:27

Sergei Golubchik made changes - 2013-09-10 15:37

Field	Original Value	New Value
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	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: {noformat} 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 {noformat} 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

Sergei Golubchik added a comment - 2013-09-10 15:39

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

Sergei Golubchik added a comment - 2013-09-10 15:39 What do you mean "without the subselect", how does the stored procedure look in this case?

Sergei Golubchik made changes - 2013-09-10 15:40

Due Date

2013-10-10

Jeff Scott added a comment - 2013-09-10 17:20

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

Jeff Scott added a comment - 2013-09-10 17:20 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

Sergei Golubchik made changes - 2013-09-10 17:48

Assignee

Elena Stepanova [ elenst ]

Jeff Scott added a comment - 2013-09-10 17:56

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

Jeff Scott added a comment - 2013-09-10 17:56 Just to follow up on my comment from last night, I was able to reproduce this with MariaDB 5.5.27.

Elena Stepanova added a comment - 2013-09-10 19:49

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.

Elena Stepanova added a comment - 2013-09-10 19:49 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.

Elena Stepanova made changes - 2013-09-10 19:49

Fix Version/s		10.0.5 [ 13201 ]
Fix Version/s		5.5.34 [ 13500 ]
Fix Version/s		5.3.13 [ 12602 ]
Affects Version/s		5.3.12 [ 12000 ]
Affects Version/s		10.0.4 [ 13101 ]
Assignee	Elena Stepanova [ elenst ]	Oleksandr Byelkin [ sanja ]

Oleksandr Byelkin made changes - 2013-09-12 09:48

Status

Open [ 1 ]

In Progress [ 3 ]

Oleksandr Byelkin made changes - 2013-09-12 11:44

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Oleksandr Byelkin made changes - 2013-09-12 14:22

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Oleksandr Byelkin added a comment - 2013-09-12 14:48

TABLE_LIST::skip_temporary is set on the PS execution.

Oleksandr Byelkin added a comment - 2013-09-12 14:48 TABLE_LIST::skip_temporary is set on the PS execution.

Oleksandr Byelkin made changes - 2013-09-12 14:51

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Oleksandr Byelkin made changes - 2013-09-12 16:09

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Oleksandr Byelkin added a comment - 2013-09-12 16:16

SELECT_LEX::mark_as_belong_to_derived set the flag (Why?)

Oleksandr Byelkin added a comment - 2013-09-12 16:16 SELECT_LEX::mark_as_belong_to_derived set the flag (Why?)

Oleksandr Byelkin added a comment - 2013-09-12 17:10

The patch committed to review.

Oleksandr Byelkin added a comment - 2013-09-12 17:10 The patch committed to review.

Oleksandr Byelkin made changes - 2013-09-12 17:10

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Oleksandr Byelkin added a comment - 2013-09-13 10:20

Pushed to 5.3

Oleksandr Byelkin added a comment - 2013-09-13 10:20 Pushed to 5.3

Oleksandr Byelkin made changes - 2013-09-18 12:36

Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2014-06-13 15:06

Workflow

defaullt [ 28904 ]

MariaDB v2 [ 44580 ]

Rasmus Johansson (Inactive) made changes - 2015-05-18 17:51

Workflow

MariaDB v2 [ 44580 ]

MariaDB v3 [ 63936 ]

Sergei Golubchik made changes - 2021-12-06 21:39

Workflow

MariaDB v3 [ 63936 ]

MariaDB v4 [ 147024 ]

People

Assignee:: Oleksandr Byelkin

Reporter:: Jeff Scott

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Due:: 2013-10-10

Created:: 2013-09-10 04:27

Updated:: 2013-09-18 12:36

Resolved:: 2013-09-18 12:36

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Activity

People

Dates

Git Integration