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 20:30	Modified:	16 Jan 2006 20:25
Reporter:	Dean Ellis
Status:	Can't repeat
Category:	Server	Severity:	S3 (Non-critical)
Version:	5.0	OS:
Assigned to:	Konstantin Osipov	Target Version:

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.