BETWEEN [SYMMETRIC | ASYMMETRIC]

От:

"Robert B. Easter" <reaster@comptechnews.com>

Дата:

9 января 2001 г. в 18:01:13

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

Re: BETWEEN [SYMMETRIC | ASYMMETRIC]

От:

"Robert B. Easter" <reaster@comptechnews.com>

Дата:

9 января 2001 г. в 19:29:53

>
> 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          Function        Specify a range comparison.        Format         ::=              [ NOT ] BETWEEN               [ ASYMMETRIC | SYMMETRIC ]                AND         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 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 ",           conforming SQL language shall not specify SYMMETRIC or           ASYMMETRIC.        2) Without Feature S024, "Enhanced structured types", no subfield           of the declared type of a  that is simply           contained in a  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/ ------------

Re: Re: BETWEEN [SYMMETRIC | ASYMMETRIC]

От:

Tom Lane <tgl@sss.pgh.pa.us>

Дата:

10 января 2001 г. в 22:11:05

Thomas Swan  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

Re: BETWEEN [SYMMETRIC | ASYMMETRIC]

От:

Thomas Swan <tswan-lst@ics.olemiss.edu>

Дата:

10 января 2001 г. в 11:10:59

At 1/9/2001 10:29 PM, Tom Lane wrote:
>Thomas Swan  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

Re: Re: BETWEEN [SYMMETRIC | ASYMMETRIC]

От:

Thomas Swan <tswan-lst@ics.olemiss.edu>

Дата:

11 января 2001 г. в 10:06:46

At 1/10/2001 09:10 PM, you wrote:
>Thomas Swan  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)

Re: BETWEEN [SYMMETRIC | ASYMMETRIC]

От:

Tom Lane <tgl@sss.pgh.pa.us>

Дата:

9 января 2001 г. в 18:58:21

"Robert B. Easter"  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

Re: BETWEEN [SYMMETRIC | ASYMMETRIC]

От:

Tom Lane <tgl@sss.pgh.pa.us>

Дата:

9 января 2001 г. в 19:45:07

"Robert B. Easter"  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