drill-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rahul Raj <rahul....@option3consulting.com>
Subject Re: Error while applying interval on a postgresql query
Date Sat, 10 Jun 2017 01:15:23 GMT
It works  fine on csv/parquet files. Issue happens on rdbms tables.

On Jun 10, 2017 04:48, "Boaz Ben-Zvi" <bben-zvi@mapr.com> wrote:

> This works fine on a json table:
>
> 0: jdbc:drill:zk=local> select * from dfs.`/data/test2.json` where
> DATE_ADD(CAST(START_DATE as DATE),interval '1' second) < CAST(CURRENT_DATE
> as DATE);
> +-----+-------------+
> | id  | start_date  |
> +-----+-------------+
> | 1   | 1997-10-27  |
> | 2   | 1997-10-27  |
> | 3   | 1997-10-28  |
> | 4   | 1997-10-29  |
> | 5   | 1997-10-30  |
> | 6   | 1997-10-31  |
> +-----+-------------+
> 6 rows selected (0.299 seconds)
>
> Where the plan has the predicate’s filter above the scan. With the jdbc
> plugin the predicate is probably pushed down, and there may be some bug
> there related to intervals.
>
>     Boaz
>
> On 6/7/17, 10:16 PM, "Rahul Raj" <rahul.raj@option3consulting.com> wrote:
>
>     Any thoughts why this could be happening? It happens on 1.10 /Oracle
>     combination too, and looks like its related with Calcite.
>
>     From what I experience, Drill fails on any date manipulation in the
> where
>     part while querying on a table from JDBC Storage plugin.
>
>     This query works fine - select DATE_ADD(CAST(STARTED_AT as
> DATE),interval
>     '1' second) < CAST(CURRENT_DATE as DATE) from
> config_1.APP.EXECUTIONSTEP
>     This fails - select * from config_1.APP.EXECUTIONSTEP  where
>     DATE_ADD(CAST(STARTED_AT as DATE),interval '1' second) <
> CAST(CURRENT_DATE
>     as DATE)
>
>     org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR:
>     AssertionError: 1000: INTERVAL_DAY_TIME [Error Id:
>     4a2f277f-d7d9-4306-8bfc-8a58e30d3991 on vpc12.o3c.in:31010]
>
>     Regards,
>     Rahul
>
>     On Tue, Mar 21, 2017 at 8:57 AM, Rahul Raj <
> rahul.raj@option3consulting.com>
>     wrote:
>
>     > 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-zvi@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.raj@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.clo
>     >> se(Foreman.java:825)
>     >>
>     >>     [drill-java-exec-1.9.0.jar:1.9.0]
>     >>
>     >>     at org.apache.drill.exec.work.foreman.Foreman.moveToState(Forem
>     >> an.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(ThreadPool
>     >> Executor.java:1142)
>     >>
>     >>     [na:1.8.0_60]
>     >>
>     >>     at
>     >>
>     >>     java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoo
>     >> lExecutor.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.toSq
>     >> l(JdbcImplementor.java:179)
>     >>
>     >>     ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19]
>     >>
>     >>     at
>     >>
>     >>     org.apache.calcite.adapter.jdbc.JdbcImplementor$Context.toSq
>     >> l(JdbcImplementor.java:268)
>     >>
>     >>     ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19]
>     >>
>     >>     at
>     >>
>     >>     org.apache.calcite.adapter.jdbc.JdbcImplementor$Context.toSq
>     >> l(JdbcImplementor.java:212)
>     >>
>     >>     ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19]
>     >>
>     >>     at
>     >>
>     >>     org.apache.calcite.adapter.jdbc.JdbcImplementor$Context.toSq
>     >> l(JdbcImplementor.java:268)
>     >>
>     >>     ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19]
>     >>
>     >>     at
>     >>
>     >>     org.apache.calcite.adapter.jdbc.JdbcImplementor$Context.toSq
>     >> l(JdbcImplementor.java:212)
>     >>
>     >>     ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19]
>     >>
>     >>     at
>     >>
>     >>     org.apache.calcite.adapter.jdbc.JdbcImplementor$Context.toSq
>     >> l(JdbcImplementor.java:268)
>     >>
>     >>     ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19]
>     >>
>     >>     at
>     >>
>     >>     org.apache.calcite.adapter.jdbc.JdbcImplementor$Context.toSq
>     >> l(JdbcImplementor.java:212)
>     >>
>     >>     ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19]
>     >>
>     >>     at
>     >>
>     >>     org.apache.calcite.adapter.jdbc.JdbcRules$JdbcFilter.impleme
>     >> nt(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.finali
>     >> zeRel(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(AbstractRelNod
>     >> e.java:256)
>     >>
>     >>     ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19]
>     >>
>     >>     at org.apache.calcite.rel.RelShuttleImpl.visitChild(RelShuttleI
>     >> mpl.java:53)
>     >>
>     >>     ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19]
>     >>
>     >>     at
>     >>
>     >>     org.apache.calcite.rel.RelShuttleImpl.visitChildren(RelShutt
>     >> leImpl.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(AbstractRelNod
>     >> e.java:256)
>     >>
>     >>     ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19]
>     >>
>     >>     at org.apache.calcite.rel.RelShuttleImpl.visitChild(RelShuttleI
>     >> mpl.java:53)
>     >>
>     >>     ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19]
>     >>
>     >>     at
>     >>
>     >>     org.apache.calcite.rel.RelShuttleImpl.visitChildren(RelShutt
>     >> leImpl.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(AbstractRelNod
>     >> e.java:256)
>     >>
>     >>     ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19]
>     >>
>     >>     at org.apache.calcite.rel.RelShuttleImpl.visitChild(RelShuttleI
>     >> mpl.java:53)
>     >>
>     >>     ~[calcite-core-1.4.0-drill-r19.jar:1.4.0-drill-r19]
>     >>
>     >>     at
>     >>
>     >>     org.apache.calcite.rel.RelShuttleImpl.visitChildren(RelShutt
>     >> leImpl.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(AbstractRelNod
>     >> e.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.getPhysical
>     >> Plan(DrillSqlWorker.java:123)
>     >>
>     >>     ~[drill-java-exec-1.9.0.jar:1.9.0]
>     >>
>     >>     at
>     >>
>     >>     org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan(Dri
>     >> llSqlWorker.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.****
>
>
>

-- 
**** 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.****

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message