[Incompatibility] Verify array_contains null handling matches Spark

[andygrove]

Summary

array_contains is currently marked as Compatible in Comet, but the null handling behavior should be verified to ensure it matches Spark's three-valued logic.

Spark Specification

According to Spark's array_contains behavior:

• Returns true if the value is found in the array
• Returns false if no match found AND no null elements exist
• Returns null if no match found BUT null elements exist (indeterminate result)
• Returns null if search value is null

Examples:

SELECT array_contains(array(1, 2, 3), 2);
-- Spark returns: true

SELECT array_contains(array(1, 2, 3), 5);
-- Spark returns: false

SELECT array_contains(array(1, null, 3), 2);
-- Spark returns: null (no match, but null element exists - indeterminate)

SELECT array_contains(array(1, null, 3), 1);
-- Spark returns: true (found match)

SELECT array_contains(array(1, 2, 3), null);
-- Spark returns: null (search value is null)

Current Comet Implementation

Comet uses DataFusion's array_has function:

val arrayContainsScalarExpr =
  scalarFunctionExprToProto("array_has", arrayExprProto, keyExprProto)

Verification Needed

1. Test array_contains(array(1, null, 3), 2) - should return null, not false
2. Test array_contains(array(1, 2, 3), null) - should return null

If DataFusion's array_has doesn't implement three-valued logic, this should be:

• Marked as Incompatible
• Or fixed with custom implementation

Current Tests

The test file includes null tests:

checkSparkAnswerAndOperator(sql(s"SELECT array_contains(a, cast(null as $typeName)) FROM t2"))

But we should verify the specific three-valued null logic case.

---

Note: This issue was generated with AI assistance.

[michaelmitchell-bit]

take