On Tue, Sep 29, 2026 at 9:59 PM Ayush Tiwari
<ayushtiwari.slg01@gmail.com> wrote:
> Thanks a lot for the review, John!
>
> Attaching v3 with updated test case and earlier fuzzy comparisons.
In the attached v4, 0001 is like v3 but with a smaller test case with
non-huge numbers (back to using a separate insert):
create table spgist_quad_fp_tbl(p point);
insert into spgist_quad_fp_tbl select point(i, 5) from
generate_series(1, 300) i;
create index spgist_quad_fp_idx on spgist_quad_fp_tbl using spgist(p);
insert into spgist_quad_fp_tbl values (point(3, 5 + 1e-6::float8));
Unfortunately, we're not out of the woods yet. With v3, it's possible
to create indexes that were not possible before, but we can still get
the wrong answer when searching. Some LLM-assisted review and fuzzing
found this test case:
-- same table as above
insert into spgist_quad_fp_tbl values (point(114 - 5e-7, 5 + 1e-6::float8));
set enable_indexscan = off;
select count(*) from spgist_quad_fp_tbl
where p <@ box(point(113.999999, 5.0000004), point(113.9999999, 5.0000016));
count
-------
1
(1 row)
reset enable_indexscan;
set enable_seqscan = off;
select count(*) from spgist_quad_fp_tbl
where p <@ box(point(113.999999, 5.0000004), point(113.9999999, 5.0000016));
count
-------
0
(1 row)
reset all;
v4 0002 fixes this case (note for those following along: needs a
reindex between 0001 and 0002 for it to give the right answer). I've
left 0002 how Claude wrote it for now, but it can probably just be
squashed into 0001. I'm not yet sure if this series fixes all the bugs
in this path, so I'll hold off on polishing and committing just yet.
I may have found 3 other possible bugs involving SP-Gist in the course
of investigating this, so I will share those in separate threads when
I return to this topic.
--
John Naylor
Amazon Web Services
