Yaoxuan Wu created SPARK-60150:
----------------------------------
Summary: A filter pushed below a DISTINCT on a float key returns a
row that fails the predicate
Key: SPARK-60150
URL: https://issues.apache.org/jira/browse/SPARK-60150
Project: Spark
Issue Type: Bug
Components: Optimizer
Affects Versions: 4.1.1, 4.2.0, 3.5.8, 4.0.0, 5.0.0
Reporter: Yaoxuan Wu
A DISTINCT (or GROUP BY, INTERSECT ALL) on a DOUBLE key outputs -0.0 as 0.0. A
filter above it is pushed below the aggregate, where it sees the raw -0.0. The
output then contains 0.0 although the predicate is false for 0.0.
{code:java}
from pyspark.sql import SparkSession
spark = SparkSession.builder.getOrCreate()
spark.createDataFrame([(-0.0,)], "x double").createOrReplaceTempView("t")
spark.sql("SELECT x, cast(x AS STRING) = '-0.0' AS p FROM (SELECT DISTINCT x
FROM t)").collect()
# [Row(x=0.0, p=False)]
spark.sql("SELECT * FROM (SELECT DISTINCT x FROM t) WHERE cast(x AS STRING) =
'-0.0'").collect()
# expected: []
# Spark 4.2.0: [Row(x=0.0)] {code}
The same happens with {{GROUP BY x}} ({{{}[Row(x=0.0, c=1)]{}}}), {{{}INTERSECT
ALL{}}}, {{subtract}} and {{{}dropDuplicates{}}}, on 3.5.8, 4.0.0, 4.1.1, 4.2.0
and master. With {{spark.sql.optimizer.excludedRules}} set to
{{PushDownPredicates,PushPredicateThroughNonJoin}} the result is {{{}[]{}}}.
Optimized plan:
{code:java}
Aggregate [x#0], [x#0]
+- Filter (isnotnull(x#0) AND (cast(x#0 as string) = -0.0))
+- LogicalRDD [x#0], false {code}
and in the physical plan the aggregate groups by
{{{}knownfloatingpointnormalized(normalizenanandzero(x#0)){}}}.
*Cause:* the rule treats a grouping key as having the same value above and
below the Aggregate. {{NormalizeFloatingNumbers}} breaks this for float/double
keys (-0.0 to 0.0, NaN canonicalization). The rule should not push a predicate
that reads such a key, or should normalize the key inside the pushed predicate.
This is the same assumption as in SPARK-60093
({{{}RemoveRedundantAggregates{}}}), in a different rule.
How it was found: a property-based testing tool checked that splitting a query
result by a predicate p into the rows where p is true, false and NULL gives
back the original result. Here the row 0.0 was counted under both {{p}} and
{{{}p IS NULL{}}}.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
