[ https://issues.apache.org/jira/browse/SPARK-18966?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15767783#comment-15767783 ]
Nattavut Sutyanyong edited comment on SPARK-18966 at 12/21/16 6:34 PM: ----------------------------------------------------------------------- {code} == Analyzed Logical Plan == a1: int, b1: int Project [a1#684, b1#685] +- Filter NOT predicate-subquery#717 [(a1#684 = a2#695) && (b2#696 = b1#685)] : +- Project [a2#695, b2#696] : +- SubqueryAlias t2, `t2` : +- Project [_1#692 AS a2#695, _2#693 AS b2#696] : +- LocalRelation [_1#692, _2#693] +- SubqueryAlias t1, `t1` +- Project [_1#681 AS a1#684, _2#682 AS b1#685] +- LocalRelation [_1#681, _2#682] == Optimized Logical Plan == Project [_1#681 AS a1#684, _2#682 AS b1#685] +- Join LeftAnti, ((isnull((_1#681 = a2#695)) || isnull((b2#696 = _2#682))) || ((_1#681 = a2#695) && (b2#696 = _2#682))) :- LocalRelation [_1#681, _2#682] +- LocalRelation [a2#695, b2#696] {code} When the comparison between b1 and b2 is unknown, the subquery returns no row hence the result is all the rows from the parent. The problem in the plan above is the expression {{isnull((b2#696 = _2#682))}} is evaluated to isnull( unknown ) which is true — that results in the entire expression of the LeftAnti is true. By the definition of LeftAnti, the rows are matched and should be filtered. was (Author: nsyca): {code} == Analyzed Logical Plan == a1: int, b1: int Project [a1#684, b1#685] +- Filter NOT predicate-subquery#717 [(a1#684 = a2#695) && (b2#696 = b1#685)] : +- Project [a2#695, b2#696] : +- SubqueryAlias t2, `t2` : +- Project [_1#692 AS a2#695, _2#693 AS b2#696] : +- LocalRelation [_1#692, _2#693] +- SubqueryAlias t1, `t1` +- Project [_1#681 AS a1#684, _2#682 AS b1#685] +- LocalRelation [_1#681, _2#682] == Optimized Logical Plan == Project [_1#681 AS a1#684, _2#682 AS b1#685] +- Join LeftAnti, ((isnull((_1#681 = a2#695)) || isnull((b2#696 = _2#682))) || ((_1#681 = a2#695) && (b2#696 = _2#682))) :- LocalRelation [_1#681, _2#682] +- LocalRelation [a2#695, b2#696] {code} When the comparison between b1 and b2 is unknown, the subquery returns no row hence the result is all the rows from the parent. The problem in the plan above is the expression isnull((b2#696 = _2#682)) is evaluated to isnull( unknown ) which is true — that results in the entire expression of the LeftAnti is true. By the definition of LeftAnti, the rows are matched and should be filtered. > NOT IN subquery with correlated expressions may return incorrect result > ----------------------------------------------------------------------- > > Key: SPARK-18966 > URL: https://issues.apache.org/jira/browse/SPARK-18966 > Project: Spark > Issue Type: Bug > Components: SQL > Affects Versions: 2.0.0 > Reporter: Nattavut Sutyanyong > Labels: correctness > > {code} > Seq((1, 2)).toDF("a1", "b1").createOrReplaceTempView("t1") > Seq[(java.lang.Integer, java.lang.Integer)]((1, null)).toDF("a2", > "b2").createOrReplaceTempView("t2") > // The expected result is 1 row of (1,2) as shown in the next statement. > sql("select * from t1 where a1 not in (select a2 from t2 where b2 = b1)").show > +---+---+ > | a1| b1| > +---+---+ > +---+---+ > sql("select * from t1 where a1 not in (select a2 from t2 where b2 = 2)").show > +---+---+ > | a1| b1| > +---+---+ > | 1| 2| > +---+---+ > {code} > The two SQL statements above should return the same result. -- This message was sent by Atlassian JIRA (v6.3.4#6332) --------------------------------------------------------------------- To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org