> >Here's my schema and data:
> >
> >create table person (name char(5));
> >insert into person values ('Bob');
> >insert into person values ('Jane');
> >
> >In mySQL 4.1-alpha, 4.0.15a, and 3.23.58, I get the
> >following results:
> >
> >mysql> SELECT * FROM person WHERE NOT name = 'Bob';
> >Empty set (0.00 sec)
> >
> >mysql> SELECT * FROM person WHERE NOT (name =
> 'Bob');
> >+------+
> >| name |
> >+------+
> >| Jane |
> >+------+
> >1 row in set (0.00 sec)
> >
> >Why do I need the parentheses? They are not
> required
> >by the SQL specification. What is the first query
> >really answering?
>
> In MySQL, NOT has higher precedence than =, so your
> first
> query is equivalent to (NOT name) = 'Bob'.
>
> Which will compare 1, 0, or NULL to 'Bob', depending
> on the
> value of name.
Two questions (with justifications):
1. Is giving NOT higher precedence than comparison
operators correct? My answer: No.
Based on my reading of the specification, giving NOT a
higher precedence than comparison operators is not
standards compliant. At the end of this message is
the grammar for the WHERE clause from the 1999 SQL
specification. Note that the precedence order is
(from highest to lowest) <comparison predicate>, NOT,
AND, OR.
2. What does "NOT x" mean where x is non-NULL and
some type other than boolean? You said it means "1,
0, or NULL". NOT NULL returning NULL appears to be
standard complaint, but how would you get 1 or 0?
In addition, consider the following from the 1999
spec:
"4.6.2.1 Operations on booleans that return booleans
The monadic boolean operator NOT and the dyadic
boolean operators AND and OR take boolean operands and
produce a boolean result."
>From this, it seems that NOT only takes booleans
(unless there is another monadic boolean operator
not).
*************************************************
<where clause> ::= WHERE <search condition>
<search condition> ::=
<boolean value expression>
<boolean value expression> ::=
<boolean term>
| <boolean value expression> OR <boolean term>
<boolean term> ::=
<boolean factor>
| <boolean term> AND <boolean factor>
<boolean factor> ::=
[ NOT ] <boolean test>
<boolean test> ::=
<boolean primary> [ IS [ NOT ] <truth value> ]
<truth value> ::=
TRUE
| FALSE
| UNKNOWN
<boolean primary> ::=
<predicate>
| <parenthesized boolean value expression>
| <nonparenthesized value expression primary>
<parenthesized boolean value expression> ::=
<left paren> <boolean value expression> <right paren>
<predicate> ::=
<comparison predicate>
| <between predicate>
| <in predicate>
| <like predicate>
| <null predicate>
| <quantified comparison predicate>
| <exists predicate>
| <unique predicate>
| <match predicate>
| <overlaps predicate>
| <similar predicate>
| <distinct predicate>
| <type predicate>
<comparison predicate> ::=
<row value expression> <comp op> <row value
expression>
<comp op> ::=
<equals operator>
| <not equals operator>
| <less than operator>
| <greater than operator>
| <less than or equals operator>
| <greater than or equals operator>
__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
