Return FALSE instead of throwing error for comparisons with empty ranges.

Change range_before, range_after, range_adjacent to return false rather
than throwing an error when one or both input ranges are empty.

The original definition is unnecessarily difficult to use, and also can
result in undesirable planner failures since the planner could try to
compare an empty range to something else while deriving statistical
estimates.  (This was, in fact, the cause of repeatable regression test
failures on buildfarm member jaguar, as well as intermittent failures
elsewhere.)

Also tweak rangetypes regression test to not drop all the objects it
creates, so that the final state of the regression database contains
some rangetype objects for pg_dump testing.

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
 --