[SPARK-18966] NOT IN subquery with correlated expressions may return incorrect result

• Type: Sub-task
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 2.0.0
• Fix Version/s: 2.2.0
• Component/s: SQL
• Labels: correctness
• Assignee: Nattavut Sutyanyong
• Reporter: Nattavut Sutyanyong
• Created: 21/Dec/16 18:18
• Updated: 14/Mar/17 19:36
• Resolved: 14/Mar/17 19:36

Description

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|
+---+---+

The two SQL statements above should return the same result.

Issue Links

• is related to: SPARK-19017 NOT IN subquery with more than one column may return incorrect results
• relates to: SPARK-18455 General support for correlated subquery processing
• links to: [Github] Pull Request #17294 (nsyca)