Uploaded image for project: 'Spark'
  1. Spark
  2. SPARK-23549

Spark SQL unexpected behavior when comparing timestamp to date

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.6.3, 2.0.2, 2.1.2, 2.2.1, 2.3.0
    • Fix Version/s: 2.4.0
    • Component/s: SQL
    • Labels:

      Description

      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|
      
      +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

      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

      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
      

      Is this a bug for Spark or a feature?

        Attachments

          Activity

            People

            • Assignee:
              kiszk Kazuaki Ishizaki
              Reporter:
              djiangxu Dong Jiang
            • Votes:
              0 Vote for this issue
              Watchers:
              9 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: