Recursive CTE should be able to infer column names without explicit column list

[lukaseder]

This query works in MySQL, PostgreSQL, SQLite, SQL Server (without recursive) but not in H2:

with recursive 
  t as (
    select 1 a
    union all
    select t.a + 1
    from t 
    where t.a < 10
  )
select * from t;

In H2, in order to reference columns from the recursive tables, they have to be declared twice:

1. In the UNION ALL query's first subquery
2. In the column list of the CTE

with recursive 
  t (a) as (
    select 1 a
    union all
    select t.a + 1
    from t 
    where t.a < 10
  )
select * from t;

That seems like an unnecessary restriction to me, given that the column name is derived correctly outside of the CTE. This works in H2 as well:

with 
  t as (
    select 1 a
  )
select a from t;

[katzyn]

I think H2 may require them according to the Standard.

7.17 <query expression>, Syntax Rules:

j) If WC is potentially recursive, then:
    i) Every <with list element> contained in WC shall contain a <with column list>.

But there is a bigger problem. Our implementation of CTEs, even of non-recursive CTEs, has significant design flaws. They need to be reimplemented in a some better way and this task requires a lot of work.

[lukaseder]

I wonder what kind of limitation or ambiguity this standard rule addresses. It's often better to be explicit, but it's also convenient if one doesn't have to be...

[katzyn]

It is definitely simpler to require them for the parser and query compiler, but I have no idea are there other reasons or it was the only reason to include this requirement into the Standard.

[manticore-projects]

Still relevant after @katzyn's work on WITH and CTEs, I have just tested against https://github.com/h2database/h2database/commit/44974624bcaa8267dbf26fc1fc65d33b3dd28bf4

[katzyn]

This is neither a bug nor a limitation. It's just a feature request.