Bug #2677 Cannot correctly resolve search patterns in where clause
Submitted: 8 Feb 2004 22:41 Modified: 9 Feb 2004 8:52
Reporter: Dove Young Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.0 OS:Microsoft Windows (Windows and bsd)
Assigned to: Dean Ellis CPU Architecture:Any

[8 Feb 2004 22:41] Dove Young
Description:
when you provide a string pattern in a where clause to search for an integer column, MySQL will not report this error. Instead, it return result set as if the pattern provided is just integer.

Even more, if you provide a string pattern consisted by numeric charactors followed by any other letter charactors in any length, MySQL will still return result set as if those letter charactors were not exist at all.

How to repeat:
Create Table payinglog33 (
logid int
);

insert into payinglog33 (logid) values (1);

select * from payinglog33 where logid = 1;
select * from payinglog33 where logid = '1';
select * from payinglog33 where logid = '1ttt';

Those three select query will return the same result.
[9 Feb 2004 8:52] Dean Ellis
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

This is by design.  MySQL performs automatic conversions where possible, which is also frequently taken advantage of as a security precaution.
[9 Feb 2004 17:25] Dove Young
Well. As you say, MySQL performs automatic conversions where possible.
But, it is really a security leak to converse in this way. It will either cause applications not security enough or cause programmers to be confused while debuging. After all, maybe it's not a good idea.