Re: How to cope with missing values - NULLS?

"Paul Vernon" <paul.vernon_at_ukk.ibmm.comm> wrote in message news:<b7ur3i$2abi$1_at_gazette.almaden.ibm.com>...
> "Peter Koch Larsen" <pkl_at_mailme.dk> wrote in message
> news:61c84197.0304181445.46842baa_at_posting.google.com...
> > [snip] Let us say that we have
> > defined a view as an outer join: columns col1 and col2 are "common,
> > but col3 and col4 might be missing. Now let's query that view - we are
> > only interested in tuples, where col4 is larger than 10: SQL gives us
> > something like
> >
> > SELECT * FROM ojview WHERE col4 > 10
> >
> > How would you phrase such a query in your (hypothetical) relational
> > system?
>
> Well col4 would not be of type say INTEGER, but of type say INTEGERuUNK and I
> would not expect the ">" operator to be defined for type INTEGERuUNK.

Okay. But if you do so, are you not simply reintroducing the null-type through the back-door, giving it a new name and some slightly new semantics?

>
> So I guess the query would something like be
>
> SELECT * FROM ojview WHERE col4 <> UNK
> and (IF IS_INTEGER(col4) THEN CAST_DOWN_AS_INTEGER(col4) > 10 ELSE TRUE)

I believe you intend to mean
.....THEN CAST_DOWN_AS_INTEGER(col4) > 10 ELSE FALSE)

>
> now I'll admit that my syntax above is a little long winded, and better
> suggestions are encouraged, but it conveys the idea (I hope) and advised run
> time errors from the CAST_DOWN. Looking at Date & Darwen's very strong
> suggestion #8 : Special default values, they don't have a syntax suggestion
> for the above, but do have many other useful observations on this topic.
>
My reading of TTM suggests to me that creating this extended type is their general solutions.

>
> > My personal conclusion is that we are stuck with NULLS - if not in the
> > SQL sense then at least in the sense that we are to have some kind of
> > sum type. And if we are stuck with such a type, then the only sensible
> > thing is to have it standardised by the DBMS - how else are we going
> > to let the DBMS perform the outer join by itself?
> >
>
> Using type inheritance, some UNK value could be 'standardised' for outer joins
> where the user does not specify their own defaults. In otherwords you *could*
> define a 'union' type that includes the UNK value for every other scalar type
> in your system. E.g. BOOLEAN_UKN, STRING_UKN, ... . Again I refer you to The
> Third Manifesto
>
> The question just then remains whether such an UKN value should be
> 'standardised'. Well if we can all agree what the meaning of UNK is, and that
> it means the same for every application and for every use of the term, like
> say the value 42 has an agreed meaning, then I would be happy to standardise
> it.

And we have nulls back - well.. at least sort of, do we not?

> However, I don't think that such an agreed meaning could be arrived at. At the
> very least I would suggest that a database schema that includes some
> generically defined UNK value is not a schema that has very well designed.

Precisely as it is with NULLs today.
>
> Regards
> Paul Vernon
> Business Intelligence, IBM Global Services

Kind regards
Peter Koch Larsen Received on Mon Apr 21 2003 - 17:17:04 CEST