You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "adriangb (via GitHub)" <gi...@apache.org> on 2026/09/19 16:56:02 UTC
[I] Wrong results: a correlated filter under the nullable side of an outer join is pulled above the join [datafusion]
adriangb opened a new issue, #25507:
URL: https://github.com/apache/datafusion/issues/25507
### Describe the bug
A correlated filter that sits on the **nullable side of an outer join** inside a subquery is pulled out of the subquery and attached to the decorrelated join. The filter then applies after the outer join instead of before it, so the rows that the outer join extends with NULLs are different and the query gives wrong results.
This affects `EXISTS`, `IN` and `NOT IN` subqueries. It is not specific to three-valued logic: the `EXISTS` form below is wrong as well.
### To Reproduce
```sql
CREATE TABLE o(k INT) AS VALUES (1), (5);
CREATE TABLE a(id INT) AS VALUES (1), (2);
CREATE TABLE b(id INT, y INT) AS VALUES (1, 1), (2, 2);
```
The subquery keeps only the rows of `b` where `b.y = o.k`, then left joins `a` to them. For `o.k = 5` no row of `b` remains, so every row of `a` is unmatched and `b.y` is NULL for all of them.
**`EXISTS` form**
```sql
SELECT o.k,
EXISTS (SELECT 1
FROM a LEFT JOIN (SELECT * FROM b WHERE b.y = o.k) AS b
ON a.id = b.id
WHERE b.y IS NULL) AS e
FROM o ORDER BY k;
```
| k | DataFusion | DuckDB 1.5.2 |
| --- | --- | --- |
| 1 | `false` | `true` |
| 5 | `false` | `true` |
For `o.k = 1` only `b.id = 1` remains, so `a.id = 2` is unmatched and `b.y` is NULL for it. For `o.k = 5` both rows of `a` are unmatched. The correct answer is `true` for both rows.
**`IN` form**
```sql
SELECT o.k,
o.k IN (SELECT b.y
FROM a LEFT JOIN (SELECT * FROM b WHERE b.y = o.k) AS b
ON a.id = b.id) AS m
FROM o ORDER BY k;
```
| k | DataFusion | DuckDB 1.5.2 |
| --- | --- | --- |
| 1 | `true` | `true` |
| 5 | `false` | `NULL` |
For `o.k = 5` the subquery gives `{NULL, NULL}`, so `5 IN (...)` is UNKNOWN.
### Expected behavior
The results of DuckDB above.
### Additional context
The plan shows the cause. `Filter: b.y = o.k` is gone from below the `Left Join` and is now the condition of the mark join:
```
Projection: o.k, __correlated_sq_1.mark AS e
LeftMark Join: o.k = __correlated_sq_1.y
TableScan: o projection=[k]
SubqueryAlias: __correlated_sq_1
Filter: b.y IS NULL
Projection: b.y
Left Join: a.id = b.id
TableScan: a projection=[id]
SubqueryAlias: b
TableScan: b projection=[id, y]
```
`Filter: b.y IS NULL` and `o.k = __correlated_sq_1.y` cannot both hold, so the mark is always `false`.
`PullUpCorrelatedExpr` in `datafusion/optimizer/src/decorrelate.rs` pulls a correlated `Filter` up through every node it does not know, and `LogicalPlan::Join` is one of those nodes. A correlated filter can move above an `Inner` join without a change of meaning, but not above the nullable side of an outer join.
Found on `main` at 64871d923c. It is independent of https://github.com/apache/datafusion/issues/25480 and of https://github.com/apache/datafusion/pull/25338.
--
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: github-unsubscribe@datafusion.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Wrong results: a correlated filter under the nullable side of an outer join is pulled above the join [datafusion]
Posted by "kita-renji (via GitHub)" <gi...@apache.org>.
kita-renji commented on issue #25507:
URL: https://github.com/apache/datafusion/issues/25507#issuecomment-5843336844
take
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Wrong results: a correlated filter under the nullable side of an outer join is pulled above the join [datafusion]
Posted by "adriangb (via GitHub)" <gi...@apache.org>.
adriangb closed issue #25507: Wrong results: a correlated filter under the nullable side of an outer join is pulled above the join
URL: https://github.com/apache/datafusion/issues/25507
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org