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: [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]
