[ https://issues.apache.org/jira/browse/DRILL-1706?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15206567#comment-15206567 ]
Arina Ielchiieva commented on DRILL-1706: ----------------------------------------- Reproduces in Drill 1.1.0. Starting from 1.2.0 is not reproducible up to 1.6.0. > date_sub function does not accept string as input in Drill > ---------------------------------------------------------- > > Key: DRILL-1706 > URL: https://issues.apache.org/jira/browse/DRILL-1706 > Project: Apache Drill > Issue Type: New Feature > Components: Functions - Drill, Functions - Hive > Affects Versions: 0.7.0 > Reporter: Hao Zhu > Assignee: Arina Ielchiieva > Fix For: 1.2.0 > > > "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 : > {code} > 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) > {code} > Comparing to Hive which is good. > {code} > 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) > {code} > Workaround in Drill: > {code} > 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) > {code} -- This message was sent by Atlassian JIRA (v6.3.4#6332)