Uploaded image for project: 'Apache Drill'
  1. Apache Drill
  2. DRILL-1706

date_sub function does not accept string as input in Drill

    XMLWordPrintableJSON

    Details

      Description

      "date_sub" function does not accept string as input in Drill, however it does in Hive.
      This different behavior of the function will make customer re-write their query to use "cast as date".

      Minimum reproduce :

      0: jdbc:drill:zk=local> select date_sub('2014-11-12 16:45:22',15) from dfs.tmp.`drilltest/test.csv` ;
      Query failed: Failure while running fragment., Invalid format: "2014-11-12 16:45:22" is malformed at "14-11-12 16:45:22" [ 9a6f18da-eb1e-4d91-879a-8d9d528efd59 on 10.250.0.115:31010 ]
        (java.lang.IllegalArgumentException) Invalid format: "2014-11-12 16:45:22" is malformed at "14-11-12 16:45:22"
          org.joda.time.format.DateTimeFormatter.parseDateTime():873
          org.apache.drill.exec.test.generated.ProjectorGen23.doSetup():63
          org.apache.drill.exec.test.generated.ProjectorGen23.setup():97
          org.apache.drill.exec.physical.impl.project.ProjectRecordBatch.setupNewSchema():427
          org.apache.drill.exec.physical.impl.project.ProjectRecordBatch.buildSchema():270
          org.apache.drill.exec.physical.impl.validate.IteratorValidatorBatchIterator.buildSchema():80
          org.apache.drill.exec.physical.impl.ScreenCreator$ScreenRoot.buildSchema():95
          org.apache.drill.exec.work.fragment.FragmentExecutor.run():111
          org.apache.drill.exec.work.WorkManager$RunnableWrapper.run():249
          .......():0
      
      Error: exception while executing query: Failure while executing query. (state=,code=0)
      

      Comparing to Hive which is good.

      0: jdbc:hive2://n1a:10000/default> select date_sub('2014-11-12 16:45:22',15) from passwords limit 1 ; 
      +-------------+
      |     _c0     |
      +-------------+
      | 2014-10-28  |
      +-------------+
      1 row selected (6.568 seconds)
      

      Workaround in Drill:

      0: jdbc:drill:zk=local> select date_sub(cast('2014-11-12 16:45:22' as date),15) from dfs.tmp.`drilltest/test.csv` ;
      +------------+
      |   EXPR$0   |
      +------------+
      | 2014-10-28 |
      +------------+
      1 row selected (0.082 seconds)
      

        Attachments

          Activity

            People

            • Assignee:
              arina Arina Ielchiieva
              Reporter:
              haozhu Hao Zhu
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: