HangFire – SQL Server – “Warning: Null value is eliminated by an aggregate or other SET operation.”

Background

In our first two posts, we introduced HangFire using two sample codes.

The Sample codes are :-

  1. Hangfire.io – Installation & Hello World Application
    Link
  2. Hangfire.IO – More Complex Application
    Link

 

In this Post

In this post, we will start exploring the data we captured through Extended Events.

 

Extended Event

Category :- Error Reported

Image

 

Tabulated

 Field Value
 error_number  8153
 message  Warning: Null value is eliminated by an aggregate or other SET operation.
 severity  10
 sql_text  sql_text (@count int)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] 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]); COMMIT TRAN
 state  1
 user_defined  False

 

 

TroubleShooting

Add Test Data


set nocount on;
set XACT_ABORT on;
go

declare @commit bit
declare @strLog varchar(100)

set @commit =0
set @commit =1

begin tran

	insert into [HangFire].[Counter]
	(
		  [Key]
		, [Value]
		, [ExpireAt]
	)
	select
		1
		, rand() * 100
		, null --rand() * 1000
	union all
	select
		2
		, rand() * 100
		, null --rand() * 1000

	select top 5 *
	from   [HangFire].[Counter] C 


while (@@trancount > 0)
begin

	if (@commit =1)
	begin

		print 'commit'
		commit tran;

	end
	else
	begin

		print 'rollback'
	
		rollback tran;
	
	end

end
go


Original Code

Script


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] 

		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])
		;  
		
--COMMIT TRAN 
ROLLBACK TRAN

Output

Image


Textual

Warning: Null value is eliminated by an aggregate or other SET operation.

 

Revised Code

Script



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 
						NULLIF
							(
								   MAX([ExpireAt]) 
							    , '1753-01-01'
							)
                FROM
                (
                    VALUES
                      (
						   [Source].ExpireAt
					  )
                    , ([Target].[ExpireAt])
                ) 
                AS MaxExpireAt
                (
                    [ExpireAt]
                )
        ) 
                 
    WHEN NOT MATCHED THEN
        INSERT ([Key], [Value], [ExpireAt]) 
        VALUES ([Source].[Key], [Source].[Value], [Source].[ExpireAt])
        ;  
         
	select  = '@RecordsToAggregate', *
	from   @RecordsToAggregate 

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

--COMMIT TRAN 
ROLLBACK TRAN

The basic change is us replacing

with

 

Source Code Control

GitHub

DanielAdeniji/hangfireSQLNullValueEliminatedByAggregate

Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s