Thread
Commits
-
btree_gist: fix NaN handling in float4/float8 opclasses.
- d569ccd40858 16 (unreleased) landed
- d215d2cc2ae1 17 (unreleased) landed
- a47005f0b11d 19 (unreleased) landed
- 98dd4406f7a4 15 (unreleased) landed
- 7d3448961da3 master landed
- 255bce44884e 14 (unreleased) landed
- 1e1d07792e08 18 (unreleased) landed
-
BUG #19501: btree_gist: use float4/float8 comparison functions to handle NaN correctly
The Post Office <noreply@postgresql.org> — 2026-05-29T15:35:05Z
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: 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=# CREATE EXTENSION btree_gist; CREATE EXTENSION test=# CREATE TABLE test_nan_bug (v float8); CREATE TABLE test=# INSERT INTO test_nan_bug VALUES (1.0), (2.0), ('NaN'::float8), (3.0); INSERT 0 4 test=# CREATE INDEX idx_nan_bug ON test_nan_bug USING gist(v); CREATE INDEX test=# SET enable_indexscan=off; SET test=# SELECT count(*) FROM test_nan_bug WHERE v = 'NaN'::float8; count ------- 1 (1 row) test=# EXPLAIN VERBOSE SELECT count(*) FROM test_nan_bug WHERE v = 'NaN'::float8; QUERY PLAN ------------------------------------------------------------------------- Aggregate (cost=1.05..1.06 rows=1 width=8) Output: count(*) -> Seq Scan on public.test_nan_bug (cost=0.00..1.05 rows=1 width=0) Output: v Filter: (test_nan_bug.v = 'NaN'::double precision) (5 rows) test=# SET enable_indexscan=on; SET enable_seqscan=off; SET SET test=# SELECT count(*) FROM test_nan_bug WHERE v = 'NaN'::float8; count ------- 0 (1 row) test=# EXPLAIN VERBOSE SELECT count(*) FROM test_nan_bug WHERE v = 'NaN'::float8; QUERY PLAN -------------------------------------------------------------------------------------------------- Aggregate (cost=8.15..8.16 rows=1 width=8) Output: count(*) -> Index Only Scan using idx_nan_bug on public.test_nan_bug (cost=0.13..8.15 rows=1 width=0) Output: v Index Cond: (test_nan_bug.v = '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=off; SELECT count(*) FROM test_nan_bug WHERE v = 'NaN'::float8; SET enable_indexscan=on; SET enable_seqscan=off; SELECT count(*) FROM test_nan_bug WHERE v = '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) >= *((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) == *((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) <= *((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); } static int @@ -57,16 +57,12 @@ gbt_float4key_cmp(const void *a, const void *b, FmgrInfo *flinfo) { float4KEY *ia = (float4KEY *) (((const Nsrt *) a)->t); float4KEY *ib = (float4KEY *) (((const Nsrt *) b)->t); + int cmp; - if (ia->lower == ib->lower) - { - if (ia->upper == ib->upper) - return 0; - - return (ia->upper > ib->upper) ? 1 : -1; - } - - return (ia->lower > ib->lower) ? 1 : -1; + cmp = float4_cmp_internal(ia->lower, ib->lower); + if (cmp != 0) + return cmp; + return float4_cmp_internal(ia->upper, ib->upper); } 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) >= *((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) == *((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) <= *((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); } static int @@ -58,16 +58,12 @@ gbt_float8key_cmp(const void *a, const void *b, FmgrInfo *flinfo) { float8KEY *ia = (float8KEY *) (((const Nsrt *) a)->t); float8KEY *ib = (float8KEY *) (((const Nsrt *) b)->t); + int cmp; - if (ia->lower == ib->lower) - { - if (ia->upper == ib->upper) - return 0; - - return (ia->upper > ib->upper) ? 1 : -1; - } - - return (ia->lower > ib->lower) ? 1 : -1; + cmp = float8_cmp_internal(ia->lower, ib->lower); + if (cmp != 0) + return cmp; + return float8_cmp_internal(ia->upper, ib->upper); } 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) +INSERT INTO float4tmp VALUES ('NaN'), ('NaN'); +SET enable_seqscan=off; +SELECT count(*) FROM float4tmp WHERE a = '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) +INSERT INTO float8tmp VALUES ('NaN'), ('NaN'); +SET enable_seqscan=off; +SELECT count(*) FROM float8tmp WHERE a = '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=off; + +SELECT count(*) FROM float4tmp WHERE a = '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=off; + +SELECT count(*) FROM float8tmp WHERE a = 'NaN'; + +RESET enable_seqscan; ``` Any thoughts? Regards, Man Zeng -
Re:BUG #19501: btree_gist: use float4/float8 comparison functions to handle NaN correctly
ylwangtju <ylwangtju@qq.com> — 2026-06-18T16:43:34Z
In addition to the index scan inconsistency you described, I found two further security-relevant effects from the same root cause. Effect 1: EXCLUDE constraint bypass (float4) CREATE TABLE reservations ( room float4, during tsrange, EXCLUDE USING gist (room WITH =, during WITH &&) ); INSERT INTO reservations VALUES ('NaN'::float4, '[2025-01-01,2025-01-02)'); INSERT INTO reservations VALUES ('NaN'::float4, '[2025-01-01,2025-01-02)'); SELECT COUNT(*) FROM reservations; Expected: 1 (second insert blocked). Actual: 2. The EXCLUDE constraint is silently bypassed for NaN values, allowing duplicate conflicting rows to be inserted. Effect 2: RLS bypass (float8) CREATE TABLE measurements (id int, val float8); CREATE INDEX ON measurements USING gist (val); INSERT INTO measurements VALUES (1, 'NaN'), (2, 1.5); ALTER TABLE measurements ENABLE ROW LEVEL SECURITY; ALTER TABLE measurements FORCE ROW LEVEL SECURITY; CREATE POLICY hide_nan ON measurements FOR SELECT USING (val != 'NaN'::float8); CREATE ROLE lowpriv LOGIN; GRANT SELECT ON measurements TO lowpriv; SET ROLE lowpriv; SET enable_seqscan = off; SET enable_bitmapscan = off; SELECT * FROM measurements ORDER BY id; Expected: only (2, 1.5). Actual: both rows including (1, NaN). The RLS policy is bypassed when the GiST index is used because gbt_float8_consistent() sets *recheck = false unconditionally, so the heap-level RLS filter is never applied. The fix is right, and I just followed up some other impacts. Original From:PG Bug reporting form <noreply@postgresql.org> Sent Time:May 29, 2026 23:35 To:pgsql-bugs <pgsql-bugs@lists.postgresql.org> Cc:zengman <zengman@halodbtech.com> Subject:BUG #19501: btree_gist: use float4/float8 comparison functions to handle NaN correctly 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: 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=# CREATE EXTENSION btree_gist; CREATE EXTENSION test=# CREATE TABLE test_nan_bug (v float8); CREATE TABLE test=# INSERT INTO test_nan_bug VALUES (1.0), (2.0), ('NaN'::float8), (3.0); INSERT 0 4 test=# CREATE INDEX idx_nan_bug ON test_nan_bug USING gist(v); CREATE INDEX test=# SET enable_indexscan=off; SET test=# SELECT count(*) FROM test_nan_bug WHERE v = 'NaN'::float8; count ------- 1 (1 row) test=# EXPLAIN VERBOSE SELECT count(*) FROM test_nan_bug WHERE v = 'NaN'::float8; QUERY PLAN ------------------------------------------------------------------------- Aggregate (cost=1.05..1.06 rows=1 width=8) Output: count(*) -> Seq Scan on public.test_nan_bug (cost=0.00..1.05 rows=1 width=0) Output: v Filter: (test_nan_bug.v = 'NaN'::double precision) (5 rows) test=# SET enable_indexscan=on; SET enable_seqscan=off; SET SET test=# SELECT count(*) FROM test_nan_bug WHERE v = 'NaN'::float8; count ------- 0 (1 row) test=# EXPLAIN VERBOSE SELECT count(*) FROM test_nan_bug WHERE v = 'NaN'::float8; QUERY PLAN -------------------------------------------------------------------------------------------------- Aggregate (cost=8.15..8.16 rows=1 width=8) Output: count(*) -> Index Only Scan using idx_nan_bug on public.test_nan_bug (cost=0.13..8.15 rows=1 width=0) Output: v Index Cond: (test_nan_bug.v = '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=off; SELECT count(*) FROM test_nan_bug WHERE v = 'NaN'::float8; SET enable_indexscan=on; SET enable_seqscan=off; SELECT count(*) FROM test_nan_bug WHERE v = '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) >= *((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) == *((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) <= *((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); } static int @@ -57,16 +57,12 @@ gbt_float4key_cmp(const void *a, const void *b, FmgrInfo *flinfo) { float4KEY *ia = (float4KEY *) (((const Nsrt *) a)->t); float4KEY *ib = (float4KEY *) (((const Nsrt *) b)->t); + int cmp; - if (ia->lower == ib->lower) - { - if (ia->upper == ib->upper) - return 0; - - return (ia->upper > ib->upper) ? 1 : -1; - } - - return (ia->lower > ib->lower) ? 1 : -1; + cmp = float4_cmp_internal(ia->lower, ib->lower); + if (cmp != 0) + return cmp; + return float4_cmp_internal(ia->upper, ib->upper); } 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) >= *((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) == *((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) <= *((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); } static int @@ -58,16 +58,12 @@ gbt_float8key_cmp(const void *a, const void *b, FmgrInfo *flinfo) { float8KEY *ia = (float8KEY *) (((const Nsrt *) a)->t); float8KEY *ib = (float8KEY *) (((const Nsrt *) b)->t); + int cmp; - if (ia->lower == ib->lower) - { - if (ia->upper == ib->upper) - return 0; - - return (ia->upper > ib->upper) ? 1 : -1; - } - - return (ia->lower > ib->lower) ? 1 : -1; + cmp = float8_cmp_internal(ia->lower, ib->lower); + if (cmp != 0) + return cmp; + return float8_cmp_internal(ia->upper, ib->upper); } 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) +INSERT INTO float4tmp VALUES ('NaN'), ('NaN'); +SET enable_seqscan=off; +SELECT count(*) FROM float4tmp WHERE a = '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) +INSERT INTO float8tmp VALUES ('NaN'), ('NaN'); +SET enable_seqscan=off; +SELECT count(*) FROM float8tmp WHERE a = '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=off; + +SELECT count(*) FROM float4tmp WHERE a = '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=off; + +SELECT count(*) FROM float8tmp WHERE a = 'NaN'; + +RESET enable_seqscan; ``` Any thoughts? Regards, Man Zeng