[
https://issues.apache.org/jira/browse/SPARK-60002?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated SPARK-60002:
-----------------------------------
Labels: pull-request-available (was: )
> CREATE TABLE ... ROW FORMAT DELIMITED silently discards NULL DEFINED AS
> -----------------------------------------------------------------------
>
> Key: SPARK-60002
> URL: https://issues.apache.org/jira/browse/SPARK-60002
> Project: Spark
> Issue Type: Bug
> Components: SQL
> Affects Versions: 3.5.6, 4.0.0
> Reporter: Ivelin Tchangalov
> Priority: Major
> Labels: pull-request-available
>
> Spark's SQL parser accepts the
> {code:java}
> NULL DEFINED AS {code}
> clause of
> {code:java}
> CREATE TABLE ... ROW FORMAT DELIMITED{code}
> but it is never written to the table's SerDe properties.
> Therefore, the same DDL over the same files returns different results in
> Hive and in Spark.
> This translates to data issues because data isn't read correctly.
> h3. Root cause
> {{AstBuilder.visitRowFormatDelimited}} builds the SerDe property map from the
> delimited
> clauses but never reads {{{}ctx.nullDefinedAs{}}}. The method carries the
> acknowledgement in a
> comment: {{// TODO we need proper support for the NULL format.}}
> {{{}field.delim{}}}, {{{}escape.delim{}}}, {{{}colelction.delim{}}},
> {{mapkey.delim}} and
> {{line.delim}} are all stored correctly. The gap is exactly one property,
> {{{}serialization.null.format{}}}, which appears nowhere in Spark's main
> source:
> {code:java}
> grep -rn "serialization\.null\.format" --include=*.scala --include=*.java .
> | grep -v /test/
> # (no output)
> {code}
> The grammar already captures the value: {{SqlBaseParser.g4}} binds it to the
> labelled token {{{}nullDefinedAs{}}}. Its only consumer in the codebase is
> {{{}getRowFormatDelimited{}}}, the SCRIPT TRANSFORM path, which maps it to
> {{{}TOK_TABLEROWFORMATNULL{}}}. The table-DDL visitor ignores it.
> h3. Reproduction
> PySpark with Hive support enabled; {{DESCRIBE FORMATTED}} reports
> {{{}Provider: hive{}}}, so
> this is a genuine Hive SerDe table and not a converted datasource table.
> {code:sql}
> CREATE EXTERNAL TABLE t (id INT, region STRING)
> ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
> NULL DEFINED AS 'NA'
> STORED AS TEXTFILE LOCATION '...';
> {code}
> Data:
> {noformat}
> 1|east
> 2|NA
> 3|west
> {noformat}
> {{DESCRIBE FORMATTED }}reports
> {noformat}
> Storage Properties [serialization.format=|, field.delim=|]
> {noformat}
> There is no {{{}serialization.null.format{}}}, and the row with id 2 reads
> {{region}} as the
> string {{'NA'}} rather than NULL. Hive returns NULL for the same table and
> data.
> Every sentinel value is affected: {{{}''{}}}, {{{}\N{}}}, {{{}NA{}}},
> {{{}X{}}}, {{\t}} and {{\001}} all behave identically. {{\N}} appears to work
> only because it is the engine default.
> h3. How the bug was introduced
> It was never implemented.
> h3. Related issues
> https://issues.apache.org/jira/browse/SPARK-14583 reported the read-side
> variant of this parity gap: a sentinel supplied through {{TBLPROPERTIES}} on
> a partitioned table that Spark fails to apply on read. It was bulk-closed as
> Incomplete in 2019. That is a different vector into the same Hive parity gap
> and is not addressed by this ticket.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
