Dolt drops sql_select_limit
- Dominant language
- Go
- Stars
- 24.4k
- Forks
- 873
- Avg merge
- 1d 5h
- Merged PRs (30d)
- 108
Description
## What happened
Dolt returns all four rows for a legal `SELECT DISTINCT` whose projection is a `ROW_NUMBER()` window, even though the session `sql_select_limit` is `2`. Unsure whether this is ignored or just unsupported?
## 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, g INT NOT NULL);
INSERT INTO t VALUES (1, 0), (2, 0), (3, 1), (4, 1);
SET @@sql_select_limit = 2;
SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY id) AS rn
FROM t
ORDER BY rn;
```
## Expected Result
MySQL defines `sql_select_limit` as the maximum number of rows returned by a `SELECT`; an explicit `LIMIT` takes precedence. Window evaluation occurs before `ORDER BY`, `LIMIT`, and `SELECT DISTINCT`, so the four row numbers are formed, the result is distinct, and the first two ordered rows are returned. See the
[MySQL `sql_select_limit` documentation](https://dev.mysql.com/doc/refman/8.4/en/server-system-variables.html) and [MySQL window execution order](https://dev.mysql.com/doc/refman/8.4/en/window-functions-usage.html).
The independent SQLite 3 oracle, run with an equivalent explicit `LIMIT 2`, returns:
```text
rn
1
2
```
Contributor guide
No contributing guide indexed for this repository
Research direction
No source file or test is named. Start by reproducing the query in a fresh Dolt repository, then trace the SQL execution path for session sql_select_limit with DISTINCT and window functions. Done means the query returns only rows 1 and 2, while an explicit LIMIT still takes precedence.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, mysql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 68/100