Here is current cvs:

SELECT 2 BETWEEN 1 AND 3;
 ?column?
----------
 t
(1 row)
 
subselects=# SELECT 2 BETWEEN 3 AND 1;
 ?column?
----------
 f
(1 row) 


Any chance of BETWEEN [SYMMETRIC | ASYMMETRIC] being implemented?

SELECT 2 BETWEEN SYMMETRIC 3 AND 1;
 ?column?
----------
 t
(1 row) 

ASYMMETRIC is the default and what is currently the case.  This would 
probably be easy TODO.
  
-- 
-------- Robert B. Easter  reaster@comptechnews.com ---------
-- CompTechNews Message Board http://www.comptechnews.com/ --
-- CompTechServ Tech Services http://www.comptechserv.com/ --
---------- http://www.comptechnews.com/~reaster/ ------------

Tue, 9 Jan 2001 18:06:38 -0500
"Robert B. Easter" <reaster@comptechnews.com>
BETWEEN [SYMMETRIC | ASYMMETRIC]
"Robert B. Easter" <reaster@comptechnews.com> writes:
> subselects=# SELECT 2 BETWEEN 3 AND 1;
>  ?column?
> ----------
>  f
> (1 row) 

SQL92 quoth:

         6) "X BETWEEN Y AND Z" is equivalent to "X>=Y AND X<=Z".

so this is correct behavior, even if it might seem surprising.

> Any chance of BETWEEN [SYMMETRIC | ASYMMETRIC] being implemented?

> SELECT 2 BETWEEN SYMMETRIC 3 AND 1;
>  ?column?
> ----------
>  t
> (1 row) 

Build a function based on this idea:

regression-# select case
regression-#   when 3 < 1 then  2 between 3 and 1
regression-#   else             2 between 1 and 3
regression-# end;
 case
------
 t
(1 row)

I don't really see this as important enough to justify introducing a
nonstandard syntax for it...

			regards, tom lane

Tue, 09 Jan 2001 18:58:15 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: BETWEEN [SYMMETRIC | ASYMMETRIC] 
>
> I don't really see this as important enough to justify introducing a
> nonstandard syntax for it...
>
> 			regards, tom lane

Sorry to quote like this, it makes me feel like a real nerd. :)

This quote from the SQL standard (1999) has it:


        8.3  <between predicate>
 
         Function
 
         Specify a range comparison.
 
         Format
 
         <between predicate> ::=
              <row value expression> [ NOT ] BETWEEN
                [ ASYMMETRIC | SYMMETRIC ]
                <row value expression> AND <row value expression>
 
 
         Syntax Rules
 
         1) If neither SYMMETRIC nor ASYMMETRIC is specified, then
            ASYMMETRIC is implicit.
 
         2) Let X, Y, and Z be the first, second, and third <row value
            expression>s, respectively.
 
         3) "X NOT BETWEEN SYMMETRIC Y AND Z" is equivalent to "NOT ( X
            BETWEEN SYMMETRIC Y AND Z )".
 
         4) "X BETWEEN SYMMETRIC Y AND Z" is equivalent to "((X BETWEEN
            ASYMMETRIC Y AND Z) OR (X BETWEEN ASYMMETRIC Z AND Y))".
 
         5) "X NOT BETWEEN ASYMMETRIC Y AND Z" is equivalent to "NOT ( X
            BETWEEN ASYMMETRIC Y AND Z )".
 
         6) "X BETWEEN ASYMMETRIC Y AND Z" is equivalent to "X>=Y AND X<=Z".
 
         Access Rules
 
            None.
 
         General Rules
 
            None.
 
         Conformance Rules
 
         1) Without Feature T461, "Symmetric <between predicate>",
            conforming SQL language shall not specify SYMMETRIC or
            ASYMMETRIC.
 
         2) Without Feature S024, "Enhanced structured types", no subfield
            of the declared type of a <row value expression> that is simply
            contained in a <between predicate> shall be of a structured
            type.                                  

-- 
-------- Robert B. Easter  reaster@comptechnews.com ---------
-- CompTechNews Message Board http://www.comptechnews.com/ --
-- CompTechServ Tech Services http://www.comptechserv.com/ --
---------- http://www.comptechnews.com/~reaster/ ------------

Tue, 9 Jan 2001 19:35:09 -0500
"Robert B. Easter" <reaster@comptechnews.com>
Re: BETWEEN [SYMMETRIC | ASYMMETRIC]
"Robert B. Easter" <reaster@comptechnews.com> writes:
> This quote from the SQL standard (1999) has it:

Oh, I didn't realize SQL99 had added it.  Creeping featurism strikes
again ;-).  Well, I suppose it'll get added to PG whenever someone
feels like implementing it, then ...

			regards, tom lane

Tue, 09 Jan 2001 19:45:01 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: BETWEEN [SYMMETRIC | ASYMMETRIC] 
At 1/9/2001 10:29 PM, Tom Lane wrote:
>Thomas Swan <tswan-lst@ics.olemiss.edu> writes:
> > Shouldn't be much of problem... where would I start to look... :)
>
>Well, the Right Way To Do It would be to invent a new expression node
>type that implements both kinds of BETWEEN.  Right now, the parser
>expands A BETWEEN B AND C into "A >= B AND A <= C", which is perfectly
>correct according to the letter of the spec, but it implies evaluating
>the subexpression A twice, which sucks.  Besides which, this doesn't

Actually if it were possible to look at the values before expanding.  You 
could reorder the expression so that it was always the case that B < C, 
then your cost would only be one comparison plus the sequential scan.


>readily generalize to the SYMMETRIC case.  I'd make a new expr node
>type with three subexpressions and a SYMMETRIC bool flag.  If you chase
>down all the places where CaseExpr nodes are processed, and add a
>BetweenExpr case in parallel, you'll have it made.
>
>                         regards, tom lane


Wed, 10 Jan 2001 10:10:51 -0600
Thomas Swan <tswan-lst@ics.olemiss.edu>
Re: BETWEEN [SYMMETRIC | ASYMMETRIC] 
Thomas Swan <tswan-lst@ics.olemiss.edu> writes:
> Actually if it were possible to look at the values before expanding.  You 
> could reorder the expression so that it was always the case that B < C, 
> then your cost would only be one comparison plus the sequential scan.

Uh ... what if B and C are not constants?

			regards, tom lane

Wed, 10 Jan 2001 22:10:58 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Re: BETWEEN [SYMMETRIC | ASYMMETRIC] 
At 1/10/2001 09:10 PM, you wrote:
>Thomas Swan <tswan-lst@ics.olemiss.edu> writes:
> > Actually if it were possible to look at the values before expanding.  You
> > could reorder the expression so that it was always the case that B < C,
> > then your cost would only be one comparison plus the sequential scan.
>
>Uh ... what if B and C are not constants?

Hmmm... I see your point.  I was looking back through the sources and was 
thinking.

I'd hate doing the work twice.

Is there something in place to reorder or sort or compare results?
Possibly expanding to something like a <= max(b,c) and a >= min(b,c)




Thu, 11 Jan 2001 09:06:44 -0600
Thomas Swan <tswan-lst@ics.olemiss.edu>
Re: Re: BETWEEN [SYMMETRIC | ASYMMETRIC] 