SQL Server user-defined aggregate returns an error if aggregating 0 rows

Question

I have this SQL Server user-defined aggregate:

[SqlUserDefinedAggregate(Format.UserDefined, Name="median", IsInvariantToDuplicates=false, IsInvariantToNulls=true, IsInvariantToOrder=true, IsNullIfEmpty=true, MaxByteSize=8000)]
public class MedianCalculator : IBinarySerialize {
    private List<double> values;

    public void Init() {
        values = new List<double>();
    }

    public void Accumulate(SqlDouble value) {
        if (!value.IsNull)
            values.Add(value.Value);
    }

    public void Merge(MedianCalculator other) {
        values.AddRange(other.values);
    }

    public SqlDouble Terminate() {
        if (values == null || values.Count == 0)
            return SqlDouble.Null;
        values.Sort();
        return (values[(int)Math.Floor((values.Count - 1) / 2.0)] + values[(int)Math.Ceiling((values.Count - 1) / 2.0)]) / 2.0;
    }

    public void Read(BinaryReader r) {
        int c = r.ReadInt32();
        values = new List<double>(c);
        for (int i = 0; i < c; i++)
            values.Add(r.ReadDouble());
    }

    public void Write(BinaryWriter w) {
        w.Write(values.Count);
        for (int i = 0; i < values.Count; i++)
             w.Write(values[i]);
    }
}

After deploying this aggregate, I try to run this query:

select dbo.median(x) from (select 1 as x where 1 = 0) t

and I get this error:

A severe error occurred on the current command.  The results, if any, should be discarded.

However, this works:

create table #t(x int)
select dbo.median(x) from #t
drop table #t

and returns NULL as expected.

This seems like a bug in SQL Server, but how can I work around it?

select @@version
Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)   Nov 24 2008 13:01:59   Copyright (c) 1988-2005 Microsoft Corporation  Developer Edition on Windows NT 6.1 (Build 7100: ) 

Answer 1

I don't know about the IsNullIfEmpty property, but what helped me with this error is using "OPTION(MAXDOP 1)" with the query. Apparently there is some kind of concurrency problem with CLR aggregate funktions in SQL Server 2005 and "MAXDOP 1" limits the degree of parallelism.

Answer 2

Change the IsNullIfEmpty property of the SqlUserDefinedAggregateAttribute to false and it will work.

This is definitely a bug in SQL Server, hopefully someone important will notice it and open a Connect issue for it.