How Spark casts data works when comparing different datatypes

user_1437346 0
Viewed 59

When I compare a Int with a String, Spark casts the right hand side

sql("SELECT 1621335543 >= '2021-05-18'").explain(true)

== Analyzed Logical Plan ==
(1621335543 >= CAST(2021-05-18 AS INT)): boolean
Project [(1621335543 >= cast(2021-05-18 as int)) AS (1621335543 >= CAST(2021-05-18 AS INT))#1309]
+- OneRowRelation

In this case, Spark casts the value '2021-05-18' to Int and the result is NULL.

But when I compare a Timestamp with a String, Spark casts the left hand side

sql("SELECT current_timestamp() >= '2021-05-18'").explain(true)

== Analyzed Logical Plan ==
(CAST(current_timestamp() AS STRING) >= 2021-05-18): boolean
Project [(cast(current_timestamp() as string) >= 2021-05-18) AS (CAST(current_timestamp() AS STRING) >= 2021-05-18)#1313]
+- OneRowRelation

How Spark chooses which DataType to cast ?

apache-spark-sql apache-spark
1 Answers

This comment from spark code base is related to your question and following should be the expected behaviour in latest spark version:

For equality between string and timestamp we cast the string to a timestamp so that things like rounding of subsecond precision does not affect the comparison.

Relevant reference to spark code is here. Which spark version are you using ?

user_28461680
Related