PRB: Unexpected Warning About Eliminating NULLs from Aggregate (317312)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
This article was previously published under Q317312

SYMPTOMS

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.

CAUSE

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.

STATUS

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.

MORE INFORMATION

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 sumcol
FROM call a
WHERE 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.
Modification Type:MajorLast Reviewed:9/25/2003
Keywords:kbnofix kbprb KB317312
©2003 Microsoft Corporation. All rights reserved.