Custom aggregate function intermittently throws NullReferenceException

Question

I have a SQL CLR aggregate function which sporadically throws a System.NullReferenceException exception when executed against the same dataset. The purpose of the custom aggregate is to:

Return latest(x, y) where x is a DATETIME column and y is an INTEGER column.

The value for column y for the most recent value of column x will be returned.

The dataset being hit by the query is a subset of 142,145 rows of a 2,931,563 row table, with the aggregation resulting in (when it runs) 141,654 rows being returned.

The code for the CLR aggregate function is as follows:

using System.Data.SqlTypes;
using System.Runtime.InteropServices;
using Microsoft.SqlServer.Server;

[StructLayout(LayoutKind.Sequential)]
[SqlUserDefinedAggregate(Format.Native, IsInvariantToDuplicates = true, IsInvariantToNulls = true, IsInvariantToOrder = true, IsNullIfEmpty = true, Name = "Latest")]
public class Latest
{
    private SqlDateTime latestDateTime;
    private SqlInt32 latestValue;

    public void Init()
    {
        latestDateTime = SqlDateTime.Null;
        latestValue = SqlInt32.Null;
    }

    public void Accumulate(SqlDateTime recordDateTime, SqlInt32 value)
    {
        if (latestDateTime.IsNull)
        {
            latestDateTime = recordDateTime;
            latestValue = value;
        }
        else
        {
            if (recordDateTime > latestDateTime)
            {
                latestDateTime = recordDateTime;
                latestValue = value;
            }
        }
    }

    public void Merge(Latest value)
    {
        if ((value.latestDateTime < latestDateTime) || (latestDateTime.IsNull))
        {
            latestValue = value.latestValue;
            latestDateTime = value.latestDateTime;
        }
    }

    public SqlInt32 Terminate()
    {
        return latestValue;
    }
};

As far as I can tell there's nowhere in the function that can result in a null reference, assuming that SQL server is following the contract outlined on MSDN (though it's much more likely I'm wrong than SQL Server!). So in short, what am I missing here?

To clarify:

• I believe I've met the requirements of the contract for a SqlUserDefinedAggregate (all required methods implemented)
• The code initialises all member variables in the Init method (again part of the contract implementation) to ensure that if SQL re-uses (as it's permitted to) an instance of the aggregate for a different group it's cleaned down and non-null
• Clearly I've missed a nuance of the contract that I'm expected to meet as I can see no reason for the NullReferenceException to be thrown. What have I missed?

Answer 1

This may turn out not to be the answer, but as I need to include code, I won't post it as a comment.

Having a NULL value for value.latestDateTime wouldn't cause this error. You only get the NULLReferenceException when an OBJECT is null, and you try to refer to it (by accessing it's properties for instance).

The only place in your code where I see you referencing an object is in the Merge void. The object is value which is of type Latest.

I know you say it's not theoretically possible for value to ever be null, but that can't be proven or disproven in the code you've posted since Merge is public and therefore accessed from other applications.

I have found in OOP that it's safest to ALWAYS null-check any object before referencing it. All you have to do is change your Merge code to this (if my c# memory serves...if my syntax is off, I'm sure you'll get the idea):

public void Merge(Latest value)
{
    if (value != null)
    {
        if ((value.latestDateTime < latestDateTime) || (latestDateTime.IsNull))
        {
            latestValue = value.latestValue;
            latestDateTime = value.latestDateTime;
        }
    }
}

It's up to you if you want to do something else when value IS null. But this makes this stretch of code absolutely safe from NullReferenceExceptions, rather than trusting to "theoretically shouldn't be possible", which almost always means "IS possible" ; )

Answer 2

Everything does appear to be correct. Except for possibly one thing. Can either of the source columns for the two input parameters be NULL? The code itself does seem to handle NULLs. However, if there are NULLs in the data then I think you have the IsInvariantToNulls property of the SqlUserDefinedAggregate attribute set incorrectly as it should be false since a NULL in either field could affect the result.

Also:

• The datatype of the field being used for recordDateTime is DATETIME and not DATETIME2, correct?

• Try running the query after adding OPTION(MAXDOP 1) as a query hint as that should prevent the Merge method from being called and that can help narrow down the issue. If the exception never happens with OPTION(MAXDOP 1) then it most likely has to do with the Merge method. But if it still occurs, then it most likely has nothing to do with the Merge method.

• To clarify some questioning of how SqlDateTime handles comparison operators (i.e. the < and >) when .IsNull is true: it is handled properly and will return a false if either side of those operators has .IsNull == true.

• I would also remove the [StructLayout(LayoutKind.Sequential)] decorator. The default .NET handling should be fine.

Answer 3

I would add explicit check for NULL:

public void Accumulate(SqlDateTime recordDateTime, SqlInt32 value)
{
    if (latestDateTime.IsNull)
    {
        latestDateTime = recordDateTime;
        latestValue = value;
    }
    else
    {
        if (!recordDateTime.IsNull)
        {
            if (recordDateTime > latestDateTime)
            {
                latestDateTime = recordDateTime;
                latestValue = value;
            }
        }
    }
}

Besides, your logic in Merge seems wrong... I'd repeat the same code as in Accumulate:

public void Merge(Latest value)
{
    if (latestDateTime.IsNull)
    {
        latestDateTime = value.latestDateTime;
        latestValue = value.latestValue;
    }
    else
    {
        if (!value.latestDateTime.IsNull)
        {
            if (value.latestDateTime > latestDateTime)
            {
                latestDateTime = value.latestDateTime;
                latestValue = value.latestValue;
            }
        }
    }
}