Issue with point_ops and NaN

From: Julien Rouhaud <rjuju123(at)gmail(dot)com>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Issue with point_ops and NaN
Date: 2021-03-30 09:57:51
Message-ID: 20210330095751.x5hnqbqcxilzwjlm@nol
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

While running some sanity checks on the regression tests, I found one test that
returns different results depending on whether an index or a sequential scan is
used.

Minimal reproducer:

=# CREATE TABLE point_tbl AS select '(nan,nan)'::point f1;
=# CREATE INDEX ON point_tbl USING gist(f1);

=# EXPLAIN SELECT * FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)';
QUERY PLAN
------------------------------------------------------------------------------
Seq Scan on point_tbl (cost=0.00..1.01 rows=1 width=16)
Filter: (f1 <@ '((0,0),(0,100),(100,100),(50,50),(100,0),(0,0))'::polygon)
(2 rows)

=# SELECT * FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)';
f1
-----------
(NaN,NaN)
(1 row)

SET enable_seqscan = 0;

=# EXPLAIN SELECT * FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)';
QUERY PLAN
----------------------------------------------------------------------------------------
Index Only Scan using point_tbl_f1_idx on point_tbl (cost=0.12..8.14 rows=1 width=16)
Index Cond: (f1 <@ '((0,0),(0,100),(100,100),(50,50),(100,0),(0,0))'::polygon)
(2 rows)

=# SELECT * FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)';
f1
----
(0 rows)

The discrepancy comes from the fact that the sequential scan checks the
condition using point_inside() / lseg_crossing(), while the gist index will
check the condition using box_overlap() / box_ov(), which have different
opinions on how to handle NaN.

Getting a consistent behavior shouldn't be hard, but I'm unsure which behavior
is actually correct.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Bharath Rupireddy 2021-03-30 11:19:49 Re: extra semicolon in postgres_fdw test cases
Previous Message Dean Rasheed 2021-03-30 09:55:12 Re: pgbench - add pseudo-random permutation function