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
January 17, 2013 at 4:12 am
jfgoude (1/17/2013)
rounding float problem ... the only way to know is to try so : 64% of trickering on this QOTD
I disagree, for several reasons.
First - if I see this question, know about the possible issues with floating point values in the ROUND function, and then decide that the only way to know the result of this code is to run te code - is that "trickery", or a proof that I understand the relevant concept of this question?
And even if you do define that as trickery, than the 64% is still not correct. If you assume that nobody can know this, so that everyone who answers the question must either run the code (your definition of "trickery") and hence is sure to get it right, or picks a random answer, then the total amount of correct answers is all from the first group plus one third of the second group (because, with three answer options, there is a 1 in 3 chance of getting it right when doing a random guess). So we know that (coderunners) + (guessers) = 100%, and (coderunner) + (guessers)/3 = 64%. Two equations with two variables - easily solved; we can deduct that based on these assumptions, there must be 54% guessers and hence only 46% coderunners.
But I would say that even those assumption are wrong. I think there are three groups. Those who run the code, those who guess - and those who first eliminate the obviously wrong answers and then guess between the rest. Having seen a lot of QotD questions already, you can know for certain that this question would never have been asked if both number were rounded up, so you can immediately discarded that option. Both other options are possible, so that gives you a 50% chance of getting it right. Based on these assumptions, we now get these equations: (coderunners) + (blindguessers) + (guessafterelimination) = 100%, and (coderunners) + (blindguessers)/3 + (guessafterelimination)/2 = 64%. Two equations, and three variables - so no way to solve this! The only thing we know for sure is that the number of coderunners has to be between 28% and 46%.
(And even those assumptions are probably wrong, because there might also be people who have not seen enough QotD's to prevent a reaction like "oh, simple question, I don't see the point but it obviously has to be this one ... WHAT???". But I don't think that this is a very large group.)
Viewing 15 posts - 31 through 45 (of 45 total)
You must be logged in to reply to this topic. Login to reply