On 12/12/21 17:00, PG Bug reporting form wrote:
> The following bug has been logged on the website:
>
> Bug reference: 17334
> Logged by: Alexander Lakhin
> Email address: exclusion@gmail.com
> PostgreSQL version: 14.1
> Operating system: Ubuntu 20.04
> Description:
>
> When executing the following queries:
> CREATE TABLE point_tbl(f1 point);
> CREATE INDEX gpointind ON point_tbl USING gist(f1);
> INSERT INTO point_tbl SELECT ('0,0') FROM generate_series(1, 185);
> INSERT INTO point_tbl VALUES ('0,NaN');
> SET enable_seqscan=off; SELECT f1 <-> point '(0,0)' AS dist FROM point_tbl
> ORDER BY dist;
>
FWIW I can reproduce this on master too. The failure happens because of
NaN value in the index:
#3 0x00000000004d3e72 in computeDistance (isLeaf=false,
box=0x760f5ce13b28, point=0x1f99608) at gistproc.c:1240
1240 Assert(box->low.y <= box->high.y);
(gdb) p box->high
$1 = {x = 0, y = nan(0x8000000000000)}
and indeed, pageinspect says this:
test=# select * from gist_page_items(get_raw_page('gpointind', 2),
'gpointind'::regclass);
itemoffset | ctid | itemlen | dead | keys
------------+---------+---------+------+----------------
1 | (0,94) | 40 | f | (f1)=((0,0))
2 | (0,95) | 40 | f | (f1)=((0,0))
...
92 | (0,185) | 40 | f | (f1)=((0,0))
93 | (1,1) | 40 | f | (f1)=((0,NaN))
(93 rows)
I'm not sure if the issue is in allowing the NaN to be added to the
index, or not handling it correctly during the index scan.
It's interesting btree_gist does not have issues (for NaN in float8
columns). It seems not to store NaN in the index, It seems to replace
them with tiny values, at least according to pageinspect.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
