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

Jerry Schwartz wrote:
> An employee of a financial institution realized a similar vulnerability in
> their systems. It was common to calculate batch totals, which were cross
> checked to make sure that no transactions went astray, but he realized that
> so long as the batch totals came out right you could move money from one
> transaction to another. He programmed in a fraction-shaving scheme like the
> one above, only he shifted the missing fraction of a cent into his own
> account.

This is famous. I was never sure if this was a very good hoax or a true 
story but indeed it was quite possible sometime ago. I have a great one 
that has nothing to do with rounding numbers but is equally good:

Long before computers were used all over the globe one guy figured out 
that the transaction list was shared between two branches via one person 
taking the list in a paper. The guy then made a load of $1mi, deposited 
in his account, waited a few days and went to the first branch to 
withdraw the money, he got his balance and there was $1mi on his 
account. After he get his money (cash) he run to the second branch 
before the paper guy and got another balance, which was still showing 
the $1mi.

The bank was never able to prove him guilty of taking $2mi from his 
account while he had only $1mi.


> Holding currency amounts in double- or extended-precision floating point
> values avoids the overflow problem for any reasonable amount, but now you're
> back to the rounding issue caused by the fact that .01 is not exactly
> representable as a binary fraction.

I had problems with super-mega-huge-double precision fields and money 
manipulation. I always use integers for money no matter what the vendor 
say to me about they're precision. And I always use at least three 
decimal places and round them at the end because integers truncate 
instead of round.

If I'd need to convert currencies I would go for 4 or more decimal 
places, depending on which currencies I needed.


cheers,
--renato