Re: WWW/Internet 2009: 2nd CFP until 21 September x
Date: Sat, 15 Aug 2009 13:54:59 GMT
Walter Mitty wrote:
> The way I think of it is that every table with nulls in it is a materialized
> outer join. If you can decompose the table into multiple tables each of
> which has no nulls, what you discover is that a null in the combined table
> corresponds to an absent row in one of the decomposed tables.
> Let me shift gears back into practical mode for a minute. In any database
> I've ever worked with, the majority of columns are not a primary key, or a
> foreign key or a part of a primary or foreign key, or ever appear in a where
> of having clause. Nulls in those columns are of almost no consequence at
> all in the overall behavior of queries. Shunning nulls in those cases is
> being overly picky.
> Nulls in "important" columns almost always cause more trouble than
> decomposing tables would cause, but nulls in inumportant columns help keep
> things simple.
> But I guess comments like this one are out of place in a theory newsgroup.
I'm with you when you suggest that experience counts and I think it's fair ball to point out where an inconsistent theory might produce consistent practical results. That might underline where the theory needs work but so does a consistent theory that might not seem to cover all bases. Deciding where to alleviate some problems can be very hard, especially when people want to think their livelihood is at stake.
It might seem perfectly reasonable that certain columns will never appear in a where clause, but in the underlying algebra they do appear. Right? I suspect that many practitioners don't realize that the implementation of a 'where restriction' logically involves join and that other factors such as key constraints hide various problems, eg., if the apparently consistent result of the restriction are referenced by other statements, the original key constraints may no longer apply. SQL users might say that isn't a big problem because that language makes it fairly hard to set up the situation. That is different from saying that such situations don't arise 'in reality'.
From what I've seen, SQL discards much of the available theory on the grounds that early implementors couldn't see how to respect all aspects. The small theory is replaced with blow-by-blow commentary for all the special cases. It's pretty clear that many of those people weren't able to distinguish the new theory from their previous experience, eg., Jim Gray couched most of his writing about db in terms of operating systems. They were all bright people, no doubt earnest too, but today we have more experience. Received on Sat Aug 15 2009 - 08:54:59 CDT