收到警告:聚合或其他 SET 操作消除了空值 [英] Getting warning: Null value is eliminated by an aggregate or other SET operation
问题描述
我有这个架构
创建表 t(id int, d date)插入 t (id, d) 值 (1, getdate()),(2, NULL)
什么时候做
声明@mindate日期从 t 中选择 @mindate = min(d)
我收到警告
<块引用>通过聚合或其他 SET 操作消除空值
为什么以及我能做些什么?
大多数情况下你不应该做任何事情.
- 可以通过将
ansi_warnings
设置为关闭来禁用警告,但这具有在第三个蛋糕称重后,秤坏了,因此没有关于第四个蛋糕的信息,但仍然可以测量周长.
+--------+--------+--------------+|蛋糕编号 |重量 |周长 |+--------+--------+---------------+|1 |50 |12.0 ||2 |80 |14.2 ||3 |70 |13.7 ||4 |空 |13.4 |+--------+--------+---------------+
查询
SELECT MAX(Weight) AS MaxWeight,AVG(Circumference) AS AvgCircumference从蛋糕
退货
+-----------+-----------------+|最大重量 |平均周长 |+-----------+-----------------+|80 |13.325 |+-----------+-----------------+
尽管从技术上讲,不能肯定地说 80 是最重蛋糕的重量(因为未知数可能更大),但上述结果通常比简单地返回未知数更有用.
+-----------+-----------------+|最大重量 |平均周长 |+-----------+-----------------+|?|13.325 |+-----------+-----------------+
很可能您希望忽略 NULL,而警告只是提醒您这种情况正在发生.
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.
- It is possible to disable the warning by setting
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. - In some limited cases you can rewrite the aggregate to avoid it. e.g.
COUNT(nullable_column)
can be rewritten asSUM(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
returnsNULL
(regardingNULL
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 yieldingNULL
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.
这篇关于收到警告:聚合或其他 SET 操作消除了空值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
- It is possible to disable the warning by setting