This happens on a JDBC Storage plugin to Postgres. It errors out on cast/DATE_ADD function call.
Regards, Rahul On Tue, Mar 21, 2017 at 2:33 AM, Boaz Ben-Zvi <bben-...@mapr.com> wrote: > 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" <rahul....@option3consulting.com> 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.**** > > > -- **** 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.****
