jayzhan211 commented on code in PR #25810:
URL: https://github.com/apache/datafusion/pull/25810#discussion_r4155343177
##########
datafusion/optimizer/src/decorrelate.rs:
##########
@@ -176,6 +176,16 @@ impl TreeNodeRewriter for PullUpCorrelatedExpr {
_ => Ok(Transformed::no(plan)),
}
}
+ // A window function computes each value from the rows of its
+ // partition. A correlated filter below the window selects those
+ // rows for each outer row. If the filter moves above the window,
+ // the window function runs over all rows of the input, and the
+ // values it computes are different.
+ LogicalPlan::Window(ref window) if
holds_outer_reference(&window.input) => {
Review Comment:
This also rejects queries that `main` already decorrelates correctly: when
the window is `PARTITION BY` the inner column of the correlated equality,
pulling the filter up keeps the result. Both queries below return correct rows
on `main` (`2`, and `(1,1,1),(2,2,1),(2,3,2)`) and error on this branch, which
contradicts "a query that decorrelates correctly does not change."
```sql
CREATE TABLE po(k INT) AS VALUES (1), (2), (5);
CREATE TABLE pb(id INT, y INT) AS VALUES (1, 1), (2, 2), (3, 2);
SELECT po.k FROM po WHERE EXISTS (SELECT 1 FROM (SELECT b.id, row_number()
OVER (PARTITION BY b.y ORDER BY b.id) AS rn FROM (SELECT * FROM pb WHERE pb.y =
po.k) AS b) AS w WHERE w.rn = 2);
SELECT po.k, sub.id, sub.rn FROM po, LATERAL (SELECT pb.id, row_number()
OVER (PARTITION BY pb.y ORDER BY pb.id) AS rn FROM pb WHERE pb.y = po.k) AS sub;
```
Suggest allowing the pull-up when every correlated predicate in
`window.input` is `inner_col = outer_ref` with `inner_col` in each window
expr's `partition_by`. Better still, append `inner_col` to `partition_by`,
which fixes the unpartitioned top-N-per-row case too.
--
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]
