[ https://issues.apache.org/jira/browse/DRILL-4348?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Nathan Smith updated DRILL-4348: -------------------------------- Description: 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! was: 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! > 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)