DatabaseMetaData#getColumns() returns conflicting results for ResultSet#getInt("DATA_TYPE") and ResultSet#getString("TYPE_NAME")

[rsmckinney]

DatabaseMetaData#getColumns() returns conflicting results for ResultSet#getInt("DATA_TYPE") and ResultSet#getString("TYPE_NAME")

For instance, a column with a TIMESTAMP type will have the following results:
ResultSet#getInt("DATA_TYPE") : VARCHAR
ResultSet#getString("TYPE_NAME") : TIMESTAMP (declared SQL type)

Of course, the right answer is TIMESTAMP. Also, for the same column referenced in a query the result is Types#TIMESTAMP as it should be.
ResultSetMetaData#getColumnType(index) : Types#TIMESTAMP

Please fix the ResultSet#getInt("DATA_TYPE") for use with DatabaseMetaData#getColumns().

[gotson]

As a general rule, please provide reproduction code.

[rsmckinney]

My bad, but I don't have the time to provide an operational test for you, I had assumed there would be one already you could examine. Anyhow, here are the steps to reproduce the issue.

1. create a db with a table having a TIMESTAMP column. Here's a test table, note last_update column is a TIMESTAMP:

create table actor
(
    actor_id    INT         not null
        primary key,
    first_name  VARCHAR(45) not null,
    last_name   VARCHAR(45) not null,
    last_update TIMESTAMP   not null
);

2. use JDBC to connect to the database
3. call DatabaseMetaData#getColumns() for the table
4. call ResultSet#getInt("DATA_TYPE") corresponding with the TIMESTAMP column
5. notice the type is Types.VARCHAR, the type should be Types.TIMESTAMP
   The current behavior is not only incorrect, it will blow up code generation tooling since the type for query columns is correctly reported as TIMESTAMP. Either a compile error or a runtime error awaits.

I'm using h2 for unit tests, but I found this issue while ad hoc testing with sqlite. I haven't tested other column types with sqlite, but based on prior type-safety related jdbc driver issues I've run into with sqlite, I have a feeling TIMESTAMP is not an isolated case e.g., DATE etc. I thought perhaps you would like to know about this.

Update:
Note, you don't need to populate the test db with any data. This is purely a metadata related issue.

[rsmckinney]

I think what you could do to fix this issue is implement DatabaseMetadata#getColumns() wrt JDBC3ResultSet#getInt("DATA_TYPE") using the same code from JDBC3ResultSet#getColumnType(int col). For some reason you managed to implement getColumnType() correctly, but not the getColumns() one. Generally, both of these implementations must return the same type for the same column reference.

[gotson]

but I don't have the time to provide an operational test for you, I had assumed there would be one already you could examine

Bold of you to believe maintainers have time while you don't even take time to explain the problems you think you have.

[rsmckinney]

Hey man, if you truly don’t understand the bug as I have described it, then ask a question; I’m happy to clarify. Otherwise, either state that you don’t agree with my analysis, which is fine. Or, fix the bug and write the test. If that doesn’t appeal to you, you ought not be a maintainer.