apache / apache/datafusion

type_coercion error: multi-condition IS NOT DISTINCT FROM in JOIN ON clause fails

Open
#23,692 3 comments 0 reactions 0 assignees View on GitHub
Dominant language
Rust
Stars
9.3k
Forks
2.4k
Avg merge
3d 7h
Merged PRs (30d)
344

Description

### 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).

Contributor guide

Open the contributing guide

Research direction

Start with the type_coercion rule implicated in planning and reproduce the failure with the equivalent Rust SQL test using two IS NOT DISTINCT FROM conditions joined by AND. Compare its inferred result types with the single-condition case. Done means multi-condition JOIN ON queries plan and execute successfully across the reported join types and column types.

Written by the indexing model from the issue text.

Assessment

Tech stack
python, rust
Domain
databases
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Active
Clarity
Mostly clear
Newbie friendliness
68/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.