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

[qzyu999]

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

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:

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 ANDed
• 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).

[qzyu999]

Root Cause Analysis Update

After deeper investigation, the bug is NOT specific to Utf8View or any particular join type. Key findings:

Reproduction

Multi-condition IS NOT DISTINCT FROM in ANY join ON clause fails type_coercion:

• Single condition works: ON l.a IS NOT DISTINCT FROM r.a
• Multi-condition fails: ON l.a IS NOT DISTINCT FROM r.a AND l.b IS NOT DISTINCT FROM r.b
• Fails for ALL join types (INNER, LEFT, LEFT ANTI, LEFT SEMI)
• Fails for ALL data types (Int32, Int64, Utf8, Utf8View)
• Fails with CREATE TABLE, register_parquet, and register_record_batches

Exception Case

Works when the data contains NULLs in the join columns AND both tables have the same rows. Example:

CREATE TABLE t1 (val1 INT, val2 INT) AS VALUES (10, 100), (NULL, 200), (30, NULL);
CREATE TABLE t2 (val1 INT, val2 INT) AS VALUES (10, 100), (NULL, 200), (30, NULL);
-- This works:
SELECT t1.* FROM t1 LEFT ANTI JOIN t2 ON t1.val1 IS NOT DISTINCT FROM t2.val1 AND t1.val2 IS NOT DISTINCT FROM t2.val2;

This suggests some optimizer rule (possibly constant folding or join elimination) short-circuits the plan before type_coercion hits the problematic path.

Likely Root Cause Location

The error occurs in TypeCoercionRewriter::f_up (analyzer/type_coercion.rs) when processing the \AND\ node in the join filter expression:

1. map_expressions processes the join filter via _up (bottom-up)
2. When reaching the AND node, it calls coerce_binary_op(*left, schema, And, *right, schema)
3. coerce_binary_op calls left.get_type(schema) which should return Boolean (since left child is IS NOT DISTINCT FROM already rewritten by the bottom-up pass)
4. But left.get_type(schema) returns the column type (Int32, Utf8View) instead of Boolean

The suspicion is that get_type() on the rewritten BinaryExpr(left_col, IsNotDistinctFrom, right_col) is somehow not recognizing it as a comparison operation that returns Boolean possibly because the schema lookup resolves the expression to a column reference instead of computing the binary operator's return type. This would happen if a NamePreserver or alias wrapping is interfering with the expression structure.

Environment

• DataFusion 54.0.0 (pip: datafusion==54.0.0)
• Python 3.12 on Windows
• Confirmed NOT platform-specific (same behavior expected on Linux)

[qzyu999]

Root Cause Found: SQL Parser Operator Precedence

The root cause is sqlparser-rs operator precedence, not DataFusion's type_coercion rule.

Proof

Adding explicit parentheses makes the query work:

-- FAILS: parsed as t1.a IS NOT DISTINCT FROM (t2.a AND t1.b) IS NOT DISTINCT FROM t2.b
SELECT * FROM t1 LEFT ANTI JOIN t2 ON t1.a IS NOT DISTINCT FROM t2.a AND t1.b IS NOT DISTINCT FROM t2.b

-- WORKS: explicit grouping
SELECT * FROM t1 LEFT ANTI JOIN t2 ON (t1.a IS NOT DISTINCT FROM t2.a) AND (t1.b IS NOT DISTINCT FROM t2.b)

Verification

I wrote a unit test that constructs the Join node directly (bypassing the SQL parser) with properly qualified columns and multi-condition IS NOT DISTINCT FROM. It passes the TypeCoercion analyzer without error. This proves the type_coercion rule is correct it's the parser that produces a malformed expression tree.

Fix

The fix belongs in sqlparser-rs: IS NOT DISTINCT FROM should have higher precedence than AND (consistent with PostgreSQL where IS operators bind tighter than boolean connectives).

Workaround for users: Wrap each IS NOT DISTINCT FROM condition in explicit parentheses. This is what we're doing in PyIceberg's DataFusion backend.

This issue is effectively a duplicate of / related to #22461 (operator precedence inconsistency with modern PostgreSQL).