Skip to content

isthmus: SubstraitToSql with Calcite's built-in dialects renders interval and LTZ casts their engines reject #1350

Description

@nielspardon

SubstraitToSql renders interval and TIMESTAMP WITH LOCAL TIME ZONE casts with Calcite's built-in PostgresqlSqlDialect and SparkSqlDialect in a form the target engine rejects. SubstraitSqlDialect overrides getCastSpec for intervals since #1346, but the Calcite dialects still use their default cast spec.

Measured on main at bc050d3, with CREATE TABLE t (ts9 TIMESTAMP(9), ltz3 TIMESTAMP(3) WITH LOCAL TIME ZONE, ltz6 TIMESTAMP(6) WITH LOCAL TIME ZONE, i INT):

SELECT ts9 + i * INTERVAL '1' DAY FROM t
  Substrait:  ... CAST(I * INTERVAL '1 00:00:00' DAY TO SECOND(6) AS INTERVAL DAY TO SECOND(9)) ...
  PostgreSQL: ... CAST("I" * INTERVAL '1 00:00:00' DAY TO SECOND(6) AS INTERVAL_DAY_SECOND(10, 9)) ...
  Spark:      ... CAST(`I` * INTERVAL '1 00:00:00' DAY TO SECOND(6) AS INTERVAL_DAY_SECOND(10, 9)) ...

SELECT CAST(ltz6 AS TIMESTAMP(3) WITH LOCAL TIME ZONE) FROM t
  PostgreSQL: SELECT CAST("LTZ6" AS TIMESTAMP(3) WITH LOCAL TIME ZONE) FROM "T"
  Spark:      SELECT CAST(`LTZ6` AS TIMESTAMP(3) WITH LOCAL TIME ZONE) FROM `T`

PostgreSQL 14 rejects the first with type "interval_day_second" does not exist and the second with syntax error at or near "WITH"; INTERVAL DAY TO SECOND(6) and TIMESTAMP(3) WITH TIME ZONE run. The Spark output was not run against Spark.

The LTZ form is reachable from any explicit cast. #1346 makes both more common for datetime arithmetic, since a mixed-precision ts ± interval now widens the narrower operand with a cast (ltz3 + INTERVAL '5' DAY renders CAST(LTZ3 AS TIMESTAMP(6) WITH LOCAL TIME ZONE) in every dialect).

The Calcite dialects are not isthmus's to override, so this likely belongs with the dialect-boundary rendering in #1013.

Activity

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions