| From: | ylwangtju <ylwangtju(at)qq(dot)com> |
|---|---|
| To: | zengman <zengman(at)halodbtech(dot)com>, pgsql-bugs <pgsql-bugs(at)lists(dot)postgresql(dot)org> |
| Cc: | zengman <zengman(at)halodbtech(dot)com> |
| Subject: | Re:BUG #19501: btree_gist: use float4/float8 comparison functions to handle NaN correctly |
| Date: | 2026-06-18 16:43:34 |
| Message-ID: | tencent_3720A390761FE22DBD346D11B781D5AC6706@qq.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-bugs |
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(at)postgresql(dot)org>
Sent Time:May 29, 2026 23:35
To:pgsql-bugs <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Cc:zengman <zengman(at)halodbtech(dot)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(at)halodbtech(dot)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(at)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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | PG Bug reporting form | 2026-06-18 18:17:12 | BUG #19527: Double-Abort Crash in `ResOwnerReleaseOSSLCipher` via `encrypt_iv` with Oversized Input |
| Previous Message | Ayush Tiwari | 2026-06-18 14:41:32 | Re: BUG #19525: In `contrib/dict_int`, handling a token whose first byte is a null byte causes `pnstrdup()` . |