morningman opened a new pull request, #67916:
URL: https://github.com/apache/doris/pull/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 |
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`.
- `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.
### Release note
Fix SQL Server JDBC catalog mapping columns of user-defined alias types
(`CREATE TYPE ... FROM base_type`) to `UNSUPPORTED_TYPE`; they are now resolved
to the Doris type of their base type.
### Check List (For Author)
- Test
- [x] Regression test
- [x] Unit Test
- [ ] Manual test (add detailed scripts or steps below)
- [ ] No need to test or manual test. Explain why:
- [ ] This is a refactor/code format and no logic has been changed.
- [ ] Previous test can cover this change.
- [ ] No code files have been changed.
- [ ] Other reason
- Unit tests: new `JdbcSQLServerClientTest` (fe-core) and extended
`JdbcSQLServerConnectorClientTest` (fe-connector-jdbc) cover alias columns for
every base type family, prove that unknown / CLR / vendor-specific types stay
`UNSUPPORTED`, and that the name-based mapping still takes precedence.
- Regression test: the SQL Server docker fixture now creates alias types
and a `dbo.test_alias_type` table; `test_sqlserver_jdbc_catalog` asserts both
`DESC` and `SELECT *` on it. Verified end to end on a local 1 FE + 1 BE cluster
built from this branch against the SQL Server 2022 docker image: the suite
passes and the real output of the two new blocks is identical to the committed
`.out`.
- Metadata evidence: `sp_columns_100` (ODBC v3, what mssql-jdbc calls
from `getColumns()`) on SQL Server 2022 reports alias columns with `TYPE_NAME`
= alias name and `DATA_TYPE` = the base type code: varchar 12, nvarchar -9, int
4, bigint -5, tinyint -6, bit -7, decimal and money 3 (money with precision 19
/ scale 4), float 6 (the driver maps it to `DOUBLE`), date 91,
datetime/datetime2/smalldatetime 93 with the base scale, time -154 (mapped to
`TIME`), uniqueidentifier -11 (mapped to `CHAR`), datetimeoffset -155,
binary/varbinary/image -2/-3/-4, sql_variant -150, xml -152 (mapped to
`LONGNVARCHAR`), geometry/geography/hierarchyid -151 (mapped to `VARBINARY`).
- Behavior changed:
- [x] Yes. SQL Server columns of user-defined alias types (and
`sysname`) are now readable through the JDBC catalog instead of being reported
as `UNSUPPORTED_TYPE`. Columns of other SQL Server types keep their previous
mapping.
- Does this need documentation?
- [x] No.
### Check List (For Reviewer who merge this PR)
- [ ] Confirm the release note
- [ ] Confirm test cases
- [ ] Confirm document
- [ ] Add branch pick label
🤖 Generated with [Claude Code](https://claude.com/claude-code)
--
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]
