Possible SQL Server 2008 RTM Bug

Question

Hi all... Have a look at this command.... it works in SQL Server 2005 but fails in 2008 RTM:

 

Declare @num_Passed Numeric(2, 0);

Declare @num_CheckoutPositionID Numeric(3, 0);

 

Set @num_Passed = -1;

Set @num_CheckoutPositionID = 3;

 

IF (@num_CheckoutPositionID = 3) AND (@num_Passed = 0)

BEGIN

print 'True';

END

ELSE

BEGIN

print 'False';

END

 

Msg 8115, Level 16, State 2, Line 7

Arithmetic overflow error converting expression to data type tinyint.

 

Now, I found 2 fixes:

 

1.  I changed the numeric data types to integer.  (Which is what it should be anyways)

2.  Change statememt to:  IF (@num_CheckoutPositionID = 3.0) AND (@num_Passed = 0.0)

 

I found this problem in some SPs which were at least 8 years old.  I will admit the datatype should have originally been integer..... but still, it should work.

 

My point is... be careful before upgrading your SQL Instance!

 

Can someone else check to see if they get this same error?  I want to make sure it isn't my installation.

 

Cheers,

 

Forch

 

 

Answer 1

BTW the problem only occurs when you use negative values

This is because numeric(2,0) is implicitly converted to tinyint, tinyint can not store negative values

 

this works

 

Code Snippet

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

BEGIN

print 'True';

END

 

 

 

 

this doesn't

 

Code Snippet

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

BEGIN

print 'True';

END

 

 

 

Using numeric(3,0) is no problem either

 

Code Snippet

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

BEGIN

print 'True';

END

 

 

 

 

This is very interesting because this works

 

Code Snippet

Declare @num_Passed Numeric(2, 0);

Set @num_Passed = -1;
select @num_Passed

 

 

 

 

Denis The SQL Menace

SQL Server Programming Hacks

ASP.NET hacks

 

Answer 2

Fascinating....  I'm wondering why it works in 2005.... 

 

Answer 3

Forch,

 

I was able to reproduce the error. The script executed ok on SS 2005 DE, but not in SS 2008 DE RTM.

 

I posted it in the private SQL Server MVP newsgroup and will update here if I get any reponse.

 

Thanks,

Alejandro Mesa

Answer 4

Hi Denis,

 

The question here is why are they converting it to tinyint in the logical expression?

 

 

AMB

Answer 5

My guess is optimization of some sort, which is interesting because the following is just a plain vanilla int

 

select

cast(sql_variant_property(1,'BaseType') as varchar(20)) + '(' +

cast(sql_variant_property(1,'Precision') as varchar(10)) + ',' +

cast(sql_variant_property(1,'Scale') as varchar(10)) + ')'

 

Let's see what comes back out of the newsgroup

 

Denis The SQL Menace

SQL Server Programming Hacks

ASP.NET hacks

 

Answer 6

Denis,

 

They are breaking their own rules, because numeric/decimal data type has greater precedence than tinyint, so the number in the right of the expression should have been converted to numeric/decimal in order to do the comparison, and not the contrary, from numeric/decimal to tinyint.

 

 

AMB

 

Answer 7

Forch,

 

This is defenitely a bug. Could you file this bug on microsoft connect, please?

 

You will need to login using a Windows Live ID account.

 

http://connect.microsoft.com/

 

 

AMB

 

Answer 8

 

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=363428

 

Forch

Answer 9

To everyone:  Please verify our findings and add your validation to the M/S Connect site.  Hopefully this will speed up the "Fix" process.

 

Thanks,

 

Forch