Article ID: 317312 - View products that this article applies to.
This article was previously published under Q317312
If you run a query that contains an aggregate function in the subquery, and if the subquery returns a non-NULL column, it may be possible for the query as a whole to generate the following warning message:
Warning: Null value is eliminated by an aggregate or other SET operation.
In certain cases, SQL Server performs a transformation that moves the calculation of the aggregate after the join used to process the subquery. If the subquery ever returns an empty result set, SQL Server replaces the value for the subquery with a NULL, so the aggregate encounters the NULL value that it would not have processed if SQL Server processed the aggregate prior to the join.
This behavior is by design. If you encounter the warning message shown in the "Symptoms" section under the conditions described in the "Cause" section, you can ignore the message.
The optimizer considers this transformation of computing the aggregate after the subquery as long as it does not affect the results SQL Server returns. One requirement that must be present for the preceding statement to be true is that the selected column in the subquery must not allow NULLs.
The following query demonstrates the problem:
The received_date column does not allow NULLs, so the MAX aggregate should not process any NULL values as long as it is processed inside of the subquery. However, the optimizer converts this subquery into an outer join, with the aggregate above it as follows:
Thus, in cases where there is no match found by the subquery, the outer join returns NULL. The aggregate then processes the rows with NULL and produces the warning message.
|--Compute Scalar(DEFINE:([Expr1002]=[Expr1002])) |--Hash Match(Aggregate, HASH:([Rank1006]), RESIDUAL:([Rank1006]=[Rank1006]) DEFINE:([Expr1002]=MAX([e].[Received_Date]), [a].[All_Legs_PKey]=ANY([a].[All_Legs_PKey]))) |--Hash Match(Left Outer Join, HASH:([a].[All_Legs_PKey])=([e].[All_Legs_PKey])) |--Rank | |--Clustered Index Scan(OBJECT:([max_agg].[dbo].[Call].[IX_Call_All_Legs_PKey_Tag_Leg] AS [a]), WHERE:([a].[Call_Date]>='Jan 1 2001 12:00AM' AND [a].[Call_Date]<='Jan 8 2001 5:59AM')) |--Table Scan(OBJECT:([max_agg].[dbo].[Call_Credits] AS [e]))
Article ID: 317312 - Last Review: September 25, 2003 - Revision: 3.1