THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

Interesting Bug/Feature In SQL Server 2008 RTM

Someone had a problem with 8 year old procs which started to fail after moving to SQL Server 2008
Of course he should have used ints, but let's see what happens

Run this code on SQL Server 2005 and 2000

DECLARE @num_Passed Numeric(2, 0);

SET @num_Passed = -1;

SELECT @num_Passed

 

IF (@num_Passed = 0)

PRINT 'True';

 

No problem right?

Run just this part on SQL 2008

DECLARE @num_Passed Numeric(2, 0);

SET @num_Passed = -1;

SELECT @num_Passed

No problem either
Now run this whole thing

DECLARE @num_Passed Numeric(2, 0);

SET @num_Passed = -1;

SELECT @num_Passed

 

IF (@num_Passed = 0)

PRINT 'True';


Oops, this is what we get
Server: Msg 8115, Level 16, State 2, Line 7
Arithmetic overflow error converting expression to data type tinyint.


Change the -1 to 1

DECLARE @num_Passed Numeric(2, 0);

SET @num_Passed = 1;

SELECT @num_Passed

 

IF (@num_Passed = 0)

PRINT 'True';


No problem either.

Run this

IF (convert(Numeric(2, 0),-1) = 0)

PRINT 'True';

That fails
Let's make it numeric(3,0)

IF (convert(Numeric(3, 0),-1) = 0)

PRINT 'True';


No problem, that runs fine. So is this a bug because of implicit conversion to tinyint which can't hold negative values?

Published Monday, August 25, 2008 12:28 PM by Denis Gobo
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

AaronBertrand said:

August 25, 2008 12:32 PM
 

brejk said:

Unfortunately this bug is still not fixed. Moreover, run this on SQL Server 2008:

select sql_variant_property(cast(0 as sql_variant),'BaseType')

and check if the result is... int :-) Where is the tinyint???

And of course tinyint and int are both lower in the type precedence than numeric... How the hell can I recommend SQL Server 2008 to my customers? ;-)

December 31, 2008 3:53 AM
 

rsocol said:

This problem is fixed in SQL Server 2008 SP1 CU1 and in SQL Server 2008 RTM CU4 (see KB 961695).

May 31, 2009 2:20 PM

Leave a Comment

(required) 
(required) 
Submit

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from http://sqlservercode.blogspot.com/ or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement