Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I was trying to figure out the source of what appeared to be a rounding error today, and discovered that one of the columns used in a mathematical equation was using the real data type, and this is causing my equation to return an incorrect value.

Can someone explain to me why

SELECT 776.2384 * cast(100 as real) / 100 

equals 776.2385 while

SELECT 776.2384 * 100 / 100

equals 776.2384?

share|improve this question
    
1  
And this one which I find lighter read and in much better formatting: http://floating-point-gui.de/ : What Every Programmer Should Know About Floating-Point Arithmetic. Plus, it has a xkcd –  ypercube Sep 13 '12 at 14:20
    
Why are you explicitly using REAL? REAL/FLOAT should be reserved for cases where they specifically need to be used. In most business applications DECIMAL/NUMERIC are a better (and more predictable) choice. –  Aaron Bertrand Sep 13 '12 at 14:22
    
@AaronBertrand I honestly have no idea. The database is part of some 3rd party software, and I don't like making changes to it unless I absolutely have to –  Rachel Sep 13 '12 at 14:24
    
@ypercube Thanks for the links :) –  Rachel Sep 13 '12 at 14:47
show 1 more comment

1 Answer

up vote 4 down vote accepted

See Using decimal, float, and real Data, and Data Type Precedence

In your second query, 776.2384 is a decimal number (and so are the two other values). So the calculation is done in that type, and the result is mathematically exact.

In the first, you introduce a real. The other literals are promoted to that type, and the calculation is done according to IEEE 754 rules, in round up mode. IEEE 754 floating points are not exact, so you will generally not obtain the "mathematically exact" result you're expecting.

Quote from the articles above:

Because of the approximate nature of the float and real data types, do not use these data types when exact numeric behavior is required, such as in financial applications, in operations involving rounding, or in equality checks. Instead, use the integer, decimal, money, or smallmoney data types.

share|improve this answer
    
So.... its actually storing the number as something like 776.238400000000001, and rounding up to 776.2385? Strangely enough, doing the division before the multiplication actually returns the correct result. I also get the correct result when only doing the multiplication, or only doing the division. Thank you though, I think I have a better understanding of what Real is and why its not accurate (although I had to Google IEEE 754 rules) :) –  Rachel Sep 13 '12 at 14:41
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.