Weird Float Conversion Result

  • souLTower

    SSCarpal Tunnel

    Points: 4026

    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)

     

  • Mark Cowne

    One Orange Chip

    Points: 26760

    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: 720983

    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: 27891

    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 9 months, 3 weeks ago by  Jacob Wilkins.
    • This reply was modified 9 months, 3 weeks ago by  Jacob Wilkins.
    • This reply was modified 9 months, 3 weeks ago by  Jacob Wilkins.
  • souLTower

    SSCarpal Tunnel

    Points: 4026

    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

    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 6 (of 6 total)

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