[jira] [Created] (SPARK-59992) element_at and array index are wrongly non-nullable under ANSI mode when the index is nullable

["Richard Chen (Jira)" <ji...@apache.org>]

Richard Chen created SPARK-59992:
------------------------------------

             Summary: element_at and array index are wrongly non-nullable under ANSI mode when the index is nullable
                 Key: SPARK-59992
                 URL: https://issues.apache.org/jira/browse/SPARK-59992
             Project: Spark
          Issue Type: Bug
          Components: SQL
    Affects Versions: 4.2.0
            Reporter: Richard Chen


With ANSI mode enabled, ElementAt and GetArrayItem report nullable = false for a non-foldable index whenever the array is non-nullable and its elements are non-nullable (e.g. array('a', 'b')). The nullability of the index is ignored, even though both expressions are null-intolerant and return NULL for a NULL index.

The optimizer trusts this flag. NullPropagation truncates a Coalesce after its first non-nullable child, so a fallback such as coalesce(element_at(arr, idx), 'z') is removed and the query returns NULL instead of 'z'. The result column is also declared non-nullable while carrying nulls, so collecting it through an encoder fails with EXPRESSION_DECODING_FAILED, and writing it to a NOT NULL column fails.

Repro, with spark.sql.ansi.enabled=true:
{code:sql}
SELECT coalesce(element_at(array('a', 'b'), CAST(nullif(id, 0) AS INT)), 'z') AS v1,
       coalesce(array('a', 'b')[CAST(nullif(id, 0) AS INT) - 1], 'z') AS v2
FROM range(2);
{code}
Expected rows: (z, z) and (a, a). Actual: the optimized plan contains no coalesce (e.g. "if ((id = 0)) null else element_at([a,b], cast(id as int), None, true)"), the first row is (NULL, NULL), and the schema declares both columns non-nullable.

The cause is GetArrayItemUtil.computeNullabilityFromArray, shared by both expressions:
{code:scala}
} else {
  if (failOnError) arrayElementNullable || child.nullable else true
}
{code}
The fix is to also include ordinal.nullable in the ANSI branch; a non-nullable index keeps the existing non-nullable result.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org