On Tue, Sep 17, 2019 at 5:18 AM David Fetter <da...@fetter.org> wrote: > It's not done by pull request at this time. Instead, it is done by sending > patches to this mailing list.
Dear all You will find enclosed the patch that extends the range type operators so they cope with elements. Any comments most welcome. Esteban
diff -urdN postgresql-11.5-orig/doc/src/sgml/func.sgml postgresql-11.5-ranges/doc/src/sgml/func.sgml --- postgresql-11.5-orig/doc/src/sgml/func.sgml 2019-09-21 11:28:11.836309263 +0200 +++ postgresql-11.5-ranges/doc/src/sgml/func.sgml 2019-09-21 10:32:53.320004000 +0200 @@ -13228,6 +13228,20 @@ </row> <row> + <entry> <literal><<</literal> </entry> + <entry>strictly left of element</entry> + <entry><literal>int8range(1,10) << 100</literal></entry> + <entry><literal>t</literal></entry> + </row> + + <row> + <entry> <literal><<</literal> </entry> + <entry>element strictly left of</entry> + <entry><literal>10 << int8range(100,110)</literal></entry> + <entry><literal>t</literal></entry> + </row> + + <row> <entry> <literal>>></literal> </entry> <entry>strictly right of</entry> <entry><literal>int8range(50,60) >> int8range(20,30)</literal></entry> @@ -13235,6 +13249,20 @@ </row> <row> + <entry> <literal>>></literal> </entry> + <entry>strictly right of element</entry> + <entry><literal>int8range(50,60) >> 20</literal></entry> + <entry><literal>t</literal></entry> + </row> + + <row> + <entry> <literal>>></literal> </entry> + <entry>element strictly right of</entry> + <entry><literal>50 >> int8range(20,30)</literal></entry> + <entry><literal>t</literal></entry> + </row> + + <row> <entry> <literal>&<</literal> </entry> <entry>does not extend to the right of</entry> <entry><literal>int8range(1,20) &< int8range(18,20)</literal></entry> @@ -13242,6 +13270,20 @@ </row> <row> + <entry> <literal>&<</literal> </entry> + <entry>does not extend to the right of element</entry> + <entry><literal>int8range(1,20) &< 20</literal></entry> + <entry><literal>t</literal></entry> + </row> + + <row> + <entry> <literal>&<</literal> </entry> + <entry>element does not extend to the right of</entry> + <entry><literal>19 &< int8range(18,20)</literal></entry> + <entry><literal>t</literal></entry> + </row> + + <row> <entry> <literal>&></literal> </entry> <entry>does not extend to the left of</entry> <entry><literal>int8range(7,20) &> int8range(5,10)</literal></entry> @@ -13249,12 +13291,40 @@ </row> <row> + <entry> <literal>&></literal> </entry> + <entry>does not extend to the left of element</entry> + <entry><literal>int8range(7,20) &> 5</literal></entry> + <entry><literal>t</literal></entry> + </row> + + <row> + <entry> <literal>&></literal> </entry> + <entry>element does not extend to the left of</entry> + <entry><literal>7 &> int8range(5,10)</literal></entry> + <entry><literal>t</literal></entry> + </row> + + <row> <entry> <literal>-|-</literal> </entry> <entry>is adjacent to</entry> <entry><literal>numrange(1.1,2.2) -|- numrange(2.2,3.3)</literal></entry> <entry><literal>t</literal></entry> </row> + <row> + <entry> <literal>-|-</literal> </entry> + <entry>is adjacent to element</entry> + <entry><literal>numrange(1.1,2.2) -|- 2.2</literal></entry> + <entry><literal>t</literal></entry> + </row> + + <row> + <entry> <literal>-|-</literal> </entry> + <entry>element is adjacent to</entry> + <entry><literal>2.2 -|- numrange(2.2,3.3, '()')</literal></entry> + <entry><literal>t</literal></entry> + </row> + <row> <entry> <literal>+</literal> </entry> <entry>union</entry> diff -urdN postgresql-11.5-orig/src/backend/utils/adt/rangetypes.c postgresql-11.5-ranges/src/backend/utils/adt/rangetypes.c --- postgresql-11.5-orig/src/backend/utils/adt/rangetypes.c 2019-09-21 11:28:11.628205263 +0200 +++ postgresql-11.5-ranges/src/backend/utils/adt/rangetypes.c 2019-09-21 10:32:53.320004000 +0200 @@ -548,6 +548,322 @@ PG_RETURN_BOOL(range_contains_elem_internal(typcache, r, val)); } +/* strictly left of element? (internal version) */ +bool +range_before_elem_internal(TypeCacheEntry *typcache, RangeType *r, Datum val) +{ + RangeBound lower, + upper; + bool empty; + int32 cmp; + + range_deserialize(typcache, r, &lower, &upper, &empty); + + /* An empty range is neither left nor right any other range */ + if (empty) + return false; + + if (!upper.infinite) + { + cmp = DatumGetInt32(FunctionCall2Coll(&typcache->rng_cmp_proc_finfo, + typcache->rng_collation, + upper.val, val)); + if (cmp < 0 || + (cmp == 0 && !upper.inclusive)) + return true; + } + + return false; +} + +/* strictly left of element? */ +Datum +range_before_elem(PG_FUNCTION_ARGS) +{ + RangeType *r = PG_GETARG_RANGE_P(0); + Datum val = PG_GETARG_DATUM(1); + TypeCacheEntry *typcache; + + typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r)); + + PG_RETURN_BOOL(range_before_elem_internal(typcache, r, val)); +} + +/* does not extend to right of element? (internal version) */ +bool +range_overleft_elem_internal(TypeCacheEntry *typcache, RangeType *r, Datum val) +{ + RangeBound lower, + upper; + bool empty; + + range_deserialize(typcache, r, &lower, &upper, &empty); + + /* An empty range is neither left nor right any element */ + if (empty) + return false; + + if (!upper.infinite) + { + if (DatumGetInt32(FunctionCall2Coll(&typcache->rng_cmp_proc_finfo, + typcache->rng_collation, + upper.val, val)) <= 0) + return true; + } + + return false; +} + +/* does not extend to right of element? */ +Datum +range_overleft_elem(PG_FUNCTION_ARGS) +{ + RangeType *r = PG_GETARG_RANGE_P(0); + Datum val = PG_GETARG_DATUM(1); + TypeCacheEntry *typcache; + + typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r)); + + PG_RETURN_BOOL(range_overleft_elem_internal(typcache, r, val)); +} + +/* strictly right of element? (internal version) */ +bool +range_after_elem_internal(TypeCacheEntry *typcache, RangeType *r, Datum val) +{ + RangeBound lower, + upper; + bool empty; + int32 cmp; + + range_deserialize(typcache, r, &lower, &upper, &empty); + + /* An empty range is neither left nor right any other range */ + if (empty) + return false; + + if (!lower.infinite) + { + cmp = DatumGetInt32(FunctionCall2Coll(&typcache->rng_cmp_proc_finfo, + typcache->rng_collation, + lower.val, val)); + if (cmp > 0 || + (cmp == 0 && !lower.inclusive)) + return true; + } + + return false; +} + +/* strictly right of element? */ +Datum +range_after_elem(PG_FUNCTION_ARGS) +{ + RangeType *r = PG_GETARG_RANGE_P(0); + Datum val = PG_GETARG_DATUM(1); + TypeCacheEntry *typcache; + + typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r)); + + PG_RETURN_BOOL(range_after_elem_internal(typcache, r, val)); +} + +/* does not extend to left of element? (internal version) */ +bool +range_overright_elem_internal(TypeCacheEntry *typcache, RangeType *r, Datum val) +{ + RangeBound lower, + upper; + bool empty; + + range_deserialize(typcache, r, &lower, &upper, &empty); + + /* An empty range is neither left nor right any element */ + if (empty) + return false; + + if (!lower.infinite) + { + if (DatumGetInt32(FunctionCall2Coll(&typcache->rng_cmp_proc_finfo, + typcache->rng_collation, + lower.val, val)) >= 0) + return true; + } + + return false; +} + +/* does not extend to left of element? */ +Datum +range_overright_elem(PG_FUNCTION_ARGS) +{ + RangeType *r = PG_GETARG_RANGE_P(0); + Datum val = PG_GETARG_DATUM(1); + TypeCacheEntry *typcache; + + typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r)); + + PG_RETURN_BOOL(range_overright_elem_internal(typcache, r, val)); +} + +/* adjacent to element (but not overlapping)? (internal version) */ +bool +range_adjacent_elem_internal(TypeCacheEntry *typcache, RangeType *r, Datum val) +{ + RangeBound lower, + upper; + bool empty; + RangeBound elembound; + bool isadj; + + range_deserialize(typcache, r, &lower, &upper, &empty); + + /* An empty range is not adjacent to any element */ + if (empty) + return false; + + /* + * A range A..B and a value V are adjacent if and only if + * B is adjacent to V, or V is adjacent to A. + */ + elembound.val = val; + elembound.infinite = false; + elembound.inclusive = true; + elembound.lower = true; + isadj = bounds_adjacent(typcache, upper, elembound); + elembound.lower = false; + return (isadj || bounds_adjacent(typcache, elembound, lower)); +} + +/* adjacent to element (but not overlapping)? */ +Datum +range_adjacent_elem(PG_FUNCTION_ARGS) +{ + RangeType *r = PG_GETARG_RANGE_P(0); + Datum val = PG_GETARG_DATUM(1); + TypeCacheEntry *typcache; + + typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r)); + + PG_RETURN_BOOL(range_adjacent_elem_internal(typcache, r, val)); +} + +/******************************************************************************/ + +/* element strictly left of? */ +Datum +elem_before_range(PG_FUNCTION_ARGS) +{ + Datum val = PG_GETARG_DATUM(0); + RangeType *r = PG_GETARG_RANGE_P(1); + TypeCacheEntry *typcache; + + typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r)); + + PG_RETURN_BOOL(range_after_elem_internal(typcache, r, val)); +} + +/* element does not extend to right of? (internal version) */ +bool +elem_overleft_range_internal(TypeCacheEntry *typcache, Datum val, RangeType *r) +{ + RangeBound lower, + upper; + bool empty; + + range_deserialize(typcache, r, &lower, &upper, &empty); + + /* An empty range is neither left nor right any element */ + if (empty) + return false; + + if (!upper.infinite) + { + if (DatumGetInt32(FunctionCall2Coll(&typcache->rng_cmp_proc_finfo, + typcache->rng_collation, + val, upper.val)) <= 0) + return true; + } + + return false; +} + +/* element does not extend to right of? */ +Datum +elem_overleft_range(PG_FUNCTION_ARGS) +{ + Datum val = PG_GETARG_DATUM(0); + RangeType *r = PG_GETARG_RANGE_P(1); + TypeCacheEntry *typcache; + + typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r)); + + PG_RETURN_BOOL(elem_overleft_range_internal(typcache, val, r)); +} + +/* element strictly right of? */ +Datum +elem_after_range(PG_FUNCTION_ARGS) +{ + Datum val = PG_GETARG_DATUM(0); + RangeType *r = PG_GETARG_RANGE_P(1); + TypeCacheEntry *typcache; + + typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r)); + + PG_RETURN_BOOL(range_before_elem_internal(typcache, r, val)); +} + +/* element does not extend to left of? (internal version) */ +bool +elem_overright_range_internal(TypeCacheEntry *typcache, Datum val, RangeType *r) +{ + RangeBound lower, + upper; + bool empty; + + range_deserialize(typcache, r, &lower, &upper, &empty); + + /* An empty range is neither left nor right any element */ + if (empty) + return false; + + if (!lower.infinite) + { + if (DatumGetInt32(FunctionCall2Coll(&typcache->rng_cmp_proc_finfo, + typcache->rng_collation, + val, lower.val)) >= 0) + return true; + } + + return false; +} + +/* element does not extend the left of? */ +Datum +elem_overright_range(PG_FUNCTION_ARGS) +{ + Datum val = PG_GETARG_DATUM(0); + RangeType *r = PG_GETARG_RANGE_P(1); + TypeCacheEntry *typcache; + + typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r)); + + PG_RETURN_BOOL(elem_overright_range_internal(typcache, val, r)); +} + +/* element adjacent to? */ +Datum +elem_adjacent_range(PG_FUNCTION_ARGS) +{ + Datum val = PG_GETARG_DATUM(0); + RangeType *r = PG_GETARG_RANGE_P(1); + TypeCacheEntry *typcache; + + typcache = range_get_typcache(fcinfo, RangeTypeGetOid(r)); + + PG_RETURN_BOOL(range_adjacent_elem_internal(typcache, r, val)); +} /* range, range -> bool functions */ diff -urdN postgresql-11.5-orig/src/include/catalog/pg_operator.dat postgresql-11.5-ranges/src/include/catalog/pg_operator.dat --- postgresql-11.5-orig/src/include/catalog/pg_operator.dat 2019-09-21 11:28:11.484133263 +0200 +++ postgresql-11.5-ranges/src/include/catalog/pg_operator.dat 2019-09-21 17:05:20.566965777 +0200 @@ -3336,5 +3336,59 @@ { oid => '3287', descr => 'delete path', oprname => '#-', oprleft => 'jsonb', oprright => '_text', oprresult => 'jsonb', oprcode => 'jsonb_delete_path' }, +{ oid => '4001', oid_symbol => 'OID_RANGE_ADJACENT_ELEM_OP', + descr => 'range is adjacent to element', + oprname => '-|-', oprleft => 'anyrange', oprright => 'anyelement', + oprresult => 'bool', oprcom => '-|-(anyelement,anyrange)', + oprcode => 'range_adjacent_elem', oprrest => 'contsel', oprjoin => 'contjoinsel' }, +{ oid => '4002', oid_symbol => 'OID_ELEM_ADJACENT_RANGE_OP', + descr => 'element is adjacent to range', + oprname => '-|-', oprleft => 'anyelement', oprright => 'anyrange', + oprresult => 'bool', oprcom => '-|-(anyrange,anyelement)', + oprcode => 'elem_adjacent_range', oprrest => 'contsel', oprjoin => 'contjoinsel' }, +{ oid => '4003', oid_symbol => 'OID_RANGE_LEFT_ELEM_OP', + descr => 'is left of element', + oprname => '<<', oprleft => 'anyrange', oprright => 'anyelement', + oprresult => 'bool', oprcom => '>>(anyelement,anyrange)', + oprcode => 'range_before_elem', oprrest => 'rangesel', + oprjoin => 'scalarltjoinsel' }, +{ oid => '4004', oid_symbol => 'OID_ELEM_LEFT_RANGE_OP', + descr => 'element is left of range', + oprname => '<<', oprleft => 'anyelement', oprright => 'anyrange', + oprresult => 'bool', oprcom => '>>(anyrange,anyelement)', + oprcode => 'elem_before_range', oprrest => 'rangesel', + oprjoin => 'scalarltjoinsel' }, +{ oid => '4005', oid_symbol => 'OID_RANGE_RIGHT_ELEM_OP', + descr => 'is right of element', + oprname => '>>', oprleft => 'anyrange', oprright => 'anyelement', + oprresult => 'bool', oprcom => '<<(anyelement,anyrange)', + oprcode => 'range_after_elem', oprrest => 'rangesel', + oprjoin => 'scalargtjoinsel' }, +{ oid => '4006', oid_symbol => 'OID_ELEM_RIGHT_RANGE_OP', + descr => 'element is right of range', + oprname => '>>', oprleft => 'anyelement', oprright => 'anyrange', + oprresult => 'bool', oprcom => '<<(anyrange,anyelement)', + oprcode => 'elem_after_range', oprrest => 'rangesel', + oprjoin => 'scalargtjoinsel' }, +{ oid => '4007', oid_symbol => 'OID_RANGE_OVERLAPS_LEFT_ELEM_OP', + descr => 'overlaps or is left of element', + oprname => '&<', oprleft => 'anyrange', oprright => 'anyelement', + oprresult => 'bool', oprcode => 'range_overleft_elem', oprrest => 'rangesel', + oprjoin => 'scalarltjoinsel' }, +{ oid => '4008', oid_symbol => 'OID_ELEM_OVERLAPS_LEFT_RANGE_OP', + descr => 'element overlaps or is left of range', + oprname => '&<', oprleft => 'anyelement', oprright => 'anyrange', + oprresult => 'bool', oprcode => 'elem_overleft_range', oprrest => 'rangesel', + oprjoin => 'scalarltjoinsel' }, +{ oid => '4009', oid_symbol => 'OID_RANGE_OVERLAPS_RIGHT_ELEM_OP', + descr => 'overlaps or is right of element', + oprname => '&>', oprleft => 'anyrange', oprright => 'anyelement', + oprresult => 'bool', oprcode => 'range_overright_elem', oprrest => 'rangesel', + oprjoin => 'scalarltjoinsel' }, +{ oid => '4010', oid_symbol => 'OID_ELEM_OVERLAPS_RIGHT_RANGE_OP', + descr => 'element overlaps or is right of range', + oprname => '&>', oprleft => 'anyelement', oprright => 'anyrange', + oprresult => 'bool', oprcode => 'elem_overright_range', oprrest => 'rangesel', + oprjoin => 'scalarltjoinsel' }, ] diff -urdN postgresql-11.5-orig/src/include/catalog/pg_proc.dat postgresql-11.5-ranges/src/include/catalog/pg_proc.dat --- postgresql-11.5-orig/src/include/catalog/pg_proc.dat 2019-09-21 11:28:11.476129264 +0200 +++ postgresql-11.5-ranges/src/include/catalog/pg_proc.dat 2019-09-21 16:00:11.649542000 +0200 @@ -9591,6 +9591,36 @@ { oid => '3169', descr => 'restriction selectivity for range operators', proname => 'rangesel', provolatile => 's', prorettype => 'float8', proargtypes => 'internal oid internal int4', prosrc => 'rangesel' }, +{ oid => '4142', + proname => 'range_adjacent_elem', prorettype => 'bool', + proargtypes => 'anyrange anyelement', prosrc => 'range_adjacent_elem' }, +{ oid => '4143', + proname => 'elem_adjacent_range', prorettype => 'bool', + proargtypes => 'anyelement anyrange', prosrc => 'elem_adjacent_range' }, +{ oid => '4144', + proname => 'range_before_elem', prorettype => 'bool', + proargtypes => 'anyrange anyelement', prosrc => 'range_before_elem' }, +{ oid => '4145', + proname => 'elem_before_range', prorettype => 'bool', + proargtypes => 'anyelement anyrange', prosrc => 'elem_before_range' }, +{ oid => '4146', + proname => 'range_after_elem', prorettype => 'bool', + proargtypes => 'anyrange anyelement', prosrc => 'range_after_elem' }, +{ oid => '4147', + proname => 'elem_after_range', prorettype => 'bool', + proargtypes => 'anyelement anyrange', prosrc => 'elem_after_range' }, +{ oid => '4148', + proname => 'range_overleft_elem', prorettype => 'bool', + proargtypes => 'anyrange anyelement', prosrc => 'range_overleft_elem' }, +{ oid => '4149', + proname => 'elem_overleft_range', prorettype => 'bool', + proargtypes => 'anyelement anyrange', prosrc => 'elem_overleft_range' }, +{ oid => '4150', + proname => 'range_overright_elem', prorettype => 'bool', + proargtypes => 'anyrange anyelement', prosrc => 'range_overright_elem' }, +{ oid => '4151', + proname => 'elem_overright_range', prorettype => 'bool', + proargtypes => 'anyelement anyrange', prosrc => 'elem_overright_range' }, { oid => '3914', descr => 'convert an int4 range to canonical form', proname => 'int4range_canonical', prorettype => 'int4range', diff -urdN postgresql-11.5-orig/src/include/utils/rangetypes.h postgresql-11.5-ranges/src/include/utils/rangetypes.h --- postgresql-11.5-orig/src/include/utils/rangetypes.h 2019-09-21 11:28:11.472127264 +0200 +++ postgresql-11.5-ranges/src/include/utils/rangetypes.h 2019-09-21 10:32:53.320004000 +0200 @@ -94,6 +94,18 @@ extern bool range_contains_elem_internal(TypeCacheEntry *typcache, RangeType *r, Datum val); +extern bool range_before_elem_internal(TypeCacheEntry *typcache, RangeType *r, Datum val); +extern bool range_overleft_elem_internal(TypeCacheEntry *typcache, RangeType *r, Datum val); +extern bool range_after_elem_internal(TypeCacheEntry *typcache, RangeType *r, Datum val); +extern bool range_overright_elem_internal(TypeCacheEntry *typcache, RangeType *r, Datum val); +extern bool range_adjacent_elem_internal(TypeCacheEntry *typcache, RangeType *r, Datum val); + +extern bool elem_before_range_internal(TypeCacheEntry *typcache, Datum r, RangeType *val); +extern bool elem_overleft_range_internal(TypeCacheEntry *typcache, Datum r, RangeType *val); +extern bool elem_after_range_internal(TypeCacheEntry *typcache, Datum r, RangeType *val); +extern bool elem_overright_range_internal(TypeCacheEntry *typcache, Datum r, RangeType *val); +extern bool elem_adjacent_range_internal(TypeCacheEntry *typcache, Datum r, RangeType *val); + /* internal versions of the above */ extern bool range_eq_internal(TypeCacheEntry *typcache, RangeType *r1, RangeType *r2); diff -urdN postgresql-11.5-orig/src/test/regress/expected/rangetypes.out postgresql-11.5-ranges/src/test/regress/expected/rangetypes.out --- postgresql-11.5-orig/src/test/regress/expected/rangetypes.out 2019-09-21 11:28:11.536159263 +0200 +++ postgresql-11.5-ranges/src/test/regress/expected/rangetypes.out 2019-09-21 17:26:44.324523000 +0200 @@ -378,6 +378,18 @@ t (1 row) +select numrange(1.0, 2.0) -|- 2.0; + ?column? +---------- + t +(1 row) + +select 2.0 -|- numrange(2.0, 3.0,'()'); + ?column? +---------- + t +(1 row) + select numrange(1.1, 3.3) <@ numrange(0.1,10.1); ?column? ---------- @@ -432,6 +444,18 @@ t (1 row) +select numrange(1.0, 2.0) << 3.0; + ?column? +---------- + t +(1 row) + +select 2.0 << numrange(3.0, 4.0); + ?column? +---------- + t +(1 row) + select numrange(1.0, 3.0,'[]') << numrange(3.0, 4.0,'[]'); ?column? ---------- @@ -450,12 +474,54 @@ f (1 row) +select numrange(1.0, 2.0) >> 3.0; + ?column? +---------- + f +(1 row) + +select 2.0 >> numrange(3.0, 4.0); + ?column? +---------- + f +(1 row) + select numrange(3.0, 70.0) &< numrange(6.6, 100.0); ?column? ---------- t (1 row) +select numrange(3.0, 70.0) &< 6.6; + ?column? +---------- + f +(1 row) + +select 70.0 &< numrange(6.6, 100.0); + ?column? +---------- + t +(1 row) + +select numrange(3.0, 70.0) &> numrange(6.6, 100.0); + ?column? +---------- + f +(1 row) + +select numrange(3.0, 70.0) &> 6.6; + ?column? +---------- + f +(1 row) + +select 70.0 &> numrange(6.6, 100.0); + ?column? +---------- + t +(1 row) + select numrange(1.1, 2.2) < numrange(1.0, 200.2); ?column? ---------- @@ -732,30 +798,60 @@ 189 (1 row) +select count(*) from test_range_gist where ir << 100; + count +------- + 189 +(1 row) + select count(*) from test_range_gist where ir >> int4range(100,500); count ------- 3554 (1 row) +select count(*) from test_range_gist where ir >> 100; + count +------- + 4791 +(1 row) + select count(*) from test_range_gist where ir &< int4range(100,500); count ------- 1029 (1 row) +select count(*) from test_range_gist where ir &< 100; + count +------- + 189 +(1 row) + select count(*) from test_range_gist where ir &> int4range(100,500); count ------- 4794 (1 row) +select count(*) from test_range_gist where ir &> 100; + count +------- + 4794 +(1 row) + select count(*) from test_range_gist where ir -|- int4range(100,500); count ------- 5 (1 row) +select count(*) from test_range_gist where ir -|- 100; + count +------- + 6 +(1 row) + -- now check same queries using index SET enable_seqscan = f; SET enable_indexscan = t; @@ -802,30 +898,60 @@ 189 (1 row) +select count(*) from test_range_gist where ir << 100; + count +------- + 189 +(1 row) + select count(*) from test_range_gist where ir >> int4range(100,500); count ------- 3554 (1 row) +select count(*) from test_range_gist where ir >> 100; + count +------- + 4791 +(1 row) + select count(*) from test_range_gist where ir &< int4range(100,500); count ------- 1029 (1 row) +select count(*) from test_range_gist where ir &< 100; + count +------- + 189 +(1 row) + select count(*) from test_range_gist where ir &> int4range(100,500); count ------- 4794 (1 row) +select count(*) from test_range_gist where ir &> 100; + count +------- + 4794 +(1 row) + select count(*) from test_range_gist where ir -|- int4range(100,500); count ------- 5 (1 row) +select count(*) from test_range_gist where ir -|- 100; + count +------- + 6 +(1 row) + -- now check same queries using a bulk-loaded index drop index test_range_gist_idx; create index test_range_gist_idx on test_range_gist using gist (ir); @@ -871,30 +997,60 @@ 189 (1 row) +select count(*) from test_range_gist where ir << 100; + count +------- + 189 +(1 row) + select count(*) from test_range_gist where ir >> int4range(100,500); count ------- 3554 (1 row) +select count(*) from test_range_gist where ir >> 100; + count +------- + 4791 +(1 row) + select count(*) from test_range_gist where ir &< int4range(100,500); count ------- 1029 (1 row) +select count(*) from test_range_gist where ir &< 100; + count +------- + 189 +(1 row) + select count(*) from test_range_gist where ir &> int4range(100,500); count ------- 4794 (1 row) +select count(*) from test_range_gist where ir &> 100; + count +------- + 4794 +(1 row) + select count(*) from test_range_gist where ir -|- int4range(100,500); count ------- 5 (1 row) +select count(*) from test_range_gist where ir -|- 100; + count +------- + 6 +(1 row) + -- test SP-GiST index that's been built incrementally create table test_range_spgist(ir int4range); create index test_range_spgist_idx on test_range_spgist using spgist (ir); @@ -951,30 +1107,60 @@ 189 (1 row) +select count(*) from test_range_spgist where ir << 100; + count +------- + 189 +(1 row) + select count(*) from test_range_spgist where ir >> int4range(100,500); count ------- 3554 (1 row) +select count(*) from test_range_spgist where ir >> 100; + count +------- + 4791 +(1 row) + select count(*) from test_range_spgist where ir &< int4range(100,500); count ------- 1029 (1 row) +select count(*) from test_range_spgist where ir &< 100; + count +------- + 189 +(1 row) + select count(*) from test_range_spgist where ir &> int4range(100,500); count ------- 4794 (1 row) +select count(*) from test_range_spgist where ir &> 100; + count +------- + 4794 +(1 row) + select count(*) from test_range_spgist where ir -|- int4range(100,500); count ------- 5 (1 row) +select count(*) from test_range_spgist where ir -|- 100; + count +------- + 6 +(1 row) + -- now check same queries using index SET enable_seqscan = f; SET enable_indexscan = t; @@ -1021,30 +1207,60 @@ 189 (1 row) +select count(*) from test_range_spgist where ir << 100; + count +------- + 189 +(1 row) + select count(*) from test_range_spgist where ir >> int4range(100,500); count ------- 3554 (1 row) +select count(*) from test_range_spgist where ir >> 100; + count +------- + 4791 +(1 row) + select count(*) from test_range_spgist where ir &< int4range(100,500); count ------- 1029 (1 row) +select count(*) from test_range_spgist where ir &< 100; + count +------- + 189 +(1 row) + select count(*) from test_range_spgist where ir &> int4range(100,500); count ------- 4794 (1 row) +select count(*) from test_range_spgist where ir &> 100; + count +------- + 4794 +(1 row) + select count(*) from test_range_spgist where ir -|- int4range(100,500); count ------- 5 (1 row) +select count(*) from test_range_spgist where ir -|- 100; + count +------- + 6 +(1 row) + -- now check same queries using a bulk-loaded index drop index test_range_spgist_idx; create index test_range_spgist_idx on test_range_spgist using spgist (ir); @@ -1090,30 +1306,60 @@ 189 (1 row) +select count(*) from test_range_spgist where ir << 100; + count +------- + 189 +(1 row) + select count(*) from test_range_spgist where ir >> int4range(100,500); count ------- 3554 (1 row) +select count(*) from test_range_spgist where ir >> 100; + count +------- + 4791 +(1 row) + select count(*) from test_range_spgist where ir &< int4range(100,500); count ------- 1029 (1 row) +select count(*) from test_range_spgist where ir &< 100; + count +------- + 189 +(1 row) + select count(*) from test_range_spgist where ir &> int4range(100,500); count ------- 4794 (1 row) +select count(*) from test_range_spgist where ir &> 100; + count +------- + 4794 +(1 row) + select count(*) from test_range_spgist where ir -|- int4range(100,500); count ------- 5 (1 row) +select count(*) from test_range_spgist where ir -|- 100; + count +------- + 6 +(1 row) + -- test index-only scans explain (costs off) select ir from test_range_spgist where ir -|- int4range(10,20) order by ir; diff -urdN postgresql-11.5-orig/src/test/regress/sql/rangetypes.sql postgresql-11.5-ranges/src/test/regress/sql/rangetypes.sql --- postgresql-11.5-orig/src/test/regress/sql/rangetypes.sql 2019-09-21 11:28:11.512147263 +0200 +++ postgresql-11.5-ranges/src/test/regress/sql/rangetypes.sql 2019-09-21 10:32:53.320004000 +0200 @@ -87,6 +87,9 @@ select numrange(1.0, 2.0) -|- numrange(2.0, 3.0,'[]'); select range_adjacent(numrange(2.0, 3.0, '(]'), numrange(1.0, 2.0, '(]')); +select numrange(1.0, 2.0) -|- 2.0; +select 2.0 -|- numrange(2.0, 3.0,'()'); + select numrange(1.1, 3.3) <@ numrange(0.1,10.1); select numrange(0.1, 10.1) <@ numrange(1.1,3.3); @@ -98,10 +101,19 @@ select numrange(4.5, 5.5, '[]') && numrange(5.5, 6.5); select numrange(1.0, 2.0) << numrange(3.0, 4.0); +select numrange(1.0, 2.0) << 3.0; +select 2.0 << numrange(3.0, 4.0); select numrange(1.0, 3.0,'[]') << numrange(3.0, 4.0,'[]'); select numrange(1.0, 3.0,'()') << numrange(3.0, 4.0,'()'); select numrange(1.0, 2.0) >> numrange(3.0, 4.0); +select numrange(1.0, 2.0) >> 3.0; +select 2.0 >> numrange(3.0, 4.0); select numrange(3.0, 70.0) &< numrange(6.6, 100.0); +select numrange(3.0, 70.0) &< 6.6; +select 70.0 &< numrange(6.6, 100.0); +select numrange(3.0, 70.0) &> numrange(6.6, 100.0); +select numrange(3.0, 70.0) &> 6.6; +select 70.0 &> numrange(6.6, 100.0); select numrange(1.1, 2.2) < numrange(1.0, 200.2); select numrange(1.1, 2.2) < numrange(1.1, 1.2); @@ -194,10 +206,15 @@ select count(*) from test_range_gist where ir && int4range(10,20); select count(*) from test_range_gist where ir <@ int4range(10,50); select count(*) from test_range_gist where ir << int4range(100,500); +select count(*) from test_range_gist where ir << 100; select count(*) from test_range_gist where ir >> int4range(100,500); +select count(*) from test_range_gist where ir >> 100; select count(*) from test_range_gist where ir &< int4range(100,500); +select count(*) from test_range_gist where ir &< 100; select count(*) from test_range_gist where ir &> int4range(100,500); +select count(*) from test_range_gist where ir &> 100; select count(*) from test_range_gist where ir -|- int4range(100,500); +select count(*) from test_range_gist where ir -|- 100; -- now check same queries using index SET enable_seqscan = f; @@ -211,10 +228,15 @@ select count(*) from test_range_gist where ir && int4range(10,20); select count(*) from test_range_gist where ir <@ int4range(10,50); select count(*) from test_range_gist where ir << int4range(100,500); +select count(*) from test_range_gist where ir << 100; select count(*) from test_range_gist where ir >> int4range(100,500); +select count(*) from test_range_gist where ir >> 100; select count(*) from test_range_gist where ir &< int4range(100,500); +select count(*) from test_range_gist where ir &< 100; select count(*) from test_range_gist where ir &> int4range(100,500); +select count(*) from test_range_gist where ir &> 100; select count(*) from test_range_gist where ir -|- int4range(100,500); +select count(*) from test_range_gist where ir -|- 100; -- now check same queries using a bulk-loaded index drop index test_range_gist_idx; @@ -227,10 +249,15 @@ select count(*) from test_range_gist where ir && int4range(10,20); select count(*) from test_range_gist where ir <@ int4range(10,50); select count(*) from test_range_gist where ir << int4range(100,500); +select count(*) from test_range_gist where ir << 100; select count(*) from test_range_gist where ir >> int4range(100,500); +select count(*) from test_range_gist where ir >> 100; select count(*) from test_range_gist where ir &< int4range(100,500); +select count(*) from test_range_gist where ir &< 100; select count(*) from test_range_gist where ir &> int4range(100,500); +select count(*) from test_range_gist where ir &> 100; select count(*) from test_range_gist where ir -|- int4range(100,500); +select count(*) from test_range_gist where ir -|- 100; -- test SP-GiST index that's been built incrementally create table test_range_spgist(ir int4range); @@ -256,10 +283,15 @@ select count(*) from test_range_spgist where ir && int4range(10,20); select count(*) from test_range_spgist where ir <@ int4range(10,50); select count(*) from test_range_spgist where ir << int4range(100,500); +select count(*) from test_range_spgist where ir << 100; select count(*) from test_range_spgist where ir >> int4range(100,500); +select count(*) from test_range_spgist where ir >> 100; select count(*) from test_range_spgist where ir &< int4range(100,500); +select count(*) from test_range_spgist where ir &< 100; select count(*) from test_range_spgist where ir &> int4range(100,500); +select count(*) from test_range_spgist where ir &> 100; select count(*) from test_range_spgist where ir -|- int4range(100,500); +select count(*) from test_range_spgist where ir -|- 100; -- now check same queries using index SET enable_seqscan = f; @@ -273,10 +305,15 @@ select count(*) from test_range_spgist where ir && int4range(10,20); select count(*) from test_range_spgist where ir <@ int4range(10,50); select count(*) from test_range_spgist where ir << int4range(100,500); +select count(*) from test_range_spgist where ir << 100; select count(*) from test_range_spgist where ir >> int4range(100,500); +select count(*) from test_range_spgist where ir >> 100; select count(*) from test_range_spgist where ir &< int4range(100,500); +select count(*) from test_range_spgist where ir &< 100; select count(*) from test_range_spgist where ir &> int4range(100,500); +select count(*) from test_range_spgist where ir &> 100; select count(*) from test_range_spgist where ir -|- int4range(100,500); +select count(*) from test_range_spgist where ir -|- 100; -- now check same queries using a bulk-loaded index drop index test_range_spgist_idx; @@ -289,10 +326,15 @@ select count(*) from test_range_spgist where ir && int4range(10,20); select count(*) from test_range_spgist where ir <@ int4range(10,50); select count(*) from test_range_spgist where ir << int4range(100,500); +select count(*) from test_range_spgist where ir << 100; select count(*) from test_range_spgist where ir >> int4range(100,500); +select count(*) from test_range_spgist where ir >> 100; select count(*) from test_range_spgist where ir &< int4range(100,500); +select count(*) from test_range_spgist where ir &< 100; select count(*) from test_range_spgist where ir &> int4range(100,500); +select count(*) from test_range_spgist where ir &> 100; select count(*) from test_range_spgist where ir -|- int4range(100,500); +select count(*) from test_range_spgist where ir -|- 100; -- test index-only scans explain (costs off)