[
https://issues.apache.org/jira/browse/SPARK-60473?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Cole Agard updated SPARK-60473:
-------------------------------
Description:
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. I am happy to take
this one!
was:
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.
> 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. I am happy to
> take this one!
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
