Monday, August 25, 2008

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?

No comments: