November 19, 2012 at 8:11 am
Hugo Kornelis (11/19/2012)
rmechaber (11/19/2012)
But I "like" this because it shows up a terrible disdain in SQL Server for handling basic mathematics under certain circumstances. I don't like the "it's a floating data type which is always an approximate value" explanation that is routinely given as the reason for this sloppiness. Millions of hand calculators have been storing numbers as floats for decades and don't do things like this.Do you have a source to confirm that hand calculators store numbers as floats rather than fixed point numbers? (I have never seen any documentation on internals of hand calculators, but I think they use a representation that is neither of the above, but a representation that avoids binary representation while still having a floating point to enable numbers with high mantissa to be stored).
Thank you Hugo for that post back. Seldom is the occasion when I fail to learn something from your comments.
You know, in fact I don't have any such source, nor can I locate one with Google. My memory is quite clear that the manuals that came with my calculators referred to floating point arithmetic, but that could be (a) an artifact of my faulty memory (my own, physiologically internal, memory storage problem!) or (b) inexact language in the manual, referring to its internal storage as "floating point" when it really isn't. Either way, you may well be right that the internal storage of numbers in calculators is different from what SQL Server uses.
Edited Addition: Bingo, Hugo: Just found this link that seems to support your point that calculators use a different memory storage.
If so, it would be handy to enable "calculator-mode" for a variety of simple math problems in SQL Server. Take that code you posted, for example. I certainly understand that trying to represent 1/3 in binary storage is an impossibility, and it makes sense that the first batch using NUMERIC(12,10) doesn't yield a correct answer. What I'm unclear about is whether one could have predicted -- ahead of time, with certainty -- that the second batch using FLOAT would correctly return 10?
And Tom, thanks for this bit at the end of your post, which I think is a clearer explanation:
What we have here is decimal rounding done by STR - which is to the closest value, while SQL Servers implementation of the binary rounding in floating point is always up, as stated on that BoL page. Of course the value handed to STR isn't 3.65, it's 3.64999999999999 accurate to 15 decimal digits (maybe even 3.6499999999999 accurate to 16) so it is actually less than 3.65, which is why STR delivers 3.6 instead of 3.7.
Rich
November 19, 2012 at 8:25 am
rmechaber (11/19/2012)
You know, in fact I don't have any such source, nor can I locate one with Google. My memory is quite clear that the manuals that came with my calculators referred to floating point arithmetic
I think calculators do use floating point arithmetic, but not the float data type. Floating point can be used in any numeric system - our common base-10, the base-2 system computers often use, or even base-7 if you are so inclined. "Our" float data type uses base-2; this is not very literally documented, but you can conclude it from this quote on http://msdn.microsoft.com/en-us/library/ms173773.aspx: "Where n is the number of bits that are used to store the mantissa of the float number" (emphasis mine).
If so, it would be handy to enable "calculator-mode" for a variety of simple math problems in SQL Server. Take that code you posted, for example. I certainly understand that trying to represent 1/3 in binary storage is an impossibility, and it makes sense that the first batch using NUMERIC(12,10) doesn't yield a correct answer. What I'm unclear about is whether one could have predicted -- ahead of time, with certainty -- that the second batch using FLOAT would correctly return 10?
I think Tom's request for support of a newer standard for floating point numbers is a technical way to say what you are also asking for. 🙂
And the answer to your second question is no. I had to do some trial and error to find a loop that would completely hide the rounding error. (Let the loop run 300 times, and you'll see some rounding error).
November 19, 2012 at 8:29 am
Hugo Kornelis (11/19/2012)
crmitchell (11/19/2012)
Hugo Kornelis (11/19/2012)
mbova407 (11/19/2012)
Thanks for this question. I always hated/never understood this banker's rounding rule. As you can see from this post it is not just in Sql(...)
Like others said I would not call it a tie breaking rule
The banker's rounding rule is completely unrelated to today's question. What you see in this question is that float cannot represent the .65 fraction in a finite amount of binary digits, so it has to cut it off somewhere - and that can cause rounding errors when calculatinig back to decimal.
I'd have to disagree with you there - the rounding rule defines how 3.75 is rounded to 3.8 as opposed to 3.7
If the banker's rule was involved, then the code below should return 3.4 and 3.6 - instead, it returned 3.5 and 3.5.
declare @x float, @y float;set @x = 3.45;
set @y = 3.55;
--What values are returned by the following statements?
select 'x = ' + str(@x,10,1) ;
select 'y = ' + str(@y,10,1);
Those values still confuse the issues as they still cannot be exactly represented
the use of 3.25 and 3.75 do however indicate that you are correct that rounding to even is not being applied by default. I believe there may be a switch which enables it however.
Which does mean that I got the question right for the wrong reason
It should define how 3.65 should be rounded to 3.6 but because 3.65 cannot be exactly represented it will not be applied to that value.
The only way to define the rounding is to first represent the number as a base-2 number, round that to 53 positions, then convert back to base-10. Because that is what happens.
No need to define the implementation - the IEEE standard defines the expected behaviour
This question should have been set such that it was clear as to which issue it was supposed to be testing.
I agree. I think the poster of the question assumed that some smart rule was at play, but didn't investigate long enough to see the actual root cause.
In many cases it will be better to multiply the decimal value by 100 (or other apppropriate power of 10) and then apply integer arithmetic in order to avoid rounding errors.
If you are dealing with numbers with a fixed amount of positions after the decimal, then you can do that - or simply use the decimal or numeric data type. If you are using scientific data (or other data with infinite decimal places) then this won't help either. PI * 100, 1/7 * 100, and sqrt(3) * 100 are still subject to rounding errors.
Hence why I said "many" and not "all"
Even your example of 1/7 may be able to avoid rounding errors if the division is delayed and can subsequently be cancelled out by a multiplication, similarly for the sqrt(3) if that is later squared. Usually you won't be able to totally eliminate them but often you can significantly reduce them.
e.g. (1+1+1)/3 will be exact but 1/3 + 1/3 + 1/3 will not
November 19, 2012 at 8:37 am
I believe we've had a similar question recently. Probably a similar discussion too...
Aigle de Guerre!
November 19, 2012 at 10:45 am
bitbucket-25253 (11/18/2012)
Unfortunately the cited reference for the correct answers does NOT or I could not find the "tie breaking rule" in that reference. Any one have better luck ?
It is not called "tie breaking rule" but this text does attempt explain it.
The number is rounded to an integer by default or if the decimal parameter is 0. The specified length should be greater than or equal to the part of the number before the decimal point plus the number's sign (if any). A short float_expression is right-justified in the specified length, and a long float_expression is truncated to the specified number of decimal places. For example, STR(12,10) yields the result of 12. This is right-justified in the result set. However, STR(1223,2) truncates the result set to **. String functions can be nested.
When automatic rounding occurs, there has always been a point where it won't round up when logic says it should.
This QOTD seems more like an example that this occurs than an example of a documented process. 😎
November 20, 2012 at 1:54 am
me too
November 20, 2012 at 6:24 am
The banker's rounding rule is completely unrelated to today's question. What you see in this question is that float cannot represent the .65 fraction in a finite amount of binary digits, so it has to cut it off somewhere - and that can cause rounding errors when calculatinig back to decimal.
How can you say that when it is following the banker rounding rule?
If it was true that it could not be represented then wouldn't it always round in the same direction?
November 20, 2012 at 6:26 am
If the banker's rule was involved, then the code below should return 3.4 and 3.6 - instead, it returned 3.5 and 3.5.
declare @x float, @y float;set @x = 3.45;
set @y = 3.55;
--What values are returned by the following statements?
select 'x = ' + str(@x,10,1) ;
select 'y = ' + str(@y,10,1);
My apologies for my earlier post. Good point.
November 21, 2012 at 6:07 am
Can anyone please explain or refer an article as to how the str worked out to give the results.
November 21, 2012 at 8:51 am
mbova407 (11/20/2012)
The banker's rounding rule is completely unrelated to today's question. What you see in this question is that float cannot represent the .65 fraction in a finite amount of binary digits, so it has to cut it off somewhere - and that can cause rounding errors when calculatinig back to decimal.
How can you say that when it is following the banker rounding rule?
If it was true that it could not be represented then wouldn't it always round in the same direction?
Try it with 3.45 and 3.55 if you still believe that the banker rounding rule is involved.
And no, it would not always round in the same direction. A data type like decimal(8,5) can not accurately represent numbers like one third or two thirds. It rounds the first one down to 0.33333 and the second one up to 0.66667.
November 21, 2012 at 9:48 am
arun1_m1 (11/21/2012)
Can anyone please explain or refer an article as to how the str worked out to give the results.
It's not STR that produces the strange effect. If STR actually got the value 3.65 handed to it, it would round up to 3.7 when told to produce just one place after the decimal point.
What actually cases the result to be 3.6 instead of 3.7 is that 3.65 can't be represented exactly as binary (because 20 is not a power of 2, and 3.65 is 73/20). That means that when 3.65 is assigned to a float value, the SQL engine has to generate a value to be used instead of 3.65, and it tries to generate the closest value it can, which in this case is about 3.6499999999999999. Because that's less than 3.65 STR rounds it down to 3.6, instead of the expected 3.7.
You can see that this rounding is caused by the representation error introduced in converting 3.65 to a floating point form quite easily. If you change the declaration for @x from float to real you have a 24 bit mantissa instead of a 52 bit one - obviously a lot less accurate. But now the nearest value to 3.65 the sql engine can generate when it handles the assignment is about 3.6500001, which is higher than or equal to 3.65. When that is implicitly converted to float, the first 24 matissa bits remain the same and the 29 extra mantissa bits will all be zero, so the value passed to STR will be more than 3.65 so STR will deliver 3.67 when asked to produce just one place after the decimal point. Of course this is pure luck - for the particular value 3.65, the bigger representation error (half a billion times as big) caused by using real instead of float just happens to be positive, not negative, so the result will be rounded in the correct direction.
Tom
November 23, 2012 at 9:08 am
You're right, this is not an issue with banker's rounding or 3.45 would have rounded even to 3.4
Andre
November 28, 2012 at 10:24 am
Good One, Mixed of Math and SQL
January 17, 2013 at 3:41 am
rounding float problem ... the only way to know is to try so : 64% of trickering on this QOTD
Viewing 15 posts - 31 through 45 (of 45 total)
You must be logged in to reply to this topic. Login to reply