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.
SubstraitToSqlrenders interval andTIMESTAMP WITH LOCAL TIME ZONEcasts with Calcite's built-inPostgresqlSqlDialectandSparkSqlDialectin a form the target engine rejects.SubstraitSqlDialectoverridesgetCastSpecfor intervals since #1346, but the Calcite dialects still use their default cast spec.Measured on
mainat bc050d3, withCREATE TABLE t (ts9 TIMESTAMP(9), ltz3 TIMESTAMP(3) WITH LOCAL TIME ZONE, ltz6 TIMESTAMP(6) WITH LOCAL TIME ZONE, i INT):PostgreSQL 14 rejects the first with
type "interval_day_second" does not existand the second withsyntax error at or near "WITH";INTERVAL DAY TO SECOND(6)andTIMESTAMP(3) WITH TIME ZONErun. 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 ± intervalnow widens the narrower operand with a cast (ltz3 + INTERVAL '5' DAYrendersCAST(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.