Re: Why does the range type's upper function behave inconsistently?

On 07/05/2015 10:13 AM, Dane Foster wrote:
> I don't understand the inconsistent behavior of the range types' upper
> function in regard to inclusive ranges.
>
> For example(s):
> 1. SELECT upper(int4range(1, 4, '[]')) = 4; -- FALSE
> 2. SELECT upper(int8range(1, 4, '[]')) = 4; -- FALSE
> 3. SELECT upper(numrange(1, 4, '[]')) = 4; -- TRUE
> 4. SELECT upper(tstzrange('2015-07-01: 00:00:00', now(), '[]')) = now();
> -- TRUE
> 5. SELECT upper(daterange('2015-01-01', current_date, '[]')) =
> current_date; -- FALSE

To follow up on Julien Rouhaud post, if you do:

production=# select daterange('2015-01-01', current_date, '[]');
         daterange
-------------------------
  [2015-01-01,2015-07-06)
(1 row)

see that the '[]] has been changed to '[)' with tomorrows date as the
upper bound.

>
> #1 & #2 are FALSE because upper returns 5 instead of 4; and #5 is FALSE
> because upper returns: current_date + interval '1 day'. I don't
> understand the logic behind why it would return the inclusive upper
> bound value for some ranges and not others. If anyone can shed some
> light on this behavior it would be greatly appreciated.
>
> One of things I originally tried to use upper for was CHECK constraints.
> That was until I wrote some unit tests and realized that upper doesn't
> consistently work the way I expected. Of course my assumptions are
> probably wrong so that's why I'm asking for clarification.
>
> Regards,
>
> Dane


--
Adrian Klaver
adrian.klaver@aklaver.com