[postgis-tickets] r15285 - KNN recheck in 9.5+ fails with index returned tuples in wrong order

[Regina Obe]

Author: robe
Date: 2017-01-07 00:52:17 -0800 (Sat, 07 Jan 2017)
New Revision: 15285

Modified:
   branches/2.3/NEWS
   branches/2.3/postgis/gserialized_gist_2d.c
   branches/2.3/regress/knn_recheck.sql
   branches/2.3/regress/knn_recheck_expected
Log:
KNN recheck in 9.5+ fails with index returned tuples in wrong order
references #3418 for PostGIS 2.3

Modified: branches/2.3/NEWS
===================================================================
--- branches/2.3/NEWS	2017-01-07 08:45:33 UTC (rev 15284)
+++ branches/2.3/NEWS	2017-01-07 08:52:17 UTC (rev 15285)
@@ -3,6 +3,7 @@
 
   * Bug Fixes
   
+  - #3418, KNN recheck in 9.5+ fails with index returned tuples in wrong order
   - #3675, Relationship functions not using an index in some cases
   - #3680, PostGIS upgrade scripts missing GRANT for views
 

Modified: branches/2.3/postgis/gserialized_gist_2d.c
===================================================================
--- branches/2.3/postgis/gserialized_gist_2d.c	2017-01-07 08:45:33 UTC (rev 15284)
+++ branches/2.3/postgis/gserialized_gist_2d.c	2017-01-07 08:52:17 UTC (rev 15285)
@@ -396,7 +396,6 @@
 
 /**
 * Calculate the centroid->centroid distance between the boxes.
-* We return the square distance to avoid a call to sqrt.
 */
 static double box2df_distance_leaf_centroid(const BOX2DF *a, const BOX2DF *b)
 {
@@ -407,7 +406,6 @@
     double b_y = (b->ymax + b->ymin) / 2.0;
 
     /* This "distance" is only used for comparisons, */
-    /* so for speed we drop contants and skip the sqrt step. */
     return sqrt((a_x - b_x) * (a_x - b_x) + (a_y - b_y) * (a_y - b_y));
 }
 
@@ -499,27 +497,27 @@
 */
 static double box2df_distance(const BOX2DF *a, const BOX2DF *b)
 {
-    /* Check for overlap */
-    if ( box2df_overlaps(a, b) )
-        return 0.0;
+	/* Check for overlap */
+	if ( box2df_overlaps(a, b) )
+		return 0.0;
 
-    if ( box2df_left(a, b) )
-    {
-        if ( box2df_above(a, b) )
+	if ( box2df_left(a, b) )
+	{
+		if ( box2df_above(a, b) )
 			return pt_distance(a->xmax, a->ymin, b->xmin, b->ymax);
 		if ( box2df_below(a, b) )
 			return pt_distance(a->xmax, a->ymax, b->xmin, b->ymin);
 		else
-			return b->xmin - a->xmax;
+			return (double)b->xmin - (double)a->xmax;
 	}
 	if ( box2df_right(a, b) )
 	{
-        if ( box2df_above(a, b) )
+		if ( box2df_above(a, b) )
 			return pt_distance(a->xmin, a->ymin, b->xmax, b->ymax);
 		if ( box2df_below(a, b) )
 			return pt_distance(a->xmin, a->ymax, b->xmax, b->ymin);
 		else
-			return a->xmin - b->xmax;
+			return (double)a->xmin - (double)b->xmax;
 	}
 	if ( box2df_above(a, b) )
 	{
@@ -528,7 +526,7 @@
 		if ( box2df_right(a, b) )
 			return pt_distance(a->xmin, a->ymin, b->xmax, b->ymax);
 		else
-			return a->ymin - b->ymax;
+			return (double)a->ymin - (double)b->ymax;
 	}
 	if ( box2df_below(a, b) )
 	{
@@ -537,9 +535,9 @@
 		if ( box2df_right(a, b) )
 			return pt_distance(a->xmin, a->ymax, b->xmax, b->ymin);
 		else
-			return b->ymin - a->ymax;
+			return (double)b->ymin - (double)a->ymax;
 	}
-	
+
 	return FLT_MAX;
 }
 
@@ -1202,7 +1200,7 @@
 	/* Box-style distance test */
 	if ( strategy == 14 ) /* operator <#> */
 	{
-		distance = (double)box2df_distance(entry_box, &query_box);
+		distance = box2df_distance(entry_box, &query_box);
 	}
 	/* True distance test (formerly centroid distance) */
 	else if ( strategy == 13 ) /* operator <-> */
@@ -1210,7 +1208,7 @@
 		/* In all cases, since we only have keys (boxes) we'll return */
 		/* the minimum possible distance, which is the box2df_distance */
 		/* and let the recheck sort things out in the case of leaves */
-		distance = (double)box2df_distance(entry_box, &query_box);
+		distance = box2df_distance(entry_box, &query_box);
 
 		if (GIST_LEAF(entry))
 			*recheck = true;

Modified: branches/2.3/regress/knn_recheck.sql
===================================================================
--- branches/2.3/regress/knn_recheck.sql	2017-01-07 08:45:33 UTC (rev 15284)
+++ branches/2.3/regress/knn_recheck.sql	2017-01-07 08:52:17 UTC (rev 15285)
@@ -223,3 +223,16 @@
 -- #3573
 SELECT '#3573', 'POINT M (0 0 13)'::geometry <<->> 'LINESTRING M (0 0 5, 0 1 6)'::geometry;
 
+-- #3418
+CREATE TABLE test_wo (geo geometry);
+INSERT INTO test_wo VALUES 
+  ('0101000020E61000007D91D0967329E4BF6631B1F9B8D64A40'::geometry), 
+  ('0101000020E6100000E2AFC91AF510C1BFCDCCCCCCCCAC4A40'::geometry);
+CREATE INDEX ON TEST_WO USING GIST (GEO);
+analyze test_wo;
+SET enable_seqscan = false;
+SELECT '#3418' As ticket, '0101000020E610000092054CE0D6DDE5BFCDCCCCCCCCAC4A40'::geometry <-> geo, ST_Distance('0101000020E610000092054CE0D6DDE5BFCDCCCCCCCCAC4A40'::geometry, geo) 
+FROM test_wo ORDER BY geo <->
+('0101000020E610000092054CE0D6DDE5BFCDCCCCCCCCAC4A40'::geometry);
+DROP TABLE test_wo;
+set enable_seqscan to default;

Modified: branches/2.3/regress/knn_recheck_expected
===================================================================
--- branches/2.3/regress/knn_recheck_expected	2017-01-07 08:45:33 UTC (rev 15284)
+++ branches/2.3/regress/knn_recheck_expected	2017-01-07 08:52:17 UTC (rev 15285)
@@ -125,3 +125,5 @@
 #3nd-3|600001|9749|54.5453|54.5453
 #3nd-3|600001|10041|54.6233|54.6233
 #3573|8
+#3418|0.331823813642119|0.331823813642119
+#3418|0.55|0.55