Bug #51375 data type convert

• Submitted: 22 Feb 2010 8:43
• Modified: 22 Feb 2010 15:06
• Reporter: guanding jin
• Status: Not a Bug
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• Version: 5.1.36/42
• OS: Any (testing on Linux )
• CPU Architecture: Any
• Tags: data type convert

[22 Feb 2010 8:43] guanding jin

Description:
First. the SQL "SELECT * FROM gl_user WHERE username=0;" is not good

Second. convert username's value from character to int,but not display any 
        warning information

finally: the SQL return data is not right
root@localhost : test 11:27:41> SELECT * FROM gl_user WHERE username=0;
+-----+-------------+
| id  | username    |
+-----+-------------+
| 303 | @testregext | 
| 304 | test!regext | 
| 305 | testregext  | 
| 306 | sdfsd       | 
| 307 | sdfsd       | 
+-----+-------------+
5 rows in set (0.00 sec)

MySQL provide function(convert & cast ) don't allow convert un-interge data to interge,if you execute "SELECT CONVERT('sd' ,INT)" ,it has grammar's error information.

so i think SQL don't convert column's values to parameter's data type ,should convert paratemer's value to columns's data type.

Example:
root@localhost : test 04:40:16> show columns from gl_user;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment | 
| username | varchar(40) | YES  |     | NULL    |                | 
+----------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
   SELECT * FROM gl_user WHERE username=0;

   auto convert 0 to  varchar(Example:'0')

How to repeat:
root@localhost : test 11:18:03> INSERT INTO gl_user(username) VALUES('@testregext'),('test!regext'),('testregext'),('sdfsd'),('sdfsd'),(NULL);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

root@localhost : test 11:20:50> SHOW CREATE TABLE gl_user;
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                           |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| gl_user | CREATE TABLE `gl_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(40) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=303 DEFAULT CHARSET=utf8 COLLATE=utf8_bin | 
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost : test 11:21:23> SELECT * FROM gl_user;
+-----+-------------+
| id  | username    |
+-----+-------------+
| 303 | @testregext | 
| 304 | test!regext | 
| 305 | testregext  | 
| 306 | sdfsd       | 
| 307 | sdfsd       | 
| 308 | NULL        | 
+-----+-------------+
6 rows in set (0.00 sec)

root@localhost : test 11:21:40> SELECT * FROM gl_user WHERE username=0;
+-----+-------------+
| id  | username    |
+-----+-------------+
| 303 | @testregext | 
| 304 | test!regext | 
| 305 | testregext  | 
| 306 | sdfsd       | 
| 307 | sdfsd       | 
+-----+-------------+
5 rows in set (0.00 sec)

root@localhost : test 11:27:32> INSERT INTO gl_user VALUES(309,1);
Query OK, 1 row affected (0.00 sec)

root@localhost : test 11:27:36> SELECT * FROM gl_user;
+-----+-------------+
| id  | username    |
+-----+-------------+
| 303 | @testregext | 
| 304 | test!regext | 
| 305 | testregext  | 
| 306 | sdfsd       | 
| 307 | sdfsd       | 
| 308 | NULL        | 
| 309 | 1           | 
+-----+-------------+
7 rows in set (0.00 sec)

root@localhost : test 11:27:41> SELECT * FROM gl_user WHERE username=0;
+-----+-------------+
| id  | username    |
+-----+-------------+
| 303 | @testregext | 
| 304 | test!regext | 
| 305 | testregext  | 
| 306 | sdfsd       | 
| 307 | sdfsd       | 
+-----+-------------+
5 rows in set (0.00 sec)

root@localhost : test 11:28:10> SELECT * FROM gl_user where username='0';
Empty set (0.00 sec)

[22 Feb 2010 13:11] Valeriy Kravchuk

This is unexpected, but intended and documented behavior. Read the manual, http://dev.mysql.com/doc/refman/5.1/en/type-conversion.html, carefully. 

When you compare string to integer, values are compared as float numbers. And when MySQL converts non-numeric string like 'username' to number it gets 0.

[22 Feb 2010 15:06] guanding jin

Thinks for your replay!
I know it,but so I suggest MySQL can avoid this situation.

[22 Feb 2010 15:32] Valeriy Kravchuk

CAST() or Convert() actually allows to convert string to integer (signed or unsigned):

77-52-24-143:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.45-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select cast('abc' as signed integer) a, cast('2ab' as signed) b;
+---+---+
| a | b |
+---+---+
| 0 | 2 |
+---+---+
1 row in set, 2 warnings (0.00 sec)

So, there is always a way to compare values of the same data type.

It may be nice, theoretically, to always convert constant/expression to column type, but I do not expect this to be implemented any time soon (as incompatible to current behavior known for ages)...