Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: BUG #19501: btree_gist: use float4/float8 comparison functions to
 handle NaN correctly
To: pgsql-bugs@lists.postgresql.org
From: PG Bug reporting form <noreply@postgresql.org>
Cc: zengman@halodbtech.com
Reply-To: zengman@halodbtech.com, pgsql-bugs@lists.postgresql.org
Date: Fri, 29 May 2026 15:35:05 +0000
Message-ID: <19501-3bff3bbc97f1e7c9@postgresql.org>
Auto-Submitted: auto-generated
Archived-At: 
 <https://www.postgresql.org/message-id/19501-3bff3bbc97f1e7c9%40postgresql.org>
Precedence: bulk

The following bug has been logged on the website:

Bug reference:      19501
Logged by:          Man Zeng
Email address:      zengman@halodbtech.com
PostgreSQL version: 18.4
Operating system:   24.04.1-Ubuntu
Description:       =20

Hi all,

Here's an interesting case where btree_gist returns inconsistent results
between index scans and sequential scans when NaN values are involved.

```
postgres@zxm-VMware-Virtual-Platform:~/code/zengine$ psql
psql (19devel)
Type "help" for help.

test=3D# CREATE EXTENSION btree_gist;
CREATE EXTENSION
test=3D# CREATE TABLE test_nan_bug (v float8);
CREATE TABLE
test=3D# INSERT INTO test_nan_bug VALUES (1.0), (2.0), ('NaN'::float8), (3.=
0);
INSERT 0 4
test=3D# CREATE INDEX idx_nan_bug ON test_nan_bug USING gist(v);
CREATE INDEX
test=3D# SET enable_indexscan=3Doff;
SET
test=3D# SELECT count(*) FROM test_nan_bug WHERE v =3D 'NaN'::float8;
 count
-------
     1
(1 row)

test=3D# EXPLAIN VERBOSE SELECT count(*) FROM test_nan_bug WHERE v =3D
'NaN'::float8;
                               QUERY PLAN
-------------------------------------------------------------------------
 Aggregate  (cost=3D1.05..1.06 rows=3D1 width=3D8)
   Output: count(*)
   ->  Seq Scan on public.test_nan_bug  (cost=3D0.00..1.05 rows=3D1 width=
=3D0)
         Output: v
         Filter: (test_nan_bug.v =3D 'NaN'::double precision)
(5 rows)

test=3D# SET enable_indexscan=3Don; SET enable_seqscan=3Doff;
SET
SET
test=3D# SELECT count(*) FROM test_nan_bug WHERE v =3D 'NaN'::float8;
 count
-------
     0
(1 row)

test=3D# EXPLAIN VERBOSE SELECT count(*) FROM test_nan_bug WHERE v =3D
'NaN'::float8;
                                            QUERY PLAN
---------------------------------------------------------------------------=
-----------------------
 Aggregate  (cost=3D8.15..8.16 rows=3D1 width=3D8)
   Output: count(*)
   ->  Index Only Scan using idx_nan_bug on public.test_nan_bug
(cost=3D0.13..8.15 rows=3D1 width=3D0)
         Output: v
         Index Cond: (test_nan_bug.v =3D 'NaN'::double precision)
(5 rows)

```

Steps to reproduce:
```sql
CREATE EXTENSION btree_gist;
CREATE TABLE test_nan_bug (v float8);
INSERT INTO test_nan_bug VALUES (1.0), (2.0), ('NaN'::float8), (3.0);
CREATE INDEX idx_nan_bug ON test_nan_bug USING gist(v);

SET enable_indexscan=3Doff;
SELECT count(*) FROM test_nan_bug WHERE v =3D 'NaN'::float8;

SET enable_indexscan=3Don; SET enable_seqscan=3Doff;
SELECT count(*) FROM test_nan_bug WHERE v =3D 'NaN'::float8;
```

My fix replaces the C comparison operators with PostgreSQL's float
comparison functions (float8_gt, float8_ge, float8_eq, etc.) and
float8_cmp_internal, which handle NaN consistently.

```
diff --git a/contrib/btree_gist/btree_float4.c
b/contrib/btree_gist/btree_float4.c
index c076918fd48..eaecdac0980 100644
--- a/contrib/btree_gist/btree_float4.c
+++ b/contrib/btree_gist/btree_float4.c
@@ -29,27 +29,27 @@ PG_FUNCTION_INFO_V1(gbt_float4_sortsupport);
 static bool
 gbt_float4gt(const void *a, const void *b, FmgrInfo *flinfo)
 {
-       return (*((const float4 *) a) > *((const float4 *) b));
+       return float4_gt(*(const float4 *) a, *(const float4 *) b);
 }
 static bool
 gbt_float4ge(const void *a, const void *b, FmgrInfo *flinfo)
 {
-       return (*((const float4 *) a) >=3D *((const float4 *) b));
+       return float4_ge(*(const float4 *) a, *(const float4 *) b);
 }
 static bool
 gbt_float4eq(const void *a, const void *b, FmgrInfo *flinfo)
 {
-       return (*((const float4 *) a) =3D=3D *((const float4 *) b));
+       return float4_eq(*(const float4 *) a, *(const float4 *) b);
 }
 static bool
 gbt_float4le(const void *a, const void *b, FmgrInfo *flinfo)
 {
-       return (*((const float4 *) a) <=3D *((const float4 *) b));
+       return float4_le(*(const float4 *) a, *(const float4 *) b);
 }
 static bool
 gbt_float4lt(const void *a, const void *b, FmgrInfo *flinfo)
 {
-       return (*((const float4 *) a) < *((const float4 *) b));
+       return float4_lt(*(const float4 *) a, *(const float4 *) b);
 }
=20
 static int
@@ -57,16 +57,12 @@ gbt_float4key_cmp(const void *a, const void *b, FmgrInfo
*flinfo)
 {
        float4KEY  *ia =3D (float4KEY *) (((const Nsrt *) a)->t);
        float4KEY  *ib =3D (float4KEY *) (((const Nsrt *) b)->t);
+       int                     cmp;
=20
-       if (ia->lower =3D=3D ib->lower)
-       {
-               if (ia->upper =3D=3D ib->upper)
-                       return 0;
-
-               return (ia->upper > ib->upper) ? 1 : -1;
-       }
-
-       return (ia->lower > ib->lower) ? 1 : -1;
+       cmp =3D float4_cmp_internal(ia->lower, ib->lower);
+       if (cmp !=3D 0)
+               return cmp;
+       return float4_cmp_internal(ia->upper, ib->upper);
 }
=20
 static float8
diff --git a/contrib/btree_gist/btree_float8.c
b/contrib/btree_gist/btree_float8.c
index d7386e885a2..132065a648c 100644
--- a/contrib/btree_gist/btree_float8.c
+++ b/contrib/btree_gist/btree_float8.c
@@ -30,27 +30,27 @@ PG_FUNCTION_INFO_V1(gbt_float8_sortsupport);
 static bool
 gbt_float8gt(const void *a, const void *b, FmgrInfo *flinfo)
 {
-       return (*((const float8 *) a) > *((const float8 *) b));
+       return float8_gt(*(const float8 *) a, *(const float8 *) b);
 }
 static bool
 gbt_float8ge(const void *a, const void *b, FmgrInfo *flinfo)
 {
-       return (*((const float8 *) a) >=3D *((const float8 *) b));
+       return float8_ge(*(const float8 *) a, *(const float8 *) b);
 }
 static bool
 gbt_float8eq(const void *a, const void *b, FmgrInfo *flinfo)
 {
-       return (*((const float8 *) a) =3D=3D *((const float8 *) b));
+       return float8_eq(*(const float8 *) a, *(const float8 *) b);
 }
 static bool
 gbt_float8le(const void *a, const void *b, FmgrInfo *flinfo)
 {
-       return (*((const float8 *) a) <=3D *((const float8 *) b));
+       return float8_le(*(const float8 *) a, *(const float8 *) b);
 }
 static bool
 gbt_float8lt(const void *a, const void *b, FmgrInfo *flinfo)
 {
-       return (*((const float8 *) a) < *((const float8 *) b));
+       return float8_lt(*(const float8 *) a, *(const float8 *) b);
 }
=20
 static int
@@ -58,16 +58,12 @@ gbt_float8key_cmp(const void *a, const void *b, FmgrInfo
*flinfo)
 {
        float8KEY  *ia =3D (float8KEY *) (((const Nsrt *) a)->t);
        float8KEY  *ib =3D (float8KEY *) (((const Nsrt *) b)->t);
+       int                     cmp;
=20
-       if (ia->lower =3D=3D ib->lower)
-       {
-               if (ia->upper =3D=3D ib->upper)
-                       return 0;
-
-               return (ia->upper > ib->upper) ? 1 : -1;
-       }
-
-       return (ia->lower > ib->lower) ? 1 : -1;
+       cmp =3D float8_cmp_internal(ia->lower, ib->lower);
+       if (cmp !=3D 0)
+               return cmp;
+       return float8_cmp_internal(ia->upper, ib->upper);
 }
=20
 static float8
diff --git a/contrib/btree_gist/expected/float4.out
b/contrib/btree_gist/expected/float4.out
index dfe732049e6..99ed6ae4668 100644
--- a/contrib/btree_gist/expected/float4.out
+++ b/contrib/btree_gist/expected/float4.out
@@ -89,3 +89,12 @@ SELECT a, a <-> '-179.0' FROM float4tmp ORDER BY a <->
'-179.0' LIMIT 3;
  -158.17741 | 20.822586
 (3 rows)
=20
+INSERT INTO float4tmp VALUES ('NaN'), ('NaN');
+SET enable_seqscan=3Doff;
+SELECT count(*) FROM float4tmp WHERE a =3D 'NaN';
+ count
+-------
+     2
+(1 row)
+
+RESET enable_seqscan;
diff --git a/contrib/btree_gist/expected/float8.out
b/contrib/btree_gist/expected/float8.out
index ebd0ef3d689..e463b8869d6 100644
--- a/contrib/btree_gist/expected/float8.out
+++ b/contrib/btree_gist/expected/float8.out
@@ -89,3 +89,12 @@ SELECT a, a <-> '-1890.0' FROM float8tmp ORDER BY a <->
'-1890.0' LIMIT 3;
   -1769.73634 | 120.26366000000007
 (3 rows)
=20
+INSERT INTO float8tmp VALUES ('NaN'), ('NaN');
+SET enable_seqscan=3Doff;
+SELECT count(*) FROM float8tmp WHERE a =3D 'NaN';
+ count
+-------
+     2
+(1 row)
+
+RESET enable_seqscan;
diff --git a/contrib/btree_gist/sql/float4.sql
b/contrib/btree_gist/sql/float4.sql
index 3da1ce953c8..0cacee08276 100644
--- a/contrib/btree_gist/sql/float4.sql
+++ b/contrib/btree_gist/sql/float4.sql
@@ -35,3 +35,11 @@ SELECT count(*) FROM float4tmp WHERE a >  -179.0::float4;
 EXPLAIN (COSTS OFF)
 SELECT a, a <-> '-179.0' FROM float4tmp ORDER BY a <-> '-179.0' LIMIT 3;
 SELECT a, a <-> '-179.0' FROM float4tmp ORDER BY a <-> '-179.0' LIMIT 3;
+
+INSERT INTO float4tmp VALUES ('NaN'), ('NaN');
+
+SET enable_seqscan=3Doff;
+
+SELECT count(*) FROM float4tmp WHERE a =3D 'NaN';
+
+RESET enable_seqscan;
diff --git a/contrib/btree_gist/sql/float8.sql
b/contrib/btree_gist/sql/float8.sql
index e1e819b37f9..2d6ef9d95e7 100644
--- a/contrib/btree_gist/sql/float8.sql
+++ b/contrib/btree_gist/sql/float8.sql
@@ -35,3 +35,11 @@ SELECT count(*) FROM float8tmp WHERE a >
-1890.0::float8;
 EXPLAIN (COSTS OFF)
 SELECT a, a <-> '-1890.0' FROM float8tmp ORDER BY a <-> '-1890.0' LIMIT 3;
 SELECT a, a <-> '-1890.0' FROM float8tmp ORDER BY a <-> '-1890.0' LIMIT 3;
+
+INSERT INTO float8tmp VALUES ('NaN'), ('NaN');
+
+SET enable_seqscan=3Doff;
+
+SELECT count(*) FROM float8tmp WHERE a =3D 'NaN';
+
+RESET enable_seqscan;
```
Any thoughts?

Regards,
Man Zeng