Bug #42936 Strings incorrectly converted to double

• Submitted: 17 Feb 2009 15:20
• Modified: 20 Feb 2009 14:38
• Reporter: Ray Ray
• Status: Duplicate
• Category: MySQL Server: General
• Severity: S3 (Non-critical)
• Version: 5.0.32, 5.0.74, 5.0.79-bzr
• OS: Linux
• CPU Architecture: Any

[17 Feb 2009 15:20] Ray Ray

Description:
Query with a number first in the values for 'in' cause it to compare as a double even if the field is varchar.

How to repeat:
Query that shows this bug:

select user from mysql.user where user in (0);

Expected results:
empty set, no warnings

Results seen:
All the users and a warning for each row:
....
Warning (Code 1292): Truncated incorrect DOUBLE value: 'root'
....

Suggested fix:
If type conversion is needed, the constants should be converted to match the field type, not the field match the constant.

[17 Feb 2009 15:44] Valeriy Kravchuk

This is not a bug. When string column is compared to number they are compared as double numbers. This is documented. See http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html for explanation.

[17 Feb 2009 19:07] Ray Ray

So this is expected behavior?

select 'bob' in ('henry',1), 'bob'='henry' or 'bob'=1;
+----------------------+--------------------------+
| 'bob' in ('henry',1) | 'bob'='henry' or 'bob'=1 |
+----------------------+--------------------------+
|                    1 |                        0 |
+----------------------+--------------------------+
1 row in set, 3 warnings (0.00 sec)

Warning (Code 1292): Truncated incorrect DOUBLE value: 'henry'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'bob'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'bob'

[18 Feb 2009 11:21] Valeriy Kravchuk

No, this is wrong:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.0.74-enterprise-gpl-nt-log MySQL Enterprise Server - Pro Editi
on (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select 'bob' in ('henry',1), 'bob'='henry' or 'bob'=1;
+----------------------+--------------------------+
| 'bob' in ('henry',1) | 'bob'='henry' or 'bob'=1 |
+----------------------+--------------------------+
|                    1 |                        0 |
+----------------------+--------------------------+
1 row in set, 3 warnings (0.08 sec)

MySQL 5.1.30 gives correct result:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.30-community-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select 'bob' in ('henry',1), 'bob'='henry' or 'bob'=1;
+----------------------+--------------------------+
| 'bob' in ('henry',1) | 'bob'='henry' or 'bob'=1 |
+----------------------+--------------------------+
|                    0 |                        0 |
+----------------------+--------------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1292
Message: Truncated incorrect DOUBLE value: 'bob'
*************************** 2. row ***************************
  Level: Warning
   Code: 1292
Message: Truncated incorrect DOUBLE value: 'bob'
2 rows in set (0.02 sec)

[18 Feb 2009 12:23] Valeriy Kravchuk

Latest 5.0.79 from bzr is also affected.

[19 Feb 2009 20:09] Evgeny Potemkin

A duplicate of the bug#18360. This bug was fixed in 5.1+ only because of Monty's decision. See comments for the bug#18360.