Fortunately, you need look no further than in the syntax rules for the
between clause:
3) “X NOT BETWEEN ASYMMETRIC Y AND Z” is equivalent to “NOT ( X BETWEEN
ASYMMETRIC Y
AND Z )”.
I do not know what ASYMMETRIC means, except that it is the default...
Thus,
select * from t1 where NOT c1 NOT BETWEEN 2 AND 3
is the same as
select * from t1 where NOT(NOT(c1 BETWEEN 2 AND 3))
which is the same as:
select * from t1 where c1 BETWEEN 2 AND 3
SQL can be simple too :)
Roy
Roland Bouman wrote:
Hi Paul,
Or do we now have to discuss what this means:
select * from t1 where NOT c1 NOT BETWEEN 2 AND 3! :P
:p
Thanks to the standard, we don't.
<where clause> ::= WHERE <search condition>, so:
"NOT c1 NOT BETWEEN 2 AND 3" is a <search condition>.
<search condition> ::= <boolean value expression>, so, it is also a
<boolean value expression>.
<boolean value expression> ::= <boolean term> | <boolean value
expression> OR <boolean term>
No OR in there, so it must be a <boolean term>
<boolean term> ::= <boolean factor> | <boolean term> AND <boolean factor>
Hey! we have an "AND" in there, so it could be that "NOT c1 NOT
BETWEEN 2" is a <boolean term>, and "3" is a <boolean factor>
Alternatively, the entire thing could also stil be a <boolean factor>
<boolean factor> ::= [ NOT ] <boolean test>
<boolean test> ::= <boolean primary> [ IS [ NOT ] <truth value> ]
<truth value> ::= TRUE | FALSE | UNKNOWN
Well, "3" is not a truth value, so we must accept that the entire
thing is a <boolean factor>.
In fact, from the production of <boolean factor> we can match the
leading "NOT", so "c1 NOT BETWEEN 2 AND 3" must be a <boolean test>
We don't see any "IS" in there, so we must conclude that
"c1 NOT BETWEEN 2 AND 3" is a <boolean primary>
<boolean primary> ::= <predicate> | <boolean predicand>
So "c1 NOT BETWEEN 2 AND 3" is either a <predicate> or a <boolean predicand>
<boolean predicand> ::= <parenthesized boolean value expression> |
<nonparenthesized value expression primary>
<parenthesized boolean value expression> ::= <left paren> <boolean
value expression> <right paren>
No parenthesis, so it is certainly not a <parenthesized boolean value
expression>. It may still be a <nonparenthesized value expression
primary>, but you all probably doze of if I prove that. So you're
going to have to trust me that "c1 NOT BETWEEN 2 AND 2" is a
predicate.
<between predicate> ::= <row value predicand> <between predicate part 2>
<between predicate part 2> ::= [ NOT ] BETWEEN [ ASYMMETRIC |
SYMMETRIC ] <row value predicand> AND <row value predicand>
More trust needed - c1 is a <row value predicand>
In the remaining "NOT BETWEEN 2 AND 3", we can see "NOT" and "BETWEEN"
and "AND", leaving us with
"2" and "3" both being a <row value predicand>.
Summing up:
NOT (c1 NOT BETWEEN 2 AND 3)
and not
(NOT c1) NOT BETWEEN 2 AND 3
Phew, that sure makes me respect those SQL parsers.
regards,
Roland
--
Paul McCullagh
PrimeBase Technologies
www.primebase.org
www.blobstreaming.org
pbxt.blogspot.com
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
