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
' - '. Supported form(s): ' - '
' - '
' - '
[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 ' - '.
Supported form(s): ' - '
' - '
' - '
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 ' -
'. Supported form(s): ' - '
' - '
' - '
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.sq
