Predict output for ROUND function

  • Kari Suresh

    Hall of Fame

    Points: 3712

    Comments posted to this topic are about the item Predict output for ROUND function

    KSB
    -----------------------------
    Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha

  • vk-kirov

    SSCertifiable

    Points: 7686

    If I change the variables to anything from float(25) to float(53), I get the wrong answer (75.42)

    What's the relation between this value and the values from the question (165.755, 165.555)?!

    If i use flat(24) or less, I get the correct answer.

    Ok, let's use float(24). The result is: 165.76, 165.55. Is this result correct?

    There's no way to get a correct answer without cheating (copy-paste-execute) or making a guess πŸ™‚ Oh, except this one: you should know SQL Server internal binary representation of floats (which is undocumented and may vary from version to version)...

  • Bhavesh_Patel

    SSCrazy

    Points: 2259

    Does the explanation for the question has any relation with the output of the code? :w00t:

    I think this question has nothing to do with your SQL Server Knowledge.



    [font="System"]Bhavesh Patel[/font]

    http://bhaveshgpatel.wordpress.com/
  • Saurabh Dwivedy

    SSCommitted

    Points: 1881

    I realized that the poster had cut-paste from the link he has included in the answer. This could have been avoided by careful proof-reading; It might have been a case of oversight.

    However I am not clear what exactly the question is testing here? How is it that the rounding behaves differently for two very similar numbers?

    When I use float(24) for I get 165.76 in the first select ---> implying rounding behaves as expected. When I use float(25) I get 165.75 --> implying there is no rouding; rounding does not behave as expected.

    In the second select statement - the situation is reversed: Now when I use float(24), I get the result - 165.55 implying no rounding. When I use float(25) - I get 165.56 - implying rounding happens as expected.

    So when a double byte precision is used for 165.755 - no rounding happens; When double byte precision is used for 165.555 rounding does happen.

    What is so drastically different between these two numbers that's causing the difference in behavior?

    Does any one know?

    Saurabh Dwivedy
    ___________________________________________________________

    My Blog: http://tinyurl.com/dwivedys

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537

    Be Happy!
  • bc_

    Hall of Fame

    Points: 3106

    Seemingly random behavior with a random explanation. Could someone provide a valid explanation?

    [font="Arial Narrow"]bc[/font]

  • KevinC.

    SSCommitted

    Points: 1570

    I don't have an explanation, but I did do some testing and learned that the same error occurs with .255, .355, .755, and .855, but does not occur for .055, .155, .455, .555, .655, and .955.

    I don't know if it's truncating the float at some point or not, but this is good to know if you need to do calculations and rounding using floats. πŸ˜‰

  • Scott Coleman

    One Orange Chip

    Points: 27446

    The "rounding" differences are actually taking place when the numbers are stored as real and float approximate numbers. These are IEEE standard formats, they are in fact documented. You can look at the binary values with this query:

    SELECT F24, ROUND(F24, 2), CAST(F24 AS VARBINARY(8)),

    F25, ROUND(F25, 2), CAST(F25 AS VARBINARY(8))

    FROM (SELECT CAST(165.755 AS FLOAT(24)) AS F24, CAST(165.755 AS FLOAT(25)) AS F25) x

    FLOAT(1) through FLOAT(24) is stored as 4-byte single-precision floating point, or REAL. FLOAT(25) through FLOAT(53) is stored as 8-byte double-precision floating point.

    The 8-byte version is 0x4064B828F5C28F5C. 406 is the sign bit and exponent, 4B828F5C28F5C is the mantissa. (There are 53 mantissa bits but the leading bit is always 1 and not stored, so the next 52 bits are shown as 13 hex digits). The exact value would be 4B828F5C28F5C28F5C (with 28F5C repeating forever), but it has to stop and round at the 13th character. The 54th bit of the mantissa is 0, so the remainder is discarded and what is stored is something like 165.754999999999. This rounding occurs as the characters "165.755" are converted to FLOAT, the ROUND() function comes later.

    The 4-byte version is 0x4325C148. The sign bit and exponent take 9 bits (43 plus part of the 2), so the mantissa looks different from above because it is shifted one bit. If you divide 4B828F5C28F5C (followed by 28F5C forever) by 2 (or convert to binary, tack a extra 0 on the front, and regroup the bits into hex digits) you get 25C147AD147AD (followed by 147AD forever). When this is rounded off after 24 bits, the remainder starts with a 1 bit so the final 7 is rounded up to 8. You end up with roughly 165.75501.

    So the ROUND() function is not rounding the exact value 165.755 in either case. With single-precision it is rounding something slightly larger and with double precision it is rounding something slightly smaller.

  • bc_

    Hall of Fame

    Points: 3106

    Thanks Scott!

    [font="Arial Narrow"]bc[/font]

  • SQLRNNR

    SSC Guru

    Points: 281252

    Thanks Scott.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Dan Guzman - Not the MVP

    Hall of Fame

    Points: 3755

    Great explanation Scott, that's very detailed and instructive. Unfortunately it does not address the behavior found by KevinC. Why is not the behavior you describe consistent in a given range of numbers?

    Dan

  • Kelsey Thornton

    SSCrazy

    Points: 2157

    Dan Guzman - Not the MVP (12/30/2009)


    Great explanation Scott, that's very detailed and instructive. Unfortunately it does not address the behavior found by KevinC. Why is not the behavior you describe consistent in a given range of numbers?

    Dan

    It haven't checked, but I am pretty sure it does address the behaviour found - because the binary (IEEE) representation of these numbers will not be "consistent".

    Basically it boils dow to "Don't use ROUND() with double-precision"

    Kelsey Thornton
    MBCS CITP

  • Rob de Vos

    SSChasing Mays

    Points: 616

    Good to know.

    oracle behaves the same.

    declare

    round1a binary_float := 165.755;

    round2a binary_float := 165.555;

    round1b float := 165.755;

    round2b float := 165.555;

    begin

    dbms_output.PUT_LINE (ROUND(round1a,2));

    dbms_output.PUT_LINE (ROUND(round2a,2));

    dbms_output.PUT_LINE (ROUND(round1b,2));

    dbms_output.PUT_LINE (ROUND(round2b,2));

    end;

    165.76

    165.55

    165.76

    165.56

    Rob

  • Scott Coleman

    One Orange Chip

    Points: 27446

    I'll try this again. Intel processors (and probably most other computer hardware) use IEEE standard floating point representations. Single precision (REAL) uses four bytes and provides 24 bits to represent the value. Double precision (FLOAT) uses eight bytes and provides 53 bits for the value. The rounding issue discussed in this post is not due to the ROUND() function, it happens when the character string "165.755" is first converted to floating point. This means it should work the same in any programming environment on any computer hardware using the same IEEE standards.

    Given the character string "165.755", the system computes the floating point value. Internally in the CPU, I believe calculations are done to 80 bit accuracy. The result is then shortened (with rounding) to 24 or 53 bits, depending on the resolution. In the case of 165.755, the 25th bit is a 1 so the single-precision value is rounded up and is a little larger than 165.755. The 54th bit is a 0, so the double-precision value is rounded down and is a little smaller than 165.755. This happens when the characters "165.755" are first converted to a floating point representation and have nothing to do with the ROUND() function.

    This is why floating point types are called approximate types, and are not used where the rounding in the last decimal place is critical. If you need exact decimal values you need to use DECIMAL, NUMERIC, or MONEY. The behavior may look random, but it is completely consistent and predictable. You just need to get out paper and pencil and figure out the binary representation of all the floating point values you plan to use to 80 bits or so.

  • BudaCli

    Hall of Fame

    Points: 3383

    Nice one Scott.

    What you don't know won't hurt you but what you know will make you plan to know better
  • SQLWinther

    SSCertifiable

    Points: 5946

    Thank you for a great qotd and explanation! Lesson learned.

    /HΓ₯kan Winther
    MCITP:Database Developer 2008
    MCTS: SQL Server 2008, Implementation and Maintenance
    MCSE: Data Platform

Viewing 15 posts - 1 through 15 (of 19 total)

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