From: Manu <manuelreyesbravo@gmail.com>
To: shihao zhong <zhong950419@gmail.com>
Cc: Kirill Reshke <reshkekirill@gmail.com>,
 Andrey Borodin <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
Date: Mon, 28 Sep 2026 14:37:32 -0300
Message-ID: <179061705243.332245.12138813025099450814@gmail.com>
In-Reply-To: 
 <CAGRkXqSN7vB_h41_jvsBbDb2sVNW=rmjxpndvL70oCDrYhbh=w@mail.gmail.com>
References: 
 <CAGRkXqSN7vB_h41_jvsBbDb2sVNW=rmjxpndvL70oCDrYhbh=w@mail.gmail.com>
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: 7bit
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/179061705243.332245.12138813025099450814%40gmail.com>
Precedence: bulk

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