Обсуждение: BUG #12568: upper of int4range unexpected value

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

damian-2 wrote
> The following bug has been logged on the website:
>=20
> Bug reference:      12568
> Logged by:          Damian Sepczuk
> Email address:     =20

> damian@

> PostgreSQL version: 9.4.0
> Operating system:   Linux Mint 17.1 Cinnamon 64-bit 3.13.0-24-generic
> Description:       =20
>=20
> While technically 3 is an upper bound of the integer range [1,3) =E2=88=
=A9 =E2=84=95 =3D {1,
> 2}, so are all natural numbers =E2=89=A5 2. I would expect the 'upper' fu=
nction to
> return the supremum (least upper bound) of the range.
> In my opinion the result of upper('[1,2]'::int4range) =3D 3 is unexpected=
.=20
>=20
>>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
>=20
> and
>=20
> "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, [)."
>=20
> 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.

This is not a bug because the answer given, while not what you expect, is
not incorrect if you allow for the fact that the given bound can be either
inclusive or exclusive when dealing with a discrete range.

You already pointed out why the exclusive bound answer is chosen - it is a
natural consequence of the canonical form chosen for the type.

So, at this point, you need to convince people why the inclusive bound is
the better one to supply.  Your expectations, while valid, are likely
insufficient a reason to change.  Range types have been in play since 9.2 s=
o
it is still fairly early in their lifetime but it isn't like two years is
insufficient time to hear about differing opinions on the topic and this is
the first that I've noticed. =20

So, what practical benefits are there to reporting the inclusive maxima
instead of the exclusive one?

As a matter of consistency having continuous ranges - which have to report
inclusive maxima - and discrete ranges different on this point is
undesirable; but I have no practical reason what it even matters.

I'm personally sold on the formal theory argument and think that a design
that requires a separate function that returns whether a given bound is
inclusive or exclusive is a code smell...but my experience in this area is
quite limited and I don't know whether the current design was grounded in
formal mathematics like what you are proposing.

David J.




--
View this message in context: http://postgresql.nabble.com/BUG-12568-upper-=
of-int4range-unexpected-value-tp5834285p5834296.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

David G Johnston <david.g.johnston@gmail.com> writes:
> damian-2 wrote
>> 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.

> This is not a bug because the answer given, while not what you expect, is
> not incorrect if you allow for the fact that the given bound can be either
> inclusive or exclusive when dealing with a discrete range.

> You already pointed out why the exclusive bound answer is chosen - it is a
> natural consequence of the canonical form chosen for the type.

Yeah.  upper() is a simple representation-extraction function, so it's
going to give the stored value which is 3.

There might well be use-cases which would justify inventing infimum() and
supremum() functions that behave as described, but we're not going to
change the behavior of upper/lower at this point.

The use-cases would have to be pretty darn convincing though, because
AFAICS there's no way to provide these functions in a generic way: there'd
need to be new per-range-type support functions to implement them.

            regards, tom lane