adriangb opened a new issue, #25792:
URL: https://github.com/apache/datafusion/issues/25792
### Describe the bug
A correlated filter that sits **below a window function** inside a subquery
is pulled out of the subquery and attached to the decorrelated join. The window
function then runs over all rows of the inner table instead of only the rows
that match the outer row, so `row_number()`, `rank()`, `lag()` and similar
functions give different values and the query gives wrong results.
This affects `EXISTS` and `LATERAL` subqueries.
### To Reproduce
```sql
CREATE TABLE o(k INT) AS VALUES (1), (2), (5);
CREATE TABLE b(id INT, y INT) AS VALUES (1, 1), (2, 2);
```
For each row of `o`, the subquery keeps only the rows of `b` where `b.y =
o.k`, then numbers them. Each `k` matches at most one row of `b`, so that row
always gets `rn = 1`.
**`EXISTS` form**
```sql
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;
```
| DataFusion | DuckDB 1.5.2 | PostgreSQL 17.11 |
| --- | --- | --- |
| `1` | `1`, `2` | `1`, `2` |
**`LATERAL` form**
```sql
SELECT o.k, w.id, w.rn
FROM o, LATERAL (
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
ORDER BY o.k;
```
| k | id | DataFusion `rn` | DuckDB 1.5.2 `rn` | PostgreSQL 17.11 `rn` |
| --- | --- | --- | --- | --- |
| 1 | 1 | `1` | `1` | `1` |
| 2 | 2 | **`2`** | `1` | `1` |
### Expected behavior
The results of DuckDB and PostgreSQL above.
### Additional context
The plan shows the cause. `Filter: b.y = o.k` is gone from below the
`WindowAggr` and is now the condition of the semi join, so `row_number()`
numbers both rows of `b`:
```
LeftSemi Join: o.k = __correlated_sq_1.y
TableScan: o projection=[k]
SubqueryAlias: __correlated_sq_1
SubqueryAlias: w
Projection: b.y
Filter: row_number() ORDER BY [b.id ASC NULLS LAST] ... = UInt64(1)
Projection: b.y, row_number() ORDER BY [b.id ASC NULLS LAST] ...
WindowAggr: windowExpr=[[row_number() ORDER BY [b.id ASC NULLS
LAST] ...]]
SubqueryAlias: b
TableScan: b projection=[id, y]
```
`PullUpCorrelatedExpr` in `datafusion/optimizer/src/decorrelate.rs` pulls a
correlated `Filter` up through every node that it does not model.
`LogicalPlan::Window` is one of those nodes. A filter can only move above a
window if it reads only the `PARTITION BY` columns, which is not the case here
(the window has no `PARTITION BY`).
Found on `main` at 6a792c6713.
This is the same class of bug as these, each for a different node:
- https://github.com/apache/datafusion/issues/25507 (`Join`, the nullable
side of an outer join), fixed by https://github.com/apache/datafusion/pull/25764
- https://github.com/apache/datafusion/issues/25283 (`Limit` with an
`OFFSET`), fixed by https://github.com/apache/datafusion/pull/25284
A fix can do the same as those PRs: mark the subquery as not pull-up-able
when a `Window` has outer references below it, so the query fails with a
not-implemented error instead of returning wrong results.
--
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]
