Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xJEpp-000000003yx-4A5b for pgsql-bugs@arkaria.postgresql.org; Tue, 20 Oct 2026 18:45:19 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xJEpo-00000001URB-26uW for pgsql-bugs@arkaria.postgresql.org; Tue, 20 Oct 2026 18:45:16 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xJEpo-00000001UR2-0Hpq for pgsql-bugs@lists.postgresql.org; Tue, 20 Oct 2026 18:45:16 +0000 Received: from mail-4323.protonmail.ch ([185.70.43.23]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xJEpk-000000003WH-1mMN for pgsql-bugs@postgresql.org; Tue, 20 Oct 2026 18:45:15 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=burd.me; s=protonmail; t=1792521910; x=1792781110; bh=/vjb2UXzjoynwqSbNxpUMvY7zVihknOiTVZJ/MXVtdw=; h=Date:To:From:Cc:Subject:Message-ID:Feedback-ID:From:To:Cc:Date: Subject:Reply-To:Feedback-ID:Message-ID:BIMI-Selector; b=bMKyQrpM5yJNFvl7aGHQc4TeSifGHm0CDYjr8GEGD6zzxTck12+NismzylPKoZK+t EW/SaIFSt+7uvDNepdSX7ZhvcVzBjrpScoMO4i5+CUeeRJZ7QUK7MBfjUmZM5yS/Yj cLd4miV+WJvuLGizvQdjnJzu+bZ+WT4ytKHO7utwdP01MtTDmh6Mgk+9Qs0V8bsJuW OWrdT+RhxtuJ4MQMVTdxJcxKgObC8rNaPr53xCUwXDxKo6eAY7+VMwYqGVXZbxGIxr otyaNugcq4V55b38QPicLJwg3XjdREuFyFemywHwfzz7vjf3+lG2ri7IcuPGhgdG11 y/gny0ANrpRkA== Date: Tue, 20 Oct 2026 18:45:06 +0000 To: pgsql-bugs@postgresql.org From: Greg Burd Cc: 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, pgsql-bugs@lists.postgresql.org Subject: Re: BUG #19705: One NaN box makes a BRIN box_inclusion_ops index omit unrelated rows Message-ID: Feedback-ID: 2668248:user:proton X-Pm-Message-ID: cf50c22e5d24cb531139acade969f259fe59bc24 MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Wed, 30 Sep 2026 21:04:19 -0600, shihao zhong wr= ote: > > The following might be relevant as a different perspective: > > > > https://www.postgresql.org/message-id/1122078.1741378100@sss.pgh.pa.us >=20 > Hi John, >=20 > 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. >=20 > 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. >=20 > 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 =3D off; SET enable_bitmapscan =3D off; SELECT count(*) FROM t WHERE g <@ polygon '((NaN,NaN))'; -- 2 SET enable_seqscan =3D 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