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

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.