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
Activity
| Field | Original Value | New Value |
|---|---|---|
| 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 |
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: {code:sql} 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 ; {code} {code:sql} 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 ; {code} {code:sql} #Executes successfully call test.testMariaBug(); #fails with Error Code: 1146. Table 'test.tmpValues' doesn't exist call test.testMariaBug(); {code} --------------------- 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 |
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Fix Version/s | 10.0 [ 16000 ] | |
| Fix Version/s | 10.1 [ 16100 ] | |
| Affects Version/s | 10.0 [ 16000 ] | |
| Affects Version/s | 10.1 [ 16100 ] | |
| Labels | upstream |
| Remote Link | This issue links to "Bug #68972 Can't find temporary table (Web Link)" [ 25703 ] |
| Labels | upstream | upstream-fixed |
| Assignee | Sergei Golubchik [ serg ] |
| Status | Confirmed [ 10101 ] | In Progress [ 3 ] |
| Status | In Progress [ 3 ] | Stalled [ 10000 ] |
| Component/s | Data Definition - Temporary [ 10123 ] | |
| Component/s | Stored routines [ 13905 ] | |
| Component/s | Data Manipulation - Subquery [ 10107 ] | |
| Fix Version/s | 10.0.29 [ 22312 ] | |
| Fix Version/s | 10.1.21 [ 22113 ] | |
| Fix Version/s | 10.0 [ 16000 ] | |
| Fix Version/s | 10.1 [ 16100 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Link |
This issue is duplicated by |
| Workflow | MariaDB v3 [ 72452 ] | MariaDB v4 [ 149794 ] |