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.
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