MySQL Bugs: #60213: Possible bug in string to decimal conversion

Bug #60213	Possible bug in string to decimal conversion
Submitted:	22 Feb 2011 22:21	Modified:	23 Feb 2011 4:20
Reporter:	Carlos Vicente	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:	5.1.41	OS:	Linux (Ubuntu 10.04)
Assigned to:		CPU Architecture:	Any

Description:
Mysql appears to be having problems converting a long number in string format into a decimal, but only when the query includes two columns in a unique index:

mysql> select id,address,prefix from ipblock where address='42540577535367678161542323472695296000';
+----+----------------------------------------+--------+
| id | address                                | prefix |
+----+----------------------------------------+--------+
| 54 | 42540577535367678161542323472695296000 |     64 |
+----+----------------------------------------+--------+
1 row in set (0.00 sec)

mysql> select id,address,prefix from ipblock where address='42540577535367678161542323472695296000' and prefix=64;
Empty set (0.00 sec)

This is particularly a problem when using Perl DBI, because there appears to be no way to make DBI pass the address value as a number, instead of a string.

BTW, this is the latest version of Mysql in Ubuntu 10.04.

How to repeat:
Will attach separate file

Detailed steps to reproduce

Attachment: mysql-string-decimal-bug.txt (text/plain), 8.18 KiB.

Hi Carlos. If you use quote around numeric, mysql converts this value to float (not to decimal), and in case of big enough numbers (as it's in the your testcase), you have to cast explicitly numbers to avoid errors.
 

Formally it's not a bug. It's documented here:

http://dev.mysql.com/doc/refman/5.1/en/precision-math-expressions.html

"If a numeric expression contains any strings, they are converted to double-precision floating-point values and the expression is approximate. "

you can look at http://bugs.mysql.com/bug.php?id=23260