-
Notifications
You must be signed in to change notification settings - Fork 1.3k
Description
The SQL standard allows for nesting WITH clauses in derived tables. Through a chain of grammar specifications, which I won't list in its entirety, we can see the following recursion:
<query expression> ::=
[ <with clause> ] <query expression body>
[ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ]
...
<subquery> ::=
<left paren> <query expression> <right paren>
This can be used as follows (e.g. in PostgreSQL):
with a(x) as(
with b(y) as(
select 1 one
)
select y from b
)
select x from aOr also:
select x from (
with a(x) as(
select 1 one
)
select * from a
) aUnfortunately, neither syntax is accepted by H2.
Running the second statement hints at the bug, which seems to be due to some table alias being lost in the transformation from the CTE version to the inlined view version:
Column "A.X" not found; SQL statement:
CREATE FORCE VIEW PUBLIC._24 AS
SELECT
A.X
FROM (
SELECT
1 AS ONE
FROM SYSTEM_RANGE(1, 1)
) [42122-196]
Note that the syntax seems to be generally supported, this is simply a SQL transformation bug. Hence, I believe that the documentation should also be updated. It lists WITH as a top-level statement. The grammar doesn't seem to allow WITH in derived tables:
http://h2database.com/html/grammar.html#with