Why does ISNULL generate no "Warning: Null value is eliminated by an aggregate or other SET operation." but CASE IS NULL and COALESCE do?

Question

I had this that generated a "Null value is eliminated by an aggregate or other SET operation." warning:

SELECT (SELECT SUM(CASE WHEN prix_unitaire IS NULL OR qte_vendues IS NULL THEN 0 ELSE prix_unitaire * qte_vendues END)
        FROM facturation_detail AS fdp
        WHERE fdp.id_facturation_detail_parent = fd.id_facturation_detail) AS prix_groupe
FROM facturation_detail AS fd
WHERE id_facturation_entete = '1AB10ADA-58F9-EA11-84FF-D4AE526DD012'

I changed it to this, but it still generated a warning:

SELECT (SELECT SUM(COALESCE(prix_unitaire * qte_vendues, 0))
        FROM facturation_detail AS fdp
        WHERE fdp.id_facturation_detail_parent = fd.id_facturation_detail) AS prix_groupe
FROM facturation_detail AS fd
WHERE id_facturation_entete = '1AB10ADA-58F9-EA11-84FF-D4AE526DD012'

Changing it to this finally got rid of the warning, but I don't understand why this third query got rid of the warning but not the previous two:

SELECT (SELECT SUM(ISNULL(prix_unitaire, 0) * ISNULL(qte_vendues, 0))
        FROM facturation_detail AS fdp
        WHERE fdp.id_facturation_detail_parent = fd.id_facturation_detail) AS prix_groupe
FROM facturation_detail AS fd
WHERE id_facturation_entete = '1AB10ADA-58F9-EA11-84FF-D4AE526DD012'

Can someone explain why the first two generate the warning, but not the last one? prix_unitaire and qte_vendues are of types decimal(10, 4) and decimal(8, 2) respectively, both allowing NULL values, in case that's relevant.

EDIT: The only rows I have into facturation_detail that have id_facturation_entete = '1AB10ADA-58F9-EA11-84FF-D4AE526DD012' are the following three:

id_facturation_entete|id_facturation_detail|id_facturation_detail_parent|prix_unitaire|qte_vendues
1AB10ADA-58F9-EA11-84FF-D4AE526DD012|1BB10ADA-58F9-EA11-84FF-D4AE526DD012|00000000-0000-0000-0000-000000000000|25.5000|1.00
1AB10ADA-58F9-EA11-84FF-D4AE526DD012|1CB10ADA-58F9-EA11-84FF-D4AE526DD012|00000000-0000-0000-0000-000000000000|47.8400|1.00
1AB10ADA-58F9-EA11-84FF-D4AE526DD012|1DB10ADA-58F9-EA11-84FF-D4AE526DD012|00000000-0000-0000-0000-000000000000|47.8400|1.00

EDIT: I'm using SQL Server Management Studio 2012.

EDIT: A screenshot of the execution plan for the first and third queries (the first and second have the same execution plan):

Answer 1

Clearly, the first two should not generate the error. As you have written the code, no NULL values are passed through to the aggregation. Only zero is passed in. Hence, I would consider this a bug, but I'm not so sure that Microsoft agrees.

What I suspect is happening is that SQL Server is optimizing the code by rearranging the operations. This rearrangement should not affect the results at all (which is why it is allowed). Well, except that you can get errors and warnings that should not be in the original query.

I don't know why this would be happening in this case. It sounds like a micro-optimization, where eliminating the some NULL comparisons before the sum() is marginally faster because the sum() is checking for NULL anyway.

A more common example is that a query like this can generate an error:

select 1 / x
from t
where x <> 0;

SQL Server can rearrange the operations so the division occurs before the filtering.

Answer 2

This is just a warning, a reminder, that NULL is "ignored" in aggregations. Aggregate functions do not consider NULL at all. All three following statements will give back the warning:

select sum(val)
from 
(values (1), (2), (null), (null)) as t(val);

select max(val)
from 
(values (1), (2), (null), (null)) as t(val);

select min(val)
from 
(values (1), (2), (null), (null)) as t(val);

When ISNULL(, 0) is used, NULL goes away and the aggregate function operates on a value, i.e 0. The same, happens with CASE when it always returns a value(not null):

--no warning
select sum(isnull(val, 0))
from 
(values (1), (2), (null), (null)) as t(val);


select sum(case when a is null or b is null then 0 else a*b end)
from 
(values (1, 1), (2, null), (null, null), (null, 3)) as t(a, b);

You could safely ignore the warning, unless you did not expect NULL values in the aggregation and you have the warning as a reminder for that.

Caution: isnull(, 0) will change the result for most of the aggregates and more often than not it is unnecessary:

select min(val), min(isnull(val, 0)) 
from 
(values (1), (2), (null), (null)) as t(val);