Re: [PATCH] Support empty ranges with bounds information

On Tue, Mar 2, 2021, at 19:17, Mark Dilger wrote:
> > On Mar 2, 2021, at 10:08 AM, Joel Jacobson <joel(at)compiler(dot)org> wrote:
> > That's why the patch doesn't change equality.
>
> How does that work if I SELECT DISTINCT ON (nr) ... and then take upper(nr). It's just random which values I get?

Yes. It's random, since equality isn't changed, the sort operation cannot tell the difference, and nor could a user who isn't aware of upper() / lower() could reveal differences.

Demo:

CREATE TABLE t AS SELECT int4range(i,i+FLOOR(random()*2)::integer,'[)') AS nr FROM generate_series(1,10) AS i;

SELECT nr, lower(nr), upper(nr) FROM t ORDER BY 1;
nr | lower | upper
--------+-------+-------
empty | 10 | 10
empty | 4 | 4
empty | 6 | 6
empty | 7 | 7
empty | 1 | 1
empty | 3 | 3
[2,3) | 2 | 3
[5,6) | 5 | 6
[8,9) | 8 | 9
[9,10) | 9 | 10
(10 rows)

SELECT DISTINCT ON (nr) nr, lower(nr), upper(nr) FROM t ORDER BY 1;
nr | lower | upper
--------+-------+-------
empty | 10 | 10
[2,3) | 2 | 3
[5,6) | 5 | 6
[8,9) | 8 | 9
[9,10) | 9 | 10
(5 rows)

/Joel