Can anyone point out this behaviour and hw to stop this ?
I know about casting or adding double quotes.
I was wondering if there is any other way
Heres table definition
CREATE TABLE `countries` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`country_name` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1
The record with id 1 is correct but id 2 doesn't satisfy the where condition but is returned.
Try this:
select * from countries where country_name='7884'
The problem is, when you write country_name = 7884, that country_name is implicitly casted as a signed integer. And in the casting process the 'S' is simply ignored, as you can see here. That selects the second row because cast('1884S' AS signed) = 1884 = 1884 (your input).
As fa06 already suggested, surround the operand by single quotes to make it a string and prevent the implicit cast -- ... where country_name = '7884'.
