Joel Robin created SPARK-59147:
----------------------------------
Summary: Nested SELECT * EXCEPT turns NULL structs into non-NULL
structs
Key: SPARK-59147
URL: https://issues.apache.org/jira/browse/SPARK-59147
Project: Spark
Issue Type: Bug
Components: SQL
Affects Versions: 4.2.0, 4.3.0
Reporter: Joel Robin
h3. Problem
Nested-field SELECT * EXCEPT reconstructs a nullable struct even when the
original struct is NULL. This silently changes the parent struct from NULL to a
non-NULL struct whose remaining fields are NULL.
h3. Reproduction
Reproduced on Apache Spark master at commit
cdab5402f9f5890117d6156b6f0e7c0ed8e1aca6.
{code:sql}
WITH input AS (
SELECT id,
CASE
WHEN id = 0 THEN CAST(NULL AS STRUCT<a: INT, b: INT>)
ELSE named_struct('a', id, 'b', id + 10)
END AS s
FROM VALUES (0), (1) AS t(id)
),
actual AS (
SELECT * EXCEPT (s.a) FROM input
)
SELECT
i.id,
i.s IS NULL AS before_except,
a.s IS NULL AS after_except,
a.s.b
FROM input i
JOIN actual a USING (id)
ORDER BY id;
{code}
h3. Actual behavior
{noformat}
(0, true, false, NULL)
(1, false, false, 11)
{noformat}
For id 0, the input struct is NULL, but the struct returned by SELECT * EXCEPT
(s.a) is non-NULL.
h3. Expected behavior
{noformat}
(0, true, true, NULL)
(1, false, false, 11)
{noformat}
Removing a nested field should preserve the nullness of the parent struct. For
id 0, the result should remain CAST(NULL AS STRUCT<b: INT>), not
named_struct('b', NULL).
h3. Customer impact
This is a silent SQL correctness issue. Nullable structs commonly come from
parsed JSON and from the null-producing side of outer joins. Customers use
nested-field SELECT * EXCEPT to remove unwanted fields from wide records;
afterward, IS NULL checks, filters, joins, COALESCE expressions, and serialized
output can behave differently because Spark has changed a NULL struct into a
present struct containing NULL fields.
The same behavior was reproduced with NULL structs produced by from_json and
with null-extended structs from outer joins.
h3. Technical analysis
UnresolvedStarExceptOrReplace.filterColumns handles a nested exclusion by
extracting the retained fields and unconditionally wrapping them in
CreateStruct. CreateStruct resolves to CreateNamedStruct, whose nullable
property is always false. Consequently, the reconstructed struct cannot
preserve the nullness of the original nullable parent expression.
Relevant locations:
*
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/unresolved.scala,
nested exclusion reconstruction in filterColumns
*
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/complexTypeCreator.scala,
CreateNamedStruct.nullable
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
