[CALCITE-5919] Compile-time implementation of EXTRACT ignores sub-millisecond values

• Type: Bug
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: 1.35.0
• Component/s: core
• Assignee: Unassigned
• Reporter: Mihai Budiu
• Created: 11/Aug/23 18:12
• Updated: 07/Sep/23 01:18

Description

When enabling the optimization rule PROJECT_REDUCE_EXPRESSIONS the compile-time evaluation of an expression like EXTRACT(MICROSECONDS FROM TIME '13:30:25.575401') will produce a result up to milliseconds only, irrespective of the type system provided. (This query will evaluate to 25575000 instead of 25575401).

The bug is in RexImpTable.ExtractImplementor, here:

case MILLISECOND:
      case MICROSECOND:
      case NANOSECOND:
        if (sqlTypeName == SqlTypeName.DATE) {
          return Expressions.constant(0L);
        }
        operand = mod(operand, TimeUnit.MINUTE.multiplier.longValue(), !isIntervalType); // << BUG
        return Expressions.multiply(
            operand, Expressions.constant((long) (1 / unit.multiplier.doubleValue())));

The mod operation uses a multiplier for MINUTE that is expressed in milliseconds, so it always truncates away values below milliseconds. The multiplier seems to assume that the type system precision for TIME is set to 3, which is the default value, but may be overridden.

Issue Links

• is related to: CALCITE-3529 TIMESTAMPDIFF function may overflow when handle unit of MICROSECOND/NANOSECOND
• is related to: CALCITE-3530 TIMESTAMPADD/TIMESTAMPDIFF with microsecond/nanosecond unit lost precision