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

From: mountain man <hobbit_at_southern_seaweed.com.op>
Date: Thu, 09 Jun 2005 01:43:09 GMT
Message-ID: <N8Npe.11005$F7.1083_at_news-server.bigpond.net.au>


"Paul" <paul_at_test.com> wrote in message news:42a6c7bd$0$32618$ed2619ec_at_ptn-nntp-reader02.plus.net...
> mountain man wrote:
>>>>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.
>
> I'd rather the SUM of a column containing a NULL return NULL. If I want
> to override the behaviour, I can always do a SUM(COALESCE(col)). But if
> the default is to treat NULLs as zero, I can't manually override it to
> the behaviour I want.

Have look at the equivalent SET command for your (R)DBMS. You should be able to switch this functionality on or off at a DB level and/or at the line level.

> At least MSSQL warns you that it's eliminated a NULL from an aggregate,
> although that's not much use if you're not running the query
> interactively.
>
> And the default behaviour of pretty much all SQL DBMSs is that x + NULL
> is NULL. Maybe some obscure settings can override it but I doubt they
> are used much.

As someone else has recently commented, it has been available for probably a decade, and I can clearly remember using it from the mid to late 90's.

Both functionalities have their place. They only have to be managed carefully, but what doesn't nowdays?

-- 
Pete Brown
Falls Creek
OZ
www.mountainman.com.au
Received on Thu Jun 09 2005 - 03:43:09 CEST

Original text of this message