[MDEV-26985] Outside reference in recursive common table expression Created: 2021-11-06  Updated: 2021-11-08

Status: Open
Project: MariaDB Server
Component/s: None
Fix Version/s: None

Type: Task Priority: Minor
Reporter: Tadas Balaišis Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Since lack of TABLE() function, I try to use recursive CTE to emulate same functionality:

SELECT * FROM 
(SELECT 5 mx) a0,
 
(
 
WITH recursive r as
 
(SELECT 1 level 
union all
 
select LEVEL+1 FROM r where LEVEL+1<=5 -- a0.mx
 
)
 
select level FROM r
 
)

This works fine. But I'm interested in uncommented version:

SELECT * FROM 
(SELECT 5 mx) a0,
 
(
 
WITH recursive r as
 
(SELECT 1 level 
union all
 
select LEVEL+1 FROM r where LEVEL+1<=a0.mx
 
)
 
select level FROM r
 
)

or 

WITH a0 as
 
(SELECT 5 mx)
 
SELECT * FROM ( 
WITH recursive r as
 
(SELECT 1 level 
union all
 
select LEVEL+1 FROM r,a0 where LEVEL+1<=a0.mx
 
)
 
select level FROM r
 
)

to use value from outside inline view. Unfortunately both queries fail with error. Can you provide a sample query to demonstrate how can I use value from outside inline view that could be used in recursive loop exit condition?

 Comments   
Comment by Tadas Balaišis [ 2021-11-08 ]

Not sure about performance, but I switched to this workaround:

WITH a0 as
 
(SELECT 5 mx)
 
SELECT * FROM ( 
WITH recursive r as
 
(SELECT 1 level 
union all
 
select LEVEL+1 FROM r
 
)
 
select level FROM r
 
),a0 WHERE LEVEL<=mx

Generated at Thu Feb 08 09:49:27 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.