viirya opened a new issue, #26720:
URL: https://github.com/apache/datafusion/issues/26720
### Describe the bug
A window function with `ORDER BY` and no explicit frame should use the
default frame `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`, under which
rows that tie on the ORDER BY key are peers and see the same result.
When the first ORDER BY column has a `UNIQUE` constraint, the SQL planner
assumes there can be no ties and switches to `ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW`. That is only equivalent if the column is also NOT NULL. A
nullable `UNIQUE` column allows several NULL rows, and those rows sort together
as peers. With the ROWS frame they get running results that depend on the order
they happen to arrive in.
### To Reproduce
```sql
CREATE TABLE t (u INT, x INT, UNIQUE(u));
INSERT INTO t VALUES (NULL, 1), (NULL, 2), (NULL, 3), (5, 10);
SELECT u, x,
sum(x) OVER (ORDER BY u NULLS FIRST) AS s,
count(*) OVER (ORDER BY u NULLS FIRST) AS c
FROM t ORDER BY u NULLS FIRST, x;
```
Actual:
```
+------+----+----+---+
| u | x | s | c |
+------+----+----+---+
| NULL | 1 | 1 | 1 |
| NULL | 2 | 3 | 2 |
| NULL | 3 | 6 | 3 |
| 5 | 10 | 16 | 4 |
+------+----+----+---+
```
`EXPLAIN` shows `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`.
### Expected behavior
The same result as without the `UNIQUE` constraint: the three NULL rows are
peers.
```
+------+----+----+---+
| u | x | s | c |
+------+----+----+---+
| NULL | 1 | 6 | 3 |
| NULL | 2 | 6 | 3 |
| NULL | 3 | 6 | 3 |
| 5 | 10 | 16 | 4 |
+------+----+----+---+
```
### Additional context
The check is in `datafusion/sql/src/expr/function.rs`
(`is_ordering_strict`): it accepts any `Dependency::Single` functional
dependency on the column without checking
`FunctionalDependence::is_valid_across_nulls`. This is the same class of bug as
#23818 (ORDER BY key pruning) and #23819 (GROUP BY / DISTINCT), which were
fixed by adding that check.
--
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]
