Ivelin Tchangalov created SPARK-60002:
-----------------------------------------
Summary: 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: 4.0.0, 3.5.6
Reporter: Ivelin Tchangalov
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 never written to the table's SerDe properties
Hive honours the clause, so the same DDL over the same files returns different
results in
Hive and in Spark.
Every delimited-text table whose sentinel is not Hive's default {{\N}}
reads that sentinel as literal data instead of NULL. Because the clause is
accepted
silently, there is no signal at table-creation time. That translates to a 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 t}} 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
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]
