jayzhan211 opened a new issue, #27458:
URL: https://github.com/apache/datafusion/issues/27458
### Describe the bug
`nth_value(x, n ORDER BY ...)` over a hash join returns the wrong row when
the optimizer swaps the join inputs and the new join output is sorted opposite
to the `ORDER BY`. Below, query 1 returns `a` instead of `b`, while query 2 has
the same physical plan and returns `b`. The rebuilt `Partial` aggregate
reverses `nth_value`; the `Final` aggregate does not, so it reads the partial
results backwards.
### To Reproduce
```sql
-- r is sorted by ts ASC, l by k DESC; l has more rows
COPY (SELECT * FROM (VALUES (1, 'a'), (2, 'b'), (3, 'c')) AS t(ts, x)) TO
'nth/r.parquet';
COPY (SELECT * FROM (VALUES (4), (3), (2), (1), (0)) AS t(k)) TO
'nth/l.parquet';
CREATE EXTERNAL TABLE r (ts INT, x VARCHAR) STORED AS PARQUET WITH ORDER (ts
ASC) LOCATION 'nth/r.parquet';
CREATE EXTERNAL TABLE l (k INT) STORED AS PARQUET WITH ORDER (k DESC)
LOCATION 'nth/l.parquet';
-- Query 1
SELECT nth_value(r.x, 2 ORDER BY r.ts) FROM l JOIN r ON l.k = r.ts;
-- Query 2: tables in the other order
SELECT nth_value(r.x, 2 ORDER BY r.ts) FROM r JOIN l ON l.k = r.ts;
```
### Expected behavior
| Query | DataFusion returns | Expected |
|---|---|---|
| 1 | `a` | `b` |
| 2 | `b` | `b` |
Query 1 returns `b` with `SET datafusion.optimizer.join_reordering = false`.
`nth_value(r.x, 1 ...)`, `first_value`, `last_value`, `array_agg` and
`string_agg` are correct in query 1's shape.
### Additional context
<details>
<summary>EXPLAIN of queries 1 and 2: the same plan</summary>
```text
AggregateExec: mode=Final, gby=[], aggr=[nth_value(r.x,Int64(2)) ORDER BY
[r.ts ASC NULLS LAST]]
CoalescePartitionsExec
AggregateExec: mode=Partial, gby=[], aggr=[nth_value(r.x,Int64(2)) ORDER
BY [r.ts ASC NULLS LAST]]
RepartitionExec: partitioning=RoundRobinBatch(12), input_partitions=1,
maintains_sort_order=true
HashJoinExec: mode=CollectLeft, join_type=Inner, on=[(ts@0, k@0)],
projection=[ts@0, x@1]
DataSourceExec: file_groups={1 group}, projection=[ts, x],
output_ordering=[ts@0 ASC NULLS LAST], file_type=parquet
DataSourceExec: file_groups={1 group}, projection=[k],
output_ordering=[k@0 DESC], file_type=parquet, predicate=DynamicFilter [ empty
], dynamic_rg_pruning=eligible
```
</details>
- The planner builds the `Final` from the `Partial`'s expressions as first
planned (`datafusion/core/src/physical_planner.rs:2282-2306`). The join then
probes with `r`, so its output is sorted by `ts ASC`.
- `JoinSelection` swaps the join so the smaller `r` is the build side; the
output now follows `l`'s `k DESC`, i.e. `ts DESC`. Rebuilding the `Partial`
(`datafusion/physical-plan/src/aggregates/mod.rs:2177-2186`) reverses the
expression to `nth_value(x, -2 ORDER BY ts DESC)` (`:3039-3045`,
`datafusion/functions-aggregate/src/nth_value.rs:127-131`).
- The `Final` skips ordering requirements (`aggregates/mod.rs:2911-2913`),
keeps `nth_value(x, 2 ORDER BY ts ASC)`, and merges the reversed partial lists
as ascending ones (`nth_value.rs:478-488`). `EXPLAIN` shows the same name for
both.
- Fix direction: give the `Final` the rebuilt `Partial`'s expressions.
- Tested on main at 73e0b0339e (any `target_partitions` above 1); line
numbers are from that commit.
### Willingness to contribute
I'm not planning to fix this myself, so anyone is welcome to pick it up.
Feel free to ping me with questions about the cause or when you'd like a review.
--
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]