On 2015/12/16 4:05 PM, E.Pasma 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
I think the bit that Keith tried to highlight is that we should always refrain from storing errors. Calculated errors are fine because we can at any time revisit the calculation procedures, we can refine and perhaps opt for more significant digits - but we can ALWAYS guarantee the accuracy-level of the calculated result. However, storing wrong values (or let's call them "approximate" values if you like) is pure evil. If the data that feeds my calculator is flawed by whichever tiny amount, or stored with errors, or retrieved with errors, then there is pretty much NOTHING I can do to revisit the original / "really really real" values of what actually happened or in any way confirm any degree of certainty on my calculation, because my axioms are wrong. And let's be clear*, No computer value is absolute in accuracy - but in the same way that 1.66666666666666666667 is closer to the real value than 1.67, in computer and IEEE:754 terms, 1.29999999999999978 might well be much closer to 1.3 than the 1.300000010378 which may be the next representable IEE:754 bit formation that gets stored when you put "1.3" into a float database field. * I'm just using artistic license here, did not calculate the real values, but the principle remains - I think Keith posted a way of finding the representable minimum differences between specific IEE:754 representations recently, if anyone is interested in the actual values. Cheers, Bag-of-water-Ryan. :)