MIME-Version: 1.0
References: <19524-9559d302c8455664@postgresql.org>
In-Reply-To: <19524-9559d302c8455664@postgresql.org>
From: Ayush Tiwari <ayushtiwari.slg01@gmail.com>
Date: Thu, 18 Jun 2026 19:06:52 +0530
Message-ID: 
 <CAJTYsWXZYpz872NHTaWjAvQ2C-nFTDibzpHx9EBhPzq8Rgftmw@mail.gmail.com>
Subject: Re: BUG #19524: In `contrib/btree_gist` float4/float8 GiST index
 operations, handling NaN values with raw C operator
To: 3020001251@tju.edu.cn, pgsql-bugs@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000a3df18065487454c"
Archived-At: 
 <https://www.postgresql.org/message-id/CAJTYsWXZYpz872NHTaWjAvQ2C-nFTDibzpHx9EBhPzq8Rgftmw%40mail.gmail.com>
Precedence: bulk

--000000000000a3df18065487454c
Content-Type: text/plain; charset="UTF-8"

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

--000000000000a3df18065487454c
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">Hi,</div><br><div class=3D"gmail_quote gm=
ail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Thu, 18 Jun 2=
026 at 18:54, PG Bug reporting form &lt;<a href=3D"mailto:noreply@postgresq=
l.org">noreply@postgresql.org</a>&gt; wrote:<br></div><blockquote class=3D"=
gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(20=
4,204,204);padding-left:1ex">The following bug has been logged on the websi=
te:<br>
<br>
Bug reference:=C2=A0 =C2=A0 =C2=A0 19524<br>
Logged by:=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Yuelin Wang<br>
Email address:=C2=A0 =C2=A0 =C2=A0 <a href=3D"mailto:3020001251@tju.edu.cn"=
 target=3D"_blank">3020001251@tju.edu.cn</a><br>
PostgreSQL version: 19beta1<br>
Operating system:=C2=A0 =C2=A0Linux (Ubuntu 24.04, x86_64)<br>
Description:=C2=A0 =C2=A0 =C2=A0 =C2=A0 <br>
<br>
**Component**: `contrib/btree_gist/btree_float4.c`, `btree_float8.c`,<br>
`btree_utils_num.c`<br>
<br>
```sql<br>
CREATE EXTENSION btree_gist;<br>
<br>
-- Effect 1: EXCLUDE constraint bypass (float4)<br>
CREATE TABLE reservations (<br>
=C2=A0 =C2=A0 room=C2=A0 =C2=A0float4,<br>
=C2=A0 =C2=A0 during tsrange,<br>
=C2=A0 =C2=A0 EXCLUDE USING gist (room WITH =3D, during WITH &amp;&amp;)<br=
>
);<br>
INSERT INTO reservations VALUES (&#39;NaN&#39;::float4, &#39;[2025-01-01,20=
25-01-02)&#39;);<br>
INSERT INTO reservations VALUES (&#39;NaN&#39;::float4, &#39;[2025-01-01,20=
25-01-02)&#39;);<br>
SELECT COUNT(*) FROM reservations;<br>
<br>
-- Effect 2: RLS bypass (float8)<br>
CREATE TABLE measurements (id int, val float8);<br>
CREATE INDEX ON measurements USING gist (val);<br>
INSERT INTO measurements VALUES (1, &#39;NaN&#39;), (2, 1.5);<br>
ALTER TABLE measurements ENABLE ROW LEVEL SECURITY;<br>
ALTER TABLE measurements FORCE ROW LEVEL SECURITY;<br>
CREATE POLICY hide_nan ON measurements FOR SELECT USING (val !=3D<br>
&#39;NaN&#39;::float8);<br>
CREATE ROLE lowpriv LOGIN;<br>
GRANT SELECT ON measurements TO lowpriv;<br>
SET ROLE lowpriv;<br>
SET enable_seqscan =3D off;<br>
SET enable_bitmapscan =3D off;<br>
SELECT * FROM measurements ORDER BY id;<br>
RESET ROLE;<br>
<br>
-- Effect 3: index corruption after page split (float8)<br>
CREATE TABLE t (val float8);<br>
CREATE INDEX ON t USING gist (val);<br>
INSERT INTO t SELECT &#39;NaN&#39;::float8 FROM generate_series(1, 2000);<b=
r>
SET enable_indexscan =3D off; SET enable_bitmapscan =3D off;<br>
SELECT COUNT(*) AS seqscan_count FROM t WHERE val =3D &#39;NaN&#39;;<br>
RESET ALL;<br>
SET enable_seqscan =3D off; SET enable_bitmapscan =3D off;<br>
SELECT COUNT(*) AS indexscan_count FROM t WHERE val =3D &#39;NaN&#39;;<br>
RESET ALL;<br>
```<br>
<br>
Expected vs actual output:<br>
<br>
| Query | Expected | Actual |<br>
|---|---|---|<br>
| `SELECT COUNT(*) FROM reservations` | `1` (second insert blocked by<br>
EXCLUDE) | `2` |<br>
| `SELECT * FROM measurements ORDER BY id` (lowpriv, index scan) | `(2,<br>
1.5)` only | `(1, NaN)` and `(2, 1.5)` |<br>
| `seqscan_count` (Effect 3, seq scan forced) | `2000` | `2000` |<br>
| `indexscan_count` (Effect 3, index scan forced) | `2000` | `0` |<br><br><=
/blockquote><div><br>This looks the same as:=C2=A0<a href=3D"https://www.po=
stgresql.org/message-id/19501-3bff3bbc97f1e7c9%40postgresql.org">https://ww=
w.postgresql.org/message-id/19501-3bff3bbc97f1e7c9%40postgresql.org</a><br>=
Could you please check.<br><br>Regards,<br>Ayush=C2=A0</div></div></div>

--000000000000a3df18065487454c--