viirya opened a new pull request, #26721:
URL: https://github.com/apache/datafusion/pull/26721
## Which issue does this PR close?
- Closes #26720.
## Rationale for this change
A window function with `ORDER BY` and no explicit frame returns wrong
results when the first ORDER BY column is a nullable `UNIQUE` column that holds
more than one NULL. The NULL rows are peers under the default `RANGE` frame and
should all see the same value, but the planner picks a `ROWS` frame for them,
so each NULL row gets a different, order-dependent running result.
## What changes are included in this PR?
- When choosing the default window frame, only treat the first ORDER BY
column as tie-free if its functional dependency is valid across NULLs
(`FunctionalDependence::is_valid_across_nulls`). This is the same check already
used for ORDER BY key pruning (#23918) and GROUP BY / DISTINCT (#26220).
PRIMARY KEY, NOT NULL `UNIQUE` columns, and GROUP BY keys still get the `ROWS`
frame.
## What is the testing strategy for this PR?
New sqllogictest cases in `functional_dependencies.slt` ("Default window
frame" section) check both the query results and the `EXPLAIN` frame unit for:
- a nullable `UNIQUE` column with two NULL rows (now `RANGE`; the results
and plan both fail without the fix),
- a PRIMARY KEY and a NOT NULL `UNIQUE` column (still `ROWS`),
- a GROUP BY key from a nullable `UNIQUE` column, where the NULLs collapse
into one group (still `ROWS`).
## Are there any user-facing changes?
Yes: wrong results are fixed. Plans for windows ordered by a nullable
`UNIQUE` column now show a `RANGE` frame instead of `ROWS`. No public API
changes.
This pull request and its description were written by Isaac.
--
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]
