Exact decimal division in MySQL

Question

How to properly divide two DECIMAL values in MySQL and get a result accurate to the number of digits defined in the column type?

Example:

select cast(1/2 as decimal(4,4)),
cast(1 as decimal(4,4))/2,
cast(1 as decimal(4,4))/cast(2 as decimal(4,4));

Results:

'0.5000', '0.49995000', '1.00000000'

Why is the second result innacurate? Why is the third result not '0.5000'?

Note: I can't just use the first form, I need to perform calculations with columns stored as decimals.

Then you don't have a problem, as (3) will apply if both operands are decimal. I can't explain (2), but if you cast the result of (3) to decimal you should get `0.5000`. — user207421, Apr 02 '15 at 22:30
@EJP: sorry, but I still have a problem. The result of (3) cast to a decimal is 0.9999... Try `select cast(cast(1 as decimal(4,4))/cast(2 as decimal(4,4)) as decimal(4,4));`. — Ferdinand.kraft, Apr 06 '15 at 12:45

score 11 · Accepted Answer · answered Apr 07 '15 at 13:10

The problem is that DECIMAL data type declaration requires 2 arguments, the first one being the total number of digits, including the fractional part.

According to the docs (http://dev.mysql.com/doc/refman/5.7/en/precision-math-decimal-characteristics.html):

The declaration syntax for a DECIMAL column is DECIMAL(M,D). The ranges of values for the arguments in MySQL 5.1 are as follows:

M is the maximum number of digits (the precision). It has a range of 1 to 65. (Older versions of MySQL permitted a range of 1 to 254.)

D is the number of digits to the right of the decimal point (the scale). It has a range of 0 to 30 and must be no larger than M.

So if you use 5 total digits instead of 4, everything is ok:

select cast(1/2 as decimal(5,4)),
cast(1 as decimal(5,4))/2,
cast(1 as decimal(5,4))/cast(2 as decimal(5,4)),
cast(cast(1 as decimal(5,4))/cast(2 as decimal(5,4)) as decimal(5,4));

Results

0.5000, 0.50000000, 0.50000000, 0.5000

Exact decimal division in MySQL

1 Answers1