List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:September 21 1999 8:54pm
Subject:Re: Bug in MySQL (of sorts)
View as plain text  
>>>>> "Bob" == Bob Kline <bkline@stripped> writes:

Bob> On Mon, 20 Sep 1999, Bryan Porter wrote:
>> In version MySQLd version 3.22.25, it would appear that a bug has shown
>> itself.
>> To duplicate :
>> Send a SQL statement equivalant to the following :
>> "SELECT * FROM tblTemp WHERE tempfield = 1 AND tempfield = 0";
>> 'tempfield' should never equal both 1 AND 0 in any given row. This is an
>> impossibility. However, in the version mentioned above, this query does
>> not generate an error, and happily ignores this syntax problem. Given
>> the fact that the programmer would probably have meant something else,
>> strict syntax checking should be added for this kind of SQL statement
>> (and others).
>> While this is probably against the Perl idiom ("Perl thinks you should
>> stay out of it's living room because you weren't invited, not because it
>> has a shotgun") it's probably best to make MySQL complain on this and
>> other like errors, if only because they would be difficult to track
>> down.
>> This is just my opinion : I apologize if the subject of this message was
>> misleading.

Bob> My opinion:

Bob>  1. This is perfectly legal SQL.
Bob>  2. It represents only the easiest case of a wide spectrum of
Bob>     conditions increasingly more difficult to detect (particularly
Bob>     true when MySQL supports nested queries).

Bob> One of the reasons we enjoy such blazing speed with MySQL is the fact
Bob> that the designers have deliberately chosen to leave out many of the
Bob> fancier options thrown in by most other vendors.  I would assume that in
Bob> the unlikely event MySQL was modified to include checking for such
Bob> conditions:
Bob>  a. they would include an option to turn the checking off; and
Bob>  b. "off" would be the default.

Bob> One aspect of your post which isn't a matter of opinion: the case you
Bob> cite represents a question of semantics, not a "syntax problem"; the
Bob> query is syntactically correct.


Just a quick note.

MySQL does detect the above case and returns at once with an empty
set (This is the only thing a SQL server is allowed to do).

To verify this you can do:

mysql> explain select * from test where id=1 and id=2;
| Comment          |
| Impossible WHERE |
1 row in set (0.00 sec)

Bug in MySQL (of sorts)Bryan Porter21 Sep
  • Re: Bug in MySQL (of sorts)Scott Hess21 Sep
  • Re: Bug in MySQL (of sorts)Bob Kline21 Sep
    • Re: Bug in MySQL (of sorts)Michael Widenius22 Sep
  • Re: Bug in MySQL (of sorts)Balint TOTH21 Sep
  • Re: Bug in MySQL (of sorts)Benjamin Pflugmann21 Sep
    • Re: Bug in MySQL (of sorts)Steve Edberg21 Sep
      • Re: Bug in MySQL (of sorts)Benjamin Pflugmann21 Sep
    • Re: Bug in MySQL (of sorts)Paul DuBois22 Sep
  • Re: Bug in MySQL (of sorts)Scott Hess21 Sep