Weird Float Conversion Result
-
We were tracking down a bug the other day and found this odd conversion issue. It only happens with a few numbers. I know conversions are a bad thing sometimes and we found a better solution. I'm just curious as to what is actually happening under the covers that caused this result to be 28 and not 29.
I would assume that the code would do the rounding (=.29) and then the math (=29). If the engine converted each part to INT .29 would equal zero.
DECLARE @tmp1 float = 0.289
SELECT CONVERT(INT, ROUND(@tmp1, 2) * 100.0)
-
I think the issue here is that not all values can by represented by floating point numbers. 0.29 is one of them.
SELECT cast(0.29 as float) -- shows 0.29 but is probably 0.2899999999
SELECT (cast(0.29 as float) * cast(100.0 as float)) - cast(29.0 as float) -- shows you the error, about 10^-15____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
That is weird. I assume you moved to numeric/decimal? Or something else?
Any other numbers that you found to be a problem? I could likely script this, but it's an interesting problem.
-
This is all correct behavior given the types involved. As Mark Cowne pointed out, what you are seeing in SSMS as 0.29 if you run the ROUND, or 29 if you do the ROUND and multiply, are actually neither of those values under the covers.
This is actually very easy to see, because it's just SSMS that is messing with the displayed values.
If you run this in SQLCMD, you'll see truer values:
1> DECLARE @tmp1 float = 0.289
2> SELECT ROUND(@tmp1, 2) * 100.0, ROUND(@tmp1,2);
3> go
------------------------ ------------------------
28.999999999999996 0.28999999999999998You'd see a similar thing if you inserted them into a FLOAT column in a table and used DBCC IND and DBCC PAGE to look at the stored values.
ParentObject Object Field VALUE
--------------------------------------- -------------------------------------------------------------- -------------------------------------------
Slot 0 Offset 0x60 Length 29 Slot 0 Column 1 Offset 0x4 Length 8 Length (physical) 8 float_nonsense 2.8999999999999996e+001
Slot 1 Offset 0x7d Length 29 Slot 1 Column 1 Offset 0x4 Length 8 Length (physical) 8 float_nonsense 2.8999999999999998e-001If you don't want occasional apparent rounding errors, then FLOAT's not a great choice 🙂
Cheers!
-
Yes, this was happening on an Excel import process. Users entered percentage values from 1-100. When they get to the server they are presented as floats by the C# import tool that we're using. We're storing the values as full-value INTs. We tested all possible values and found this behavior in:
29% - 0.28999999998
57% - 0.56999999998
58% - 0.57999999998
-
Thanks for the responses, everyone. Yes, once again tripped up by floats. And fooled by the fact that for most of the numbers the result worked out how we wanted. A good argument for using the correct conversions and robust testing.
After finding the error we converted to decimals.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply