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

On Thu, 24 Sept 2026 at 07:44, shihao zhong <zhong950419(at)gmail(dot)com> wrote:
>
> Hi Kirill,
>
> Agreed on all four. v4 attached, split into BRIN and GiST.
>
> 0001 is BRIN, master only. bound_box() is untouched. The consistent
> function also scans a range whose union is not mergeable with itself,
> so old NaN summaries work without REINDEX. Needs a catversion bump.
>
> 0002 is GiST, back to 14. Old internal keys with a NaN match every
> search and get KNN distance zero. Without the distance part, KNN on old
> indexes returned rows out of order. 0002 also fixes point ~= with a NaN
> query, which lost rows even on a fresh index. The leaf check used
> FPeq(), but point_eq() compares exactly when there is a NaN.
>
> The .nocfbot file is BRIN for the back branches, against REL_18. It
> needs no catalog change. Consistent scans a range whose union does not
> contain itself, using the existing contains support function. A NaN box
> fails that, and any sane union passes. Nothing on disk changes, so old
> indexes work without REINDEX after a minor upgrade. The code applies to
> 14 and later, the test hunk needs a small rebase on 14 to 16. This check
> would also work on master, if we want one fix everywhere.
>
> I checked 0001 and 0002 with indexes built by unpatched master, then
> pg_upgraded, no REINDEX. The back branch patch got the same check on
> REL_18, with a minor upgrade.
>
> Thanks,
> Shihao

Hi! v4 good.

I have tested point fix for ~= and it survives an index built by the
old master binary. The REL_18 backport looks good to me.

I see your changes to KNN search:

```
* classification logic to work.
@@ -1465,9 +1552,13 @@ gist_point_distance(PG_FUNCTION_ARGS)
switch (strategyGroup)
{
case PointStrategyNumberGroup:
- distance = computeDistance(GIST_LEAF(entry),
- DatumGetBoxP(entry->key),
- PG_GETARG_POINT_P(1));
+ /* A NaN internal key tells us nothing, see box_has_nan() */
+ if (nan_internal_key(entry))
+ distance = 0.0;
+ else
+ distance = computeDistance(GIST_LEAF(entry),
+ DatumGetBoxP(entry->key),
+ PG_GETARG_POINT_P(1));
break;
default:
```

But I cannot reproduce any wrong results with unpatched binary for
KNN. Something that I tried:

CREATE TABLE knn (p point);
INSERT INTO knn SELECT point(0.001*i, 0.001*i)
FROM generate_series(1,1000) i;
INSERT INTO knn VALUES ('(NaN,NaN)');
INSERT INTO knn SELECT point(50+0.001*i, 50+0.001*i)
FROM generate_series(1,1000) i;
CREATE INDEX knn_idx ON knn USING gist (p);

SET enable_seqscan = off;
SELECT count(*) FROM (SELECT 1 FROM knn
ORDER BY p <-> point '(0,0)' LIMIT 2200) s; -- 2001
SELECT count(*) FROM (SELECT p <-> point '(0,0)' d FROM knn
ORDER BY p <-> point '(0,0)' LIMIT 2200) s
WHERE d::text = 'NaN'; -- 1

and this works without v4 (and with).

Another case (from regress tests):

```
reshke=# CREATE TABLE POINT_TBL(f1 point);

INSERT INTO POINT_TBL(f1) VALUES
('(0.0,0.0)'),
('(-10.0,0.0)'),
('(-3.0,4.0)'),
('(5.1, 34.5)'),
('(-5.0,-12.0)'),
('(1e-300,-1e-300)'), -- To underflow
('(1e+300,Inf)'), -- To overflow
('(Inf,1e+300)'), -- Transposed
(' ( Nan , NaN ) '),
('10.0,10.0');
-- We intentionally don't vacuum point_tbl here; geometry depends on that

reshke=#
SELECT * FROM point_tbl WHERE f1 <@ polygon
'(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)';
f1
------------------
(1e-300,-1e-300)
(0,0)
(10,10)
(5.1,34.5)
(4 rows)

reshke=# set enable_seqscan to true;
SET
reshke=#
SELECT * FROM point_tbl WHERE f1 <@ polygon
'(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)';
f1
------------------
(0,0)
(5.1,34.5)
(1e-300,-1e-300)
(NaN,NaN)
(10,10)
(5 rows)
```

So, v4 keeps point <@ polygon and point <@ circle losing NaN points
that the heap returns. I added simple NaN check to
CircleStrategyNumberGroup/PolygonStrategyNumberGroup cases.

So, attaching v4 as v5 (without changes) and v5-0003 for fix the latter issue

--
Best regards,
Kirill Reshke