开发者

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

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 aggreg开发者_如何学编程ate, 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: ) 


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.


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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜