[
https://issues.apache.org/jira/browse/SPARK-60038?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mihailo Aleksic updated SPARK-60038:
------------------------------------
Summary: Merge nested nullability of all values in UNPIVOT value columns
(was: Merge nested nullability of all values in UNPIVOT value columns- #59271)
> Merge nested nullability of all values in UNPIVOT value columns
> ---------------------------------------------------------------
>
> Key: SPARK-60038
> URL: https://issues.apache.org/jira/browse/SPARK-60038
> Project: Spark
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 4.1.0
> Reporter: Mihailo Aleksic
> Priority: Major
>
> UNPIVOT (and {{Dataset.unpivot}} / {{melt}}) types each value column with the
> data type of the first listed value. Only top-level nullability is merged
> across the values; nested nullability (array {{containsNull}}, map
> {{valueContainsNull}}, struct field
> {{nullable}}) is taken from the first value. When the first value has NOT
> NULL nested elements or fields and a later value has NULLs in them, the value
> column claims those NULLs cannot exist: they are read as non-NULL values
> (e.g. {{0}}), and {{IS NULL}} on them is
> optimized to {{false}}. The result depends on the order of the values, and
> INSERT / CTAS write the wrong values.
> Repro:
> {code:sql}
> -- jan and feb are NOT NULL, mar and apr are nullable
> CREATE TEMPORARY VIEW monthlySales AS SELECT * FROM VALUES
> ('north', 10, 20, 30, CAST(NULL AS INT)),
> ('south', 5, 6, NULL, NULL)
> AS monthlySales(store, jan, feb, mar, apr);
> SELECT store, half, b.lo, b.hi, b.hi IS NULL AS hi_is_null
> FROM (
> SELECT store, named_struct('lo', jan, 'hi', feb) AS h1,
> named_struct('lo', mar, 'hi', apr) AS h2
> FROM monthlySales
> )
> UNPIVOT (b FOR half IN (h1, h2))
> ORDER BY store, half;
> {code}
> Actual ({{b}} is typed {{struct<lo: int NOT NULL, hi: int NOT NULL>}},
> taken from {{h1}}):
> {noformat}
> north h1 10 20 false
> north h2 30 0 false
> south h1 5 6 false
> south h2 0 0 false
> {noformat}
> Expected:
> {noformat}
> north h1 10 20 false
> north h2 30 NULL true
> south h1 5 6 false
> south h2 NULL NULL true
> {noformat}
> Listing the nullable value first, {{IN (h2, h1)}}, returns the expected
> result. Array values ({{array(jan, feb)}} vs {{array(mar, apr)}}), map
> values, multi-value UNPIVOT and {{Dataset.unpivot}} are affected in the same
> way.
> Cause: {{Unpivot.valuesTypeCoercioned}} compares the value types with
> {{DataTypeUtils.sameType}}, which ignores nullability. {{UnpivotCoercion}},
> which would widen the types and merge their nested nullability, therefore
> does not run when the values differ only in
> nested nullability, and {{UnpivotTransformer}} types the value column with
> {{values.head(index).dataType}}. The single-pass resolver's
> {{UnpivotResolver}} already applies {{UnpivotTypeCoercion}} unconditionally.
> This affects all versions since UNPIVOT was added in 3.4.0 (SPARK-38864,
> SPARK-39876).
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
