ptrosek opened a new issue, #67793: URL: https://github.com/apache/doris/issues/67793
### Search before asking - [x] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no similar issues. ### Version Apache Doris: 4.1.0 SQL Server: 2019 JDBC Driver: both mssql-jdbc-13.4.0.jre8.jar and mssql-jdbc-12.10.2.jre8.jar have the same issue ### What's Wrong? In a SQL Server 2019 database (specifically observed in an InsERT Subiekt GT schema), columns defined using SQL Server user-defined alias types (`CREATE TYPE ... FROM <base_type>`) synchronize into the JDBC Catalog as `UNSUPPORTED_TYPE`. This causes queries referencing the affected table via `SELECT *` to fail: ``` SQL Error [1105] [HY000]: errCode = 2, detailMessage = type UNSUPPORTED is unsupported for Nereids ``` Currently, querying these tables requires explicitly enumerating only non-aliased columns or bypassing catalog table abstraction via `EXECUTE_STMT()`. ### What You Expected? Columns declared with a SQL Server alias type should resolve to the Doris type corresponding to their underlying primitive base type (for example, `customtexttype FROM varchar(50)` resolving to `TEXT`/`STRING`), allowing `SELECT *` and catalog queries to work normally. ### How to Reproduce? On SQL Server: ``` CREATE TYPE dbo.customtexttype FROM varchar(50) NOT NULL; GO CREATE TABLE dbo.test_alias ( id int PRIMARY KEY, plain_col varchar(50) NULL, alias_col dbo.customtexttype NULL ); GO ``` In Doris: ``` CREATE CATALOG sqlserver_test PROPERTIES ( 'type' = 'jdbc', 'user' = '<user>', 'password' = '<password>', 'jdbc_url' = 'jdbc:sqlserver://<host>:1433;databaseName=<db>;encrypt=false', 'driver_url' = 'https://repo1.maven.org/maven2/com/microsoft/sqlserver/mssql-jdbc/13.4.0.jre8/mssql-jdbc-13.4.0.jre8.jar', 'driver_class' = 'com.microsoft.sqlserver.jdbc.SQLServerDriver' ); ``` ``` DESC sqlserver_test.dbo.test_alias; ``` Result: | Field | Type | Null | Key | Default | Extra | |---|---|---|---|---|---| | id | int | No | true | null | | | plain_col | text | Yes | false | null | | | alias_col | unknown type: UNSUPPORTED_TYPE | Yes | false | null | | ``` SELECT * FROM sqlserver_test.dbo.test_alias; ``` Will result in: ``` SQL Error [1105] [HY000]: errCode = 2, detailMessage = type UNSUPPORTED is unsupported for Nereids ``` ### Anything Else? ### Root Cause In `JdbcSQLServerClient.jdbcTypeToDoris(JdbcFieldSchema fieldSchema)` (`fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/client/JdbcSQLServerClient.java`): ```java @Override protected Type jdbcTypeToDoris(JdbcFieldSchema fieldSchema) { String sqlserverType = fieldSchema.getDataTypeName().toLowerCase(); switch (sqlserverType) { case "bit": return Type.BOOLEAN; case "tinyint": case "smallint": return Type.SMALLINT; // ... case "char": case "varchar": return ScalarType.createStringType(); default: return Type.UNSUPPORTED; // <-- Alias types fall through here } } ``` 1. Doris inspects `fieldSchema.getDataTypeName()`, which is populated from JDBC's `DatabaseMetaData.getColumns()` result column `TYPE_NAME`. 2. For an alias type, SQL Server returns the declared alias name (`"customtexttype"`), not the underlying type name (`"varchar"`). 3. Because the alias name does not match any literal `case`, execution falls through to `default: return Type.UNSUPPORTED`. 4. However, `DatabaseMetaData.getColumns()` simultaneously populates `DATA_TYPE` with the standard JDBC integer code for the base type (`java.sql.Types.VARCHAR = 12`). Doris already extracts and stores this in memory via `fieldSchema.setDataType(rs.getInt("DATA_TYPE"))`. ### Evidence from SQL Server Catalogs Inspecting `dbo.test_alias` directly in SQL Server system views: ```sql SELECT c.name AS column_name, t.name AS declared_type, bt.name AS base_type, t.is_user_defined, t.is_assembly_type FROM sys.columns c JOIN sys.types t ON c.user_type_id = t.user_type_id LEFT JOIN sys.types bt ON t.system_type_id = bt.user_type_id AND bt.is_user_defined = 0 WHERE c.object_id = OBJECT_ID('dbo.test_alias') AND t.is_user_defined = 1; ``` Observed rows: | column_name | declared_type | base_type | is_user_defined | is_assembly_type | |---|---|---|---|---| | alias_col | customtexttype | varchar | 1 | 0 | `is_assembly_type = 0` confirms this is a plain alias type over a base type, not a CLR UDT. Metadata values returned by `mssql-jdbc` for `alias_col`: - `fieldSchema.getDataTypeName()`: `"customtexttype"` - `fieldSchema.getDataType()`: `12` (`java.sql.Types.VARCHAR`) - `fieldSchema.getColumnSize()`: `50` - `fieldSchema.getDecimalDigits()`: `0` ### Proposed Solution (Hybrid Fallback) Retain the primary name-based `switch (sqlserverType)` to preserve dialect-specific overrides (such as SQL Server `tinyint` mapping to `SMALLINT` or `money` mapping to `DOUBLE`). In the `default:` branch, inspect `fieldSchema.getDataType()` against `java.sql.Types` before returning `Type.UNSUPPORTED`: ```java @Override protected Type jdbcTypeToDoris(JdbcFieldSchema fieldSchema) { String sqlserverType = fieldSchema.getDataTypeName().toLowerCase(); switch (sqlserverType) { case "bit": return Type.BOOLEAN; case "tinyint": case "smallint": return Type.SMALLINT; case "int": return Type.INT; case "bigint": return Type.BIGINT; case "real": return Type.FLOAT; case "float": case "money": case "smallmoney": return Type.DOUBLE; case "decimal": case "numeric": return ScalarType.createDecimalV3Type(fieldSchema.getColumnSize(), fieldSchema.getDecimalDigits()); case "date": return ScalarType.getDefaultDateType(Type.DATE); case "datetime": case "datetime2": case "smalldatetime": return ScalarType.createDatetimeV2Type(6); case "char": case "varchar": case "nchar": case "nvarchar": case "text": case "ntext": case "time": case "datetimeoffset": return ScalarType.createStringType(); case "image": case "binary": case "varbinary": return Type.UNSUPPORTED; default: // Fallback for alias types resolving to standard JDBC base types return mapJdbcTypeFallback(fieldSchema); } } private Type mapJdbcTypeFallback(JdbcFieldSchema fieldSchema) { switch (fieldSchema.getDataType()) { case java.sql.Types.BIT: case java.sql.Types.BOOLEAN: return Type.BOOLEAN; case java.sql.Types.TINYINT: case java.sql.Types.SMALLINT: return Type.SMALLINT; case java.sql.Types.INTEGER: return Type.INT; case java.sql.Types.BIGINT: return Type.BIGINT; case java.sql.Types.REAL: return Type.FLOAT; case java.sql.Types.FLOAT: case java.sql.Types.DOUBLE: return Type.DOUBLE; case java.sql.Types.NUMERIC: case java.sql.Types.DECIMAL: return ScalarType.createDecimalV3Type(fieldSchema.getColumnSize(), fieldSchema.getDecimalDigits()); case java.sql.Types.DATE: return ScalarType.getDefaultDateType(Type.DATE); case java.sql.Types.TIMESTAMP: return ScalarType.createDatetimeV2Type(6); case java.sql.Types.CHAR: case java.sql.Types.VARCHAR: case java.sql.Types.LONGVARCHAR: case java.sql.Types.NCHAR: case java.sql.Types.NVARCHAR: case java.sql.Types.LONGNVARCHAR: return ScalarType.createStringType(); default: return Type.UNSUPPORTED; } } ``` ### Are you willing to submit PR? - [x] Yes I am willing to submit a PR! ### Code of Conduct - [x] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
