Dolt mis-evaluates a correlated anti-join over a derived ROW_NUMBER() result
- Dominant language
- Go
- Stars
- 24.4k
- Forks
- 873
- Avg merge
- 1d 5h
- Merged PRs (30d)
- 108
Description
## What happened
Dolt evaluates a legal `NOT EXISTS` predicate incorrectly when its correlated equality compares an outer row to the `id` projected by a derived table that also computes `ROW_NUMBER()`. The window must rank both input rows before the correlated predicate is applied. Dolt instead behaves as if the derived window sees only the one row selected by the outer `id` lookup, so every row receives `rn = 1` and the anti-join removes every outer row.
The witness uses only ordinary signed `INT` values, a legal `ORDER BY`, and a legal `ROW_NUMBER` window. It does not use CAST, JSON, BIGINT, floating point, precision boundaries, GIS, binary values, or type coercion.
## Environment
Dolt main (commit `c3b5ce3c67f8677ca08a0a58d8c03cdc95bff8b7`). MySQL version 8.0.43.
## How to reproduce
Run the corresponding SQL in a fresh Dolt repository.
```sql
CREATE TABLE t(id INT PRIMARY KEY, k INT);
INSERT INTO t VALUES (1, 10), (2, 20);
SELECT o.id
FROM t AS o
WHERE NOT EXISTS (
SELECT 1
FROM (
SELECT id, ROW_NUMBER() OVER (ORDER BY k, id) AS rn
FROM t
) AS w
WHERE w.id = o.id AND w.rn <= 1
)
ORDER BY o.id;
```
## Expected Result
The two input rows sort as `(id,k) = (1,10),(2,20)`. Therefore the derived window has `rn = 1` for `id=1` and `rn = 2` for `id=2`. The `NOT EXISTS` condition removes only `id=1`, so the expected result is:
```text
id
2
```
## Dolt actual result
Dolt exits successfully but returns an empty result set:
```text
id
```
Contributor guide
No contributing guide indexed for this repository
Research direction
Start with the SQL reproduction in a fresh Dolt repository and compare the derived ROW_NUMBER() result with the correlated NOT EXISTS behavior. Trace the database execution path for correlated predicates over derived tables and window functions, then add a regression test whose result is the single row with id 2.
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
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 52/100