Implement operators for checking if the range contains a multirange

We have operators for checking if the multirange contains a range but don't
have the opposite.  This commit improves completeness of the operator set by
adding two new operators: @> (anyrange,anymultirange) and
<@(anymultirange,anyrange).

Catversion is bumped.

2 files changed, 280 insertions, 0 deletions

diff --git a/src/test/regress/expected/multirangetypes.out b/src/test/regress/expected/multirangetypes.out
index 180aa1e8a53..aa7232efc57 100644
--- a/src/test/regress/expected/multirangetypes.out
+++ b/src/test/regress/expected/multirangetypes.out
@@ -979,6 +979,126 @@ select '{(10,20),(30,40),(50,60)}'::nummultirange @> '(52,56)'::numrange;
  t
 (1 row)
 
+SELECT numrange(null,null) @> nummultirange(numrange(1,2));
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT numrange(null,null) @> nummultirange(numrange(null,2));
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT numrange(null,null) @> nummultirange(numrange(2,null));
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT numrange(null,5) @> nummultirange(numrange(null,3));
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT numrange(null,5) @> nummultirange(numrange(null,8));
+ ?column? 
+----------
+ f
+(1 row)
+
+SELECT numrange(5,null) @> nummultirange(numrange(8,null));
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT numrange(5,null) @> nummultirange(numrange(3,null));
+ ?column? 
+----------
+ f
+(1 row)
+
+SELECT numrange(1,5) @> nummultirange(numrange(8,9));
+ ?column? 
+----------
+ f
+(1 row)
+
+SELECT numrange(1,5) @> nummultirange(numrange(3,9));
+ ?column? 
+----------
+ f
+(1 row)
+
+SELECT numrange(1,5) @> nummultirange(numrange(1,4));
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT numrange(1,5) @> nummultirange(numrange(1,5));
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT numrange(1,9) @> nummultirange(numrange(-4,-2), numrange(1,5));
+ ?column? 
+----------
+ f
+(1 row)
+
+SELECT numrange(1,9) @> nummultirange(numrange(1,5), numrange(8,9));
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT numrange(1,9) @> nummultirange(numrange(1,5), numrange(6,9));
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT numrange(1,9) @> nummultirange(numrange(1,5), numrange(6,10));
+ ?column? 
+----------
+ f
+(1 row)
+
+SELECT '{[1,9)}' @> '{[1,5)}'::nummultirange;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '{[1,9)}' @> '{[-4,-2), [1,5)}'::nummultirange;
+ ?column? 
+----------
+ f
+(1 row)
+
+SELECT '{[1,9)}' @> '{[1,5), [8,9)}'::nummultirange;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '{[1,9)}' @> '{[1,5), [6,9)}'::nummultirange;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '{[1,9)}' @> '{[1,5), [6,10)}'::nummultirange;
+ ?column? 
+----------
+ f
+(1 row)
+
 -- is contained by
 SELECT nummultirange() <@ nummultirange();
  ?column? 
@@ -1112,6 +1232,126 @@ SELECT '{[6,7)}' <@ '{[1,5), [6,9)}'::nummultirange;
  t
 (1 row)
 
+SELECT nummultirange(numrange(1,2)) <@ numrange(null,null);
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT nummultirange(numrange(null,2)) <@ numrange(null,null);
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT nummultirange(numrange(2,null)) <@ numrange(null,null);
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT nummultirange(numrange(null,3)) <@ numrange(null,5);
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT nummultirange(numrange(null,8)) <@ numrange(null,5);
+ ?column? 
+----------
+ f
+(1 row)
+
+SELECT nummultirange(numrange(8,null)) <@ numrange(5,null);
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT nummultirange(numrange(3,null)) <@ numrange(5,null);
+ ?column? 
+----------
+ f
+(1 row)
+
+SELECT nummultirange(numrange(8,9)) <@ numrange(1,5);
+ ?column? 
+----------
+ f
+(1 row)
+
+SELECT nummultirange(numrange(3,9)) <@ numrange(1,5);
+ ?column? 
+----------
+ f
+(1 row)
+
+SELECT nummultirange(numrange(1,4)) <@ numrange(1,5);
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT nummultirange(numrange(1,5)) <@ numrange(1,5);
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT nummultirange(numrange(-4,-2), numrange(1,5)) <@ numrange(1,9);
+ ?column? 
+----------
+ f
+(1 row)
+
+SELECT nummultirange(numrange(1,5), numrange(8,9)) <@ numrange(1,9);
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT nummultirange(numrange(1,5), numrange(6,9)) <@ numrange(1,9);
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT nummultirange(numrange(1,5), numrange(6,10)) <@ numrange(1,9);
+ ?column? 
+----------
+ f
+(1 row)
+
+SELECT '{[1,5)}'::nummultirange <@ '{[1,9)}';
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '{[-4,-2), [1,5)}'::nummultirange <@ '{[1,9)}';
+ ?column? 
+----------
+ f
+(1 row)
+
+SELECT '{[1,5), [8,9)}'::nummultirange <@ '{[1,9)}';
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '{[1,5), [6,9)}'::nummultirange <@ '{[1,9)}';
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '{[1,5), [6,10)}'::nummultirange <@ '{[1,9)}';
+ ?column? 
+----------
+ f
+(1 row)
+
 -- overleft
 SELECT 'empty'::numrange &< nummultirange();
  ?column? 
diff --git a/src/test/regress/sql/multirangetypes.sql b/src/test/regress/sql/multirangetypes.sql
index c9f84cf81d4..9d7af404c98 100644
--- a/src/test/regress/sql/multirangetypes.sql
+++ b/src/test/regress/sql/multirangetypes.sql
@@ -188,6 +188,26 @@ SELECT '{[1,5), [8,9)}'::nummultirange @> '{[1,5)}';
 SELECT '{[1,5), [8,9)}'::nummultirange @> '{[6,7)}';
 SELECT '{[1,5), [6,9)}'::nummultirange @> '{[6,7)}';
 select '{(10,20),(30,40),(50,60)}'::nummultirange @> '(52,56)'::numrange;
+SELECT numrange(null,null) @> nummultirange(numrange(1,2));
+SELECT numrange(null,null) @> nummultirange(numrange(null,2));
+SELECT numrange(null,null) @> nummultirange(numrange(2,null));
+SELECT numrange(null,5) @> nummultirange(numrange(null,3));
+SELECT numrange(null,5) @> nummultirange(numrange(null,8));
+SELECT numrange(5,null) @> nummultirange(numrange(8,null));
+SELECT numrange(5,null) @> nummultirange(numrange(3,null));
+SELECT numrange(1,5) @> nummultirange(numrange(8,9));
+SELECT numrange(1,5) @> nummultirange(numrange(3,9));
+SELECT numrange(1,5) @> nummultirange(numrange(1,4));
+SELECT numrange(1,5) @> nummultirange(numrange(1,5));
+SELECT numrange(1,9) @> nummultirange(numrange(-4,-2), numrange(1,5));
+SELECT numrange(1,9) @> nummultirange(numrange(1,5), numrange(8,9));
+SELECT numrange(1,9) @> nummultirange(numrange(1,5), numrange(6,9));
+SELECT numrange(1,9) @> nummultirange(numrange(1,5), numrange(6,10));
+SELECT '{[1,9)}' @> '{[1,5)}'::nummultirange;
+SELECT '{[1,9)}' @> '{[-4,-2), [1,5)}'::nummultirange;
+SELECT '{[1,9)}' @> '{[1,5), [8,9)}'::nummultirange;
+SELECT '{[1,9)}' @> '{[1,5), [6,9)}'::nummultirange;
+SELECT '{[1,9)}' @> '{[1,5), [6,10)}'::nummultirange;
 
 -- is contained by
 SELECT nummultirange() <@ nummultirange();
@@ -212,6 +232,26 @@ SELECT '{[1,5)}' <@ '{[-4,-2), [1,5)}'::nummultirange;
 SELECT '{[1,5)}' <@ '{[1,5), [8,9)}'::nummultirange;
 SELECT '{[6,7)}' <@ '{[1,5), [8,9)}'::nummultirange;
 SELECT '{[6,7)}' <@ '{[1,5), [6,9)}'::nummultirange;
+SELECT nummultirange(numrange(1,2)) <@ numrange(null,null);
+SELECT nummultirange(numrange(null,2)) <@ numrange(null,null);
+SELECT nummultirange(numrange(2,null)) <@ numrange(null,null);
+SELECT nummultirange(numrange(null,3)) <@ numrange(null,5);
+SELECT nummultirange(numrange(null,8)) <@ numrange(null,5);
+SELECT nummultirange(numrange(8,null)) <@ numrange(5,null);
+SELECT nummultirange(numrange(3,null)) <@ numrange(5,null);
+SELECT nummultirange(numrange(8,9)) <@ numrange(1,5);
+SELECT nummultirange(numrange(3,9)) <@ numrange(1,5);
+SELECT nummultirange(numrange(1,4)) <@ numrange(1,5);
+SELECT nummultirange(numrange(1,5)) <@ numrange(1,5);
+SELECT nummultirange(numrange(-4,-2), numrange(1,5)) <@ numrange(1,9);
+SELECT nummultirange(numrange(1,5), numrange(8,9)) <@ numrange(1,9);
+SELECT nummultirange(numrange(1,5), numrange(6,9)) <@ numrange(1,9);
+SELECT nummultirange(numrange(1,5), numrange(6,10)) <@ numrange(1,9);
+SELECT '{[1,5)}'::nummultirange <@ '{[1,9)}';
+SELECT '{[-4,-2), [1,5)}'::nummultirange <@ '{[1,9)}';
+SELECT '{[1,5), [8,9)}'::nummultirange <@ '{[1,9)}';
+SELECT '{[1,5), [6,9)}'::nummultirange <@ '{[1,9)}';
+SELECT '{[1,5), [6,10)}'::nummultirange <@ '{[1,9)}';
 
 -- overleft
 SELECT 'empty'::numrange &< nummultirange();