Stevo Mitric created SPARK-60875:
------------------------------------
Summary: Recursive CTE reads NULLs written into nested types of
the anchor back as 0
Key: SPARK-60875
URL: https://issues.apache.org/jira/browse/SPARK-60875
Project: Spark
Issue Type: Bug
Components: SQL
Affects Versions: 4.4.0
Reporter: Stevo Mitric
The self-reference of a recursive CTE (UnionLoopRef) is typed with the anchor's
output types; only top-level nullability is widened (SPARK-52430). If the
anchor builds an array, map or struct from non-null values, e.g. array(c) or
map('k', c), the element, value or field type stays non-nullable. When the
recursive step writes a NULL there, the next iteration reads it back through
generated code that skips the null check, so the NULL silently becomes 0:
{code:sql}
CREATE OR REPLACE TEMPORARY VIEW parts AS SELECT id AS part_id, id AS cost FROM
range(200);
WITH RECURSIVE bom(lvl, part_id, costs) AS (
SELECT 0, part_id, array(cost) FROM parts
UNION ALL
SELECT lvl + 1, part_id, IF(lvl = 0, array(CAST(NULL AS BIGINT)),
array(costs[0] + 1))
FROM bom WHERE lvl < 2
)
SELECT lvl, count(*), count_if(costs[0] IS NULL), sum(costs[0]) FROM bom GROUP
BY lvl ORDER BY lvl;
-- actual: 0 200 0 19900 / 1 200 200 NULL / 2 200 0 200
-- expected: 0 200 0 19900 / 1 200 200 NULL / 2 200 200 NULL
{code}
The same happens for map values (info['k'] returns 0 instead of NULL) and for
arrays inside structs. A NULL struct element inside an array throws a
NullPointerException instead. A column that only copies another column (b = a)
is also affected once a's NULLs reach it.
This happens with the default configuration. Steps larger than
spark.sql.cteRecursionAnchorRowsLimitToConvertToLocalRelation (100 rows) are
always affected. Smaller steps are converted to a LocalRelation and are
affected when the recursive step also reads a non-local relation (e.g. a join
with a table), because the step then runs through generated code.
Related, same cause: when another column of the recursion needs a cast, a
recursive step that writes a NULL into such a nested type fails analysis
instead:
{code:sql}
WITH RECURSIVE t(n, m, arr) AS (
SELECT 1, CAST(1 AS BIGINT), ARRAY(1)
UNION ALL
SELECT n + 1, n + 1, ARRAY(CAST(NULL AS INT)) FROM t WHERE n < 3
)
SELECT * FROM t;
-- [CANNOT_MERGE_INCOMPATIBLE_DATA_TYPE] Failed to merge incompatible data
types "ARRAY<INT>" and "ARRAY<INT>"
{code}
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
