dolthub / dolthub/dolt

Dolt mis-evaluates a correlated anti-join over a derived ROW_NUMBER() result

Open
#11,548 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 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.