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?
Mostly you should do nothing about it.
ansi_warnings
off but this has other effects, e.g. on how division by zero is handled and can cause failures when your queries use features like indexed views, computed columns or XML methods.COUNT(nullable_column)
can be rewritten as SUM(CASE WHEN nullable_column IS NULL THEN 0 ELSE 1 END)
but this isn't always possible to do straightforwardly without changing the semantics.It's just an informational message required in the SQL standard. Apart from adding unwanted noise to the messages stream it has no ill effects (other than meaning that SQL Server can't just bypass reading NULL
rows, which can have an overhead but disabling the warning doesn't give better execution plans in this respect)
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)
but
SELECT SUM(N) FROM (VALUES (NULL), (3), (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)
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
Returns
+-----------+------------------+ | 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.
@juergen provided two good answers:
SET ANSI_WARNINGS OFF
select @mindate = min(isnull(d, cast(0 as datetime))) from t
However if you want to ignore rows where the d column is null and not concern yourself with the ANSI_WARNINGS
option then you can do this by excluding all rows where d is set to null as so:
select @mindate = min(d) from t where (d IS NOT NULL)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With