SQL Server - Warning - "Null value is eliminated by an aggregate or other SET operation"

bug?
1

When we run HangFire and placed a trace using SQL Server Profile against the DB, we noticed the warning “Null value is eliminated by an aggregate or other SET operation”.

Traced the error back to this code:
;MERGE [HangFire].[AggregatedCounter] WITH (HOLDLOCK) AS [Target]
USING
(
SELECT
[Key]
, SUM([Value]) as [Value]
, MAX([ExpireAt]) AS [ExpireAt]

    FROM @RecordsToAggregate  

    GROUP BY
            [Key]

) AS [Source] 
(
      [Key]
    , [Value]
    , [ExpireAt]
) 
    ON [Target].[Key] = [Source].[Key] 
     
WHEN MATCHED THEN
    UPDATE SET  
               [Target].[Value] = [Target].[Value] + [Source].[Value]
            ,  [Target].[ExpireAt] = 
    (
            SELECT MAX([ExpireAt]) 
            FROM
            (
                VALUES
                  ([Source].ExpireAt)
                , ([Target].[ExpireAt])
            ) 
            AS MaxExpireAt
            (
                [ExpireAt]
            )
    ) 
             
WHEN NOT MATCHED THEN
    INSERT ([Key], [Value], [ExpireAt]) 
    VALUES ([Source].[Key], [Source].[Value], [Source].[ExpireAt])
    ;

To fix:

;MERGE [HangFire].[AggregatedCounter] WITH (HOLDLOCK) AS [Target]
USING
(
SELECT
[Key]
, SUM([Value]) as [Value]
– dadeniji - when ExpiryAt is null, replace with 1900-01-01
–, MAX([ExpireAt]) AS [ExpireAt]
, MAX(isNull([ExpireAt], ‘1900-01-01’)) AS [ExpireAt]

    FROM @RecordsToAggregate  

    GROUP BY
            [Key]

) AS [Source] 
(
      [Key]
    , [Value]
    , [ExpireAt]
) 
    ON [Target].[Key] = [Source].[Key] 
     
WHEN MATCHED THEN
    UPDATE SET  
               [Target].[Value] = [Target].[Value] + [Source].[Value]
            ,  [Target].[ExpireAt] = 
    (
            SELECT MAX([ExpireAt]) 
            FROM
            (
                VALUES
                  ([Source].ExpireAt)
                , ([Target].[ExpireAt])
            ) 
            AS MaxExpireAt
            (
                [ExpireAt]
            )
    ) 
             
WHEN NOT MATCHED THEN
    INSERT ([Key], [Value], [ExpireAt]) 
    VALUES ([Source].[Key], [Source].[Value], [Source].[ExpireAt])
    ;

Blogged about it here:

HangFire – SQL Server – “Warning: Null value is eliminated by an aggregate or other SET operation.”
https://learningintheopen.org/2017/04/24/hangfire-sql-server-warning-null-value-is-eliminated-by-an-aggregate-or-other-set-operation/

2

This is not a correct solution, because

  1. Target.ExpireAt can be null, and it is still aggregated with MAX(), so it hardly fixes anything.
  2. What’s more important, new records will have ExpireAt = ‘1900-01-01’ instead of null, and will be wrongly deleted by the expiration manager.i
3

PieceoSummer:

set nocount on
go

declare @count int

set @count = 10

DECLARE @RecordsToAggregate TABLE
(
[Key] NVARCHAR(100) NOT NULL
, [Value] SMALLINT NOT NULL
, [ExpireAt] DATETIME NULL
)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

BEGIN TRAN

DELETE TOP (@count) C 
OUTPUT DELETED.[Key], DELETED.[Value], DELETED.[ExpireAt] 
INTO @RecordsToAggregate 
FROM [HangFire].[Counter] C WITH (READPAST, XLOCK, INDEX(0))  
 
SET NOCOUNT ON 

;MERGE [HangFire].[AggregatedCounter] WITH (HOLDLOCK) AS [Target] 
USING 
(  
    SELECT
              [Key]
            , SUM([Value]) as [Value]
            --, MAX([ExpireAt]) AS [ExpireAt] 
			, Max
				(
					case 
						when ([ExpireAt] is not null) then [ExpireAt]
						else '1753-01-01'
					end
				)

    FROM @RecordsToAggregate  

    GROUP BY
            [Key]

) AS [Source] 
(
      [Key]
    , [Value]
    , [ExpireAt]
) 
    ON [Target].[Key] = [Source].[Key] 
     
WHEN MATCHED THEN
    UPDATE SET  
               [Target].[Value] = [Target].[Value] + [Source].[Value]
            ,  [Target].[ExpireAt] = 
    (
            SELECT MAX([ExpireAt]) 
            FROM
            (
                VALUES
                  (
					NULLIF
					(
						   [Source].ExpireAt
						 , '1753-01-01'
					)
				  )
                , ([Target].[ExpireAt])
            ) 
            AS MaxExpireAt
            (
                [ExpireAt]
            )
    ) 
             
WHEN NOT MATCHED THEN
    INSERT ([Key], [Value], [ExpireAt]) 
    VALUES ([Source].[Key], [Source].[Value], [Source].[ExpireAt])
    ;  
     
select [source] = '@RecordsToAggregate', *
from   @RecordsToAggregate 

select [source] = '[HangFire].[AggregatedCounter]', *
from   [HangFire].[AggregatedCounter]

–COMMIT TRAN
ROLLBACK TRAN