Bug in isnull() function

Question

The isnull() function has the following bug: the datatype and size of the output appears to be determined by the datatype and size of the first input column, as in the following examples:

declare

@a varchar(6)
select isnull(@a , 'ABC12345')

RETURNS: ABC123 (truncated)

Also ...

declare

@a numeric(5)
select ISNULL(@a, 123456

)

RETURNS:

Msg 8115, Level 16, State 8, Line 3
Arithmetic overflow error converting int to data type numeric.

I understand that the datatype of the second parameter needs to be the same general type as the first parameter, but fixing the size of the output to the size of the first parameter has led to some pretty unpleasant and unexpected results.

I consider this a bug in the server.



Answer 1

What you describe matches exactly with what I find documented in Books Online. I.e., the function work as expected. BElow are snippets from Books Online (http://msdn.microsoft.com/en-us/library/ms184325.aspx):

ISNULL ( check_expression , replacement_value )

Return Types
Returns the same type as check_expression.

Coalesce is different, though. It returns the type which is the input types of the highest precedence.

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 2

I understand that what you get is not what you want, but every column and variable has precision.  If you violate the precision then there are problems.

What would you want?  12345 or 23456?  It makes perfect sense to me that it overflowed.  In fact, the thing that surprised me is the character string behavior.   A little script to demonstrate:

-- Varchar Test
declare @ac varchar(6)
select isnull(@ac , 'ABC12345')
--RETURNS: ABC123 (truncated)

create table #tempchar (ac varchar(3));
insert into #tempchar values ('ABC12345')
-- String or binary data would be truncated
drop table #tempchar


-- Numeric Test
declare @an numeric(5)
select ISNULL(@an, 123456 )
-- Arithmetic overflow error converting numeric to data type numeric.

create table #tempnum (an numeric(5));
insert into #tempnum values (123456)
-- Arithmetic overflow error converting numeric to data type numeric.
drop table #tempnum

The only one that had no error was the varchar trunctation of extra characters.

Of course, frankly I think truncating character strings is often more desirable than getting a failure.  But for numbers I prefer an error.

Just for what it is worth,
RLF

Answer 3

OK, I get what you're saying ... I guess we were really surprised that the character string truncation didn't get an error. ANSI_WARNINGS are on... I'd expect the error.

Answer 4

This is mainly an issue for us because we are migrating from Sybase to SQL Server - the isnull function in Sybase behaves just like it does in SQL Server when dealing with numeric datatypes (the return type is the datatype of the check_expression and results in overflow if the replacement value is too large).

However, unlike SQL Server, Sybase does not truncate the replacement value if it's a varchar - the full replacement string is returned. So actually, the behavior in SQL Server is actually more consistent than the behavior in Sybase, although it's breaking a bunch of our code in SQL Server where the replacment value strings are being truncated.

Looks like the solution to this problem is to replace all instances of the isnull function with the coalesce function. Coalesce behaves the same way in both environments with the resulting data type matching the size of the largest input value.

-Ray