[ https://issues.apache.org/jira/browse/CALCITE-5491?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17680421#comment-17680421 ]
Julian Hyde commented on CALCITE-5491: -------------------------------------- [~tanclary] Note that Calcite's {{DATETIME}} family includes the {{{}TIME{}}}, {{{}DATE{}}}, {{TIMESTAMP}} types. Not to be confused with the {{DATETIME}} type in databases such as MySQL, MSSQL, BigQuery. (Calcite does not have a {{DATETIME}} type, although we recently added various {{x_DATETIME}} and {{DATETIME_x}} functions (CALCITE-4297, CALCITE-4269, etc.) in the BigQuery library.) > Cannot apply 'TIMESTAMPDIFF' to arguments of type 'TIMESTAMPDIFF > ---------------------------------------------------------------- > > Key: CALCITE-5491 > URL: https://issues.apache.org/jira/browse/CALCITE-5491 > Project: Calcite > Issue Type: Bug > Components: core > Affects Versions: 1.33.0 > Reporter: Sergey Nuyanzin > Priority: Blocker > Labels: pull-request-available > Fix For: 1.33.0 > > Time Spent: 0.5h > Remaining Estimate: 0h > > Set to blocker since it is a regression (same queries work on 1.32.0) > Several queries with {{TIMESTAMPDIFF}} started to fail like > {noformat} > 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) > {noformat} > an example of query > {code:sql} > 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'); > {code} > also in case of turned off type coercion > {code:sql} > SELECT TIMESTAMPDIFF(MONTH, DATE '2021-02-04', DATE '2021-02-04'); > {code} -- This message was sent by Atlassian Jira (v8.20.10#820010)