Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
6  
Using a number literal for comparison against a character column is a bad habit to begin with. You should really change your code to use proper literals (do not compare apples to oranges) –  a_horse_with_no_name Dec 29 '11 at 12:16
4  
In addition to "don't mix data types"... What is "not working"? errors? warnings? odd results? What values fails (large numbers that would exceed integer for example)? –  gbn Dec 29 '11 at 12:52
    
Why the hesitation of using single quotes? Any solution is probably going to entail modifying your query. –  Derek Downey Dec 30 '11 at 15:04
add comment

2 Answers 2

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.