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
DEFAULTtrue );
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