October 16, 2007 at 10:41 am
I understand. Really - I do. I've had these "fights" with the CFO. The problem is - there's no such thing as "precision agnostic" in floating-point arithmetics: not dealing with the issue of precision means that those less aware than we think they're dealing in accurate numbers when we're not.
I am also observing the "differences" you describe - but I expect them since I was told they'd be there. They change because of the continuous "mini" rounding operations happening to certain numbers along the way. The rounding noise will trigger a little differently each time.
I'm merely addressing that you can't expect different behavior from what the definition tells you it's going to be.
The only way you will ever make the financial-types happy is to deal in "precise" data types: integers (all of them), and by extension - decimal/money (which are AFAIK stored as integers, with an extra piec of data stating where the decimal point is).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 17, 2007 at 1:52 am
thanks for joining in on my question, I didn't expect to evoke this interest level!
So ... yes I agree you shouldn't use floats - but it's not my system I'm just troubleshooting and you can't just turnaround to, say, the MD of a company and say "well you shouldn't use floats" without some backup and solid reasoning, after all from his point of view how do you explain that the same data gives different results on different servers?
Anyway I did get confirmation elsewhere, and it's to do with the cpu's. I have hazy memories of reading about floating point precision within the cpu back in the days of the p pro and early zeons - but I don't have the original article and I don't have a precise memory. I'm told ( and for all the reasons you guys pointed out ) that I'd never get consistant results from one server to another - it's suggested just cloning the database on the same server would also produce a different result - sadly i don't have the disk space to check that one.
so thanks very much for all your input.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
October 18, 2007 at 4:09 pm
Just a few of more points.
- Floating point is for scientific and numerical computations - stress on scientific here.
- Floating point is CPU specific - remember the Pentium 'floating point math' debacle a few years back in which identical computers were doing scientific calculations a a number of the computers did not agree ? The difference was in the CPU 'build level'.
- CPAs, MBAs and PhDs along with managers and directors that design and use application systems with float for numerical calculation that require precision are one 'M' away from being 'Mormons' !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
October 18, 2007 at 4:21 pm
Jeff Gray (10/16/2007)
I'm not pointing out anything other than the fact that the order in which the rows are added changes the result, thus suggesting that addition of floats is not associative. I'm precision agnostic, just trying to help Colin explain the results he is getting.I get different results every time I run the code.
This will illustrate the point of ordering:
SELECT 3E-16 + 3E-16 + 3E-16 + 1E1, 1E1 + 3E-16 + 3E-16 + 3E-16
3 numbers of 3E-16 together get over the precision threshold for 1E1.
But same numbers added to 1E1 one by one cannot change result - each of them is beyond the precision limit.
_____________
Code for TallyGenerator
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply