ResultSetMetadata of the ResultSet from DatabaseMetadata.getColumns() is Incorrect for Some Columns

[mbach979]

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

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

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.

-- 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'

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):

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.

mssql-synapse-get-columns-bug.tar.gz

[lilgreenbird]

hi @mbach979,

Thank you for the report, we will investigate and get back to you. Sounds like you have already spent some time debugging this so if you have a fix you could also submit a PR which would expedite the process.
Thanks.

[mbach979]

hi @mbach979,

Thank you for the report, we will investigate and get back to you. Sounds like you have already spent some time debugging this so if you have a fix you could also submit a PR which would expedite the process. Thanks.

I actually do have a potential fix. Unfortunately, since it was developed on my company's time, I am working on getting approval for its release.

[lilgreenbird]

hi @mbach979

Thank you, we are able to see the error from your repro code. I've added this issue to our backlog it will be triaged along with other issues and feature requests. If you are able to contribute to a fix please either create a PR, or include that here, that will help expedite the process.
Thanks.