[jira] [Commented] (CALCITE-5919) Compile-time implementation of EXTRACT ignores sub-millisecond values

[Mihai Budiu (Jira)]

    [ 
https://issues.apache.org/jira/browse/CALCITE-5919?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17753345#comment-17753345
 ] 

Mihai Budiu commented on CALCITE-5919:
--------------------------------------

Here is a test which reproduces this problem when added to RelOptRulesTest:
{code:Java}
  @Test void testExtractMicroseconds() {
    RelDataTypeSystemImpl typeSystem = new RelDataTypeSystemImpl() {
      @Override
      public int getMaxPrecision(SqlTypeName typeName) {
        if (typeName.equals(SqlTypeName.TIME))
          return 6;
        return super.getMaxPrecision(typeName);
      }
    };
    final String sql = "select extract(microsecond from time 
'00:00:00.123456')";
    sql(sql)
        .withFactory(f -> f.withTypeSystem(ignore -> typeSystem))
        .withRule(CoreRules.PROJECT_REDUCE_EXPRESSIONS)
        .check();
  }
{code}

The plans before is:
{code}
LogicalProject(EXPR$0=[EXTRACT(FLAG(MICROSECOND), 00:00:00.123456:TIME(6))])
  LogicalValues(tuples=[[{ 0 }]])
{code}

The plan after is:
{code}
LogicalProject(EXPR$0=[123000:BIGINT])
  LogicalValues(tuples=[[{ 0 }]])
{code}

> Compile-time implementation of EXTRACT ignores sub-millisecond values
> ---------------------------------------------------------------------
>
>                 Key: CALCITE-5919
>                 URL: https://issues.apache.org/jira/browse/CALCITE-5919
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 1.35.0
>            Reporter: Mihai Budiu
>            Priority: Minor
>
> 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:
> {code:java}
> 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())));
> {code}
> 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.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)