MySQL bug? Select WHERE id='1blah'

MySQL Version 5.0.67

Take a look at this very simple table and tell me if I have found a MySQL bug, I have tried to search for an answer but as you can imagine it's a bit hard to come up with the right search terms

CREATE TABLE `product` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(60) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM开发者_C百科 DEFAULT CHARSET=utf8;

INSERT INTO `product` VALUES (1, 'jim');
INSERT INTO `product` VALUES (2, 'bob');

From there I can then select the following

SELECT * FROM `product` WHERE `id` = '1';

Obviously this returns a row, but then, so does this

SELECT * FROM `product` WHERE `id` = '1blah';

Erm... WHY? Surely this is wrong or am I going mad? Will crawl the web a bit more before I file a bug report with MySQL.

It's automatically converting the string "1blah" into an integer. As the string begins with a "1" the resultant integer is simply 1.

As such, it's just trying to do the right thing, even though it might seem a bit counter-intuitive.

This happens because of type conversion. Since your column has integer value, '1blah' is converted to 1. Please, see http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html for more details.

If you didn't enclose the integer id in quotes, it'd work and you'd get an error as you'd hope. That is,

SELECT * FROM `product` WHERE `id` = 1;

1 row in set

works, while

SELECT * FROM `product` WHERE `id` = 1blah;

ERROR 1054 (42S22): Unknown column '1blah' in 'where clause'

errors.