On Thu, Sep 08, 2005 at 05:16:30PM -0400, D. Richard Hipp wrote:
> So SQL sez:
>
> SUM(<empty-set>) == NULL
> SUM(<all-nulls>) == NULL
> SUM(<some-nulls>) == Sum of non-null values
>
> Martin sez:
>
> SUM(<empty-set>) == 0
> SUM(<all-nulls>) == NULL
> SUM(<some-nulls>) == NULL
>
> SQLite 3.2.5 does this:
>
> SUM(<empty-set>) == 0
> SUM(<all-nulls>) == 0
> SUM(<some-nulls>) == Sum of non-null values
>
> I can understand Martin's point of view. The SQL standard
> point of view makes no sense to me at all.
Note that all three of the above systems define SUM(<empty-set>)
to be an identity element such that:
SUM(SUM(X), SUM(<empty-set>)) = SUM(X)
However, the following system breaks the identity:
SUM(<empty-set>) == 0
SUM(<all-nulls>) == NULL
SUM(<some-nulls>) == Sum of non-null values
SUM(SUM(<all-nulls>), SUM(<empty-set>)) = SUM(NULL, 0) = 0 != SUM(<all-nulls>)
So for a mathematically consistent definition of SUM, it makes sense that
SUM(<empty-set>) == NULL if SUM(<all-nulls>) == NULL.
- glen
