Dolt: `DISTINCT` window output with a composite outer sort reaches a stale field index
- Dominant language
- Go
- Stars
- 24.4k
- Forks
- 873
- Avg merge
- 1d 8h
- Merged PRs (30d)
- 120
Description
## What happened
`DISTINCT` window output with a composite outer sort reaches a stale field index.
Removing `DISTINCT` returns the expected rows.
## Environment
Dolt main commit `59fb843bf6a4b653d7c8b6d997a603b10cf279d9` (`dolt version 2.2.3`).
## How to reproduce
```sql
CREATE TABLE t(id INT PRIMARY KEY,g INT NOT NULL,k INT NOT NULL);
INSERT INTO t VALUES (1,0,0),(2,0,1),(3,0,2);
SELECT DISTINCT g,
RANK() OVER (PARTITION BY g ORDER BY k ASC) AS r
FROM t
ORDER BY g, 2;
```
The syntax is legal: `2` is the ordinal of the second projected column.
## Expected result
MySQL 8.0.43 fresh run:
The exact expected rows are:
```text
g r
0 1
0 2
0 3
```
This is also the result of an independent rank calculation followed by `DISTINCT` and the same outer ordering.
## Actual result
Dolt produces no rows and returns the same internal error in all three runs:
```text
unable to sort: unable to find field with index 4 in row of 2 columns.
This is a bug. Please file an issue here: https://github.com/dolthub/dolt/issues
```
Removing `DISTINCT` returns the expected rows.
Contributor guide
No contributing guide indexed for this repository
Research direction
Start by running the provided SQL reproduction on Dolt and compare it with the MySQL result. Trace the DISTINCT, window-function ranking, and composite outer ORDER BY handling, then add a regression test for the query. Done means the query returns the three expected rows without the stale field-index error.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 65/100