On Tue, Aug 1, 2023 at 10:07 AM Laurenz Albe <[email protected]> wrote:
> >
> >
> > > I had an idea about this:
> > > So far, you only consider constant ranges. But if we have a STABLE range
> > > expression, you could use an index scan for "expr <@ range", for example
> > > Index Cond (expr >= lower(range) AND expr < upper(range)).
> > >
The above part, not sure how to implement it, not sure it is doable.
Refactor:
drop SupportRequestIndexCondition and related code, since mentioned in
upthread, it's dead code.
refactor the regression test. (since data types with infinity cover
more cases than int4range, so I deleted some tests).
now there are 3 helper functions (build_bound_expr,
find_simplified_clause, match_support_request), 2 entry functions
(elem_contained_by_range_support, range_contains_elem_support)
Collation problem seems solved. Putting the following test on the
src/test/regress/sql/rangetypes.sql will not work. Maybe because of
the order of the regression test, in SQL-ASCII encoding, I cannot
create collation="cs-CZ-x-icu".
drop type if EXISTS textrange1, textrange2;
drop table if EXISTS collate_test1, collate_test2;
CREATE TYPE textrange1 AS RANGE (SUBTYPE = text, collation="C");
create type textrange2 as range(subtype=text, collation="cs-CZ-x-icu");
CREATE TABLE collate_test1 (b text COLLATE "en-x-icu" NOT NULL);
INSERT INTO collate_test1(b) VALUES ('a'), ('c'), ('d'), ('ch');
CREATE TABLE collate_test2 (b text NOT NULL);
INSERT INTO collate_test2(b) VALUES ('a'), ('c'), ('d'), ('ch');
--should include 'ch'
SELECT * FROM collate_test1 WHERE b <@ textrange1('a', 'd');
--should not include 'ch'
SELECT * FROM collate_test1 WHERE b <@ textrange2('a', 'd');
--should include 'ch'
SELECT * FROM collate_test2 WHERE b <@ textrange1('a', 'd');
--should not include 'ch'
SELECT * FROM collate_test2 WHERE b <@ textrange2('a', 'd');
-----------------
From bcae7f8a0640b48b04f243660539e8670de43d41 Mon Sep 17 00:00:00 2001
From: pgaddict <[email protected]>
Date: Fri, 13 Oct 2023 12:43:41 +0800
Subject: [PATCH v2 1/1] Optimize qual cases like Expr <@ RangeConst and
RangeConst @> Expr
Previously these two quals will be processed as is.
With this patch, adding prosupport
function to range_contains_elem, elem_contained_by_range.
So cases like Expr <@ rangeCOnst, rangeConst @> expr
will be rewritten to "expr opr range_lower_bound and expr opr
range_upper_bound".
Expr <@ rangeConst will be logically equivalent to rangeConst @> Expr,
if rangeConst is the same. added some tests to validate the generated
plan.
---
src/backend/utils/adt/rangetypes.c | 199 +++++++++++++++++++-
src/backend/utils/adt/rangetypes_selfuncs.c | 6 +-
src/include/catalog/pg_proc.dat | 12 +-
src/test/regress/expected/rangetypes.out | 194 +++++++++++++++++++
src/test/regress/sql/rangetypes.sql | 101 ++++++++++
5 files changed, 505 insertions(+), 7 deletions(-)
diff --git a/src/backend/utils/adt/rangetypes.c b/src/backend/utils/adt/rangetypes.c
index d65e5625..647bd5e4 100644
--- a/src/backend/utils/adt/rangetypes.c
+++ b/src/backend/utils/adt/rangetypes.c
@@ -31,13 +31,19 @@
#include "postgres.h"
#include "access/tupmacs.h"
+#include "access/stratnum.h"
#include "common/hashfn.h"
#include "lib/stringinfo.h"
#include "libpq/pqformat.h"
#include "miscadmin.h"
+#include "nodes/supportnodes.h"
+#include "nodes/makefuncs.h"
+#include "nodes/nodeFuncs.h"
+#include "nodes/pg_list.h"
#include "nodes/miscnodes.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"
@@ -69,7 +75,11 @@ 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);
+static Node *match_support_request(Node *rawreq);
/*
*----------------------------------------------------------
@@ -558,7 +568,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) */
@@ -2173,6 +2182,29 @@ make_empty_range(TypeCacheEntry *typcache)
return make_range(typcache, &lower, &upper, true, NULL);
}
+/*
+ * Planner support function for elem_contained_by_range operator
+ */
+Datum
+elem_contained_by_range_support(PG_FUNCTION_ARGS)
+{
+ Node *rawreq = (Node *) PG_GETARG_POINTER(0);
+ Node *ret = match_support_request(rawreq);
+
+ PG_RETURN_POINTER(ret);
+}
+
+/*
+ * Planner support function for range_contains_elem operator
+ */
+Datum
+range_contains_elem_support(PG_FUNCTION_ARGS)
+{
+ Node *rawreq = (Node *) PG_GETARG_POINTER(0);
+ Node *ret = match_support_request(rawreq);
+
+ PG_RETURN_POINTER(ret);
+}
/*
*----------------------------------------------------------
@@ -2714,3 +2746,166 @@ datum_write(Pointer ptr, Datum datum, bool typbyval, char typalign,
return ptr;
}
+/*
+ * build (Expr Operator Range_bound) expression. something like: expr >= lower(range)
+ * if operator both sides have collation, operator should use (right) range's collation
+ *
+*/
+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
+ );
+}
+
+/*
+ * Supports both the ELEM_CONTAINED_BY_RANGE and RANGE_CONTAINS_ELEM cases.
+ *
+ */
+static Node *
+find_simplified_clause(Const *rangeConst, Expr *otherExpr)
+{
+ RangeType *range = DatumGetRangeTypeP(rangeConst->constvalue);
+ TypeCacheEntry *rangetypcache = lookup_type_cache(RangeTypeGetOid(range), TYPECACHE_RANGE_INFO);
+ RangeBound lower;
+ RangeBound upper;
+ bool empty;
+ Oid rng_collation;
+
+ 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
+ {
+ /* At least one bound is available, we have something to work with. */
+ TypeCacheEntry *elemTypcache = lookup_type_cache(rangetypcache->rngelemtype->type_id, TYPECACHE_BTREE_OPFAMILY);
+ Expr *lowerExpr = NULL;
+ Expr *upperExpr = NULL;
+
+ /* There might not be an operator family available for this element */
+ if (!OidIsValid(elemTypcache->btree_opf))
+ return NULL;
+
+ if (!lower.infinite)
+ {
+ lowerExpr = build_bound_expr(elemTypcache->btree_opf,
+ elemTypcache,
+ true,
+ lower.inclusive,
+ lower.val,
+ otherExpr,
+ rng_collation
+ );
+ }
+
+ if (!upper.infinite)
+ {
+ upperExpr = build_bound_expr(elemTypcache->btree_opf,
+ 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;
+ }
+
+ return NULL;
+}
+
+static Node *
+match_support_request(Node *rawreq)
+{
+ if (IsA(rawreq, SupportRequestSimplify))
+ {
+ 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
diff --git a/src/backend/utils/adt/rangetypes_selfuncs.c b/src/backend/utils/adt/rangetypes_selfuncs.c
index fbabb3e1..7c4cf0ae 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 f0b7b9cb..3777ce4a 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10468,13 +10468,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' },
@@ -10496,6 +10498,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 ee02ff01..02f3f29e 100644
--- a/src/test/regress/expected/rangetypes.out
+++ b/src/test/regress/expected/rangetypes.out
@@ -1834,3 +1834,197 @@ 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 function
+--
+-- Test actual results, as well as estimates.
+CREATE TABLE date_support_test AS SELECT '2000-01-01'::DATE + g AS some_date FROM
+ generate_series(-1000, 1000) sub(g);
+CREATE UNIQUE INDEX ON date_support_test( some_date );
+INSERT INTO date_support_test values ( '-infinity' ), ( 'infinity' );
+ANALYZE date_support_test;
+create or REPLACE function check2plan(text, text)
+RETURNS void
+AS $$
+ declare ln1 text default '';
+ ln2 text default '';
+BEGIN
+ execute $1 into ln1;
+ execute $2 into ln2;
+ if ln1 = ln2 and ln1 != '' and ln2 != '' then
+ RAISE NOTICE 'these two query''plan is the same';
+ end if;
+END;
+$$
+LANGUAGE plpgsql;
+-- Empty ranges
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange('2000-01-01', '2000-01-01', '()');
+ 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)
+
+-- No bounds, so not a bounded 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)
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '()');
+ QUERY PLAN
+----------------------------------------------------------------------------------------
+ Aggregate
+ -> Seq Scan on date_support_test
+ Filter: ((some_date > '-infinity'::date) AND (some_date < '01-01-2000'::date))
+(3 rows)
+
+-- Should return 1000 rows, since -infinity and 2000-01-01 are not included in the range
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '()');
+ count
+-------
+ 1000
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '[)');
+ QUERY PLAN
+-----------------------------------------------------------------------------------------
+ Aggregate
+ -> Seq Scan on date_support_test
+ Filter: ((some_date >= '-infinity'::date) AND (some_date < '01-01-2000'::date))
+(3 rows)
+
+-- Should return 1001 rows, since -infinity is included here
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '[)');
+ count
+-------
+ 1001
+(1 row)
+
+select check2plan($$ EXPLAIN (COSTS OFF)
+ SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '()');
+ $$,
+ $$
+ EXPLAIN (COSTS OFF)
+ SELECT count(some_date) FROM date_support_test
+ WHERE daterange('-infinity', '2000-01-01'::DATE, '()') @> some_date
+ $$);
+NOTICE: these two query'plan is the same
+ check2plan
+------------
+
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '[]');
+ QUERY PLAN
+-----------------------------------------------------------------------------------------
+ Aggregate
+ -> Seq Scan on date_support_test
+ Filter: ((some_date >= '-infinity'::date) AND (some_date < '01-02-2000'::date))
+(3 rows)
+
+-- Should return 1002 rows, since -infinity and 2000-01-01 are included here
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '[]');
+ count
+-------
+ 1002
+(1 row)
+
+select check2plan($a$
+ EXPLAIN (COSTS OFF)
+ SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('2000-01-01'::DATE, 'infinity', '[)')
+ $a$,
+ $b$
+ EXPLAIN (COSTS OFF)
+ SELECT count(some_date) FROM date_support_test WHERE daterange('2000-01-01'::DATE, 'infinity', '[)') @> some_date
+ $b$);
+NOTICE: these two query'plan is the same
+ check2plan
+------------
+
+(1 row)
+
+-- Should return 1001 rows, since infinity not included here
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('2000-01-01'::DATE, 'infinity', '[)');
+ count
+-------
+ 1001
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('2000-01-01'::DATE, 'infinity', '[]');
+ QUERY PLAN
+-----------------------------------------------------------------------------------------
+ Aggregate
+ -> Seq Scan on date_support_test
+ Filter: ((some_date >= '01-01-2000'::date) AND (some_date <= 'infinity'::date))
+(3 rows)
+
+-- Should return 1002 rows, since infinity is included here
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('2000-01-01'::DATE, 'infinity', '[]');
+ count
+-------
+ 1002
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('infinity', 'infinity', '[]');
+ QUERY PLAN
+-------------------------------------------------------------------------------------------
+ Aggregate
+ -> Index Only Scan using date_support_test_some_date_idx on date_support_test
+ Index Cond: ((some_date >= 'infinity'::date) AND (some_date <= 'infinity'::date))
+(3 rows)
+
+-- Should return 1 rows, since just infinity is included here
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('infinity', 'infinity', '[]');
+ count
+-------
+ 1
+(1 row)
+
+-- Should return 0 rows, since this is up to, but not including infinity
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('infinity', 'infinity', '[)');
+ count
+-------
+ 0
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('infinity', 'infinity', '[)');
+ QUERY PLAN
+--------------------------------
+ Aggregate
+ -> Result
+ One-Time Filter: false
+(3 rows)
+
+DROP TABLE date_support_test;
+drop function check2plan;
diff --git a/src/test/regress/sql/rangetypes.sql b/src/test/regress/sql/rangetypes.sql
index c23be928..7d6d1799 100644
--- a/src/test/regress/sql/rangetypes.sql
+++ b/src/test/regress/sql/rangetypes.sql
@@ -629,3 +629,104 @@ 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 function
+--
+-- Test actual results, as well as estimates.
+CREATE TABLE date_support_test AS SELECT '2000-01-01'::DATE + g AS some_date FROM
+ generate_series(-1000, 1000) sub(g);
+CREATE UNIQUE INDEX ON date_support_test( some_date );
+INSERT INTO date_support_test values ( '-infinity' ), ( 'infinity' );
+ANALYZE date_support_test;
+
+create or REPLACE function check2plan(text, text)
+RETURNS void
+AS $$
+ declare ln1 text default '';
+ ln2 text default '';
+BEGIN
+ execute $1 into ln1;
+ execute $2 into ln2;
+ if ln1 = ln2 and ln1 != '' and ln2 != '' then
+ RAISE NOTICE 'these two query''plan is the same';
+ end if;
+END;
+$$
+LANGUAGE plpgsql;
+
+-- Empty ranges
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test
+WHERE some_date <@ daterange('2000-01-01', '2000-01-01', '()');
+
+-- 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', '[]');
+
+-- No bounds, so not a bounded range.
+EXPLAIN (COSTS OFF)
+SELECT some_date FROM date_support_test WHERE some_date <@ daterange(null, null);
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '()');
+-- Should return 1000 rows, since -infinity and 2000-01-01 are not included in the range
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '()');
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '[)');
+-- Should return 1001 rows, since -infinity is included here
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '[)');
+
+select check2plan($$ EXPLAIN (COSTS OFF)
+ SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '()');
+ $$,
+ $$
+ EXPLAIN (COSTS OFF)
+ SELECT count(some_date) FROM date_support_test
+ WHERE daterange('-infinity', '2000-01-01'::DATE, '()') @> some_date
+ $$);
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '[]');
+
+-- Should return 1002 rows, since -infinity and 2000-01-01 are included here
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('-infinity', '2000-01-01'::DATE, '[]');
+
+select check2plan($a$
+ EXPLAIN (COSTS OFF)
+ SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('2000-01-01'::DATE, 'infinity', '[)')
+ $a$,
+ $b$
+ EXPLAIN (COSTS OFF)
+ SELECT count(some_date) FROM date_support_test WHERE daterange('2000-01-01'::DATE, 'infinity', '[)') @> some_date
+ $b$);
+
+-- Should return 1001 rows, since infinity not included here
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('2000-01-01'::DATE, 'infinity', '[)');
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('2000-01-01'::DATE, 'infinity', '[]');
+
+-- Should return 1002 rows, since infinity is included here
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('2000-01-01'::DATE, 'infinity', '[]');
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('infinity', 'infinity', '[]');
+-- Should return 1 rows, since just infinity is included here
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('infinity', 'infinity', '[]');
+
+-- Should return 0 rows, since this is up to, but not including infinity
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('infinity', 'infinity', '[)');
+
+EXPLAIN (COSTS OFF)
+SELECT count(some_date) FROM date_support_test WHERE some_date <@ daterange('infinity', 'infinity', '[)');
+
+DROP TABLE date_support_test;
+drop function check2plan;
\ No newline at end of file
--
2.34.1
