Cole Agard created SPARK-60473:
----------------------------------
Summary: NOT IN subquery on a non-nullable double column returns a
row that is in the subquery
Key: SPARK-60473
URL: https://issues.apache.org/jira/browse/SPARK-60473
Project: Spark
Issue Type: Bug
Components: Optimizer
Affects Versions: 5.0.0
Reporter: Cole Agard
A NOT IN subquery on non-nullable FLOAT/DOUBLE columns compares 0.0 and -0.0 as
different values, although {{=}} treats them as equal. A row whose value is in
the subquery result is then returned.
{code:java}
from pyspark.sql import SparkSession
spark = SparkSession.builder.getOrCreate()
spark.createDataFrame([(0.0,)], "x DOUBLE NOT
NULL").createOrReplaceTempView("t")
spark.createDataFrame([(-0.0,)], "y DOUBLE NOT
NULL").createOrReplaceTempView("u")
spark.sql("SELECT 0.0D = -0.0D AS eq").collect()
# [Row(eq=True)]
spark.sql("SELECT * FROM t WHERE x NOT IN (SELECT y FROM u)").collect()
# expected: []
# Spark 4.2.0: [Row(x=0.0)] {code}
The same happens on 3.5.8, 4.0.0, 4.1.1, 4.2.0 and master. {{x IN (SELECT y
FROM u)}} returns {{[Row(x=0.0)]}} as expected, and the equivalent {{NOT EXISTS
(SELECT 1 FROM u WHERE u.y = t.x)}} returns {{[]}}. With nullable columns the
NOT IN query is also wrong on 3.5.8, 4.0.0 and 4.1.1, and correct on 4.2.0 and
master. On 4.2.0, with {{spark.sql.optimizer.excludedRules}} set to
{{NullPropagation}} the result is {{[]}}.
Optimized plan (4.2.0):
{code:java}
Join LeftAnti, (x#0 = y#1)
:- LogicalRDD [x#0], false
+- LogicalRDD [y#1], false {code}
Neither join key is wrapped in
{{knownfloatingpointnormalized(normalizenanandzero(...))}}.
*Cause:* {{RewritePredicateSubquery}} turns the NOT IN into a left anti join on
{{(x = y) OR isnull(x = y)}}. Both columns are non-nullable, so
{{NullPropagation}} replaces {{isnull(x = y)}} with {{false}} in the same
batch. When {{NormalizeFloatingNumbers}} runs, the condition is {{(x = y) OR
false}}, which neither {{ExtractEquiJoinKeys}} nor
{{ExtractSingleColumnNullAwareAntiJoin}} matches, so the keys are not
normalized. {{BooleanSimplification}} later reduces the condition to {{x = y}},
and the join compares the raw keys. The condition should be simplified before
{{NormalizeFloatingNumbers}} runs, or {{NormalizeFloatingNumbers}} should
handle this shape.
This is the same kind of float normalization gap as SPARK-60150.
How it was found: while fixing SPARK-60150, a test of NOT IN over a float
grouping key failed only when the columns were non-nullable.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
