September 14, 2015 at 6:50 am
I think the explanation is just plain wrong.
I don't see anything which would produce data types or numbers with the precision and scale of (2,1) or (1,1) -- 0.999 is (3,3) and 2 is an int. The result, 1.00, is (3,2).
What's happening is that SQL Server is not adjusting the resultant datatype for the potential increase in number of digits to the left of the decimal point (i.e, a potential decrease in scale of 1)
The datatype for ROUND is based on the original type (3,3). But rounding up to two decimal places gives you a result of 1.00, which is (3,2). A number with precision and scale of (3,2) cannot fit in (3,3) because there is no place to put the additional number to the left of the decimal point.
This is supported by the fact that SELECT ROUND(CAST(0.999 AS NUMERIC(4,3)), 2) works fine, and that SELECT ROUND(9.999, 2) does not.
September 14, 2015 at 7:11 am
Tricky for me, thanks.
September 14, 2015 at 7:59 am
sknox (9/14/2015)
I think the explanation is just plain wrong.I don't see anything which would produce data types or numbers with the precision and scale of (2,1) or (1,1) -- 0.999 is (3,3) and 2 is an int. The result, 1.00, is (3,2).
What's happening is that SQL Server is not adjusting the resultant datatype for the potential increase in number of digits to the left of the decimal point (i.e, a potential decrease in scale of 1)
The datatype for ROUND is based on the original type (3,3). But rounding up to two decimal places gives you a result of 1.00, which is (3,2). A number with precision and scale of (3,2) cannot fit in (3,3) because there is no place to put the additional number to the left of the decimal point.
This is supported by the fact that SELECT ROUND(CAST(0.999 AS NUMERIC(4,3)), 2) works fine, and that SELECT ROUND(9.999, 2) does not.
+1 quite right. the explanation is wrong in the details. (though correct in its intent I believe)
Gerald Britton, Pluralsight courses
September 14, 2015 at 1:06 pm
Interesting behavior for sure. Thanks for the question.
September 14, 2015 at 1:24 pm
Thanks for the question and discussion. I was not aware of any of this.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
September 14, 2015 at 4:20 pm
V strange
if you try any of the below, the results will be:
"Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type numeric."
SELECT round(.999,0);
SELECT round(.999,1);
SELECT round(.999,2);
but, if you type :
SELECT round(.999,3); and so on, you'll get results.
Whereas in Oracle,PostGre, you get the correct as expected results.
Any flaw by MS?
Thanks.
September 14, 2015 at 10:39 pm
SQL-DBA-01 (9/14/2015)
V strangeif you try any of the below, the results will be:
"Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type numeric."
SELECT round(.999,0);
SELECT round(.999,1);
SELECT round(.999,2);
but, if you type :
SELECT round(.999,3); and so on, you'll get results.
Whereas in Oracle,PostGre, you get the correct as expected results.
Any flaw by MS?
Not a flaw, the server interprets the literals as numeric(3,3), any attempt to implicitly lower the precision will error.
😎
September 15, 2015 at 8:40 am
Interestingly enough, select round(0.99+0.009,2) also works
September 15, 2015 at 12:03 pm
bmm7749-sqlservercentral (9/15/2015)
Interestingly enough, select round(0.99+0.009,2) also works
That is because of the rules for inferring data types after addition. Those rules DO take into account that the most significant digit can carry over. So an addition always results in a datatype that allows one extra digit before the comma. (So 0.99 = numeric(2,2); 0.009 = numeric(3,3); the addition result is numeric (4,3) - the exact rules can be found in BOL).
That does allow room for the extra digit that results from the rounding.
September 15, 2015 at 12:53 pm
A programming language should work in an intuitive way and/or hide needless complexity. Really, 0.999 and 1.999 should both evaluate to a common data type and therefore be processed by ROUND in exactly the same way. Failing that, ROUND should try to do an implicit conversion if that will prevent an error.
Just my 2 pennies...
September 15, 2015 at 1:18 pm
curt_coker (9/15/2015)
A programming language should work in an intuitive way and/or hide needless complexity. Really, 0.999 and 1.999 should both evaluate to a common data type and therefore be processed by ROUND in exactly the same way. Failing that, ROUND should try to do an implicit conversion if that will prevent an error.Just my 2 pennies...
That would make our jobs too easy.:-)
September 27, 2015 at 5:05 am
sknox (9/14/2015)
I think the explanation is just plain wrong.I don't see anything which would produce data types or numbers with the precision and scale of (2,1) or (1,1) -- 0.999 is (3,3) and 2 is an int. The result, 1.00, is (3,2).
What's happening is that SQL Server is not adjusting the resultant datatype for the potential increase in number of digits to the left of the decimal point (i.e, a potential decrease in scale of 1)
The datatype for ROUND is based on the original type (3,3). But rounding up to two decimal places gives you a result of 1.00, which is (3,2). A number with precision and scale of (3,2) cannot fit in (3,3) because there is no place to put the additional number to the left of the decimal point.
This is supported by the fact that SELECT ROUND(CAST(0.999 AS NUMERIC(4,3)), 2) works fine, and that SELECT ROUND(9.999, 2) does not.
I agree, the explanation is bunk, as can be seen from the examples given on the BOL round page, or by noticing that the result of ROUND(x,2) has the same type as x (and that the default type for literals with only a 0 before the decimal point is has scale and precision equal, which can easily be discovered using INSERT INTO). The failure when rounding requires an extra digit before the decimal point is explicity described with an example on the BOL page, although unlike this QOTD the example doesn't involve a default type with equal scale and preceision.
It is however a terrible fault in SQL Server. Every other implementation of SQL I have come across will either increase length by 1 or decrease scale by 1 when doing this rounding, and every other programming language I'ae seen that has a ROUND function gets it right. Add it to the NULL sum of the empty set as nother gross failure to cope with maths.
I imagine the 44% who picked 1 as the answer just assumed that T-SQL would have got the standard mathematical function ROUND right - and it's a pity they were wrong.
Tom
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply