Thread: BETWEEN patch

A patch to gram.y in src/backend/parser

Provides for the SQL99 expected behavior of
        select * from foo where fo_num between 1 and 5
yields the same result as
        select * from foo where fo_num between 5 and 1

Granted this is brute force and not very elegant, however it does provide the correct behavior.  Optimally it would be nice to do a comparison on the values after between and then sort the  two limiters and do a single rewrite leaving only one pass or scan.

In other words in pseudo SQL:

select * from foo where fo_num between a and b

becomes
        
select * from foo where ((fo_num >= min_value(a, b)) and (fo_num <= max_value(a,b))

This would yield only two comparisons or resolutions and then a single sequential or index scan to find the correct tuples.

This was done against beta1... --
- Thomas Swan                                   
- Network Administrator
- Graduate Student  - Computer Science
-
- The Institute for Continuing Studies
- The University of Mississippi
-
- http://www.ics.olemiss.edu
- http://www.olemiss.edu

Thomas Swan <tswan@ics.olemiss.edu> writes:
> A patch to gram.y in src/backend/parser
> Provides for the SQL99 expected behavior of
>          select * from foo where fo_num between 1 and 5
> yields the same result as
>          select * from foo where fo_num between 5 and 1

This is NOT correct under either SQL92 or SQL99.  Read the spec again.
        regards, tom lane

At 1/24/2001 10:19 AM, Tom Lane wrote:
>Thomas Swan <tswan@ics.olemiss.edu> writes:
> > A patch to gram.y in src/backend/parser
> > Provides for the SQL99 expected behavior of
> >          select * from foo where fo_num between 1 and 5
> > yields the same result as
> >          select * from foo where fo_num between 5 and 1
>
>This is NOT correct under either SQL92 or SQL99.  Read the spec again.
>
>                         regards, tom lane

After sending it... I realized that it was not correct either.   So,  I'm 
back to figuring how to do it... so, um, ignore the previous patch...

Thanks..

Thomas Swan <tswan@ics.olemiss.edu> writes:
> select * from foo where fo_num between 1 and 5
> yields the same result as
> select * from foo where fo_num between 5 and 1
>> 
>> This is NOT correct under either SQL92 or SQL99.  Read the spec again.

> After sending it... I realized that it was not correct either.   So,  I'm 
> back to figuring how to do it... so, um, ignore the previous patch...

Someone (Easter, IIRC) already submitted a patch to support the SQL99
SYMMETRIC BETWEEN option.  I think we're sitting on it till 7.2, on the
grounds of "no new features in beta".  Check the list archives for the
last few weeks if you need it now.
        regards, tom lane