diff --git a/src/backend/utils/adt/rangetypes_selfuncs.c b/src/backend/utils/adt/rangetypes_selfuncs.c index 7162574..bd4add9 100644 --- a/src/backend/utils/adt/rangetypes_selfuncs.c +++ b/src/backend/utils/adt/rangetypes_selfuncs.c @@ -263,45 +263,51 @@ calc_rangesel(TypeCacheEntry *typcache, VariableStatData *vardata, } if (RangeIsEmpty(constval)) { /* * An empty range matches all ranges, all empty ranges, or nothing, * depending on the operator */ switch (operator) { + /* these return false if either argument is empty */ case OID_RANGE_OVERLAP_OP: case OID_RANGE_OVERLAPS_LEFT_OP: case OID_RANGE_OVERLAPS_RIGHT_OP: case OID_RANGE_LEFT_OP: case OID_RANGE_RIGHT_OP: - /* these return false if either argument is empty */ + /* nothing is less than an empty range */ + case OID_RANGE_LESS_OP: selec = 0.0; break; + /* only empty ranges can contain an empty range */ case OID_RANGE_CONTAINED_OP: + /* nothing is less than an empty range, only empty ranges are equal */ case OID_RANGE_LESS_EQUAL_OP: - case OID_RANGE_GREATER_EQUAL_OP: - - /* - * these return true when both args are empty, false if only - * one is empty - */ selec = empty_frac; break; + /* everything contains an empty range */ case OID_RANGE_CONTAINS_OP: - /* everything contains an empty range */ + /* everything is greater than or equals to an empty range */ + case OID_RANGE_GREATER_EQUAL_OP: selec = 1.0; break; + /* everything except empty ranges are greater than an empty range */ + case OID_RANGE_GREATER_OP: + selec = 1.0 - empty_frac; + break; + + /* an element cannot be empty */ case OID_RANGE_CONTAINS_ELEM_OP: default: elog(ERROR, "unexpected operator %u", operator); selec = 0.0; /* keep compiler quiet */ break; } } else { /* @@ -436,27 +442,27 @@ calc_hist_selectivity(TypeCacheEntry *typcache, VariableStatData *vardata, case OID_RANGE_LESS_EQUAL_OP: hist_selec = calc_hist_selectivity_scalar(typcache, &const_lower, hist_lower, nhist, true); break; case OID_RANGE_GREATER_OP: hist_selec = 1 - calc_hist_selectivity_scalar(typcache, &const_lower, - hist_lower, nhist, true); + hist_lower, nhist, false); break; case OID_RANGE_GREATER_EQUAL_OP: hist_selec = 1 - calc_hist_selectivity_scalar(typcache, &const_lower, - hist_lower, nhist, false); + hist_lower, nhist, true); break; case OID_RANGE_LEFT_OP: /* var << const when upper(var) < lower(const) */ hist_selec = calc_hist_selectivity_scalar(typcache, &const_lower, hist_upper, nhist, false); break; case OID_RANGE_RIGHT_OP: diff --git a/src/include/catalog/pg_operator.h b/src/include/catalog/pg_operator.h index d7dcd1c..eb0caed 100644 --- a/src/include/catalog/pg_operator.h +++ b/src/include/catalog/pg_operator.h @@ -1713,24 +1713,24 @@ DESCR("equal"); DATA(insert OID = 3883 ( "<>" PGNSP PGUID b f f 3831 3831 16 3883 3882 range_ne neqsel neqjoinsel )); DESCR("not equal"); DATA(insert OID = 3884 ( "<" PGNSP PGUID b f f 3831 3831 16 3887 3886 range_lt rangesel scalarltjoinsel )); DESCR("less than"); #define OID_RANGE_LESS_OP 3884 DATA(insert OID = 3885 ( "<=" PGNSP PGUID b f f 3831 3831 16 3886 3887 range_le rangesel scalarltjoinsel )); DESCR("less than or equal"); #define OID_RANGE_LESS_EQUAL_OP 3885 DATA(insert OID = 3886 ( ">=" PGNSP PGUID b f f 3831 3831 16 3885 3884 range_ge rangesel scalargtjoinsel )); DESCR("greater than or equal"); -#define OID_RANGE_GREATER_OP 3886 +#define OID_RANGE_GREATER_EQUAL_OP 3886 DATA(insert OID = 3887 ( ">" PGNSP PGUID b f f 3831 3831 16 3884 3885 range_gt rangesel scalargtjoinsel )); DESCR("greater than"); -#define OID_RANGE_GREATER_EQUAL_OP 3887 +#define OID_RANGE_GREATER_OP 3887 DATA(insert OID = 3888 ( "&&" PGNSP PGUID b f f 3831 3831 16 3888 0 range_overlaps rangesel areajoinsel )); DESCR("overlaps"); #define OID_RANGE_OVERLAP_OP 3888 DATA(insert OID = 3889 ( "@>" PGNSP PGUID b f f 3831 2283 16 3891 0 range_contains_elem rangesel contjoinsel )); DESCR("contains"); #define OID_RANGE_CONTAINS_ELEM_OP 3889 DATA(insert OID = 3890 ( "@>" PGNSP PGUID b f f 3831 3831 16 3892 0 range_contains rangesel contjoinsel )); DESCR("contains"); #define OID_RANGE_CONTAINS_OP 3890 DATA(insert OID = 3891 ( "<@" PGNSP PGUID b f f 2283 3831 16 3889 0 elem_contained_by_range rangesel contjoinsel )); diff --git a/src/test/regress/expected/rangetypes.out b/src/test/regress/expected/rangetypes.out index 39db992..35d0dd3 100644 --- a/src/test/regress/expected/rangetypes.out +++ b/src/test/regress/expected/rangetypes.out @@ -253,20 +253,25 @@ select * from numrange_test where nr = '(1.1, 2.2)'; nr ---- (0 rows) select * from numrange_test where nr = '[1.1, 2.2)'; nr ----------- [1.1,2.2) (1 row) +select * from numrange_test where nr < 'empty'; + nr +---- +(0 rows) + 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 @@ -280,20 +285,47 @@ 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 <= 'empty'; + nr +------- + empty +(1 row) + +select * from numrange_test where nr >= 'empty'; + nr +----------- + (,) + [3,) + (,5) + [1.1,2.2) + empty + [1.7,1.7] +(6 rows) + +select * from numrange_test where nr > 'empty'; + nr +----------- + (,) + [3,) + (,5) + [1.1,2.2) + [1.7,1.7] +(5 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 diff --git a/src/test/regress/sql/rangetypes.sql b/src/test/regress/sql/rangetypes.sql index fad843a..aa026ca 100644 --- a/src/test/regress/sql/rangetypes.sql +++ b/src/test/regress/sql/rangetypes.sql @@ -60,23 +60,27 @@ 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 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 < 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 <= 'empty'; +select * from numrange_test where nr >= 'empty'; +select * from numrange_test where nr > 'empty'; 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, '()');