BUG #12568: upper of int4range unexpected value - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      12568
Logged by:          Damian Sepczuk
Email address:      damian@sepczuk.pl
PostgreSQL version: 9.4.0
Operating system:   Linux Mint 17.1 Cinnamon 64-bit 3.13.0-24-generic
Description:

While technically 3 is an upper bound of the integer range [1,3) ∩ ℕ = {1,
2}, so are all natural numbers ≥ 2. I would expect the 'upper' function to
return the supremum (least upper bound) of the range.
In my opinion the result of upper('[1,2]'::int4range) = 3 is unexpected.

>From pg documentation:
upper(anyrange) | range's element type | upper bound of range |
upper(numrange(1.1,2.2)) | 2.2
upper_inc(anyrange) | boolean | is the upper bound inclusive? |
upper_inc(numrange(1.1,2.2)) | false

and

"The built-in range types int4range, int8range, and daterange all use a
canonical form that includes the lower bound and excludes the upper bound;
that is, [)."

I understand, that the canonical form of the discrete range is [1,3) but,
still, 3 is not the supremum of [1,3) in the discrete domain of int4
numbers. Supremum of [1,3) in int4 is 2.

It seems that the upper and upper_inc functions don't take into account the
fact the range is discrete.


> SELECT version();
PostgreSQL 9.4.0 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu
4.8.2-19ubuntu1) 4.8.2, 64-bit


> SELECT x, lower(x), upper(x), lower_inc(x), upper_inc(x) FROM (SELECT
'[1,2]'::int4range x) q;
   x   | lower | upper | lower_inc | upper_inc
-------+-------+-------+-----------+-----------
 [1,3) |     1 |     3 | t         | f
                ^^^^^^^_____________^^^-----------> Unexpected!

> SELECT x, lower(x), upper(x), lower_inc(x), upper_inc(x) FROM (SELECT
'(1,2]'::int4range x) q;
   x   | lower | upper | lower_inc | upper_inc
-------+-------+-------+-----------+-----------
 [2,3) |     2 |     3 | t         | f
                ^^^^^^^_____________^^^-----------> Unexpected!

SELECT x, lower(x), upper(x), lower_inc(x), upper_inc(x) FROM (SELECT
'(1,3)'::int4range x) q;
   x   | lower | upper | lower_inc | upper_inc
-------+-------+-------+-----------+-----------
 [2,3) |     2 |     3 | t         | f
                ^^^^^^^_____________^^^-----------> Unexpected!

SELECT x, lower(x), upper(x), lower_inc(x), upper_inc(x) FROM (SELECT
'(1,2)'::int4range x) q;
   x   | lower | upper | lower_inc | upper_inc
-------+-------+-------+-----------+-----------
 empty |       |       | f         | f


> SELECT x, lower(x), upper(x), lower_inc(x), upper_inc(x) FROM (SELECT
'[1,2]'::numrange x) q;
   x   | lower | upper | lower_inc | upper_inc
-------+-------+-------+-----------+-----------
 [1,2] |     1 |     2 | t         | t


> SELECT x, lower(x), upper(x), lower_inc(x), upper_inc(x) FROM (SELECT
'[1,2)'::numrange x) q1;
   x   | lower | upper | lower_inc | upper_inc
-------+-------+-------+-----------+-----------
 [1,2) |     1 |     2 | t         | f

> SELECT x, lower(x), upper(x), lower_inc(x), upper_inc(x) FROM (SELECT
'[1,3]'::numrange x) q1;
   x   | lower | upper | lower_inc | upper_inc
-------+-------+-------+-----------+-----------
 [1,3] |     1 |     3 | t         | t