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: [email protected]
For additional commands, e-mail: [email protected]
