Aug 16, 2014, 3:14 PM
Post #10 of 10
Re: [zak100] mysql-perl : Float value out of range and duplicate key error
[In reply to]
OK, using DECIMAL(m,n) is solving your decimal display issue.
But there is a far far more important reason not to use floats for monetary amounts. Floating point arithmetics gives wrong results because of the way floating point numbers are stored within computers (a binary mantissa and a binary exponent). Because they are stored in binary format, they cannot be accurately representing decimal numbers that we are using in the daily life, especially for representing monetary amounts, just as 1/3 is 0.3333333333... and cannot be accurately represented in our decimal system.
As an imaginary example, and depending on your particular computer, you may have an invoice just adding two floating point numbers, say 4.12 and 3.13, and not end up with 7,25, but possibly 7.24 or 7.26. And I can tell you for a fact, if you produce dozens of millions of invoices per month like the company I am working for (as a freelance cunsultant, but that does not change anything, except that you will see the point later), even if that error is not happening too often, you'll have thousands of clients (probably more) complaining every month and that's gonna cost your company a fortune in Customer Service Representatives' work. And since CSRs don't know what to answer, the incidents will escalate to level 1 support, level 2 support, usually all the way up, and even the highest level support will often not know what to say. And that's again a lot of money wasted, with no result. So, they will call the software editor, the hardware manufacturer and some senior consultants, and that's where I am starting to make a lot of money.
Perhaps I should not reveal that secret to avoid drying my sources of income, but on this one, the cat has been out of the bag for so many decades that people should know better. And, believe it or not, although this problem has been known for more than half a century, I have been asked to solve this kind of problems at least 5 or 6 times in the last 10 years. And contrary to what I said jokingly above, I haven't been making so much money on these issues, because it usually did not take me very long to find the origin of the problem (even though it is sometimes obscured by other things), but solving these problems has contributed to establish my expert reputation and my capability to demand a higher daily rate.
The way to solve the problem in (I guess almost) all databases and accounting software packages, and in programs using them, is to use internally integers, for which calculations are accurate. So the database is really using integers to store, for example, not dollars, but cents (that's if the second "n" number in the DECIMAL(m,n) is 2, there are other reasons for which you may prefer to do the calculations in tenths of cents or less). And anytime you do a select, insert, update or other SQL operation on such a number, the database engine is making the conversion between dollars and cents without telling you, so that you don't have to worry about that.
There are a number of other gotchas in this field. For example, even using integer representations of monetary amount, OK, you solve the machine internal representation of decimal numbers, but there are a number of other reasons for which you might end up with very difficult to solve rounding problems. Date proratas can also be a serious headache. etc.
May be you want to read this important document:
http://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html to get an idea of the problems involved.