[jira] [Created] (SPARK-60473) NOT IN subquery on a non-nullable double column returns a row that is in the subquery

["Cole Agard (Jira)" <ji...@apache.org>]

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: issues-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org