[SPARK-38790] Unexpected behaviour for "IN" operator in spark when null is involved in array

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Not A Problem
• Affects Version/s: 3.2.1
• Component/s: Spark Core, SQL

Environment

Tested on pyspark 3.2.1

Language

spark

• Assignee: Unassigned
• Reporter: Vishnu K Suman
• Created: 05/Apr/22 16:49
• Updated: 06/Apr/22 16:19
• Resolved: 06/Apr/22 16:19

Description

 

`IN`operator in spark.sql is giving unexpected results

1 in (null, 1) => true

1 in (null, 2) = null

I would have expected the second piece of code to throw false.

 

 

>>> spark.sql('SELECT 1 in (null, 1)').show()
+----------------+
|(1 IN (NULL, 1))|
+----------------+
|            true|
+----------------+

>>> spark.sql('SELECT 1 in (null, 2)').show()
+----------------+
|(1 IN (NULL, 2))|
+----------------+
|            null|
+----------------+