Message-ID: <6b3cf869-9a30-1005-11a4-fdbeb939bed2@enterprisedb.com>
Date: Mon, 13 Dec 2021 03:32:18 +0100
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.2.0
Subject: Re: BUG #17334: Assert failed inside computeDistance() on gist index
 scanning
Content-Language: en-US
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: exclusion@gmail.com, pgsql-bugs@lists.postgresql.org
References: <17334-135f485c21739caa@postgresql.org>
 <7c191fd2-9103-ae81-8f4f-22f39edd3c90@enterprisedb.com>
 <746816.1639352241@sss.pgh.pa.us>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
In-Reply-To: <746816.1639352241@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/6b3cf869-9a30-1005-11a4-fdbeb939bed2%40enterprisedb.com>
Precedence: bulk

On 12/13/21 00:37, Tom Lane wrote:
> Tomas Vondra <tomas.vondra@enterprisedb.com> writes:
>> FWIW I can reproduce this on master too. The failure happens because of
>> NaN value in the index:
> 
> Man, what a pain those are.
> 
>> 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.
> 
> Surely omitting the entry from the index would lead to incorrect
> answers.  Without any index, we get
> 
> regression=# CREATE TABLE point_tbl(f1 point);
> CREATE TABLE
> regression=# INSERT INTO point_tbl SELECT ('0,0') FROM generate_series(1, 2);
> INSERT 0 2
> regression=# INSERT INTO point_tbl VALUES ('0,NaN');
> INSERT 0 1
> regression=# SELECT f1, f1 <-> point '(0,0)' AS dist FROM point_tbl ORDER BY dist;
>     f1    | dist
> ---------+------
>   (0,0)   |    0
>   (0,0)   |    0
>   (0,NaN) |  NaN
> (3 rows)
> 
> You can argue about where the NaN distance should sort, but
> not about whether the row should appear at all.
> 
>> 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.
> 
> Yipes, that's even worse, if true. >

Yeah. I haven't looked at the code, but this is what I see:

   test=# create extension btree_gist ;
   CREATE EXTENSION
   test=# CREATE TABLE t(f1 double precision);
   CREATE TABLE
   test=# INSERT INTO t VALUES ('NaN'::float8);
   INSERT 0 1
   test=# CREATE INDEX idx ON t USING gist(f1);
   CREATE INDEX
   test=# select * from gist_page_items(get_raw_page('idx', 0), 
'idx'::regclass);
    itemoffset | ctid  | itemlen | dead |         keys
   ------------+-------+---------+------+-----------------------
             1 | (0,1) |      24 | f    | (f1)=(1.9187051e-316)
   (1 row)


test=# set enable_seqscan = on;
SET
test=# select * from t where f1 = 'NaN'::float8;
  f1
-----
  NaN
(1 row)


test=# set enable_seqscan = off;
SET
test=# select * from t where f1 = 'NaN'::float8;
  f1
----
(0 rows)


So yeah, that seems like an index corruption. Moreover:

test=# INSERT INTO t VALUES (0::float8);
test=# INSERT INTO t VALUES ('NaN'::float8);
test=# INSERT INTO t VALUES (0::float8);

test=# select * from t order by f1 <-> 0;
  f1
-----
  NaN
    0
    0
  NaN
(4 rows)


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company