String comparison with integer is not working sometimes in MySQL 5.5

Question

I have upgraded MySQL to version 5.5. Before upgrading when we used string comparison with integer, it was working.

Code is "value = 1", here 'value' is declared as varchar(45) and its value can be anything either 1 or some string.

But after upgrading, it is not working properly. Sometimes it works fine and sometimes not. I tested in different setups, in some setups it is working but in some other it is failing.

Here if I change the code as value = '-1', then it is working fine in all cases and different setups.

But I had used this case in my application in many places.

Is there anything to be handled for newer version other than using single quotes?

Please help me out of this.

Answer 1

This isn't a full answer but you might try looking over the 5.5 change list starting at http://dev.mysql.com/doc/refman/5.5/en/news-5-5-0.html

Note the section nav on the right that goes subversion by subversion. Skim through each looking at "incompatible changes". If you find something describing what you are seeing it will at least give you better context of what is happening and why.

Answer 2

If every value other that 1 works, it sounds like a problem with the sign of the number being autoconverted in your OS.

You should just do value = '1' and be done with.

You could also do value+0 = 1.

I tried this in InnoDB and MyISAM on MySQL 5.5.12 on my PC

mysql> use test
Database changed
mysql> create table my_nums (a varchar(45));
Query OK, 0 rows affected (0.08 sec)

mysql> insert into my_nums values (0),(1),(2);
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select (a = 1) result from my_nums;
+--------+
| result |
+--------+
|      0 |
|      1 |
|      0 |
+--------+
3 rows in set (0.00 sec)

mysql> drop table my_nums;
Query OK, 0 rows affected (0.08 sec)

mysql> create table my_nums (a varchar(45)) engine=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into my_nums values (0),(1),(2);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select (a = 1) result from my_nums;
+--------+
| result |
+--------+
|      0 |
|      1 |
|      0 |
+--------+
3 rows in set (0.00 sec)

mysql>

Try this out in your installation of MySQL in your particular OS. This may or may not reveal a problem. From MySQL 5.5.12 in Windows, everything looks OK.

If you are using PHP or ODBC to pass the query to MySQL, please update your drivers so that it could properly read/write MySQL 5.5.