Re: Semantics of SUM

On 14 Nov 2009, at 22:01, Andy Seaborne wrote:
> On 14/11/2009 06:05, Steve Harris wrote:
>> On 13 Nov 2009, at 17:05, Andy Seaborne wrote:
>>> Summary :
>>> SQL standard requires nulls to be skipped in SUM.
>>> MySQL skips errors just like NULLs.
>>> PostgreSQL skips nulls and expression errors on sum(val+5)
>>> PostgreSQL causes an error on casting error.
>>> Addition with + and nulls does not work as sum over nulls.
>>
>> The last point I'm not sure sure that it's possible to tell is it? As
>> SUM skips NULLs, how do you tell the difference?
>
> The SQL standard says that aggregates can see the nulls and then  
> choose what to do.  Sum() does not include it in the summation so it  
> isn't as simple as sum is "+" over the group.  All the standard  
> aggregates do this.

SQL-92 says that given a table T, the table on which the function is  
applied is "the result of applying the <value expression> to each row  
of T and eliminating null values". COUNT(*) is noted as an exception  
in the previous paragraph. In §6.5, around page 125.

The SQL spec is not terribly easy to follow, but my reading of this  
(and the surrounding paragraphs) are that, given T: v ( 1, 2, 3,  
null ) and the expression SUM(v+1), you compute (2, 3, 4, null), then  
remove the null to leave TX: (2, 3, 4), and apply SUM() to that.

SUM in SQL is not described in terms of SQL +, but its semantics are  
compatible with SPARQL's +. The SQL definition of SUM is rather vague.

SUM etc. return null if they are passed no values, e.g. if all input  
values were null.

- Steve

-- 
Steve Harris, CTO, Garlik Limited
2 Sheen Road, Richmond, TW9 1AE, UK
+44(0)20 8973 2465  http://www.garlik.com/
Registered in England and Wales 535 7233 VAT # 849 0517 11
Registered office: Thames House, Portsmouth Road, Esher, Surrey, KT10  
9AD

Received on Sunday, 15 November 2009 10:35:27 UTC