Float parameter in WHERE clause unexpected mismatch

[mfellows]

What happens?

When querying a float column using a WHERE clause with positional parameters, the expected row is not returned, although the query succeeds with a CAST or a string-formatted value, even though both the original insertion and the subsequent query come from the same Python value.

To Reproduce

import duckdb

conn = duckdb.connect()
conn.execute("create table testing (col1 float)")
float_value = 2.1
conn.execute("insert into testing values (?)", [float_value])

conn.query("select * from testing where col1 = ?", params=[float_value])
# unexpected: 0 rows

conn.query(f"select * from testing where col1 = {float_value}")
# returns 1 row as expected

conn.query("select * from testing where col1 = cast(? as float)", params=[float_value])
# returns 1 row as expected

OS:

Windows 11

DuckDB Package Version:

1.4.3

Python Version:

3.10

Full Name:

Max Fellows

Affiliation:

Natural Resources Canada

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

Did you include all relevant data sets for reproducing the issue?

Yes

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration to reproduce the issue?

• Yes, I have

[evertlammerts]

Floating point types are notoriously hard. Read more about it here: https://duckdb.org/docs/stable/sql/data_types/numeric#floating-point-types

Depending on your use case you might want to use a Decimal instead... but it really depends on what you need.