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
-
MDEV-11038 Error creating temporary tables in procedures
- Closed
- links to