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

Hi Shihao,

I ran v6 (0001 through 0004) through the same differential matrix as
v5, on master (a5447a2deac) and REL_18_STABLE, built without
assertions. 6769 checks per build: seq scan versus index for every
operator the box, point, polygon and circle opclasses list, with NaN
rows first, last, alone and scattered.

> With your script, GiST has 9 mismatches left on master, all point <@
> polygon with a NaN vertex.

Confirmed. On master, v6 takes BRIN box_inclusion_ops from 79
mismatches to 0, GiST box_ops from 85 to 0, GiST circle_ops from 60 to
0, and GiST poly_ops from 71 to 0. GiST point_ops goes from 162 to 9,
and those 9 are exactly the point <@ polygon case with a NaN vertex in
the polygon: the seq scan returns about 2025 rows and the index 0. The
SP-GiST box_ops and poly_ops counts (10 and 28) are unchanged, as
expected; they are the separate matter from earlier in the thread.

> v6-0004 gives distance 0 to keys and query points with a NaN, as 0002
> already does for internal keys.

0004 also clears the KNN failure I reported. On master,

CREATE TABLE b (v box);
INSERT INTO b VALUES ('(1,NaN),(0,0)');
INSERT INTO b SELECT box(point(x, y), point(x + 1, y + 1))
FROM generate_series(0, 44) x, generate_series(0, 44) y;
CREATE INDEX ON b USING gist (v);
SET enable_seqscan = off;
SELECT v <-> point '(0.5,0.5)' FROM b
ORDER BY v <-> point '(0.5,0.5)' LIMIT 3;

fails the assertion box->low.y <= box->high.y in computeDistance() (and
raises "inconsistent point values" without assertions). With 0004 the
index returns 0, 0.5, 0.5, the same as the seq scan, with no assertion
and no error.

On REL_18 the BRIN backport (v6-REL_18-0001) also takes BRIN
box_inclusion_ops from 79 to 0. 0002 does not apply there, the
1b105f9472b context you mentioned, so I tested only the BRIN change on
that branch; the GiST counts stay at the master control numbers.

That leaves the point <@ polygon NaN-vertex case as the only mismatch
on master. I am happy to keep it out of scope for this set if you
would rather handle a NaN in the query polygon separately.

Regards,
Manu