[postgis-tickets] [PostGIS] #3418: KNN recheck in 9.5 fails with index returned tuples in wrong order when used in function

[PostGIS]

#3418: KNN recheck in 9.5 fails with index returned tuples in wrong order when
used in function
----------------------+---------------------------
  Reporter:  robe     |      Owner:  pramsey
      Type:  defect   |     Status:  new
  Priority:  medium   |  Milestone:  PostGIS 2.4.0
 Component:  postgis  |    Version:  2.2.x
Resolution:           |   Keywords:
----------------------+---------------------------

Comment (by robe):

 Response from Robert Haas in above thread:


 ----

 {{{
 On Fri, Dec 30, 2016 at 12:51 AM, Regina Obe <lr(at)pcorp(dot)us> wrote:
 > I've been trying to troubleshoot the cause of this PostGIS recheck bug
 we
 > have reported by two people so far.  The last test was a nice simple
 > repeatable one that triggered the issue:
 >
 > https://trac.osgeo.org/postgis/ticket/3418
 >
 > from what I have seen this only affects cases where we are doing a
 distance
 > check between two points, which we actually don't need to enable recheck
 for
 > anyway, but trying to disable that seems like just shoving the real
 problem
 > under the covers.

 Agreed.

 > If things are out of order, why isn't just going to was_exact = false
 good
 > enough?
 >
 > I'm not sure if the mistake is in our PostGIS code or something in
 > PostgreSQL recheck logic.
 > If I change the elog(ERROR ...) to a elog(NOTICE, the answers  are
 correct
 > and sort order is right.
 >
 > Under what conditions would cmp return less than 0?  I tried following
 the
 > code in cmp_orderbyvals, but got lost
 > and trying to put elog notices in to see what the distance is returning
 (I
 > probably did it wrong), just ended up crashing by backend.

 cmp would return 0 if the estimated distance returned by the index AM
 were greater than the actual distance.  The estimated distance can be
 less than the actual distance, but it isn't allowed to be more.  See
 gist_bbox_distance for an example of a "lossy" distance calculation,
 and more generally "git show
 35fcb1b3d038a501f3f4c87c05630095abaaadab".

 --
 Robert Haas
 EnterpriseDB: http://www.enterprisedb.com
 The Enterprise PostgreSQL Company

 }}}

 ----

--
Ticket URL: <https://trac.osgeo.org/postgis/ticket/3418#comment:12>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.