Small rounding

Question

Post reply

Small rounding

Steve Jones - SSC Editor

SSC Guru

Points: 736297
More actions
September 8, 2016 at 9:38 pm

#400697

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

Viewing 15 posts - 1 through 15 (of 25 total)

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

Carlo Romagnano SSC-Insane Points: 22672 More actions · Answer 1

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

)

Matjaz Urank SSC Enthusiast Points: 137 More actions · Answer 2

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

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 3

Iulian -207023

SSCertifiable

Points: 7639

September 9, 2016 at 2:24 am

#1899972

Yes, they shall fix this one.

Nice spot,

Iulian

RossOverThere SSC Veteran Points: 293 More actions · Answer 4

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?

Mark Dalley SSCrazy Points: 2981 More actions · Answer 5

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

George Vobr SSChampion Points: 10001 More actions · Answer 6

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.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 7

This was a VERY interesting questions that's going to require some playing when I have time.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

sknox SSChampion Points: 12388 More actions · Answer 8

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

RossOverThere SSC Veteran Points: 293 More actions · Answer 9

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.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 10

Matjaz Urank (9/9/2016)
.. 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.

Unless you plan to work with numeric literals instead of tables, that's not a good reason to go to Oracle.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

webrunner SSC-Dedicated Points: 31870 More actions · Answer 11

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

Matjaz Urank SSC Enthusiast Points: 137 More actions · Answer 12

Luis Cazares (9/9/2016)
Matjaz Urank (9/9/2016)
.. 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.
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.

RonKyle SSC-Dedicated Points: 31557 More actions · Answer 13

Seemed easy, but being incorrect really does teach you more sometimes...