Bug #42738 integers not being cast as strings when compared against varchars

• Submitted: 10 Feb 2009 20:44
• Modified: 10 Feb 2009 21:22
• Reporter: mark konetchy
• Status: Not a Bug
• Category: MySQL Server: Data Types
• Severity: S1 (Critical)
• Version: 5.0.51
• OS: Any
• CPU Architecture: Any
• Tags: cast, comparison, conversion

[10 Feb 2009 20:44] mark konetchy

Description:
when strings that start with integers (like md5s) are compared against integers an erroneous match is being returned if the integer matches the first integer characters of the string, eg

'123ccc2937159123' will match 123, but

'x123ccc39015115' will not.

How to repeat:
mysql> select version();
+-------------+
| version()   |
+-------------+
| 5.0.51b-log | 
+-------------+
1 row in set (0.00 sec)

mysql> show create table t;
+-------+-------------------------------------------------------------------------------------------+
| Table | Create Table                                                                              |
+-------+-------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `a` varchar(255) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 | 
+-------+-------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from t;
+-------+
| a     |
+-------+
| 2eeoo | 
+-------+
1 row in set (0.00 sec)

mysql> select * from t where a = 2;
+-------+
| a     |
+-------+
| 2eeoo | 
+-------+
1 row in set (0.00 sec)

mysql> select * from t where a = "2";
Empty set (0.00 sec)

mysql> update t set a = concat('x' , a);
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t where a = 2;
Empty set (0.00 sec)

[10 Feb 2009 20:45] mark konetchy

the fix should be to cast everything thats being compared to a char datatype to a char.

[10 Feb 2009 20:47] Paul DuBois

The string will be converted to a number, not the number to a string.

http://dev.mysql.com/doc/refman/5.1/en/type-conversion.html

[10 Feb 2009 21:22] Sveta Smirnova

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read about Type Conversion in Expression Evaluation at http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html