Window functions with ORDER BY on a non-output column return rows in nondeterministic order
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Bug Report
Please answer these questions before submitting your issue. Thanks!
### 1. Minimal reproduce step (Required)
```
CREATE TABLE wm(v INT, g INT);
INSERT INTO wm VALUES (1,1),(2,1),(5,1),(3,2),(3,2);
SELECT v, COUNT(v) OVER (PARTITION BY g ORDER BY v) c FROM wm ORDER BY g, v;
-- row order varies between executions (expected: 1,2,5,3,3)
```
### 2. What did you expect to see? (Required)
When the outer ORDER BY keys exactly match the window's internal sort keys (all ASC) and reference a column absent from the SELECT list, the planner eliminates the outer Sort (EXPLAIN: 1 Sort in the ASC plan vs 2 in the DESC plan), but the parallel Window executor (Shuffle) does not preserve order. Measured: 2 orderings in 12 runs at 5 rows; up to 6 distinct orderings in 6 runs at 500 rows. Affects all window functions. ORDER BY g DESC, v keeps the Sort and is always correct.
(expected: 1,2,5,3,3)
### 3. What did you see instead (Required)
```
mysql> SELECT v, COUNT(v) OVER (PARTITION BY g ORDER BY v) c FROM wm ORDER BY g, v;
+------+------+
| v | c |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 5 | 3 |
| 3 | 2 |
| 3 | 2 |
+------+------+
5 rows in set (0.00 sec)
mysql> SELECT v, COUNT(v) OVER (PARTITION BY g ORDER BY v) c FROM wm ORDER BY g, v;
+------+------+
| v | c |
+------+------+
| 3 | 2 |
| 3 | 2 |
| 1 | 1 |
| 2 | 2 |
| 5 | 3 |
+------+------+
5 rows in set (0.00 sec)
```
### 4. What is your TiDB version? (Required)
```
mysql> SELECT tidb_version()
-> ;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v9.0.0-beta.2.pre-2174-g65ac2fad58-dirty
Edition: Community
Git Commit Hash: 65ac2fad582510b92d3c4b79999e48217c989737
Git Branch: master
UTC Build Time: 2026-08-30 15:18:00
GoVersion: go1.25.12
Race Enabled: false
Check Table Before Drop: false
Store: unistore
Kernel Type: Classic |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
```
Contributor guide
Research direction
Reproduce the query repeatedly and compare the ASC and DESC EXPLAIN plans, then trace the planner's outer Sort elimination and the parallel Window executor (Shuffle). Done means ORDER BY g, v remains deterministic when g and v are absent from the SELECT list, with coverage for the reported window-function case.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 45/100