MySQL Bugs: #64589: Alphanumeric strings bring back results on integer columns

Bug #64589	Alphanumeric strings bring back results on integer columns - SQL mode needed!
Submitted:	8 Mar 2012 5:08	Modified:	16 Mar 2012 15:56
Reporter:	Ryan Klefas	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.1; 5.5	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	cast int, cast string, INT, int column, INTEGER, where

Description:
When querying an integer column, alphanumeric strings will be truncated/cast to an integer and bring back results when it shouldn't.  It's surprisingly easy to recreate.  This seems to be a longstanding bug; I found an old forum post describing the exact same thing here:  

http://www.dbforums.com/mysql/1005918-int-where-clause.html

How to repeat:
CREATE TABLE if not exists integer_bug_test (fieldname int);

insert into integer_bug_test (fieldname) values ('1'), ('65'), ('709');

# This query should not return anything...

SELECT * FROM integer_bug_test WHERE fieldname = '65mdmlklkd';

# This returns nothing, probably because it starts with a letter...

SELECT * FROM integer_bug_test WHERE fieldname = 'ggg65mdmlklkd';

Suggested fix:
I expect that the string/integer that I am querying for is the ONLY thing that will match.

what we need is a strict mode that applies to selects!

This is expected and not a bug, as it was explained many times in different bug reports and manual. Look:

mysql> select '65aaaaaa' + 0;
+----------------+
| '65aaaaaa' + 0 |
+----------------+
|             65 |
+----------------+
1 row in set, 1 warning (0.10 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1292
Message: Truncated incorrect DOUBLE value: '65aaaaaa'
1 row in set (0.00 sec)

But we do need SQL mode to treat this as error, not just output warning, as Shane pointed out. So, this is a valid feature request.

I think it's important to point out that in my original test queries, I am doing a search using the WHERE clause.  When doing this, MySQL emits NO warnings, and NO errors.  

And I found this odd behavior when using MySQL under 'traditional' mode.

id is an int(11) field

SELECT * FROM`registration` WHERE id ='4Z941399'

returns the same thing as

SELECT * FROM`registration` WHERE id = 4

No error message.

That's critical to me.
How do I stop that?