Incomprehensible result of a comparison between a string and null value in PySpark

user_439621 0
Viewed 786

Can anyone explain to me the behavior of a comparison between a string and a null value.

DataFrame:

import pyspark.sql.functions as F

df = spark.createDataFrame([
["text_1,","text_1"],
["text_1,","text_2"],
["text_1,",None]
]).toDF("col1", "col2")


df1 = df.withColumn("equal", F.when(F.col("col1") == F.col("col2"), "equal").otherwise("not equal")
   
+-------+-------+----------+
|col1   |col2   |equal     |
+-------+-------+----------+
|text_1 |text_1 |equal     |
|text_1 |text_2 |not equal |
|text_1 |null   |not equal |  <*
+-------+-------+----------+


df2 = df.withColumn("equal", F.when(F.col("col1") != F.col("col2"), "equal").otherwise("not equal")

+-------+-------+----------+
|col1   |col2   |equal     |
+-------+-------+----------+
|text_1 |text_1 |equal     |
|text_1 |text_2 |not equal |
|text_1 |null   |equal     |   <*
+-------+-------+----------+

The is equal comparison seems to go well but the is not equal goes wrong.

Can anyone explain this to me and how can I solve this without checking on .isNotNull or fill the null values with an empty string (if possible).

pyspark apache-spark-sql apache-spark null
2 Answers

The reason why you got equal for comparison with null is because text1 != null gives null, which is interpreted as false by the when statement, so you got the unexpected equal from the otherwise statement.

You can use eqNullSafe, which returns False instead of null when one of the column is null. If you want to compare inequality, use the negation ~ of eqNullSafe.

import pyspark.sql.functions as F

df3 = df.withColumn("equal",
    F.when(~F.col("col1").eqNullSafe(F.col("col2")), "not equal")
     .otherwise("equal")
)

df3.show()
+------+------+---------+
|  col1|  col2|    equal|
+------+------+---------+
|text_1|text_1|    equal|
|text_1|text_2|not equal|
|text_1|  null|not equal|
+------+------+---------+

If you want to fill the null values with an empty string, you can use coalesce:

import pyspark.sql.functions as F

df4 = df.withColumn("equal",
    F.when(F.col("col1") != F.coalesce(F.col("col2"), F.lit("")), "not equal")
     .otherwise("equal")
)

df4.show()
+------+------+---------+
|  col1|  col2|    equal|
+------+------+---------+
|text_1|text_1|    equal|
|text_1|text_2|not equal|
|text_1|  null|not equal|
+------+------+---------+
user_141657300

NULL is not a value but represents the lack of value. You can't compare it to a value nor to another null, the comparison always interpreted as false in if, when functions. For null safe equality check, you can also use SQL expression is distinct from:

from pyspark.sql import functions as F

df1 = df.withColumn(
    "equal",
    F.when(
        F.expr("col1 is distinct from col2"), "not equal"
    ).otherwise("equal")
)

df1.show()

# +------+------+---------+
# |  col1|  col2|    equal|
# +------+------+---------+
# |text_1|text_1|    equal|
# |text_1|text_2|not equal|
# |text_1|  null|not equal|
# +------+------+---------+

Or using <=> operator:

df1 = df.withColumn(
    "equal",
    F.when(
        ~F.expr("col1 <=> col2"), "not equal"
    ).otherwise("equal")
)
user_13865510
Related