[jira] [Created] (CALCITE-7652) MssqlSqlDialect unparses CAST to TIMESTAMP as "TIMESTAMP", which is invalid in SQL Server (should be DATETIME2)

[Alexis Cubilla (Jira)]

Alexis Cubilla created CALCITE-7652:
---------------------------------------

             Summary: 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


 

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.

 



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