[
https://issues.apache.org/jira/browse/SPARK-60875?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated SPARK-60875:
-----------------------------------
Labels: pull-request-available (was: )
> 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
> Priority: Major
> Labels: pull-request-available
>
> 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]
