Windowed recursive CTE names collide with a differently cased base table
- Dominant language
- Go
- Stars
- 24.4k
- Forks
- 873
- Avg merge
- 1d 8h
- Merged PRs (30d)
- 120
Description
## What happened
Dolt exhibits this behavior: windowed recursive CTE names collide with a differently cased base table. Not sure whether this is intended.
## Environment
Dolt main commit `59fb843bf6a4b653d7c8b6d997a603b10cf279d9` (`dolt version 2.2.3`).
## How to reproduce
```sql
CREATE TABLE mytable(i INT);
INSERT INTO mytable VALUES (1),(2),(3);
WITH RECURSIVE MYTABLE(j) AS (
SELECT 2
UNION
SELECT MYTABLE.j
FROM MYTABLE JOIN mytable ON MYTABLE.j = mytable.i
)
SELECT FIRST_VALUE(j) OVER () AS j
FROM MYTABLE;
```
## Expected result
MySQL 8.0.43 fresh run:
```text
j
2
```
MySQL treats the CTE name and the base-table name as case-sensitive in this scope. The final window expression returns one row, `2`.
## Actual result
Dolt instead reports:
```text
error on line 1 for query WITH RECURSIVE MYTABLE(j) AS ( SELECT 2 UNION SELECT MYTABLE.j FROM MYTABLE JOIN mytable ON MYTABLE.j = mytable.i ) SELECT FIRST_VALUE(j) OVER () AS j FROM MYTABLE: ambiguous column name "j", it's present in all these tables: [mytable mytable]
```
Contributor guide
No contributing guide indexed for this repository
Research direction
Start by running the supplied recursive CTE query against Dolt at commit 59fb843bf6a4b653d7c8b6d997a603b10cf279d9 and compare it with MySQL 8.0.43. Trace name resolution for the differently cased MYTABLE CTE and mytable base table, then add or run a regression test for this query. Done means Dolt returns the expected single row, 2, without an ambiguous-column error.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, mysql, sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 52/100