Thread
-
BETWEEN [SYMMETRIC | ASYMMETRIC]
Robert B. Easter <reaster@comptechnews.com> — 2001-01-09T23:06:38Z
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]
Tom Lane <tgl@sss.pgh.pa.us> — 2001-01-09T23:58:15Z
"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 -
Re: BETWEEN [SYMMETRIC | ASYMMETRIC]
Robert B. Easter <reaster@comptechnews.com> — 2001-01-10T00:35:09Z
> > 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/ ------------ -
Re: BETWEEN [SYMMETRIC | ASYMMETRIC]
Tom Lane <tgl@sss.pgh.pa.us> — 2001-01-10T00:45:01Z
"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
-
Re: BETWEEN [SYMMETRIC | ASYMMETRIC]
Thomas Swan <tswan-lst@ics.olemiss.edu> — 2001-01-10T16:10:51Z
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
-
Re: Re: BETWEEN [SYMMETRIC | ASYMMETRIC]
Tom Lane <tgl@sss.pgh.pa.us> — 2001-01-11T03:10:58Z
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
-
Re: Re: BETWEEN [SYMMETRIC | ASYMMETRIC]
Thomas Swan <tswan-lst@ics.olemiss.edu> — 2001-01-11T15:06:44Z
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)