Horton

Thread

Commits

  1. btree_gist: fix NaN handling in float4/float8 opclasses.

  1. BUG #19524: In `contrib/btree_gist` float4/float8 GiST index operations, handling NaN values with raw C operator

    The Post Office <noreply@postgresql.org> — 2026-06-18T07:52:50Z

    The following bug has been logged on the website:

Bug reference:      19524
Logged by:          Yuelin Wang
Email address:      3020001251@tju.edu.cn
PostgreSQL version: 19beta1
Operating system:   Linux (Ubuntu 24.04, x86_64)
Description:        

**Component**: `contrib/btree_gist/btree_float4.c`, `btree_float8.c`,
`btree_utils_num.c`

```sql
CREATE EXTENSION btree_gist;

-- 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;

-- 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;
RESET ROLE;

-- Effect 3: index corruption after page split (float8)
CREATE TABLE t (val float8);
CREATE INDEX ON t USING gist (val);
INSERT INTO t SELECT 'NaN'::float8 FROM generate_series(1, 2000);
SET enable_indexscan = off; SET enable_bitmapscan = off;
SELECT COUNT(*) AS seqscan_count FROM t WHERE val = 'NaN';
RESET ALL;
SET enable_seqscan = off; SET enable_bitmapscan = off;
SELECT COUNT(*) AS indexscan_count FROM t WHERE val = 'NaN';
RESET ALL;
```

Expected vs actual output:

| Query | Expected | Actual |
|---|---|---|
| `SELECT COUNT(*) FROM reservations` | `1` (second insert blocked by
EXCLUDE) | `2` |
| `SELECT * FROM measurements ORDER BY id` (lowpriv, index scan) | `(2,
1.5)` only | `(1, NaN)` and `(2, 1.5)` |
| `seqscan_count` (Effect 3, seq scan forced) | `2000` | `2000` |
| `indexscan_count` (Effect 3, index scan forced) | `2000` | `0` |

The comparison functions in `btree_float4.c` and `btree_float8.c` use raw C
operators (`==`, `<`, `>`) on `float4`/`float8` values. Under IEEE 754, all
comparisons involving NaN return false, including `NaN == NaN`, which
diverges from PostgreSQL's semantic ordering where NaN equals itself and
sorts above all finite values. This causes two independent failures:
`gbt_float8eq(NaN, NaN)` returns false, so `BtreeGistNotEqualStrategyNumber`
in `btree_utils_num.c:300` incorrectly concludes that NaN satisfies `!=
NaN`, and since `gbt_float8_consistent()` unconditionally sets `*recheck =
false`, the heap-level filter is never applied. Separately,
`gbt_float8key_cmp(NaN, NaN)` returns -1 instead of 0, violating strict weak
ordering and producing a corrupted GiST tree during `picksplit` that cannot
locate NaN entries.

The fix is to replace the raw C operators in `gbt_float4gt/ge/eq/le/lt` and
`gbt_float8gt/ge/eq/le/lt` with PostgreSQL's `float4_cmp_internal()` and
`float8_cmp_internal()`, which handle NaN correctly. The key comparators
`gbt_float4key_cmp()` and `gbt_float8key_cmp()` need the same update. As a
defensive measure, `*recheck` in the consistent functions should be set to
`true` when the query or key involves NaN.

  2. Re: BUG #19524: In `contrib/btree_gist` float4/float8 GiST index operations, handling NaN values with raw C operator

    Ayush Tiwari <ayushtiwari.slg01@gmail.com> — 2026-06-18T13:36:52Z

    Hi,

On Thu, 18 Jun 2026 at 18:54, PG Bug reporting form <noreply@postgresql.org>
wrote:

> The following bug has been logged on the website:
>
> Bug reference:      19524
> Logged by:          Yuelin Wang
> Email address:      3020001251@tju.edu.cn
> PostgreSQL version: 19beta1
> Operating system:   Linux (Ubuntu 24.04, x86_64)
> Description:
>
> **Component**: `contrib/btree_gist/btree_float4.c`, `btree_float8.c`,
> `btree_utils_num.c`
>
> ```sql
> CREATE EXTENSION btree_gist;
>
> -- 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;
>
> -- 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;
> RESET ROLE;
>
> -- Effect 3: index corruption after page split (float8)
> CREATE TABLE t (val float8);
> CREATE INDEX ON t USING gist (val);
> INSERT INTO t SELECT 'NaN'::float8 FROM generate_series(1, 2000);
> SET enable_indexscan = off; SET enable_bitmapscan = off;
> SELECT COUNT(*) AS seqscan_count FROM t WHERE val = 'NaN';
> RESET ALL;
> SET enable_seqscan = off; SET enable_bitmapscan = off;
> SELECT COUNT(*) AS indexscan_count FROM t WHERE val = 'NaN';
> RESET ALL;
> ```
>
> Expected vs actual output:
>
> | Query | Expected | Actual |
> |---|---|---|
> | `SELECT COUNT(*) FROM reservations` | `1` (second insert blocked by
> EXCLUDE) | `2` |
> | `SELECT * FROM measurements ORDER BY id` (lowpriv, index scan) | `(2,
> 1.5)` only | `(1, NaN)` and `(2, 1.5)` |
> | `seqscan_count` (Effect 3, seq scan forced) | `2000` | `2000` |
> | `indexscan_count` (Effect 3, index scan forced) | `2000` | `0` |
>
>
This looks the same as:
https://www.postgresql.org/message-id/19501-3bff3bbc97f1e7c9%40postgresql.org
Could you please check.

Regards,
Ayush

  3. Re: BUG #19524: In `contrib/btree_gist` float4/float8 GiST index operations, handling NaN values with raw C operator

    王跃林 <violin0613@tju.edu.cn> — 2026-06-18T13:43:23Z

    I apologize for the overlap. The root cause is identical and my analysis additionally identifies EXCLUDE constraint bypass and RLS bypass as security impacts with the same fix.










 王跃林
3020001251@tju.edu.cn











Original:
From：Ayush Tiwari <ayushtiwari.slg01@gmail.com>Date：2026-06-18 21:36:52(中国 (GMT+08:00))To：3020001251<3020001251@tju.edu.cn> , pgsql-bugs<pgsql-bugs@lists.postgresql.org>Cc：Subject：Re: BUG #19524: In `contrib/btree_gist` float4/float8 GiST index operations, handling NaN values with raw C operatorHi,

On Thu, 18 Jun 2026 at 18:54, PG Bug reporting form <noreply@postgresql.org> wrote:

The following bug has been logged on the website:

 Bug reference:      19524
 Logged by:          Yuelin Wang
 Email address:      3020001251@tju.edu.cn
 PostgreSQL version: 19beta1
 Operating system:   Linux (Ubuntu 24.04, x86_64)
 Description:       

 **Component**: `contrib/btree_gist/btree_float4.c`, `btree_float8.c`,
 `btree_utils_num.c`

 ```sql
 CREATE EXTENSION btree_gist;

 -- 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;

 -- 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;
 RESET ROLE;

 -- Effect 3: index corruption after page split (float8)
 CREATE TABLE t (val float8);
 CREATE INDEX ON t USING gist (val);
 INSERT INTO t SELECT 'NaN'::float8 FROM generate_series(1, 2000);
 SET enable_indexscan = off; SET enable_bitmapscan = off;
 SELECT COUNT(*) AS seqscan_count FROM t WHERE val = 'NaN';
 RESET ALL;
 SET enable_seqscan = off; SET enable_bitmapscan = off;
 SELECT COUNT(*) AS indexscan_count FROM t WHERE val = 'NaN';
 RESET ALL;
 ```

 Expected vs actual output:

 | Query | Expected | Actual |
 |---|---|---|
 | `SELECT COUNT(*) FROM reservations` | `1` (second insert blocked by
 EXCLUDE) | `2` |
 | `SELECT * FROM measurements ORDER BY id` (lowpriv, index scan) | `(2,
 1.5)` only | `(1, NaN)` and `(2, 1.5)` |
 | `seqscan_count` (Effect 3, seq scan forced) | `2000` | `2000` |
 | `indexscan_count` (Effect 3, index scan forced) | `2000` | `0` |


This looks the same as: https://www.postgresql.org/message-id/19501-3bff3bbc97f1e7c9%40postgresql.org
Could you please check.

Regards,
Ayush

  4. Re: BUG #19524: In `contrib/btree_gist` float4/float8 GiST index operations, handling NaN values with raw C operator

    Ayush Tiwari <ayushtiwari.slg01@gmail.com> — 2026-06-18T13:48:03Z

    Hi,

On Thu, 18 Jun 2026 at 19:13, 王跃林 <violin0613@tju.edu.cn> wrote:

> I apologize for the overlap. The root cause is identical and my analysis
> additionally identifies EXCLUDE constraint bypass and RLS bypass as
> security impacts with the same fix.
>
It would be great if you could add additional details
over that bug thread and take it forward.

Regards,
Ayush

  5. Re: BUG #19524: In `contrib/btree_gist` float4/float8 GiST index operations, handling NaN values with raw C operator

    王跃林 <violin0613@tju.edu.cn> — 2026-06-18T14:02:47Z

    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 root cause is that gbt_float8eq(NaN, NaN) returns false due to IEEE 754 semantics, causing BtreeGistNotEqualStrategyNumber in btree_utils_num.c:300 to incorrectly conclude that NaN satisfies != NaN. The fix (replacing raw C operators with float8_cmp_internal) addresses both effects. As an additional defensive measure, *recheck in the consistent functions should be set to true when the query or key involves NaN.









 王跃林
3020001251@tju.edu.cn











Original:
From：Ayush Tiwari <ayushtiwari.slg01@gmail.com>Date：2026-06-18 21:48:03(中国 (GMT+08:00))To：王跃林<violin0613@tju.edu.cn>Cc：pgsql-bugs <pgsql-bugs@lists.postgresql.org>Subject：Re: BUG #19524: In `contrib/btree_gist` float4/float8 GiST index operations, handling NaN values with raw C operatorHi,

On Thu, 18 Jun 2026 at 19:13, 王跃林 <violin0613@tju.edu.cn> wrote:

I apologize for the overlap. The root cause is identical and my analysis additionally identifies EXCLUDE constraint bypass and RLS bypass as security impacts with the same fix.
It would be great if you could add additional details
over that bug thread and take it forward.

Regards,
Ayush

  6. Re: BUG #19524: In `contrib/btree_gist` float4/float8 GiST index operations, handling NaN values with raw C operator

    Ayush Tiwari <ayushtiwari.slg01@gmail.com> — 2026-06-18T14:23:37Z

    Hi,

On Thu, 18 Jun 2026 at 19:32, 王跃林 <violin0613@tju.edu.cn> wrote:

> 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 root cause is that gbt_float8eq(NaN, NaN) returns false due to IEEE
> 754 semantics, causing BtreeGistNotEqualStrategyNumber in
> btree_utils_num.c:300 to incorrectly conclude that NaN satisfies != NaN.
> The fix (replacing raw C operators with float8_cmp_internal) addresses both
> effects. As an additional defensive measure, *recheck in the consistent
> functions should be set to true when the query or key involves NaN.
>
I meant forwarding this on the other thread (BUG #19501)
which has been reported.

Regards,
Ayush