Discussion:
Error while applying interval on a postgresql query
Add Reply
Rahul Raj
2017-03-20 15:10:19 UTC
Reply
Permalink
Raw Message
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.****
Boaz Ben-Zvi
2017-03-20 21:03:10 UTC
Reply
Permalink
Raw Message
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" <***@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.****
Rahul Raj
2017-03-21 03:27:31 UTC
Reply
Permalink
Raw Message
This happens on a JDBC Storage plugin to Postgres. It errors out on
cast/DATE_ADD
function call.

Regards,
Rahul
Post by Boaz Ben-Zvi
What do you mean by ‘postrgresql table’ ?
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
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]
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
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.****
Rahul Raj
2017-06-08 05:16:01 UTC
Reply
Permalink
Raw Message
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
This happens on a JDBC Storage plugin to Postgres. It errors out on cast/DATE_ADD
function call.
Regards,
Rahul
Post by Boaz Ben-Zvi
What do you mean by ‘postrgresql table’ ?
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
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
]
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
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.****
Boaz Ben-Zvi
2017-06-09 23:18:22 UTC
Reply
Permalink
Raw Message
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" <***@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
This happens on a JDBC Storage plugin to Postgres. It errors out on cast/DATE_ADD
function call.
Regards,
Rahul
What do you mean by ‘postrgresql table’ ?
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
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
]
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
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-
Rahul Raj
2017-06-10 01:15:23 UTC
Reply
Permalink
Raw Message
It works fine on csv/parquet files. Issue happens on rdbms tables.
Post by Boaz Ben-Zvi
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
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)
4a2f277f-d7d9-4306-8bfc-8a58e30d3991 on vpc12.o3c.in:31010]
Regards,
Rahul
On Tue, Mar 21, 2017 at 8:57 AM, Rahul Raj <
Post by Rahul Raj
This happens on a JDBC Storage plugin to Postgres. It errors out on
cast/DATE_ADD
Post by Rahul Raj
function call.
Regards,
Rahul
Post by Boaz Ben-Zvi
What do you mean by ‘postrgresql table’ ?
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
Post by Rahul Raj
Post by Boaz Ben-Zvi
'1998-02-13' limit 2;
+-------+
| id |
+-------+
| 462 |
| 1048 |
+-------+
2 rows selected (0.21 seconds)
Thanks,
Boaz
Hi,
Drill 1.9 gives error while applying interval function on a
postgresql
Post by Rahul Raj
Post by Boaz Ben-Zvi
table. The below two queries error out. Not sure about the other
databases.
select `id` from (select * from config_1.public.project_
release)
Post by Rahul Raj
Post by Boaz Ben-Zvi
where
CAST(DATE_ADD(`start_date`,interval '19800' second(5)) AS
DATE) =
Post by Rahul Raj
Post by Boaz Ben-Zvi
DATE
'2011-11-11'
select `id` from (select * from config_1.public.project_
release)
Post by Rahul Raj
Post by Boaz Ben-Zvi
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)
Post by Rahul Raj
Post by Boaz Ben-Zvi
where
CAST(`start_date` AS DATE) = DATE '2011-11-11'
[Error Id: 048fe4b9-ecab-40fe-aca9-d57eb2df9b0c on
vpc12.o3c.in:31010
Post by Rahul Raj
Post by Boaz Ben-Zvi
]
AssertionError: 1000: INTERVAL_DAY_TIME
[Error Id: 048fe4b9-ecab-40fe-aca9-d57eb2df9b0c on
vpc12.o3c.in:31010
Post by Rahul Raj
Post by Boaz Ben-Zvi
]
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)
Post by Rahul Raj
Post by Boaz Ben-Zvi
[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]
Unexpected
INTERVAL_DAY_TIME
Post by Rahul Raj
Post by Boaz Ben-Zvi
... 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)
Post by Rahul Raj
Post by Boaz Ben-Zvi
~[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)
Post by Rahul Raj
Post by Boaz Ben-Zvi
[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
AssertionError: 1000: INTERVAL_DAY_TIME
Regards,
Rahul
--
**** This email and any files transmitted with it are
confidential and
Post by Rahul Raj
Post by Boaz Ben-Zvi
intended solely for the use of the individual or entity to whom
it is
Post by Rahul Raj
Post by Boaz Ben-Zvi
addressed. If you are not the named addressee then you should
not
Post by Rahul Raj
Post by Boaz Ben-Zvi
disseminate, distribute or copy this e-mail. Please notify the
sender
Post by Rahul Raj
Post by Boaz Ben-Zvi
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.****
Loading...