adriangb opened a new issue, #26762:
URL: https://github.com/apache/datafusion/issues/26762
### Describe the bug
A window function ordered by `(total DESC, k)` fails to plan with a
`SanityCheckPlan` error when:
- `k` is a function of a column that a `WHERE` equality filter makes
constant (for example `lower(g)` under `WHERE g = 'a'`);
- a `SUM(...) OVER (PARTITION BY k)` window feeds the ordered window; and
- `target_partitions = 1`.
The optimizer seems to treat `k` as a constant and keeps the child order
`[k, total]` as enough, so it adds no sort. The sanity check then does not see
`k` as a constant and rejects the plan.
### To Reproduce
```sql
SET datafusion.execution.target_partitions = 1;
CREATE TABLE t (g VARCHAR, v BIGINT);
INSERT INTO t VALUES ('a', 1), ('b', 2);
SELECT k, DENSE_RANK() OVER (ORDER BY total DESC, k) AS r
FROM (
SELECT k, SUM(s) OVER (PARTITION BY k) AS total
FROM (SELECT lower(g) AS k, SUM(v) AS s FROM t WHERE g = 'a' GROUP BY
lower(g))
);
```
Run with `datafusion-cli -f repro.sql`. The output (plan abridged):
```text
SanityCheckPlan
caused by
Error during planning: Plan: ["BoundedWindowAggExec: wdw=[dense_rank() ORDER
BY [total DESC NULLS FIRST, k ASC NULLS LAST] ...], mode=[Sorted]",
" ProjectionExec: expr=[k@0 as k, sum(s) PARTITION BY [k] ... as total]",
" WindowAggExec: wdw=[sum(s) PARTITION BY [k] ROWS BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING ...]",
" ProjectionExec: expr=[lower(t.g)@0 as k, sum(t.v)@1 as s]",
" AggregateExec: mode=Single, gby=[lower(g@0) as lower(t.g)],
aggr=[sum(t.v)]",
" FilterExec: g@0 = a",
" DataSourceExec: partitions=1, partition_sizes=[1]"]
does not satisfy order requirements: [total@1 DESC, k@0 ASC NULLS LAST].
Child-0 order: [[k@0 ASC NULLS LAST, total@1 DESC], [k@0 ASC NULLS LAST,
total@1 ASC NULLS LAST], [k@0 ASC NULLS LAST, total@1 ASC], [k@0 ASC NULLS
LAST, total@1 DESC NULLS LAST]]
```
### Expected behavior
The query plans and returns one row, `k = 'a'`, `r = 1`.
### Additional context
What I checked:
| Change | Result |
|---|---|
| `datafusion-cli` 54.0.0 | works |
| `main` at 390d2107d1 and at 350ca76505 | fails |
| `GROUP BY g` (plain column) in place of `lower(g)` or `NULLIF(g, '')` |
works |
| No `WHERE g = 'a'` | works |
| `target_partitions` = 4 or 16 | works |
| `ROW_NUMBER()` in place of `DENSE_RANK()` | fails |
| `ORDER BY total DESC, k` at the end of the query, in place of the window |
works |
| No `SUM(...) OVER (PARTITION BY k)` window (rank the aggregate directly) |
works |
A workaround is to order the window by one struct key, which the optimizer
does not reduce to the child order: `DENSE_RANK() OVER (ORDER BY
named_struct('total', -total, 'key', k) ROWS BETWEEN UNBOUNDED PRECEDING AND
CURRENT ROW)`.
--
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]
