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.

Answer 1

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