alamb opened a new issue, #27267:
URL: https://github.com/apache/datafusion/issues/27267
### Describe the bug
A query that computes `MAX(x) OVER (ORDER BY ts)` and then sorts on that
column with `ASC NULLS LAST` returns rows in the wrong order when `x` contains
NULLs. The NULL row is returned first, where the query asked for it last.
The same wrong order appears for:
- `MIN(x) OVER (ORDER BY ts)` sorted `DESC NULLS LAST`
- `MAX(x) OVER (ORDER BY ts ROWS BETWEEN CURRENT ROW AND UNBOUNDED
FOLLOWING)` sorted `DESC NULLS FIRST`
### To Reproduce
```sql
set datafusion.execution.target_partitions = 1;
CREATE TABLE t(ts INT, x INT) AS VALUES (1, NULL), (2, 3), (3, 1), (4, 5);
SELECT ts, m
FROM (
SELECT ts, MAX(x) OVER (ORDER BY ts) AS m
FROM (SELECT * FROM t ORDER BY ts)
)
ORDER BY m ASC NULLS LAST;
```
Returns:
```text
+----+------+
| ts | m |
+----+------+
| 1 | NULL |
| 2 | 3 |
| 3 | 3 |
| 4 | 5 |
+----+------+
```
### Expected behavior
`NULLS LAST` means the NULL row comes after every non-NULL value:
```text
+----+------+
| ts | m |
+----+------+
| 2 | 3 |
| 3 | 3 |
| 4 | 5 |
| 1 | NULL |
+----+------+
```
### Additional context
`EXPLAIN` shows the final sort on `m` is removed, so the planner believes
the window output already satisfies `m ASC NULLS LAST`. Reproduced on main at
94486b1e93.
Found by @jayzhan211 while reviewing the `EquivalenceProperties` docs in
#27250. The original report and repro are in this review comment:
https://github.com/apache/datafusion/pull/27250#discussion_r4512590554
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
