MIME-Version: 1.0
References: 
 <CAMQXxcgbtD2LXfX0tpgvOizxP-XxrCHV2ZDy4By_TZnJMsxXWQ@mail.gmail.com>
 <FFB7AB27-9334-4F17-BF56-291845F4A9FD@yesql.se>
In-Reply-To: <FFB7AB27-9334-4F17-BF56-291845F4A9FD@yesql.se>
From: Bill Kim <billkimjh@gmail.com>
Date: Mon, 29 Jun 2026 21:04:52 +0900
Message-ID: 
 <CAMQXxch1N=sZ1fsfx57VhTe=8qpmb1K1vnaB5WVyDbAs8bqExg@mail.gmail.com>
Subject: Re: BUG #19524: NaN handling in btree_gist's float4/float8 opclasses
To: Daniel Gustafsson <daniel@yesql.se>
Cc: pgsql-bugs@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000b940db065563441f"
Archived-At: 
 <https://www.postgresql.org/message-id/CAMQXxch1N%3DsZ1fsfx57VhTe%3D8qpmb1K1vnaB5WVyDbAs8bqExg%40mail.gmail.com>
Precedence: bulk

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

  Hi,

  contrib/btree_gist's float4/float8 GiST opclasses compared keys with
  raw C operators (=3D=3D, <, >). Under IEEE 754 every comparison involving
  NaN returns false, so GiST disagreed with the regular btree opclass,
  which uses float[4|8]_cmp_internal() (all NaNs equal, NaN sorts after
  every non-NaN value).

  The disagreement was reachable from SQL:

  * an index scan on a GiST index over a float column returned no rows
    for `WHERE a =3D 'NaN'`, while a sequential scan returned the NaN row;
  * `EXCLUDE USING gist (a WITH =3D)` accepted two rows whose key was NaN,
    because gbt_float8eq(NaN, NaN) was false;
  * an RLS predicate `USING (a !=3D 'NaN')` leaked NaN rows on index scan.

  The fix swaps the five gbt_float{4,8}{gt,ge,eq,le,lt} comparators and
  the picksplit gbt_float{4,8}key_cmp for the NaN-aware
  float{4,8}_{gt,ge,eq,le,lt} / float{4,8}_cmp_internal() helpers in
  utils/float.h =E2=80=94 same total order the btree opclass already uses. =
No
  on-disk format change.

  Added regression coverage in contrib/btree_gist/sql/float{4,8}.sql for
  the three scenarios above. `make check` is green: core 245/245 +
  contrib btree_gist 32/32 + all 48 contrib modules.
  Originally reported as BUG #19501 and BUG #19524:

https://www.postgresql.org/message-id/19501-3bff3bbc97f1e7c9%40postgresql.o=
rg

https://www.postgresql.org/message-id/19524-9559d302c8455664%40postgresql.o=
rg

  I used an LLM (Claude Code) to help with the analysis and to draft the
  patch. I reviewed and tested the diff myself before sending.

  Regards,
  Bill Kim

2026=EB=85=84 6=EC=9B=94 29=EC=9D=BC (=EC=9B=94) =EC=98=A4=EC=A0=84 3:54, D=
aniel Gustafsson <daniel@yesql.se>=EB=8B=98=EC=9D=B4 =EC=9E=91=EC=84=B1:

> > On 28 Jun 2026, at 04:14, Bill Kim <billkimjh@gmail.com> wrote:
> >
> >  Hi all,
> >
> >   I have a patch for this and `make check` passes (core + all contrib,
> plus
> >   new btree_gist NaN cases covering the three scenarios in the report).
> >   Posting here first in case someone is already further along =E2=80=94=
 if not,
> >   I'll send it to pgsql-hackers and register it in the open CF in a
> couple
> >   of days.
>
> Even if someone else is working on it, always feel free to send your patc=
h.
> Multiple implementations of a fix is not a problem but an opportunity for
> everyone to learn from each other.
>
> --
> Daniel Gustafsson
>
>

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

<div dir=3D"ltr">=C2=A0 Hi,<br><br>=C2=A0 contrib/btree_gist&#39;s float4/f=
loat8 GiST opclasses compared keys with<br>=C2=A0 raw C operators (=3D=3D, =
&lt;, &gt;). Under IEEE 754 every comparison involving<br>=C2=A0 NaN return=
s false, so GiST disagreed with the regular btree opclass,<br>=C2=A0 which =
uses float[4|8]_cmp_internal() (all NaNs equal, NaN sorts after<br>=C2=A0 e=
very non-NaN value).<br><br>=C2=A0 The disagreement was reachable from SQL:=
<br><br>=C2=A0 * an index scan on a GiST index over a float column returned=
 no rows<br>=C2=A0 =C2=A0 for `WHERE a =3D &#39;NaN&#39;`, while a sequenti=
al scan returned the NaN row;<br>=C2=A0 * `EXCLUDE USING gist (a WITH =3D)`=
 accepted two rows whose key was NaN,<br>=C2=A0 =C2=A0 because gbt_float8eq=
(NaN, NaN) was false;<br>=C2=A0 * an RLS predicate `USING (a !=3D &#39;NaN&=
#39;)` leaked NaN rows on index scan.<br><br>=C2=A0 The fix swaps the five =
gbt_float{4,8}{gt,ge,eq,le,lt} comparators and<br>=C2=A0 the picksplit gbt_=
float{4,8}key_cmp for the NaN-aware<br>=C2=A0 float{4,8}_{gt,ge,eq,le,lt} /=
 float{4,8}_cmp_internal() helpers in<br>=C2=A0 utils/float.h =E2=80=94 sam=
e total order the btree opclass already uses. No<br>=C2=A0 on-disk format c=
hange.<br><br>=C2=A0 Added regression coverage in contrib/btree_gist/sql/fl=
oat{4,8}.sql for<br>=C2=A0 the three scenarios above. `make check` is green=
: core 245/245 +<br>=C2=A0 contrib btree_gist 32/32 + all 48 contrib module=
s.<br>=C2=A0 Originally reported as BUG #19501 and BUG #19524:<br>=C2=A0 <a=
 href=3D"https://www.postgresql.org/message-id/19501-3bff3bbc97f1e7c9%40pos=
tgresql.org">https://www.postgresql.org/message-id/19501-3bff3bbc97f1e7c9%4=
0postgresql.org</a><br>=C2=A0 <a href=3D"https://www.postgresql.org/message=
-id/19524-9559d302c8455664%40postgresql.org">https://www.postgresql.org/mes=
sage-id/19524-9559d302c8455664%40postgresql.org</a><br><br>=C2=A0 I used an=
 LLM (Claude Code) to help with the analysis and to draft the<br>=C2=A0 pat=
ch. I reviewed and tested the diff myself before sending.<br><br>=C2=A0 Reg=
ards,<br>=C2=A0 Bill Kim</div><br><div class=3D"gmail_quote gmail_quote_con=
tainer"><div dir=3D"ltr" class=3D"gmail_attr">2026=EB=85=84 6=EC=9B=94 29=
=EC=9D=BC (=EC=9B=94) =EC=98=A4=EC=A0=84 3:54, Daniel Gustafsson &lt;<a hre=
f=3D"mailto:daniel@yesql.se">daniel@yesql.se</a>&gt;=EB=8B=98=EC=9D=B4 =EC=
=9E=91=EC=84=B1:<br></div><blockquote class=3D"gmail_quote" style=3D"margin=
:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"=
>&gt; On 28 Jun 2026, at 04:14, Bill Kim &lt;<a href=3D"mailto:billkimjh@gm=
ail.com" target=3D"_blank">billkimjh@gmail.com</a>&gt; wrote:<br>
&gt; <br>
&gt;=C2=A0 Hi all,<br>
&gt; <br>
&gt;=C2=A0 =C2=A0I have a patch for this and `make check` passes (core + al=
l contrib, plus<br>
&gt;=C2=A0 =C2=A0new btree_gist NaN cases covering the three scenarios in t=
he report).<br>
&gt;=C2=A0 =C2=A0Posting here first in case someone is already further alon=
g =E2=80=94 if not,<br>
&gt;=C2=A0 =C2=A0I&#39;ll send it to pgsql-hackers and register it in the o=
pen CF in a couple<br>
&gt;=C2=A0 =C2=A0of days.<br>
<br>
Even if someone else is working on it, always feel free to send your patch.=
<br>
Multiple implementations of a fix is not a problem but an opportunity for<b=
r>
everyone to learn from each other.<br>
<br>
--<br>
Daniel Gustafsson<br>
<br>
</blockquote></div>

--000000000000b940db065563441f--