Re: Ranges for well-ordered types - Mailing list pgsql-hackers

On Jun 11, 2006, at 10:57 , Jim C. Nasby wrote:

> On Sun, Jun 11, 2006 at 10:18:11AM +0900, Michael Glaesemann wrote:
>>
>> Under design I proposed, closed-closed and closed-open are just two
>> different representations of the same range: to the commonly used
>> notation, the closed-open range [p1, p2) is equivalent to  the  
>> closed-
>> closed range [p1, next(p2)], where next() is the successor function.
>
> Why try messing aronud with a successor function when you can just  
> use <
> instead of <= ?

If I understand you correctly, you're pointing out that the  
constraints for a valid range in closed-closed and closed-open  
representation are different:

for a valid closed-open range r1 [a1, b1), a1 < b1
for a valid closed-closed range r2 [a2, b2], a2 <= b2

That's different from being able to show equivalence between two  
ranges in different representations, e.g., r1 = r2 iff a1 = a2 and b1  
= next(b2). As Bruno pointed out earlier, in some cases, a closed- 
open representation is desirable, and I think that in others, such as  
date ranges, a closed-closed representation is useful. Another place  
where I'd use a closed-closed representation would be for describing  
score ranges for grades (e.g., 70-79 is a C, 80-89 is a B). I'm not  
sure how to go about converting between these two representations  
without using a successor (or predecessor) function.

Another way of looking at not having a successor function is not  
restricting ranges to be comprised of discrete point types. In that  
case, you can't really use closed-closed representation at all (or  
open-open, for that matter), because the successor function is  
necessary to define the meets and before predicates, or to convert  
between closed-closed and closed-open representations, if one is  
using closed-open representation internally.

Another wrinkle in cases without a defined successor function is  
boundary cases: ranges that include one or both of the bounds of the  
point type. For example, with a closed-open representation, a range  
can't include the upper bound of the point type. Perhaps a way around  
this would be to allow infinity as a possible value: the date range  
['2006-06-11', 'infinity') would describe a range from June 11, 2006  
through the upper bound of the date type (5874897-12-31 on my  
machine, though interestingly enough:

postgres=# SELECT '5874897-12-31'::date;     date
---------------
5874897-12-31
(1 row)

postgres=# SELECT '5874897-12-31'::date + 2;   ?column?
---------------
5874898-01-02
(1 row)

postgres=# SELECT '5874898-01-02'::date;
ERROR:  date out of range: "5874898-01-02"
postgres=# SELECT ('5874897-12-31'::date + 2)::date;     date
---------------
5874898-01-02
(1 row)

postgres=# SELECT '5874897-12-31'::date + interval '2 days';          ?column?
----------------------------
29357-07-06 15:41:44.48384
(1 row)

postgres=# SELECT ('5874897-12-31'::date + interval '2 days')::date;    date
-------------
29357-07-06
(1 row)

postgres=# select version();                                                                    
version
------------------------------------------------------------------------ 
----------------------------------------------------------------------
PostgreSQL 8.1.4 on powerpc-apple-darwin8.6.0, compiled by GCC  
powerpc-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc.  
build 5341)
(1 row)

That looks a bit odd. :(

Also, a successor function is very useful in testing other  
predicates. To keep things simpler, I'm going to use the same  
representation for both ranges, as internally you'd probably convert  
the ranges to some canonical representation for comparison. Whether  
that canonical representation is closed-closed, closed-open, or a  
point and an interval doesn't really matter.

Practically, I think being able to use both closed-closed and closed- 
open representations as needed is useful, and for most purposes, the  
types can be considered discrete and bounded. Is there a lot to be  
gained by not defining a successor function?

Michael Glaesemann
grzm seespotcode net