[SPARK-19017] NOT IN subquery with more than one column may return incorrect results

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 2.0.0, 2.0.1, 2.0.2, 2.1.0
• Fix Version/s: 2.1.1, 2.2.0
• Component/s: SQL
• Labels: correctness
• Assignee: Nattavut Sutyanyong
• Reporter: Nattavut Sutyanyong
• Created: 28/Dec/16 20:22
• Updated: 14/May/19 06:27
• Resolved: 24/Jan/17 22:32

Description

When putting more than one column in the NOT IN, the query may not return correctly if there is a null data. We can demonstrate the problem with the following data set and query:

Seq((2,1)).toDF("a1","b1").createOrReplaceTempView("t1")
Seq[(java.lang.Integer,java.lang.Integer)]((1,null)).toDF("a2","b2").createOrReplaceTempView("t2")

sql("select * from t1 where (a1,b1) not in (select a2,b2 from t2)").show
+---+---+
| a1| b1|
+---+---+
+---+---+

Issue Links

• relates to: SPARK-18966 NOT IN subquery with correlated expressions may return incorrect result
• links to: [Github] Pull Request #16467 (nsyca)