Strange SQL Server Behavior

A co-worker discovered the following odd behavior in Microsoft SQL Server 2000.

declare @foo table (
A int, B varchar(50))

insert @foo values(null,'abc')

select IsNull(A,'-'), B from @foo
select IsNull(convert(varchar(5),A),'-'), B from @foo

The result of the first select statement is the value 0 for the first column. This statement in reality should have caused an error message and indeed does cause an error when the type of the A column is changed to a float. For some reason an integer has an implicit null conversion to the value 0. This may have something to do with the setting of ANSI NULL, I have not investigated it that much.

As a final note, this functionality is the same in SQL Server 2000 and SQL Server 2005.

The following select statement in MySQL returns the value '-' for the column A when in reality column A should be of type Integer!

SELECT IFNULL( A, '-' ) , B

and the following query works without error as well:

SELECT 1,''
UNION
SELECT IFNULL(A,'-'),B
FROM FOO

when the previous query should return an error because an integer type is not compatible with a char/varchar type!

So in conclusion it pays to be aware of this issue as my co-worker discovered. Apparently a significant amount of work was done based upon incorrect data resulting from this odd behavior.