Microsoft Azure SQL Data Warehouse - 10.0.13655.0 Oct 23 2021 03:49:53 Copyright (c) Microsoft Corporation
CREATE TABLE test_get_cols_rsmd_t ( col1 int, col2 varchar(100), col3 datetime )
CREATE VIEW test_get_cols_rsmd_v AS SELECT * FROM test_get_cols_rsmd_t
The DatabaseMetaData.getColumns(...).getColumnClassName(x) should either be the class specified in the API, or assignable to class in the spec.
For DatabaseMetaData.getColumns().getColumnType(i), the values are not as prescribed, but obviously something with Types.INTEGER will not produce a String and vice-versa.
The output of the attached test program shows which columns are wrong per the spec given the simple schema above with one table and one view.
-- Testing Table getColumns(null, null, "test_get_cols_rsmd_t", null) RSMD
ERROR: Column 'REMARKS' type 'java.lang.Integer' is not assignable to expected type 'java.lang.String'
ERROR: Column 'COLUMN_DEF' type 'java.lang.Integer' is not assignable to expected type 'java.lang.String'
ERROR: Column 'SCOPE_CATALOG' type 'java.lang.Integer' is not assignable to expected type 'java.lang.String'
ERROR: Column 'SCOPE_SCHEMA' type 'java.lang.Integer' is not assignable to expected type 'java.lang.String'
ERROR: Column 'SCOPE_TABLE' type 'java.lang.Integer' is not assignable to expected type 'java.lang.String'
ERROR: Column 'SOURCE_DATA_TYPE' type 'java.lang.Integer' is not assignable to expected type 'java.lang.Short'
-- Testing View getColumns(null, null, "test_get_cols_rsmd_v", null) RSMD
ERROR: Column 'REMARKS' type 'java.lang.Integer' is not assignable to expected type 'java.lang.String'
ERROR: Column 'COLUMN_DEF' type 'java.lang.Integer' is not assignable to expected type 'java.lang.String'
ERROR: Column 'SCOPE_CATALOG' type 'java.lang.Integer' is not assignable to expected type 'java.lang.String'
ERROR: Column 'SCOPE_SCHEMA' type 'java.lang.Integer' is not assignable to expected type 'java.lang.String'
ERROR: Column 'SCOPE_TABLE' type 'java.lang.Integer' is not assignable to expected type 'java.lang.String'
ERROR: Column 'SOURCE_DATA_TYPE' type 'java.lang.Integer' is not assignable to expected type 'java.lang.Short'
From debugging the code the problem arises in SQLServerDatabaseMetaData.generateAzureDWSelect and SQLServerDatabaseMetaData.generateAzureDWEmptyRS because the are generating UNIONs of SQL literals retrieved from the sp_columns_100 procedure. The generated SQL then looks like the following for the above table when making the call getColumns(null, null, "test_get_cols_rsmd_t", null):
SELECT 'xxxxxxxx' AS TABLE_CAT,
'dbo' AS TABLE_SCHEM,
'test_get_cols_rsmd_t' AS TABLE_NAME,
'col1' AS COLUMN_NAME,
4 AS DATA_TYPE,
'int' AS TYPE_NAME,
10 AS COLUMN_SIZE,
4 AS BUFFER_LENGTH,
0 AS DECIMAL_DIGITS,
10 AS NUM_PREC_RADIX,
1 AS NULLABLE,
NULL AS REMARKS,
NULL AS COLUMN_DEF,
4 AS SQL_DATA_TYPE,
NULL AS SQL_DATETIME_SUB,
NULL AS CHAR_OCTET_LENGTH,
1 AS ORDINAL_POSITION,
'YES' AS IS_NULLABLE,
NULL AS SCOPE_CATALOG,
NULL AS SCOPE_SCHEMA,
NULL AS SCOPE_TABLE,
38 AS SOURCE_DATA_TYPE,
'NO' AS IS_AUTOINCREMENT,
'NO' AS IS_GENERATEDCOLUMN,
0 AS SS_IS_SPARSE,
0 AS SS_IS_COLUMN_SET,
NULL AS SS_UDT_CATALOG_NAME,
NULL AS SS_UDT_SCHEMA_NAME,
NULL AS SS_UDT_ASSEMBLY_TYPE_NAME,
NULL AS SS_XML_SCHEMACOLLECTION_CATALOG_NAME,
NULL AS SS_XML_SCHEMACOLLECTION_SCHEMA_NAME,
NULL AS SS_XML_SCHEMACOLLECTION_NAME
UNION ALL
SELECT 'xxxxxxxxxxxxx' AS TABLE_CAT,
'dbo' AS TABLE_SCHEM,
'test_get_cols_rsmd_t' AS TABLE_NAME,
'col2' AS COLUMN_NAME,
12 AS DATA_TYPE,
'varchar' AS TYPE_NAME,
100 AS COLUMN_SIZE,
100 AS BUFFER_LENGTH,
NULL AS DECIMAL_DIGITS,
NULL AS NUM_PREC_RADIX,
1 AS NULLABLE,
NULL AS REMARKS,
NULL AS COLUMN_DEF,
12 AS SQL_DATA_TYPE,
NULL AS SQL_DATETIME_SUB,
100 AS CHAR_OCTET_LENGTH,
2 AS ORDINAL_POSITION,
'YES' AS IS_NULLABLE,
NULL AS SCOPE_CATALOG,
NULL AS SCOPE_SCHEMA,
NULL AS SCOPE_TABLE,
39 AS SOURCE_DATA_TYPE,
'NO' AS IS_AUTOINCREMENT,
'NO' AS IS_GENERATEDCOLUMN,
0 AS SS_IS_SPARSE,
0 AS SS_IS_COLUMN_SET,
NULL AS SS_UDT_CATALOG_NAME,
NULL AS SS_UDT_SCHEMA_NAME,
NULL AS SS_UDT_ASSEMBLY_TYPE_NAME,
NULL AS SS_XML_SCHEMACOLLECTION_CATALOG_NAME,
NULL AS SS_XML_SCHEMACOLLECTION_SCHEMA_NAME,
NULL AS SS_XML_SCHEMACOLLECTION_NAME
UNION ALL
SELECT 'xxxxxxxxxxx' AS TABLE_CAT,
'dbo' AS TABLE_SCHEM,
'test_get_cols_rsmd_t' AS TABLE_NAME,
'col3' AS COLUMN_NAME,
93 AS DATA_TYPE,
'datetime' AS TYPE_NAME,
23 AS COLUMN_SIZE,
16 AS BUFFER_LENGTH,
3 AS DECIMAL_DIGITS,
NULL AS NUM_PREC_RADIX,
1 AS NULLABLE,
NULL AS REMARKS,
NULL AS COLUMN_DEF,
9 AS SQL_DATA_TYPE,
3 AS SQL_DATETIME_SUB,
NULL AS CHAR_OCTET_LENGTH,
3 AS ORDINAL_POSITION,
'YES' AS IS_NULLABLE,
NULL AS SCOPE_CATALOG,
NULL AS SCOPE_SCHEMA,
NULL AS SCOPE_TABLE,
111 AS SOURCE_DATA_TYPE,
'NO' AS IS_AUTOINCREMENT,
'NO' AS IS_GENERATEDCOLUMN,
0 AS SS_IS_SPARSE,
0 AS SS_IS_COLUMN_SET,
NULL AS SS_UDT_CATALOG_NAME,
NULL AS SS_UDT_SCHEMA_NAME,
NULL AS SS_UDT_ASSEMBLY_TYPE_NAME,
NULL AS SS_XML_SCHEMACOLLECTION_CATALOG_NAME,
NULL AS SS_XML_SCHEMACOLLECTION_SCHEMA_NAME,
NULL AS SS_XML_SCHEMACOLLECTION_NAME
ORDER BY table_cat,
table_schem,
table_name,
ordinal_position
The issue lies in the untyped NULLs and numbers (for the columns which should be shorts). Each generated NULL literal and number should be wrapped in a convert/cast to the correct type.
Driver version
10.2.0
SQL Server version
Microsoft Azure SQL Data Warehouse - 10.0.13655.0 Oct 23 2021 03:49:53 Copyright (c) Microsoft Corporation
Client Operating System
Windows 10/RHEL 7
JAVA/JVM version
Corretto 11.0.10_9
Table schema
Note that the attached test program will create/drop the above database objects
Problem description
The ResultSetMetaData.getColumnClassName (and also ResultSetMetaData.getColumnType) of the ResultSet returned by DatabaseMetaData.getColumns() return incorrect values per the JDBC API specification for ResultSetMetadata.getColumns(). (https://docs.oracle.com/javase/8/docs/api/java/sql/DatabaseMetaData.html#getColumns-java.lang.String-java.lang.String-java.lang.String-java.lang.String-).
Expected behavior
The DatabaseMetaData.getColumns(...).getColumnClassName(x) should either be the class specified in the API, or assignable to class in the spec.
For DatabaseMetaData.getColumns().getColumnType(i), the values are not as prescribed, but obviously something with Types.INTEGER will not produce a String and vice-versa.
Actual behavior
The output of the attached test program shows which columns are wrong per the spec given the simple schema above with one table and one view.
Any other details that can be helpful
From debugging the code the problem arises in SQLServerDatabaseMetaData.generateAzureDWSelect and SQLServerDatabaseMetaData.generateAzureDWEmptyRS because the are generating UNIONs of SQL literals retrieved from the sp_columns_100 procedure. The generated SQL then looks like the following for the above table when making the call getColumns(null, null, "test_get_cols_rsmd_t", null):
The issue lies in the untyped NULLs and numbers (for the columns which should be shorts). Each generated NULL literal and number should be wrapped in a convert/cast to the correct type.
mssql-synapse-get-columns-bug.tar.gz