MySQL can't handle large values even with DECIMAL

Question

DECIMAL is supposed to be exact. It isn't. It rounds like crazy!

TABLE:
account_balance DECIMAL(18,4)

If I insert 43210987654321.9999 it rounds to 43210987654322.0000

If I insert 43210987654321.9876 it rounds to 43210987654321.9840

WHY? If I use numbers larger than 18 during table creation it is even worse.

EDIT:
I posted this clarification edit because some people do not know about the DECIMAL data type.

The DECIMAL data type is a FIXED POINT data type as opposed to a FLOATING POINT data type.

The reason it is used is:
(1) Store massive numbers that have decimal values with exact precision.
(2) Prevent rounding errors that can not be prevented when using floating point calculations. Thus any calculation on DECIMAL values should be exact... no rounding error.

POSTRESQL
http://www.postgresql.org/docs/8.1/static/datatype.html

numeric -- user-specified precision -- exact --no limit

The type numeric can store numbers with up to 1000 digits of precision and perform calculations exactly. It is especially recommended for storing monetary amounts and other quantities where exactness is required. However, arithmetic on numeric values is very slow compared to the integer types, or to the floating-point types described in the next section.

The types decimal and numeric are equivalent. Both types are part of the SQL standard.

---

MySQL

DECIMAL( , ) A DOUBLE stored as a string , allowing for a fixed decimal point.
http://www.htmlite.com/mysql003.php

MySQL DOC
Fixed-Point (Exact-Value) Types

The DECIMAL and NUMERIC types store exact numeric data values. These types are used when it is important to preserve exact precision, for example with monetary data. In MySQL, NUMERIC is implemented as DECIMAL, so the following remarks about DECIMAL apply equally to NUMERIC.

As of MySQL 5.0.3, DECIMAL values are stored in binary format. Previously, they were stored as strings, with one character used for each digit of the value. http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

The maximum value of 65 for M means that calculations on DECIMAL values are accurate up to 65 digits. This limit of 65 digits of precision also applies to exact-value numeric literals, so the maximum range of such literals differs from before. (In older versions of MySQL, decimal values could have up to 254 digits. However, calculations were done using floating-point and thus were approximate, not exact.)

Calculations involving exact-value decimal numbers are accurate to 65 digits. This is fewer than the maximum number of digits permitted before MySQL 5.0.3 (254 digits), but the exact-value precision is greater. Calculations formerly were done with double-precision floating-point, which has a precision of 52 bits (about 15 decimal digits).

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

Answer 1

I tested on my local mysql 5.1.36 and it does not round. What version are you using.

Also, what are you using to insert. Are you sure it is mysql rounding and not the storage before inserting to mysql.