LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Links

Wiki
Blog

Forum Statistics

Users
Members:
1879
Members Online:
1
Guests Online:
107

Total Post History
Posts:
81451
Topics:
18716

7-Day Post History
New Posts:
0
New Topics:
0
Active Topics:
0

Our newest member
mwojcik

Other

FAQ
All times are UTC [ DST ]

Bug in ROUND function

Microsoft SQL Server
Please wait...

Bug in ROUND function

Postby Niikola on Thu Jun 27, 2013 1:27 pm

I know it is unusual (let's be polite and not use some other word) to use ROUND over literal value, but:

ROUND(n,m)
where m and n are literal constants, 0.5<=n<1 and m=0 returns error:


SERVER(LOGIN): Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type numeric.


Round(n,m) where m>0 returns proper result for any value of n

SQL Server Connect link
https://connect.microsoft.com/SQLServer/feedback/details/791155/round-function-returns-arithmetic-overflow-for-literal-values-0-5-and-1
Smile...tomorrow will be worse
User avatar
Niikola
Senior Apprentice
Senior Apprentice
LTD Bronze - Rating: 139LTD Bronze - Rating: 139LTD Bronze - Rating: 139
 
Posts: 169
Joined: Thu Jun 25, 2009 12:02 pm
Location: Barcelona, Spain

Re: Bug in ROUND function

Postby SQLDenis on Thu Jun 27, 2013 1:49 pm

Interesting

Here is what I found, these 4 all work

  1. declare @d numeric(2,1) = 0.5
  2. select ROUND(@d,0)
  3. go
  4.  
  5. declare @d float = 0.5
  6. select ROUND(@d,0)
  7. go
  8.  
  9. declare @d varchar(10) = 0.5
  10. select ROUND(@d,0)
  11. go
  12.  
  13.  
  14. select ROUND('0.5',0)



This blows up
  1. select ROUND(0.5,0)  -- boo



But wait, let's see what 0.5 actually is
  1. SELECT   SQL_VARIANT_PROPERTY(0.5,'BaseType') AS 'Base Type',
  2.          SQL_VARIANT_PROPERTY(0.5,'Precision') AS 'Precision',
  3.          SQL_VARIANT_PROPERTY(0.5,'Scale') AS 'Scale'


Base Type	Precision	Scale
numeric 1 1


Scale 1 and precision 1???

Let's try that out

  1. declare @d numeric(1,1) = 0.5
  2. select @d   -- no problem
  3. select ROUND(@d,0)  -- hasta la vista
  4. go


so it looks like 0.5 gets stored as numeric(1,1) internally
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: Bug in ROUND function

Postby SQLDenis on Thu Jun 27, 2013 2:07 pm

Actually now that I think about it it kind of makes sense, since 0.5 is numeric(1,1) you can't round it up to 1


  1. declare @d numeric(1,1) = 1
  2. select @d  


But of course why doesn't '0.5' blow up? It probably does the conversion after the rounding
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: Bug in ROUND function

Postby SQLDenis on Thu Jun 27, 2013 6:33 pm

User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond

Re: Bug in ROUND function

Postby Niikola on Thu Jun 27, 2013 10:45 pm

Maybe I wasn't precise enough.

It fails when literal value, not declared variable is passed to the function.

Also, when I said values bigger than or equal to 0.5 and smaller than 1, that includes any decimal value in that range with any number of decimal digits, e.g. 0.734 (o.k. I didn't really tested any number of decimal digits, but with 4 also fails.

In all tests I tried with variables and table columns (both of them have already declared data type) ROUND worked properly.

As Denis noted "It probably does the conversion after the rounding", although, the error message does not say if error was caused by conversion of 0.5 or conversion of the result to numeric. I believe it is conversion of result, because I've used 0.5 so many times as literal value and never had an issue.

After reviewing links (thanks Denis), it became obvious it is problem with conversion of result to "assumed" data type of literal value. Even if you put 0.734 it becomes numeric(3,3) which results in arithmetic overflow because result must have precision for at least 1 bigger than scale.

Looking from other side, if you use 0.5/1 I expect it will be converted to float (I will test this tomorrow).
Smile...tomorrow will be worse
User avatar
Niikola
Senior Apprentice
Senior Apprentice
LTD Bronze - Rating: 139LTD Bronze - Rating: 139LTD Bronze - Rating: 139
 
Posts: 169
Joined: Thu Jun 25, 2009 12:02 pm
Location: Barcelona, Spain
Unrated

Re: Bug in ROUND function

Postby Niikola on Fri Jun 28, 2013 8:34 am

I was wrong.
0.5/1 is numeric(6,6) :(

but
Round(0.5*1,0) works, because 0.5*1 is numeric(3,1) :)

This issue reminded me of one discussion held in some forum long time ago: "What is the point using (fake) constant values (aka Declare @n numeric(5,2)= 0.5;) when we can just use literal values in the code"

I like to use constants, because they are grouped at the beginning of the code, it's easier to document (comment - I mean variables used as constants) them as well as to modify them if necessary. Now I have another point - their data types are defined, which removes need of implicit conversions and significantly reduces possibility of errors (hidden or obvious).
Smile...tomorrow will be worse
User avatar
Niikola
Senior Apprentice
Senior Apprentice
LTD Bronze - Rating: 139LTD Bronze - Rating: 139LTD Bronze - Rating: 139
 
Posts: 169
Joined: Thu Jun 25, 2009 12:02 pm
Location: Barcelona, Spain
Unrated

Re: Bug in ROUND function

Postby SQLDenis on Fri Jun 28, 2013 12:55 pm

Niikola wrote:It fails when literal value, not declared variable is passed to the function.



Because your variable is big enough to hold the value rounded up, if you declare the variable like this (which is what the constant is as well) it will blow up on the round part

  1. declare @d numeric(1,1) = 0.5
  2. select @d   -- no problem
  3. select ROUND(@d,0)  -- error
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: Bug in ROUND function

Postby gmmastros on Fri Jun 28, 2013 1:28 pm

Round(0.5*1,0) works, because 0.5*1 is numeric(3,1)


This is because the of data type rules for multiplication.

Data Type Precedence
http://msdn.microsoft.com/en-us/library/ms190309.aspx

Precision, Scale, and Length
http://msdn.microsoft.com/en-us/library/ms190476.aspx

0.5 is interpreted at numeric(1,1). 1 is interpreted as integer.

When different datatypes are "combined", the rules for data type precedence take affect. In this case, when combining a decimal and an integer, the integer is converted to decimal. In this case, it's converted to decimal(1,0).

For multiplication, the rule is:

.5 = decimal(1,1)
1 = decimal(1,0)

Precision = p1 + p2 + 1
Precision = 1 + 1 + 1
Precision = 3

Scale = s1 + s2
Scale = 1 + 0
Scale = 1

The resulting data type is Decimal(3,1).

Just to add more "weirdness"... what do you think the result of the following code produces?

Select Round(.5 + 0, 0)
-George
User avatar
gmmastros
LTD Admin
LTD Admin
LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630
LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630
LTD Gold - Rating: 1630LTD Gold - Rating: 1630
 
Posts: 2369
Joined: Tue Oct 09, 2007 5:19 pm

Re: Bug in ROUND function

Postby SQLDenis on Fri Jun 28, 2013 1:36 pm

Interesting


  1. SELECT   SQL_VARIANT_PROPERTY(.5 + 0,'BaseType') AS 'Base Type',
  2.          SQL_VARIANT_PROPERTY(.5 + 0,'Precision') AS 'Precision',
  3.          SQL_VARIANT_PROPERTY(.5 + 0,'Scale') AS 'Scale'



Here is the result, guess before clicking SHOW
Code is hidden, SHOW
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: Bug in ROUND function

Postby SQLDenis on Fri Jun 28, 2013 3:51 pm

Mmmmmm, interesting stuff


  1. SELECT  SQL_VARIANT_PROPERTY(.5 + 0,'Precision') AS 'Precision0',
  2.     SQL_VARIANT_PROPERTY(.5 + 0.0,'Precision') AS 'Precision0.0',
  3.     SQL_VARIANT_PROPERTY(.5 + 0.00,'Precision') AS 'Precision0.00'



Precision0	Precision0.0	Precision0.00
3 2 3
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: Bug in ROUND function

Postby gmmastros on Fri Jun 28, 2013 5:40 pm

0.5 has precision 1, scale 1
0.0 has precision 1, scale 1

For addition, the result precision is

max(s1, s2) + max(p1-s1, p2-s2) + 1
Max(1,1) + max(1-1, 1-1) + 1
1 + max(0,0) + 1
1 + 0 + 1
2

This actually makes perfect sense. Think about the largest number you can store in a decimal(1,1). It's 0.9

0.9 + 0.9 = 1.8, which requires a decimal(2,1).
-George
User avatar
gmmastros
LTD Admin
LTD Admin
LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630
LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630
LTD Gold - Rating: 1630LTD Gold - Rating: 1630
 
Posts: 2369
Joined: Tue Oct 09, 2007 5:19 pm
Unrated

Re: Bug in ROUND function

Postby Niikola on Mon Jul 01, 2013 9:25 am

Denis, I agree with your excellent explanations, but I still believe this should be marked as a bug as it is obvious that result of ROUND function can (and does) have different precision of the argument. Similar rule as for addition and multiplication should be applied.

  1. Declare @n numeric(1,1)=0.5;
  2. Select @n + @n;  -- this works as result has different precision than arguments
  3. Set @n += @n;    -- this fails because result cannot be stored in numeric(1,1)
  4.  


In the same way, I expect to have:
  1. Declare @n numeric(1,1)=0.5;
  2. Select Round(@n,0);   -- this fails, but should work as result should have
  3.                       -- different precision than arguments
  4. Set @n = Round(@n,0); -- this should fail because result (which have
  5.                       -- different precision) cannot be stored in numeric(1,1)
  6.  
Smile...tomorrow will be worse
User avatar
Niikola
Senior Apprentice
Senior Apprentice
LTD Bronze - Rating: 139LTD Bronze - Rating: 139LTD Bronze - Rating: 139
 
Posts: 169
Joined: Thu Jun 25, 2009 12:02 pm
Location: Barcelona, Spain
Unrated