SQLDescribeCol and SQLColAttribute return different types for bool, timestamptz between different driver versions (1.59 vs 2.x)

[fivetran-JamesGarcia]

Driver version

Amazon Redshift (x64) (2.0.1, 2.1.2.0)

Redshift version

PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.71629

Client Operating System

Linux 4.18.0-477.27.1.el8_8.x86_64 #1 SMP Wed Sep 20 15:55:39 UTC 2023 x86_64 x86_64 x86_64 GNU/Linux

Table schema

CREATE TABLE IF NOT EXISTS public.jtbl ( int_col INTEGER ENCODE az64 ,varchar_col VARCHAR(256) ENCODE lzo ,bool_col BOOLEAN ENCODE RAW ,timestamp_col TIMESTAMP WITH TIME ZONE ENCODE az64 ,char_col CHAR(10) ENCODE lzo )

Problem description

1. When describing columns using SQLDescribeCol and SQLColAttribute, we expect the same ColumnDataType returned from the mentioned functions are when upgrading from AWS Redshift driver from version (1.59 to 2.0.1/2.1.2). For backwards compatibility we expect the same DataTypes returned for the same table.
2. The ColumnDataType returned from the mentioned functions are different for ColumnDataType. More specifically affecting BOOL and TIMESTAMP datatypes, though we've observered this on some CHAR type functions as well.

I've attached a sample ODBC code which reproduces the problem.

Here are there results from running the program against 1.59 driver (Note there's 2 statements inside which illustrate the problem) Statement 1: select ( '1' ) as \"change_op\", ( current_timestamp(3) ) as \"change_time\"";

Number of Result Columns 2

Column : 1 Column Name : change_op Column Name Len : 9 SQL Data Type : -9 Type Desc Name: varchar Type Desc: -9 Data Size : 1 DecimalDigits : 0 Nullable 1

Column : 2 Column Name : change_time Column Name Len : 11 SQL Data Type : 93 Type Desc Name: timestamptz Type Desc: 9 Data Size : 26 DecimalDigits : 6 Nullable 1

Statement 2: SELECT Int_col, Char_col, Varchar_col, Bool_col, TimeStamp_col FROM JTBL Number of Result Columns 5

Column : 1 Column Name : int_col Column Name Len : 7 SQL Data Type : 4 Type Desc Name: int4 Type Desc: 4 Data Size : 10 DecimalDigits : 0 Nullable 1

Column : 2 Column Name : char_col Column Name Len : 8 SQL Data Type : -8 Type Desc Name: bpchar Type Desc: -8 Data Size : 10 DecimalDigits : 0 Nullable 1

Column : 3 Column Name : varchar_col Column Name Len : 11 SQL Data Type : -10 Type Desc Name: varchar Type Desc: -10 Data Size : 256 DecimalDigits : 0 Nullable 1

Column : 4 Column Name : bool_col Column Name Len : 8 SQL Data Type : 12 Type Desc Name: bool Type Desc: 12 Data Size : 5 DecimalDigits : 0 Nullable 1

Column : 5 Column Name : timestamp_col Column Name Len : 13 SQL Data Type : 93 Type Desc Name: timestamptz Type Desc: 9 Data Size : 26 DecimalDigits : 6 Nullable 1

Here are there results from running the program against 2.1.2 driver.
Statement 1: select ( '1' ) as \"change_op\", ( current_timestamp(3) ) as \"change_time\"";

Number of Result Columns 2

Column : 1 Column Name : change_op Column Name Len : 9 SQL Data Type : 12 Type Desc Name: CHARACTER VARYING Type Desc: 12 Data Size : 1 DecimalDigits : 0 Nullable 1

Column : 2 Column Name : change_time Column Name Len : 11 SQL Data Type : 12 Type Desc Name: CHARACTER VARYING Type Desc: 12 Data Size : -1 DecimalDigits : 0 Nullable 1

Statement 2: SELECT Int_col, Char_col, Varchar_col, Bool_col, TimeStamp_col FROM JTBL Number of Result Columns 5

Column : 1 Column Name : int_col Column Name Len : 7 SQL Data Type : 4 Type Desc Name: INTEGER Type Desc: 4 Data Size : 10 DecimalDigits : 0 Nullable 1

Column : 2 Column Name : char_col Column Name Len : 8 SQL Data Type : 1 Type Desc Name: CHARACTER Type Desc: 1 Data Size : 10 DecimalDigits : 0 Nullable 1

Column : 3 Column Name : varchar_col Column Name Len : 11 SQL Data Type : 12 Type Desc Name: CHARACTER VARYING Type Desc: 12 Data Size : 256 DecimalDigits : 0 Nullable 1

Column : 4 Column Name : bool_col Column Name Len : 8 SQL Data Type : -7 Type Desc Name: BOOL Type Desc: -7 Data Size : 1 DecimalDigits : 0 Nullable 1 unknown datatype = -7

Column : 5 Column Name : timestamp_col Column Name Len : 13 SQL Data Type : 12 Type Desc Name: CHARACTER VARYING Type Desc: 12 Data Size : 32 DecimalDigits : 0 Nullable 1

Reproduction code

Attached C code, please comment in pieces of code to change the respective statements or drivers. gcc version: (gcc (GCC) 8.5.0) To compile: put C files under same directory, then compile: gcc desc.c -lodbc -o desc To run: export ODBCINST=/etc/odbcinst.ini (or set it to the test system's value)

C_files.zip

[vahid110]

Hi @fivetran-JamesGarcia Thank you for taking the time to report this issue. I appreciate your effort and will make sure it reaches the appropriate team for investigation as soon as possible.

[fivetran-JamesGarcia]

@vahid110 , Any updates with this? Thanks!

[Shermont]

I notice that version 2.1.3.0 attempted to fix the TIMESTAMPTZ issue. But they didn't quite fix it properly. I opened a new issue (#30) to report that problem.

[vahid110]

Hi @fivetran-JamesGarcia As @Shermont correctly mentioned, the TIMESTAMP issue was flagged in a separate issue and a fix is in the release queue. My team is now looking into the BOOL issue.