Why does this SQL query fail

Question

The query below is failing unexpectedly with an arithmatic overflow error.

select IsNull(t2.val, 5005)
from(

SELECT 336.6 as val UNION ALL 
SELECT NULL

) as t2

"Arithmetic overflow error converting int to data type numeric."

Strangely if the query is modified to remove the NULL and replace it with the same value as in the null coalesce (5005), it runs without issue

select IsNull(t2.val, 5005)
from(

SELECT 336.6 as val UNION ALL 
SELECT 5005

) as t2

Also, omitting the SELECT NULL line entirely allows the query to run without issue

select IsNull(t2.val, 5005)
from(

SELECT 336.6 as val

) as t2

If the coalesce value in the IsNull function is changed to an integer which is small enough to convert to the decimal in the subquery without widening, the query runs

select IsNull(t2.val, 500)
from(

SELECT 336.6 as val UNION ALL
SELECT NULL

) as t2

Tested this in both SQL Server 2005 and SQL Server 2008.

Ordinarily combining integers with decimals is seamless and SQL Server will convert both the integer and the decimal into a decimal type large enough to accommodate both. But for some reason running a query where the cast occurrs from both the UNION and the IsNull, causes the cast to fail.

Does anyone know why this is?

Answer 1

Try doing this

select * into t2 
from(
   SELECT 336.6 as val 
   UNION ALL 
   SELECT NULL
) as x

If you now look at the columns, you see a numeric with numeric precision of 4 and scale of 1

select * from INFORMATION_SCHEMA.COLUMNS  where TABLE_NAME='T2'

SQL made that decision based on the smallest numeric precision to hold 336.6. Now, when you ask it to convert the NULL to 5005, you are saying, convert any NULL values to a number too big to fit in a numeric with the precision of 4 and a scale of 1. The error message indicates that 5005 won't fit in Numeric(4,1)

This will work because the table will now generate a larger numeric field, since SQL needs to accommodate 5005. Create the table using the new contents of T2 from below, and the field type should go to Numeric(5,1) allowing the 5005 to fit.

 select IsNull(t2.val, 5005)
    from(

    SELECT 336.6 as val UNION ALL 
    SELECT 5005

    ) as t2

When you run the statement without a NULL in your inner query, SQL never evaluates the 5005, so it doesn't reach a condition where it needs to put 5005 into a numeric(4,1) field.

select IsNull(t2.val, 5005)
from(

SELECT 336.6 as val

) as t2