juergen d juergen d - 2 months ago 10
SQL Question

Getting warning: Null value is eliminated by an aggregate or other SET operation

I have this schema

create table t(id int, d date)

insert into t (id, d) values (1, getdate()),
(2, NULL)

When doing

declare @mindate date
select @mindate = min(d) from t

I get the warning

Null value is eliminated by an aggregate or other SET operation

Why and what can I do about it?


You can also do nothing about it.

It's just an informational message required in the SQL standard. It has no ill effects.

The reason for returning this message is that throughout most operations in SQL nulls propagate.

SELECT NULL + 3 + 7 returns NULL (regarding NULL as an unknown quantity this makes sense as ? + 3 + 7 is also unknown)


               (7)) V(N) 

Returns 10 and the warning that nulls were ignored.

However these are exactly the semantics you want for typical aggregation queries. Otherwise the presence of a single NULL would mean aggregations on that column over all rows would always end up yielding NULL which is not very useful.

Which is the heaviest cake below? (Image Source, Creative Commons image altered (cropped and annotated) by me)

enter image description here

After the third cake was weighed the scales broke and so no information is available about the fourth but it was still possible to measure the circumference.

| CakeId | Weight | Circumference |
|      1 | 50     | 12.0          |
|      2 | 80     | 14.2          |
|      3 | 70     | 13.7          |
|      4 | NULL   | 13.4          |

The query

SELECT MAX(Weight)        AS MaxWeight,
       AVG(Circumference) AS AvgCircumference
FROM   Cakes 


| MaxWeight | AvgCircumference |
|        80 |          13.325  |

even though technically it is not possible to say with certainty that 80 was the weight of the heaviest cake (as the unknown number may be larger) the results above are generally more useful than simply returning unknown.

| MaxWeight | AvgCircumference |
|         ? |          13.325  |

So likely you want NULLs to be ignored, and the warning just alerts you to the fact that this is happening.