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
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