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 1xJjNf-00000000ORv-2XS8 for pgsql-bugs@arkaria.postgresql.org; Thu, 22 Oct 2026 03:22:16 +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 1xJjNe-000000089AO-44nX for pgsql-bugs@arkaria.postgresql.org; Thu, 22 Oct 2026 03:22:14 +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 1xJjNe-000000089AG-2sJX for pgsql-bugs@lists.postgresql.org; Thu, 22 Oct 2026 03:22:14 +0000 Received: from mail-vk1-xa2f.google.com ([2607:f8b0:4864:20::a2f]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1xJjNb-00000000Kip-2b0V for pgsql-bugs@lists.postgresql.org; Thu, 22 Oct 2026 03:22:14 +0000 Received: by mail-vk1-xa2f.google.com with SMTP id 71dfb90a1353d-5c82b4d250eso95078e0c.0 for ; Wed, 21 Oct 2026 20:22:11 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20251104; t=1792639330; x=1793244130; darn=lists.postgresql.org; h=mime-version:content-transfer-encoding:content-type:references :in-reply-to:message-id:date:subject:cc:to:from:from:to:cc:subject :date:message-id:reply-to:content-type; bh=HbLgbKrsyYSuc7GtwVTUcRdfbjGLndGn+hmi7oBcoX4=; b=SYIJOVkpEZI0RQZ/906iLMr8/0LDpD7BlkAHV3/3H/sMQMqTB447X3E+HOmlUKCgG/ 0GuGcBiRNxNg9NUAkfeOsbhjkt1cKS2E0jkDNtRSPRVBuxdF6keWCwb3uJJ1t/4Jj/4y b+838CnRNE4mjCA02EO5o8sm0bp9EngLj++qMVU2s+lR/+wnqFNXgnkyKKOKwhUfs2ZZ WktyqMw3mukyb9ys5p3rlfiYjShFypybv67v803IBVIP0xKgkuEH2WMdO/A0jQdwenNB iezqYZqj2htE7liW5oIfmZB+rXn3YXXkeP9ZxHCZbGVfHd49lUzfQzsyjZ4ugeqoK3Vp RU8Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20260707; t=1792639330; x=1793244130; h=mime-version:content-transfer-encoding:content-type:references :in-reply-to:message-id:date:subject:cc:to:from:x-gm-gg :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to :content-type; bh=HbLgbKrsyYSuc7GtwVTUcRdfbjGLndGn+hmi7oBcoX4=; b=U7E3Hb5V9nE/2U5uHRYbMYU36sIubEk800AzGSZDY1nhYzGa1j96zOLoJ3KcthIRIO 3easZ0+ZoZTC10ofFWposCfxJU7WukhKqMnE7TMc5fHlTQuFYG6CWrVZOwyxtISs5DHl Az551pofUKGejUlhhN6uqAl/5xR5CcPw/32Q6KM1YBZ8Fi8MFr+yR+JUJj4+/6AIIRVF k0VqBRWE4NH+cRpBAxsccTe0jZSSSNr6+pMyzs0abfqUoqDDo7ohbz05vdJsW1rJPuow fQyFzMi9YSS2boSq/KCYO6ZKqwIRjwlEIebQcW1kEvVUvhGsPa8QLNIUL4y+6Wgtuf2J FAyQ== X-Forwarded-Encrypted: i=1; AKwUvBwNJuT1FoqQcPK4Rawy0yCl8SV+HxCV2MYRLoi4E/kEnwYKzW407pfzEfC9285Qp/0NHvDHKCyGBwzl@lists.postgresql.org X-Gm-Message-State: AFq9FYKz7d9GnMAfyLjGeOV2KTnoHulYABy0nYaCqRXd3ELMQnVxSGVT Ur1t89FSCgxTaIWSFmxPSMlwUtHc/R9nwnMb6Jdi14RCefHeWKUatJ46 X-Gm-Gg: AYBFou0qrTMKCbY98LcExh0+5tP4PAGORG7fk5+5ekMb0vW6GI6EaS1ugWzgflNVpx1 lLqrIdx6TeXHqZDkYuCfAZS0HD1OkaNG6HsiIp74pHRvVVdlJ/pP2qZ8+bjlfL/BBwn6Yg80fAl v/dCX9JYASqHiFat3/MaXjN0JL/mRlpcF/OBaqWOImtn0i17eRhCjyI6qEMPpjz3xUf5djSO+zs JEqsgU1pJ+kxVSGW+N2xnV/es8ysdmoiJHLU8Bt1GnK5nC7JuhoklVpxZqmZcSx1qenMWsMCA5b X9rn0JvNKyDcVRp4mTyyu/OfU+tCPdpv31/062eexofHFmVZZUqAB7kvJZzS5VZbYnRHp5FRzFl yuKOvAjlIcYoCysp6fB9Mms3VSN29l56IWZxPtPymy0YWAoaJacEu9Cg48wosHJkaGZvleOctR9 Qjfv0+GIKurW4aezyZLqGYhfEMgueQHkyyHZMDbAoNHAVpBZQWwOBhRnmEy6eNnIt4VlzumdbJr 0H3dc6dD2mJV67+PauEYFry0xCstiWE3r6b6S1ZYLSpo0fGTl0Dm3sZ7sjfMlI1/hCw01U/NsKp n52DEOY3rCyKqTGkeRRjI+aRGuEGoIeyBNFX77P4EYuaAWFQVuiKhamjmIElccY9ebgYVPCBncj c9LkeL3c= X-Received: by 2002:ac5:c4f8:0:b0:5ea:d67f:fd39 with SMTP id 71dfb90a1353d-5eaebe76faamr1265622e0c.5.1792639330185; Wed, 21 Oct 2026 20:22:10 -0700 (PDT) Received: from 192.168.1.109 ([2803:3c90:cee:ea00:5c3c:7e70:29e3:7a55]) by smtp.gmail.com with ESMTPSA id 71dfb90a1353d-5eae98237b0sm5490136e0c.15.2026.10.21.20.22.07 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Wed, 21 Oct 2026 20:22:09 -0700 (PDT) From: Manu To: Greg Burd , shihao zhong Cc: John Naylor , Tomas Vondra , Heikki Linnakangas , Kirill Reshke , Andrey Borodin , 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 Date: Thu, 22 Oct 2026 00:22:00 -0300 Message-ID: <179263932039.1070059.624694400186744253@gmail.com> In-Reply-To: References: Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: 7bit MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hi Greg, shihao, > 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 I traced this one in gdb on master. With a NaN coordinate every test in lseg_crossing() is false, so it always falls through to the last "return 2 * y_sign" (geo_ops.c:5669) and each edge adds -2 to total_cross. point_inside() then reports the point inside. That matches your reading: the seq scan is the side that is off, and the index is the consistent one (box and circle also say false for the same inputs). point_inside() has six callers, so the same answer shows up in point <@ polygon, polygon @> point, point <-> polygon (distance 0) and point <@ closed path. It also depends on which coordinate is NaN: point '(0.5,NaN)' is inside the unit square, point '(NaN,0.5)' is not. > 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 This turns out to be the same bug. That error comes only from the reorder recheck (nodeIndexscan.c:319), and only opclasses whose distance asks for a recheck reach it, so the column has to be a polygon (GiST or SP-GiST poly_ops); GiST point_ops never rechecks. In gdb the index returns NaN as the lower bound for the NaN key, the recheck computes dist_ppoly(), which returns 0 because point_inside() said "inside", and 0 < NaN raises the error. No infinite point is needed: CREATE TABLE p1 (g polygon); INSERT INTO p1 VALUES ('((NaN,NaN))'); CREATE INDEX ON p1 USING gist (g); SET enable_seqscan = off; SELECT g <-> point '(10,10)' FROM p1 ORDER BY g <-> point '(10,10)'; The seq scan returns 0. With more rows and a query point near the data, master stops earlier with "inconsistent point values" in computeDistance(), which is likely why an infinite point was needed to see it. It is also the polygon ordering failure from the v5 matrix. shihao, v6-0004 fixes it for GiST: on v6 every GiST KNN query I tried on polygon, box, circle and point columns matches the seq scan. SP-GiST poly_ops still fails with v6: the same rows in an SP-GiST index raise the error for (0.5,0.5), (1e308,1e308), (Infinity,0), (-Infinity,-Infinity) and (Infinity,Infinity). The same failure accounts for 25 of the 28 SP-GiST poly_ops mismatches the matrix still showed with v6; the other 3 are ~=. One data point on the expected output. On master, create_index.out already shows the case 1 disagreement on point_tbl, which has a (NaN,NaN) row: the seq scan count for SELECT count(*) FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)'; is 5, and the GiST index-only scan of the same query is 4. The matrix and gdb point to point_inside() as the side that is off, so the GiST/SP-GiST answer (4) is the consistent one; v6-0003 brings the index to match the seq scan (5) rather than the other way round. A small guard in point_inside() makes both return 4. As a direction, not a patch, I measured a guard at the top of point_inside() that returns 0 ("not inside") when the point or any polygon vertex has a NaN (about nine lines; I can send the diff). On master alone it fixes both cases, SP-GiST included, and the scalar operators above give false and NaN, the same as box and circle. On top of v6 it takes the differential matrix (6769 checks, seq scan versus index) from 47 mismatches to 13; the 13 left were already in v6's 47 and are the SP-GiST box_ops ordering and ~= cases, unrelated. There were no assertion failures with assertions enabled. To be clear about what it is: a measurement prototype, with no test of its own and no expected files updated. It does not replace v6-0004, which is still needed for NaN internal keys ("inconsistent point values" when the query point is near the data). make check changes in geometry, create_index and, on v6, in 0003's new gist test (7602 rows to 7599), all of them the "NaN is inside" answers that the expected files record today. I can send the diff and the regression diffs if they are useful. That last point is the open question I would leave to you and the committers: the change is user-visible, since today's answer is pinned in the expected output, so it could be master only or back-patched along with the rest of the set. Regards, Manu