SNOW-1250205: Driver returns incorrect column type while querying on Query History Views

[inishchith]

1. What version of JDBC driver are you using?

• v3.15.0

2. What operating system and processor architecture are you using?

• MacOS Ventura 13.6.4, arm64

3. What version of Java are you using?

• Java 18

5. What did you do?

• Views in Context - Access History, Query History
  • The document suggests that the column QUERY_START_TIME (from Access History View) and START_TIME (from Query History View) are both of type TIMESTAMP_LTZ.
• Via the JDBC driver, try to fetch the row for the same query (via query_id)

example query:

SELECT 
    qh.QUERY_ID AS QHI, 
    qh.START_TIME AS START_TIME, 
    ah.QUERY_ID AS AHI, 
    ah.QUERY_START_TIME AS QUERY_START_TIME 
FROM 
    SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY AS qh
INNER JOIN 
    SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY AS ah 
ON 
    qh.QUERY_ID = ah.QUERY_ID
WHERE 
    qh.QUERY_ID='QUERY_ID';

6. What did you expect to see?

• Both START_TIME and QUERY_START_TIME should ideally return the same column type (i.e TIMESTAMP_LTZ or SnowflakeTimestampWithTimezone) and value, but it doesn't. (image)

7. Can you set logging to DEBUG and collect the logs?

   https://community.snowflake.com/s/article/How-to-generate-log-file-on-Snowflake-connectors

8. What is your Snowflake account identifier, if any? (Optional)

• qia75894

[sfc-gh-sghosh]

Hello @inishchith ,

Thanks for raising the issue, we are taking a look.

Regards,
Sujan

[sfc-gh-sghosh]

Hello @inishchith ,

I tried to reproduce it with latest Snowflake JDBC 3.15.0, but its reflecting the correct data type TIMESTAMPLTZ for columns START_TIME from view SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY and SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY

Here is the output:
Column Name: QHI, Data Type: VARCHAR
Column Name: START_TIME, Data Type: TIMESTAMPLTZ
Column Name: AHI, Data Type: VARCHAR
Column Name: QUERY_START_TIME, Data Type: TIMESTAMPLTZ

Here is the sample code snippet

ResultSet resultSet=statement.executeQuery("SELECT \n"
				+ "    qh.QUERY_ID AS QHI, \n"
				+ "    qh.START_TIME AS START_TIME, \n"
				+ "    ah.QUERY_ID AS AHI, \n"
				+ "    ah.QUERY_START_TIME AS QUERY_START_TIME \n"
				+ "FROM \n"
				+ "    SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY AS qh\n"
				+ "INNER JOIN \n"
				+ "    SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY AS ah \n"
				+ "ON \n"
				+ "    qh.QUERY_ID = ah.QUERY_ID\n"
				+ "WHERE \n"
				+ "    qh.QUERY_ID='QUERY_ID' limit 1");
		

        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();

   
        for (int i = 1; i <= columnCount; i++) {
            String columnName = metaData.getColumnName(i);
            String columnType = metaData.getColumnTypeName(i);
            System.out.println("Column Name: " + columnName + ", Data Type: " + columnType );

        }

Regards,
Sujaan

[sfc-gh-sghosh]

Hello @inishchith ,

let us know if anything further or if you could provide the sample application with jdbc log which reproduces the issue, because its working expected and no issue being surfaced.

Regards,
Sujan

[inishchith]

@sfc-gh-sghosh Thanks for the turnaround, I wasn't able to reproduce this with the above snippet.
I think this could be with the way I am using it - I shall take a look. We can close this, If am able to get back - will reopen and share.

Thanks again!