Re: WWW/Internet 2009: 2nd CFP until 21 September x

Walter Mitty wrote:
...
> Connecting SQL and "facts" can be tricky, particularly when nulls are
> involved.
>
> If I say
>
> select distinct OrderId, MailingAddress from Orders
>
> and there are some rows with Null in MailingAddress, those rows don't
> appear in the result. The row may exist in orders, but its projection on
> the space
> {OrderId, MailingAddress} doesn't exist (at least in the dialects I used).
> (assuming that what SELECT DISTINCT does is really a projection.)
>
> Likewise if I say select count(MailingAddress) from Orders
>
> I'm going to get the count of rows that have a value in MailingAddress. If
> I compare with
>
> select count(1) from Orders where MailingAddress is not null
> select count(1) from Orders where MailingAddress is null
> select count(1) from Orders
>
>
>
> I can see what happened. If some rows actually have null in Mailing
> address, it is as though those rows were not there when you do some
> operation that only deals with rows containing a value in MailingAddress.
>
> Comparing this behaviour with either the open world assumption or the closed
> world assumption is largely in the mind of the beholder.

Thanks for those examples. They suggest to me that SQL tables with nulls must indeed have multiple predicates, maybe they are a little similar to what Hugh Darwen and his discussion group call 'multi-relations'. While he doesn't recommend them, he wrote a paper about them, it''s on the ttm site. If I'm not mistaken, he needed to invent algebraic operators to deal with multi-relations in some logical fashion, eg., to pick which relation one is interested in. It seems that SQL's "is null"/"is not null" clause/operator definitions don't have similar underpinnings.

A few years ago I asked here of people who had access to the official sql standard how the word 'relation' was defined. To my surprise, the very few responders said it wasn't mentioned. Received on Sat Aug 15 2009 - 01:17:03 CEST