List: General Discussion « Previous MessageNext Message » From: Gerald L. Clark Date: September 7 2006 5:10pm Subject: Re: Decimal versus Float Point Type View as plain text
```Renato Golin wrote:
> 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
>
How do you expect to split a dollar 3 ways?
It is not the math you do that determins whether you use float or
decimal, it is what you are modeling that is important.
Dollars are decimal, and dollar calculations must be rounded to the
nearest cent, or mill.

--
Gerald L. Clark
Supplier Systems Corporation
```