On Wed, 16 Dec 2015 15:05:34 +0100 "E.Pasma" <pasma10 at concepts.nl> wrote:
> 16 dec 2015, Keith Medcalf: > >> BEGIN; > >> UPDATE fmtemp SET bal = ROUND(bal,2) + 123.45; > >> (repeat a 1.000.001 times > >> END; > >> SELECT bal FROM fmtemp; > >> 123450123.45 > > > > You should NEVER round as you have done above. You may get lucky > > and the errors may cancel each other out, or you may get more > > usual results where the error equals the theoretical max of the sum > > of the absolute value of all the truncated values, which can be > > quite significant depending on the scale of the number you are > > dealing with (and theior scales relative to each other). > > Hello, I was only trying to digest JKL's post and the result looks > good. The example prints the value as it is in the database and > shows that there is no accumulated error there. I do not see a > counter example (not yet). > > Ok this does not work of any scale of numbers. But a solution with > integers neither does Keith's advice is well taken. Keep the real number; round for presentation. I always say, "store what you know". Yes, starting from zero you can add 123.45 to a double-precision floating point number for a very, very long time, about 81,004,455,245 times, before the error will appear in pennies. When it does, you'll have 13 digits left of the decimal. That's on the order of the US GDP. We don't measure things like that down to the penny, so no one will know if you're wrong. ;-) The thing to keep in mind is that you get ~15 decimal places of precision. The decimal floats. You can put it way on the left, and measure tiny things accurately. You can put it on the right, and measure astronomical things accurately. Unless you care about millimeters to the moon, it will do the job. Whole books have been written on numerical accuracy. I suspect if that mattered to your application you'd know about it. My advice is to let the engineers worry about it -- they did, years ago -- and accept rounded output unless and until you have an example of a computation for which that doesn't work. --jkl
