Nathan Smith created DRILL-4348:
-----------------------------------
Summary: Date arithmetic issues
Key: DRILL-4348
URL: https://issues.apache.org/jira/browse/DRILL-4348
Project: Apache Drill
Issue Type: Bug
Components: Execution - Data Types, Execution - Relational Operators
Affects Versions: 1.4.0
Reporter: Nathan Smith
I have been encountering issues while trying to do date(time) arithmetic.
According to https://issues.apache.org/jira/browse/DRILL-549, the subtraction
operator should work with DATE types, but I am getting following error when
executing this type of query:
{code}
SELECT
COUNT((CAST(tran_dttm AS DATE) - DATE '2012-07-01') < 0) AS
before_july,
COUNT((CAST(tran_dttm AS DATE) - DATE '2012-07-01') > 0) AS after_july
FROM dfs.root.ldg_tran_parquet WHERE EXTRACT(year FROM tran_dttm) = 2012
{code}
{code}
2016-02-03 15:02:39,891 [qtp254153532-52] ERROR
o.a.d.e.server.rest.QueryResources - Query from Web UI Failed
org.apache.drill.common.exceptions.UserRemoteException: VALIDATION ERROR: From
line 2, column 9 to line 2, column 59: Cannot apply '-' to arguments of type
'<DATE> - <DATE>'. Supported form(s): '<NUMERIC> - <NUMERIC>'
'<DATETIME_INTERVAL> - <DATETIME_INTERVAL>'
'<DATETIME> - <DATETIME_INTERVAL>'
[Error Id: 73b5df2c-14f6-424c-83c7-af4a6158247c on
drill-standalone.aunsight.office:31010]
(org.apache.calcite.tools.ValidationException)
org.apache.calcite.runtime.CalciteContextException: From line 2, column 9 to
line 2, column 59: Cannot apply '-' to arguments of type '<DATE> - <DATE>'.
Supported form(s): '<NUMERIC> - <NUMERIC>'
'<DATETIME_INTERVAL> - <DATETIME_INTERVAL>'
'<DATETIME> - <DATETIME_INTERVAL>'
org.apache.calcite.prepare.PlannerImpl.validate():189
org.apache.calcite.prepare.PlannerImpl.validateAndGetType():198
org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateNode():451
org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateAndConvert():198
org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan():167
org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan():197
org.apache.drill.exec.work.foreman.Foreman.runSQL():909
org.apache.drill.exec.work.foreman.Foreman.run():244
java.util.concurrent.ThreadPoolExecutor.runWorker():1145
java.util.concurrent.ThreadPoolExecutor$Worker.run():615
java.lang.Thread.run():745
Caused By (org.apache.calcite.runtime.CalciteContextException) From line 2,
column 9 to line 2, column 59: Cannot apply '-' to arguments of type '<DATE> -
<DATE>'. Supported form(s): '<NUMERIC> - <NUMERIC>'
'<DATETIME_INTERVAL> - <DATETIME_INTERVAL>'
'<DATETIME> - <DATETIME_INTERVAL>'
sun.reflect.NativeConstructorAccessorImpl.newInstance0():-2
sun.reflect.NativeConstructorAccessorImpl.newInstance():57
sun.reflect.DelegatingConstructorAccessorImpl.newInstance():45
java.lang.reflect.Constructor.newInstance():526
org.apache.calcite.runtime.Resources$ExInstWithCause.ex():405
org.apache.calcite.sql.SqlUtil.newContextException():714
org.apache.calcite.sql.SqlUtil.newContextException():702
org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError():3931
org.apache.calcite.sql.SqlCallBinding.newValidationSignatureError():275
org.apache.calcite.sql.type.FamilyOperandTypeChecker.checkSingleOperandType():92
org.apache.calcite.sql.type.FamilyOperandTypeChecker.checkOperandTypes():109
org.apache.calcite.sql.type.CompositeOperandTypeChecker.checkOperandTypes():245
org.apache.calcite.sql.SqlOperator.checkOperandTypes():563
org.apache.calcite.sql.SqlOperator.validateOperands():420
org.apache.calcite.sql.SqlOperator.deriveType():487
org.apache.calcite.sql.SqlBinaryOperator.deriveType():143
org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit():4268
org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit():4255
org.apache.calcite.sql.SqlCall.accept():130
org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl():1495
org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType():1478
org.apache.calcite.sql.SqlOperator.deriveType():483
org.apache.calcite.sql.SqlBinaryOperator.deriveType():143
org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit():4268
org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit():4255
org.apache.calcite.sql.SqlCall.accept():130
org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl():1495
org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType():1478
org.apache.calcite.sql.SqlFunction.deriveType():288
org.apache.calcite.sql.SqlFunction.deriveType():230
org.apache.calcite.sql.fun.SqlCountAggFunction.deriveType():88
org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit():4268
org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit():4255
org.apache.calcite.sql.SqlCall.accept():130
org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl():1495
org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType():1478
org.apache.calcite.sql.SqlAsOperator.deriveType():132
org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit():4268
org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit():4255
org.apache.calcite.sql.SqlCall.accept():130
org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl():1495
org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType():1478
org.apache.calcite.sql.validate.SqlValidatorImpl.expandSelectItem():440
org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList():3427
org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect():2995
org.apache.calcite.sql.validate.SelectNamespace.validateImpl():60
org.apache.calcite.sql.validate.AbstractNamespace.validate():86
org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():877
org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery():863
org.apache.calcite.sql.SqlSelect.validate():210
org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression():837
org.apache.calcite.sql.validate.SqlValidatorImpl.validate():551
org.apache.calcite.prepare.PlannerImpl.validate():187
org.apache.calcite.prepare.PlannerImpl.validateAndGetType():198
org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateNode():451
org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateAndConvert():198
org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan():167
org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan():197
org.apache.drill.exec.work.foreman.Foreman.runSQL():909
org.apache.drill.exec.work.foreman.Foreman.run():244
java.util.concurrent.ThreadPoolExecutor.runWorker():1145
java.util.concurrent.ThreadPoolExecutor$Worker.run():615
java.lang.Thread.run():745
Caused By (org.apache.calcite.sql.validate.SqlValidatorException) Cannot
apply '-' to arguments of type '<DATE> - <DATE>'. Supported form(s): '<NUMERIC>
- <NUMERIC>'
'<DATETIME_INTERVAL> - <DATETIME_INTERVAL>'
'<DATETIME> - <DATETIME_INTERVAL>'
sun.reflect.NativeConstructorAccessorImpl.newInstance0():-2
sun.reflect.NativeConstructorAccessorImpl.newInstance():57
sun.reflect.DelegatingConstructorAccessorImpl.newInstance():45
java.lang.reflect.Constructor.newInstance():526
org.apache.calcite.runtime.Resources$ExInstWithCause.ex():405
org.apache.calcite.runtime.Resources$ExInst.ex():514
org.apache.calcite.sql.SqlUtil.newContextException():714
org.apache.calcite.sql.SqlUtil.newContextException():702
org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError():3931
org.apache.calcite.sql.SqlCallBinding.newValidationSignatureError():275
org.apache.calcite.sql.type.FamilyOperandTypeChecker.checkSingleOperandType():92
org.apache.calcite.sql.type.FamilyOperandTypeChecker.checkOperandTypes():109
org.apache.calcite.sql.type.CompositeOperandTypeChecker.checkOperandTypes():245
org.apache.calcite.sql.SqlOperator.checkOperandTypes():563
org.apache.calcite.sql.SqlOperator.validateOperands():420
org.apache.calcite.sql.SqlOperator.deriveType():487
org.apache.calcite.sql.SqlBinaryOperator.deriveType():143
org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit():4268
org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit():4255
org.apache.calcite.sql.SqlCall.accept():130
org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl():1495
org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType():1478
org.apache.calcite.sql.SqlOperator.deriveType():483
org.apache.calcite.sql.SqlBinaryOperator.deriveType():143
org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit():4268
org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit():4255
org.apache.calcite.sql.SqlCall.accept():130
org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl():1495
org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType():1478
org.apache.calcite.sql.SqlFunction.deriveType():288
org.apache.calcite.sql.SqlFunction.deriveType():230
org.apache.calcite.sql.fun.SqlCountAggFunction.deriveType():88
org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit():4268
org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit():4255
org.apache.calcite.sql.SqlCall.accept():130
org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl():1495
org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType():1478
org.apache.calcite.sql.SqlAsOperator.deriveType():132
org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit():4268
org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit():4255
org.apache.calcite.sql.SqlCall.accept():130
org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl():1495
org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType():1478
org.apache.calcite.sql.validate.SqlValidatorImpl.expandSelectItem():440
org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList():3427
org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect():2995
org.apache.calcite.sql.validate.SelectNamespace.validateImpl():60
org.apache.calcite.sql.validate.AbstractNamespace.validate():86
org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():877
org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery():863
org.apache.calcite.sql.SqlSelect.validate():210
org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression():837
org.apache.calcite.sql.validate.SqlValidatorImpl.validate():551
org.apache.calcite.prepare.PlannerImpl.validate():187
org.apache.calcite.prepare.PlannerImpl.validateAndGetType():198
org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateNode():451
org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateAndConvert():198
org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan():167
org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan():197
org.apache.drill.exec.work.foreman.Foreman.runSQL():909
org.apache.drill.exec.work.foreman.Foreman.run():244
java.util.concurrent.ThreadPoolExecutor.runWorker():1145
java.util.concurrent.ThreadPoolExecutor$Worker.run():615
java.lang.Thread.run():745
at
org.apache.drill.exec.rpc.user.QueryResultHandler.resultArrived(QueryResultHandler.java:119)
~[drill-java-exec-1.4.0.jar:1.4.0]
at
org.apache.drill.exec.rpc.user.UserClient.handleReponse(UserClient.java:113)
~[drill-java-exec-1.4.0.jar:1.4.0]
at
org.apache.drill.exec.rpc.BasicClientWithConnection.handle(BasicClientWithConnection.java:46)
~[drill-rpc-1.4.0.jar:1.4.0]
at
org.apache.drill.exec.rpc.BasicClientWithConnection.handle(BasicClientWithConnection.java:31)
~[drill-rpc-1.4.0.jar:1.4.0]
at org.apache.drill.exec.rpc.RpcBus.handle(RpcBus.java:69)
~[drill-rpc-1.4.0.jar:1.4.0]
at org.apache.drill.exec.rpc.RpcBus$RequestEvent.run(RpcBus.java:400)
~[drill-rpc-1.4.0.jar:1.4.0]
at
org.apache.drill.common.SerializedExecutor$RunnableProcessor.run(SerializedExecutor.java:105)
~[drill-rpc-1.4.0.jar:1.4.0]
at
org.apache.drill.exec.rpc.RpcBus$SameExecutor.execute(RpcBus.java:264)
~[drill-rpc-1.4.0.jar:1.4.0]
at
org.apache.drill.common.SerializedExecutor.execute(SerializedExecutor.java:142)
~[drill-rpc-1.4.0.jar:1.4.0]
at
org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(RpcBus.java:298)
~[drill-rpc-1.4.0.jar:1.4.0]
at
org.apache.drill.exec.rpc.RpcBus$InboundHandler.decode(RpcBus.java:269)
~[drill-rpc-1.4.0.jar:1.4.0]
at
io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:89)
~[netty-codec-4.0.27.Final.jar:4.0.27.Final]
at
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:339)
~[netty-transport-4.0.27.Final.jar:4.0.27.Final]
at
io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:324)
~[netty-transport-4.0.27.Final.jar:4.0.27.Final]
at
io.netty.handler.timeout.IdleStateHandler.channelRead(IdleStateHandler.java:254)
~[netty-handler-4.0.27.Final.jar:4.0.27.Final]
at
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:339)
~[netty-transport-4.0.27.Final.jar:4.0.27.Final]
at
io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:324)
~[netty-transport-4.0.27.Final.jar:4.0.27.Final]
at
io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103)
~[netty-codec-4.0.27.Final.jar:4.0.27.Final]
at
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:339)
~[netty-transport-4.0.27.Final.jar:4.0.27.Final]
at
io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:324)
~[netty-transport-4.0.27.Final.jar:4.0.27.Final]
at
io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:242)
~[netty-codec-4.0.27.Final.jar:4.0.27.Final]
at
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:339)
~[netty-transport-4.0.27.Final.jar:4.0.27.Final]
at
io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:324)
~[netty-transport-4.0.27.Final.jar:4.0.27.Final]
at
io.netty.channel.ChannelInboundHandlerAdapter.channelRead(ChannelInboundHandlerAdapter.java:86)
~[netty-transport-4.0.27.Final.jar:4.0.27.Final]
at
io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:339)
~[netty-transport-4.0.27.Final.jar:4.0.27.Final]
at
io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:324)
~[netty-transport-4.0.27.Final.jar:4.0.27.Final]
at
io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:847)
~[netty-transport-4.0.27.Final.jar:4.0.27.Final]
at
io.netty.channel.epoll.AbstractEpollStreamChannel$EpollStreamUnsafe.epollInReady(AbstractEpollStreamChannel.java:618)
~[netty-transport-native-epoll-4.0.27.Final-linux-x86_64.jar:na]
at
io.netty.channel.epoll.EpollEventLoop.processReady(EpollEventLoop.java:329)
~[netty-transport-native-epoll-4.0.27.Final-linux-x86_64.jar:na]
at io.netty.channel.epoll.EpollEventLoop.run(EpollEventLoop.java:250)
~[netty-transport-native-epoll-4.0.27.Final-linux-x86_64.jar:na]
at
io.netty.util.concurrent.SingleThreadEventExecutor$2.run(SingleThreadEventExecutor.java:111)
~[netty-common-4.0.27.Final.jar:4.0.27.Final]
at java.lang.Thread.run(Thread.java:745) ~[na:1.7.0_95]
{code}
I know that there are other ways to do this kind of query, but this is similar
to a query that Tableau is generating, which I am unable to customize. I have
observed similar errors with other date arithmetic operators such as '<, >, <=,
>='.
Thanks!
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
