[
https://issues.apache.org/jira/browse/CALCITE-7652?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Alexis Cubilla updated CALCITE-7652:
------------------------------------
Description:
PROBLEM
When RelToSqlConverter generates SQL for the SQL Server dialect
(MssqlSqlDialect),
a CAST whose target type is TIMESTAMP is unparsed literally as TIMESTAMP(n).
In SQL Server, TIMESTAMP is a deprecated synonym for ROWVERSION — a binary,
auto-generated type — not a date/time type. As a result, the generated SQL is
rejected by the server.
This is commonly triggered by an implicit cast. For example, comparing a
DATETIME
column (Calcite type TIMESTAMP(3)) against CURRENT_TIMESTAMP (Calcite type
TIMESTAMP(0)) makes Calcite insert a cast to align precision.
STEPS TO REPRODUCE
Generate SQL for the MSSQL dialect from a query such as:
SELECT "fec" FROM "t" WHERE "fec" < CURRENT_TIMESTAMP ("fec" is
TIMESTAMP(3))
RelToSqlConverter with MssqlSqlDialect.DEFAULT produces:
SELECT [fec] FROM [t] WHERE [fec] < CAST(CURRENT_TIMESTAMP AS TIMESTAMP(3))
Equivalently, MssqlSqlDialect.DEFAULT.getCastSpec(<TIMESTAMP(3)>) returns
"TIMESTAMP(3)".
ACTUAL RESULT
SQL Server rejects the statement:
CAST or CONVERT: invalid attributes specified for type 'timestamp'
EXPECTED RESULT
The dialect should emit a valid SQL Server date/time type. TIMESTAMP(n) should
map
to DATETIME2(n) (SQL Server 2008+):
SELECT [fec] FROM [t] WHERE [fec] < CAST(CURRENT_TIMESTAMP AS DATETIME2(3))
ROOT CAUSE
MssqlSqlDialect does not override getCastSpec(RelDataType) for
SqlTypeName.TIMESTAMP,
so it falls back to the generic behaviour that emits the ANSI type name
TIMESTAMP —
which is not a temporal type in SQL Server.
PROPOSED FIX
Override MssqlSqlDialect.getCastSpec to map TIMESTAMP to DATETIME2 and
TIMESTAMP WITH LOCAL TIME ZONE to DATETIMEOFFSET, both preserving
fractional-seconds precision (0–7; without an argument SQL Server defaults to
7). Add RelToSqlConverterTest cases asserting the generated SQL for both.
was:
PROBLEM
When RelToSqlConverter generates SQL for the SQL Server dialect
(MssqlSqlDialect),
a CAST whose target type is TIMESTAMP is unparsed literally as TIMESTAMP(n).
In SQL Server, TIMESTAMP is a deprecated synonym for ROWVERSION — a binary,
auto-generated type — not a date/time type. As a result, the generated SQL is
rejected by the server.
This is commonly triggered by an implicit cast. For example, comparing a
DATETIME
column (Calcite type TIMESTAMP(3)) against CURRENT_TIMESTAMP (Calcite type
TIMESTAMP(0)) makes Calcite insert a cast to align precision.
STEPS TO REPRODUCE
Generate SQL for the MSSQL dialect from a query such as:
SELECT "fec" FROM "t" WHERE "fec" < CURRENT_TIMESTAMP ("fec" is
TIMESTAMP(3))
RelToSqlConverter with MssqlSqlDialect.DEFAULT produces:
SELECT [fec] FROM [t] WHERE [fec] < CAST(CURRENT_TIMESTAMP AS TIMESTAMP(3))
Equivalently, MssqlSqlDialect.DEFAULT.getCastSpec(<TIMESTAMP(3)>) returns
"TIMESTAMP(3)".
ACTUAL RESULT
SQL Server rejects the statement:
CAST or CONVERT: invalid attributes specified for type 'timestamp'
EXPECTED RESULT
The dialect should emit a valid SQL Server date/time type. TIMESTAMP(n) should
map
to DATETIME2(n) (SQL Server 2008+):
SELECT [fec] FROM [t] WHERE [fec] < CAST(CURRENT_TIMESTAMP AS DATETIME2(3))
ROOT CAUSE
MssqlSqlDialect does not override getCastSpec(RelDataType) for
SqlTypeName.TIMESTAMP,
so it falls back to the generic behaviour that emits the ANSI type name
TIMESTAMP —
which is not a temporal type in SQL Server.
PROPOSED FIX
Override MssqlSqlDialect.getCastSpec to map TIMESTAMP to DATETIME2 (preserving
precision), plus a RelToSqlConverterTest case asserting the generated SQL. The
TIMESTAMP WITH LOCAL TIME ZONE variant (which maps to datetimeoffset) can be
handled in the same place.
> MssqlSqlDialect unparses CAST to TIMESTAMP as "TIMESTAMP", which is invalid
> in SQL Server (should be DATETIME2)
> ---------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-7652
> URL: https://issues.apache.org/jira/browse/CALCITE-7652
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.42.0
> Reporter: Alexis Cubilla
> Priority: Minor
> Labels: pull-request-available
>
>
> PROBLEM
> When RelToSqlConverter generates SQL for the SQL Server dialect
> (MssqlSqlDialect),
> a CAST whose target type is TIMESTAMP is unparsed literally as TIMESTAMP(n).
> In SQL Server, TIMESTAMP is a deprecated synonym for ROWVERSION — a binary,
> auto-generated type — not a date/time type. As a result, the generated SQL is
> rejected by the server.
> This is commonly triggered by an implicit cast. For example, comparing a
> DATETIME
> column (Calcite type TIMESTAMP(3)) against CURRENT_TIMESTAMP (Calcite type
> TIMESTAMP(0)) makes Calcite insert a cast to align precision.
> STEPS TO REPRODUCE
> Generate SQL for the MSSQL dialect from a query such as:
> SELECT "fec" FROM "t" WHERE "fec" < CURRENT_TIMESTAMP ("fec" is
> TIMESTAMP(3))
> RelToSqlConverter with MssqlSqlDialect.DEFAULT produces:
> SELECT [fec] FROM [t] WHERE [fec] < CAST(CURRENT_TIMESTAMP AS
> TIMESTAMP(3))
> Equivalently, MssqlSqlDialect.DEFAULT.getCastSpec(<TIMESTAMP(3)>) returns
> "TIMESTAMP(3)".
> ACTUAL RESULT
> SQL Server rejects the statement:
> CAST or CONVERT: invalid attributes specified for type 'timestamp'
> EXPECTED RESULT
> The dialect should emit a valid SQL Server date/time type. TIMESTAMP(n)
> should map
> to DATETIME2(n) (SQL Server 2008+):
> SELECT [fec] FROM [t] WHERE [fec] < CAST(CURRENT_TIMESTAMP AS
> DATETIME2(3))
> ROOT CAUSE
> MssqlSqlDialect does not override getCastSpec(RelDataType) for
> SqlTypeName.TIMESTAMP,
> so it falls back to the generic behaviour that emits the ANSI type name
> TIMESTAMP —
> which is not a temporal type in SQL Server.
> PROPOSED FIX
> Override MssqlSqlDialect.getCastSpec to map TIMESTAMP to DATETIME2 and
> TIMESTAMP WITH LOCAL TIME ZONE to DATETIMEOFFSET, both preserving
> fractional-seconds precision (0–7; without an argument SQL Server defaults to
> 7). Add RelToSqlConverterTest cases asserting the generated SQL for both.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
