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

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.
.

Relevant Pages

  • Re: NULLs: theoretical problems?
    ... with NULLs and joins, or NULLs and keys composed of more than one field, but ... The solution is of course to decompose a relation around its key so ... without any theoretical or logical flaws. ... Given the fact that it can generate much longer queries, ...
    (comp.databases.theory)
  • Re: WWW/Internet 2009: 2nd CFP until 21 September x
    ... The way I think of it is that every table with nulls in it is a ... If you can decompose the table into multiple ... There are some places where missing data does no harm, ... But information systems are not just methematics. ...
    (comp.databases.theory)
  • Re: WWW/Internet 2009: 2nd CFP until 21 September x
    ... 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. ... 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. ... An implicit suggestion here is that there is a way to determine which apps are 'simple' in some sense, eg., not capable of contradictions, other than by avoiding nulls. ...
    (comp.databases.theory)
  • Re: A new proof of the superiority of set oriented approaches: numerical/time serie linear interpola
    ... I've been willing to use NULLS in databases rather than ... decompose tables to eliminate the need for NULLS. ... even though that's what SQL did. ... As soon as one uses a marker of any kind, one deviates from using values and 2vl. ...
    (comp.databases.theory)
  • Re: Space required for an empty varchar field?
    ... >who don't understand how to deal with NULLS. ... as they'll be in the result set of an outer join. ... it gets unwieldy fast if you have multiplle NULLable columns. ... Thanks Hugo. ...
    (microsoft.public.sqlserver.programming)
Loading