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
-
Activity
igor and sanja, this was apparently pushed to 10.2.
I failed to find a 10.3 or 10.4 version of this. On merge to 10.3 I had to resolve numerous conflicts, but the tests passed for me. On an attempted merge to 10.4, I was less lucky, and 3 tests fail. Please fix.
This was pushed separately to 10.4. It looks like some adjustment for 10.5 is needed too.
This was pushed separately to 10.5 and finally merged to 10.6.
A follow-up fix seems to be necessary. A DBUG_ASSERT(false) statement was removed from the following function, but the comment was not adjusted to say when the member function is expected to be invoked:
double Item_in_subselect::val_real() |
{
|
/* |
As far as Item_in_subselect called only from Item_in_optimizer this
|
method should not be used
|
*/ |
A fix for this bug was pushed into 10.2. Then an adjusted fix was pushed into 10.4. Another variant was pushed into 10.5.
OK to push