MySQL Bugs: #7670: Loss of precision for some integer values stored into DOUBLE column

Bug #7670	Loss of precision for some integer values stored into DOUBLE column
Submitted:	4 Jan 2005 19:30	Modified:	16 Jan 2006 19:25
Reporter:	Dean Ellis	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0	OS:
Assigned to:	Konstantin Osipov	CPU Architecture:	Any

Description:
Some integer values appear to lose precision (or to be miscast as signed long long) when being inserted into DOUBLE columns.  There is a workaround, but it does not help when using mysqldump and reloading the data.

How to repeat:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 ( a DOUBLE(53,0) );
INSERT INTO t1 VALUES (9988317491112007680) ,(99883133042600208184115200);
SELECT a FROM t1;
TRUNCATE t1;
INSERT INTO t1 VALUES (9988317491112007680.0) ,(99883133042600208184115200.0);
SELECT a FROM t1;

Suggested fix:
n/a

As our manual states, values in DOUBLE column, regardless of precision specified, are stored in the 8-byte floating point value. 

Mantissa part, as per IEEE specs, in 8-byte can contain up to 15 significant digits, with rounding going up to the 16th digit.

I was wrong, as test case works for numbers with larger number of digits and not for the 
one with smaller number of digits.

At least, it is not documented in our manual.

This bug will be fixed in 5.0, for 4.0 and 4.1 it's documented in 'known bugs' section.

See also BUG#5083, BUG#5913

This appears to be fixed in 5.0:

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE t1 ( a DOUBLE(53,0) );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 VALUES (9988317491112007680) ,(99883133042600208184115200);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT a FROM t1;
+----------------------------+
| a                          |
+----------------------------+
|        9988317491112007680 |
| 99883133042600208184115200 |
+----------------------------+
2 rows in set (0.00 sec)

mysql> TRUNCATE t1;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (9988317491112007680.0) ,(99883133042600208184115200.0);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT a FROM t1;
+----------------------------+
| a                          |
+----------------------------+
|        9988317491112007680 |
| 99883133042600208184115200 |
+----------------------------+
2 rows in set (0.00 sec)

Adding the test case  to the test suite and closing the bug.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/1160

An additional note: the bug in an erroneous conversion to (long long), which was applied prior to conversions to double is fixed. However, for string -> double conversion, MySQL uses sprintf() provided by the operating system C library.
The test case added to 5.0 produces different results on QNX, exactly due to discrepancies in the library.
This is a documented behaviour.