On 31.01.2017 13:04, Kyotaro HORIGUCHI wrote:
The following comment,
/* Can any range from range_box to be overlower than this argument? */
This might be better to be using the same wording to its users,
for example the comment for overLeft4D is the following.
| /* Can any rectangle from rect_box does not extend the right of this
argument? */
And the documentation uses the following sentence,
https://www.postgresql.org/docs/current/static/functions-geometry.html
| Does not extend to the right of?
So I think "Can any range from range_box does not extend the
upper of this argument?" is better than the overlower. What do
you think?
I think "does not extend the upper" is better than unclear "overlower" too.
So I've corrected this comment in the attached v03 patch.
I confirmed other functions in geo_spgist but found no bugs like this.
I found no bugs in other functions too.
The minimal bad example for the '&<' case is the following.
=# create table t (b box);
=# create index on t using spgist(b);
=# insert into t values ('((215, 305),(210,300))'::box);
=# select * from t where b &< '((100,200),(300,400))'::box;
b
---------------------
(215,305),(210,300)
(1 row)
=# set enable_seqscan to false;
=# select * from t where b &< '((100,200),(300,400))'::box;
b
---
(0 rows)
I don't know how you were able to reproduce this bug in
spg_box_quad_inner_consistent()
using only one box because index tree must have at least one inner node (or 157
boxes)
in order to spg_box_quad_inner_consistent() began to be called. That's why
existing
tests for box_ops didn't detect this bug: box_temp table has only 56 rows.
--
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..c95ec1c 100644
--- a/src/backend/utils/adt/geo_spgist.c
+++ b/src/backend/utils/adt/geo_spgist.c
@@ -286,6 +286,14 @@ lower2D(RangeBox *range_box, Range *query)
FPlt(range_box->right.low, query->low);
}
+/* Can any range from range_box does not extend higher than this argument? */
+static bool
+overLower2D(RangeBox *range_box, Range *query)
+{
+ return FPle(range_box->left.low, query->high) &&
+ FPle(range_box->right.low, query->high);
+}
+
/* Can any range from range_box to be higher than this argument? */
static bool
higher2D(RangeBox *range_box, Range *query)
@@ -294,6 +302,14 @@ higher2D(RangeBox *range_box, Range *query)
FPgt(range_box->right.high, query->high);
}
+/* Can any range from range_box does not extend lower than this argument? */
+static bool
+overHigher2D(RangeBox *range_box, Range *query)
+{
+ return FPge(range_box->left.high, query->low) &&
+ FPge(range_box->right.high, query->low);
+}
+
/* Can any rectangle from rect_box be left of this argument? */
static bool
left4D(RectBox *rect_box, RangeBox *query)
@@ -305,7 +321,7 @@ left4D(RectBox *rect_box, RangeBox *query)
static bool
overLeft4D(RectBox *rect_box, RangeBox *query)
{
- return lower2D(&rect_box->range_box_x, &query->right);
+ return overLower2D(&rect_box->range_box_x, &query->left);
}
/* Can any rectangle from rect_box be right of this argument? */
@@ -319,7 +335,7 @@ right4D(RectBox *rect_box, RangeBox *query)
static bool
overRight4D(RectBox *rect_box, RangeBox *query)
{
- return higher2D(&rect_box->range_box_x, &query->right);
+ return overHigher2D(&rect_box->range_box_x, &query->left);
}
/* Can any rectangle from rect_box be below of this argument? */
@@ -333,7 +349,7 @@ below4D(RectBox *rect_box, RangeBox *query)
static bool
overBelow4D(RectBox *rect_box, RangeBox *query)
{
- return lower2D(&rect_box->range_box_y, &query->left);
+ return overLower2D(&rect_box->range_box_y, &query->right);
}
/* Can any rectangle from rect_box be above of this argument? */
@@ -347,7 +363,7 @@ above4D(RectBox *rect_box, RangeBox *query)
static bool
overAbove4D(RectBox *rect_box, RangeBox *query)
{
- return higher2D(&rect_box->range_box_y, &query->right);
+ return overHigher2D(&rect_box->range_box_y, &query->right);
}
/*
diff --git a/src/test/regress/expected/box.out b/src/test/regress/expected/box.out
index 5f8b945..251df93 100644
--- a/src/test/regress/expected/box.out
+++ b/src/test/regress/expected/box.out
@@ -455,3 +455,108 @@ 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 on the larger volume of data
+--
+CREATE TEMPORARY TABLE quad_box_tbl (b box);
+INSERT INTO quad_box_tbl
+ SELECT box(point(x * 10, y * 10), point(x * 10 + 5, y * 10 + 5))
+ FROM generate_series(1, 100) x,
+ generate_series(1, 100) y;
+-- insert repeating data to test allTheSame
+INSERT INTO quad_box_tbl
+ SELECT '((200, 300),(210, 310))'
+ FROM generate_series(1, 1000);
+INSERT INTO quad_box_tbl
+ VALUES
+ (NULL),
+ (NULL),
+ ('((-infinity,-infinity),(infinity,infinity))'),
+ ('((-infinity,100),(-infinity,500))'),
+ ('((-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
+-------
+ 901
+(1 row)
+
+SELECT count(*) FROM quad_box_tbl WHERE b &< box '((100,200),(300,500))';
+ count
+-------
+ 3901
+(1 row)
+
+SELECT count(*) FROM quad_box_tbl WHERE b && box '((100,200),(300,500))';
+ count
+-------
+ 1653
+(1 row)
+
+SELECT count(*) FROM quad_box_tbl WHERE b &> box '((100,200),(300,500))';
+ count
+-------
+ 10100
+(1 row)
+
+SELECT count(*) FROM quad_box_tbl WHERE b >> box '((100,200),(300,500))';
+ count
+-------
+ 7000
+(1 row)
+
+SELECT count(*) FROM quad_box_tbl WHERE b >> box '((100,200),(300,500))';
+ count
+-------
+ 7000
+(1 row)
+
+SELECT count(*) FROM quad_box_tbl WHERE b <<| box '((100,200),(300,500))';
+ count
+-------
+ 1900
+(1 row)
+
+SELECT count(*) FROM quad_box_tbl WHERE b &<| box '((100,200),(300,500))';
+ count
+-------
+ 5901
+(1 row)
+
+SELECT count(*) FROM quad_box_tbl WHERE b |&> box '((100,200),(300,500))';
+ count
+-------
+ 9100
+(1 row)
+
+SELECT count(*) FROM quad_box_tbl WHERE b |>> box '((100,200),(300,500))';
+ count
+-------
+ 5000
+(1 row)
+
+SELECT count(*) FROM quad_box_tbl WHERE b @> box '((201,301),(202,303))';
+ count
+-------
+ 1003
+(1 row)
+
+SELECT count(*) FROM quad_box_tbl WHERE b <@ box '((100,200),(300,500))';
+ count
+-------
+ 1600
+(1 row)
+
+SELECT count(*) FROM quad_box_tbl WHERE b ~= box '((200,300),(205,305))';
+ count
+-------
+ 1
+(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..37f05cb 100644
--- a/src/test/regress/sql/box.sql
+++ b/src/test/regress/sql/box.sql
@@ -179,3 +179,52 @@ 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 on the larger volume of data
+--
+CREATE TEMPORARY TABLE quad_box_tbl (b box);
+
+INSERT INTO quad_box_tbl
+ SELECT box(point(x * 10, y * 10), point(x * 10 + 5, y * 10 + 5))
+ FROM generate_series(1, 100) x,
+ generate_series(1, 100) y;
+
+-- insert repeating data to test allTheSame
+INSERT INTO quad_box_tbl
+ SELECT '((200, 300),(210, 310))'
+ FROM generate_series(1, 1000);
+
+INSERT INTO quad_box_tbl
+ VALUES
+ (NULL),
+ (NULL),
+ ('((-infinity,-infinity),(infinity,infinity))'),
+ ('((-infinity,100),(-infinity,500))'),
+ ('((-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),(205,305))';
+
+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
