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 <laurenz.a...@cybertec.at> 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/94f64d1f-b8c0-b0c5-98bc-0793a34e0...@kimmet.dk --- 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