jayzhan211 opened a new issue, #27216:
URL: https://github.com/apache/datafusion/issues/27216
### Describe the bug
`NOT IN (subquery)` returns wrong rows when both the outer table and the
subquery table are unbounded (streaming tables, `CREATE UNBOUNDED EXTERNAL
TABLE`). NULL outer values are kept, and a NULL in the subquery no longer
empties the result. The same queries over bounded tables are correct.
### To Reproduce
`l.csv`:
```
a,b
1,x
2,x
,x
```
`r.csv`:
```
a,b
2,y
,y
```
```sql
CREATE UNBOUNDED EXTERNAL TABLE l(a INT, b VARCHAR) STORED AS CSV LOCATION
'l.csv' OPTIONS ('format.has_header' 'true');
CREATE UNBOUNDED EXTERNAL TABLE r(a INT, b VARCHAR) STORED AS CSV LOCATION
'r.csv' OPTIONS ('format.has_header' 'true');
-- Query 1
SELECT a FROM l WHERE a NOT IN (SELECT a FROM r);
-- Query 2
SELECT a FROM l WHERE a = 1 OR a NOT IN (SELECT a FROM r);
-- Query 3
SELECT a FROM l WHERE a NOT IN (SELECT a FROM r WHERE a IS NOT NULL);
```
### Expected behavior
| Query | DataFusion returns | Expected |
|---|---|---|
| 1 | `1`, `NULL` | no rows (the subquery contains NULL) |
| 2 | `1`, `NULL` | `1` |
| 3 | `1`, `NULL` | `1` (`NULL NOT IN (2)` is NULL) |
With `CREATE EXTERNAL TABLE` (bounded) instead, DataFusion returns the
expected rows, and so does DuckDB.
### Additional context
The logical plan is correct (`LeftAnti Join: l.a = __correlated_sq_1.a
null_aware`), but the physical plan is `SymmetricHashJoinExec:
mode=Partitioned, join_type=LeftAnti, on=[(a@0, a@0)]` without the null-aware
flag.
`JoinSelection`'s
[`hash_join_convert_symmetric_subrule`](https://github.com/apache/datafusion/blob/3f4a37fbabdcb07705db6341268e6c0b34da0d34/datafusion/physical-optimizer/src/join_selection.rs#L370)
replaces a `HashJoinExec` whose inputs are both unbounded with a
`SymmetricHashJoinExec`
([L453](https://github.com/apache/datafusion/blob/3f4a37fbabdcb07705db6341268e6c0b34da0d34/datafusion/physical-optimizer/src/join_selection.rs#L453)).
It passes `null_equality` but not `null_aware`, and `SymmetricHashJoinExec`
has no null-aware mode. The swap subrule in the same file already skips
null-aware joins
([L518](https://github.com/apache/datafusion/blob/3f4a37fbabdcb07705db6341268e6c0b34da0d34/datafusion/physical-optimizer/src/join_selection.rs#L518));
this one has no such check.
Fix direction: don't convert a null-aware join. A streaming `NOT IN` can't
be decided before the subquery side ends, so returning a plan error for this
case is likely better than another operator mode.
### Willingness to contribute
I'm not planning to fix this myself, so anyone is welcome to pick it up.
Feel free to ping me with questions about the cause or when you'd like a review.
--
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]
