From 766948beddef66dd89563f465919eca6e131861c Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Tue, 22 Nov 2011 16:05:49 -0500 Subject: [PATCH] Still more review for range-types patch. Per discussion, relax the range input/construction rules so that the only hard error is lower bound > upper bound. Cases where the lower bound is <= upper bound, but the range nonetheless normalizes to empty, are now permitted. Fix core dump in range_adjacent when bounds are infinite. Marginal cleanup of regression test cases, some more code commenting. --- src/backend/utils/adt/rangetypes.c | 472 ++++++++++++++--------- src/backend/utils/adt/rangetypes_gist.c | 45 +-- src/include/utils/rangetypes.h | 5 + src/test/regress/expected/rangetypes.out | 309 +++++++++------ src/test/regress/sql/rangetypes.sql | 97 +++-- 5 files changed, 556 insertions(+), 372 deletions(-) diff --git a/src/backend/utils/adt/rangetypes.c b/src/backend/utils/adt/rangetypes.c index 5581eb1c4a0..bb413362ed1 100644 --- a/src/backend/utils/adt/rangetypes.c +++ b/src/backend/utils/adt/rangetypes.c @@ -3,6 +3,22 @@ * rangetypes.c * I/O functions, operators, and support functions for range types. * + * The stored (serialized) format of a range value is: + * + * 4 bytes: varlena header + * 4 bytes: range type's OID + * Lower boundary value, if any, aligned according to subtype's typalign + * Upper boundary value, if any, aligned according to subtype's typalign + * 1 byte for flags + * + * This representation is chosen to avoid needing any padding before the + * lower boundary value, even when it requires double alignment. We can + * expect that the varlena header is presented to us on a suitably aligned + * boundary (possibly after detoasting), and then the lower boundary is too. + * Note that this means we can't work with a packed (short varlena header) + * value; we must detoast it first. + * + * * Portions Copyright (c) 1996-2011, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * @@ -40,12 +56,13 @@ typedef struct RangeIOData static RangeIOData *get_range_io_data(FunctionCallInfo fcinfo, Oid rngtypid, IOFuncSelector func); static char range_parse_flags(const char *flags_str); -static void range_parse(char *input_str, char *flags, char **lbound_str, +static void range_parse(const char *input_str, char *flags, char **lbound_str, char **ubound_str); -static char *range_parse_bound(char *string, char *ptr, char **bound_str, - bool *infinite); -static char *range_deparse(char flags, char *lbound_str, char *ubound_str); -static char *range_bound_escape(char *in_str); +static const char *range_parse_bound(const char *string, const char *ptr, + char **bound_str, bool *infinite); +static char *range_deparse(char flags, const char *lbound_str, + const char *ubound_str); +static char *range_bound_escape(const char *value); static bool range_contains_internal(TypeCacheEntry *typcache, RangeType *r1, RangeType *r2); static Size datum_compute_size(Size sz, Datum datum, bool typbyval, @@ -125,7 +142,7 @@ range_out(PG_FUNCTION_ARGS) if (RANGE_HAS_UBOUND(flags)) ubound_str = OutputFunctionCall(&cache->proc, upper.val); - /* deparse */ + /* construct result string */ output_str = range_deparse(flags, lbound_str, ubound_str); PG_RETURN_CSTRING(output_str); @@ -156,8 +173,9 @@ range_recv(PG_FUNCTION_ARGS) flags = (unsigned char) pq_getmsgbyte(buf); /* - * mask out any unsupported flags, particularly RANGE_xB_NULL which would - * confuse following tests. + * Mask out any unsupported flags, particularly RANGE_xB_NULL which would + * confuse following tests. Note that range_serialize will take care of + * cleaning up any inconsistencies in the remaining flags. */ flags &= (RANGE_EMPTY | RANGE_LB_INC | @@ -330,32 +348,22 @@ get_range_io_data(FunctionCallInfo fcinfo, Oid rngtypid, IOFuncSelector func) *---------------------------------------------------------- */ +/* Construct empty range value from no arguments */ Datum range_constructor0(PG_FUNCTION_ARGS) { Oid rngtypid = get_fn_expr_rettype(fcinfo->flinfo); RangeType *range; TypeCacheEntry *typcache; - RangeBound lower; - RangeBound upper; typcache = range_get_typcache(fcinfo, rngtypid); - lower.val = (Datum) 0; - lower.infinite = false; - lower.inclusive = false; - lower.lower = true; - - upper.val = (Datum) 0; - upper.infinite = false; - upper.inclusive = false; - upper.lower = false; - - range = make_range(typcache, &lower, &upper, true); + range = make_empty_range(typcache); PG_RETURN_RANGE(range); } +/* Construct singleton range value from one argument */ Datum range_constructor1(PG_FUNCTION_ARGS) { @@ -363,8 +371,6 @@ range_constructor1(PG_FUNCTION_ARGS) Oid rngtypid = get_fn_expr_rettype(fcinfo->flinfo); RangeType *range; TypeCacheEntry *typcache; - RangeBound lower; - RangeBound upper; typcache = range_get_typcache(fcinfo, rngtypid); @@ -373,21 +379,12 @@ range_constructor1(PG_FUNCTION_ARGS) (errcode(ERRCODE_DATA_EXCEPTION), errmsg("range constructor argument must not be NULL"))); - lower.val = arg1; - lower.infinite = false; - lower.inclusive = true; - lower.lower = true; - - upper.val = arg1; - upper.infinite = false; - upper.inclusive = true; - upper.lower = false; - - range = make_range(typcache, &lower, &upper, false); + range = make_singleton_range(typcache, arg1); PG_RETURN_RANGE(range); } +/* Construct standard-form range value from two arguments */ Datum range_constructor2(PG_FUNCTION_ARGS) { @@ -416,6 +413,7 @@ range_constructor2(PG_FUNCTION_ARGS) PG_RETURN_RANGE(range); } +/* Construct general range value from three arguments */ Datum range_constructor3(PG_FUNCTION_ARGS) { @@ -452,7 +450,10 @@ range_constructor3(PG_FUNCTION_ARGS) PG_RETURN_RANGE(range); } -/* range -> subtype */ + +/* range -> subtype functions */ + +/* extract lower bound value */ Datum range_lower(PG_FUNCTION_ARGS) { @@ -473,6 +474,7 @@ range_lower(PG_FUNCTION_ARGS) PG_RETURN_DATUM(lower.val); } +/* extract upper bound value */ Datum range_upper(PG_FUNCTION_ARGS) { @@ -494,7 +496,9 @@ range_upper(PG_FUNCTION_ARGS) } -/* range -> bool */ +/* range -> bool functions */ + +/* is range empty? */ Datum range_empty(PG_FUNCTION_ARGS) { @@ -504,6 +508,7 @@ range_empty(PG_FUNCTION_ARGS) PG_RETURN_BOOL(flags & RANGE_EMPTY); } +/* is lower bound inclusive? */ Datum range_lower_inc(PG_FUNCTION_ARGS) { @@ -513,6 +518,7 @@ range_lower_inc(PG_FUNCTION_ARGS) PG_RETURN_BOOL(flags & RANGE_LB_INC); } +/* is upper bound inclusive? */ Datum range_upper_inc(PG_FUNCTION_ARGS) { @@ -522,6 +528,7 @@ range_upper_inc(PG_FUNCTION_ARGS) PG_RETURN_BOOL(flags & RANGE_UB_INC); } +/* is lower bound infinite? */ Datum range_lower_inf(PG_FUNCTION_ARGS) { @@ -531,6 +538,7 @@ range_lower_inf(PG_FUNCTION_ARGS) PG_RETURN_BOOL(flags & RANGE_LB_INF); } +/* is upper bound infinite? */ Datum range_upper_inf(PG_FUNCTION_ARGS) { @@ -541,7 +549,48 @@ range_upper_inf(PG_FUNCTION_ARGS) } -/* range, range -> bool */ +/* range, element -> bool functions */ + +/* contains? */ +Datum +range_contains_elem(PG_FUNCTION_ARGS) +{ + RangeType *r1 = PG_GETARG_RANGE(0); + Datum val = PG_GETARG_DATUM(1); + TypeCacheEntry *typcache; + RangeType *r2; + + typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r1)); + + /* Construct a singleton range representing just "val" */ + r2 = make_singleton_range(typcache, val); + + /* And use range_contains */ + PG_RETURN_BOOL(range_contains_internal(typcache, r1, r2)); +} + +/* contained by? */ +Datum +elem_contained_by_range(PG_FUNCTION_ARGS) +{ + Datum val = PG_GETARG_DATUM(0); + RangeType *r1 = PG_GETARG_RANGE(1); + TypeCacheEntry *typcache; + RangeType *r2; + + typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r1)); + + /* Construct a singleton range representing just "val" */ + r2 = make_singleton_range(typcache, val); + + /* And use range_contains */ + PG_RETURN_BOOL(range_contains_internal(typcache, r1, r2)); +} + + +/* range, range -> bool functions */ + +/* equality */ Datum range_eq(PG_FUNCTION_ARGS) { @@ -578,6 +627,7 @@ range_eq(PG_FUNCTION_ARGS) PG_RETURN_BOOL(true); } +/* inequality */ Datum range_ne(PG_FUNCTION_ARGS) { @@ -586,35 +636,7 @@ range_ne(PG_FUNCTION_ARGS) PG_RETURN_BOOL(!eq); } -Datum -range_contains_elem(PG_FUNCTION_ARGS) -{ - RangeType *r1 = PG_GETARG_RANGE(0); - Datum val = PG_GETARG_DATUM(1); - TypeCacheEntry *typcache; - RangeBound lower2; - RangeBound upper2; - RangeType *r2; - - typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r1)); - - /* Construct a singleton range representing just "val" */ - lower2.val = val; - lower2.infinite = false; - lower2.inclusive = true; - lower2.lower = true; - - upper2.val = val; - upper2.infinite = false; - upper2.inclusive = true; - upper2.lower = false; - - r2 = make_range(typcache, &lower2, &upper2, false); - - /* And use range_contains */ - PG_RETURN_BOOL(range_contains_internal(typcache, r1, r2)); -} - +/* contains? */ Datum range_contains(PG_FUNCTION_ARGS) { @@ -631,35 +653,7 @@ range_contains(PG_FUNCTION_ARGS) PG_RETURN_BOOL(range_contains_internal(typcache, r1, r2)); } -Datum -elem_contained_by_range(PG_FUNCTION_ARGS) -{ - Datum val = PG_GETARG_DATUM(0); - RangeType *r1 = PG_GETARG_RANGE(1); - TypeCacheEntry *typcache; - RangeBound lower2; - RangeBound upper2; - RangeType *r2; - - typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r1)); - - /* Construct a singleton range representing just "val" */ - lower2.val = val; - lower2.infinite = false; - lower2.inclusive = true; - lower2.lower = true; - - upper2.val = val; - upper2.infinite = false; - upper2.inclusive = true; - upper2.lower = false; - - r2 = make_range(typcache, &lower2, &upper2, false); - - /* And use range_contains */ - PG_RETURN_BOOL(range_contains_internal(typcache, r1, r2)); -} - +/* contained by? */ Datum range_contained_by(PG_FUNCTION_ARGS) { @@ -676,6 +670,7 @@ range_contained_by(PG_FUNCTION_ARGS) PG_RETURN_BOOL(range_contains_internal(typcache, r2, r1)); } +/* strictly left of? */ Datum range_before(PG_FUNCTION_ARGS) { @@ -705,6 +700,7 @@ range_before(PG_FUNCTION_ARGS) PG_RETURN_BOOL(range_cmp_bounds(typcache, &upper1, &lower2) < 0); } +/* strictly right of? */ Datum range_after(PG_FUNCTION_ARGS) { @@ -734,6 +730,7 @@ range_after(PG_FUNCTION_ARGS) PG_RETURN_BOOL(range_cmp_bounds(typcache, &lower1, &upper2) > 0); } +/* adjacent to (but not overlapping)? */ Datum range_adjacent(PG_FUNCTION_ARGS) { @@ -770,23 +767,20 @@ range_adjacent(PG_FUNCTION_ARGS) */ if (lower1.inclusive != upper2.inclusive) { - if (DatumGetInt32(FunctionCall2Coll(&typcache->rng_cmp_proc_finfo, - typcache->rng_collation, - lower1.val, upper2.val)) == 0) + if (range_cmp_bound_values(typcache, &lower1, &upper2) == 0) PG_RETURN_BOOL(true); } if (upper1.inclusive != lower2.inclusive) { - if (DatumGetInt32(FunctionCall2Coll(&typcache->rng_cmp_proc_finfo, - typcache->rng_collation, - upper1.val, lower2.val)) == 0) + if (range_cmp_bound_values(typcache, &upper1, &lower2) == 0) PG_RETURN_BOOL(true); } PG_RETURN_BOOL(false); } +/* overlaps? */ Datum range_overlaps(PG_FUNCTION_ARGS) { @@ -824,6 +818,7 @@ range_overlaps(PG_FUNCTION_ARGS) PG_RETURN_BOOL(false); } +/* does not extend to right of? */ Datum range_overleft(PG_FUNCTION_ARGS) { @@ -856,6 +851,7 @@ range_overleft(PG_FUNCTION_ARGS) PG_RETURN_BOOL(false); } +/* does not extend to left of? */ Datum range_overright(PG_FUNCTION_ARGS) { @@ -889,7 +885,9 @@ range_overright(PG_FUNCTION_ARGS) } -/* range, range -> range */ +/* range, range -> range functions */ + +/* set difference */ Datum range_minus(PG_FUNCTION_ARGS) { @@ -954,6 +952,7 @@ range_minus(PG_FUNCTION_ARGS) PG_RETURN_NULL(); } +/* set union */ Datum range_union(PG_FUNCTION_ARGS) { @@ -1003,6 +1002,7 @@ range_union(PG_FUNCTION_ARGS) PG_RETURN_RANGE(make_range(typcache, result_lower, result_upper, false)); } +/* set intersection */ Datum range_intersect(PG_FUNCTION_ARGS) { @@ -1045,6 +1045,7 @@ range_intersect(PG_FUNCTION_ARGS) /* Btree support */ +/* btree comparator */ Datum range_cmp(PG_FUNCTION_ARGS) { @@ -1082,6 +1083,7 @@ range_cmp(PG_FUNCTION_ARGS) PG_RETURN_INT32(range_cmp_bounds(typcache, &upper1, &upper2)); } +/* inequality operators using the range_cmp function */ Datum range_lt(PG_FUNCTION_ARGS) { @@ -1116,6 +1118,7 @@ range_gt(PG_FUNCTION_ARGS) /* Hash support */ +/* hash a range value */ Datum hash_range(PG_FUNCTION_ARGS) { @@ -1281,7 +1284,11 @@ daterange_canonical(PG_FUNCTION_ARGS) *---------------------------------------------------------- * SUBTYPE_DIFF FUNCTIONS * - * Functions for specific built-in range types. + * Functions for specific built-in range types. + * + * Note that subtype_diff does return the difference, not the absolute value + * of the difference, and it must take care to avoid overflow. + * (numrange_subdiff is at some risk there ...) *---------------------------------------------------------- */ @@ -1394,38 +1401,19 @@ range_get_typcache(FunctionCallInfo fcinfo, Oid rngtypid) return typcache; } - -/* - * Serialized format is: - * - * 4 bytes: Range type Oid - * Lower boundary, if any, aligned according to subtype's typalign - * Upper boundary, if any, aligned according to subtype's typalign - * 1 byte for flags - * - * This representation is chosen to be compact when the boundary - * values need to be MAXALIGNed. A palloc chunk always starts out - * MAXALIGNed, and the first 4 bytes will be the length header (range - * types are always variable-length), then the next 4 bytes will be - * the range type Oid. That leaves the first boundary item MAXALIGNed - * without the need for padding. - * - * However, it requires a slightly odd deserialization strategy, - * because we have to read the flags byte before we know whether to - * read a boundary value. - */ - /* * range_serialize: construct a range value from bounds and empty-flag * * This does not force canonicalization of the range value. In most cases, - * external callers should only be canonicalization functions. + * external callers should only be canonicalization functions. Note that + * we perform some datatype-independent canonicalization checks anyway. */ RangeType * range_serialize(TypeCacheEntry *typcache, RangeBound *lower, RangeBound *upper, bool empty) { RangeType *range; + int cmp; Size msize; Pointer ptr; int16 typlen; @@ -1434,28 +1422,48 @@ range_serialize(TypeCacheEntry *typcache, RangeBound *lower, RangeBound *upper, char typstorage; char flags = 0; - /* fetch information about range's element type */ - typlen = typcache->rngelemtype->typlen; - typbyval = typcache->rngelemtype->typbyval; - typalign = typcache->rngelemtype->typalign; - typstorage = typcache->rngelemtype->typstorage; + /* + * Verify range is not invalid on its face, and construct flags value, + * preventing any non-canonical combinations such as infinite+inclusive. + */ + Assert(lower->lower); + Assert(!upper->lower); - /* construct flags value */ if (empty) flags |= RANGE_EMPTY; else { - if (range_cmp_bounds(typcache, lower, upper) > 0) + cmp = range_cmp_bound_values(typcache, lower, upper); + + /* error check: if lower bound value is above upper, it's wrong */ + if (cmp > 0) ereport(ERROR, (errcode(ERRCODE_DATA_EXCEPTION), errmsg("range lower bound must be less than or equal to range upper bound"))); - flags |= lower->inclusive ? RANGE_LB_INC : 0; - flags |= lower->infinite ? RANGE_LB_INF : 0; - flags |= upper->inclusive ? RANGE_UB_INC : 0; - flags |= upper->infinite ? RANGE_UB_INF : 0; + /* if bounds are equal, and not both inclusive, range is empty */ + if (cmp == 0 && !(lower->inclusive && upper->inclusive)) + flags |= RANGE_EMPTY; + else + { + /* infinite boundaries are never inclusive */ + if (lower->infinite) + flags |= RANGE_LB_INF; + else if (lower->inclusive) + flags |= RANGE_LB_INC; + if (upper->infinite) + flags |= RANGE_UB_INF; + else if (upper->inclusive) + flags |= RANGE_UB_INC; + } } + /* Fetch information about range's element type */ + typlen = typcache->rngelemtype->typlen; + typbyval = typcache->rngelemtype->typbyval; + typalign = typcache->rngelemtype->typalign; + typstorage = typcache->rngelemtype->typstorage; + /* Count space for varlena header and range type's OID */ msize = sizeof(RangeType); Assert(msize == MAXALIGN(msize)); @@ -1615,7 +1623,9 @@ make_range(TypeCacheEntry *typcache, RangeBound *lower, RangeBound *upper, range = range_serialize(typcache, lower, upper, empty); - if (OidIsValid(typcache->rng_canonical_finfo.fn_oid)) + /* no need to call canonical on empty ranges ... */ + if (OidIsValid(typcache->rng_canonical_finfo.fn_oid) && + !RangeIsEmpty(range)) range = DatumGetRangeType(FunctionCall1(&typcache->rng_canonical_finfo, RangeTypeGetDatum(range))); @@ -1710,6 +1720,50 @@ range_cmp_bounds(TypeCacheEntry *typcache, RangeBound *b1, RangeBound *b2) return result; } +/* + * Compare two range boundary point values, returning <0, 0, or >0 according + * to whether b1 is less than, equal to, or greater than b2. + * + * This is similar to but simpler than range_cmp_bounds(). We just compare + * the values held in b1 and b2, ignoring inclusive/exclusive flags. The + * lower/upper flags only matter for infinities, where they tell us if the + * infinity is plus or minus. + */ +int +range_cmp_bound_values(TypeCacheEntry *typcache, RangeBound *b1, + RangeBound *b2) +{ + /* + * First, handle cases involving infinity, which don't require invoking + * the comparison proc. + */ + if (b1->infinite && b2->infinite) + { + /* + * Both are infinity, so they are equal unless one is lower and the + * other not. + */ + if (b1->lower == b2->lower) + return 0; + else + return b1->lower ? -1 : 1; + } + else if (b1->infinite) + return b1->lower ? -1 : 1; + else if (b2->infinite) + return b2->lower ? 1 : -1; + + /* + * Both boundaries are finite, so compare the held values. + */ + return DatumGetInt32(FunctionCall2Coll(&typcache->rng_cmp_proc_finfo, + typcache->rng_collation, + b1->val, b2->val)); +} + +/* + * Build an empty range value of the type indicated by the typcache entry. + */ RangeType * make_empty_range(TypeCacheEntry *typcache) { @@ -1729,6 +1783,28 @@ make_empty_range(TypeCacheEntry *typcache) return make_range(typcache, &lower, &upper, true); } +/* + * Build a range value representing a single point. + */ +RangeType * +make_singleton_range(TypeCacheEntry *typcache, Datum val) +{ + RangeBound lower; + RangeBound upper; + + lower.val = val; + lower.infinite = false; + lower.inclusive = true; + lower.lower = true; + + upper.val = val; + upper.infinite = false; + upper.inclusive = true; + upper.lower = false; + + return make_range(typcache, &lower, &upper, false); +} + /* *---------------------------------------------------------- @@ -1785,17 +1861,26 @@ range_parse_flags(const char *flags_str) } /* - * Parse range input, modeled after record_in in rowtypes.c. + * Parse range input. * + * Input parameters: + * string: input string to be parsed + * Output parameters: + * *flags: receives flags bitmask + * *lbound_str: receives palloc'd lower bound string, or NULL if none + * *ubound_str: receives palloc'd upper bound string, or NULL if none + * + * This is modeled somewhat after record_in in rowtypes.c. + * The input syntax is: * := EMPTY * | , * := '[' | '(' * := ']' | ')' * - * Whitespace before or after is ignored. Whitespace within a - * is taken literally and becomes the input string for that bound. + * Whitespace before or after is ignored. Whitespace within a + * is taken literally and becomes part of the input string for that bound. * - * A of length zero is taken as "infinite" (i.e. no bound); unless it + * A of length zero is taken as "infinite" (i.e. no bound), unless it * is surrounded by double-quotes, in which case it is the literal empty * string. * @@ -1804,10 +1889,10 @@ range_parse_flags(const char *flags_str) * double-quotes, a double-quote can be escaped with double-quote or backslash. */ static void -range_parse(char *string, char *flags, char **lbound_str, +range_parse(const char *string, char *flags, char **lbound_str, char **ubound_str) { - char *ptr = string; + const char *ptr = string; bool infinite; *flags = 0; @@ -1821,6 +1906,8 @@ range_parse(char *string, char *flags, char **lbound_str, strlen(RANGE_EMPTY_LITERAL)) == 0) { *flags = RANGE_EMPTY; + *lbound_str = NULL; + *ubound_str = NULL; ptr += strlen(RANGE_EMPTY_LITERAL); @@ -1834,64 +1921,55 @@ range_parse(char *string, char *flags, char **lbound_str, (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), errmsg("malformed range literal: \"%s\"", string), - errdetail("Unexpected end of input."))); + errdetail("Junk after \"empty\" keyword."))); return; } - if (*ptr == '[' || *ptr == '(') + if (*ptr == '[') { - if (*ptr == '[') - *flags |= RANGE_LB_INC; + *flags |= RANGE_LB_INC; ptr++; } + else if (*ptr == '(') + ptr++; else - { ereport(ERROR, (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), errmsg("malformed range literal: \"%s\"", string), errdetail("Missing left parenthesis or bracket."))); - } ptr = range_parse_bound(string, ptr, lbound_str, &infinite); if (infinite) - { *flags |= RANGE_LB_INF; - *flags &= ~RANGE_LB_INC; - } - if (*ptr != ',') + if (*ptr == ',') + ptr++; + else ereport(ERROR, (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), errmsg("malformed range literal: \"%s\"", string), - errdetail("Missing upper bound."))); - ptr++; + errdetail("Missing comma after lower bound."))); ptr = range_parse_bound(string, ptr, ubound_str, &infinite); + if (infinite) + *flags |= RANGE_UB_INF; - if (*ptr == ')' || *ptr == ']') + if (*ptr == ']') { - if (*ptr == ']') - *flags |= RANGE_UB_INC; + *flags |= RANGE_UB_INC; + ptr++; } - else - { + else if (*ptr == ')') + ptr++; + else /* must be a comma */ ereport(ERROR, (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), errmsg("malformed range literal: \"%s\"", string), - errdetail("Too many boundaries."))); - } - - ptr++; - - if (infinite) - { - *flags |= RANGE_UB_INF; - *flags &= ~RANGE_UB_INC; - } + errdetail("Too many commas."))); /* consume whitespace */ while (*ptr != '\0' && isspace((unsigned char) *ptr)) @@ -1903,12 +1981,25 @@ range_parse(char *string, char *flags, char **lbound_str, errmsg("malformed range literal: \"%s\"", string), errdetail("Junk after right parenthesis or bracket."))); - - return; } -static char * -range_parse_bound(char *string, char *ptr, char **bound_str, bool *infinite) +/* + * Helper for range_parse: parse and de-quote one bound string. + * + * We scan until finding comma, right parenthesis, or right bracket. + * + * Input parameters: + * string: entire input string (used only for error reports) + * ptr: where to start parsing bound + * Output parameters: + * *bound_str: receives palloc'd bound string, or NULL if none + * *infinite: set true if no bound, else false + * + * The return value is the scan ptr, advanced past the bound string. + */ +static const char * +range_parse_bound(const char *string, const char *ptr, + char **bound_str, bool *infinite) { StringInfoData buf; @@ -1920,7 +2011,7 @@ range_parse_bound(char *string, char *ptr, char **bound_str, bool *infinite) } else { - /* Extract string for this column */ + /* Extract string for this bound */ bool inquote = false; initStringInfo(&buf); @@ -1970,10 +2061,13 @@ range_parse_bound(char *string, char *ptr, char **bound_str, bool *infinite) /* * Convert a deserialized range value to text form * + * Inputs are the flags byte, and the two bound values already converted to + * text (but not yet quoted). If no bound value, pass NULL. + * * Result is a palloc'd string */ static char * -range_deparse(char flags, char *lbound_str, char *ubound_str) +range_deparse(char flags, const char *lbound_str, const char *ubound_str) { StringInfoData buf; @@ -1982,17 +2076,17 @@ range_deparse(char flags, char *lbound_str, char *ubound_str) initStringInfo(&buf); - appendStringInfoString(&buf, (flags & RANGE_LB_INC) ? "[" : "("); + appendStringInfoChar(&buf, (flags & RANGE_LB_INC) ? '[' : '('); if (RANGE_HAS_LBOUND(flags)) appendStringInfoString(&buf, range_bound_escape(lbound_str)); - appendStringInfoString(&buf, ","); + appendStringInfoChar(&buf, ','); if (RANGE_HAS_UBOUND(flags)) appendStringInfoString(&buf, range_bound_escape(ubound_str)); - appendStringInfoString(&buf, (flags & RANGE_UB_INC) ? "]" : ")"); + appendStringInfoChar(&buf, (flags & RANGE_UB_INC) ? ']' : ')'); return buf.data; } @@ -2003,19 +2097,19 @@ range_deparse(char flags, char *lbound_str, char *ubound_str) * Result is a palloc'd string */ static char * -range_bound_escape(char *value) +range_bound_escape(const char *value) { bool nq; - char *tmp; + const char *ptr; StringInfoData buf; initStringInfo(&buf); /* Detect whether we need double quotes for this value */ nq = (value[0] == '\0'); /* force quotes for empty string */ - for (tmp = value; *tmp; tmp++) + for (ptr = value; *ptr; ptr++) { - char ch = *tmp; + char ch = *ptr; if (ch == '"' || ch == '\\' || ch == '(' || ch == ')' || @@ -2031,9 +2125,9 @@ range_bound_escape(char *value) /* And emit the string */ if (nq) appendStringInfoChar(&buf, '"'); - for (tmp = value; *tmp; tmp++) + for (ptr = value; *ptr; ptr++) { - char ch = *tmp; + char ch = *ptr; if (ch == '"' || ch == '\\') appendStringInfoChar(&buf, ch); @@ -2045,6 +2139,12 @@ range_bound_escape(char *value) return buf.data; } +/* + * Test whether range r1 contains range r2. + * + * Caller has already checked that they are the same range type, and looked up + * the necessary typcache entry. + */ static bool range_contains_internal(TypeCacheEntry *typcache, RangeType *r1, RangeType *r2) { @@ -2058,11 +2158,13 @@ range_contains_internal(TypeCacheEntry *typcache, RangeType *r1, RangeType *r2) range_deserialize(typcache, r1, &lower1, &upper1, &empty1); range_deserialize(typcache, r2, &lower2, &upper2, &empty2); + /* If either range is empty, the answer is easy */ if (empty2) return true; else if (empty1) return false; + /* Else we must have lower1 <= lower2 and upper1 >= upper2 */ if (range_cmp_bounds(typcache, &lower1, &lower2) > 0) return false; if (range_cmp_bounds(typcache, &upper1, &upper2) < 0) diff --git a/src/backend/utils/adt/rangetypes_gist.c b/src/backend/utils/adt/rangetypes_gist.c index 3fc05d2650b..815d2bf8d0a 100644 --- a/src/backend/utils/adt/rangetypes_gist.c +++ b/src/backend/utils/adt/rangetypes_gist.c @@ -35,8 +35,6 @@ #define RANGESTRAT_EQ 18 #define RANGESTRAT_NE 19 -#define RangeIsEmpty(r) (range_get_flags(r) & RANGE_EMPTY) - /* * Auxiliary structure for picksplit method. */ @@ -58,6 +56,7 @@ static bool range_gist_consistent_leaf(FmgrInfo *flinfo, static int sort_item_cmp(const void *a, const void *b); +/* GiST query consistency check */ Datum range_gist_consistent(PG_FUNCTION_ARGS) { @@ -69,8 +68,6 @@ range_gist_consistent(PG_FUNCTION_ARGS) RangeType *key = DatumGetRangeType(entry->key); TypeCacheEntry *typcache; RangeType *query; - RangeBound lower; - RangeBound upper; /* All operators served by this function are exact */ *recheck = false; @@ -78,25 +75,18 @@ range_gist_consistent(PG_FUNCTION_ARGS) switch (strategy) { /* - * For contains and contained by operators, the other operand is a - * "point" of the subtype. Construct a singleton range containing - * just that value. + * For element contains and contained by operators, the other operand + * is a "point" of the subtype. Construct a singleton range + * containing just that value. (Since range_contains_elem and + * elem_contained_by_range would do that anyway, it's actually more + * efficient not less so to merge these cases into range containment + * at this step. But revisit this if we ever change the implementation + * of those functions.) */ case RANGESTRAT_CONTAINS_ELEM: case RANGESTRAT_ELEM_CONTAINED_BY: typcache = range_get_typcache(fcinfo, RangeTypeGetOid(key)); - - lower.val = dquery; - lower.infinite = false; - lower.inclusive = true; - lower.lower = true; - - upper.val = dquery; - upper.infinite = false; - upper.inclusive = true; - upper.lower = false; - - query = make_range(typcache, &lower, &upper, false); + query = make_singleton_range(typcache, dquery); break; default: @@ -112,6 +102,7 @@ range_gist_consistent(PG_FUNCTION_ARGS) key, query)); } +/* form union range */ Datum range_gist_union(PG_FUNCTION_ARGS) { @@ -134,6 +125,7 @@ range_gist_union(PG_FUNCTION_ARGS) PG_RETURN_RANGE(result_range); } +/* compress, decompress are no-ops */ Datum range_gist_compress(PG_FUNCTION_ARGS) { @@ -150,6 +142,7 @@ range_gist_decompress(PG_FUNCTION_ARGS) PG_RETURN_POINTER(entry); } +/* page split penalty function */ Datum range_gist_penalty(PG_FUNCTION_ARGS) { @@ -177,6 +170,7 @@ range_gist_penalty(PG_FUNCTION_ARGS) subtype_diff = &typcache->rng_subdiff_finfo; + /* we want to compare the size of "orig" to size of "orig union new" */ s_union = range_super_union(typcache, orig, new); range_deserialize(typcache, orig, &lower1, &upper1, &empty1); @@ -268,9 +262,9 @@ range_gist_penalty(PG_FUNCTION_ARGS) /* * The GiST PickSplit method for ranges * - * Algorithm based on sorting. Incoming array of periods is sorted using - * sort_item_cmp function. After that first half of periods goes to the left - * datum, and the second half of periods goes to the right datum. + * Algorithm based on sorting. Incoming array of ranges is sorted using + * sort_item_cmp function. After that first half of ranges goes to the left + * output, and the second half of ranges goes to the right output. */ Datum range_gist_picksplit(PG_FUNCTION_ARGS) @@ -318,7 +312,7 @@ range_gist_picksplit(PG_FUNCTION_ARGS) v->spl_nright = 0; /* - * First half of items goes to the left datum. + * First half of items goes to the left output. */ pred_left = sortItems[0].data; *left++ = sortItems[0].index; @@ -331,7 +325,7 @@ range_gist_picksplit(PG_FUNCTION_ARGS) } /* - * Second half of items goes to the right datum. + * Second half of items goes to the right output. */ pred_right = sortItems[split_idx].data; *right++ = sortItems[split_idx].index; @@ -351,6 +345,7 @@ range_gist_picksplit(PG_FUNCTION_ARGS) PG_RETURN_POINTER(v); } +/* equality comparator for GiST */ Datum range_gist_same(PG_FUNCTION_ARGS) { @@ -375,6 +370,8 @@ range_gist_same(PG_FUNCTION_ARGS) /* * Return the smallest range that contains r1 and r2 + * + * XXX would it be better to redefine range_union as working this way? */ static RangeType * range_super_union(TypeCacheEntry *typcache, RangeType * r1, RangeType * r2) diff --git a/src/include/utils/rangetypes.h b/src/include/utils/rangetypes.h index 7d826d55210..f64f7c887e3 100644 --- a/src/include/utils/rangetypes.h +++ b/src/include/utils/rangetypes.h @@ -49,6 +49,8 @@ typedef struct RANGE_UB_NULL | \ RANGE_UB_INF))) +#define RangeIsEmpty(r) (range_get_flags(r) & RANGE_EMPTY) + /* Internal representation of either bound of a range (not what's on disk) */ typedef struct @@ -153,7 +155,10 @@ extern RangeType *make_range(TypeCacheEntry *typcache, RangeBound *lower, RangeBound *upper, bool empty); extern int range_cmp_bounds(TypeCacheEntry *typcache, RangeBound *b1, RangeBound *b2); +extern int range_cmp_bound_values(TypeCacheEntry *typcache, RangeBound *b1, + RangeBound *b2); extern RangeType *make_empty_range(TypeCacheEntry *typcache); +extern RangeType *make_singleton_range(TypeCacheEntry *typcache, Datum val); /* GiST support (in rangetypes_gist.c) */ extern Datum range_gist_consistent(PG_FUNCTION_ARGS); diff --git a/src/test/regress/expected/rangetypes.out b/src/test/regress/expected/rangetypes.out index 130446d0069..4083a02542d 100644 --- a/src/test/regress/expected/rangetypes.out +++ b/src/test/regress/expected/rangetypes.out @@ -1,7 +1,8 @@ +-- Tests for range data types. +create type textrange as range (subtype=text, collation="C"); -- --- test parser +-- test input parser -- -create type textrange as range (subtype=text, collation="C"); -- negative tests; should fail select ''::textrange; ERROR: malformed range literal: "" @@ -27,12 +28,12 @@ select '(,,a)'::textrange; ERROR: malformed range literal: "(,,a)" LINE 1: select '(,,a)'::textrange; ^ -DETAIL: Too many boundaries. +DETAIL: Too many commas. select '(),a)'::textrange; ERROR: malformed range literal: "(),a)" LINE 1: select '(),a)'::textrange; ^ -DETAIL: Missing upper bound. +DETAIL: Missing comma after lower bound. select '(a,))'::textrange; ERROR: malformed range literal: "(a,))" LINE 1: select '(a,))'::textrange; @@ -42,35 +43,15 @@ select '(],a)'::textrange; ERROR: malformed range literal: "(],a)" LINE 1: select '(],a)'::textrange; ^ -DETAIL: Missing upper bound. +DETAIL: Missing comma after lower bound. select '(a,])'::textrange; ERROR: malformed range literal: "(a,])" LINE 1: select '(a,])'::textrange; ^ DETAIL: Junk after right parenthesis or bracket. -select '( , )'::textrange; -ERROR: range lower bound must be less than or equal to range upper bound -LINE 1: select '( , )'::textrange; - ^ -select '("","")'::textrange; -ERROR: range lower bound must be less than or equal to range upper bound -LINE 1: select '("","")'::textrange; - ^ -select '(",",",")'::textrange; -ERROR: range lower bound must be less than or equal to range upper bound -LINE 1: select '(",",",")'::textrange; - ^ -select '("\\","\\")'::textrange; -ERROR: range lower bound must be less than or equal to range upper bound -LINE 1: select '("\\","\\")'::textrange; - ^ -select '[a,a)'::textrange; +select '[z,a]'::textrange; ERROR: range lower bound must be less than or equal to range upper bound -LINE 1: select '[a,a)'::textrange; - ^ -select '(a,a]'::textrange; -ERROR: range lower bound must be less than or equal to range upper bound -LINE 1: select '(a,a]'::textrange; +LINE 1: select '[z,a]'::textrange; ^ -- should succeed select ' empty '::textrange; @@ -121,6 +102,12 @@ select '(,)'::textrange; (,) (1 row) +select '[ , ]'::textrange; + textrange +----------- + [" "," "] +(1 row) + select '["",""]'::textrange; textrange ----------- @@ -169,36 +156,63 @@ select '(!,[)'::textrange; (!,"[") (1 row) +select '[a,a]'::textrange; + textrange +----------- + [a,a] +(1 row) + +-- these are allowed but normalize to empty: +select '[a,a)'::textrange; + textrange +----------- + empty +(1 row) + +select '(a,a]'::textrange; + textrange +----------- + empty +(1 row) + +select '(a,a)'::textrange; + textrange +----------- + empty +(1 row) + -- -- create some test data and test the operators -- CREATE TABLE numrange_test (nr NUMRANGE); create index numrange_test_btree on numrange_test(nr); -SET enable_seqscan = f; INSERT INTO numrange_test VALUES('[,)'); INSERT INTO numrange_test VALUES('[3,]'); INSERT INTO numrange_test VALUES('[, 5)'); INSERT INTO numrange_test VALUES(numrange(1.1, 2.2)); INSERT INTO numrange_test VALUES('empty'); INSERT INTO numrange_test VALUES(numrange(1.7)); -SELECT isempty(nr) FROM numrange_test; - isempty ---------- - f - f - f - f - t - f +SELECT nr, isempty(nr), lower(nr), upper(nr) FROM numrange_test; + nr | isempty | lower | upper +-----------+---------+-------+------- + (,) | f | | + [3,) | f | 3 | + (,5) | f | | 5 + [1.1,2.2) | f | 1.1 | 2.2 + empty | t | | + [1.7,1.7] | f | 1.7 | 1.7 (6 rows) -SELECT lower_inc(nr), lower(nr), upper(nr), upper_inc(nr) FROM numrange_test - WHERE NOT isempty(nr) AND NOT lower_inf(nr) AND NOT upper_inf(nr); - lower_inc | lower | upper | upper_inc ------------+-------+-------+----------- - t | 1.1 | 2.2 | f - t | 1.7 | 1.7 | t -(2 rows) +SELECT nr, lower_inc(nr), lower_inf(nr), upper_inc(nr), upper_inf(nr) FROM numrange_test; + nr | lower_inc | lower_inf | upper_inc | upper_inf +-----------+-----------+-----------+-----------+----------- + (,) | f | t | f | t + [3,) | t | f | f | t + (,5) | f | t | f | f + [1.1,2.2) | t | f | f | f + empty | f | f | f | f + [1.7,1.7] | t | f | t | f +(6 rows) SELECT * FROM numrange_test WHERE range_contains(nr, numrange(1.9,1.91)); nr @@ -229,23 +243,71 @@ SELECT * FROM numrange_test WHERE 1.9 <@ nr; [1.1,2.2) (3 rows) -SELECT * FROM numrange_test WHERE nr = 'empty'; +select * from numrange_test where nr = 'empty'; nr ------- empty (1 row) -SELECT * FROM numrange_test WHERE range_eq(nr, '(1.1, 2.2)'); +select * from numrange_test where nr = '(1.1, 2.2)'; nr ---- (0 rows) -SELECT * FROM numrange_test WHERE nr = '[1.1, 2.2)'; +select * from numrange_test where nr = '[1.1, 2.2)'; nr ----------- [1.1,2.2) (1 row) +select * from numrange_test where nr < numrange(-1000.0, -1000.0,'[]'); + nr +------- + (,) + (,5) + empty +(3 rows) + +select * from numrange_test where nr < numrange(0.0, 1.0,'[]'); + nr +------- + (,) + (,5) + empty +(3 rows) + +select * from numrange_test where nr < numrange(1000.0, 1001.0,'[]'); + nr +----------- + (,) + [3,) + (,5) + [1.1,2.2) + empty + [1.7,1.7] +(6 rows) + +select * from numrange_test where nr > numrange(-1001.0, -1000.0,'[]'); + nr +----------- + [3,) + [1.1,2.2) + [1.7,1.7] +(3 rows) + +select * from numrange_test where nr > numrange(0.0, 1.0,'[]'); + nr +----------- + [3,) + [1.1,2.2) + [1.7,1.7] +(3 rows) + +select * from numrange_test where nr > numrange(1000.0, 1000.0,'[]'); + nr +---- +(0 rows) + select numrange(2.0, 1.0); ERROR: range lower bound must be less than or equal to range upper bound select numrange(2.0, 3.0) -|- numrange(3.0, 4.0); @@ -260,6 +322,12 @@ select range_adjacent(numrange(2.0, 3.0), numrange(3.1, 4.0)); f (1 row) +select range_adjacent(numrange(2.0, 3.0), numrange(3.1, null)); + range_adjacent +---------------- + f +(1 row) + select numrange(2.0, 3.0, '[]') -|- numrange(3.0, 4.0, '()'); ?column? ---------- @@ -400,54 +468,6 @@ select numrange(1.0, 2.0) * numrange(2.5, 3.0); empty (1 row) -select * from numrange_test where nr < numrange(-1000.0, -1000.0,'[]'); - nr -------- - (,) - (,5) - empty -(3 rows) - -select * from numrange_test where nr < numrange(0.0, 1.0,'[]'); - nr -------- - (,) - (,5) - empty -(3 rows) - -select * from numrange_test where nr < numrange(1000.0, 1001.0,'[]'); - nr ------------ - (,) - [3,) - (,5) - [1.1,2.2) - empty - [1.7,1.7] -(6 rows) - -select * from numrange_test where nr > numrange(-1001.0, -1000.0,'[]'); - nr ------------ - [3,) - [1.1,2.2) - [1.7,1.7] -(3 rows) - -select * from numrange_test where nr > numrange(0.0, 1.0,'[]'); - nr ------------ - [3,) - [1.1,2.2) - [1.7,1.7] -(3 rows) - -select * from numrange_test where nr > numrange(1000.0, 1000.0,'[]'); - nr ----- -(0 rows) - create table numrange_test2(nr numrange); create index numrange_test2_hash_idx on numrange_test2 (nr); INSERT INTO numrange_test2 VALUES('[, 5)'); @@ -512,57 +532,74 @@ select * from numrange_test natural join numrange_test2 order by nr; set enable_nestloop to default; set enable_hashjoin to default; set enable_mergejoin to default; -SET enable_seqscan TO DEFAULT; DROP TABLE numrange_test; DROP TABLE numrange_test2; -- test canonical form for int4range -select int4range(1,10,'[]'); +select int4range(1, 10, '[]'); int4range ----------- [1,11) (1 row) -select int4range(1,10,'[)'); +select int4range(1, 10, '[)'); int4range ----------- [1,10) (1 row) -select int4range(1,10,'(]'); +select int4range(1, 10, '(]'); int4range ----------- [2,11) (1 row) -select int4range(1,10,'[]'); +select int4range(1, 10, '()'); int4range ----------- - [1,11) + [2,10) +(1 row) + +select int4range(1, 2, '()'); + int4range +----------- + empty (1 row) -- test canonical form for daterange -select daterange('2000-01-10'::date, '2000-01-20'::date,'[]'); +select daterange('2000-01-10'::date, '2000-01-20'::date, '[]'); daterange ------------------------- [01-10-2000,01-21-2000) (1 row) -select daterange('2000-01-10'::date, '2000-01-20'::date,'[)'); +select daterange('2000-01-10'::date, '2000-01-20'::date, '[)'); daterange ------------------------- [01-10-2000,01-20-2000) (1 row) -select daterange('2000-01-10'::date, '2000-01-20'::date,'(]'); +select daterange('2000-01-10'::date, '2000-01-20'::date, '(]'); daterange ------------------------- [01-11-2000,01-21-2000) (1 row) -select daterange('2000-01-10'::date, '2000-01-20'::date,'[]'); +select daterange('2000-01-10'::date, '2000-01-20'::date, '()'); daterange ------------------------- - [01-10-2000,01-21-2000) + [01-11-2000,01-20-2000) +(1 row) + +select daterange('2000-01-10'::date, '2000-01-11'::date, '()'); + daterange +----------- + empty +(1 row) + +select daterange('2000-01-10'::date, '2000-01-11'::date, '(]'); + daterange +------------------------- + [01-11-2000,01-12-2000) (1 row) -- test GiST index that's been built incrementally @@ -806,15 +843,15 @@ insert into test_range_excl insert into test_range_excl values(int4range(123), int4range(2), '[2010-01-02 11:00, 2010-01-02 12:00)'); insert into test_range_excl - values(int4range(123), int4range(3), '[2010-01-02 10:10, 2010-01-02 11:10)'); + values(int4range(123), int4range(3), '[2010-01-02 10:10, 2010-01-02 11:00)'); ERROR: conflicting key value violates exclusion constraint "test_range_excl_room_during_excl" -DETAIL: Key (room, during)=([123,124), ["Sat Jan 02 10:10:00 2010","Sat Jan 02 11:10:00 2010")) conflicts with existing key (room, during)=([123,124), ["Sat Jan 02 10:00:00 2010","Sat Jan 02 11:00:00 2010")). +DETAIL: Key (room, during)=([123,124), ["Sat Jan 02 10:10:00 2010","Sat Jan 02 11:00:00 2010")) conflicts with existing key (room, during)=([123,124), ["Sat Jan 02 10:00:00 2010","Sat Jan 02 11:00:00 2010")). insert into test_range_excl values(int4range(124), int4range(3), '[2010-01-02 10:10, 2010-01-02 11:10)'); insert into test_range_excl - values(int4range(125), int4range(1), '[2010-01-02 10:10, 2010-01-02 11:10)'); + values(int4range(125), int4range(1), '[2010-01-02 10:10, 2010-01-02 11:00)'); 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")). +DETAIL: Key (speaker, during)=([1,2), ["Sat Jan 02 10:10:00 2010","Sat Jan 02 11:00:00 2010")) conflicts with existing key (speaker, during)=([1,2), ["Sat Jan 02 10:00:00 2010","Sat Jan 02 11:00:00 2010")). -- test bigint ranges select int8range(10000000000::int8, 20000000000::int8,'(]'); int8range @@ -851,11 +888,11 @@ select '[123.001, 5.e9)'::float8range @> 888.882::float8; (1 row) create table float8range_test(f8r float8range, i int); -insert into float8range_test values(float8range(-100.00007, '1.111113e9')); +insert into float8range_test values(float8range(-100.00007, '1.111113e9'), 42); select * from float8range_test; - f8r | i --------------------------+--- - [-100.00007,1111113000) | + f8r | i +-------------------------+---- + [-100.00007,1111113000) | 42 (1 row) drop table float8range_test; @@ -870,12 +907,16 @@ select '[4,50)'::mydomainrange @> 7::mydomain; t (1 row) -drop type mydomainrange; -drop domain mydomain; +drop domain mydomain; -- fail +ERROR: cannot drop type mydomain because other objects depend on it +DETAIL: type mydomainrange depends on type mydomain +HINT: Use DROP ... CASCADE to drop the dependent objects too. +drop domain mydomain cascade; +NOTICE: drop cascades to type mydomainrange -- -- Test domains over range types -- -create domain restrictedrange as int4range check (upper(value) < 10); +create domain restrictedrange as int4range check (upper(value) < 10); select '[4,5)'::restrictedrange @> 7; ?column? ---------- @@ -901,7 +942,7 @@ select textrange2('a','z') @> 'b'::text; drop type textrange1; drop type textrange2; -- --- Test out polymorphic type system +-- Test polymorphic type system -- create function anyarray_anyrange_func(a anyarray, r anyrange) returns anyelement as 'select $1[1] + lower($2);' language sql; @@ -930,6 +971,12 @@ ERROR: cannot determine result data type DETAIL: A function returning a polymorphic type must have at least one polymorphic argument. create function range_add_bounds(anyrange) returns anyelement as 'select lower($1) + upper($1)' language sql; +select range_add_bounds(int4range(1, 17)); + range_add_bounds +------------------ + 18 +(1 row) + select range_add_bounds(numrange(1.0001, 123.123)); range_add_bounds ------------------ @@ -978,6 +1025,8 @@ select arrayrange(ARRAY[1,2], ARRAY[2,1]); ["{1,2}","{2,1}") (1 row) +select arrayrange(ARRAY[2,1], ARRAY[1,2]); -- fail +ERROR: range lower bound must be less than or equal to range upper bound select array[1,1] <@ arrayrange(array[1,2], array[2,1]); ?column? ---------- @@ -994,11 +1043,29 @@ select array[1,3] <@ arrayrange(array[1,2], array[2,1]); -- OUT/INOUT/TABLE functions -- create function outparam_succeed(i anyrange, out r anyrange, out t text) - as $$ select $1, 'foo' $$ language sql; + as $$ select $1, 'foo'::text $$ language sql; +select * from outparam_succeed(int4range(1,2)); + r | t +-------+----- + [1,2) | foo +(1 row) + create function inoutparam_succeed(out i anyelement, inout r anyrange) - as $$ select $1, $2 $$ language sql; + as $$ select upper($1), $1 $$ language sql; +select * from inoutparam_succeed(int4range(1,2)); + i | r +---+------- + 2 | [1,2) +(1 row) + create function table_succeed(i anyelement, r anyrange) returns table(i anyelement, r anyrange) as $$ select $1, $2 $$ language sql; +select * from table_succeed(123, int4range(1,11)); + i | r +-----+-------- + 123 | [1,11) +(1 row) + -- should fail create function outparam_fail(i anyelement, out r anyrange, out t text) as $$ select '[1,10]', 'foo' $$ language sql; @@ -1010,7 +1077,7 @@ create function inoutparam_fail(inout i anyelement, out r anyrange) ERROR: cannot determine result data type DETAIL: A function returning ANYRANGE must have at least one ANYRANGE argument. --should fail -create function table_succeed(i anyelement) returns table(i anyelement, r anyrange) +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 function returning ANYRANGE must have at least one ANYRANGE argument. diff --git a/src/test/regress/sql/rangetypes.sql b/src/test/regress/sql/rangetypes.sql index b34a0d7c347..1136debfb96 100644 --- a/src/test/regress/sql/rangetypes.sql +++ b/src/test/regress/sql/rangetypes.sql @@ -1,10 +1,11 @@ +-- Tests for range data types. + +create type textrange as range (subtype=text, collation="C"); -- --- test parser +-- test input parser -- -create type textrange as range (subtype=text, collation="C"); - -- negative tests; should fail select ''::textrange; select '-[a,z)'::textrange; @@ -15,12 +16,7 @@ select '(),a)'::textrange; select '(a,))'::textrange; select '(],a)'::textrange; select '(a,])'::textrange; -select '( , )'::textrange; -select '("","")'::textrange; -select '(",",",")'::textrange; -select '("\\","\\")'::textrange; -select '[a,a)'::textrange; -select '(a,a]'::textrange; +select '[z,a]'::textrange; -- should succeed select ' empty '::textrange; @@ -31,6 +27,7 @@ select '(a,)'::textrange; select '[,z]'::textrange; select '[a,]'::textrange; select '(,)'::textrange; +select '[ , ]'::textrange; select '["",""]'::textrange; select '[",",","]'::textrange; select '["\\","\\"]'::textrange; @@ -39,6 +36,11 @@ select '((,z)'::textrange; select '([,z)'::textrange; select '(!,()'::textrange; select '(!,[)'::textrange; +select '[a,a]'::textrange; +-- these are allowed but normalize to empty: +select '[a,a)'::textrange; +select '(a,a]'::textrange; +select '(a,a)'::textrange; -- -- create some test data and test the operators @@ -46,7 +48,6 @@ select '(!,[)'::textrange; CREATE TABLE numrange_test (nr NUMRANGE); create index numrange_test_btree on numrange_test(nr); -SET enable_seqscan = f; INSERT INTO numrange_test VALUES('[,)'); INSERT INTO numrange_test VALUES('[3,]'); @@ -55,22 +56,29 @@ INSERT INTO numrange_test VALUES(numrange(1.1, 2.2)); INSERT INTO numrange_test VALUES('empty'); INSERT INTO numrange_test VALUES(numrange(1.7)); -SELECT isempty(nr) FROM numrange_test; -SELECT lower_inc(nr), lower(nr), upper(nr), upper_inc(nr) FROM numrange_test - WHERE NOT isempty(nr) AND NOT lower_inf(nr) AND NOT upper_inf(nr); +SELECT nr, isempty(nr), lower(nr), upper(nr) FROM numrange_test; +SELECT nr, lower_inc(nr), lower_inf(nr), upper_inc(nr), upper_inf(nr) FROM numrange_test; SELECT * FROM numrange_test WHERE range_contains(nr, numrange(1.9,1.91)); SELECT * FROM numrange_test WHERE nr @> numrange(1.0,10000.1); SELECT * FROM numrange_test WHERE range_contained_by(numrange(-1e7,-10000.1), nr); SELECT * FROM numrange_test WHERE 1.9 <@ nr; -SELECT * FROM numrange_test WHERE nr = 'empty'; -SELECT * FROM numrange_test WHERE range_eq(nr, '(1.1, 2.2)'); -SELECT * FROM numrange_test WHERE nr = '[1.1, 2.2)'; + +select * from numrange_test where nr = 'empty'; +select * from numrange_test where nr = '(1.1, 2.2)'; +select * from numrange_test where nr = '[1.1, 2.2)'; +select * from numrange_test where nr < numrange(-1000.0, -1000.0,'[]'); +select * from numrange_test where nr < numrange(0.0, 1.0,'[]'); +select * from numrange_test where nr < numrange(1000.0, 1001.0,'[]'); +select * from numrange_test where nr > numrange(-1001.0, -1000.0,'[]'); +select * from numrange_test where nr > numrange(0.0, 1.0,'[]'); +select * from numrange_test where nr > numrange(1000.0, 1000.0,'[]'); select numrange(2.0, 1.0); select numrange(2.0, 3.0) -|- numrange(3.0, 4.0); select range_adjacent(numrange(2.0, 3.0), numrange(3.1, 4.0)); +select range_adjacent(numrange(2.0, 3.0), numrange(3.1, null)); select numrange(2.0, 3.0, '[]') -|- numrange(3.0, 4.0, '()'); select numrange(1.0, 2.0) -|- numrange(2.0, 3.0,'[]'); select range_adjacent(numrange(2.0, 3.0, '(]'), numrange(1.0, 2.0, '(]')); @@ -102,15 +110,9 @@ select numrange(1.0, 2.0) * numrange(2.0, 3.0); select numrange(1.0, 2.0) * numrange(1.5, 3.0); select numrange(1.0, 2.0) * numrange(2.5, 3.0); -select * from numrange_test where nr < numrange(-1000.0, -1000.0,'[]'); -select * from numrange_test where nr < numrange(0.0, 1.0,'[]'); -select * from numrange_test where nr < numrange(1000.0, 1001.0,'[]'); -select * from numrange_test where nr > numrange(-1001.0, -1000.0,'[]'); -select * from numrange_test where nr > numrange(0.0, 1.0,'[]'); -select * from numrange_test where nr > numrange(1000.0, 1000.0,'[]'); - create table numrange_test2(nr numrange); create index numrange_test2_hash_idx on numrange_test2 (nr); + INSERT INTO numrange_test2 VALUES('[, 5)'); INSERT INTO numrange_test2 VALUES(numrange(1.1, 2.2)); INSERT INTO numrange_test2 VALUES(numrange(1.1, 2.2)); @@ -137,21 +139,24 @@ select * from numrange_test natural join numrange_test2 order by nr; set enable_nestloop to default; set enable_hashjoin to default; set enable_mergejoin to default; -SET enable_seqscan TO DEFAULT; + DROP TABLE numrange_test; DROP TABLE numrange_test2; -- test canonical form for int4range -select int4range(1,10,'[]'); -select int4range(1,10,'[)'); -select int4range(1,10,'(]'); -select int4range(1,10,'[]'); +select int4range(1, 10, '[]'); +select int4range(1, 10, '[)'); +select int4range(1, 10, '(]'); +select int4range(1, 10, '()'); +select int4range(1, 2, '()'); -- test canonical form for daterange -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,'(]'); -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, '[)'); +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-11'::date, '()'); +select daterange('2000-01-10'::date, '2000-01-11'::date, '(]'); -- test GiST index that's been built incrementally create table test_range_gist(ir int4range); @@ -238,11 +243,11 @@ insert into test_range_excl insert into test_range_excl values(int4range(123), int4range(2), '[2010-01-02 11:00, 2010-01-02 12:00)'); insert into test_range_excl - values(int4range(123), int4range(3), '[2010-01-02 10:10, 2010-01-02 11:10)'); + values(int4range(123), int4range(3), '[2010-01-02 10:10, 2010-01-02 11:00)'); insert into test_range_excl values(int4range(124), int4range(3), '[2010-01-02 10:10, 2010-01-02 11:10)'); insert into test_range_excl - values(int4range(125), int4range(1), '[2010-01-02 10:10, 2010-01-02 11:10)'); + values(int4range(125), int4range(1), '[2010-01-02 10:10, 2010-01-02 11:00)'); -- test bigint ranges select int8range(10000000000::int8, 20000000000::int8,'(]'); @@ -264,7 +269,7 @@ create type float8range as range (subtype=float8, subtype_diff=float4mi); create type float8range as range (subtype=float8, subtype_diff=float8mi); select '[123.001, 5.e9)'::float8range @> 888.882::float8; create table float8range_test(f8r float8range, i int); -insert into float8range_test values(float8range(-100.00007, '1.111113e9')); +insert into float8range_test values(float8range(-100.00007, '1.111113e9'), 42); select * from float8range_test; drop table float8range_test; @@ -275,14 +280,14 @@ drop table float8range_test; create domain mydomain as int4; create type mydomainrange as range(subtype=mydomain); select '[4,50)'::mydomainrange @> 7::mydomain; -drop type mydomainrange; -drop domain mydomain; +drop domain mydomain; -- fail +drop domain mydomain cascade; -- -- Test domains over range types -- -create domain restrictedrange as int4range check (upper(value) < 10); +create domain restrictedrange as int4range check (upper(value) < 10); select '[4,5)'::restrictedrange @> 7; select '[4,50)'::restrictedrange @> 7; -- should fail drop domain restrictedrange; @@ -301,7 +306,7 @@ drop type textrange1; drop type textrange2; -- --- Test out polymorphic type system +-- Test polymorphic type system -- create function anyarray_anyrange_func(a anyarray, r anyrange) @@ -325,6 +330,7 @@ create function bogus_func(int) create function range_add_bounds(anyrange) returns anyelement as 'select lower($1) + upper($1)' language sql; +select range_add_bounds(int4range(1, 17)); select range_add_bounds(numrange(1.0001, 123.123)); create function rangetypes_sql(q anyrange, b anyarray, out c anyelement) @@ -352,6 +358,7 @@ drop table i8r_array; create type arrayrange as range (subtype=int4[]); select arrayrange(ARRAY[1,2], ARRAY[2,1]); +select arrayrange(ARRAY[2,1], ARRAY[1,2]); -- fail select array[1,1] <@ arrayrange(array[1,2], array[2,1]); select array[1,3] <@ arrayrange(array[1,2], array[2,1]); @@ -361,14 +368,20 @@ select array[1,3] <@ arrayrange(array[1,2], array[2,1]); -- create function outparam_succeed(i anyrange, out r anyrange, out t text) - as $$ select $1, 'foo' $$ language sql; + as $$ select $1, 'foo'::text $$ language sql; + +select * from outparam_succeed(int4range(1,2)); create function inoutparam_succeed(out i anyelement, inout r anyrange) - as $$ select $1, $2 $$ language sql; + as $$ select upper($1), $1 $$ language sql; + +select * from inoutparam_succeed(int4range(1,2)); create function table_succeed(i anyelement, r anyrange) returns table(i anyelement, r anyrange) as $$ select $1, $2 $$ language sql; +select * from table_succeed(123, int4range(1,11)); + -- should fail create function outparam_fail(i anyelement, out r anyrange, out t text) as $$ select '[1,10]', 'foo' $$ language sql; @@ -378,5 +391,5 @@ create function inoutparam_fail(inout i anyelement, out r anyrange) as $$ select $1, '[1,10]' $$ language sql; --should fail -create function table_succeed(i anyelement) returns table(i anyelement, r anyrange) +create function table_fail(i anyelement) returns table(i anyelement, r anyrange) as $$ select $1, '[1,10]' $$ language sql; -- 2.30.2