[CALCITE-5491] Allow TIME and DATE to be args for TIMESTAMPDIFF

• Type: Bug
• Status: Closed
• Priority: Blocker
• Resolution: Fixed
• Affects Version/s: 1.33.0
• Fix Version/s: 1.33.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Tanner Clary
• Reporter: Sergey Nuyanzin
• Created: 23/Jan/23 22:34
• Updated: 07/Feb/23 22:33
• Resolved: 25/Jan/23 03:38

Description

Set to blocker since it is a regression (same queries work on 1.32.0)
Several queries with TIMESTAMPDIFF started to fail like

    org.apache.calcite.runtime.CalciteContextException: From line 1, column 9 to line 1, column 66: Cannot apply 'TIMESTAMPDIFF' to arguments of type 'TIMESTAMPDIFF(<INTERVAL MONTH>, <DATE>, <DATE>)'. Supported form(s): 'TIMESTAMPDIFF(<ANY>, <TIMESTAMP>, <TIMESTAMP>)'
        at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:505)
        at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:945)
        at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:930)
        at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:5424)
        at org.apache.calcite.sql.SqlCallBinding.newValidationSignatureError(SqlCallBinding.java:401)
        at org.apache.calcite.sql.type.FamilyOperandTypeChecker.checkSingleOperandType(FamilyOperandTypeChecker.java:108)
        at org.apache.calcite.sql.type.FamilyOperandTypeChecker.checkOperandTypes(FamilyOperandTypeChecker.java:142)
        at org.apache.calcite.sql.SqlOperator.checkOperandTypes(SqlOperator.java:753)
        at org.apache.calcite.sql.SqlOperator.validateOperands(SqlOperator.java:499)
        at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:335)
        at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:231)
        at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:6451)
        at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:6438)
        at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:161)
        at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1892)
        at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1877)
        at org.apache.calcite.sql.SqlNode.validateExpr(SqlNode.java:276)
        at org.apache.calcite.sql.SqlOperator.validateCall(SqlOperator.java:474)
        at org.apache.calcite.sql.validate.SqlValidatorImpl.validateCall(SqlValidatorImpl.java:6129)
        at org.apache.calcite.sql.SqlCall.validate(SqlCall.java:138)
        at org.apache.calcite.sql.SqlNode.validateExpr(SqlNode.java:275)
        at org.apache.calcite.sql.SqlOperator.validateCall(SqlOperator.java:474)
        at org.apache.calcite.sql.validate.SqlValidatorImpl.validateCall(SqlValidatorImpl.java:6129)
        at org.apache.calcite.sql.SqlCall.validate(SqlCall.java:138)
        at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:1076)
        at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:782)
        at org.apache.calcite.sql.test.AbstractSqlTester.parseAndValidate(AbstractSqlTester.java:160)
        at org.apache.calcite.sql.test.AbstractSqlTester.validateAndThen(AbstractSqlTester.java:248)
        at org.apache.calcite.test.SqlOperatorFixtureImpl.lambda$forEachQueryValidateAndThen$1(SqlOperatorFixtureImpl.java:154)
        at org.apache.calcite.sql.test.AbstractSqlTester.forEachQuery(AbstractSqlTester.java:441)
        at org.apache.calcite.test.SqlOperatorFixtureImpl.forEachQueryValidateAndThen(SqlOperatorFixtureImpl.java:153)
        at org.apache.calcite.test.SqlOperatorFixtureImpl.checkType(SqlOperatorFixtureImpl.java:130)
        at org.apache.calcite.sql.test.SqlOperatorFixture.checkScalar(SqlOperatorFixture.java:237)
        at org.apache.calcite.test.SqlOperatorTest.lambda$testTimestampDiff$96(SqlOperatorTest.java:8208)

an example of query

SELECT TIMESTAMPDIFF(MONTH, TIME '00:00:00', TIMESTAMP '2021-02-04 12:00:00');
SELECT TIMESTAMPDIFF(MONTH, TIME '00:00:00', DATE '2021-02-04');

also in case of turned off type coercion

SELECT TIMESTAMPDIFF(MONTH, DATE '2021-02-04', DATE '2021-02-04');

Issue Links

• relates to: CALCITE-5423 Implement TIMESTAMP_DIFF function (compatible with BigQuery)
• links to: GitHub Pull Request #3041