Weird Float Conversion Result

Question

Weird Float Conversion Result

souLTower

SSCarpal Tunnel

Points: 4026
More actions
December 11, 2019 at 2:32 pm

#3706989

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)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 1

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

Steve Jones - SSC Editor SSC Guru Points: 734434 More actions · Answer 2

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.

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 3

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.28999999999999998

You'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-001

If you don't want occasional apparent rounding errors, then FLOAT's not a great choice 🙂

Cheers!

This reply was modified 4 years, 4 months ago by Jacob Wilkins.
This reply was modified 4 years, 4 months ago by Jacob Wilkins.
This reply was modified 4 years, 4 months ago by Jacob Wilkins.

souLTower SSCarpal Tunnel Points: 4026 More actions · Answer 4

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

souLTower SSCarpal Tunnel Points: 4026 More actions · Answer 5

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.