Round

Question

Round

Viewing 12 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply

sknox SSChampion Points: 12388 More actions · Answer 1

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.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 2

akljfhnlaflkj

SSC Guru

Points: 76202

September 14, 2015 at 7:11 am

#1826334

Tricky for me, thanks.

g.britton SSChampion Points: 13863 More actions · Answer 3

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

Ken SSCoach Points: 17191 More actions · Answer 4

Interesting behavior for sure. Thanks for the question.

webrunner SSC-Dedicated Points: 31863 More actions · Answer 5

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

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 6

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.

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 7

SQL-DBA-01 (9/14/2015)
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?

Not a flaw, the server interprets the literals as numeric(3,3), any attempt to implicitly lower the precision will error.

😎

bmm7749-sqlservercentral SSC Rookie Points: 28 More actions · Answer 8

Interestingly enough, select round(0.99+0.009,2) also works

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 9

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.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

curt_coker Grasshopper Points: 18 More actions · Answer 10

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...

Marcia J SSCertifiable Points: 5649 More actions · Answer 11

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.:-)

TomThomson SSC Guru Points: 104773 More actions · Answer 12

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