Fetch methods give wrong result on certain values of numeric datatypes like numeric(36,14) or numeric(30,11)

[lucarnu]

Driver version

redshift-connector-2.0.918

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.61687 (Redshift serverless)

Client Operating System

Ubuntu 22.04.3 LTS on wsl

Python version

Python 3.11.6

Table schema

Problem description

1. Expected behaviour:

The result of following request by the functions fetchall/fetchone/fetch_dataframe should give a dataframe with value 135430.11999999999500 :

select 135430.11999999999500::numeric(36,14) as test

2. Actual behaviour:

The result of the given request is a dataframe with value -49037.32073709552116 .

3. Error message/stack trace:

No error message is available.

4. Any other details that can be helpful:

This behavior is not induced by pandas itself:

df = pd.DataFrame([{"test": 135430.11999999999500}])
print(df)

The problem persists on some variations of the cast like numeric(30,19).

An explicit cast to float or double precision in redshift leads to the expected behavior.

select 135430.11999999999500::double precision as test

Python Driver trace logs

No trace log is available.

Reproduction code

import redshift_connector

conn = redshift_connector.connect(
    host='<host>',
    database='<database>',
    user='<username>',
    password='<password>',
    ssl=True
)

cursor: redshift_connector.Cursor = conn.cursor()
query = f'''
    select 135430.11999999999500::numeric(36,14) as test
    union all
    select 1135430.11999999999500::numeric(36,14) as test
    union all
    select 35430.11999999999500::numeric(36,14) as test
    union all
    select 7872432525245.4577::numeric(36,14) as test
    union all
    select 252252::numeric(36,14) as test
'''
cursor.execute(query)
df = cursor.fetch_dataframe()
print(df)
conn.close()

[Brooke-white]

Hi @lucarnu , thank you for reporting this issue. For an immediate work around please provide client_protocol_version=1 as a connection parameter. I will update this issue as I have more information

[Brooke-white]

Hi @lucarnu , we will release a fix for this in our February release.

[Brooke-white]

Hi @lucarnu, we've fixed this in 2.1.0. thanks for your patience :)