h2database / h2database/h2database
Recursive CTE should be able to infer column names without explicit column list
Nobody has claimed this yet.
- 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:
- In the
UNION ALLquery's first subquery - 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
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- 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