Referencing a previous expression in a CTE causes an parsing error

[sefsen]

The CTE

WITH RECURSIVE T(N) AS (
    SELECT 1
    UNION ALL
    SELECT N+1 FROM T WHERE N<10
), U AS (
    SELECT 1
    FROM  T
    UNION ALL
    SELECT 1
)
SELECT * FROM U;

works for me with H2 version 1.4.200 without any errors.

In H2 version 2.1.214 an error is thrown during the parsing of the CTE (see dbeaver.log).

The error occurs when I reference the previous expression T in the expression U.

Is this a bug and if yes, is there a workaround available?

[katzyn]

Recursive CTEs are experimental in H2 according to documentation. De-facto non-recursive CTEs also have defective by design implementation and fixes for some edge cases sometimes produce new regressions.

This particular query can be simplified to

SELECT 1 FROM SYSTEM_RANGE(1, 11);

and it is recommended to use SYSTEM_RANGE for generation of series of values in H2.

I'm not sure about workarounds for more complex cases, there are some other issues about CTEs, maybe some workarounds are mentioned of them.

[sefsen]

The CTE in my description is just a very simplified example of my real use case. I my real use case, I do not generate a series of values. I query a data tree structure with specific restrictions.

Is it planned to support CTE's fully in future?

[lukaseder]

Possibly a duplicate of this one? https://github.com/h2database/h2database/issues/3508

[beikov]

I just ran into this issue myself. It's a bummer because until H2 version 2, recursive CTEs were at least partly usable when inlining all non-recursive CTEs as subqueries. My sample is more complex, but it simplifies down the sample that @sefsen posted. The main problem here is the use of a recursive CTE in a nested context i.e. another CTE or in a subquery:

with recursive t(n) AS(
    select 1
    UNION ALL
    select n+1 from t where n < 10
)
select u.*
from (
     select n from t
     union all
     select 1
 ) u

[katzyn]

Duplicate of #3508