MySQL Bugs: #20517: Fixed point vs. Floating numbers with high precision mis-treated.

Bug #20517	Fixed point vs. Floating numbers with high precision mis-treated.
Submitted:	17 Jun 2006 10:12	Modified:	19 Jul 2006 23:41
Reporter:	Robin Johnson	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	4.1.20, 5.0.22	OS:	Linux (Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
These two numbers are identical (spaces added to illustrate):
   12345678901234567890.0123456789
0.123456789012345678900123456789E20

The type in use here is DECIMAL(30,10) - 20 digits before the decimal point, and 10 after.

However over multiple versions of MySQL, they are not treated as identical.

In MySQL4.1, both of them are truncated to 17 accurate digits.
In MySQL5.0, the un-normalized form works properly, but the normalized form is broken, with only 15 accurate digits.

As a temporary workaround, I am quoting the numbers.

How to repeat:
For MySQL 4.1.20:
# no CAST function here, so this displays the error instead
CREATE TABLE a (n DECIMAL(30,10));
INSERT INTO a VALUES (12345678901234567890.0123456789),(0.123456789012345678900123456789E20);
SELECT * FROM a;
+---------------------------------+
| n                               |
+---------------------------------+
| 12345678901234567168.0000000000 |
| 12345678901234567168.0000000000 |
+---------------------------------+

In MySQL 5.0.22:
SELECT CAST(0.123456789012345678900123456789E20 AS DECIMAL(30,10));
+-------------------------------------------------------------+
| cast(0.123456789012345678900123456789E20 as decimal(30,10)) |
+-------------------------------------------------------------+
|                             12345678901234570000.0000000000 | 
+-------------------------------------------------------------+
SELECT CAST(12345678901234567890.0123456789 AS DECIMAL(30,10));
+---------------------------------------------------------+
| CAST(12345678901234567890.0123456789 AS DECIMAL(30,10)) |
+---------------------------------------------------------+
|                         12345678901234567890.0123456789 | 
+---------------------------------------------------------+

Suggested fix:
1. Fix the actual bug - the fact that it almost works in 5.0 indicates that the code is mostly available, just needs some tweaking.
2. Update chapter 9.1.2 of the documentation to indicate the workaround for earlier versions.

Possibly of interest, I posted on my blog a request for users of other major RDBMS systems to run a similar test case, and post their results - It does seem that a lot of other systems have similar issues.
http://robbat2.livejournal.com/200033.html

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

Please, read carefully documentation about Precision Math and NUMERIC values:
http://dev.mysql.com/doc/refman/5.0/en/precision-math.html, http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html, http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html

From the documentation that you linked:
"As of 5.0.3, MySQL performs DECIMAL operations with a precision of 64 decimal digits, which should solve most common inaccuracy problems when it comes to DECIMAL columns."

Yet this is clearly incorrect here:

In MySQL 5.0.22:
SELECT CAST(0.123456789012345678900123456789E20 AS DECIMAL(30,10));
+-------------------------------------------------------------+
| cast(0.123456789012345678900123456789E20 as decimal(30,10)) |
+-------------------------------------------------------------+
|                             12345678901234570000.0000000000 | 
+-------------------------------------------------------------+

Unless 0.123456789012345678900123456789E20 was treated as a floating point number, which is not clear in any of the documentation - Documentation section '9.1.2. Numbers' should indicate which numbers are fixed point, and which numbers are floating point.

Alternatively, may I submit a patch that correctly treats 0.123456789012345678900123456789E20 as a fixed point number?

Hi,

"Alternatively, may I submit a patch that correctly treats
0.123456789012345678900123456789E20 as a fixed point number?"

Yes please subtmit the patch for our development team to analyze.

Thanks in advance.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".