[CALCITE-5207] SubstitutionVisitor should not canonicalize DatetimePlus

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Assignee: Unassigned
• Reporter: Steven Talbot
• Created: 11/Jul/22 12:34
• Updated: 25/Oct/24 21:57

Description

From CALCITE-3914, "canonicalizeNode" attempts to canonicalize any SqlKind.PLUS rexCall. Unfortunately, this includes a `SqlDatetimePlusOperator`. When the canonicalize causes the operand order to reverse, this will result in an exception in the line

rexBuilder.makeCall(call.getOperator(), right, left) 

in the operators return type inference (since now operand 1 is a timestamp and operand 0 is an interval)

final IntervalSqlType unitType =
    (IntervalSqlType) opBinding.getOperandType(1) 

A call like the following should repro when used in tests like those in https://github.com/apache/calcite/commit/ee1a9d2cacb67da4c5d7e8f0441c94a40dc69d66: 

final RexNode ts_plus_interval =
        rexBuilder.makeCall(
                SqlStdOperatorTable.DATETIME_PLUS,
                rexBuilder.makeTimestampLiteral(new TimestampString("2018-01-01 00:00:00"), 0),
                        rexBuilder.makeIntervalLiteral(
                                new BigDecimal(12),
                                new SqlIntervalQualifier(TimeUnit.MONTH, null, SqlParserPos.ZERO)
                        )); 

Issue Links

• is caused by: CALCITE-3914 Improve SubstitutionVisitor to consider RexCall of type PLUS and TIMES for canonicalization