Re: BUG #19705: One NaN box makes a BRIN box_inclusion_ops index omit unrelated rows

On Wed, 30 Sep 2026 21:04:19 -0600, shihao zhong <zhong950419(at)gmail(dot)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