August 26, 2010 at 8:01 am
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. :w00t:
August 26, 2010 at 8:21 am
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
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
August 26, 2010 at 8:37 am
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'
August 26, 2010 at 8:43 am
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 floatset @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
August 26, 2010 at 10:42 am
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.'
August 26, 2010 at 2:02 pm
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.
August 26, 2010 at 3:11 pm
weitzera (8/26/2010)
for example 1, .9, .1, and .005 are representable just fine
The mantissa of a FLOAT is a 53-bit 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.
Floating-point calculations are done in the CPU with 80-bit 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.
August 27, 2010 at 3:51 am
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'
August 27, 2010 at 3:54 am
weitzera and scott,
Thank you for the explanations, I have a much better understanding of "float" approximation issues now
August 27, 2010 at 3:58 am
Hello All,
I'm not agree with explaination given.
"Floating point data is approximate (http://msdn.microsoft.com/en-us/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
August 27, 2010 at 4:13 am
following script doesn't go in infinite loop
DECLARE @i float,@rc int
set @i = 0
while @i <> 1
begin
set @i = @i + 0.8
set @i = @i + 0.1
set @i = @i + 0.1
print @i
end
But below script is going in infinite loop
DECLARE @i float,@rc int
set @i = 0
while @i <> 1
begin
set @i = @i + 0.7
set @i = @i + 0.1
set @i = @i + 0.1
set @i = @i + 0.1
print @i
end
Can any one know the answer on this??
Regards
Manmohan
August 27, 2010 at 4:33 am
Please try to see the trailing decimal digits using
print convert(decimal(38,36),@i)
I think the 1st example behaved well because it was dealing with an even number (8), while the 2nd does not because it was with odd numbers (7).
Because float comparison does not behave uniformly in all cases, I think this is why float comparison should not be used to test for equality, this is "equal", = and "not equal", <>.
But float may be used for less than or greater than, if accuracy/significance is required then the ideal less actual result may be used re: "while (ideal - actual) > 0.0001". The latter assumes the operations will converge close to the ideal point, otherwise there must be additional codes to check if divergence is happening in order to put a stop operation.
August 27, 2010 at 4:37 am
Gopinath Srirangan (8/27/2010)
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
You're only adding one float there, so the cumulative error isn't that great. In the original WHILE loop there were ten separate additions of 0.1, which caused more of an issue.
August 27, 2010 at 7:55 am
Actually, we're doing the exact same thing here. This was the original query:
while @i <> 1
begin
declare @a table(a int)
set @rc = @i + 0.9
set rowcount @rc
insert into @a select id from sysobjects
set @i = @i + 0.1
end
So, there was only one floating point addition which made @i<>1 TRUE, but in the if statement @i<>1 is FALSE.
August 27, 2010 at 8:03 am
Steve Eckhart (8/27/2010)
Actually, we're doing the exact same thing here. This was the original query:
while @i <> 1begin
declare @a table(a int)
set @rc = @i + 0.9
set rowcount @rc
insert into @a select id from sysobjects
set @i = @i + 0.1
end
So, there was only one floating point addition which made @i<>1 TRUE, but in the if statement @i<>1 is FALSE.
I don't see that. RC is set to @i + 0.9, then later 0.1 is added to @i. I'm not seeing the point where the value of @rc is copied into @i, which it would have to be for your statement to be correct. Maybe I'm just misreading the code?
Viewing 15 posts - 31 through 45 (of 54 total)
You must be logged in to reply to this topic. Login to reply