I think the difference between mysql's precedence operation is significant to point out. Looking at the behavior of other databases, SQL Server and Oracle both respond to the query you gave with Recordss about users with names != 'Bob'. Jane returned. Significantly, this is inversion does not include NULL, in case anyone thought it would. Searching newsgroups, it appears this precendence in mySQL has been around for at least 5 years. I saw a post (written by Monty himself) asserting it. That might give us the poor-man's answer as to why the precendence is as it is: because it always has been.
My suggestion? Work around the problem by always wrapping the predicate of NOT with parens. Also, submit the issue as a test within "Crash-me". Potential converts to mysql will want to beware of the difference, even though I suspect most users will never notice the difference, because your usage is an unusual construct. IMHO, anyway. > -----Original Message----- > From: Ed Smith [mailto:[EMAIL PROTECTED] > Sent: Friday, October 03, 2003 7:22 AM > To: [EMAIL PROTECTED] > Subject: Re: Why does mySQL violate NOT precedence? > > > Your point does not seem relevant to my question on > mySQL compliance with the specification. In fact, > your response doesn't even mention the specification. > > Perhaps I'm misunderstanding your question. You are > wondering why I would ask a query like > > SELECT * > FROM Person > WHERE NOT name = 'Bob'? > > Might I ask such a query to get the list of all people > who are not named Bob? > > Also, my question involves mySQL's conformance to the > SQL specification. Your demonstration of what mySQL > does with "SELECT not name..." seems irrelevant if > it's mySQL's compliance that is in question. > > > I'm not sure why you would use the syntax you are > > trying to use. I > > think you are making a few assumptions that may be > > incorrect. If you do > > this query: > > SELECT not name='Bob' FROM person > > You'll see your result is set contains all 0's. > > Records where > > name='Bob' would return 1, but you are taking the > > opposite of 1, which > > would be 0. For those records that return 0, you > > want the opposite of > > 0, which is????? Infinity? If you convert infinity > > to boolean, it would > > be false. So from my point of view, MySQL is using > > the correct > > precedence. > > > > If you want to find all records that are not Bob, > > why not just us this > > syntax: > > SELECT * FROM person WHERE name!='Bob' > > > > It's more efficient since there is only one > > comparison occurring > > instead of two, negating name='Bob'. After all, you > > are looking to > > negate the equal, not negate name. > > > > On Friday, October 3, 2003, at 07:42 AM, Ed Smith > > wrote: > > > > > 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) > > > > > -- > > Brent Baisley > > Systems Architect > > Landover Associates, Inc. > > Search & Advisory Services for Advanced Technology > > Environments > > p: 212.759.6400/800.759.0577 > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > __________________________________ > 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
