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:
Msg 8153 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:
SELECT a.All_Legs_PKey, (SELECT MAX(e.received_date) FROM Call_Credits e WHERE e.all_legs_pkey = a.All_Legs_PKey ) as sumcolFROM call aWHERE a.call_date BETWEEN 'Jan 1 2001 12:00AM' AND 'Jan 8 2001 5:59AM'
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:
|--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]))
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.