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

Drill fails on date functions in 'where clause' when queried on a JDBC Storage plugin

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.9.0, 1.10.0
    • Fix Version/s: 1.15.0
    • Component/s: Storage - JDBC
    • Labels:
      None

      Description

      Drill 1.9/1.10 fails on any date manipulation in the where part while querying on a table from JDBC Storage plugin. Issue happens on postgres and Oracle.

      The following two queries error out:

      select * from config_1.APP.EXECUTIONSTEP where DATE_ADD(CAST(STARTED_AT as DATE),interval '1' second) < CAST(CURRENT_DATE as DATE)

      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'

      However date manipulation succeeds when date function are applied on the selected fields

      select DATE_ADD(CAST(STARTED_AT as DATE),interval '1' second) < CAST(CURRENT_DATE as DATE) from config_1.APP.EXECUTIONSTEP

      select `id` from (select * from config_1.public.project_release) where CAST(`start_date` AS DATE) = DATE '2011-11-11'

      Error:

      [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

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                rajrahul Rahul Raj
              • Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: