This is an automated email from the ASF dual-hosted git repository.
mihaibudiu pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new fe5b548532 [CALCITE-7652] MssqlSqlDialect unparses CAST to TIMESTAMP
as "TIMESTAMP", which is invalid in SQL Server (should be DATETIME2)
fe5b548532 is described below
commit fe5b548532468363187af69c8b0bfeba1b625dc3
Author: Alexis Cubilla <[email protected]>
AuthorDate: Mon Jul 13 13:28:19 2026 -0300
[CALCITE-7652] MssqlSqlDialect unparses CAST to TIMESTAMP as "TIMESTAMP",
which is invalid in SQL Server (should be DATETIME2)
MssqlSqlDialect.getCastSpec did not override TIMESTAMP, so a CAST to
TIMESTAMP was emitted with the ANSI type name TIMESTAMP. In SQL Server,
TIMESTAMP is a deprecated synonym for ROWVERSION (a binary type), so the
generated SQL was rejected ("CAST or CONVERT: invalid attributes specified
for type 'timestamp'").
Map TIMESTAMP to DATETIME2 and TIMESTAMP WITH LOCAL TIME ZONE to
DATETIMEOFFSET (SQL Server 2008+), preserving fractional-seconds precision.
---
.../calcite/sql/dialect/MssqlSqlDialect.java | 39 ++++++++++++++
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 61 ++++++++++++++++++++++
2 files changed, 100 insertions(+)
diff --git
a/core/src/main/java/org/apache/calcite/sql/dialect/MssqlSqlDialect.java
b/core/src/main/java/org/apache/calcite/sql/dialect/MssqlSqlDialect.java
index a88f78b48f..964fa03f8f 100644
--- a/core/src/main/java/org/apache/calcite/sql/dialect/MssqlSqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/dialect/MssqlSqlDialect.java
@@ -22,8 +22,10 @@
import org.apache.calcite.rel.type.RelDataTypeSystem;
import org.apache.calcite.rel.type.RelDataTypeSystemImpl;
import org.apache.calcite.sql.SqlAbstractDateTimeLiteral;
+import org.apache.calcite.sql.SqlAlienSystemTypeNameSpec;
import org.apache.calcite.sql.SqlBasicFunction;
import org.apache.calcite.sql.SqlCall;
+import org.apache.calcite.sql.SqlDataTypeSpec;
import org.apache.calcite.sql.SqlDialect;
import org.apache.calcite.sql.SqlFunction;
import org.apache.calcite.sql.SqlFunctionCategory;
@@ -80,6 +82,10 @@ public class MssqlSqlDialect extends SqlDialect {
SqlBasicFunction.create("SUBSTRING", ReturnTypes.ARG0_NULLABLE_VARYING,
OperandTypes.VARIADIC, SqlFunctionCategory.STRING);
+ /** Maximum fractional-seconds precision of SQL Server's {@code DATETIME2}
+ * and {@code DATETIMEOFFSET} types. */
+ private static final int MAX_DATETIME_PRECISION = 7;
+
/** Whether to generate "SELECT TOP(fetch)" rather than
* "SELECT ... FETCH NEXT fetch ROWS ONLY". */
private final boolean top;
@@ -92,6 +98,39 @@ public MssqlSqlDialect(Context context) {
top = context.databaseMajorVersion() < 11;
}
+ @Override public @Nullable SqlNode getCastSpec(RelDataType type) {
+ switch (type.getSqlTypeName()) {
+ case TIMESTAMP:
+ // In SQL Server, TIMESTAMP is a deprecated synonym for ROWVERSION
+ // (a binary, auto-generated type), not a temporal type. The correct
+ // fixed-precision date/time type is DATETIME2 (SQL Server 2008+).
+ return createDatetimeCastSpec("DATETIME2", type);
+ case TIMESTAMP_WITH_LOCAL_TIME_ZONE:
+ // SQL Server's timezone-aware date/time type.
+ return createDatetimeCastSpec("DATETIMEOFFSET", type);
+ default:
+ return super.getCastSpec(type);
+ }
+ }
+
+ /** Builds a SQL Server date/time cast target such as {@code DATETIME2(3)}.
+ *
+ * <p>SQL Server supports a fractional-seconds precision in the range
+ * {@code [0, 7]}. An unspecified precision is omitted, letting SQL Server
+ * apply its own default (7); a higher precision is clamped to 7. A precision
+ * above 7 is only reachable through a custom type system, since Calcite's
+ * default caps TIMESTAMP precision at
+ * {@link org.apache.calcite.sql.type.SqlTypeName#MAX_DATETIME_PRECISION}. */
+ private static SqlNode createDatetimeCastSpec(String typeAlias, RelDataType
type) {
+ final int precision = type.getPrecision();
+ final String spec = precision < 0
+ ? typeAlias
+ : typeAlias + "(" + Math.min(precision, MAX_DATETIME_PRECISION) + ")";
+ return new SqlDataTypeSpec(
+ new SqlAlienSystemTypeNameSpec(spec, type.getSqlTypeName(),
SqlParserPos.ZERO),
+ SqlParserPos.ZERO);
+ }
+
/** {@inheritDoc}
*
* <p>MSSQL does not support NULLS FIRST, so we emulate using CASE
diff --git
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
index 3327d0f368..f056ee9d4f 100644
---
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
+++
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
@@ -1617,6 +1617,67 @@ private static String toSql(RelNode root, SqlDialect
dialect,
sql(query).ok(expected);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7652";>[CALCITE-7652]
+ * MssqlSqlDialect unparses CAST to TIMESTAMP as "TIMESTAMP", which is
invalid
+ * in SQL Server (should be DATETIME2)</a>. */
+ @Test void testCastToTimestampMssql() {
+ final String query = "select cast(\"hire_date\" as timestamp(3))\n"
+ + "from \"employee\"";
+ final String expectedMssql = "SELECT CAST([hire_date] AS DATETIME2(3))\n"
+ + "FROM [foodmart].[employee]";
+ sql(query).withMssql().ok(expectedMssql);
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7652";>[CALCITE-7652]
+ * MssqlSqlDialect unparses CAST to TIMESTAMP as "TIMESTAMP", which is
invalid
+ * in SQL Server (should be DATETIME2)</a>. TIMESTAMP WITH LOCAL TIME ZONE
maps
+ * to DATETIMEOFFSET. */
+ @Test void testCastToTimestampWithLocalTimeZoneMssql() {
+ final String query = "select cast(\"hire_date\" as timestamp(3) with local
time zone)\n"
+ + "from \"employee\"";
+ final String expectedMssql = "SELECT CAST([hire_date] AS
DATETIMEOFFSET(3))\n"
+ + "FROM [foodmart].[employee]";
+ sql(query).withMssql().ok(expectedMssql);
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7652";>[CALCITE-7652]
+ * MssqlSqlDialect unparses CAST to TIMESTAMP as "TIMESTAMP", which is
invalid
+ * in SQL Server (should be DATETIME2)</a>. SQL Server's DATETIME2 and
+ * DATETIMEOFFSET support a fractional-seconds precision of at most 7, so a
+ * higher precision (only reachable through a custom type system, since
+ * Calcite's default caps TIMESTAMP precision at 3) is clamped to 7. */
+ @Test void testCastToTimestampMssqlClampsPrecision() {
+ final RelDataTypeSystem typeSystem = new RelDataTypeSystemImpl() {
+ @Override public int getMaxPrecision(SqlTypeName typeName) {
+ switch (typeName) {
+ case TIMESTAMP:
+ case TIMESTAMP_WITH_LOCAL_TIME_ZONE:
+ return 9;
+ default:
+ return super.getMaxPrecision(typeName);
+ }
+ }
+ };
+ final SqlTypeFactoryImpl typeFactory = new SqlTypeFactoryImpl(typeSystem);
+ final RelDataType timestamp9 =
+ typeFactory.createSqlType(SqlTypeName.TIMESTAMP, 9);
+ final SqlNode timestampCast =
MssqlSqlDialect.DEFAULT.getCastSpec(timestamp9);
+ assertThat(timestampCast, notNullValue());
+ assertThat(timestampCast.toSqlString(MssqlSqlDialect.DEFAULT).getSql(),
+ is("DATETIME2(7)"));
+
+ final RelDataType timestampTz9 =
+ typeFactory.createSqlType(SqlTypeName.TIMESTAMP_WITH_LOCAL_TIME_ZONE,
9);
+ final SqlNode timestampTzCast =
+ MssqlSqlDialect.DEFAULT.getCastSpec(timestampTz9);
+ assertThat(timestampTzCast, notNullValue());
+ assertThat(timestampTzCast.toSqlString(MssqlSqlDialect.DEFAULT).getSql(),
+ is("DATETIMEOFFSET(7)"));
+ }
+
/**
* Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-4706";>[CALCITE-4706]
