Working on the tests for new SP-GiST opclasses for polygons and circles, I've
found a bug in the SP-GiST box_ops (added in 9.6): some operators
(&<, &>, $<|, |&>) have wrong tests in spg_box_quad_inner_consistent().
This obviously leads to incorrect results of a SP-GiST index scan (see tests
in the attached patch, their results were taken from a sequential scan).
--
Nikita Glukhov
Postgres Professional:http://www.postgrespro.com
The Russian Postgres Company
diff --git a/src/backend/utils/adt/geo_spgist.c b/src/backend/utils/adt/geo_spgist.c
index aacb340..446aa38 100644
--- a/src/backend/utils/adt/geo_spgist.c
+++ b/src/backend/utils/adt/geo_spgist.c
@@ -280,74 +280,90 @@ contained4D(RectBox *rect_box, RangeBox *query)
/* Can any range from range_box to be lower than this argument? */
static bool
-lower2D(RangeBox *range_box, Range *query)
+lower2D(RangeBox *range_box, double query)
{
- return FPlt(range_box->left.low, query->low) &&
- FPlt(range_box->right.low, query->low);
+ return FPlt(range_box->left.low, query) &&
+ FPlt(range_box->right.low, query);
+}
+
+/* Can any range from range_box to be lower or equal to this argument? */
+static bool
+lowerEqual2D(RangeBox *range_box, double query)
+{
+ return FPle(range_box->left.low, query) &&
+ FPle(range_box->right.low, query);
}
/* Can any range from range_box to be higher than this argument? */
static bool
-higher2D(RangeBox *range_box, Range *query)
+higher2D(RangeBox *range_box, double query)
+{
+ return FPgt(range_box->left.high, query) &&
+ FPgt(range_box->right.high, query);
+}
+
+/* Can any range from range_box to be higher or equal to this argument? */
+static bool
+higherEqual2D(RangeBox *range_box, double query)
{
- return FPgt(range_box->left.high, query->high) &&
- FPgt(range_box->right.high, query->high);
+ return FPge(range_box->left.high, query) &&
+ FPge(range_box->right.high, query);
}
/* Can any rectangle from rect_box be left of this argument? */
static bool
left4D(RectBox *rect_box, RangeBox *query)
{
- return lower2D(&rect_box->range_box_x, &query->left);
+ return lower2D(&rect_box->range_box_x, query->left.low);
}
/* Can any rectangle from rect_box does not extend the right of this argument? */
static bool
overLeft4D(RectBox *rect_box, RangeBox *query)
{
- return lower2D(&rect_box->range_box_x, &query->right);
+ return lowerEqual2D(&rect_box->range_box_x, query->left.high);
}
/* Can any rectangle from rect_box be right of this argument? */
static bool
right4D(RectBox *rect_box, RangeBox *query)
{
- return higher2D(&rect_box->range_box_x, &query->left);
+ return higher2D(&rect_box->range_box_x, query->left.high);
}
/* Can any rectangle from rect_box does not extend the left of this argument? */
static bool
overRight4D(RectBox *rect_box, RangeBox *query)
{
- return higher2D(&rect_box->range_box_x, &query->right);
+ return higherEqual2D(&rect_box->range_box_x, query->left.low);
}
/* Can any rectangle from rect_box be below of this argument? */
static bool
below4D(RectBox *rect_box, RangeBox *query)
{
- return lower2D(&rect_box->range_box_y, &query->right);
+ return lower2D(&rect_box->range_box_y, query->right.low);
}
/* Can any rectangle from rect_box does not extend above this argument? */
static bool
overBelow4D(RectBox *rect_box, RangeBox *query)
{
- return lower2D(&rect_box->range_box_y, &query->left);
+ return lowerEqual2D(&rect_box->range_box_y, query->right.high);
}
/* Can any rectangle from rect_box be above of this argument? */
static bool
above4D(RectBox *rect_box, RangeBox *query)
{
- return higher2D(&rect_box->range_box_y, &query->right);
+ return higher2D(&rect_box->range_box_y, query->right.high);
}
/* Can any rectangle from rect_box does not extend below of this argument? */
static bool
overAbove4D(RectBox *rect_box, RangeBox *query)
{
- return higher2D(&rect_box->range_box_y, &query->right);
+ return higherEqual2D(&rect_box->range_box_y, query->right.low);
}
/*
diff --git a/src/test/regress/expected/box.out b/src/test/regress/expected/box.out
index 5f8b945..09db8e8 100644
--- a/src/test/regress/expected/box.out
+++ b/src/test/regress/expected/box.out
@@ -455,3 +455,116 @@ EXPLAIN (COSTS OFF) SELECT * FROM box_temp WHERE f1 ~= '(20,20),(40,40)';
RESET enable_seqscan;
DROP INDEX box_spgist;
+--
+-- Test the SP-GiST index with big random data
+--
+CREATE TEMPORARY TABLE quad_box_tbl (id int, b box);
+SELECT setseed(0);
+ setseed
+---------
+
+(1 row)
+
+INSERT INTO quad_box_tbl
+ SELECT i, box(point(x, y), point(x + w, y + h))
+ FROM (SELECT i,
+ random() * 1000 as x, random() * 1000 as y,
+ random() * 20 as w, random() * 20 as h
+ FROM generate_series(1, 10000) AS i) q;
+-- Insert repeating data to test allTheSame
+INSERT INTO quad_box_tbl
+ SELECT i, '((200, 300),(210, 310))'
+ FROM generate_series(10001, 11000) AS i;
+INSERT INTO quad_box_tbl
+ VALUES
+ (11001, NULL),
+ (11002, NULL),
+ (11003, '((-infinity,-infinity),(infinity,infinity))'),
+ (11004, '((-infinity,100),(-infinity,500))'),
+ (11005, '((-infinity,-infinity),(700,infinity))');
+CREATE INDEX quad_box_tbl_idx ON quad_box_tbl USING spgist(b);
+SET enable_seqscan = OFF;
+SET enable_indexscan = ON;
+SET enable_bitmapscan = ON;
+SELECT count(*) FROM quad_box_tbl WHERE b << box '((100,200),(300,500))';
+ count
+-------
+ 891
+(1 row)
+
+SELECT count(*) FROM quad_box_tbl WHERE b &< box '((100,200),(300,500))';
+ count
+-------
+ 3964
+(1 row)
+
+SELECT count(*) FROM quad_box_tbl WHERE b && box '((100,200),(300,500))';
+ count
+-------
+ 1675
+(1 row)
+
+SELECT count(*) FROM quad_box_tbl WHERE b &> box '((100,200),(300,500))';
+ count
+-------
+ 10012
+(1 row)
+
+SELECT count(*) FROM quad_box_tbl WHERE b >> box '((100,200),(300,500))';
+ count
+-------
+ 6946
+(1 row)
+
+SELECT count(*) FROM quad_box_tbl WHERE b >> box '((100,200),(300,500))';
+ count
+-------
+ 6946
+(1 row)
+
+SELECT count(*) FROM quad_box_tbl WHERE b <<| box '((100,200),(300,500))';
+ count
+-------
+ 1902
+(1 row)
+
+SELECT count(*) FROM quad_box_tbl WHERE b &<| box '((100,200),(300,500))';
+ count
+-------
+ 5892
+(1 row)
+
+SELECT count(*) FROM quad_box_tbl WHERE b |&> box '((100,200),(300,500))';
+ count
+-------
+ 9009
+(1 row)
+
+SELECT count(*) FROM quad_box_tbl WHERE b |>> box '((100,200),(300,500))';
+ count
+-------
+ 5004
+(1 row)
+
+SELECT count(*) FROM quad_box_tbl WHERE b @> box '((201,301),(202,303))';
+ count
+-------
+ 1002
+(1 row)
+
+SELECT count(*) FROM quad_box_tbl WHERE b <@ box '((100,200),(300,500))';
+ count
+-------
+ 1592
+(1 row)
+
+SELECT count(*) FROM quad_box_tbl WHERE b ~= box '((200,300),(210,310))';
+ count
+-------
+ 1000
+(1 row)
+
+RESET enable_seqscan;
+RESET enable_indexscan;
+RESET enable_bitmapscan;
+DROP INDEX quad_box_tbl_idx;
diff --git a/src/test/regress/sql/box.sql b/src/test/regress/sql/box.sql
index 128a016..c4364f7 100644
--- a/src/test/regress/sql/box.sql
+++ b/src/test/regress/sql/box.sql
@@ -179,3 +179,56 @@ EXPLAIN (COSTS OFF) SELECT * FROM box_temp WHERE f1 ~= '(20,20),(40,40)';
RESET enable_seqscan;
DROP INDEX box_spgist;
+
+--
+-- Test the SP-GiST index with big random data
+--
+CREATE TEMPORARY TABLE quad_box_tbl (id int, b box);
+
+SELECT setseed(0);
+
+INSERT INTO quad_box_tbl
+ SELECT i, box(point(x, y), point(x + w, y + h))
+ FROM (SELECT i,
+ random() * 1000 as x, random() * 1000 as y,
+ random() * 20 as w, random() * 20 as h
+ FROM generate_series(1, 10000) AS i) q;
+
+-- Insert repeating data to test allTheSame
+INSERT INTO quad_box_tbl
+ SELECT i, '((200, 300),(210, 310))'
+ FROM generate_series(10001, 11000) AS i;
+
+INSERT INTO quad_box_tbl
+ VALUES
+ (11001, NULL),
+ (11002, NULL),
+ (11003, '((-infinity,-infinity),(infinity,infinity))'),
+ (11004, '((-infinity,100),(-infinity,500))'),
+ (11005, '((-infinity,-infinity),(700,infinity))');
+
+CREATE INDEX quad_box_tbl_idx ON quad_box_tbl USING spgist(b);
+
+SET enable_seqscan = OFF;
+SET enable_indexscan = ON;
+SET enable_bitmapscan = ON;
+
+SELECT count(*) FROM quad_box_tbl WHERE b << box '((100,200),(300,500))';
+SELECT count(*) FROM quad_box_tbl WHERE b &< box '((100,200),(300,500))';
+SELECT count(*) FROM quad_box_tbl WHERE b && box '((100,200),(300,500))';
+SELECT count(*) FROM quad_box_tbl WHERE b &> box '((100,200),(300,500))';
+SELECT count(*) FROM quad_box_tbl WHERE b >> box '((100,200),(300,500))';
+SELECT count(*) FROM quad_box_tbl WHERE b >> box '((100,200),(300,500))';
+SELECT count(*) FROM quad_box_tbl WHERE b <<| box '((100,200),(300,500))';
+SELECT count(*) FROM quad_box_tbl WHERE b &<| box '((100,200),(300,500))';
+SELECT count(*) FROM quad_box_tbl WHERE b |&> box '((100,200),(300,500))';
+SELECT count(*) FROM quad_box_tbl WHERE b |>> box '((100,200),(300,500))';
+SELECT count(*) FROM quad_box_tbl WHERE b @> box '((201,301),(202,303))';
+SELECT count(*) FROM quad_box_tbl WHERE b <@ box '((100,200),(300,500))';
+SELECT count(*) FROM quad_box_tbl WHERE b ~= box '((200,300),(210,310))';
+
+RESET enable_seqscan;
+RESET enable_indexscan;
+RESET enable_bitmapscan;
+
+DROP INDEX quad_box_tbl_idx;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
