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.
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 |