adriangb opened a new pull request, #25810:
URL: https://github.com/apache/datafusion/pull/25810
## Which issue does this PR close?
- Closes #25792.
## Rationale for this change
A correlated filter below a window function gives wrong results. The window
function must see only the rows that match the current outer row.
```sql
CREATE TABLE o(k INT) AS VALUES (1), (2), (5);
CREATE TABLE b(id INT, y INT) AS VALUES (1, 1), (2, 2);
SELECT o.k FROM o WHERE EXISTS (
SELECT 1 FROM (SELECT b.id, row_number() OVER (ORDER BY b.id) AS rn
FROM (SELECT * FROM b WHERE b.y = o.k) AS b) AS w
WHERE w.rn = 1)
ORDER BY o.k;
```
| Query form | Correct result (DuckDB 1.5.2, PostgreSQL 17) | `main` | This
PR |
| --- | --- | --- | --- |
| `EXISTS` (above) | `1`, `2` | `1` | not-implemented error |
| `NOT EXISTS` | `5` | `2`, `5` | not-implemented error |
| `1 IN (SELECT row_number() OVER (ORDER BY id) FROM b WHERE b.y = o.k)` |
`1`, `2` | `1` | not-implemented error |
| scalar `max(rn)` | `(1, 1)`, `(2, 1)`, `(5, NULL)` | `(1, 1)`, `(2, 2)`,
`(5, NULL)` | not-implemented error |
| `LATERAL` | `rn = 1` for `k = 2` | `rn = 2` for `k = 2` | not-implemented
error |
`PullUpCorrelatedExpr` moves the filter `b.y = o.k` from below the
`WindowAggr` to the join that replaces the subquery. Then `row_number()`
numbers the rows of all outer rows together.
An error is better than wrong rows. A query that decorrelates correctly does
not change.
## What changes are included in this PR?
- In `PullUpCorrelatedExpr::f_down`, a `LogicalPlan::Window` whose input
holds an outer reference sets `can_pull_up = false`. The subquery stays
correlated.
- It uses `holds_outer_reference`, which
https://github.com/apache/datafusion/pull/25764 added, to find an outer
reference in the window input. That helper does not go into a
`LogicalPlan::Subquery`, because the outer references there belong to a nested
scope (for example a nested `LATERAL`).
A correlated filter above the window does not change. The window input does
not depend on the outer row, so the filter is pulled up as before.
This is the same class of bug as
https://github.com/apache/datafusion/issues/25507 (correlated filter below an
outer join), which https://github.com/apache/datafusion/pull/25764 fixed with
the same guard pattern. https://github.com/apache/datafusion/issues/25808 is
another bug of this class (`LATERAL` with `SELECT DISTINCT`), which this PR
does not fix.
A later change can decorrelate some of these queries. For example, when the
correlated filter is an equality on an inner column, the pull up can add that
column to `PARTITION BY`. This PR does not do that.
## What is the testing strategy for this PR?
New sqllogictest cases:
- `subquery.slt`: an `EXPLAIN` that shows the subquery stays correlated, and
`statement error` cases for `EXISTS`, `NOT EXISTS`, `IN` and a scalar subquery.
An `EXPLAIN` and a result for a correlated filter above the window, which is
still decorrelated.
- `lateral_join.slt`: a `statement error` case for the `LATERAL` form, and
an `EXPLAIN` and a result for a correlated filter above the window.
Without the fix, all six new error and `EXPLAIN` cases fail: each query
returns the wrong rows in the table above. The full sqllogictest suite and
`cargo test -p datafusion-optimizer` pass, and no existing plan changes.
## Are there any user-facing changes?
Queries with a correlated filter below a window function now fail with a
not-implemented error. Before, they returned wrong results. No public API
changes.
🤖 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]
