Thread: BETWEEN [SYMMETRIC | ASYMMETRIC]

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/ ------------

"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

>
> 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 ]
<rowvalue 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
BETWEENSYMMETRIC Y AND Z )".        4) "X BETWEEN SYMMETRIC Y AND Z" is equivalent to "((X BETWEEN           ASYMMETRIC
YAND 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".
AccessRules           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
<rowvalue 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/ ------------

"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

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

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

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)