SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Predict output for ROUND function


Predict output for ROUND function

Author
Message
Kelsey Thornton
Kelsey Thornton
SSChasing Mays
SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)

Group: General Forum Members
Points: 633 Visits: 282
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
Rob de Vos
Old Hand
Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)Old Hand (331 reputation)

Group: General Forum Members
Points: 331 Visits: 214
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
Scott Coleman
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3832 Visits: 1465
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
BudaCli
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1266 Visits: 598
Nice one Scott.

What you don't know won't hurt you but what you know will make you plan to know better
hakan.winther
hakan.winther
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2861 Visits: 608
Thank you for a great qotd and explanation! Lesson learned.

/Håkan Winther
MCITPBigGrinatabase Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
Mark D Powell
Mark D Powell
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1850 Visits: 463
Scott, very fine explantion.

-- Mark D Powell --
sknox
sknox
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2628 Visits: 2832
I'm honestly surprised at the number of people here who were not aware of the approximate nature of IEEE floating-point numbers. That information was in every programming class I took and every programming book I read. (It's even in BOL: http://msdn.microsoft.com/en-us/library/ms187912.aspx -- read the "Using float and real Data" section).

As a result, the first choice I was immediately able to eliminate was the "correct" one. I have to admit, though: I didn't want to go throught the same level of work as Scott did (great explanation, by the way); nor did I want to "cheat" by copy-paste-execute, so I guessed (correctly, as it turned out).

This is not a criticism of anyone's knowledge -- it is instead a vote of applause for this question. It brings out a limitation of a commonly-used data type, of which many were not aware. This is an example of a question that both tests and educates.

Plus, it reinforces my natural instinct to avoid float whenever possible...:-P
vk-kirov
vk-kirov
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3728 Visits: 4408
sknox (1/11/2010)
I have to admit, though: I didn't want to go throught the same level of work as Scott did (great explanation, by the way); nor did I want to "cheat" by copy-paste-execute, so I guessed (correctly, as it turned out).

I cheated because I knew about the incomplete correspondence between decimals and binaries, and did not want to guess. But now I have found a great website for decimal-to-IEEE754 conversion: http://www.binaryconvert.com

This site shows hexadecimal and binary representation of an entered number, as well as the most accurate result of the conversion from this binary back to decimal.

Here is the result for 165.755 (double precision):
0x4064B828F5C28F5C
01000000 01100100 10111000 00101000
11110101 11000010 10001111 01011100
The result of backward conversion to decimal: 1.65754999999999995452526491135E2

The same for 165.555:
0x4064B1C28F5C28F6
01000000 01100100 10110001 11000010
10001111 01011100 00101000 11110110
1.65555000000000006821210263297E2

With these values the correct answer becomes obvious Smile
Kelsey Thornton
Kelsey Thornton
SSChasing Mays
SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)SSChasing Mays (633 reputation)

Group: General Forum Members
Points: 633 Visits: 282
Now I have found a great website for decimal-to-IEEE754 conversion: http://www.binaryconvert.com

This site shows hexadecimal and binary representation of an entered number, as well as the most accurate result of the conversion from this binary back to decimal.



Great resource - Thanks! Cool

Kelsey Thornton
MBCS CITP
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search