aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/backend/utils/adt/rangetypes.c31
-rw-r--r--src/test/regress/expected/rangetypes.out42
-rw-r--r--src/test/regress/expected/sanity_check.out4
-rw-r--r--src/test/regress/output/misc.source4
-rw-r--r--src/test/regress/sql/rangetypes.sql48
5 files changed, 57 insertions, 72 deletions
diff --git a/src/backend/utils/adt/rangetypes.c b/src/backend/utils/adt/rangetypes.c
index f773c31813a..5c16ca1b19e 100644
--- a/src/backend/utils/adt/rangetypes.c
+++ b/src/backend/utils/adt/rangetypes.c
@@ -701,15 +701,11 @@ range_before(PG_FUNCTION_ARGS)
lower1.rngtypid != upper2.rngtypid)
elog(ERROR, "range types do not match");
+ /* An empty range is neither before nor after any other range */
if (empty1 || empty2)
- ereport(ERROR,
- (errcode(ERRCODE_DATA_EXCEPTION),
- errmsg("input range is empty")));
-
- if (range_cmp_bounds(fcinfo, &upper1, &lower2) < 0)
- PG_RETURN_BOOL(true);
- else
PG_RETURN_BOOL(false);
+
+ PG_RETURN_BOOL(range_cmp_bounds(fcinfo, &upper1, &lower2) < 0);
}
Datum
@@ -732,15 +728,11 @@ range_after(PG_FUNCTION_ARGS)
lower1.rngtypid != upper2.rngtypid)
elog(ERROR, "range types do not match");
+ /* An empty range is neither before nor after any other range */
if (empty1 || empty2)
- ereport(ERROR,
- (errcode(ERRCODE_DATA_EXCEPTION),
- errmsg("input range is empty")));
-
- if (range_cmp_bounds(fcinfo, &lower1, &upper2) > 0)
- PG_RETURN_BOOL(true);
- else
PG_RETURN_BOOL(false);
+
+ PG_RETURN_BOOL(range_cmp_bounds(fcinfo, &lower1, &upper2) > 0);
}
Datum
@@ -764,10 +756,9 @@ range_adjacent(PG_FUNCTION_ARGS)
lower1.rngtypid != upper2.rngtypid)
elog(ERROR, "range types do not match");
+ /* An empty range is not adjacent to any other range */
if (empty1 || empty2)
- ereport(ERROR,
- (errcode(ERRCODE_DATA_EXCEPTION),
- errmsg("undefined for empty ranges")));
+ PG_RETURN_BOOL(false);
/*
* For two ranges to be adjacent, the lower boundary of one range has to
@@ -819,6 +810,7 @@ range_overlaps(PG_FUNCTION_ARGS)
lower1.rngtypid != upper2.rngtypid)
elog(ERROR, "range types do not match");
+ /* An empty range does not overlap any other range */
if (empty1 || empty2)
PG_RETURN_BOOL(false);
@@ -853,6 +845,7 @@ range_overleft(PG_FUNCTION_ARGS)
lower1.rngtypid != upper2.rngtypid)
elog(ERROR, "range types do not match");
+ /* An empty range is neither before nor after any other range */
if (empty1 || empty2)
PG_RETURN_BOOL(false);
@@ -882,6 +875,7 @@ range_overright(PG_FUNCTION_ARGS)
lower1.rngtypid != upper2.rngtypid)
elog(ERROR, "range types do not match");
+ /* An empty range is neither before nor after any other range */
if (empty1 || empty2)
PG_RETURN_BOOL(false);
@@ -917,6 +911,7 @@ range_minus(PG_FUNCTION_ARGS)
lower1.rngtypid != upper2.rngtypid)
elog(ERROR, "range types do not match");
+ /* if either is empty, r1 is the correct answer */
if (empty1 || empty2)
PG_RETURN_RANGE(r1);
@@ -971,6 +966,7 @@ range_union(PG_FUNCTION_ARGS)
range_deserialize(fcinfo, r1, &lower1, &upper1, &empty1);
range_deserialize(fcinfo, r2, &lower2, &upper2, &empty2);
+ /* if either is empty, the other is the correct answer */
if (empty1)
PG_RETURN_RANGE(r2);
if (empty2)
@@ -1051,6 +1047,7 @@ range_cmp(PG_FUNCTION_ARGS)
lower1.rngtypid != upper2.rngtypid)
elog(ERROR, "range types do not match");
+ /* For b-tree use, empty ranges sort before all else */
if (empty1 && empty2)
PG_RETURN_INT32(0);
else if (empty1)
diff --git a/src/test/regress/expected/rangetypes.out b/src/test/regress/expected/rangetypes.out
index 3514dece300..9b879486170 100644
--- a/src/test/regress/expected/rangetypes.out
+++ b/src/test/regress/expected/rangetypes.out
@@ -144,7 +144,6 @@ select '(!,[)'::textrange;
(!,"[")
(1 row)
-drop type textrange;
--
-- create some test data and test the operators
--
@@ -529,6 +528,7 @@ select daterange('2000-01-10'::date, '2000-01-20'::date,'[]');
[01-10-2000,01-21-2000)
(1 row)
+-- test GiST index that's been built incrementally
create table test_range_gist(ir int4range);
create index test_range_gist_idx on test_range_gist using gist (ir);
insert into test_range_gist select int4range(g, g+10) from generate_series(1,2000) g;
@@ -538,10 +538,10 @@ insert into test_range_gist select 'empty'::int4range from generate_series(1,500
insert into test_range_gist select int4range(NULL,g*10,'(]') from generate_series(1,100) g;
insert into test_range_gist select int4range(g*10,NULL,'(]') from generate_series(1,100) g;
insert into test_range_gist select int4range(g, g+10) from generate_series(1,2000) g;
-BEGIN;
-SET LOCAL enable_seqscan = t;
-SET LOCAL enable_bitmapscan = f;
-SET LOCAL enable_indexscan = f;
+-- first, verify non-indexed results
+SET enable_seqscan = t;
+SET enable_indexscan = f;
+SET enable_bitmapscan = f;
select count(*) from test_range_gist where ir @> 'empty'::int4range;
count
-------
@@ -578,41 +578,40 @@ select count(*) from test_range_gist where ir <@ int4range(10,50);
1062
(1 row)
-select count(*) from (select * from test_range_gist where not isempty(ir)) s where ir << int4range(100,500);
+select count(*) from test_range_gist where ir << int4range(100,500);
count
-------
189
(1 row)
-select count(*) from (select * from test_range_gist where not isempty(ir)) s where ir >> int4range(100,500);
+select count(*) from test_range_gist where ir >> int4range(100,500);
count
-------
3554
(1 row)
-select count(*) from (select * from test_range_gist where not isempty(ir)) s where ir &< int4range(100,500);
+select count(*) from test_range_gist where ir &< int4range(100,500);
count
-------
1029
(1 row)
-select count(*) from (select * from test_range_gist where not isempty(ir)) s where ir &> int4range(100,500);
+select count(*) from test_range_gist where ir &> int4range(100,500);
count
-------
4794
(1 row)
-select count(*) from (select * from test_range_gist where not isempty(ir)) s where ir -|- int4range(100,500);
+select count(*) from test_range_gist where ir -|- int4range(100,500);
count
-------
5
(1 row)
-COMMIT;
-BEGIN;
-SET LOCAL enable_seqscan = f;
-SET LOCAL enable_bitmapscan = f;
-SET LOCAL enable_indexscan = t;
+-- now check same queries using index
+SET enable_seqscan = f;
+SET enable_indexscan = t;
+SET enable_bitmapscan = f;
select count(*) from test_range_gist where ir @> 'empty'::int4range;
count
-------
@@ -679,13 +678,9 @@ select count(*) from test_range_gist where ir -|- int4range(100,500);
5
(1 row)
-COMMIT;
+-- now check same queries using a bulk-loaded index
drop index test_range_gist_idx;
create index test_range_gist_idx on test_range_gist using gist (ir);
-BEGIN;
-SET LOCAL enable_seqscan = f;
-SET LOCAL enable_bitmapscan = f;
-SET LOCAL enable_indexscan = t;
select count(*) from test_range_gist where ir @> 'empty'::int4range;
count
-------
@@ -752,8 +747,9 @@ select count(*) from test_range_gist where ir -|- int4range(100,500);
5
(1 row)
-COMMIT;
-drop table test_range_gist;
+RESET enable_seqscan;
+RESET enable_indexscan;
+RESET enable_bitmapscan;
--
-- Btree_gist is not included by default, so to test exclusion
-- constraints with range types, use singleton int ranges for the "="
@@ -782,7 +778,6 @@ insert into test_range_excl
values(int4range(125), int4range(1), '[2010-01-02 10:10, 2010-01-02 11:10)');
ERROR: conflicting key value violates exclusion constraint "test_range_excl_speaker_during_excl"
DETAIL: Key (speaker, during)=([1,2), ["Sat Jan 02 10:10:00 2010","Sat Jan 02 11:10:00 2010")) conflicts with existing key (speaker, during)=([1,2), ["Sat Jan 02 10:00:00 2010","Sat Jan 02 11:00:00 2010")).
-drop table test_range_excl;
-- test bigint ranges
select int8range(10000000000::int8, 20000000000::int8,'(]');
int8range
@@ -924,7 +919,6 @@ select arrayrange(ARRAY[1,2], ARRAY[2,1]);
["{1,2}","{2,1}")
(1 row)
-drop type arrayrange;
--
-- OUT/INOUT/TABLE functions
--
diff --git a/src/test/regress/expected/sanity_check.out b/src/test/regress/expected/sanity_check.out
index 38c88d977ad..cb468e58b91 100644
--- a/src/test/regress/expected/sanity_check.out
+++ b/src/test/regress/expected/sanity_check.out
@@ -151,6 +151,8 @@ SELECT relname, relhasindex
student | f
tenk1 | t
tenk2 | t
+ test_range_excl | t
+ test_range_gist | t
test_tsvector | f
text_tbl | f
time_tbl | f
@@ -159,7 +161,7 @@ SELECT relname, relhasindex
timetz_tbl | f
tinterval_tbl | f
varchar_tbl | f
-(148 rows)
+(150 rows)
--
-- another sanity check: every system catalog that has OIDs should have
diff --git a/src/test/regress/output/misc.source b/src/test/regress/output/misc.source
index 34bde31b9f1..45bc926407d 100644
--- a/src/test/regress/output/misc.source
+++ b/src/test/regress/output/misc.source
@@ -670,6 +670,8 @@ SELECT user_relns() AS user_relns
subselect_tbl
tenk1
tenk2
+ test_range_excl
+ test_range_gist
test_tsvector
text_tbl
time_tbl
@@ -680,7 +682,7 @@ SELECT user_relns() AS user_relns
toyemp
varchar_tbl
xacttest
-(102 rows)
+(104 rows)
SELECT name(equipment(hobby_construct(text 'skywalking', text 'mer')));
name
diff --git a/src/test/regress/sql/rangetypes.sql b/src/test/regress/sql/rangetypes.sql
index 44885c870cf..573e85ebb25 100644
--- a/src/test/regress/sql/rangetypes.sql
+++ b/src/test/regress/sql/rangetypes.sql
@@ -35,8 +35,6 @@ select '([,z)'::textrange;
select '(!,()'::textrange;
select '(!,[)'::textrange;
-drop type textrange;
-
--
-- create some test data and test the operators
--
@@ -148,6 +146,7 @@ select daterange('2000-01-10'::date, '2000-01-20'::date,'[)');
select daterange('2000-01-10'::date, '2000-01-20'::date,'(]');
select daterange('2000-01-10'::date, '2000-01-20'::date,'[]');
+-- test GiST index that's been built incrementally
create table test_range_gist(ir int4range);
create index test_range_gist_idx on test_range_gist using gist (ir);
@@ -159,10 +158,10 @@ insert into test_range_gist select int4range(NULL,g*10,'(]') from generate_serie
insert into test_range_gist select int4range(g*10,NULL,'(]') from generate_series(1,100) g;
insert into test_range_gist select int4range(g, g+10) from generate_series(1,2000) g;
-BEGIN;
-SET LOCAL enable_seqscan = t;
-SET LOCAL enable_bitmapscan = f;
-SET LOCAL enable_indexscan = f;
+-- first, verify non-indexed results
+SET enable_seqscan = t;
+SET enable_indexscan = f;
+SET enable_bitmapscan = f;
select count(*) from test_range_gist where ir @> 'empty'::int4range;
select count(*) from test_range_gist where ir = int4range(10,20);
@@ -170,17 +169,16 @@ select count(*) from test_range_gist where ir @> 10;
select count(*) from test_range_gist where ir @> int4range(10,20);
select count(*) from test_range_gist where ir && int4range(10,20);
select count(*) from test_range_gist where ir <@ int4range(10,50);
-select count(*) from (select * from test_range_gist where not isempty(ir)) s where ir << int4range(100,500);
-select count(*) from (select * from test_range_gist where not isempty(ir)) s where ir >> int4range(100,500);
-select count(*) from (select * from test_range_gist where not isempty(ir)) s where ir &< int4range(100,500);
-select count(*) from (select * from test_range_gist where not isempty(ir)) s where ir &> int4range(100,500);
-select count(*) from (select * from test_range_gist where not isempty(ir)) s where ir -|- int4range(100,500);
-COMMIT;
-
-BEGIN;
-SET LOCAL enable_seqscan = f;
-SET LOCAL enable_bitmapscan = f;
-SET LOCAL enable_indexscan = t;
+select count(*) from test_range_gist where ir << int4range(100,500);
+select count(*) from test_range_gist where ir >> int4range(100,500);
+select count(*) from test_range_gist where ir &< int4range(100,500);
+select count(*) from test_range_gist where ir &> int4range(100,500);
+select count(*) from test_range_gist where ir -|- int4range(100,500);
+
+-- now check same queries using index
+SET enable_seqscan = f;
+SET enable_indexscan = t;
+SET enable_bitmapscan = f;
select count(*) from test_range_gist where ir @> 'empty'::int4range;
select count(*) from test_range_gist where ir = int4range(10,20);
@@ -193,16 +191,11 @@ select count(*) from test_range_gist where ir >> int4range(100,500);
select count(*) from test_range_gist where ir &< int4range(100,500);
select count(*) from test_range_gist where ir &> int4range(100,500);
select count(*) from test_range_gist where ir -|- int4range(100,500);
-COMMIT;
+-- now check same queries using a bulk-loaded index
drop index test_range_gist_idx;
create index test_range_gist_idx on test_range_gist using gist (ir);
-BEGIN;
-SET LOCAL enable_seqscan = f;
-SET LOCAL enable_bitmapscan = f;
-SET LOCAL enable_indexscan = t;
-
select count(*) from test_range_gist where ir @> 'empty'::int4range;
select count(*) from test_range_gist where ir = int4range(10,20);
select count(*) from test_range_gist where ir @> 10;
@@ -214,9 +207,10 @@ select count(*) from test_range_gist where ir >> int4range(100,500);
select count(*) from test_range_gist where ir &< int4range(100,500);
select count(*) from test_range_gist where ir &> int4range(100,500);
select count(*) from test_range_gist where ir -|- int4range(100,500);
-COMMIT;
-drop table test_range_gist;
+RESET enable_seqscan;
+RESET enable_indexscan;
+RESET enable_bitmapscan;
--
-- Btree_gist is not included by default, so to test exclusion
@@ -243,8 +237,6 @@ insert into test_range_excl
insert into test_range_excl
values(int4range(125), int4range(1), '[2010-01-02 10:10, 2010-01-02 11:10)');
-drop table test_range_excl;
-
-- test bigint ranges
select int8range(10000000000::int8, 20000000000::int8,'(]');
-- test tstz ranges
@@ -343,8 +335,6 @@ create type arrayrange as range (subtype=int4[]);
select arrayrange(ARRAY[1,2], ARRAY[2,1]);
-drop type arrayrange;
-
--
-- OUT/INOUT/TABLE functions
--
generated by cgit v1.2.3 (git 2.25.1) at 2025-06-21 15:26:09 +0000