[
https://issues.apache.org/jira/browse/SPARK-58482?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Josh Rosen updated SPARK-58482:
-------------------------------
Description:
CACHE TABLE causes the following query to return incorrect results:
{code:sql}
CREATE TABLE n(id INT, v DOUBLE) USING PARQUET;
INSERT INTO n VALUES (1, 1.5), (2, CAST('NaN' AS DOUBLE)), (3, 0.0);
SELECT id FROM n WHERE v = CAST('NaN' AS DOUBLE); -- 1 row, correct
CACHE TABLE n;
SELECT id FROM n WHERE v = CAST('NaN' AS DOUBLE); -- 0 rows
{code}
Caching the table changes the answer. Spark's equality treats NaN as equal to
itself, so the
correct result is 1 row in both cases.
The batch statistics are gathered with IEEE comparison.
In {{{}DoubleColumnStats.gatherValueStats
([src|https://github.com/apache/spark/blob/4dcdd4dba544210898180a410b8b30e0b85157cb/sql/core/src/main/scala/org/apache/spark/sql/execution/columnar/ColumnStats.scala#L247-L252])
the bounds are updated with \{{{}if (value > upper) upper = value{}}}, and
every comparison against NaN is false, so NaN never widens the maximum. The
batch containing NaN therefore reports bounds drawn only from its non-NaN
values.
The pruning test then compares under Catalyst's total ordering, where NaN sorts
above all other values. {{SimpleMetricsCachedBatchSerializer.buildFilter}}
evaluates
{{{}lowerBound <= lit && lit <= upperBound{}}}, the literal NaN is above the
recorded maximum, and the batch is skipped. The row is not returned.
{{FloatColumnStats}} has the same shape and the same defect.
Related: SPARK-24934 fixed the same class of problem for partition pruning,
where min/max
statistics were used for types whose comparison semantics did not match. This
is the columnar cache instance of it.
Suggested direction: gather the bounds using the same ordering the pruning test
uses, or record a NaN-present flag and skip bounds pruning for a NaN literal.
Whichever is chosen, producer and consumer need to agree on the comparator,
which is the actual invariant being broken here.
was:
CACHE TABLE causes the following query to return incorrect results:
{code:sql}
CREATE TABLE n(id INT, v DOUBLE) USING PARQUET;
INSERT INTO n VALUES (1, 1.5), (2, CAST('NaN' AS DOUBLE)), (3, 0.0);
SELECT id FROM n WHERE v = CAST('NaN' AS DOUBLE); -- 1 row, correct
CACHE TABLE n;
SELECT id FROM n WHERE v = CAST('NaN' AS DOUBLE); -- 0 rows
{code}
Caching the table changes the answer. Spark's equality treats NaN as equal to
itself, so the
correct result is 1 row in both cases.
The batch statistics are gathered with IEEE comparison.
In {{DoubleColumnStats.gatherValueStats
}}([src|https://github.com/apache/spark/blob/4dcdd4dba544210898180a410b8b30e0b85157cb/sql/core/src/main/scala/org/apache/spark/sql/execution/columnar/ColumnStats.scala#L247-L252])
the bounds are updated with {{{}if (value > upper) upper = value{}}}, and
every comparison against NaN is false, so NaN never widens the maximum. The
batch containing NaN therefore reports bounds drawn only from its non-NaN
values.
The pruning test then compares under Catalyst's total ordering, where NaN sorts
above all other values. {{SimpleMetricsCachedBatchSerializer.buildFilter}}
evaluates
{{{}lowerBound <= lit && lit <= upperBound{}}}, the literal NaN is above the
recorded maximum, and the batch is skipped. The row is not returned.
{{FloatColumnStats}} has the same shape and the same defect.
Related: SPARK-24934 fixed the same class of problem for partition pruning,
where min/max
statistics were used for types whose comparison semantics did not match. This
is the columnar cache instance of it.
Suggested direction: gather the bounds using the same ordering the pruning test
uses, or record a NaN-present flag and skip bounds pruning for a NaN literal.
Whichever is chosen, producer and consumer need to agree on the comparator,
which is the actual invariant being broken here.
> Filtering a cached tables for NaN values returns incorrect results:
> cached-batch pruning compares under a different ordering than the statistics
> were built with
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: SPARK-58482
> URL: https://issues.apache.org/jira/browse/SPARK-58482
> Project: Spark
> Issue Type: Bug
> Components: SQL
> Affects Versions: 4.0.0
> Reporter: Josh Rosen
> Priority: Major
> Labels: correctness
>
> CACHE TABLE causes the following query to return incorrect results:
> {code:sql}
> CREATE TABLE n(id INT, v DOUBLE) USING PARQUET;
> INSERT INTO n VALUES (1, 1.5), (2, CAST('NaN' AS DOUBLE)), (3, 0.0);
> SELECT id FROM n WHERE v = CAST('NaN' AS DOUBLE); -- 1 row, correct
> CACHE TABLE n;
> SELECT id FROM n WHERE v = CAST('NaN' AS DOUBLE); -- 0 rows
> {code}
> Caching the table changes the answer. Spark's equality treats NaN as equal to
> itself, so the
> correct result is 1 row in both cases.
> The batch statistics are gathered with IEEE comparison.
> In {{{}DoubleColumnStats.gatherValueStats
> ([src|https://github.com/apache/spark/blob/4dcdd4dba544210898180a410b8b30e0b85157cb/sql/core/src/main/scala/org/apache/spark/sql/execution/columnar/ColumnStats.scala#L247-L252])
> the bounds are updated with \{{{}if (value > upper) upper = value{}}}, and
> every comparison against NaN is false, so NaN never widens the maximum. The
> batch containing NaN therefore reports bounds drawn only from its non-NaN
> values.
> The pruning test then compares under Catalyst's total ordering, where NaN
> sorts above all other values.
> {{SimpleMetricsCachedBatchSerializer.buildFilter}} evaluates
> {{{}lowerBound <= lit && lit <= upperBound{}}}, the literal NaN is above the
> recorded maximum, and the batch is skipped. The row is not returned.
> {{FloatColumnStats}} has the same shape and the same defect.
> Related: SPARK-24934 fixed the same class of problem for partition pruning,
> where min/max
> statistics were used for types whose comparison semantics did not match. This
> is the columnar cache instance of it.
> Suggested direction: gather the bounds using the same ordering the pruning
> test uses, or record a NaN-present flag and skip bounds pruning for a NaN
> literal. Whichever is chosen, producer and consumer need to agree on the
> comparator, which is the actual invariant being broken here.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
