FLOAT4 comparison does not work as expected

[ahmed-shameem]

What happens?

When predicate is used of a float4 column, the comparison does not yield expected result in pg_duckdb. However, DuckDB returns results as expected.

To Reproduce

Only float4/real comparison does not work correctly. If we use only float instead, it produces correct result. float8 also works as expected.

-- Setup for float4 / real
-- float4 table where predicate does not work

CREATE TABLE FLOAT4_TBL (f1  float4);
INSERT INTO FLOAT4_TBL(f1) VALUES ('1004.30   ');

-- execute the following query:

postgres=# SET duckdb.force_execution TO true;
SET
postgres=# select * from FLOAT4_TBL where f1 = '1004.3';
 f1
----
(0 rows)

postgres=# select * from FLOAT4_TBL where f1 = 1004.3;
 f1
----
(0 rows)

-- Setup for float
-- float table where predicate works as expected

CREATE TABLE FLOAT_TBL (f1  float);
INSERT INTO FLOAT_TBL(f1) VALUES ('1004.30   ');

-- execute the following query:

postgres=# SET duckdb.force_execution TO true;
SET
postgres=# select * from FLOAT_TBL where f1 = '1004.3';
   f1
--------
 1004.3
(1 row)

postgres=# select * from FLOAT_TBL where f1 = '1004.3';
   f1
--------
 1004.3
(1 row)

-- Setup for float8
-- float table where predicate works as expected

CREATE TABLE FLOAT8_TBL (f1 float8);
INSERT INTO FLOAT8_TBL VALUES ('1004.30   ');

-- execute the following query:

postgres=# SET duckdb.force_execution TO true;
SET
postgres=# select * from FLOAT8_TBL where f1 = '1004.3';
   f1
--------
 1004.3
(1 row)

postgres=# select * from FLOAT8_TBL where f1 = 1004.3;
   f1
--------
 1004.3
(1 row)

The float4 comparison works only when we cast the column to numeric/decimal:

postgres=# select * from FLOAT4_TBL where f1::numeric = '1004.3';
   f1
--------
 1004.3
(1 row)

postgres=# select * from FLOAT4_TBL where f1::numeric = 1004.3;
   f1
--------
 1004.3
(1 row)

pg_duckdb version:

postgres=# \dx
                  List of installed extensions
   Name    | Version |   Schema   |         Description
-----------+---------+------------+------------------------------
 pg_duckdb | 0.3.0   | public     | DuckDB Embedded in Postgres
 plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

However, interesting thing is, DuckDB works as expected (DuckDb version: v1.4.0 (Andium) duckdb/duckdb@b8a06e4):

D CREATE TABLE FLOAT4_TBL (f1  float4);
D
D INSERT INTO FLOAT4_TBL(f1) VALUES ('1004.30  ');
D
D select * from FLOAT4_TBL where f1 = '1004.3';
┌────────┐
│   f1   │
│ float  │
├────────┤
│ 1004.3 │
└────────┘
D select * from FLOAT4_TBL where f1 = 1004.30 ;
┌────────┐
│   f1   │
│ float  │
├────────┤
│ 1004.3 │
└────────┘

This clearly proves that there is certainly as issue on pg_duckdb side. Most probably some kind of type mapping

OS:

MacOS arm64

pg_duckdb Version (if built from source use commit hash):

0.3.0

Postgres Version (if built from source use commit hash):

17.6

Hardware:

No response

Full Name:

Shameem Ahmed

Affiliation:

AWS

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 (e.g., CPU architecture, Linux distribution) to reproduce the issue?

• Yes, I have