Last week, I talked about how implicit conversions can cause unexpected issues. In this post, I will take the example forward. Implicit conversion can create problems not only during basic data load, but also during data retrieval and manipulation operations. Today, we will see how implicit conversions can cause COALESCE to error out in a rather unexpected way.

The script below creates the test data similar to what we saw in the previous post.

USE tempdb;
GO
--Demo objects & data
IF OBJECT_ID('dbo.BewareOfImplicitConversion','U') IS NOT NULL
    DROP TABLE dbo.BewareOfImplicitConversion;
GO

CREATE TABLE dbo.BewareOfImplicitConversion
    ( RecordId                   INT         NOT NULL IDENTITY(100,10),
      RowDescription             VARCHAR(20) NOT NULL,
      IntMasqueradingAsCharacter VARCHAR(20)     NULL,
      IntValue                   INT             NULL
    );
GO

INSERT INTO dbo.BewareOfImplicitConversion (RowDescription, IntMasqueradingAsCharacter, IntValue)
VALUES ('One',   NULL, 1),
       ('Two',   NULL, 4),
       ('Three',  '3', 6),
       ('Four',   '4', 8),
       ('Five',   'V', 10);
GO

Now, let us try to select the data from the test table – we will extract the data for all rows except the last one (where we have inserted a character value (V) instead of a numeric value in the column: dbo.BewareOfImplicitConversion.IntMasqueradingAsCharacter.

USE tempdb;
GO
--With COALESCE, everything will work fine untill a character data is encountered
SELECT ic.RecordId,
       ic.RowDescription,
       COALESCE(ic.IntMasqueradingAsCharacter, ic.IntValue) AS CalculatedValue
FROM dbo.BewareOfImplicitConversion AS ic
WHERE ic.IntValue < 10;
GO

As we can see from the screenshot above, the SELECT worked and COALESCE was able to successfully use the values from the integer column if the column IntMasqueradingAsCharacter was NULL. Now, let us try to fetch data for the last row.

USE tempdb;
GO
SELECT ic.RecordId,
       ic.RowDescription,
       COALESCE(ic.IntMasqueradingAsCharacter, ic.IntValue) AS CalculatedValue
FROM dbo.BewareOfImplicitConversion AS ic
WHERE ic.IntValue = 10;
GO

The following error is returned:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ‘V’ to data type int.

The reason is quite simple – as discussed in my previous post, data type precedence comes into effect and the  character data is implicitly converted to an integer.

Root Cause

Per MSDN (see references below), COALESCE follows the CASE expression rules and returns the data type of value with the highest precedence. This means that COALESCE would have attempted to convert supplied values to the data-type with highest precedence, which in this case is INT (the data-type of the column IntValue). Because the value ‘V’ cannot be implicitly converted to an integer, the COALESCE fails and returns the data-type conversion error.

A possible solution

One of the solutions is to use the ISNULL function instead. ISNULL() uses the data-type of the 1st supplied parameter (and all other parameters are converted to it accordingly). What this means is that in this case, all parameters will be treated as character values even though a data-type with higher precedence exists in the inputs.

Here’s the output when we use ISNULL() instead of COALESCE().

USE tempdb;
GO
--With ISNULL, the return datatype is always same as the data-type of the first expression.
SELECT ic.RecordId,
       ic.RowDescription,
       ISNULL(ic.IntMasqueradingAsCharacter, ic.IntValue) AS CalculatedValue
FROM dbo.BewareOfImplicitConversion AS ic
WHERE ic.IntValue < 10;

SELECT ic.RecordId,
       ic.RowDescription,
       ISNULL(ic.IntMasqueradingAsCharacter, ic.IntValue) AS CalculatedValue
FROM dbo.BewareOfImplicitConversion AS ic
WHERE ic.IntValue = 10;
GO

Conclusion

As I end this 2-part series, the only point I want to make is that one should be very, very careful in the initial database design and during code review in choosing the right data-types and data-type combinations.

These issues are very data centric and the analogy that I often like to quote is that these issues are like ghosts under the bed – they lie dormant for most of the time, but when the right data conditions are available, they raise their head  – simply because the development teams did not keep the necessary conversions and checks in place.

References

• COALESCE and it’s comparison with ISNULL [MSDN Link]

Until we meet next time,
Be courteous. Drive responsibly.