From: Greg Burd <greg@burd.me>
To: pgsql-bugs@postgresql.org
Cc: zhong950419@gmail.com
Cc: johncnaylorls@gmail.com
Cc: tomas@vondra.me
Cc: heikki.linnakangas@iki.fi
Cc: manuelreyesbravo@gmail.com
Cc: reshkekirill@gmail.com
Cc: x4mmm@yandex-team.ru
Cc: kehan5800@gmail.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19705: One NaN box makes a BRIN box_inclusion_ops index omit unrelated rows
Date: Tue, 20 Oct 2026 18:45:06 +0000
Message-ID: <ate07peWPMRb0FXw@floki> (raw)
On Wed, 30 Sep 2026 21:04:19 -0600, shihao zhong <zhong950419@gmail.com> wrote:
> > The following might be relevant as a different perspective:
> >
> > https://www.postgresql.org/message-id/1122078.1741378100@sss.pgh.pa.us
>
> Hi John,
>
> Thanks. I don't think v6 conflicts with Tom's idea. Banning NaN
> would reject input that works today, so it can't be back-patched. And
> in master, NaN boxes already on disk stay there after pg_upgrade. So
> the indexes need fixing either way.
>
> If you want less code, I can drop 0004 and use the v6-REL_18-0001
> check on master too. That leaves no catalog change.
>
> Thanks,
> Shihao
Hey Shihao,
I happened onto this very issue myself when testing the other day. I
was testing a fix for this with an index-vs-sequential-scan check and
found two NaN/infinity cases it does not cover. Both are on master as
well, so I'm adding my voice here rather than as separate reports; they
may be in scope for this patch or may want their own. Your call.
1. Polygon containment. A polygon with NaN vertices is reported to
contain every point by a sequential scan, but no points by a GiST index:
CREATE TABLE t (g point);
INSERT INTO t VALUES ('(0,0)'), ('(5,5)');
CREATE INDEX ON t USING gist (g);
SET enable_indexscan = off; SET enable_bitmapscan = off;
SELECT count(*) FROM t WHERE g <@ polygon '((NaN,NaN))'; -- 2
SET enable_seqscan = off;
SELECT count(*) FROM t WHERE g <@ polygon '((NaN,NaN))'; -- 0
The sequential scan is the wrong side here: point_inside() builds a
crossing count from lseg_crossing(), and with NaN vertices every float
comparison is false, so it falls through and counts a crossing for each
edge, making any point "inside". box and circle give false for the same
inputs, and point '(NaN,NaN)' <@ an ordinary square also comes back
true. The GiST side (empty bbox -> no match) is the consistent answer.
2. KNN order. An ORDER BY ... <-> with an infinite query point over a
column that contains NaN points raises
ERROR: index returned tuples in wrong order
from the executor's reorder recheck, because the index distance order
and the recheck disagree for an infinite point against NaN keys.
Both reproduce on current master. I realize this patch is scoped to the
BRIN and GiST box cases from the original report; flag these as either
in or out of scope as you see fit.
best.
-greg
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-bugs@postgresql.org
Cc: greg@burd.me, zhong950419@gmail.com, johncnaylorls@gmail.com, tomas@vondra.me, heikki.linnakangas@iki.fi, manuelreyesbravo@gmail.com, reshkekirill@gmail.com, x4mmm@yandex-team.ru, kehan5800@gmail.com
Subject: Re: BUG #19705: One NaN box makes a BRIN box_inclusion_ops index omit unrelated rows
In-Reply-To: <ate07peWPMRb0FXw@floki>
References: <ate07peWPMRb0FXw@floki>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox