SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SUM of FLOAT inconsistency


SUM of FLOAT inconsistency

Author
Message
brewmanz
brewmanz
SSC Eights!
SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)

Group: General Forum Members
Points: 903 Visits: 406
Comments posted to this topic are about the item SUM of FLOAT inconsistency
Ronald H
Ronald H
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2022 Visits: 634
Great QotD! One of the rare ones that isn't easy to cheat on.

Thumbs up!

Ronald

Ronald Hensbergen

Help us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/
-------------------------------------------------------------------------
2+2=5 for significant large values of 2
Mark Sumner
Mark Sumner
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1279 Visits: 456
3 of the options seemed very unlikely, and the answer is easily verified by changing the order of @float* inserts. Cheating rules ok Wink
Carlo Romagnano
Carlo Romagnano
SSCrazy Eights
SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)

Group: General Forum Members
Points: 9323 Visits: 3440
That's true:
DECLARE @SumA float, @SumB float
DECLARE @MyFloat1 float, @MyFloat2 float, @MyFloat3 float
DECLARE @MyTable table
(
ID int primary key identity,
NumA float,
NumB float
)
SET @MyFloat1 = 10000000000020000
SET @MyFloat2 = -10000000000010000
SET @MyFloat3 = 1
INSERT INTO @MyTable
SELECT @MyFloat1, CAST(@MyFloat3 AS FLOAT)
UNION
SELECT @MyFloat2, @MyFloat1
UNION ALL
SELECT @MyFloat3, @MyFloat2

SELECT SUM(NumA), SUM(NumB) FROM
(select top 100 * from @MyTable
order by 1
) AS A
SELECT SUM(NumA), SUM(NumB) FROM
(select top 100 * from @MyTable
order by 2
) AS B

Result:
(3 row(s) affected)

10001 10000

(1 row(s) affected)


10000 10001

(1 row(s) affected)
Chris.Strolia-Davis
Chris.Strolia-Davis
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 83
Were the declarations of @SumA and @SumB supposed to confuse us on this one? I found them to be completely unnecessary.

Thanks for posting. I have often found SQL calculations that create inconsistent or incorrect results as a misunderstanding of datatype size and rounding issues which can often be fixed by simply calculating in the appropriate order.

Of course, even the most experienced query writers can miss this sort of thing sometimes. If the calculations are critical, it is usually a good idea to put in some sort of validation check so that if there are problems, they can be found early and corrected quickly.
Carlo Romagnano
Carlo Romagnano
SSCrazy Eights
SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)

Group: General Forum Members
Points: 9323 Visits: 3440
Main rule is never to use float, because of unwanted effetcs:
Here the version with decimal. It always is correct.

DECLARE @SumA float, @SumB float
DECLARE @MyFloat1 float, @MyFloat2 float, @MyFloat3 float
DECLARE @MyTable table
(
ID int primary key identity,
NumA DECIMAL(17,0),
NumB DECIMAL(17,0)
)
SET @MyFloat1 = 10000000000020000
SET @MyFloat2 = -10000000000010000
SET @MyFloat3 = 1
INSERT INTO @MyTable
SELECT @MyFloat1, CAST(@MyFloat3 AS FLOAT)
UNION
SELECT @MyFloat2, @MyFloat1
UNION ALL
SELECT @MyFloat3, @MyFloat2

SELECT SUM(NumA), SUM(NumB) FROM
(select top 100 * from @MyTable
order by 1
) AS A
SELECT SUM(NumA), SUM(NumB) FROM
(select top 100 * from @MyTable
order by 2
) AS B
brewmanz
brewmanz
SSC Eights!
SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)

Group: General Forum Members
Points: 903 Visits: 406
This is my first QotD, and I'm pleased that it's gone quite well. It was prompted by outrage at missing out on points for another QotD (Sep 23, 2008, Accessing and changing data 2008) where it was deemed that the SqlServer 2008 GROUP BY GROUPING SETS was not the same as equivalent GROUP BY code because "Aggregates on floating-point numbers might return slightly different results." I protested but didn't get my 2 points back. So I wrote this QotD to prove my point, that identical aggregate code on an identical set of FLOAT numbers can ALSO produce "slightly different results", depending on the order of execution (which *should* be irrelevant in a perfect theoretical world without truncation).

BTW playing with the UNION & UNION ALL can reverse the results, but they will always (in my experience) produce the 2 differing results. Others noticed similar effects by sorting etc.

Carlo Romagnano (10/13/2008)
Main rule is never to use float, because of unwanted effects:
Here the version with decimal. It always is correct.

It always is correct WITH THIS SET OF DATA. Try adding a couple of zero(e)s at the end of each of the 3 numbers and watch "Msg 8115, Level 16, State 6, Line 12 - Arithmetic overflow error converting float to data type numeric." appear.
The FLOAT still works, and produces even stranger (but predictable) results of 1000164 and 1000192.
That's the point - if the numbers were coming from an external source (and may have decimal places), you might not have the luxury of knowing the full range of numbers used. As a chemical engineer I learnt about floating point numbers before integers, and converted all measurements to Megafurlongs per microfortnight for consistency (the speed of light is just above 1.8 with those units - far more manageable).

Chris.Strolia-Davis (10/13/2008)
Were the declarations of @SumA and @SumB supposed to confuse us on this one? I found them to be completely unnecessary.

The declarations were not intended to confuse; as a software developer for many decades (mainly with C# for the last 6 years, with some T-SQL on the side), I like to define all my variables of a type that I decide. I don't want to rely on some box of blacklegging binary bits* to make the decision for me. With the latest releases of dotNet allowing implicit declaration types, I'm a bit nervous actually.

Brewmanz
aka Bryan White, NZ

* Thanks to Douglas Adams for this term
Chris.Strolia-Davis
Chris.Strolia-Davis
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 83
brewmanz (10/13/2008)

Chris.Strolia-Davis (10/13/2008)
Were the declarations of @SumA and @SumB supposed to confuse us on this one? I found them to be completely unnecessary.

The declarations were not intended to confuse; as a software developer for many decades (mainly with C# for the last 6 years, with some T-SQL on the side), I like to define all my variables of a type that I decide. I don't want to rely on some box of blacklegging binary bits* to make the decision for me. With the latest releases of dotNet allowing implicit declaration types, I'm a bit nervous actually.


All I am saying is that I don't see you actually using @SumA or @SumB in this query. They were declared, but don't seem to have been used. If you comment out that line altogether, it changes nothing ... that I'm aware of Wink.
brewmanz
brewmanz
SSC Eights!
SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)SSC Eights! (903 reputation)

Group: General Forum Members
Points: 903 Visits: 406
Chris.Strolia-Davis (10/13/2008)

All I am saying is that I don't see you actually using @SumA or @SumB in this query. They were declared, but don't seem to have been used. If you comment out that line altogether, it changes nothing ... that I'm aware of Wink.

Doh! You are quite right.
When I re-read the QotD text, I thought that maybe it had been editorially modified as I *did* use them in my earlier playing, assigning SUM(Num%) to the @Sum% variables. Maybe they'd changed things.
But no; I've checked my draft pre-submission, and the editors are not to blame.
Mea culpa.

Please don't tell my daughter. I take great delight in proofreading her work and finding mistakes (she works for a brochure publishing company). Sadly, it seems that I am capable of making mistakes, too. Soon I'll be into double figures this century ;-)

Regards

brewmanz
antony-688446
antony-688446
Mr or Mrs. 500
Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)

Group: General Forum Members
Points: 568 Visits: 371
Great QOTD, from another Douglas Adams fan, SQL Using, Kiwi! There can't be many of us! Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search