Re: Does Codd's view of a relational database differ from that of Date&Darwin? [M.Gittens]

"Alfredo Novoa" <alfredo_novoa_at_hotmail.com> wrote in message news:g2fba11tarn9pb4m8m2vphljgru5bi26sr_at_4ax.com...
>
> On Tue, 7 Jun 2005 15:07:09 +0200, Jon Heggland <heggland_at_idi.ntnu.no>
> wrote:
>
>>In article <42a591c4$0$41901$ed2619ec_at_ptn-nntp-reader03.plus.net>,
>>paul_at_test.com says...
>>> Alfredo Novoa 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.
>
> Thanks for the clarification.

On the contrary, the answer is wrong for the MS TSQL. The AVG and SUM commands ignore nulls - they are not treated as *anything*, which in fact, they're not.

And x + NULL does not evaluate to NULL but x. See the appropriate SET command in another post. Here the SQL SUM *is* iterated addition.

> Here is clear that SQL nulls are a complete botch-up.

On the contrary, some understanding of the management of nulls by SQL is missing some relevant information, and I suspect it may be purposeful in order to mimick the rhetorical [see Gittens] arguments of Fearless Leader CJDate ;-)

-- 
Pete Brown
IT Managers & Engineers
Falls Creek
Australia
www.mountainman.com.au/software