Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

sum(float) != sum(float) Expand / Collapse
Author
Message
Posted Tuesday, October 16, 2007 8:47 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, June 22, 2014 6:53 PM
Points: 967, Visits: 388
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 ID
Compute sum([some float column])


Post #411337
Posted Tuesday, October 16, 2007 9:15 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, June 22, 2014 6:53 PM
Points: 967, Visits: 388
I just tried a little experiment myself:

create table foo (
ID int identity,
X float)

set nocount on

declare @ctr int
select @ctr = 0

while @ctr < 1000
BEGIN
insert foo (X)
select rand()

select @ctr= @ctr + 1
END

select X from foo
order by newid()
compute sum(X)

select X from foo
order by newid()
compute sum(X)

drop table foo

The 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

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 3:35 PM
Points: 2,393, Visits: 3,399
Not for me.

I have tested on all these and your code gives exact same sum.


SQL Server Express 2005 9.0.3186
SQL Server Enterprise 2000 8.00.818
SQL Server Standard 2000 8.00.818
SQL 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

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:20 PM
Points: 7,120, Visits: 15,014
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!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, June 22, 2014 6:53 PM
Points: 967, Visits: 388
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

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:20 PM
Points: 7,120, Visits: 15,014
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

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 9, 2014 6:02 AM
Points: 2,674, Visits: 697
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 GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Post #411580
Posted Thursday, October 18, 2007 4:09 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:55 PM
Points: 3,194, Visits: 2,292
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' !





Regards
Rudy Komacsar
Senior Database Administrator

"Ave Caesar! - Morituri te salutamus."
Post #412460
Posted Thursday, October 18, 2007 4:21 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, July 20, 2014 5:23 PM
Points: 4,576, Visits: 8,341
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
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse