cool9850311 opened a new pull request, #27532:
URL: https://github.com/apache/datafusion/pull/27532

   ## Which issue does this PR close?
   
   Closes #27457.
   
   ## Rationale for this change
   
   A window function with `PARTITION BY` returns wrong values under a `LIMIT` 
when its input has more than one partition:
   
   ```sql
   SET datafusion.execution.target_partitions = 4;
   SET datafusion.execution.batch_size = 1;
   SET datafusion.optimizer.repartition_file_scans = false;
   CREATE TABLE t1 (a INT, b INT) AS VALUES (5, 1), (0, 1), (0, 2), (0, 3);
   CREATE TABLE t2 (a INT, b INT) AS VALUES (5, 2);
   
   SELECT a, b, row_number() OVER (PARTITION BY a ORDER BY b ROWS UNBOUNDED 
PRECEDING) AS rn
   FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2) LIMIT 3;
   -- returns a row (5, 2, 1); (5, 2) has rn = 2 in the full result
   ```
   
   `LimitPushPastWindows` sets the fetch on the `SortExec` below the window, 
and a partition-preserving sort with a fetch keeps the globally first `fetch` 
rows of its input rather than the first rows of each partition on its own: the 
partitions of one `SortExec` share a TopK threshold, and `TopKRepartition` 
copies the TopK below the hash repartition, where it keeps the first rows of 
each input partition. Both are what a merge or coalesce with a fetch wants, and 
neither is what a window wants, because a window reads each partition on its 
own and a row missing from the middle of a group changes the value of every 
later row of that group.
   
   ## What changes are included in this PR?
   
   Two commits, because there are two reasonable guards and I would like your 
opinion on which one (asked on the issue):
   
   - **(A)** `apply_limit` sets the fetch only on a node with one output 
partition, which is what the issue suggests. That is correct, but it also drops 
the TopK from plans that are already sound, which costs 16x at `LIMIT 10` on 
`datafusion/core/benches/topk_repartition.rs`.
   - **(B)** It sets the fetch on a multi-partition `SortExec` as well, but 
only when the limit came from a `SortPreservingMergeExec` whose ordering covers 
the window's `PARTITION BY`. Then a row the window computed a wrong value for 
lost to `fetch` rows that all reach that merge before it, so it is past the 
merge's own fetch and never emitted. That leaves the benchmark unchanged and 
only the four broken shapes planning a full sort.
   
   Dropping the second commit gives (A).
   
   ## What is the testing strategy for this PR?
   
   Yes: four reproductions in `window_limits.slt`, each asserting that every 
row it returns is a row of the full result — which rows a `LIMIT` with no total 
order returns is up to the partitions' timing — preceded by an assertion that 
the limited query returns three rows at all, so the subset check cannot pass on 
an empty result. Each of the four returned a wrong row in 19 to 20 of 20 runs 
on `main`, and 0 of 20 under either guard. Unit tests in 
`limit_pushdown_past_window.rs` cover one branch of the new check each.
   
   ## Are there any user-facing changes?
   
   The wrong results are fixed. A window with `PARTITION BY` under a `LIMIT` 
and more than one input partition now plans a full sort below the window, 
unless an `ORDER BY` above it covers the `PARTITION BY`, in which case the plan 
is unchanged. No API change.
   
   🤖 Generated with [Claude Code](https://claude.com/claude-code)
   


-- 
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