[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.2.3
 Component:  postgis  |    Version:  2.2.x
Resolution:           |   Keywords:
----------------------+---------------------------

Comment (by robe):

 still an issue in 2.3.  We might need to push this to 2.2.4 unless pramsey
 has another hammer lying around like he had at r13588.

 I created a version for geography and geography doesn't exhibit the same
 error.
 I know I have fewer records here, so I retested geometry with my new
 smaller


 {{{
 generate_series(-100,100, 9) AS x CROSS JOIN generate_series(-175,100,9)
 }}}

 and was still able to trigger the error.

 {{{
 DROP TABLE IF EXISTS knn_recheck_geog ;
 CREATE TABLE knn_recheck_geog(gid serial primary key, geom geography);
 INSERT INTO knn_recheck_geog(gid,geom)
 SELECT ROW_NUMBER() OVER(ORDER BY x,y) AS gid,
 ST_Point(x*0.777,y*0.887)::geography As geom
 FROM generate_series(-100,100, 9) AS x CROSS JOIN
 generate_series(-175,100,9) As y;

 DROP FUNCTION IF EXISTS zz_2nn_angle(geometry);
 CREATE OR REPLACE FUNCTION zz_2nn_angle(geography) RETURNS float AS $$
 -- Here are my wonderful points to KNN search:
 WITH index_query AS (

         SELECT edge.geom AS geom
         FROM (SELECT * FROM knn_recheck_geog) AS edge
 -- This is my query point
         ORDER BY $1
                 <->
         edge.geom LIMIT 2
         ),
 templine AS (
         SELECT ST_MakeLine(geom::geometry)::geography AS geom FROM
 index_query
 ),
 angle1 AS (
         SELECT ST_Azimuth(ST_StartPoint(geom::geometry)::geography, $1)
 angle FROM templine
 ),
 angle2 AS (
         SELECT ST_Azimuth(ST_EndPoint(geom::geometry)::Geography, $1)
 angle FROM templine
 )
 SELECT a1.angle - a2.angle FROM angle1 a1, angle2 a2
 $$ LANGUAGE SQL;

 CREATE INDEX idx_knn_recheck_geog on knn_recheck_geog using gist(geom);

 set enable_seqscan = true;
 set enable_indexscan = true;
 SELECT zz_2nn_angle(geom)
 FROM knn_recheck_geog;

 }}}

--
Ticket URL: <https://trac.osgeo.org/postgis/ticket/3418#comment:5>
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.