h2database / h2database/h2database

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

Open
#3,623 5 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

WITH clause
Dominant language
Java
Stars
4.6k
Forks
1.3k
Avg merge
1d 2h
Merged PRs (30d)
9

Description

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;

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

No implementation file or test is named. Start by running the recursive CTE examples against H2 and compare the behavior with the listed database systems; done means the query without an explicit CTE column list can reference its inferred column name and return the expected rows.

Written by the indexing model from the issue text.

Assessment

Tech stack
sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Clearly specified
Newbie friendliness
45/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.