September 8, 2016 at 9:38 pm
Comments posted to this topic are about the item Small rounding
September 8, 2016 at 11:30 pm
This was removed by the editor as SPAM
September 9, 2016 at 12:48 am
That's true!
SELECT ROUND(0.7 , 0) as a into #a
Here, the table created:
CREATE TABLE dbo.#a1
(
a numeric(1,1) NOT NULL
)
September 9, 2016 at 1:13 am
.. and SELECT ROUND( 7.0/10, 0) = 1
I can't understand why technically this could be an error. But that doesn't mean that this isn't a bug. Does Microsoft knows about that and thinks it's correct behaviour?
On Oracle SELECT ROUND(0.7 , 0) is 1. So this is a good reason why should I switch to Oracle.
September 9, 2016 at 2:24 am
Yes, they shall fix this one.
Nice spot,
Iulian
September 9, 2016 at 2:29 am
Great question. I find it incredible that this is true.
Unfortunately I didn't understand the explanation either, so I'm going to have to do some further reading! I guess it's to do with the assumed data type of the value 0.7?
September 9, 2016 at 4:22 am
I find this incredible as well. To my mind, rounding a number X to N places consists of the following steps:
1. Multiply X by 10 to the power of N
2. Add 0.5
3. Truncate to an integer
4. Divide by 10 to the power of N.
E.g. 2345.67 to 1 decimal place:
1. Multiply by 10 to the power of 1 giving 23456.7
2. Add 0.5 giving 23457.2
3. Truncate giving 23457
4 Divide by 10 (to the power of 1) giving 2345.7
E.g. 2345.67 to -1 decimal place (i.e. to the nearest 10)
1. Multiply by 10 to the power of -1 giving 234.567
2. Add 0.5 giving 235.067
3. Truncate giving 235
4. Divide by 10 to the power of -1 giving 2350
Sure enough, when I try SELECT ROUND(2345.67, -1), I get 2350.00
OK, on to Steve's example of SELECT ROUND(0.7, 0)
1. Multiplying by 10 to the power of 0 (i.e. 1) is a null operation, leaving the value at 0.7
2. Add 0.5 giving 1.2
3. Truncate to 1
4. Dividing is likewise a null operation, leaving the value at 1.
So, why does it insist on overflowing? I reckon Oracle is right on this one, going by the post above.
There must be something about precision that I am missing. To my mind, the BOL article Steve linked to omits to explain this exact point. In fact, the remarks state that "ROUND always returns a value" !
MarkD
September 9, 2016 at 5:04 am
Hmm, interesting, numeric_expression 0.5 - 0.9 cannot be with the ROUND function directly rounded.
SELECT ROUND (0.4, 0);
SELECT ROUND (0.5, 0);
Results
---------------------------------------
0.0
(1 row(s) affected)
---------------------------------------
Msg 8115, Level 16, State 2, Line 9
Arithmetic overflow error converting expression to data type numeric.
Only with use @local_variable, e.g. as follows:
DECLARE @n varchar(10)= '0.7';
SELECT @n;
SELECT ROUND (@n, 0);
Results
----------
0.7
(1 row(s) affected)
----------------------
1
(1 row(s) affected)
Thanks Steve for this "discovery" question.
September 9, 2016 at 6:08 am
This was a VERY interesting questions that's going to require some playing when I have time.
September 9, 2016 at 6:21 am
Good question, but the explanation is incomplete. Precision is only half of the story, the other being scale.
1.0 can fit in a number with precision of 1, if the scale is 0.
But 0.7 has precision of 1 and scale of 1. That means that it can only have one significant digit (precision = 1), and that 1 significant digit must lie to the right of the decimal point (scale = 1).
This leaves space for (precision - scale) = 0 digits to the left of the decimal point.
So the correct explanation is that the combination of precision and scale causes the 1.0 to overflow the numeric data.
Edit: Fixed Formatting
September 9, 2016 at 7:01 am
sknox (9/9/2016)
Good question, but the explanation is incomplete. Precision is only half of the story, the other being scale.1.0 can fit in a number with precision of 1, if the scale is 0.
But 0.7 has precision of 1 and scale of 1. That means that it can only have one significant digit (precision = 1), and that 1 significant digit must lie to the right of the decimal point (scale = 1).
This leaves space for (precision - scale) = 0 digits to the left of the decimal point.
So the correct explanation is that the combination of precision and scale causes the 1.0 to overflow the numeric data.
Edit: Fixed Formatting
That's the explanation I needed - thanks!
So the default type of 0.7 is something akin to NUMERIC(1,1). When ROUND calculates its result based on a NUMERIC or DECIMAL, it returns a DECIMAL of the same precision and scale, and 1.0 doesn't fit into a DECIMAL(1,1).
The world makes sense again.
September 9, 2016 at 7:13 am
Matjaz Urank (9/9/2016)
.. and SELECT ROUND( 7.0/10, 0) = 1I can't understand why technically this could be an error. But that doesn't mean that this isn't a bug. Does Microsoft knows about that and thinks it's correct behaviour?
On Oracle SELECT ROUND(0.7 , 0) is 1. So this is a good reason why should I switch to Oracle.
Unless you plan to work with numeric literals instead of tables, that's not a good reason to go to Oracle.
September 9, 2016 at 7:27 am
Thanks, great question!
- 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 9, 2016 at 7:27 am
Luis Cazares (9/9/2016)
Matjaz Urank (9/9/2016)
.. and SELECT ROUND( 7.0/10, 0) = 1I can't understand why technically this could be an error. But that doesn't mean that this isn't a bug. Does Microsoft knows about that and thinks it's correct behaviour?
On Oracle SELECT ROUND(0.7 , 0) is 1. So this is a good reason why should I switch to Oracle.
Unless you plan to work with numeric literals instead of tables, that's not a good reason to go to Oracle.
Remark about moving to Oracle was not serious, I just think that when using such critical system as SQL server is, you shouldn't worry about such trivial errors. Even if there is some reasonable explanation why technically this produces an error on SQL server, I don't think that "The world makes sense again" as somebody wrote in previous post.
September 9, 2016 at 7:46 am
Seemed easy, but being incorrect really does teach you more sometimes...
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply