This is an automated email from the ASF dual-hosted git repository.
morningman pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new ee17a0acab7 [fix](jdbc) Resolve SQL Server user-defined alias types by
JDBC type code (#67916)
ee17a0acab7 is described below
commit ee17a0acab76deb013f8982f593aad178f22cbf1
Author: Mingyu Chen (Rayner) <[email protected]>
AuthorDate: Mon Sep 14 18:06:59 2026 +0800
[fix](jdbc) Resolve SQL Server user-defined alias types by JDBC type code
(#67916)
### What problem does this PR solve?
Issue Number: close #67793
Related PR: none
Problem Summary:
Columns declared with a SQL Server user-defined alias type (`CREATE TYPE
dbo.customtexttype FROM varchar(50)`) are mapped to `UNSUPPORTED_TYPE`
by the JDBC catalog, so `SELECT *` on such a table fails with
```
type UNSUPPORTED is unsupported for Nereids
```
`JdbcSQLServerClient.jdbcTypeToDoris()` (and its counterpart
`JdbcSQLServerConnectorClient.jdbcTypeToConnectorType()` in
`fe-connector-jdbc`) dispatches only on `TYPE_NAME`. For an alias type,
`DatabaseMetaData.getColumns()` reports the alias name
(`customtexttype`) as `TYPE_NAME`, so the name never matches and the
`default` branch returns `UNSUPPORTED`. The same result set however
still carries the base type in `DATA_TYPE` (`java.sql.Types.VARCHAR`),
`COLUMN_SIZE` (50) and `DECIMAL_DIGITS`.
This PR keeps the name-based switch as the primary mapping (it carries
SQL Server specific choices such as `tinyint -> SMALLINT` and `money ->
DECIMAL(19,4)`) and, only when the name is not recognised, resolves the
column by its standard `java.sql.Types` code. The fallback mirrors the
name-based mapping and is applied in both implementations:
- `fe/fe-core/.../jdbc/client/JdbcSQLServerClient.java`
-
`fe/fe-connector/fe-connector-jdbc/.../JdbcSQLServerConnectorClient.java`
Scope of the fallback (verified against the mssql-jdbc `DataTypeFilter`
that post-processes `getColumns()`):
| base type of the alias | `DATA_TYPE` | Doris type |
|---|---|---|
| bit | BIT | BOOLEAN |
| tinyint / smallint | TINYINT / SMALLINT | SMALLINT |
| int / bigint | INTEGER / BIGINT | INT / BIGINT |
| real | REAL | FLOAT |
| float | DOUBLE (driver maps ODBC FLOAT to DOUBLE) | DOUBLE |
| decimal / numeric / money / smallmoney | DECIMAL / NUMERIC with the
base precision and scale | DECIMALV3(p, s), string when p > 38 |
| date | DATE | DATEV2 |
| datetime / datetime2 / smalldatetime | TIMESTAMP, scale capped at 6 |
DATETIMEV2(scale) |
| char / varchar / text / nchar / nvarchar / ntext / time /
uniqueidentifier / sysname | CHAR / VARCHAR / LONGVARCHAR / NCHAR /
NVARCHAR / LONGNVARCHAR / TIME | STRING |
The name-based path strips the IDENTITY decoration a column is reported
with (`int identity`, `decimal() identity`, `numeric(18, 0) identity`,
`decimal(18,0) IDENTITY(1,1)`) only when the name has that form for one
of the base types IDENTITY is allowed on **and** `DATA_TYPE` is that
base type's code. Any other name is matched as it is: SQL Server allows
an alias to be named with spaces or parentheses (`CREATE TYPE dbo.[int
alias] FROM varchar(50)`, reported as `TYPE_NAME = int alias`,
`DATA_TYPE = VARCHAR`), and cutting the name at the first space or
parenthesis, as both clients did, turned such an alias into the system
type its name starts with (an INT column for a varchar alias). An alias
cannot share a system type's name outright (`CREATE TYPE dbo.[int] ...`
is rejected by SQL Server), so an unrecognised name is an alias and goes
to the code fallback.
Deliberately **not** resolved by the fallback:
- Binary codes (`BINARY`, `VARBINARY`, `LONGVARBINARY`): mssql-jdbc
reports CLR user-defined types (`geometry`, `geography`, `hierarchyid`,
...) as `VARBINARY` too, so an alias over `varbinary` cannot be told
apart from an unsupported CLR type by the type code alone. They stay
`UNSUPPORTED`.
- Vendor specific codes (`sql_variant`, `datetimeoffset` aliases): stay
`UNSUPPORTED`.
- A consequence of the two rules above: an alias whose *name* starts
with a binary type name and whose base type is binary too (`CREATE TYPE
dbo.[varbinary alias] FROM varbinary(20)`) is `UNSUPPORTED` as well.
Before this PR its name was cut to `varbinary` and the column happened
to be readable; now the name is not a system type name and the binary
code is not resolved.
- `xml`, `sql_variant`, `geometry`, `geography`, `hierarchyid`, `json`,
`vector` are now listed explicitly as unsupported system types so that
the fallback never changes their existing behavior (`xml` would
otherwise be reported as `LONGNVARCHAR`).
The BE side needs no change: the scanner reads values by the Doris
column type (`getObject()` / `getBigDecimal()`), and the driver returns
the Java object of the base type for alias columns.
---
.../sqlserver/init/03-create-table.sql | 105 +++++++++++++
.../docker-compose/sqlserver/init/04-insert.sql | 20 +++
.../jdbc/client/JdbcSQLServerConnectorClient.java | 121 ++++++++++++++-
.../client/JdbcSQLServerConnectorClientTest.java | 131 +++++++++++++++-
.../jdbc/client/JdbcSQLServerClient.java | 129 ++++++++++++++--
.../jdbc/client/JdbcSQLServerClientTest.java | 165 +++++++++++++++++++++
.../jdbc/test_sqlserver_jdbc_catalog.out | 80 ++++++++++
.../jdbc/test_sqlserver_jdbc_catalog.groovy | 25 ++++
8 files changed, 762 insertions(+), 14 deletions(-)
diff --git
a/docker/thirdparties/docker-compose/sqlserver/init/03-create-table.sql
b/docker/thirdparties/docker-compose/sqlserver/init/03-create-table.sql
index d0b1989ce30..c57c0c2f384 100644
--- a/docker/thirdparties/docker-compose/sqlserver/init/03-create-table.sql
+++ b/docker/thirdparties/docker-compose/sqlserver/init/03-create-table.sql
@@ -284,3 +284,108 @@ CREATE TABLE dbo.test_date_filter (
datetime_value datetime NULL,
datetime2_value datetime2 NULL
);
+
+-- User-defined alias types (CREATE TYPE ... FROM base_type).
DatabaseMetaData.getColumns()
+-- reports such columns with TYPE_NAME set to the alias name, see #67793.
+-- They must exist before the tables below are created, hence the batch
separator.
+CREATE TYPE dbo.doris_alias_varchar FROM varchar(50) NOT NULL;
+CREATE TYPE dbo.doris_alias_varcharmax FROM varchar(max) NULL;
+CREATE TYPE dbo.doris_alias_nvarchar FROM nvarchar(20) NULL;
+CREATE TYPE dbo.doris_alias_nvarcharmax FROM nvarchar(max) NULL;
+CREATE TYPE dbo.doris_alias_char FROM char(10) NULL;
+CREATE TYPE dbo.doris_alias_nchar FROM nchar(10) NULL;
+CREATE TYPE dbo.doris_alias_text FROM text NULL;
+CREATE TYPE dbo.doris_alias_ntext FROM ntext NULL;
+CREATE TYPE dbo.doris_alias_bit FROM bit NULL;
+CREATE TYPE dbo.doris_alias_tinyint FROM tinyint NULL;
+CREATE TYPE dbo.doris_alias_smallint FROM smallint NULL;
+CREATE TYPE dbo.doris_alias_int FROM int NULL;
+CREATE TYPE dbo.doris_alias_bigint FROM bigint NULL;
+CREATE TYPE dbo.doris_alias_real FROM real NULL;
+CREATE TYPE dbo.doris_alias_float FROM float NULL;
+CREATE TYPE dbo.doris_alias_decimal FROM decimal(10, 2) NULL;
+CREATE TYPE dbo.doris_alias_numeric FROM numeric(38, 10) NULL;
+CREATE TYPE dbo.doris_alias_money FROM money NULL;
+CREATE TYPE dbo.doris_alias_smallmoney FROM smallmoney NULL;
+CREATE TYPE dbo.doris_alias_date FROM date NULL;
+CREATE TYPE dbo.doris_alias_time FROM time NULL;
+CREATE TYPE dbo.doris_alias_datetime FROM datetime NULL;
+CREATE TYPE dbo.doris_alias_datetime2 FROM datetime2(3) NULL;
+CREATE TYPE dbo.doris_alias_datetime2_default FROM datetime2 NULL;
+CREATE TYPE dbo.doris_alias_smalldatetime FROM smalldatetime NULL;
+CREATE TYPE dbo.doris_alias_guid FROM uniqueidentifier NULL;
+CREATE TYPE dbo.doris_alias_identity FROM int NOT NULL;
+-- Aliases over types that the JDBC catalog can not resolve by type code. They
must stay UNSUPPORTED.
+CREATE TYPE dbo.doris_alias_binary FROM binary(20) NULL;
+CREATE TYPE dbo.doris_alias_varbinary FROM varbinary(20) NULL;
+CREATE TYPE dbo.doris_alias_image FROM image NULL;
+CREATE TYPE dbo.doris_alias_datetimeoffset FROM datetimeoffset NULL;
+CREATE TYPE dbo.doris_alias_variant FROM sql_variant NULL;
+-- Alias names that start with a system type name: they are reported as they
are and must not be
+-- mistaken for that system type.
+CREATE TYPE dbo.[int alias] FROM varchar(50) NULL;
+CREATE TYPE dbo.[decimal(18,0) identity] FROM nvarchar(20) NULL;
+CREATE TYPE dbo.[int identity] FROM varchar(10) NULL;
+GO
+
+-- Every supported base type family behind an alias, plus sysname (a built-in
alias over nvarchar(128)).
+CREATE TABLE dbo.test_alias_type (
+ id int PRIMARY KEY NOT NULL,
+ plain_col varchar(50) NULL,
+ alias_varchar_col dbo.doris_alias_varchar NULL,
+ alias_varcharmax_col dbo.doris_alias_varcharmax NULL,
+ alias_nvarchar_col dbo.doris_alias_nvarchar NULL,
+ alias_nvarcharmax_col dbo.doris_alias_nvarcharmax NULL,
+ alias_char_col dbo.doris_alias_char NULL,
+ alias_nchar_col dbo.doris_alias_nchar NULL,
+ alias_text_col dbo.doris_alias_text NULL,
+ alias_ntext_col dbo.doris_alias_ntext NULL,
+ alias_bit_col dbo.doris_alias_bit NULL,
+ alias_tinyint_col dbo.doris_alias_tinyint NULL,
+ alias_smallint_col dbo.doris_alias_smallint NULL,
+ alias_int_col dbo.doris_alias_int NULL,
+ alias_bigint_col dbo.doris_alias_bigint NULL,
+ alias_real_col dbo.doris_alias_real NULL,
+ alias_float_col dbo.doris_alias_float NULL,
+ alias_decimal_col dbo.doris_alias_decimal NULL,
+ alias_numeric_col dbo.doris_alias_numeric NULL,
+ alias_money_col dbo.doris_alias_money NULL,
+ alias_smallmoney_col dbo.doris_alias_smallmoney NULL,
+ alias_date_col dbo.doris_alias_date NULL,
+ alias_time_col dbo.doris_alias_time NULL,
+ alias_datetime_col dbo.doris_alias_datetime NULL,
+ alias_datetime2_col dbo.doris_alias_datetime2 NULL,
+ alias_datetime2_default_col dbo.doris_alias_datetime2_default NULL,
+ alias_smalldatetime_col dbo.doris_alias_smalldatetime NULL,
+ alias_guid_col dbo.doris_alias_guid NULL,
+ sysname_col sysname NULL
+);
+
+-- IDENTITY on an alias typed column: the driver reports the plain alias name
as TYPE_NAME.
+CREATE TABLE dbo.test_alias_identity (
+ id dbo.doris_alias_identity IDENTITY(1,1) PRIMARY KEY,
+ val dbo.doris_alias_varchar NULL
+);
+
+-- Alias names that start with a system type name, next to a real IDENTITY
column.
+CREATE TABLE dbo.test_alias_name (
+ id int IDENTITY(1,1) PRIMARY KEY,
+ alias_named_int_col dbo.[int alias] NULL,
+ alias_named_decimal_identity_col dbo.[decimal(18,0) identity] NULL,
+ alias_named_int_identity_col dbo.[int identity] NULL
+);
+
+-- Negative cases: aliases over binary types, datetimeoffset and sql_variant,
and the xml / CLR system
+-- types. All of them must be reported as UNSUPPORTED while the other columns
stay readable.
+CREATE TABLE dbo.test_alias_unsupported (
+ id int PRIMARY KEY NOT NULL,
+ plain_col varchar(50) NULL,
+ alias_binary_col dbo.doris_alias_binary NULL,
+ alias_varbinary_col dbo.doris_alias_varbinary NULL,
+ alias_image_col dbo.doris_alias_image NULL,
+ alias_datetimeoffset_col dbo.doris_alias_datetimeoffset NULL,
+ alias_variant_col dbo.doris_alias_variant NULL,
+ xml_col xml NULL,
+ geometry_col geometry NULL,
+ hierarchyid_col hierarchyid NULL
+);
diff --git a/docker/thirdparties/docker-compose/sqlserver/init/04-insert.sql
b/docker/thirdparties/docker-compose/sqlserver/init/04-insert.sql
index 894773761c6..64275f091e9 100644
--- a/docker/thirdparties/docker-compose/sqlserver/init/04-insert.sql
+++ b/docker/thirdparties/docker-compose/sqlserver/init/04-insert.sql
@@ -132,3 +132,23 @@ Insert into dbo.test_date_filter values
(3, '2024-12-31', '2024-12-31 23:59:59', '2024-12-31 23:59:59.999'),
(4, '2023-01-17', '2023-01-17 08:00:00', '2023-01-17 08:00:00'),
(5, '2025-03-15', '2025-03-15 12:00:00', '2025-03-15 12:00:00.500');
+
+-- Alias typed columns, see #67793
+Insert into dbo.test_alias_type values
+(1, 'plain', 'alias', 'alias varchar max', 'alias nvarchar', 'alias nvarchar
max', 'Doris', 'Doris', 'alias text', 'alias ntext',
+ 1, 255, 32767, 1, 9223372036854775807, 123.123, 1.5, 12345.67,
1234567890123456789012345678.0123456789, 123.4567, 214748.3647,
+ '2023-01-17', '16:49:05.1234567', '2023-01-17 16:49:05', '2023-01-17
10:30:45.123', '2023-01-17 16:49:05.1234567', '2023-01-17 16:49:05',
+ 'FFFFFFFF-FFFF-FFFF-FFFF-FFFFFFFFFFFF', 'sysname value'),
+(2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
+ NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
+ NULL, NULL, NULL, NULL, NULL, NULL,
+ NULL, NULL);
+
+Insert into dbo.test_alias_identity (val) values ('first'), ('second');
+
+Insert into dbo.test_alias_name (alias_named_int_col,
alias_named_decimal_identity_col, alias_named_int_identity_col)
+values ('not an int', 'not a decimal', 'not an id');
+
+Insert into dbo.test_alias_unsupported values
+(1, 'plain', 0x01, 0x0102, 0x03, '2023-01-17 16:49:05 +08:00', 1, '<a/>',
geometry::STGeomFromText('POINT (1 2)', 0), hierarchyid::GetRoot()),
+(2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
diff --git
a/fe/fe-connector/fe-connector-jdbc/src/main/java/org/apache/doris/connector/jdbc/client/JdbcSQLServerConnectorClient.java
b/fe/fe-connector/fe-connector-jdbc/src/main/java/org/apache/doris/connector/jdbc/client/JdbcSQLServerConnectorClient.java
index 2cdab21a0b6..81c656ab154 100644
---
a/fe/fe-connector/fe-connector-jdbc/src/main/java/org/apache/doris/connector/jdbc/client/JdbcSQLServerConnectorClient.java
+++
b/fe/fe-connector/fe-connector-jdbc/src/main/java/org/apache/doris/connector/jdbc/client/JdbcSQLServerConnectorClient.java
@@ -26,7 +26,11 @@ import org.apache.logging.log4j.Logger;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
+import java.sql.Types;
+import java.util.Locale;
import java.util.Map;
+import java.util.regex.Matcher;
+import java.util.regex.Pattern;
/**
* SQL Server-specific JDBC connector client.
@@ -36,6 +40,12 @@ public class JdbcSQLServerConnectorClient extends
JdbcConnectorClient {
private static final Logger LOG =
LogManager.getLogger(JdbcSQLServerConnectorClient.class);
+ // TYPE_NAME of an IDENTITY column decorates the base type: "int
identity", "decimal() identity",
+ // "numeric(18, 0) identity", "decimal(18,0) IDENTITY(1,1)". IDENTITY is
only allowed on these base types.
+ private static final Pattern IDENTITY_TYPE_NAME = Pattern.compile(
+
"^(tinyint|smallint|int|bigint|decimal|numeric)\\s*(\\([^)]*\\))?\\s+identity(\\s*\\([^)]*\\))?$",
+ Pattern.CASE_INSENSITIVE);
+
public JdbcSQLServerConnectorClient(
String catalogName, JdbcDbType dbType, String jdbcUrl,
boolean onlySpecifiedDatabase,
@@ -48,13 +58,48 @@ public class JdbcSQLServerConnectorClient extends
JdbcConnectorClient {
enableMappingVarbinary, enableMappingTimestampTz);
}
+ /**
+ * The base type of an IDENTITY column's TYPE_NAME, or the name unchanged.
+ * <p>
+ * The decoration is trusted only when {@code DATA_TYPE} is the code of
the named base type: an alias type
+ * may legally be named like that ({@code CREATE TYPE dbo.[int identity]
FROM varchar(10)}, or
+ * {@code dbo.[int alias]}), and it then has to be resolved by its code,
not by the words of its name.
+ */
+ static String identityBaseType(String typeName, int dataType) {
+ Matcher matcher = IDENTITY_TYPE_NAME.matcher(typeName);
+ if (!matcher.matches()) {
+ return typeName;
+ }
+ String baseType = matcher.group(1).toLowerCase(Locale.ROOT);
+ boolean codeMatches;
+ switch (baseType) {
+ case "tinyint":
+ codeMatches = dataType == Types.TINYINT;
+ break;
+ case "smallint":
+ codeMatches = dataType == Types.SMALLINT;
+ break;
+ case "int":
+ codeMatches = dataType == Types.INTEGER;
+ break;
+ case "bigint":
+ codeMatches = dataType == Types.BIGINT;
+ break;
+ default:
+ codeMatches = dataType == Types.DECIMAL || dataType ==
Types.NUMERIC;
+ break;
+ }
+ return codeMatches ? baseType : typeName;
+ }
+
@Override
public ConnectorType jdbcTypeToConnectorType(JdbcFieldInfo fieldInfo) {
String rawType =
fieldInfo.getDataTypeName().orElse("unknown").toLowerCase();
- // SQL Server JDBC driver decorates type names for IDENTITY columns,
- // e.g., "int identity", "decimal() identity". Strip parenthesized
parts
- // and suffixes to get the base type name.
- String ssType = rawType.replaceAll("[\\s(].*", "");
+ // An IDENTITY column is reported as "int identity" or "decimal(18,0)
identity": only the base type
+ // is matched below. Any other name is matched as it is: system type
names are single words, and a
+ // user-defined alias type may be named with spaces or parentheses
("int alias") and must not be
+ // mistaken for the system type its name starts with.
+ String ssType = identityBaseType(rawType, fieldInfo.getDataType());
switch (ssType) {
case "bit":
return ConnectorType.of("BOOLEAN");
@@ -105,6 +150,74 @@ public class JdbcSQLServerConnectorClient extends
JdbcConnectorClient {
return enableMappingVarbinary
? ConnectorType.of("VARBINARY",
fieldInfo.requiredColumnSize(), -1)
: ConnectorType.of("STRING");
+ case "xml":
+ case "sql_variant":
+ case "geometry":
+ case "geography":
+ case "hierarchyid":
+ case "json":
+ case "vector":
+ // SQL Server system types that Doris does not support. They
are listed explicitly
+ // so that they never reach the JDBC type code fallback below.
+ return ConnectorType.of("UNSUPPORTED");
+ default:
+ return jdbcTypeCodeToConnectorType(fieldInfo);
+ }
+ }
+
+ /**
+ * Fallback for type names that are not SQL Server system types.
+ * <p>
+ * User-defined alias types ({@code CREATE TYPE dbo.my_type FROM
varchar(50)}) are reported by
+ * {@code DatabaseMetaData.getColumns()} with {@code TYPE_NAME} set to the
alias name, so they can not be
+ * matched by name. {@code DATA_TYPE}, {@code COLUMN_SIZE} and {@code
DECIMAL_DIGITS} still describe the
+ * base type, so the standard {@link Types} code is used to resolve the
Doris type. The mapping mirrors
+ * the name based one above.
+ * <p>
+ * Binary codes are deliberately not mapped: mssql-jdbc also reports CLR
user-defined types
+ * (geometry, geography, hierarchyid, ...) as {@link Types#VARBINARY}, so
they can not be told apart from
+ * an alias over a binary type by the type code alone. Vendor specific
codes stay unsupported as well.
+ */
+ private ConnectorType jdbcTypeCodeToConnectorType(JdbcFieldInfo fieldInfo)
{
+ switch (fieldInfo.getDataType()) {
+ case Types.BIT:
+ case Types.BOOLEAN:
+ return ConnectorType.of("BOOLEAN");
+ // SQL Server tinyint is unsigned (0 to 255), so it needs SMALLINT
+ case Types.TINYINT:
+ case Types.SMALLINT:
+ return ConnectorType.of("SMALLINT");
+ case Types.INTEGER:
+ return ConnectorType.of("INT");
+ case Types.BIGINT:
+ return ConnectorType.of("BIGINT");
+ case Types.REAL:
+ return ConnectorType.of("FLOAT");
+ case Types.FLOAT:
+ case Types.DOUBLE:
+ return ConnectorType.of("DOUBLE");
+ case Types.DECIMAL:
+ case Types.NUMERIC: {
+ // money and smallmoney are reported as DECIMAL(19,4) and
DECIMAL(10,4)
+ int precision = fieldInfo.requiredColumnSize();
+ int scale = fieldInfo.requiredDecimalDigits();
+ return createDecimalOrString(precision, scale);
+ }
+ case Types.DATE:
+ return ConnectorType.of("DATEV2");
+ case Types.TIMESTAMP: {
+ int scale = fieldInfo.getDecimalDigits().orElse(0);
+ scale = Math.min(scale, JDBC_DATETIME_SCALE);
+ return ConnectorType.of("DATETIMEV2", scale, -1);
+ }
+ case Types.CHAR:
+ case Types.NCHAR:
+ case Types.VARCHAR:
+ case Types.NVARCHAR:
+ case Types.LONGVARCHAR:
+ case Types.LONGNVARCHAR:
+ case Types.TIME:
+ return ConnectorType.of("STRING");
default:
return ConnectorType.of("UNSUPPORTED");
}
diff --git
a/fe/fe-connector/fe-connector-jdbc/src/test/java/org/apache/doris/connector/jdbc/client/JdbcSQLServerConnectorClientTest.java
b/fe/fe-connector/fe-connector-jdbc/src/test/java/org/apache/doris/connector/jdbc/client/JdbcSQLServerConnectorClientTest.java
index 313f7680054..4e20424712c 100644
---
a/fe/fe-connector/fe-connector-jdbc/src/test/java/org/apache/doris/connector/jdbc/client/JdbcSQLServerConnectorClientTest.java
+++
b/fe/fe-connector/fe-connector-jdbc/src/test/java/org/apache/doris/connector/jdbc/client/JdbcSQLServerConnectorClientTest.java
@@ -23,15 +23,20 @@ import org.apache.doris.connector.spi.ConnectorType;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
+import java.sql.Types;
import java.util.Collections;
import java.util.Optional;
/**
* Tests for {@link JdbcSQLServerConnectorClient}, focusing on SQL Server
- * IDENTITY column type name handling.
+ * IDENTITY column type name handling and user-defined alias type resolution.
*/
public class JdbcSQLServerConnectorClientTest {
+ // ODBC type codes that mssql-jdbc passes through
DatabaseMetaData.getColumns() unchanged
+ private static final int SQL_VARIANT = -150;
+ private static final int SQL_SS_TIMESTAMPOFFSET = -155;
+
private JdbcSQLServerConnectorClient createClient() {
return new JdbcSQLServerConnectorClient(
"test_catalog",
@@ -134,4 +139,128 @@ public class JdbcSQLServerConnectorClientTest {
Assertions.assertEquals("%",
client.getSchemaPatternForDatabaseNameList());
}
+
+ /**
+ * One DatabaseMetaData.getColumns() row as reported by mssql-jdbc. For a
user-defined alias type,
+ * TYPE_NAME is the alias name while DATA_TYPE, COLUMN_SIZE and
DECIMAL_DIGITS describe the base type.
+ */
+ private static JdbcFieldInfo column(String typeName, int dataType, int
columnSize, int decimalDigits) {
+ return new JdbcFieldInfo("col", Optional.of(typeName), dataType,
+ Optional.of(columnSize), Optional.of(decimalDigits),
Optional.empty());
+ }
+
+ private static String typeOf(JdbcSQLServerConnectorClient client,
JdbcFieldInfo info) {
+ return client.jdbcTypeToConnectorType(info).getTypeName();
+ }
+
+ @Test
+ void testAliasTypeIsResolvedByJdbcTypeCode() {
+ JdbcSQLServerConnectorClient client = createClient();
+
+ // CREATE TYPE dbo.customtexttype FROM varchar(50), the case reported
in #67793
+ Assertions.assertEquals("STRING", typeOf(client,
column("customtexttype", Types.VARCHAR, 50, 0)));
+ // sysname is a built-in alias over nvarchar(128)
+ Assertions.assertEquals("STRING", typeOf(client, column("sysname",
Types.NVARCHAR, 128, 0)));
+ Assertions.assertEquals("STRING", typeOf(client, column("alias_nchar",
Types.NCHAR, 10, 0)));
+ Assertions.assertEquals("STRING",
+ typeOf(client, column("alias_text", Types.LONGVARCHAR,
Integer.MAX_VALUE, 0)));
+ Assertions.assertEquals("STRING",
+ typeOf(client, column("alias_ntext", Types.LONGNVARCHAR,
Integer.MAX_VALUE / 2, 0)));
+ Assertions.assertEquals("STRING", typeOf(client, column("alias_time",
Types.TIME, 16, 7)));
+ // uniqueidentifier is reported as CHAR(36)
+ Assertions.assertEquals("STRING", typeOf(client, column("alias_guid",
Types.CHAR, 36, 0)));
+
+ Assertions.assertEquals("BOOLEAN", typeOf(client, column("alias_bit",
Types.BIT, 1, 0)));
+ // SQL Server tinyint is unsigned, so it keeps the SMALLINT mapping of
the name based path
+ Assertions.assertEquals("SMALLINT", typeOf(client,
column("alias_tinyint", Types.TINYINT, 3, 0)));
+ Assertions.assertEquals("SMALLINT", typeOf(client,
column("alias_smallint", Types.SMALLINT, 5, 0)));
+ Assertions.assertEquals("INT", typeOf(client, column("alias_int",
Types.INTEGER, 10, 0)));
+ Assertions.assertEquals("BIGINT", typeOf(client,
column("alias_bigint", Types.BIGINT, 19, 0)));
+ Assertions.assertEquals("FLOAT", typeOf(client, column("alias_real",
Types.REAL, 24, 0)));
+ Assertions.assertEquals("DOUBLE", typeOf(client, column("alias_float",
Types.DOUBLE, 53, 0)));
+
+ ConnectorType decimal =
client.jdbcTypeToConnectorType(column("alias_decimal", Types.DECIMAL, 10, 2));
+ Assertions.assertEquals("DECIMALV3", decimal.getTypeName());
+ Assertions.assertEquals(10, decimal.getPrecision());
+ Assertions.assertEquals(2, decimal.getScale());
+ // money is reported as DECIMAL(19,4), the same as the name based
mapping produces
+ ConnectorType money =
client.jdbcTypeToConnectorType(column("alias_money", Types.DECIMAL, 19, 4));
+ Assertions.assertEquals("DECIMALV3", money.getTypeName());
+ Assertions.assertEquals(19, money.getPrecision());
+ Assertions.assertEquals(4, money.getScale());
+ // precision beyond DECIMAL128 falls back to STRING like the name
based path
+ Assertions.assertEquals("STRING", typeOf(client,
column("alias_numeric", Types.NUMERIC, 39, 0)));
+
+ Assertions.assertEquals("DATEV2", typeOf(client, column("alias_date",
Types.DATE, 10, 0)));
+ ConnectorType datetime =
client.jdbcTypeToConnectorType(column("alias_datetime", Types.TIMESTAMP, 23,
3));
+ Assertions.assertEquals("DATETIMEV2", datetime.getTypeName());
+ Assertions.assertEquals(3, datetime.getPrecision());
+ // datetime2 defaults to 7 fractional digits, Doris supports at most 6
+ ConnectorType datetime2 =
client.jdbcTypeToConnectorType(column("alias_datetime2", Types.TIMESTAMP, 27,
7));
+ Assertions.assertEquals("DATETIMEV2", datetime2.getTypeName());
+ Assertions.assertEquals(6, datetime2.getPrecision());
+ }
+
+ @Test
+ void testUnknownTypesStayUnsupported() {
+ JdbcSQLServerConnectorClient client = createClient();
+
+ // CLR user-defined types are reported as VARBINARY, exactly like an
alias over varbinary,
+ // so binary codes must not be resolved by the fallback
+ Assertions.assertEquals("UNSUPPORTED",
+ typeOf(client, column("geometry", Types.VARBINARY,
Integer.MAX_VALUE, 0)));
+ Assertions.assertEquals("UNSUPPORTED", typeOf(client,
column("my_clr_type", Types.VARBINARY, 8000, 0)));
+ Assertions.assertEquals("UNSUPPORTED", typeOf(client,
column("alias_binary", Types.BINARY, 20, 0)));
+ Assertions.assertEquals("UNSUPPORTED",
+ typeOf(client, column("alias_image", Types.LONGVARBINARY,
Integer.MAX_VALUE, 0)));
+ // vendor specific type codes
+ Assertions.assertEquals("UNSUPPORTED", typeOf(client,
column("sql_variant", SQL_VARIANT, 8000, 0)));
+ Assertions.assertEquals("UNSUPPORTED",
+ typeOf(client, column("alias_datetimeoffset",
SQL_SS_TIMESTAMPOFFSET, 34, 7)));
+ // explicitly unsupported system types keep that behavior whatever
type code the driver reports
+ Assertions.assertEquals("UNSUPPORTED",
+ typeOf(client, column("xml", Types.LONGNVARCHAR,
Integer.MAX_VALUE / 2, 0)));
+ Assertions.assertEquals("UNSUPPORTED",
+ typeOf(client, column("json", Types.LONGNVARCHAR,
Integer.MAX_VALUE / 2, 0)));
+ Assertions.assertEquals("UNSUPPORTED", typeOf(client,
column("hierarchyid", Types.VARBINARY, 892, 0)));
+ }
+
+ @Test
+ void testAliasNamedLikeASystemTypeIsResolvedByJdbcTypeCode() {
+ JdbcSQLServerConnectorClient client = createClient();
+
+ // A delimited alias name may contain spaces and parentheses ([int
alias], [decimal(18,0) identity]);
+ // it is reported as is, and the base type is still what DATA_TYPE says
+ Assertions.assertEquals("STRING", typeOf(client, column("int alias",
Types.VARCHAR, 50, 0)));
+ Assertions.assertEquals("STRING", typeOf(client, column("decimal(18,0)
identity", Types.NVARCHAR, 20, 0)));
+ Assertions.assertEquals("STRING", typeOf(client, column("int
identity", Types.VARCHAR, 10, 0)));
+ Assertions.assertEquals("STRING", typeOf(client, column("bigint
identity", Types.NVARCHAR, 20, 0)));
+ Assertions.assertEquals("DATETIMEV2", typeOf(client,
column("varchar(50) alias", Types.TIMESTAMP, 23, 3)));
+
+ // The IDENTITY decoration of a real system type, in the forms the
driver versions report it in
+ Assertions.assertEquals("INT", typeOf(client, column("int identity",
Types.INTEGER, 10, 0)));
+ Assertions.assertEquals("BIGINT", typeOf(client, column("bigint
identity", Types.BIGINT, 19, 0)));
+ Assertions.assertEquals("SMALLINT", typeOf(client, column("tinyint
identity", Types.TINYINT, 3, 0)));
+ for (String decorated : new String[] {"decimal identity", "decimal()
identity",
+ "decimal(18,0) IDENTITY(1,1)", "DECIMAL(18, 0) IDENTITY"}) {
+ ConnectorType ct =
client.jdbcTypeToConnectorType(column(decorated, Types.DECIMAL, 18, 0));
+ Assertions.assertEquals("DECIMALV3", ct.getTypeName(), decorated);
+ Assertions.assertEquals(18, ct.getPrecision(), decorated);
+ Assertions.assertEquals(0, ct.getScale(), decorated);
+ }
+ ConnectorType numeric =
client.jdbcTypeToConnectorType(column("numeric(18, 0) identity", Types.NUMERIC,
18, 0));
+ Assertions.assertEquals("DECIMALV3", numeric.getTypeName());
+ Assertions.assertEquals(18, numeric.getPrecision());
+ }
+
+ @Test
+ void testSystemTypeNamesTakePrecedence() {
+ JdbcSQLServerConnectorClient client = createClient();
+
+ // the name based mapping is unchanged, the type code is only
consulted for unknown names
+ Assertions.assertEquals("SMALLINT", typeOf(client, column("tinyint",
Types.TINYINT, 3, 0)));
+ Assertions.assertEquals("STRING", typeOf(client, column("varbinary",
Types.VARBINARY, 20, 0)));
+ Assertions.assertEquals("STRING",
+ typeOf(client, column("datetimeoffset",
SQL_SS_TIMESTAMPOFFSET, 34, 7)));
+ }
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/client/JdbcSQLServerClient.java
b/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/client/JdbcSQLServerClient.java
index 50cd5ed9244..d1aaa42ef3c 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/client/JdbcSQLServerClient.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/client/JdbcSQLServerClient.java
@@ -21,8 +21,19 @@ import org.apache.doris.catalog.ScalarType;
import org.apache.doris.catalog.Type;
import org.apache.doris.datasource.jdbc.util.JdbcFieldSchema;
+import java.sql.Types;
+import java.util.Locale;
+import java.util.regex.Matcher;
+import java.util.regex.Pattern;
+
public class JdbcSQLServerClient extends JdbcClient {
+ // TYPE_NAME of an IDENTITY column decorates the base type: "int
identity", "decimal() identity",
+ // "numeric(18, 0) identity", "decimal(18,0) IDENTITY(1,1)". IDENTITY is
only allowed on these base types.
+ private static final Pattern IDENTITY_TYPE_NAME = Pattern.compile(
+
"^(tinyint|smallint|int|bigint|decimal|numeric)\\s*(\\([^)]*\\))?\\s+identity(\\s*\\([^)]*\\))?$",
+ Pattern.CASE_INSENSITIVE);
+
protected JdbcSQLServerClient(JdbcClientConfig jdbcClientConfig) {
super(jdbcClientConfig);
}
@@ -34,18 +45,48 @@ public class JdbcSQLServerClient extends JdbcClient {
return "%";
}
+ /**
+ * The base type of an IDENTITY column's TYPE_NAME, or the name unchanged.
+ * <p>
+ * The decoration is trusted only when {@code DATA_TYPE} is the code of
the named base type: an alias type
+ * may legally be named like that ({@code CREATE TYPE dbo.[int identity]
FROM varchar(10)}, or
+ * {@code dbo.[int alias]}), and it then has to be resolved by its code,
not by the words of its name.
+ */
+ static String identityBaseType(String typeName, int dataType) {
+ Matcher matcher = IDENTITY_TYPE_NAME.matcher(typeName);
+ if (!matcher.matches()) {
+ return typeName;
+ }
+ String baseType = matcher.group(1).toLowerCase(Locale.ROOT);
+ boolean codeMatches;
+ switch (baseType) {
+ case "tinyint":
+ codeMatches = dataType == Types.TINYINT;
+ break;
+ case "smallint":
+ codeMatches = dataType == Types.SMALLINT;
+ break;
+ case "int":
+ codeMatches = dataType == Types.INTEGER;
+ break;
+ case "bigint":
+ codeMatches = dataType == Types.BIGINT;
+ break;
+ default:
+ codeMatches = dataType == Types.DECIMAL || dataType ==
Types.NUMERIC;
+ break;
+ }
+ return codeMatches ? baseType : typeName;
+ }
+
@Override
protected Type jdbcTypeToDoris(JdbcFieldSchema fieldSchema) {
String originSqlserverType =
fieldSchema.getDataTypeName().orElse("unknown");
- // For sqlserver IDENTITY type, such as 'INT IDENTITY'
- // originSqlserverType is "int identity", so we only get "int".
- // For types with parameters like 'decimal(18,0) IDENTITY(1,1)', we
need to extract the base type
- String sqlserverType = originSqlserverType.split(" ")[0];
-
- // Handle types with parentheses like decimal(18,0), varchar(50), etc.
- if (sqlserverType.contains("(")) {
- sqlserverType = sqlserverType.substring(0,
sqlserverType.indexOf("("));
- }
+ // An IDENTITY column is reported as "int identity" or "decimal(18,0)
identity": only the base type
+ // is matched below. Any other name is matched as it is: system type
names are single words, and a
+ // user-defined alias type may be named with spaces or parentheses
("int alias") and must not be
+ // mistaken for the system type its name starts with.
+ String sqlserverType = identityBaseType(originSqlserverType,
fieldSchema.getDataType());
switch (sqlserverType) {
case "bit":
@@ -99,6 +140,76 @@ public class JdbcSQLServerClient extends JdbcClient {
case "varbinary":
return enableMappingVarbinary ?
ScalarType.createVarbinaryType(fieldSchema.requiredColumnSize())
: ScalarType.createStringType();
+ case "xml":
+ case "sql_variant":
+ case "geometry":
+ case "geography":
+ case "hierarchyid":
+ case "json":
+ case "vector":
+ // SQL Server system types that Doris does not support. They
are listed explicitly
+ // so that they never reach the JDBC type code fallback below.
+ return Type.UNSUPPORTED;
+ default:
+ return jdbcTypeCodeToDoris(fieldSchema);
+ }
+ }
+
+ /**
+ * Fallback for type names that are not SQL Server system types.
+ * <p>
+ * User-defined alias types ({@code CREATE TYPE dbo.my_type FROM
varchar(50)}) are reported by
+ * {@code DatabaseMetaData.getColumns()} with {@code TYPE_NAME} set to the
alias name, so they can not be
+ * matched by name. {@code DATA_TYPE}, {@code COLUMN_SIZE} and {@code
DECIMAL_DIGITS} still describe the
+ * base type, so the standard {@link Types} code is used to resolve the
Doris type. The mapping mirrors
+ * the name based one above.
+ * <p>
+ * Binary codes are deliberately not mapped: mssql-jdbc also reports CLR
user-defined types
+ * (geometry, geography, hierarchyid, ...) as {@link Types#VARBINARY}, so
they can not be told apart from
+ * an alias over a binary type by the type code alone. Vendor specific
codes stay unsupported as well.
+ */
+ private Type jdbcTypeCodeToDoris(JdbcFieldSchema fieldSchema) {
+ switch (fieldSchema.getDataType()) {
+ case Types.BIT:
+ case Types.BOOLEAN:
+ return Type.BOOLEAN;
+ // SQL Server tinyint is unsigned (0 to 255), so it needs SMALLINT
+ case Types.TINYINT:
+ case Types.SMALLINT:
+ return Type.SMALLINT;
+ case Types.INTEGER:
+ return Type.INT;
+ case Types.BIGINT:
+ return Type.BIGINT;
+ case Types.REAL:
+ return Type.FLOAT;
+ case Types.FLOAT:
+ case Types.DOUBLE:
+ return Type.DOUBLE;
+ case Types.DECIMAL:
+ case Types.NUMERIC: {
+ // money and smallmoney are reported as DECIMAL(19,4) and
DECIMAL(10,4)
+ int precision = fieldSchema.getColumnSize().orElse(0);
+ int scale = fieldSchema.getDecimalDigits().orElse(0);
+ return createDecimalOrStringType(precision, scale);
+ }
+ case Types.DATE:
+ return ScalarType.createDateV2Type();
+ case Types.TIMESTAMP: {
+ int scale = fieldSchema.getDecimalDigits().orElse(0);
+ if (scale > 6) {
+ scale = 6;
+ }
+ return ScalarType.createDatetimeV2Type(scale);
+ }
+ case Types.CHAR:
+ case Types.VARCHAR:
+ case Types.LONGVARCHAR:
+ case Types.NCHAR:
+ case Types.NVARCHAR:
+ case Types.LONGNVARCHAR:
+ case Types.TIME:
+ return ScalarType.createStringType();
default:
return Type.UNSUPPORTED;
}
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/datasource/jdbc/client/JdbcSQLServerClientTest.java
b/fe/fe-core/src/test/java/org/apache/doris/datasource/jdbc/client/JdbcSQLServerClientTest.java
new file mode 100644
index 00000000000..d7b8ad645bf
--- /dev/null
+++
b/fe/fe-core/src/test/java/org/apache/doris/datasource/jdbc/client/JdbcSQLServerClientTest.java
@@ -0,0 +1,165 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package org.apache.doris.datasource.jdbc.client;
+
+import org.apache.doris.catalog.ScalarType;
+import org.apache.doris.catalog.Type;
+import org.apache.doris.datasource.jdbc.util.JdbcFieldSchema;
+
+import org.junit.jupiter.api.Assertions;
+import org.junit.jupiter.api.Test;
+import org.mockito.Answers;
+import org.mockito.Mockito;
+
+import java.sql.DatabaseMetaData;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Types;
+
+public class JdbcSQLServerClientTest {
+
+ // ODBC type codes that mssql-jdbc passes through
DatabaseMetaData.getColumns() unchanged
+ private static final int SQL_VARIANT = -150;
+ private static final int SQL_SS_TIMESTAMPOFFSET = -155;
+
+ private final JdbcSQLServerClient client =
Mockito.mock(JdbcSQLServerClient.class, Answers.CALLS_REAL_METHODS);
+
+ /**
+ * Builds the schema of one DatabaseMetaData.getColumns() row as reported
by mssql-jdbc.
+ * For a user-defined alias type, TYPE_NAME is the alias name while
DATA_TYPE, COLUMN_SIZE
+ * and DECIMAL_DIGITS describe the base type.
+ */
+ private static JdbcFieldSchema column(String typeName, int dataType, int
columnSize, int decimalDigits)
+ throws SQLException {
+ ResultSet rs = Mockito.mock(ResultSet.class);
+ Mockito.when(rs.getString("COLUMN_NAME")).thenReturn("col");
+ Mockito.when(rs.getInt("DATA_TYPE")).thenReturn(dataType);
+ Mockito.when(rs.getString("TYPE_NAME")).thenReturn(typeName);
+ Mockito.when(rs.getInt("COLUMN_SIZE")).thenReturn(columnSize);
+ Mockito.when(rs.getInt("DECIMAL_DIGITS")).thenReturn(decimalDigits);
+
Mockito.when(rs.getInt("NULLABLE")).thenReturn(DatabaseMetaData.columnNullable);
+ return new JdbcFieldSchema(rs);
+ }
+
+ @Test
+ public void testAliasTypeIsResolvedByJdbcTypeCode() throws SQLException {
+ // CREATE TYPE dbo.customtexttype FROM varchar(50), the case reported
in #67793
+ Assertions.assertEquals(Type.STRING,
client.jdbcTypeToDoris(column("customtexttype", Types.VARCHAR, 50, 0)));
+ // sysname is a built-in alias over nvarchar(128)
+ Assertions.assertEquals(Type.STRING,
client.jdbcTypeToDoris(column("sysname", Types.NVARCHAR, 128, 0)));
+ Assertions.assertEquals(Type.STRING,
client.jdbcTypeToDoris(column("alias_nchar", Types.NCHAR, 10, 0)));
+ Assertions.assertEquals(Type.STRING,
+ client.jdbcTypeToDoris(column("alias_text", Types.LONGVARCHAR,
Integer.MAX_VALUE, 0)));
+ Assertions.assertEquals(Type.STRING,
+ client.jdbcTypeToDoris(column("alias_ntext",
Types.LONGNVARCHAR, Integer.MAX_VALUE / 2, 0)));
+ Assertions.assertEquals(Type.STRING,
client.jdbcTypeToDoris(column("alias_time", Types.TIME, 16, 7)));
+ // uniqueidentifier is reported as CHAR(36)
+ Assertions.assertEquals(Type.STRING,
client.jdbcTypeToDoris(column("alias_guid", Types.CHAR, 36, 0)));
+
+ Assertions.assertEquals(Type.BOOLEAN,
client.jdbcTypeToDoris(column("alias_bit", Types.BIT, 1, 0)));
+ // SQL Server tinyint is unsigned, so it keeps the SMALLINT mapping of
the name based path
+ Assertions.assertEquals(Type.SMALLINT,
client.jdbcTypeToDoris(column("alias_tinyint", Types.TINYINT, 3, 0)));
+ Assertions.assertEquals(Type.SMALLINT,
client.jdbcTypeToDoris(column("alias_smallint", Types.SMALLINT, 5, 0)));
+ Assertions.assertEquals(Type.INT,
client.jdbcTypeToDoris(column("alias_int", Types.INTEGER, 10, 0)));
+ Assertions.assertEquals(Type.BIGINT,
client.jdbcTypeToDoris(column("alias_bigint", Types.BIGINT, 19, 0)));
+ Assertions.assertEquals(Type.FLOAT,
client.jdbcTypeToDoris(column("alias_real", Types.REAL, 24, 0)));
+ Assertions.assertEquals(Type.DOUBLE,
client.jdbcTypeToDoris(column("alias_float", Types.DOUBLE, 53, 0)));
+
+ Assertions.assertEquals(ScalarType.createDecimalV3Type(10, 2),
+ client.jdbcTypeToDoris(column("alias_decimal", Types.DECIMAL,
10, 2)));
+ Assertions.assertEquals(ScalarType.createDecimalV3Type(38, 10),
+ client.jdbcTypeToDoris(column("alias_numeric", Types.NUMERIC,
38, 10)));
+ // money is reported as DECIMAL(19,4), the same as the name based
mapping produces
+ Assertions.assertEquals(ScalarType.createDecimalV3Type(19, 4),
+ client.jdbcTypeToDoris(column("alias_money", Types.DECIMAL,
19, 4)));
+
+ Assertions.assertEquals(Type.DATEV2,
client.jdbcTypeToDoris(column("alias_date", Types.DATE, 10, 0)));
+ Assertions.assertEquals(ScalarType.createDatetimeV2Type(3),
+ client.jdbcTypeToDoris(column("alias_datetime",
Types.TIMESTAMP, 23, 3)));
+ // datetime2 defaults to 7 fractional digits, Doris supports at most 6
+ Assertions.assertEquals(ScalarType.createDatetimeV2Type(6),
+ client.jdbcTypeToDoris(column("alias_datetime2",
Types.TIMESTAMP, 27, 7)));
+ Assertions.assertEquals(ScalarType.createDatetimeV2Type(0),
+ client.jdbcTypeToDoris(column("alias_smalldatetime",
Types.TIMESTAMP, 16, 0)));
+ }
+
+ @Test
+ public void testUnknownTypesStayUnsupported() throws SQLException {
+ // CLR user-defined types are reported as VARBINARY, exactly like an
alias over varbinary,
+ // so binary codes must not be resolved by the fallback
+ Assertions.assertEquals(Type.UNSUPPORTED,
+ client.jdbcTypeToDoris(column("geometry", Types.VARBINARY,
Integer.MAX_VALUE, 0)));
+ Assertions.assertEquals(Type.UNSUPPORTED,
+ client.jdbcTypeToDoris(column("my_clr_type", Types.VARBINARY,
8000, 0)));
+ Assertions.assertEquals(Type.UNSUPPORTED,
client.jdbcTypeToDoris(column("alias_binary", Types.BINARY, 20, 0)));
+ Assertions.assertEquals(Type.UNSUPPORTED,
+ client.jdbcTypeToDoris(column("alias_image",
Types.LONGVARBINARY, Integer.MAX_VALUE, 0)));
+ // vendor specific type codes
+ Assertions.assertEquals(Type.UNSUPPORTED,
client.jdbcTypeToDoris(column("sql_variant", SQL_VARIANT, 8000, 0)));
+ Assertions.assertEquals(Type.UNSUPPORTED,
+ client.jdbcTypeToDoris(column("alias_datetimeoffset",
SQL_SS_TIMESTAMPOFFSET, 34, 7)));
+ // explicitly unsupported system types keep that behavior whatever
type code the driver reports
+ Assertions.assertEquals(Type.UNSUPPORTED,
+ client.jdbcTypeToDoris(column("xml", Types.LONGNVARCHAR,
Integer.MAX_VALUE / 2, 0)));
+ Assertions.assertEquals(Type.UNSUPPORTED,
+ client.jdbcTypeToDoris(column("json", Types.LONGNVARCHAR,
Integer.MAX_VALUE / 2, 0)));
+ Assertions.assertEquals(Type.UNSUPPORTED,
+ client.jdbcTypeToDoris(column("hierarchyid", Types.VARBINARY,
892, 0)));
+ }
+
+ @Test
+ public void testAliasNamedLikeASystemTypeIsResolvedByJdbcTypeCode() throws
SQLException {
+ // A delimited alias name may contain spaces and parentheses ([int
alias], [decimal(18,0) identity]);
+ // it is reported as is, and the base type is still what DATA_TYPE says
+ Assertions.assertEquals(Type.STRING,
client.jdbcTypeToDoris(column("int alias", Types.VARCHAR, 50, 0)));
+ Assertions.assertEquals(Type.STRING,
+ client.jdbcTypeToDoris(column("decimal(18,0) identity",
Types.NVARCHAR, 20, 0)));
+ Assertions.assertEquals(Type.STRING,
client.jdbcTypeToDoris(column("int identity", Types.VARCHAR, 10, 0)));
+ Assertions.assertEquals(Type.STRING,
+ client.jdbcTypeToDoris(column("bigint identity",
Types.NVARCHAR, 20, 0)));
+ Assertions.assertEquals(ScalarType.createDatetimeV2Type(3),
+ client.jdbcTypeToDoris(column("varchar(50) alias",
Types.TIMESTAMP, 23, 3)));
+
+ // The IDENTITY decoration of a real system type, in the forms the
driver versions report it in
+ Assertions.assertEquals(Type.INT, client.jdbcTypeToDoris(column("int
identity", Types.INTEGER, 10, 0)));
+ Assertions.assertEquals(Type.BIGINT,
client.jdbcTypeToDoris(column("bigint identity", Types.BIGINT, 19, 0)));
+ Assertions.assertEquals(Type.SMALLINT,
+ client.jdbcTypeToDoris(column("tinyint identity",
Types.TINYINT, 3, 0)));
+ Assertions.assertEquals(ScalarType.createDecimalV3Type(18, 0),
+ client.jdbcTypeToDoris(column("decimal identity",
Types.DECIMAL, 18, 0)));
+ Assertions.assertEquals(ScalarType.createDecimalV3Type(18, 0),
+ client.jdbcTypeToDoris(column("decimal() identity",
Types.DECIMAL, 18, 0)));
+ Assertions.assertEquals(ScalarType.createDecimalV3Type(18, 0),
+ client.jdbcTypeToDoris(column("numeric(18, 0) identity",
Types.NUMERIC, 18, 0)));
+ Assertions.assertEquals(ScalarType.createDecimalV3Type(18, 0),
+ client.jdbcTypeToDoris(column("decimal(18,0) IDENTITY(1,1)",
Types.DECIMAL, 18, 0)));
+ }
+
+ @Test
+ public void testSystemTypeNamesTakePrecedence() throws SQLException {
+ // the name based mapping is unchanged, the type code is only
consulted for unknown names
+ Assertions.assertEquals(Type.SMALLINT,
client.jdbcTypeToDoris(column("tinyint", Types.TINYINT, 3, 0)));
+ Assertions.assertEquals(Type.INT, client.jdbcTypeToDoris(column("int
identity", Types.INTEGER, 10, 0)));
+ Assertions.assertEquals(ScalarType.createDecimalV3Type(19, 4),
+ client.jdbcTypeToDoris(column("money", Types.DECIMAL, 19, 4)));
+ Assertions.assertEquals(Type.STRING,
client.jdbcTypeToDoris(column("varbinary", Types.VARBINARY, 20, 0)));
+ Assertions.assertEquals(Type.STRING,
client.jdbcTypeToDoris(column("timestamp", Types.BINARY, 8, 0)));
+ Assertions.assertEquals(Type.STRING,
+ client.jdbcTypeToDoris(column("datetimeoffset",
SQL_SS_TIMESTAMPOFFSET, 34, 7)));
+ }
+}
diff --git
a/regression-test/data/external_table_p2/jdbc/test_sqlserver_jdbc_catalog.out
b/regression-test/data/external_table_p2/jdbc/test_sqlserver_jdbc_catalog.out
index 3069fb1904f..aa34b8d79ac 100644
---
a/regression-test/data/external_table_p2/jdbc/test_sqlserver_jdbc_catalog.out
+++
b/regression-test/data/external_table_p2/jdbc/test_sqlserver_jdbc_catalog.out
@@ -133,6 +133,74 @@ timestamp_col text Yes true \N
-- !identity_decimal --
1 1
+-- !desc_alias_type --
+alias_bigint_col bigint Yes true \N
+alias_bit_col boolean Yes true \N
+alias_char_col text Yes true \N
+alias_date_col date Yes true \N
+alias_datetime2_col datetime(3) Yes true \N
+alias_datetime2_default_col datetime(6) Yes true \N
+alias_datetime_col datetime(3) Yes true \N
+alias_decimal_col decimal(10,2) Yes true \N
+alias_float_col double Yes true \N
+alias_guid_col text Yes true \N
+alias_int_col int Yes true \N
+alias_money_col decimal(19,4) Yes true \N
+alias_nchar_col text Yes true \N
+alias_ntext_col text Yes true \N
+alias_numeric_col decimal(38,10) Yes true \N
+alias_nvarchar_col text Yes true \N
+alias_nvarcharmax_col text Yes true \N
+alias_real_col float Yes true \N
+alias_smalldatetime_col datetime Yes true \N
+alias_smallint_col smallint Yes true \N
+alias_smallmoney_col decimal(10,4) Yes true \N
+alias_text_col text Yes true \N
+alias_time_col text Yes true \N
+alias_tinyint_col smallint Yes true \N
+alias_varchar_col text Yes true \N
+alias_varcharmax_col text Yes true \N
+id int No true \N
+plain_col text Yes true \N
+sysname_col text Yes true \N
+
+-- !alias_type --
+1 plain alias alias varchar max alias nvarchar alias nvarchar
max Doris Doris alias text alias ntext true
255 32767 1 9223372036854775807 123.123 1.5 12345.67
1234567890123456789012345678.0123456789 123.4567 214748.3647
2023-01-17 16:49:05.123 2023-01-17T16:49:05 2023-01-17T10:30:45.123
2023-01-17T16:49:05.123456 2023-01-17T16:49
FFFFFFFF-FFFF-FFFF-FFFF-FFFFFFFFFFFF sysname value
+2 \N \N \N \N \N \N \N \N \N
\N \N \N \N \N \N \N \N \N \N
\N \N \N \N \N \N \N \N \N
+
+-- !desc_alias_identity --
+id int No true \N
+val text Yes true \N
+
+-- !alias_identity --
+1 first
+2 second
+
+-- !desc_alias_name --
+alias_named_decimal_identity_col text Yes true \N
+alias_named_int_col text Yes true \N
+alias_named_int_identity_col text Yes true \N
+id int No true \N
+
+-- !alias_name --
+1 not an int not a decimal not an id
+
+-- !desc_alias_unsupported --
+alias_binary_col unknown type: UNSUPPORTED_TYPE Yes true \N
+alias_datetimeoffset_col unknown type: UNSUPPORTED_TYPE Yes true
\N
+alias_image_col unknown type: UNSUPPORTED_TYPE Yes true \N
+alias_varbinary_col unknown type: UNSUPPORTED_TYPE Yes true \N
+alias_variant_col unknown type: UNSUPPORTED_TYPE Yes true \N
+geometry_col unknown type: UNSUPPORTED_TYPE Yes true \N
+hierarchyid_col unknown type: UNSUPPORTED_TYPE Yes true \N
+id int No true \N
+plain_col text Yes true \N
+xml_col unknown type: UNSUPPORTED_TYPE Yes true \N
+
+-- !alias_unsupported --
+1 plain
+2 \N
+
-- !datetime_eq --
1 2023-01-17 2023-01-17T10:30 2023-01-17T10:30:00.123
@@ -179,6 +247,18 @@ bit_value boolean No true \N
id int No true \N
varbinary_value varbinary(20) Yes true \N
+-- !desc_alias_unsupported_varbinary --
+alias_binary_col unknown type: UNSUPPORTED_TYPE Yes true \N
+alias_datetimeoffset_col unknown type: UNSUPPORTED_TYPE Yes true
\N
+alias_image_col unknown type: UNSUPPORTED_TYPE Yes true \N
+alias_varbinary_col unknown type: UNSUPPORTED_TYPE Yes true \N
+alias_variant_col unknown type: UNSUPPORTED_TYPE Yes true \N
+geometry_col unknown type: UNSUPPORTED_TYPE Yes true \N
+hierarchyid_col unknown type: UNSUPPORTED_TYPE Yes true \N
+id int No true \N
+plain_col text Yes true \N
+xml_col unknown type: UNSUPPORTED_TYPE Yes true \N
+
-- !query --
1 false 0x4D616B6520446F72697320477265617421000000
0x4D616B6520446F72697320477265617421
2 true 0x4D616B6520446F72697320477265617421000000
0x4D616B6520446F72697320477265617421
diff --git
a/regression-test/suites/external_table_p2/jdbc/test_sqlserver_jdbc_catalog.groovy
b/regression-test/suites/external_table_p2/jdbc/test_sqlserver_jdbc_catalog.groovy
index 01648cdca6c..fbbb3078e85 100644
---
a/regression-test/suites/external_table_p2/jdbc/test_sqlserver_jdbc_catalog.groovy
+++
b/regression-test/suites/external_table_p2/jdbc/test_sqlserver_jdbc_catalog.groovy
@@ -86,6 +86,29 @@ suite("test_sqlserver_jdbc_catalog", "p2,external") {
order_qt_identity_decimal """ select * from test_identity_decimal
order by id; """
+ // Regression test for https://github.com/apache/doris/issues/67793
+ // Columns declared with a user-defined alias type (CREATE TYPE ...
FROM base_type) are reported
+ // by the driver with the alias name as TYPE_NAME. They must resolve
to the Doris type of their
+ // base type instead of UNSUPPORTED, so that both DESC and SELECT *
work.
+ order_qt_desc_alias_type """ desc test_alias_type; """
+ order_qt_alias_type """ select * from test_alias_type order by id; """
+ // IDENTITY on an alias typed column
+ order_qt_desc_alias_identity """ desc test_alias_identity; """
+ order_qt_alias_identity """ select * from test_alias_identity order by
id; """
+ // Alias types named like a system type ([int alias], [decimal(18,0)
identity], [int identity]) are
+ // resolved by their base type; only a real IDENTITY column is
reported with its base type decorated.
+ order_qt_desc_alias_name """ desc test_alias_name; """
+ order_qt_alias_name """ select * from test_alias_name order by id; """
+ // Aliases over binary types, datetimeoffset and sql_variant can not
be resolved by the JDBC type
+ // code, and the xml / CLR system types are not supported either. They
stay UNSUPPORTED, the other
+ // columns of the table remain readable and SELECT * still fails on
the unsupported columns.
+ order_qt_desc_alias_unsupported """ desc test_alias_unsupported; """
+ order_qt_alias_unsupported """ select id, plain_col from
test_alias_unsupported order by id; """
+ test {
+ sql """ select * from test_alias_unsupported order by id; """
+ exception "UNSUPPORTED"
+ }
+
// Test cases for SQL Server date format pushdown
(handleSQLServerDateFormat)
// Uses test_date_filter table which has diverse date/datetime values
across rows
// to verify that filters genuinely include/exclude the correct rows.
@@ -138,6 +161,8 @@ suite("test_sqlserver_jdbc_catalog", "p2,external") {
sql """ use ${ex_db_name} """
order_qt_desc """ desc test_binary; """
+ // enable.mapping.varbinary only applies to the native binary types,
aliases over them stay UNSUPPORTED
+ order_qt_desc_alias_unsupported_varbinary """ desc
test_alias_unsupported; """
sql """ CALL EXECUTE_STMT("test_sqlserver_jdbc_catalog_binary",
"DELETE FROM dbo.test_binary WHERE id = 4") """
order_qt_query """ select * from test_binary order by id; """
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
