What do you mean by ‘postrgresql table’ ? I just retried the query below (on a json table) and it worked OK:
0: jdbc:drill:zk=local> select * from test2 limit 2; +-----+-------------+ | id | start_date | +-----+-------------+ | 1 | 1997-10-27 | | 2 | 1997-10-27 | +-----+-------------+ 2 rows selected (0.178 seconds) 0: jdbc:drill:zk=local> select `id` from (select * from test2) where cast(DATE_ADD(`start_date`, interval '19800' second(5)) as DATE) = DATE '1998-02-13' limit 2; +-------+ | id | +-------+ | 462 | | 1048 | +-------+ 2 rows selected (0.21 seconds) Thanks, Boaz On 3/20/17, 8:10 AM, "Rahul Raj" <[email protected]> wrote: Hi, Drill 1.9 gives error while applying interval function on a postgresql table. The below two queries error out. Not sure about the other databases. select `id` from (select * from config_1.public.project_release) where CAST(DATE_ADD(`start_date`,interval '19800' second(5)) AS DATE) = DATE '2011-11-11' select `id` from (select * from config_1.public.project_release) where CAST(DATE_ADD(`start_date`,interval '1' second) AS DATE) = DATE '2011-11-11' However query below succeeds select `id` from (select * from config_1.public.project_release) where CAST(`start_date` AS DATE) = DATE '2011-11-11' [Error Id: 048fe4b9-ecab-40fe-aca9-d57eb2df9b0c on vpc12.o3c.in:31010] org.apache.drill.common.exceptions.UserException: SYSTEM ERROR: AssertionError: 1000: INTERVAL_DAY_TIME [Error Id: 048fe4b9-ecab-40fe-aca9-d57eb2df9b0c on vpc12.o3c.in:31010] at org.apache.drill.common.exceptions.UserException$Builder.build(UserException.java:543) ~[drill-common-1.9.0.jar:1.9.0] at org.apache.drill.exec.work.foreman.Foreman$ForemanResult.close(Foreman.java:825) [drill-java-exec-1.9.0.jar:1.9.0] at org.apache.drill.exec.work.foreman.Foreman.moveToState(Foreman.java:935) [drill-java-exec-1.9.0.jar:1.9.0] at org.apache.drill.exec.work.foreman.Foreman.run(Foreman.java:281) [drill-java-exec-1.9.0.jar:1.9.0] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_60] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_60] at java.lang.Thread.run(Thread.java:745) [na:1.8.0_60] Caused by: org.apache.drill.exec.work.foreman.ForemanException: Unexpected exception during fragment initialization: 1000: INTERVAL_DAY_TIME ... 4 common frames omitted Caused by: java.lang.AssertionError: 1000: INTERVAL_DAY_TIME at org.apache.calcite.adapter.jdbc.JdbcImplementor$Context.toSql(JdbcImplementor.java:179) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.adapter.jdbc.JdbcImplementor$Context.toSql(JdbcImplementor.java:268) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.adapter.jdbc.JdbcImplementor$Context.toSql(JdbcImplementor.java:212) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.adapter.jdbc.JdbcImplementor$Context.toSql(JdbcImplementor.java:268) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.adapter.jdbc.JdbcImplementor$Context.toSql(JdbcImplementor.java:212) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.adapter.jdbc.JdbcImplementor$Context.toSql(JdbcImplementor.java:268) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.adapter.jdbc.JdbcImplementor$Context.toSql(JdbcImplementor.java:212) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.adapter.jdbc.JdbcRules$JdbcFilter.implement(JdbcRules.java:658) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.adapter.jdbc.JdbcImplementor.visitChild(JdbcImplementor.java:118) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.drill.exec.store.jdbc.JdbcPrel.<init>(JdbcPrel.java:62) ~[drill-jdbc-storage-1.9.0.jar:1.9.0] at org.apache.drill.exec.store.jdbc.JdbcIntermediatePrel.finalizeRel(JdbcIntermediatePrel.java:66) ~[drill-jdbc-storage-1.9.0.jar:1.9.0] at org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler$PrelFinalizer.visit(DefaultSqlHandler.java:306) ~[drill-java-exec-1.9.0.jar:1.9.0] at org.apache.calcite.rel.AbstractRelNode.accept(AbstractRelNode.java:256) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.rel.RelShuttleImpl.visitChild(RelShuttleImpl.java:53) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.rel.RelShuttleImpl.visitChildren(RelShuttleImpl.java:68) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.rel.RelShuttleImpl.visit(RelShuttleImpl.java:126) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler$PrelFinalizer.visit(DefaultSqlHandler.java:308) ~[drill-java-exec-1.9.0.jar:1.9.0] at org.apache.calcite.rel.AbstractRelNode.accept(AbstractRelNode.java:256) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.rel.RelShuttleImpl.visitChild(RelShuttleImpl.java:53) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.rel.RelShuttleImpl.visitChildren(RelShuttleImpl.java:68) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.rel.RelShuttleImpl.visit(RelShuttleImpl.java:126) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler$PrelFinalizer.visit(DefaultSqlHandler.java:308) ~[drill-java-exec-1.9.0.jar:1.9.0] at org.apache.calcite.rel.AbstractRelNode.accept(AbstractRelNode.java:256) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.rel.RelShuttleImpl.visitChild(RelShuttleImpl.java:53) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.rel.RelShuttleImpl.visitChildren(RelShuttleImpl.java:68) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.calcite.rel.RelShuttleImpl.visit(RelShuttleImpl.java:126) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler$PrelFinalizer.visit(DefaultSqlHandler.java:308) ~[drill-java-exec-1.9.0.jar:1.9.0] at org.apache.calcite.rel.AbstractRelNode.accept(AbstractRelNode.java:256) ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19] at org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.convertToPrel(DefaultSqlHandler.java:425) ~[drill-java-exec-1.9.0.jar:1.9.0] at org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan(DefaultSqlHandler.java:169) ~[drill-java-exec-1.9.0.jar:1.9.0] at org.apache.drill.exec.planner.sql.DrillSqlWorker.getPhysicalPlan(DrillSqlWorker.java:123) ~[drill-java-exec-1.9.0.jar:1.9.0] at org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan(DrillSqlWorker.java:97) ~[drill-java-exec-1.9.0.jar:1.9.0] at org.apache.drill.exec.work.foreman.Foreman.runSQL(Foreman.java:1008) [drill-java-exec-1.9.0.jar:1.9.0] at org.apache.drill.exec.work.foreman.Foreman.run(Foreman.java:264) [drill-java-exec-1.9.0.jar:1.9.0] ... 3 common frames omitted 2017-03-20 14:16:44,943 [USER-rpc-event-queue] ERROR o.a.d.exec.server.rest.QueryWrapper - Query Failed org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR: AssertionError: 1000: INTERVAL_DAY_TIME Regards, Rahul -- **** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom it is addressed. If you are not the named addressee then you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and delete this e-mail from your system.****
