Two famous (if apocryphal) stories and a more serious contribution:
A bank decided that it would truncate, rather than round, when doing
interest calculations. They decided that no one would ever miss the average
half-cent. They reckoned without all of the people who had certificates that
paid exactly $100 dollars a month, and who screamed bloody murder when the
got $99.99 instead.
Second story:
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.
a more serious note, there are other ways to deal with these issues.
is to maintain all amounts in cents, rather than in dollars. This avoids the
rounding problem, so long as you use a floating point type with enough
precision; but it doesn't work well if you are exchanging currencies. Even
if you are using decimal data types, you have to be very careful about your
precision if you will be using the same field to hold different currencies.
A field designed with euros in mind might not hold the equivalent amount in
yen.
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.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
Message
From: Renato Golin [mailto:renato (AT) ebi (DOT) ac.uk]
Sent: Thursday, September 07, 2006 6:40 PM
To: Gerald L. Clark
Cc: mysql (AT) lists (DOT) mysql.com
Subject: Re: Decimal versus Float Point Type
How do you expect to split a dollar 3 ways?
Sorry, I should have added more smiles, it was supposed to be a joke about
the dollars ;)
But still, I could win a lot of money by distributing people's money to
their three kids and getting 1 cent out of every operation. :D

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,