[
https://issues.apache.org/jira/browse/SPARK-26078?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16717215#comment-16717215
]
ASF GitHub Bot commented on SPARK-26078:
----------------------------------------
mgaido91 commented on a change in pull request #23057: [SPARK-26078][SQL] Dedup
self-join attributes on IN subqueries
URL: https://github.com/apache/spark/pull/23057#discussion_r240619938
##########
File path:
sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/subquery.scala
##########
@@ -92,18 +114,20 @@ object RewritePredicateSubquery extends Rule[LogicalPlan]
with PredicateHelper {
// Deduplicate conflicting attributes if any.
dedupJoin(Join(outerPlan, sub, LeftAnti, joinCond))
Review comment:
the main problem is that in the other cases, so when exists is there, the
condition is already created. So we would need to complicate quite a lot the
method in order to handle the 2 cases and I am not sure wether it is worth. For
instance, the `values`, in the `Exists` case, should be taken from the
conditions as those expressions referencing attributes from one side and the
join condition needs to be rewritten. So I don't think that it is a good idea
to have a common rewrite for both them: it would be overcomplicated IMHO.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
[email protected]
> WHERE .. IN fails to filter rows when used in combination with UNION
> --------------------------------------------------------------------
>
> Key: SPARK-26078
> URL: https://issues.apache.org/jira/browse/SPARK-26078
> Project: Spark
> Issue Type: Bug
> Components: SQL
> Affects Versions: 2.3.1, 2.4.0
> Reporter: Arttu Voutilainen
> Priority: Blocker
> Labels: correctness
>
> Hey,
> We encountered a case where Spark SQL does not seem to handle WHERE .. IN
> correctly, when used in combination with UNION, but instead returns also rows
> that do not fulfill the condition. Swapping the order of the datasets in the
> UNION makes the problem go away. Repro below:
>
> {code}
> sql = SQLContext(sc)
> a = spark.createDataFrame([{'id': 'a', 'num': 2}, {'id':'b', 'num':1}])
> b = spark.createDataFrame([{'id': 'a', 'num': 2}, {'id':'b', 'num':1}])
> a.registerTempTable('a')
> b.registerTempTable('b')
> bug = sql.sql("""
> SELECT id,num,source FROM
> (
> SELECT id, num, 'a' as source FROM a
> UNION ALL
> SELECT id, num, 'b' as source FROM b
> ) AS c
> WHERE c.id IN (SELECT id FROM b WHERE num = 2)
> """)
> no_bug = sql.sql("""
> SELECT id,num,source FROM
> (
> SELECT id, num, 'b' as source FROM b
> UNION ALL
> SELECT id, num, 'a' as source FROM a
> ) AS c
> WHERE c.id IN (SELECT id FROM b WHERE num = 2)
> """)
> bug.show()
> no_bug.show()
> bug.explain(True)
> no_bug.explain(True)
> {code}
> This results in one extra row in the "bug" DF coming from DF "b", that should
> not be there as it
> {code:java}
> >>> bug.show()
> +---+---+------+
> | id|num|source|
> +---+---+------+
> | a| 2| a|
> | a| 2| b|
> | b| 1| b|
> +---+---+------+
> >>> no_bug.show()
> +---+---+------+
> | id|num|source|
> +---+---+------+
> | a| 2| b|
> | a| 2| a|
> +---+---+------+
> {code}
> The reason can be seen in the query plans:
> {code:java}
> >>> bug.explain(True)
> ...
> == Optimized Logical Plan ==
> Union
> :- Project [id#0, num#1L, a AS source#136]
> : +- Join LeftSemi, (id#0 = id#4)
> : :- LogicalRDD [id#0, num#1L], false
> : +- Project [id#4]
> : +- Filter (isnotnull(num#5L) && (num#5L = 2))
> : +- LogicalRDD [id#4, num#5L], false
> +- Join LeftSemi, (id#4#172 = id#4#172)
> :- Project [id#4, num#5L, b AS source#137]
> : +- LogicalRDD [id#4, num#5L], false
> +- Project [id#4 AS id#4#172]
> +- Filter (isnotnull(num#5L) && (num#5L = 2))
> +- LogicalRDD [id#4, num#5L], false
> {code}
> Note the line *+- Join LeftSemi, (id#4#172 = id#4#172)* - this condition
> seems wrong, and I believe it causes the LeftSemi to return true for all rows
> in the left-hand-side table, thus failing to filter as the WHERE .. IN
> should. Compare with the non-buggy version, where both LeftSemi joins have
> distinct #-things on both sides:
> {code:java}
> >>> no_bug.explain()
> ...
> == Optimized Logical Plan ==
> Union
> :- Project [id#4, num#5L, b AS source#142]
> : +- Join LeftSemi, (id#4 = id#4#173)
> : :- LogicalRDD [id#4, num#5L], false
> : +- Project [id#4 AS id#4#173]
> : +- Filter (isnotnull(num#5L) && (num#5L = 2))
> : +- LogicalRDD [id#4, num#5L], false
> +- Project [id#0, num#1L, a AS source#143]
> +- Join LeftSemi, (id#0 = id#4#173)
> :- LogicalRDD [id#0, num#1L], false
> +- Project [id#4 AS id#4#173]
> +- Filter (isnotnull(num#5L) && (num#5L = 2))
> +- LogicalRDD [id#4, num#5L], false
> {code}
>
> Best,
> -Arttu
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
