Josh Rosen created SPARK-58482:
----------------------------------
Summary: 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
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]
