Bug #51397 Invalid string <-> integer comparison
Submitted: 22 Feb 2010 18:17 Modified: 22 Feb 2010 18:59
Reporter: Stefan Müller Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1.42-community OS:Any
Assigned to: CPU Architecture:Any
Tags: comparison, INTEGER, string

[22 Feb 2010 18:17] Stefan Müller 
Description:
An update to one of my tables affected more rows than it should. The reason was that the condition (by mistake) contained a comparison between a varchar column and a literal integer number which was evaluated as true for a special string length. The following select statements shows the problem (the last digit is different):

select 12345678901234567 = '12345678901234568'
======
1

For shorter strings/numbers the statement works correctly:

select 2345678901234567 = '2345678901234568'
======
0

How to repeat:
Any update in the form

UPDATE x SET y='VALUE' WHERE z=12345678901234567 

succeeds when z is a varchar column and contains for example '12345678901234568'
[22 Feb 2010 18:30] Valeriy Kravchuk 
Doesn't this manual page, http://dev.mysql.com/doc/refman/5.1/en/type-conversion.html, describe and explain the results you have?
[22 Feb 2010 18:46] Stefan Müller 
Hi, thanks for your fast support! 

Yes, this document explains my problem. Anyhow I personally don't like this behaviour and think it is dangerous. In my special case, we have transaction ids in our software which came into the "overflow" area of 53bits within the last days and the software suddenly failed after working for more than a year corretly. 

Sure I must admit that comparing a varchar column to an integer is also a bug in our software :-)

So I think you can handle this as closed.

Thanks,
Stefan