[
https://issues.apache.org/jira/browse/SPARK-60473?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Cole Agard updated SPARK-60473:
-------------------------------
Affects Version/s: 3.5.8
4.2.0
4.1.1
4.0.0
Labels: correctness (was: )
> 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: 4.0.0, 3.5.8, 4.2.0, 4.1.1, 5.0.0
> Reporter: Cole Agard
> Priority: Major
> Labels: correctness
>
> 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]
