Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.21, 10.0(EOL), 10.1(EOL)
-
CentOS 7.1
Description
Using a Stored Procedure, creating a table and populating the table in a single statement, using a nested select as virtual table for the data, and having that nested select obtain data from a temporary table and call a stored function, causes the references to the temporary table to be lost on all runs after the first. Sequential calls fail with "1146 .Table test,tmpValues doesn't exist'
Code to setup the test and duplicate as follows:
USE `test`; |
DROP procedure IF EXISTS `testMariaBug`; |
|
|
DELIMITER $$
|
USE `test`$$ |
CREATE DEFINER=`root`@`localhost` PROCEDURE `testMariaBug`() |
BEGIN
|
DROP TABLE IF EXISTS tmpValues; |
CREATE temporary TABLE tmpValues (`value` INT NOT NULL ) |
select 1 `value`; |
|
drop table if exists tmpOutput; |
create table tmpOutput ( |
`COL` varchar(45) |
)
|
select distinct |
COL
|
from |
(
|
select |
test.TestFunction() as COL |
from |
tmpValues i
|
) t;
|
END$$ |
|
|
DELIMITER ;
|
USE `test`; |
DROP function IF EXISTS `TestFunction`; |
|
|
DELIMITER $$
|
USE `test`$$ |
CREATE DEFINER=`root`@`localhost` FUNCTION `TestFunction`( |
) RETURNS text CHARSET latin1 |
BEGIN
|
|
|
return 'blah'; |
|
END$$ |
|
|
DELIMITER ;
|
#Executes successfully
|
call test.testMariaBug();
|
|
|
#fails with Error Code: 1146. Table 'test.tmpValues' doesn't exist |
call test.testMariaBug();
|
---------------------
Work arounds exist as follows:
- make the nested select table non temporary
- place another table in the table list of the parent query, in front of the select table (t)
- remove the function reference and replacing it with a constant or value from the temporary table
Attachments
Issue Links
- is duplicated by
-
-
- Closed
-
- links to
MarkP,
Thanks for the report and the test case.
Apparently, we somehow inherited this problem from upstream MySQL – it's also present in 5.6 and 5.7. The corresponding upstream report is https://bugs.mysql.com/bug.php?id=68972.
Here is the revision where the problem appeared in MySQL:
commit c2efb0c2962f91f5d2aaaf7553c90e66b892b8c0Author: Dmitry Lenev <Dmitry.Lenev@oracle.com>Date: Fri Apr 1 22:08:48 2011 +0400One more follow-up for the patch for Bug#1174660227480: Extend CREATE TEMPORARY TABLES privilege toallow temp table operations).Changed code not to produce unwarranted ER_CANT_REOPEN errorsin cases when prepared statement which used some table twice,directly and indirectly, was re-executed in presence ofshadowing temporary table. Now, as expected, such statementsare re-prepared after validation in these cases.Adjusted code checking privileges for tables from UNIONclauses in CREATE TABLE and ALTER TABLE to make thesestatements re-execution safe.