Dong Jiang created SPARK-23549: ---------------------------------- Summary: Spark SQL unexpected behavior when comparing timestamp to date Key: SPARK-23549 URL: https://issues.apache.org/jira/browse/SPARK-23549 Project: Spark Issue Type: Bug Components: SQL Affects Versions: 2.2.1 Reporter: Dong Jiang
{code:java} scala> spark.version res1: String = 2.2.1 scala> spark.sql("select cast('2017-03-01 00:00:00' as timestamp) between cast('2017-02-28' as date) and cast('2017-03-01' as date)").show +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |((CAST(CAST(2017-03-01 00:00:00 AS TIMESTAMP) AS STRING) >= CAST(CAST(2017-02-28 AS DATE) AS STRING)) AND (CAST(CAST(2017-03-01 00:00:00 AS TIMESTAMP) AS STRING) <= CAST(CAST(2017-03-01 AS DATE) AS STRING)))| +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | false| +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+{code} As shown above, when a timestamp is compared to date in SparkSQL, both timestamp and date are downcast to string, and leading to unexpected result. If run the same SQL in presto/Athena, I got the expected result {code:java} select cast('2017-03-01 00:00:00' as timestamp) between cast('2017-02-28' as date) and cast('2017-03-01' as date) _col0 1 true{code} -- This message was sent by Atlassian JIRA (v7.6.3#76005) --------------------------------------------------------------------- To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org