Re: Does Codd's view of a relational database differ from that ofDate& Darwin? [M.Gittens]
From: Paul <paul_at_test.com>
Date: Tue, 07 Jun 2005 15:49:47 +0100
Message-ID: <42a5b411$0$8714$ed2619ec_at_ptn-nntp-reader02.plus.net>
>
> Not exactly: SQL ignores NULLs in aggregate functions (except COUNT(*)).
> It is not treated as zero for AVG, for instance. Also note that x + NULL
> evaluates to NULL; therefore, SQL's SUM is not iterated addition -- it
> has a much more complicated definition.
Date: Tue, 07 Jun 2005 15:49:47 +0100
Message-ID: <42a5b411$0$8714$ed2619ec_at_ptn-nntp-reader02.plus.net>
Jon Heggland wrote:
>>>>Why not say then that all aggregates that involve a NULL return NULL? >>> >>>This is what SQL does. Sometimes we want to get a value but we get a >>>null instead. >> >>Are you sure? I've just tried summing a column that contains a NULL in >>PostgreSQL and it doesn't return NULL - it treats the NULLs as zeros.
>
> Not exactly: SQL ignores NULLs in aggregate functions (except COUNT(*)).
> It is not treated as zero for AVG, for instance. Also note that x + NULL
> evaluates to NULL; therefore, SQL's SUM is not iterated addition -- it
> has a much more complicated definition.
I'd suggest that SQL's SUM *should* be iterated addition. And that all aggregates should return NULL if any of the attributes in the aggregated column are NULL. Surely if any of them are unknown, we must say that the sum or average is unknown also?
What are the arguments for not doing this?
Paul. Received on Tue Jun 07 2005 - 16:49:47 CEST