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]

Reply via email to