Bill (pstscrpt) wrote in computerscience,

  • Music:

Null, etc.

For a long time, I've wanted built-in language support for three distinct kinds of Null:

Unknown (what Null is treated as by SQL, now)
Not Applicable (like unknown in practice, but conceptually different -- outer joins would return these)
Absent (More like Nil in some languages than SQL's Null -- checks for equality would return true or false)

Absent for foreign key references is the really important one of these because it's an important thing to record, and databases currently have to chose between using Null (fighting SQL's built-in support for treating it as unknown, and muddying the distinction between that and things that really are unknown), using zero and leaving off foreign keys (just plain wrong, but unfortunately common) and using zero with matching zero-rows in the linked table (conceptually the best, but I've never seen it in practice, and can be misleading if you don't include "<> 0" in all your queries).

I was doing some PL/SQL math programming just now (billing, with thresholds stored in a table), and it occurred to me that infinity also really belongs on the Null spectrum, as it has similar propagation rules:
(Infinity > ANumber) = true
(Infinity < ANumber) = false
(Infinity > Infinity) = unknown
(Infinity > -Infinity) = true
(Infinity = ANumber) = unknown debatable, I know
(Infinity - ANumber) = Infinity
(Infinity - Infinity) = unknown
(Infinity + Infinity) = Infinity, etc.

Of course, any Null support at all in languages other than SQL would be a good start. Nil is a big part of what I like about Ruby, and I miss VB 6's variant type because it could store Nulls fairly smoothly when they came back from a database.
  • Post a new comment


    default userpic