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
Tue, 23 Jan 2001 15:17:49 -0600
Thomas Swan <tswan@ics.olemiss.edu>
BETWEEN patch
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

Wed, 24 Jan 2001 11:19:14 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: BETWEEN patch 
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..


Wed, 24 Jan 2001 13:23:44 -0600
Thomas Swan <tswan@ics.olemiss.edu>
Re: BETWEEN patch 
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

Thu, 25 Jan 2001 23:15:09 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: BETWEEN patch 