jayzhan211 commented on issue #26762:
URL: https://github.com/apache/datafusion/issues/26762#issuecomment-6703016084
@alamb a second `SanityCheckPlan` regression is on `branch-56`, and #27191
fixes it along with this issue. Can it count as part of this issue on the 56
list, rather than needing its own? The cause is different: it comes from #26747
and involves no constant.
A window ordered by `(k, c)` fails when its input is a `LIMIT` query ordered
by `(k, a, c)`, the filter says `a = CAST(c AS DATE)`, and the query doesn't
select `a`. Changing any one of those conditions avoids the error, so the shape
is narrow.
```sql
CREATE TABLE t (k VARCHAR, a DATE, c TIMESTAMP) AS VALUES
('x', DATE '2026-01-02', TIMESTAMP '2026-01-02 05:00:00'),
('x', DATE '2026-01-01', TIMESTAMP '2026-01-01 09:00:00'),
('x', DATE '2026-01-02', TIMESTAMP '2026-01-02 01:00:00'),
('y', DATE '2026-01-03', TIMESTAMP '2026-01-03 01:00:00');
SELECT k, c, ROW_NUMBER() OVER (ORDER BY k, c) AS r
FROM (SELECT k, a, c FROM t WHERE a = CAST(c AS DATE) ORDER BY k, a, c LIMIT
10)
ORDER BY r;
```
| Build | Result |
|---|---|
| Just before #26747 | 4 rows, with `r` from 1 to 4 in `(k, c)` order |
| #26747 (found with `git bisect`), `branch-56`, `main` | `SanityCheckPlan`
error |
| #27191 | Same 4 rows |
<details>
<summary>Cause, and the query changes that avoid the error</summary>
Since #26747, a sort with a fetch keeps its own keys when they satisfy its
parent (`pushdown_sorts_helper`). Its comment gives the reason: replacing the
keys can change which rows the `LIMIT` keeps. So the TopK now stays on `(k, a,
c)`, which proves `(k, c)` through `a = CAST(c AS DATE)`, and the window's sort
is removed below the projection. The projection then drops `a`, and above it
only `(k)` can be proven:
```text
BoundedWindowAggExec: ORDER BY [k, c], mode=[Sorted]
ProjectionExec: k, c
SortExec: TopK(fetch=10), expr=[k, a, c]
```
#27191 keeps the sort above a projection that can't prove the ordering its
input has.
Each of these changes alone makes the query work on `main`:
| Change | Result |
|---|---|
| Also select `a` | Works |
| Remove `WHERE a = CAST(c AS DATE)` | Works |
| Remove `LIMIT 10` | Works |
| Replace the window with an outer `ORDER BY k, c` | Works |
The results are the same at the default `target_partitions` and at 1.
</details>
--
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]
