You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@drill.apache.org by "Nathan Smith (JIRA)" <ji...@apache.org> on 2016/02/03 21:08:39 UTC
[jira] [Created] (DRILL-4348) Date arithmetic issues
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)