drill-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Nathan Smith (JIRA)" <j...@apache.org>
Subject [jira] [Created] (DRILL-4348) Date arithmetic issues
Date Wed, 03 Feb 2016 20:08:39 GMT
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)

Mime
View raw message