On Fri, 2023-10-20 at 16:24 +0800, jian he wrote:
> [new patch]
Thanks, that patch works as expected and passes regression tests.
Some comments about the code:
> --- a/src/backend/utils/adt/rangetypes.c
> +++ b/src/backend/utils/adt/rangetypes.c
> @@ -558,7 +570,6 @@ elem_contained_by_range(PG_FUNCTION_ARGS)
> PG_RETURN_BOOL(range_contains_elem_internal(typcache, r, val));
> }
>
> -
> /* range, range -> bool functions */
>
> /* equality (internal version) */
Please don't change unrelated whitespace.
> +static Node *
> +find_simplified_clause(Const *rangeConst, Expr *otherExpr)
> +{
> + Form_pg_range pg_range;
> + HeapTuple tup;
> + Oid opclassOid;
> + RangeBound lower;
> + RangeBound upper;
> + bool empty;
> + Oid rng_collation;
> + TypeCacheEntry *elemTypcache;
> + Oid opfamily = InvalidOid;
> +
> + RangeType *range = DatumGetRangeTypeP(rangeConst->constvalue);
> + TypeCacheEntry *rangetypcache =
> lookup_type_cache(RangeTypeGetOid(range), TYPECACHE_RANGE_INFO);
> + {
This brace is unnecessary. Perhaps a leftover from a removed conditional
statement.
> + /* this part is get the range's SUBTYPE_OPCLASS from pg_range
> catalog.
> + * Refer load_rangetype_info function last line.
> + * TypeCacheEntry->rngelemtype typcaheenetry either don't have
> opclass entry or with default opclass.
> + * Range's subtype opclass only in catalog table.
> + */
The comments in the patch need some more love.
Apart from the language, you should have a look at the style guide:
- single-line comments should start with lower case and have no period:
/* example of a single-line comment */
- Multi-line comments should start with /* on its own line and end with */ on
its
own line. They should use whole sentences:
/*
* In case a comment spans several lines, it should look like
* this. Try not to exceed 80 characters.
*/
> + tup = SearchSysCache1(RANGETYPE,
> ObjectIdGetDatum(RangeTypeGetOid(range)));
> +
> + /* should not fail, since we already checked typtype ... */
> + if (!HeapTupleIsValid(tup))
> + elog(ERROR, "cache lookup failed for range type %u",
> RangeTypeGetOid(range));
If this is a "can't happen" case, it should be an Assert.
> +
> + pg_range = (Form_pg_range) GETSTRUCT(tup);
> +
> + opclassOid = pg_range->rngsubopc;
> +
> + ReleaseSysCache(tup);
> +
> + /* get opclass properties and look up the comparison function */
> + opfamily = get_opclass_family(opclassOid);
> + }
> +
> + range_deserialize(rangetypcache, range, &lower, &upper, &empty);
> + rng_collation = rangetypcache->rng_collation;
> +
> + if (empty)
> + {
> + /* If the range is empty, then there can be no matches. */
> + return makeBoolConst(false, false);
> + }
> + else if (lower.infinite && upper.infinite)
> + {
> + /* The range has no bounds, so matches everything. */
> + return makeBoolConst(true, false);
> + }
> + else
> + {
Many of the variables declared at the beginning of the function are only used in
this branch. You should declare them here.
> +static Node *
> +match_support_request(Node *rawreq)
> +{
> + if (IsA(rawreq, SupportRequestSimplify))
> + {
To keep the indentation shallow, the preferred style is:
if (/* case we don't handle */)
return NULL;
/* proceed without indentation */
> + SupportRequestSimplify *req = (SupportRequestSimplify *) rawreq;
> + FuncExpr *fexpr = req->fcall;
> + Node *leftop;
> + Node *rightop;
> + Const *rangeConst;
> + Expr *otherExpr;
> +
> + Assert(list_length(fexpr->args) == 2);
> +
> + leftop = linitial(fexpr->args);
> + rightop = lsecond(fexpr->args);
> +
> + switch (fexpr->funcid)
> + {
> + case F_ELEM_CONTAINED_BY_RANGE:
> + if (!IsA(rightop, Const) || ((Const *)
> rightop)->constisnull)
> + return NULL;
> +
> + rangeConst = (Const *) rightop;
> + otherExpr = (Expr *) leftop;
> + break;
> +
> + case F_RANGE_CONTAINS_ELEM:
> + if (!IsA(leftop, Const) || ((Const *)
> leftop)->constisnull)
> + return NULL;
> +
> + rangeConst = (Const *) leftop;
> + otherExpr = (Expr *) rightop;
> + break;
> +
> + default:
> + return NULL;
> + }
> +
> + return find_simplified_clause(rangeConst, otherExpr);
> + }
> + return NULL;
> +}
> \ No newline at end of file
You are calling this funtion from both "elem_contained_by_range_support" and
"range_contains_elem_support", only to branch based on the function type.
I think the code would be simpler if you did away with "match_support_request"
at all.
I adjusted your patch according to my comments; what do you think?
I also went over the regression tests. I did away with the comparison
function, instead
I used examples that don't return too many rows. I cut down on the test cases
a little
bit. I added a test that uses the "text_pattern_ops" operator class.
Yours,
Laurenz Albe
From 0838ad7421461fa9359a9bbbfbb6c438a8ad4de5 Mon Sep 17 00:00:00 2001
From: Laurenz Albe <[email protected]>
Date: Sun, 12 Nov 2023 17:46:41 +0100
Subject: [PATCH] Simplify containment in range constants with support function
Expressions of the kind "element <@ range constant" or "range constant @> element"
are translated to expressions that a B-tree index can support.
Authors: Kim Johan Anderson, Jian He
Reviewed-by: Laurenz Albe
Discussion: https://postgr.es/m/[email protected]
---
src/backend/utils/adt/rangetypes.c | 191 +++++++++++++++++++
src/backend/utils/adt/rangetypes_selfuncs.c | 6 +-
src/include/catalog/pg_proc.dat | 12 +-
src/test/regress/expected/rangetypes.out | 193 ++++++++++++++++++++
src/test/regress/sql/rangetypes.sql | 92 ++++++++++
5 files changed, 489 insertions(+), 5 deletions(-)
diff --git a/src/backend/utils/adt/rangetypes.c b/src/backend/utils/adt/rangetypes.c
index 24bad52923..dbbb6fc8a6 100644
--- a/src/backend/utils/adt/rangetypes.c
+++ b/src/backend/utils/adt/rangetypes.c
@@ -31,16 +31,24 @@
#include "postgres.h"
#include "access/tupmacs.h"
+#include "access/stratnum.h"
+#include "catalog/pg_range.h"
#include "common/hashfn.h"
#include "lib/stringinfo.h"
#include "libpq/pqformat.h"
#include "miscadmin.h"
#include "nodes/miscnodes.h"
+#include "nodes/makefuncs.h"
+#include "nodes/nodeFuncs.h"
+#include "nodes/pg_list.h"
+#include "nodes/supportnodes.h"
#include "port/pg_bitutils.h"
#include "utils/builtins.h"
+#include "utils/fmgroids.h"
#include "utils/date.h"
#include "utils/lsyscache.h"
#include "utils/rangetypes.h"
+#include "utils/syscache.h"
#include "utils/timestamp.h"
#include "varatt.h"
@@ -69,6 +77,10 @@ static Size datum_compute_size(Size data_length, Datum val, bool typbyval,
char typalign, int16 typlen, char typstorage);
static Pointer datum_write(Pointer ptr, Datum datum, bool typbyval,
char typalign, int16 typlen, char typstorage);
+static Expr *build_bound_expr(Oid opfamily, TypeCacheEntry *typeCache,
+ bool isLowerBound, bool isInclusive,
+ Datum val, Expr *otherExpr, Oid rng_collation);
+static Node *find_simplified_clause(Const *rangeConst, Expr *otherExpr);
/*
@@ -2173,6 +2185,57 @@ make_empty_range(TypeCacheEntry *typcache)
return make_range(typcache, &lower, &upper, true, NULL);
}
+/*
+ * Planner support function for the elem_contained_by_range (<@) operator.
+ */
+Datum
+elem_contained_by_range_support(PG_FUNCTION_ARGS)
+{
+ Node *rawreq = (Node *) PG_GETARG_POINTER(0),
+ *leftop,
+ *rightop;
+ FuncExpr *fexpr;
+
+ if (!IsA(rawreq, SupportRequestSimplify))
+ PG_RETURN_POINTER(NULL);
+
+ fexpr = ((SupportRequestSimplify *) rawreq)->fcall;
+
+ Assert(list_length(fexpr->args) == 2);
+ leftop = linitial(fexpr->args);
+ rightop = lsecond(fexpr->args);
+
+ if (!IsA(rightop, Const) || ((Const *) rightop)->constisnull)
+ PG_RETURN_POINTER(NULL);
+
+ PG_RETURN_POINTER(find_simplified_clause((Const *) rightop, (Expr *) leftop));
+}
+
+/*
+ * Planner support function for the range_contains_elem (@>) operator.
+ */
+Datum
+range_contains_elem_support(PG_FUNCTION_ARGS)
+{
+ Node *rawreq = (Node *) PG_GETARG_POINTER(0),
+ *leftop,
+ *rightop;
+ FuncExpr *fexpr;
+
+ if (!IsA(rawreq, SupportRequestSimplify))
+ PG_RETURN_POINTER(NULL);
+
+ fexpr = ((SupportRequestSimplify *) rawreq)->fcall;
+
+ Assert(list_length(fexpr->args) == 2);
+ leftop = linitial(fexpr->args);
+ rightop = lsecond(fexpr->args);
+
+ if (!IsA(leftop, Const) || ((Const *) leftop)->constisnull)
+ PG_RETURN_POINTER(NULL);
+
+ PG_RETURN_POINTER(find_simplified_clause((Const *) leftop, (Expr *) rightop));
+}
/*
*----------------------------------------------------------
@@ -2715,3 +2778,131 @@ datum_write(Pointer ptr, Datum datum, bool typbyval, char typalign,
return ptr;
}
+
+/*
+ * Helper funciton for find_simplified_clause().
+ * Builds the expression (otherExpr Operator lower/upper(val)).
+ */
+static Expr *
+build_bound_expr(Oid opfamily, TypeCacheEntry *typeCache,
+ bool isLowerBound, bool isInclusive,
+ Datum val, Expr *otherExpr, Oid rng_collation)
+{
+ Oid elemType = typeCache->type_id;
+ int16 elemTypeLen = typeCache->typlen;
+ bool elemByValue = typeCache->typbyval;
+ Oid elemCollation = typeCache->typcollation;
+ int16 strategy;
+ Oid oproid;
+ Expr *constExpr;
+
+ if (isLowerBound)
+ strategy = isInclusive ? BTGreaterEqualStrategyNumber : BTGreaterStrategyNumber;
+ else
+ strategy = isInclusive ? BTLessEqualStrategyNumber : BTLessStrategyNumber;
+
+ oproid = get_opfamily_member(opfamily, elemType, elemType, strategy);
+
+ if (!OidIsValid(oproid))
+ return NULL;
+
+ constExpr = (Expr *) makeConst(elemType,
+ -1,
+ elemCollation,
+ elemTypeLen,
+ val,
+ false,
+ elemByValue);
+
+ return make_opclause(oproid,
+ BOOLOID,
+ false,
+ otherExpr,
+ constExpr,
+ InvalidOid,
+ rng_collation);
+}
+
+/*
+ * Common code for the ELEM_CONTAINED_BY_RANGE and RANGE_CONTAINS_ELEM support
+ * functions.
+ */
+static Node *
+find_simplified_clause(Const *rangeConst, Expr *otherExpr)
+{
+ HeapTuple tup;
+ Form_pg_range pg_range;
+ Oid opclassOid;
+ RangeBound lower;
+ RangeBound upper;
+ bool empty;
+ RangeType *range = DatumGetRangeTypeP(rangeConst->constvalue);
+ TypeCacheEntry *rangetypcache = lookup_type_cache(RangeTypeGetOid(range),
+ TYPECACHE_RANGE_INFO);
+
+ /* get the operator class from the pg_range catalog */
+ tup = SearchSysCache1(RANGETYPE, ObjectIdGetDatum(RangeTypeGetOid(range)));
+
+ /* should always be tha case, since we already checked typtype */
+ Assert(HeapTupleIsValid(tup));
+
+ pg_range = (Form_pg_range) GETSTRUCT(tup);
+
+ opclassOid = pg_range->rngsubopc;
+
+ ReleaseSysCache(tup);
+
+ range_deserialize(rangetypcache, range, &lower, &upper, &empty);
+
+ if (empty)
+ {
+ /* if the range is empty, then there can be no matches */
+ return makeBoolConst(false, false);
+ }
+ else if (lower.infinite && upper.infinite)
+ {
+ /* the range has no bounds, so matches everything */
+ return makeBoolConst(true, false);
+ }
+ else
+ {
+ /* at least one bound is available, we have something to work with */
+ Expr *lowerExpr = NULL;
+ Expr *upperExpr = NULL;
+ Oid elemType = rangetypcache->rngelemtype->type_id;
+ Oid opfamily = get_opclass_family(opclassOid);
+ TypeCacheEntry *elemTypcache = lookup_type_cache(elemType, 0);
+ Oid rng_collation = rangetypcache->rng_collation;
+
+ if (!lower.infinite)
+ {
+ lowerExpr = build_bound_expr(opfamily,
+ elemTypcache,
+ true,
+ lower.inclusive,
+ lower.val,
+ otherExpr,
+ rng_collation
+ );
+ }
+
+ if (!upper.infinite)
+ {
+ upperExpr = build_bound_expr(opfamily,
+ elemTypcache,
+ false,
+ upper.inclusive,
+ upper.val,
+ otherExpr,
+ rng_collation
+ );
+ }
+
+ if (lowerExpr != NULL && upperExpr != NULL)
+ return (Node *) makeBoolExpr(AND_EXPR, list_make2(lowerExpr, upperExpr), -1);
+ else if (lowerExpr != NULL)
+ return (Node *) lowerExpr;
+ else if (upperExpr != NULL)
+ return (Node *) upperExpr;
+ }
+}
diff --git a/src/backend/utils/adt/rangetypes_selfuncs.c b/src/backend/utils/adt/rangetypes_selfuncs.c
index fbabb3e18c..7c4cf0aef3 100644
--- a/src/backend/utils/adt/rangetypes_selfuncs.c
+++ b/src/backend/utils/adt/rangetypes_selfuncs.c
@@ -196,9 +196,9 @@ rangesel(PG_FUNCTION_ARGS)
else if (operator == OID_RANGE_ELEM_CONTAINED_OP)
{
/*
- * Here, the Var is the elem, not the range. For now we just punt and
- * return the default estimate. In future we could disassemble the
- * range constant and apply scalarineqsel ...
+ * Here, the Var is the elem, not the range.
+ * The support function in rangetypes.c should have simplified this case,
+ * enabling the clausesel.c machinery to handle it.
*/
}
else if (((Const *) other)->consttype == vardata.vartype)
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index f14aed422a..294ff66c67 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10475,13 +10475,15 @@
proargtypes => 'anyrange anyrange', prosrc => 'range_overlaps' },
{ oid => '3858',
proname => 'range_contains_elem', prorettype => 'bool',
- proargtypes => 'anyrange anyelement', prosrc => 'range_contains_elem' },
+ proargtypes => 'anyrange anyelement', prosrc => 'range_contains_elem',
+ prosupport => 'range_contains_elem_support' },
{ oid => '3859',
proname => 'range_contains', prorettype => 'bool',
proargtypes => 'anyrange anyrange', prosrc => 'range_contains' },
{ oid => '3860',
proname => 'elem_contained_by_range', prorettype => 'bool',
- proargtypes => 'anyelement anyrange', prosrc => 'elem_contained_by_range' },
+ proargtypes => 'anyelement anyrange', prosrc => 'elem_contained_by_range',
+ prosupport => 'elem_contained_by_range_support' },
{ oid => '3861',
proname => 'range_contained_by', prorettype => 'bool',
proargtypes => 'anyrange anyrange', prosrc => 'range_contained_by' },
@@ -10503,6 +10505,12 @@
{ oid => '3867',
proname => 'range_union', prorettype => 'anyrange',
proargtypes => 'anyrange anyrange', prosrc => 'range_union' },
+{ oid => '9998', descr => 'Planner support function for range_contains_elem operator',
+ proname => 'range_contains_elem_support', prorettype => 'internal',
+ proargtypes => 'internal', prosrc => 'range_contains_elem_support' },
+{ oid => '9999', descr => 'Planner support function for elem_contained_by_range operator',
+ proname => 'elem_contained_by_range_support', prorettype => 'internal',
+ proargtypes => 'internal', prosrc => 'elem_contained_by_range_support' },
{ oid => '4057',
descr => 'the smallest range which includes both of the given ranges',
proname => 'range_merge', prorettype => 'anyrange',
diff --git a/src/test/regress/expected/rangetypes.out b/src/test/regress/expected/rangetypes.out
index ee02ff0163..035a6b4dee 100644
--- a/src/test/regress/expected/rangetypes.out
+++ b/src/test/regress/expected/rangetypes.out
@@ -1834,3 +1834,196 @@ create function table_fail(i anyelement) returns table(i anyelement, r anyrange)
as $$ select $1, '[1,10]' $$ language sql;
ERROR: cannot determine result data type
DETAIL: A result of type anyrange requires at least one input of type anyrange or anymultirange.
+--
+-- Test support functions
+--
+CREATE TEMP TABLE date_support_test AS
+ SELECT '2000-01-01'::DATE + g AS some_date
+ FROM generate_series(-1000, 1000) sub(g);
+CREATE UNIQUE INDEX date_support_test_idx ON date_support_test (some_date);
+INSERT INTO date_support_test values ('-infinity'), ('infinity');
+ANALYZE date_support_test;
+-- empty ranges
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange 'empty';
+ QUERY PLAN
+--------------------------
+ Result
+ One-Time Filter: false
+(2 rows)
+
+-- only lower bound present
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange('2000-01-01', NULL, '[)');
+ QUERY PLAN
+---------------------------------------------
+ Seq Scan on date_support_test
+ Filter: (some_date >= '01-01-2000'::date)
+(2 rows)
+
+-- only upper bound present
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange(NULL, '2000-01-01', '(]');
+ QUERY PLAN
+--------------------------------------------
+ Seq Scan on date_support_test
+ Filter: (some_date < '01-02-2000'::date)
+(2 rows)
+
+-- unbounded range
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange(NULL, NULL);
+ QUERY PLAN
+-------------------------------
+ Seq Scan on date_support_test
+(1 row)
+
+-- lower range "-Infinity" excluded
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange('-Infinity', '1997-04-10'::DATE, '()');
+ QUERY PLAN
+--------------------------------------------------------------------------------------
+ Index Only Scan using date_support_test_idx on date_support_test
+ Index Cond: ((some_date > '-infinity'::date) AND (some_date < '04-10-1997'::date))
+(2 rows)
+
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange('-Infinity', '1997-04-10'::DATE, '()');
+ some_date
+------------
+ 04-06-1997
+ 04-07-1997
+ 04-08-1997
+ 04-09-1997
+(4 rows)
+
+-- lower range "-Infinity" included
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange('-Infinity', '1997-04-10'::DATE, '[)');
+ QUERY PLAN
+---------------------------------------------------------------------------------------
+ Index Only Scan using date_support_test_idx on date_support_test
+ Index Cond: ((some_date >= '-infinity'::date) AND (some_date < '04-10-1997'::date))
+(2 rows)
+
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange('-Infinity', '1997-04-10'::DATE, '[)');
+ some_date
+------------
+ -infinity
+ 04-06-1997
+ 04-07-1997
+ 04-08-1997
+ 04-09-1997
+(5 rows)
+
+-- upper range "Infinity" excluded
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange('2002-09-25'::DATE, 'Infinity', '[)');
+ QUERY PLAN
+--------------------------------------------------------------------------------------
+ Index Only Scan using date_support_test_idx on date_support_test
+ Index Cond: ((some_date >= '09-25-2002'::date) AND (some_date < 'infinity'::date))
+(2 rows)
+
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange('2002-09-25'::DATE, 'Infinity', '[)');
+ some_date
+------------
+ 09-25-2002
+ 09-26-2002
+ 09-27-2002
+(3 rows)
+
+-- upper range "Infinity" included
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange('2002-09-25'::DATE, 'Infinity', '[]');
+ QUERY PLAN
+---------------------------------------------------------------------------------------
+ Index Only Scan using date_support_test_idx on date_support_test
+ Index Cond: ((some_date >= '09-25-2002'::date) AND (some_date <= 'infinity'::date))
+(2 rows)
+
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange('2002-09-25'::DATE, 'Infinity', '[]');
+ some_date
+------------
+ 09-25-2002
+ 09-26-2002
+ 09-27-2002
+ infinity
+(4 rows)
+
+-- should also work if we use "@>"
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test
+WHERE daterange('-Infinity', '1997-04-10'::DATE, '()') @> some_date;
+ QUERY PLAN
+--------------------------------------------------------------------------------------
+ Index Only Scan using date_support_test_idx on date_support_test
+ Index Cond: ((some_date > '-infinity'::date) AND (some_date < '04-10-1997'::date))
+(2 rows)
+
+SELECT some_date FROM date_support_test
+WHERE daterange('-Infinity', '1997-04-10'::DATE, '()') @> some_date;
+ some_date
+------------
+ 04-06-1997
+ 04-07-1997
+ 04-08-1997
+ 04-09-1997
+(4 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test
+WHERE daterange('2002-09-25'::DATE, 'Infinity', '[]') @> some_date;
+ QUERY PLAN
+---------------------------------------------------------------------------------------
+ Index Only Scan using date_support_test_idx on date_support_test
+ Index Cond: ((some_date >= '09-25-2002'::date) AND (some_date <= 'infinity'::date))
+(2 rows)
+
+SELECT some_date FROM date_support_test
+WHERE daterange('2002-09-25'::DATE, 'Infinity', '[]') @> some_date;
+ some_date
+------------
+ 09-25-2002
+ 09-26-2002
+ 09-27-2002
+ infinity
+(4 rows)
+
+DROP TABLE date_support_test;
+-- test a custom range type with a non-default operator class
+CREATE TYPE textrange_supp AS RANGE (
+ SUBTYPE = text,
+ SUBTYPE_OPCLASS = text_pattern_ops
+);
+CREATE TEMP TABLE text_support_test (t text COLLATE "C");
+INSERT INTO text_support_test VALUES ('a'), ('c'), ('d'), ('ch');
+EXPLAIN (COSTS OFF)
+SELECT * FROM text_support_test WHERE t <@ textrange_supp('a', 'd');
+ QUERY PLAN
+------------------------------------------------------
+ Seq Scan on text_support_test
+ Filter: ((t ~>=~ 'a'::text) AND (t ~<~ 'd'::text))
+(2 rows)
+
+SELECT * FROM text_support_test WHERE t <@ textrange_supp('a', 'd');
+ t
+----
+ a
+ c
+ ch
+(3 rows)
+
+DROP TABLE text_support_test;
+DROP TYPE textrange_supp;
diff --git a/src/test/regress/sql/rangetypes.sql b/src/test/regress/sql/rangetypes.sql
index c23be928c3..b4c0db8228 100644
--- a/src/test/regress/sql/rangetypes.sql
+++ b/src/test/regress/sql/rangetypes.sql
@@ -629,3 +629,95 @@ create function inoutparam_fail(inout i anyelement, out r anyrange)
--should fail
create function table_fail(i anyelement) returns table(i anyelement, r anyrange)
as $$ select $1, '[1,10]' $$ language sql;
+
+--
+-- Test support functions
+--
+
+CREATE TEMP TABLE date_support_test AS
+ SELECT '2000-01-01'::DATE + g AS some_date
+ FROM generate_series(-1000, 1000) sub(g);
+CREATE UNIQUE INDEX date_support_test_idx ON date_support_test (some_date);
+INSERT INTO date_support_test values ('-infinity'), ('infinity');
+ANALYZE date_support_test;
+
+-- empty ranges
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange 'empty';
+
+-- only lower bound present
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange('2000-01-01', NULL, '[)');
+
+-- only upper bound present
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange(NULL, '2000-01-01', '(]');
+
+-- unbounded range
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange(NULL, NULL);
+
+-- lower range "-Infinity" excluded
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange('-Infinity', '1997-04-10'::DATE, '()');
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange('-Infinity', '1997-04-10'::DATE, '()');
+
+-- lower range "-Infinity" included
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange('-Infinity', '1997-04-10'::DATE, '[)');
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange('-Infinity', '1997-04-10'::DATE, '[)');
+
+-- upper range "Infinity" excluded
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange('2002-09-25'::DATE, 'Infinity', '[)');
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange('2002-09-25'::DATE, 'Infinity', '[)');
+
+-- upper range "Infinity" included
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange('2002-09-25'::DATE, 'Infinity', '[]');
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange('2002-09-25'::DATE, 'Infinity', '[]');
+
+-- should also work if we use "@>"
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test
+WHERE daterange('-Infinity', '1997-04-10'::DATE, '()') @> some_date;
+SELECT some_date FROM date_support_test
+WHERE daterange('-Infinity', '1997-04-10'::DATE, '()') @> some_date;
+
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test
+WHERE daterange('2002-09-25'::DATE, 'Infinity', '[]') @> some_date;
+SELECT some_date FROM date_support_test
+WHERE daterange('2002-09-25'::DATE, 'Infinity', '[]') @> some_date;
+
+DROP TABLE date_support_test;
+
+-- test a custom range type with a non-default operator class
+CREATE TYPE textrange_supp AS RANGE (
+ SUBTYPE = text,
+ SUBTYPE_OPCLASS = text_pattern_ops
+);
+
+CREATE TEMP TABLE text_support_test (t text COLLATE "C");
+
+INSERT INTO text_support_test VALUES ('a'), ('c'), ('d'), ('ch');
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM text_support_test WHERE t <@ textrange_supp('a', 'd');
+SELECT * FROM text_support_test WHERE t <@ textrange_supp('a', 'd');
+
+DROP TABLE text_support_test;
+
+DROP TYPE textrange_supp;
--
2.41.0
