qzyu999 opened a new issue, #23692:
URL: https://github.com/apache/datafusion/issues/23692
### Describe the bug
Multi-condition `IS NOT DISTINCT FROM` in JOIN ON clauses fails with a
type_coercion error when two or more conditions are combined with `AND`.
Single-condition `IS NOT DISTINCT FROM` works correctly.
Error message:
```
DataFusion error: type_coercion
caused by
Error during planning: Cannot infer common argument type for logical boolean
operation Int64 AND Boolean
```
The type_coercion rule appears to treat the result of `IS NOT DISTINCT FROM`
as the column's data type (e.g., `Int64`, `Utf8`) rather than `Boolean` when
combined with `AND` in a JOIN condition.
### To Reproduce
```python
import pyarrow as pa
from datafusion import SessionContext
ctx = SessionContext()
ctx.register_record_batches("l", [[pa.record_batch({"a": [1, 2], "b": ["x",
"y"]})]])
ctx.register_record_batches("r", [[pa.record_batch({"a": [1], "b": ["x"]})]])
# Single condition -- works fine
result = ctx.sql('SELECT l.* FROM l LEFT ANTI JOIN r ON l."a" IS NOT
DISTINCT FROM r."a"')
print(result.to_arrow_table()) # OK: returns row (2, "y")
# Two conditions with AND -- crashes
result = ctx.sql(
'SELECT l.* FROM l LEFT ANTI JOIN r '
'ON l."a" IS NOT DISTINCT FROM r."a" '
'AND l."b" IS NOT DISTINCT FROM r."b"'
)
# ERROR: Cannot infer common argument type for logical boolean operation
Int64 AND Boolean
```
Equivalent Rust SQL test:
```sql
SELECT l.* FROM l LEFT ANTI JOIN r
ON l.a IS NOT DISTINCT FROM r.a
AND l.b IS NOT DISTINCT FROM r.b
```
### Expected behavior
The query should succeed and return rows from `l` that have no matching row
in `r` (using IS NOT DISTINCT FROM semantics where NULL = NULL).
`IS NOT DISTINCT FROM` should return `Boolean` regardless of input type,
allowing it to be combined with `AND` in JOIN conditions.
### Additional context
- **DataFusion version:** 54.0.0 (via datafusion-python)
- **Affects all column types:** Int64, Utf8, Utf8View โ the error type
changes but the failure is the same
- **Single-condition joins work fine** โ only triggers when 2+ `IS NOT
DISTINCT FROM` conditions are `AND`ed
- **All join types affected:** LEFT ANTI JOIN, INNER JOIN, LEFT JOIN โ all
fail with the same error
- **Workaround:** Execute single-column joins only, or use alternative SQL
patterns
This is blocking multi-column equality delete resolution in Apache Iceberg
(PyIceberg), which requires `IS NOT DISTINCT FROM` semantics per the Iceberg
specification (ยง5.5.2).
Possibly related to #22461 (operator precedence for IS NOT DISTINCT FROM).
--
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]
