Fetch methods give wrong result on certain values of numeric datatypes like numeric(36,14) or numeric(30,11) #207
Description
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
- 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
- Actual behaviour:
The result of the given request is a dataframe with value -49037.32073709552116 .
- Error message/stack trace:
No error message is available.
- 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()