[ https://issues.apache.org/jira/browse/DRILL-5578?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16287010#comment-16287010 ]
Julian Hyde commented on DRILL-5578: ------------------------------------ These are the lines in Calcite JdbcImplementor.java: {code} switch (literal.getTypeName().getFamily()) { case CHARACTER: return SqlLiteral.createCharString((String) literal.getValue2(), POS); ... case ANY: case NULL: switch (literal.getTypeName()) { case NULL: return SqlLiteral.createNull(POS); // fall through } default: throw new AssertionError(literal + ": " + literal.getTypeName()); } {code} So it appears that Calcite can't convert an interval literal from a RexNode to a SQL string. Possible explanations: (1) Calcite usually represents interval literals as numerics - but Drill is doing things differently; (2) we've not tested before because we don't know of any standard SQL functions that accept intervals as arguments. > Drill fails on date functions in 'where clause' when queried on a JDBC > Storage plugin > ------------------------------------------------------------------------------------- > > Key: DRILL-5578 > URL: https://issues.apache.org/jira/browse/DRILL-5578 > Project: Apache Drill > Issue Type: Bug > Affects Versions: 1.9.0, 1.10.0 > Reporter: Rahul Raj > > 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 -- This message was sent by Atlassian JIRA (v6.4.14#64029)