Missing Results when using Floating-point in BETWEEN #9825

What happens?

Considering the test case below: The third SELECT returns an empty result, which is surprising: If the result of second query contains at least one true, the value of the BETWEEN expression should be true, and thus the third query should return one row in t0.

If replacing 3.1 to 3, or only inserting 5, things go well.

To Reproduce

CREATE TABLE t0(c1 INT);
INSERT INTO t0(c1) VALUES (-1);
INSERT INTO t0(c1) VALUES (5);

SELECT * FROM t0; -- -1, 5
SELECT (c1 BETWEEN 3.1 AND c1) FROM t0; -- false, true
SELECT * FROM t0 WHERE (c1 BETWEEN 3.1 AND c1); 
-- Expected: 5
-- Actual: empty

OS:

ubuntu 22.04

DuckDB Version:

v0.9.3-dev915 e46ece4

DuckDB Client:

CLI

Full Name:

Suyang Zhong

Affiliation:

NUS

Have you tried this on the latest main branch?

I have tested with a main build

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• Yes, I have