By Carsten | February 20, 2008
This thing is catching on … today’s question comes courtesy of Lasse Christiansen who is apparently residing in Japan (Lasse, correct me if I’m wrong).
Suppose you issue the following list of commands:
CREATE TABLE test ( id varchar(1) NOT NULL, PRIMARY KEY (id) ); INSERT INTO test VALUES ('0'), ('1'), ('2'), ('a'), ('b');
The INSERT succeeds — nothing surprising here, the table now contains the values ‘0′, ‘1′, ‘2′, ‘a’ and ‘b’
Now you do:
DELETE FROM test WHERE id=0;
…and your client informs you that 3 rows were deleted.
Stuck? Click here for a hint.
Hint: Notice that in the DELETE statement, 0 is unquoted.
Answer: This behavior is by design. When you compare values of different types (in this case character strings and integers), MySQL will first convert both values to type FLOAT* and then do the comparison for the WHERE clause. In the statement shown, the strings ‘0′, ‘a’ and ‘b’, as well as the integer 0, all become 0.0 when converted to FLOAT — and so they do compare equal.
*Actually, that’s oversimplifying things a bit, especially when NULL and/or temporal types are part of the equation. You can get the full story here.
If you have come across something that you think would make a good pop quiz question, send it to carsten (at) bitbybit (dot) dk. It’s OK if you don’t know the answer to your own question — that only makes things more interesting.More Pop Quiz entries.