Using TIMESTAMPDIFF() with RelBuilder

[Thomas D'Silva <td...@twilio.com.INVALID>]

Hi,

I get an error while using TIMESTAMPDIFF with RelBuilder. For this example
code

  @Test void testRun2() throws Exception {
    // Equivalent SQL:
    //   SELECT TIMESTAMP(SECOND, HIREDATE, TIMESTAMP'1970-01-01') FROM EMP
    final RelBuilder builder = RelBuilder.create(config().build());
    RelNode root =
        builder.scan("EMP")
            .project(
                builder.getRexBuilder().makeCall(
                    SqlStdOperatorTable.TIMESTAMP_DIFF,
                    builder.getRexBuilder().makeFlag(TimeUnit.SECOND),
                    builder.field("HIREDATE"),

builder.getRexBuilder().makeTimestampLiteral(TimestampString.fromMillisSinceEpoch(0),
0)
                )
            )
            .build();

    try (PreparedStatement preparedStatement = RelRunners.run(root)) {
      String s = CalciteAssert.toString(preparedStatement.executeQuery());
      final String result = "";
      assertThat(s, is(result));
    }
  }

I get an exception

Suppressed: java.lang.RuntimeException: cannot translate call
TIMESTAMPDIFF($t8, $t4, $t9)
at
org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitCall(RexToLixTranslator.java:1157)
at
org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitCall(RexToLixTranslator.java:98)

When a sql string is executed it appears that the TIMESTAMPDIFF function is
converted to a difference of two timestamps (
https://github.com/apache/calcite/blob/a03586c26e1888daebabb271b603fd2871d6a359/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java#L1853
).

Is there a way to use the TIMESTAMPDIFF() with RelBuilder or would I have
to do a similar translation as is done in TimestampDiffConvertlet?

Thank you,
Thomas

Re: Using TIMESTAMPDIFF() with RelBuilder

[Julian Hyde <jh...@gmail.com>]

We don’t really want TIMESTAMPDIFF to work because it’s not standard (although it is exists in various dialects).

Datetime minus is preferred. It’s more powerful because it accepts an interval value. I haven’t tried that either, but I imagine you would create an interval literal and then pass the result to the ‘-‘ operator.

Combining convertlets with RelBuilder is an interesting idea. If you can get that working without adding too much cruft to RelBuilder we should consider it. (I have a very low bar for cruft in RelBuilder because it’s massive - 5,000 lines long - and, as a builder for the core Rel and Rex languages, there should only be one way to do things.)

Julian



> On Feb 2, 2022, at 3:07 PM, Thomas D'Silva <td...@twilio.com.INVALID> wrote:
> 
> Hi,
> 
> I get an error while using TIMESTAMPDIFF with RelBuilder. For this example
> code
> 
>  @Test void testRun2() throws Exception {
>    // Equivalent SQL:
>    //   SELECT TIMESTAMP(SECOND, HIREDATE, TIMESTAMP'1970-01-01') FROM EMP
>    final RelBuilder builder = RelBuilder.create(config().build());
>    RelNode root =
>        builder.scan("EMP")
>            .project(
>                builder.getRexBuilder().makeCall(
>                    SqlStdOperatorTable.TIMESTAMP_DIFF,
>                    builder.getRexBuilder().makeFlag(TimeUnit.SECOND),
>                    builder.field("HIREDATE"),
> 
> builder.getRexBuilder().makeTimestampLiteral(TimestampString.fromMillisSinceEpoch(0),
> 0)
>                )
>            )
>            .build();
> 
>    try (PreparedStatement preparedStatement = RelRunners.run(root)) {
>      String s = CalciteAssert.toString(preparedStatement.executeQuery());
>      final String result = "";
>      assertThat(s, is(result));
>    }
>  }
> 
> I get an exception
> 
> Suppressed: java.lang.RuntimeException: cannot translate call
> TIMESTAMPDIFF($t8, $t4, $t9)
> at
> org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitCall(RexToLixTranslator.java:1157)
> at
> org.apache.calcite.adapter.enumerable.RexToLixTranslator.visitCall(RexToLixTranslator.java:98)
> 
> When a sql string is executed it appears that the TIMESTAMPDIFF function is
> converted to a difference of two timestamps (
> https://github.com/apache/calcite/blob/a03586c26e1888daebabb271b603fd2871d6a359/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java#L1853
> ).
> 
> Is there a way to use the TIMESTAMPDIFF() with RelBuilder or would I have
> to do a similar translation as is done in TimestampDiffConvertlet?
> 
> Thank you,
> Thomas