MySQL Bugs: #36615: Floating point number incorrectly stored in 64 bit integer if it has 20+ digits

Bug #36615	Floating point number incorrectly stored in 64 bit integer if it has 20+ digits
Submitted:	9 May 2008 7:27	Modified:	14 May 2008 20:50
Reporter:	Stephan Austermühle	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.0.51b	OS:	HP/UX (11iV1 64bit)
Assigned to:		CPU Architecture:	Any

Description:
We currently migrating our Perl application from HP-UX to Solaris (bot 64bit). That also means that we now have long doubles available meaning that we possibly get a lot more digits in the fractional part. Unfortunately Perl's DBI seems to pass all values as strings in prepared statements which unveils a bug in MySQL: It looks like the MySQL servers does not convert floating point numbers correctly to integer if it is passed as string AND it has more than 20 characters.

How to repeat:
> CREATE TABLE foobar ( n BIGINT UNSIGNED );

> INSERT INTO foobar (n) VALUES (1234567890.123);
Query OK, 1 row affected (0.01 sec)

> INSERT INTO foobar (n) VALUES (1234567890.1234567890123);
Query OK, 1 row affected (0.00 sec)

> INSERT INTO foobar (n) VALUES ('1234567890.123');
Query OK, 1 row affected (0.01 sec)

> INSERT INTO foobar (n) VALUES ('1234567890.1234567890123');
Query OK, 1 row affected, 1 warning (0.00 sec)

> SELECT * FROM foobar;
+---------------------+
| n                   |
+---------------------+
|          1234567890 | 
|          1234567890 | 
|          1234567890 | 
| 9223372036854775807 |  (!)
+---------------------+

Look at the last insert.

It looks like the MySQL servers does not convert floating point numbers correctly to integer if it is passed as string AND it has more than 20 characters. Some more experiments show that it seems to just concatenate the integer and the fractional digits and then storing that number to an integer.

Suggested fix:
MySQL should correctly truncate a floating point number to an integer as it does when passing as number instead of a string

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.51a, and inform about the results.

This problem is surely not repeatable with latest versions on 32-bit platform.

Hi,

I will test it with 5.0.51 but since the Release Notes do not indicate a problem like this I expect it to be still present. I have no 32bit releases available so I can provide results for 64bit only.

Kind regards,

Stephan

Thank you for the bug report. I could not repeat with latest source server
on Suse 10.3 64-bit. Please upgrade or wait the next release. Thanks in
advance.

Sorry I forgot to paste the result:

miguel@hegel:~/dbs> 5.0/bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.62-debug Source distribution

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

mysql> CREATE TABLE foobar ( n BIGINT UNSIGNED );
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO foobar (n) VALUES (1234567890.123);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO foobar (n) VALUES (1234567890.1234567890123);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO foobar (n) VALUES ('1234567890.123');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO foobar (n) VALUES ('1234567890.1234567890123');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM foobar;
+------------+
| n          |
+------------+
| 1234567890 | 
| 1234567890 | 
| 1234567890 | 
| 1234567890 | 
+------------+
4 rows in set (0.00 sec)

mysql> show variables like "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  | 
| version                 | 5.0.62-debug        | 
| version_comment         | Source distribution | 
| version_compile_machine | x86_64              | 
| version_compile_os      | suse-linux-gnu      | 
+-------------------------+---------------------+
5 rows in set (0.00 sec)

mysql>

Hi,

today I had the chance to upgrade to 5.0.51b and repeat my tests: The problem is still there:

mysql> CREATE TABLE foobar ( n BIGINT UNSIGNED );                 
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO foobar (n) VALUES ('1234567890.1234567890123');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM foobar;
+----------------------+
| n                    |
+----------------------+
| 12345678901234567890 | 
+----------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO foobar (n) VALUES ('1234567890.12');           
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM foobar;                           
+----------------------+
| n                    |
+----------------------+
| 12345678901234567890 | 
|           1234567890 | 
+----------------------+
2 rows in set (0.00 sec)

Here is the version information:

mysql> show variables like "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  | 
| version                 | 5.0.51b             | 
| version_comment         | Source distribution | 
| version_compile_machine | hppa2.0w            | 
| version_compile_os      | hp-hpux11.11        | 
+-------------------------+---------------------+

Depending on how the MySQL server converts the string containing the number with many fractional digits to a 64bit integer it might also be an OS bug.

Kind regards,

Stephan