Re: When is a record NULL?

On Jul 25, 2009, at 9:42 AM, Kevin Grittner wrote:

> I know that Codd was insistent that any relation (which included the
> result of any query) which could contain duplicate rows should be
> called a "corrupted relation". (In fact, in one of his books I think
> he averaged a comment on this point about once every two pages.) So I
> shudder to think what his reaction would be to a relation with a row
> which contained no values. I have a really hard time figuring out
> what useful information such a row could represent.

I agree that it's pathological, but it's clearly allowed by SQL, so we
need to be able to deal with it effectively. Intuitively would be
nice, but effectively will do.

Consider:

CREATE TABLE peeps (
name TEXT NOT NULL,
dob date,
ssn text,
active boolean NOT NULL DEFAULT true
);

INSERT INTO peeps
VALUES ('Tom', '1963-03-23', '123-45-6789', true),
('Damian', NULL, NULL, true),
('Larry', NULL, '932-45-3456', true),
('Bruce', '1965-12-31', NULL, true);

% SELECT dob, ssn from peeps where active;
dob | ssn
------------+-------------
1963-03-23 | 123-45-6789
[null] | [null]
[null] | 932-45-3456
1965-12-31 | [null]

Useless perhaps, but it's gonna happen, and someone may even have a
reason for it. Until such time as NULLs are killed off, we need to be
able to deal with SQL's pathologies.

Best,

David