Re: Examples of SQL anomalies?

Philipp Post <Post.Philipp_at_googlemail.com> writes:

>> Nulls are often but not exclusively used to mean unknown value. Failure to distinguish between different uses of null is the basis of most of these, rather pointless, arguments. <

> Not that I would expect a change in the standard for these things, but
> wouldn't it be better to have two different markers for "no data" such
> as:

Better in the sense of more expressive, but probably less usable, given the level of understanding of typical practitioners. More below.

> - NULL for "unknown yet, but should have a value lateron" which
> behaves like 1 + NULL + 1 = NULL
> - N/A for "not applicable" what will be omitted in any expression and
> function such as 1 + N/A + 1 = 2

Good to see that someone here understands this distinction. The former behaviour is sometimes known as null contagion, or more formally by saying that the + operator is strict with respect to undefined values.

The latter behaviour effectively consists in using a default value, here 0, to replace any null argument.

The "not applicable" case is itself separable into two cases: an attribute may be "always inapplicable" to an entity, ie never defined, e.g. the social security number of a corporation, or "optional", sometimes defined and sometimes not, e.g. a person's spouse.

> Normally a database should be designed a way N/A can not occurr (the
> column should not be there), but having this would give the
> possibility to use it where needed and to clear up the inconsistent
> behaviour, I think.

Yes, at the cost of extra complexity in the language definition, the behaviour of operators, etc. Given the ease with which denizens of this group get confused about the basics, I'm not sure it's worth the bother, except perhaps in specialised knowledge representation languages. Received on Tue Jul 08 2008 - 13:34:22 CEST