dolthub / dolthub/dolt

Windowed recursive CTE names collide with a differently cased base table

Open
#11,513 0 comments 0 reactions 0 assignees View on GitHub
bug correctness customer issue good repro sql
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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.