Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.5.5, 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
-
None
Description
When I try to use a single CTE multiple times inside a stored function, the function fails with
ERROR 1146 (42S02): Table 'test.testtab' doesn't exist
DROP TABLE IF EXISTS testtab;
|
CREATE TABLE testtab (c1 int(11) );
|
INSERT INTO testtab VALUES (1),(2),(6);
|
|
DROP FUNCTION IF EXISTS TESTFUN;
|
DELIMITER |
|
CREATE FUNCTION TESTFUN() RETURNS INT DETERMINISTIC BEGIN
|
RETURN (
|
WITH
|
cte1 as (select c1 from testtab)
|
-- here
|
SELECT SUM(c1) FROM ( SELECT * FROM cte1 UNION ALL SELECT * FROM cte1 ) as u1
|
);
|
END|
|
DELIMITER ;
|
SELECT TESTFUN();
|
Returns -> ERROR 1146 (42S02): Table 'test.testtab' doesn't exist
Expected -> 18
Running the same query without wrapping it in a function works fine:
WITH
|
cte1 as (select c1 from testtab)
|
-- here
|
SELECT SUM(c1) FROM ( SELECT * FROM cte1 UNION ALL SELECT * FROM cte1 ) as u1;
|
Returns -> 18
The same error occurs when:
- CTE is self-joined in a query - inside a stored function
- multiple branches of a CASE ... END use it in tests: WHEN EXISTS (SELECT NULL FROM CTE WHERE X=Y) THEN "result 1" WHEN EXISTS ( SELECT NULL FROM CTE ) THEN "result 2" END - inside a stored function
Attachments
Issue Links
- blocks
-
-
- Confirmed
-
- is duplicated by
-
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-