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. On a more serious note, there are other ways to deal with these issues. One 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 -----Original Message----- From: Renato Golin [mailto:[EMAIL PROTECTED] Sent: Thursday, September 07, 2006 6:40 PM To: Gerald L. Clark Cc: mysql@lists.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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
