Re: NULLs

From: Hugo Kornelis <hugo_at_perFact.REMOVETHIS.info.INVALID>
Date: Tue, 08 Jan 2008 00:12:02 +0100
Message-ID: <uhb5o3ph13888idlb4c57eeqqgebjvk5u2_at_4ax.com>


On Thu, 3 Jan 2008 15:23:51 -0800 (PST), TroyK wrote:

>On Jan 3, 3:55 pm, Hugo Kornelis
><h..._at_perFact.REMOVETHIS.info.INVALID> wrote:

>> * "List all employees known by the HR department to be aged 40 and
>> above" will exclude employees with no age on file, just as "SELECT name
>> FREM employees WHERE age > 40" will exclude employees with age NULL.
>>
>
><snip>
>
>>
>> Best, Hugo
>
>Was just discussing a similar example with colleagues today. Let's
>introduce a hypothetical business rule: all employees must be over 40
>years old (with the age column still nullable).
>
>What would you expect the result of the SELECT statement to return in
>that case?
>
>TroyK

Hi Troy,

Interesting point, because it touches on the limits of what computers can achieve, and forces us to make a decision.

A human worker would of course list all employees in such a case (unless he knows something about the HR department not following the rules). And I'm sure that a database could easily be programmed to inspect the CHECK constraint and then return all employees. But do we want that?

What if the constraint is not that all employees must be over 40 years old, but that all employees must be born before 1970. Employees with no age on file are currently not known to be over 40 years old - but two years from now they will. Should they then suddenly be all included on the report? Should the computer be smart enough to inspect this CHECK constraint and draw conclusions from it? Can that even be done?

And even if that can be done, we'd still have only a partial solution. What if I change the constraint to "all employees must have graduated Harvard and have at least 25 years of experience at other companies". I can still infer that all employees are over 40 from this constraint, and so can you - but can you teach a computer program to do *that*? And even if you can, are you sure that you can tackle each other wacky example I can dream up?

At present (and, IMHO, for many years to come), computer programs are not able to unravel all tautologies. So we either settle for not attempting to unravel tautologies at all, placing the burden firmly on the developer to make sure to remove tautologies from their queries. Or we choose to implement as much tautology removal as we can, knowing full well that we'll never get the job done completely. My preference is the former, because in that case, I at least know where I stand and am able to predict the outcome of a query without first having to have a full understanding of exactly what tautologies are and are not detected by the current version and service pack of the currently employed database.

So to answer your question - I would expect the result of the SELECT statement to omit the employees with no age recorded. And I would also expect the developer who wrote the query to be discontinued, or at least invited for a harsh conversation.

Best, Hugo Received on Tue Jan 08 2008 - 00:12:02 CET

Original text of this message