[Bug]: Unexpected Casting During Division Between Two Columns

[luca-ferreri]

What happened?

I really appreciate the effort and dedication put into this project. I would like to open a bug report to help improve it further or to learn something new.

When performing a division operation between two columns of double data type in a table, the denominator is unexpectedly cast to a numeric data type, leading to unexpected results.

Steps to Reproduce:

import pandas as pd
import duckdb
from sqlalchemy import create_engine, Double, MetaData, select

df = pd.DataFrame(
    [
        [1, 420.0, 0.9591],
        [2, 567.8, 0.9591],
        [3, 2345.9, 0.9591],
        [2, 1000.0, 0.9591],
        [3, 2000.0, 0.9591],
    ],
    columns=["id", "value", "eur2usd_rate"],
)
with duckdb.connect("to_delete.db") as con:
    con.execute("create table fake_df as select * from df")

duckdb_eng = create_engine("duckdb:///./to_delete.db")

metadata = MetaData()
metadata.reflect(duckdb_eng)
fake_df = metadata.tables["fake_df"]

sql_ = select(
    fake_df.c.id,
    fake_df.c.value,
    fake_df.c.eur2usd_rate,
    (fake_df.c.value / fake_df.c.eur2usd_rate).label("unexpected__value_usd"),
    #below we force the casting of the denominator to Double 
    (fake_df.c.value / fake_df.c.eur2usd_rate.cast(Double)).label("expected_value_usd"),
)

executing the query

df_ = pd.read_sql(sql_, duckdb_eng)

the result follows

	id	value	eur2usd_rate	unexpected_value_usd	expected_value_usd
0	1	420.0	0.9591	437.910537475	437.910541132
1	2	567.8	0.9591	592.013340900	592.013345845
2	3	2345.9	0.9591	2445.938880623	2445.938901053
3	2	1000.0	0.9591	1042.644136844	1042.644145553
4	3	2000.0	0.9591	2085.288273689	2085.288291106

To illustrate, let's consider the first row. In DuckDB, the query SELECT 420./.9591 returns 437.910541132 (the expected value). However, when using DuckDB-engine, the result is 437.910537475, which matches the result in DuckDB when you run SELECT 420./.9591::FLOAT.

DuckDB Engine Version

0.14.0

DuckDB Version

1.1.3

SQLAlchemy Version

2.0.36

Relevant log output

No response

Code of Conduct

• I agree to follow this project's Code of Conduct

[Mause]

Hi @luca-ferreri, are you able to give a code sample where the result differs? Or are you only refering to the generated SQL being different than expected?

[luca-ferreri]

Hi @Mause, thanks once again. For convenience, I've updated and improved the bug file above. To summarize:

in DuckDB, the query SELECT 420./.9591 returns 437.910541132 (the expected value). However, when using DuckDB-engine, the result is 437.910537475, which matches the result in DuckDB when you run SELECT 420./.9591::FLOAT.

[Mause]

I've merged and released #1194 in 0.14.1, though I'm not sure this will solve your issue.

Your issue is more related to DuckDB inferring different data types in different situations when types aren't explicitly specified.

⚫◗ SELECT typeof(420./.9591), typeof(420.0), typeof(0.9591)
    ;
┌───────────────────────┬───────────────┬────────────────┐
│ typeof((420 / .9591)) │ typeof(420.0) │ typeof(0.9591) │
│        varchar        │    varchar    │    varchar     │
├───────────────────────┼───────────────┼────────────────┤
│ DOUBLE                │ DECIMAL(4,1)  │ DECIMAL(5,4)   │
└───────────────────────┴───────────────┴────────────────┘

Notably, none of these types are floats

[luca-ferreri]

new version, 0.14.1, solves my issue, thank you @Mause!

What I've learned is that with duckdb-engine version<0.14.1

print((fake_df.c.value / fake_df.c.eur2usd_rate).compile(duckdb_eng))

returns

fake_df.value / CAST(fake_df.eur2usd_rate AS FLOAT)

and in duckdb

SELECT typeof(420./.9591::FLOAT)

returns a FLOAT (Single precision floating-point number (4 bytes)).

While with duckdb-engine version>=0.14.1

print((fake_df.c.value / fake_df.c.eur2usd_rate).compile(duckdb_eng))

returns

fake_df.value / fake_df.eur2usd_rate

and in duckdb division of doubles (like these of example dataframe) or division of numbers with finite decimal expansion like 420./.9591 returns double floating-point data types.