I am reposting this in hopes of convincing the mySQL
implementors to change mySQL or to at least gain some
understanding as to why they violated the
specification.
Apparently, in MySQL, NOT has higher precedence than
the comparison operators, so
WHERE NOT name = 'Bob'
is equivalent to
WHERE (NOT name) = 'Bob'.
See example at the end of this message if you wish to
verify this.
Two questions (with justifications):
1. Is giving NOT higher precedence than comparison
operators a violation of the specification? My
answer: Yes.
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? NOT NULL returning NULL
appears to be standards 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>
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)
__________________________________
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]
