

SSCommitted
Group: General Forum Members
Last Login: Friday, December 19, 2014 8:20 AM
Points: 1,520,
Visits: 1,060


The main point of the QOD about testing for equality of floating point data is an excellent one. But, I admit I am more fascinated by this:
Hugo Kornelis (8/26/2010) Declarations are not relly executable statements. They are processed when a batch is parsed, not when it it executed. The declaration has to be positioned before the use in "left to right / top to bottom" order, not in execution order.
I spent some time playing with Hugo's illustrations. Whoa. This seems like a fundamental thing of which I was entirely unaware.




SSCrazy
Group: General Forum Members
Last Login: Today @ 8:38 AM
Points: 2,582,
Visits: 3,078


mccork (8/25/2010) The answer was almost obvious from the "do not run it on production server" recommendation.
But, a good question for highlighting the pitfalls of "float".
That is what led me to guess correctly on this one, especially since that recommendation is literally one that goes without saying on SSC (no one should ever run QOTD code on production, even if they think that nothing can go wrong... Murphy's law and all that)  so I still need to read more about why this is. But I agree it is still a good question and leads to fruitful discussion about loops and floats.
Thanks, webrunner
 "Operator! Give me the number for 911!"  Homer Simpson
"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'" Ref.: http://tkyte.blogspot.com/2009/02/sqljoke.html




Ten Centuries
Group: General Forum Members
Last Login: Monday, March 23, 2015 11:04 AM
Points: 1,050,
Visits: 8,664


Very good question. I discovered that whether or not 0.9 + 0.1 <> 1 is not consistent I guess depending on sequence. If you execute the following in SQL Server 2005, you get "Equal":
declare @f float set @f = 0.9 set @f = @f + 0.1 if @f <> 1 print 'Not Equal' else print 'Equal'
Steve Eckhart




SSCrazy
Group: General Forum Members
Last Login: Today @ 1:41 AM
Points: 2,864,
Visits: 2,737


Steve Eckhart (8/26/2010)
Very good question. I discovered that whether or not 0.9 + 0.1 <> 1 is not consistent I guess depending on sequence. If you execute the following in SQL Server 2005, you get "Equal": declare @f float set @f = 0.9 set @f = @f + 0.1 if @f <> 1 print 'Not Equal' else print 'Equal'
try this one: declare @f float set @f = 0 set @f = @f + 0.1 + 0.1 + 0.1 + 0.1 + 0.1 + 0.1 + 0.1 + 0.1 + 0.1 + 0.1 print 1  @f




SSC Eights!
Group: General Forum Members
Last Login: Thursday, June 18, 2015 12:51 PM
Points: 884,
Visits: 622


Pete,
No, by approximate, it is not meant that no finite numbers are representable, for example 1, .9, .1, and .005 are representable just fine. It is just an acknowledgment that certain numbers are not representable in base two, just as some numbers are not representable in base 10
In the example given, the problem is that .3 and .7 are not representable. A simple algorithm for converting a base 10 fraction to a base 2 fraction is as follows:
set number = .1 (remember, this is binary, .1 = 2^1, or 1/(2^1) or .5 in decimal) set exp = 1 while number <> target if number > target set number = number  2^exp end if set exp = exp  1 set number = number + 2^exp end while
If you follow this for .3 you get: .01001100110011001100110011... ad nauseum
Compare this to the process of converting .1 from base 3 into base 10 (.1 in base 3 is the ratio 1/3)
A common question when this explanation is given is: 3 can be represented in base 2, and 1 can be represented, so why doesn't the floating point data type represent the value as "3 * 10^1" The answer as I understand it is that floats are highly optimized for storage space, so for the same number of bits, you would get less range of representable numbers from a float specified this way. Feel free to correct me on this if there were other considerations I'm forgetting.
Dan Guzman  Not the MVP (7/22/2010) All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'




Ten Centuries
Group: General Forum Members
Last Login: Sunday, June 28, 2015 2:22 PM
Points: 1,396,
Visits: 3,052


Here's another little script to demonstrate the effect of using float. Although the local variable looks pretty good out of a simple "print @i", converting the value to decimal bears out the internal inaccuracy.
declare @i float set @i = .1 while @i <= 10.0 begin print @i print convert(decimal(38,36),@i) set @i = @i + .1 End The first few rows returned are here:
0.1 0.100000000000000000000000000000000000 0.2 0.200000000000000000000000000000000000 0.3 0.300000000000000060000000000000000000 0.4 0.400000000000000000000000000000000000 0.5 0.500000000000000000000000000000000000 0.6 0.600000000000000000000000000000000000 0.7 0.700000000000000000000000000000000000 0.8 0.799999999999999880000000000000000000 0.9 0.899999999999999880000000000000000000 1 0.999999999999999880000000000000000000 and the last few are here:
9.6 9.599999999999981900000000000000000000 9.7 9.699999999999981500000000000000000000 9.8 9.799999999999981200000000000000000000 9.9 9.899999999999980800000000000000000000 10 9.999999999999980400000000000000000000 It's now obvious that 9.9999999999999804 is not equal to 10.




SSCrazy
Group: General Forum Members
Last Login: Monday, June 29, 2015 2:36 PM
Points: 2,875,
Visits: 1,249


weitzera (8/26/2010) for example 1, .9, .1, and .005 are representable just fine
The mantissa of a FLOAT is a 53bit binary fraction, and it can exactly represent only rational numbers whose denominator is a power of 2. This means that 0.1 can't be represented exactly, the value stored is actually closer to 0.1000000000000000055511. Neither can 0.9 or 0.005. Any integer value under 15 digits long can be stored exactly, as can binary fractions like 0.5, 0.25, 0.125, etc.
Floatingpoint calculations are done in the CPU with 80bit precision and the result is rounded to 53 bits, which in many cases works very well and the approximation errors cancel out. 10 x 0.1 results in exactly 1.0, for example. But repeatedly adding 0.1 means the same tiny error is added at each step and it eventually becomes noticable as shown in the examples posted above.




SSCEnthusiastic
Group: General Forum Members
Last Login: Thursday, May 7, 2015 11:22 AM
Points: 169,
Visits: 324


Hi, But another sample query as shown below gives expected result which contradicts from while loop.
DECLARE @i float set @i = .9 set @i = @i + .1
 Query 1 if @i <> 1 print 'True' else print 'False'
Answer: 'False'
 Query 2 if @i = 1 print 'True' else print 'False'
Answer: 'True'
Can anyone pls explain on difference in this two. (ie while loop resulted is true but if condition in query 1 returned 'False'




SSC Journeyman
Group: General Forum Members
Last Login: Thursday, November 14, 2013 1:05 AM
Points: 96,
Visits: 277


weitzera and scott,
Thank you for the explanations, I have a much better understanding of "float" approximation issues now




Grasshopper
Group: General Forum Members
Last Login: Tuesday, June 25, 2013 10:32 AM
Points: 11,
Visits: 23


Hello All, I'm not agree with explaination given. "Floating point data is approximate (http://msdn.microsoft.com/enus/library/ms173773.aspx). So an increment of 0.1 will never be equal to 1 and the exit condition @i <> 1 always is true (infinite loop).
The script contains two bad mistakes: floating comparison and a WHILE condition that easily falls into infinite loop. "
Following script is also increasing the variable @i, but it iterate loop once.
DECLARE @i float,@rc int set @i = 0
while @i <> 1 begin declare @a table(a int) set @i = @i + 0.9 insert into @a select 1 set @i = @i + 0.1 end
 LAST SELECT SELECT * FROM @a



