November 19, 2012 at 4:20 am
Very tricky question. I agree with all the posters on the unpredictability of this function.
November 19, 2012 at 4:56 am
You mean the rule you were taught at school to round halves to even numbers?
The big problem in this question though is that whilst 3.75 can be stored exactly in binary 3.65 cannot.
November 19, 2012 at 6:24 am
crmitchell (11/19/2012)
You mean the rule you were taught at school to round halves to even numbers?
That's "banker's rounding", and it certainly isn't routinely taught in schools in the UK; and besides, SQL server definitely doesn't use it. The glitch here is due to inaccuracies in the storage of floating-point numbers, not any sort of tie-breaking rule.
November 19, 2012 at 6:26 am
I'll take a slightly different approach in my reply: I think this is a great question.
Not because the original answer explains the oddity of rounding -- it doesn't.
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.
Every time I write code to handle numerical data with a decimal component in SQL Server, I feel like I'm walking through the Fire Swamp in the Princess Bride.:crazy: "ROUSes, er, roundoff errors? I don't think they exist."
Rich
November 19, 2012 at 6:34 am
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
November 19, 2012 at 6:35 am
crmitchell (11/19/2012)
You mean the rule you were taught at school to round halves to even numbers?
Don't know what sort of school you went to, but the rule I was taught is that 3.65 would round UP to 3.7! And during my maths degree courses, if I had routinely rounded halves to even numbers there is no way I would have passed the exams!!!
November 19, 2012 at 6:38 am
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 SqlLike others said I would not call it a tie breaking rule
Agree - I think "arbitrary" would be a far better rule name.
On further digging this might help
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
November 19, 2012 at 6:50 am
Good Question. This question looks like it sparked an interesting discussion.
November 19, 2012 at 6:54 am
The Math.Round method seems to use the banker's rounding:
And it states:
The behavior of this method follows IEEE Standard 754, section 4. This kind of rounding is sometimes called rounding to nearest, or banker's rounding. It minimizes rounding errors that result from consistently rounding a midpoint value in a single direction.
Makes sense.
November 19, 2012 at 7:05 am
Sean of the Lynchmob (11/19/2012)
crmitchell (11/19/2012)
You mean the rule you were taught at school to round halves to even numbers?Don't know what sort of school you went to, but the rule I was taught is that 3.65 would round UP to 3.7! And during my maths degree courses, if I had routinely rounded halves to even numbers there is no way I would have passed the exams!!!
I partly agree with you - most of the time we expected to round up - or rather away from zero but if its important that rounding errors do not accumulate then the principle of rounding to even numbers is applied.
On the maths courses on my degree we were taught that you must specify any assumptions made and we lost marks on exams if we didn't.
Microsoft allow the user to define how rounding is applied on the rounding functions but their documentation doesn't make it clear as to how to do so.
If you require a specific behaviour then it should be explicitely coded.
The problem as I stated is that if it was the intention to pose a question on rounding then the issues of inexact representation of floats should have been avoided.
November 19, 2012 at 7:21 am
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).
I personally don't think it's sloppiness. There is no way that you can avoid rounding errors if you store numbers in a finite amount of bytes. The schemes used by SQL Server have different tradeoffs, but they both have tradeoffs. In float, data is internally represented as a binary (base-2) number; this means that some fractions that are finite in decimal are also finite in float (e.g. decimal 0.25 is binary 0.001), but others are not. This results in rounding errors. For numeric and decimal, numbers are internally stored in decimal format, so the rounding errors are the same as you would get when working out the calculations on a sheet of graph paper using the same amount of decimal places. Below is an example where I add 30 times 1/3 and do not get 10, but 9.999999. Incidentally, using float instead of decimal does return 10 - not because there are no rounding errors in the float calculation, but because they are so small that you don't see them when the result is rounded for display purposes.
It is possible to invent other methods to represent numbers. My daughter has a pocket calculater that allows the user to calculate with fractions - you can enter 1 2/3 + 2 1/2 and the result will be displayed as 4 1/6. You could do this in SQL Server as well - all it takes is a CLR data type that uses three integers to represent the whole part and the two parts of the fraction, and that implements the appropriate methods for arithmetic operators. But there are limitations - the sine of 4 1/6 is not shown as a fraction on my daughters calculator.
In theory, you could also find a way to represent square roots without loss of precision, and have the computer return exactly 3 if you compute the square of the square root of 3. But that would be harder than handling fractions, because there is (as far as I know) no way to simplify the result of 2 + sqrt(3) + sqrt(7) + sqrt(11), so you'd have to have support for a long series of numbers in the internal representation.
However, as long as you use data types as they are intended, you should not run into problems with SQL Server. The numeric data type is used for storing data with a fixed amount of decimal places - this is usually monetary data. The float data type is ideally suited for scientific applications, where all input comes from measurements, and hence already have a margin of error. My science teacher taught me to compute with at least two decimal places more than the precision of the measurement and then round the final result back to the precision of the least precise measurement - the extra precision in the calculations suffice to ensure that rounding will always be "correct". And the float data type does exactly what my science teacher taught me (exact that the amount of extra precision used inn the binary representation is usually more than two decimal places in the corresponding decimal representation).
(EDIT: Forgot to paste in the code before sending the reply)
DECLARE @x numeric(12,10) = CAST(0 AS numeric(12,10));
DECLARE @y numeric(12,10) = CAST(1 AS numeric(12,10)) / CAST(3 AS numeric(12,10));
DECLARE @i int = 0;
WHILE @i < 30
BEGIN
SET @x += @y;
SET @i += 1;
END;
SELECT @x;
go
DECLARE @x float = CAST(0 AS float);
DECLARE @y float = CAST(1 AS float) / CAST(3 AS float);
DECLARE @i int = 0;
WHILE @i < 30
BEGIN
SET @x += @y;
SET @i += 1;
END;
SELECT @x;
go
November 19, 2012 at 7:25 am
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.
November 19, 2012 at 7:39 am
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
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.
This question should have been set such that it was clear as to which issue it was supposed to be testing.
Is it supposed to be a test on the understanding of rounding or of the reperesentation of floating point numbers?
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.
November 19, 2012 at 7:39 am
I agree with many of the earlier comments. The only good thing about this question is the debate it provokes - plus it may discourage people from careless use of STR instead of cast and convert. The nonsense explanation contains a reference to a BoL page whose only relevance is that it points out that the first argument of STR is typed FLOAT (ie FLOAT(53), as 53 is the default precision for FLOAT), and does not in the least support the explanation provided.
As Hugo rightly says, the bizarre rounding effect is caused by the representation error. The representation error in SQL Server's float type is different in kind from the representation error in its so called exact numerics for one simple reasons: the floating point uses an outdated version of the IEEE 754 floating point standard. For now we are stuck with this, and because we know that the argument of DTR, if not already float, is implicitly converted to float before STR gets its hand on it, an alternative to STR is needed for use with exact numeric types. This is actually very easy to build from cast and convert: -- instead of
STR(@x,length,decimal)
-- which delivers incorrect rounding caused by representation errors introduced by
-- the implicit conversion to float.
-- For exact numerics we can use
convert(varchar(length),cast( as numeric(length-1,decimal)))
-- provided decimal > 0.
-- When decimal is 0, we need
convert(varchar(length),cast( as numeric(length,0)))
-- instead, because the decimal point doesn't take up a space.
-- if, like me, you think that usually when a floating point number's decimal
-- representation ends up in something like 49999 it is a representation error
-- for 50000, you can use
convert(varchar(length),cast(cast(x as numeric(2+length,1+decimal))
as numeric(length-1,decimal)))
-- which first rounds to one place more than required, so that representation
-- inaccuracy that might affect the result is eliminated.
-- as before, use length instead of length-1 if the required number
-- of decimal places is 0.
-- But don't use this on floats or reals if you think they are likely to have accurate
-- values that really do end in 49999999 or similar (with the 49999 beyond the last
-- position in the required output).
There are two problems with the outdated floating point standard: (a) it provides a maximum size of 64 bits for a floating point quantity, hence a maximum precision of 53 and (b) it insists on an exponent base inapproporiate for use when data is entered using decimal notation.
When (or do I mean if?) the 2008 revision of the floating point standard is available in SQL Server we will be able to use 128 bit floats with a decimal base for the exponent, so there will be no representation error converting from decimal notation, only precision error, and the precision error will of course be less than with 64 bit floats (the precision will still be less than 38 decimal digits, but at 36 decimal digits it's not much less).
The rounding mentioned in the Using decimal, float, and real DataBol page has nothing to do with this - that's about binary rounding in the course of arithmetic, which impacts coversion to float not from it. 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.
Tom
November 19, 2012 at 7:53 am
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);
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.
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.
Viewing 15 posts - 16 through 30 (of 45 total)
You must be logged in to reply to this topic. Login to reply