From: Date: September 7 2006 4:37pm
Subject: Re: Decimal versus Float Point Type
List-Archive: http://lists.mysql.com/mysql/201710
Message-Id: <45002E9A.3060904@ebi.ac.uk>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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