Skip to content

Unknown column when nesting WITH clause in WITH clause or in derived table #821

@lukaseder

Description

@lukaseder

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 a

Or also:

select x from (
  with a(x) as(
    select 1 one
  )
  select * from a
) a

Unfortunately, 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

Metadata

Metadata

Assignees

No one assigned

    Labels

    WITH clauseRelated to the WITH clause implementation.

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions