Jerry Schwartz wrote:
> The difference is that, for example, .01 can be represented exactly
> in decimal; but float types are binary, so .01 cannot be represented
> exactly. This can lead to all kinds of trouble when doing arithmetic,
> the errors accumulate.
Yes! but that can also lead to some other problems... ;) Check this example:
myslq> create table numbers (a decimal(10,2), b float);
myslq> insert into numbers values (100, 100);
mysql> select @a := (a/3), @b := (b/3), @a * 3, @b * 3 from numbers \G
*************************** 1. row ***************************
@a := (a/3): 33.333333333
@b := (b/3): 33.333333333333
@a + @a + @a: 99.999999999000000000000000000000
@b + @b + @b: 100
The decimal did exactly what's supposed to do on this cases, it
truncated the rest, thus loosing the 1/3 part.
So for sums the decimal is better, but for divisions the float is
better, up to some point, of course. I mean, using DECIMAL will not give
you a "fail proof arithmetic" in any means.
> It's one reason why most people write their loops with "< x + 1"
> rather than "= x". That gets past the problem, but if you are adding
> together many values the final answer may be wrong.
I'd rather use a better algorithm instead of a work around in those
cases... ;)
cheers,
--renato