dolthub / dolthub/dolt

Dolt drops sql_select_limit

Open
#11,549 1 comment 0 reactions 0 assignees View on GitHub
analyzer bug correctness customer issue enhancement good repro sql
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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.