Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 sum(float) != sum(float) Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, October 16, 2007 8:47 AM
 SSC Eights! Group: General Forum Members Last Login: Monday, April 6, 2015 11:52 AM Points: 967, Visits: 389
 Colin, One more question. Can you attempt the float sum again, but this time specify the ordering of the rows? I ask because an unconfirmed source (wikipedia) seems to indicate that addition is NOT associative.Thus (a + b) + c = a + (b + c) is not always true for floating point numbers, if this article is true.select ID,[some float column] from [your table]order by IDCompute sum([some float column])
Post #411337
 Posted Tuesday, October 16, 2007 9:15 AM
 SSC Eights! Group: General Forum Members Last Login: Monday, April 6, 2015 11:52 AM Points: 967, Visits: 389
 I just tried a little experiment myself:create table foo (ID int identity,X float)set nocount ondeclare @ctr intselect @ctr = 0while @ctr < 1000BEGINinsert foo (X)select rand()select @ctr= @ctr + 1ENDselect X from fooorder by newid()compute sum(X)select X from fooorder by newid()compute sum(X)drop table fooThe compute clauses in the identical select statements return different results. This is apparently caused by the ordering of the data.
Post #411351
 Posted Tuesday, October 16, 2007 9:23 AM
 SSCrazy Group: General Forum Members Last Login: Tuesday, April 14, 2015 6:45 AM Points: 2,403, Visits: 3,431
 Not for me.I have tested on all these and your code gives exact same sum.SQL Server Express 2005 9.0.3186SQL Server Enterprise 2000 8.00.818SQL Server Standard 2000 8.00.818SQL Server Enterprise 2000 8.00.2039 N 56°04'39.16"E 12°55'05.25"
Post #411356
 Posted Tuesday, October 16, 2007 9:41 AM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 10:09 AM Points: 7,504, Visits: 17,943
 Jeff - the differences you're seeing are past the precision scale (7th number), aren't they? I'll bet you even get a substantially more "precise" result if and when you declare it as a Float(53). Funny how float point arithmetic works EXACTLY like BOL says it would..... ----------------------------------------------------------------------------------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?
Post #411364
 Posted Tuesday, October 16, 2007 10:04 AM
 SSC Eights! Group: General Forum Members Last Login: Monday, April 6, 2015 11:52 AM Points: 967, Visits: 389
 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.
Post #411371
 Posted Tuesday, October 16, 2007 10:40 AM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 10:09 AM Points: 7,504, Visits: 17,943
 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?
Post #411387
 Posted Wednesday, October 17, 2007 1:52 AM
 SSCrazy Group: General Forum Members Last Login: Wednesday, April 27, 2016 3:18 AM Points: 2,687, Visits: 715
 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. The GrumpyOldDBAwww.grumpyolddba.co.ukhttp://sqlblogcasts.com/blogs/grumpyolddba/
Post #411580
 Posted Thursday, October 18, 2007 4:09 PM
 Hall of Fame Group: General Forum Members Last Login: Friday, November 4, 2016 10:42 AM Points: 3,243, Visits: 2,467
 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."
Post #412460
 Posted Thursday, October 18, 2007 4:21 PM
 SSCertifiable Group: General Forum Members Last Login: Yesterday @ 7:01 PM Points: 5,669, Visits: 11,166
 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.
Post #412464

 Permissions