Re: NULLs

On Thu, 3 Jan 2008 17:21:12 -0800 (PST), David BL wrote:

>On Jan 4, 7:55 am, Hugo Kornelis
><h..._at_perFact.REMOVETHIS.info.INVALID> wrote:
(snip)
>> Let's look at some examples that are often used to illustrate the
>> evilness of 3VL: aggregation and tautologies:
>>
>> * "Return the average of all ages known to HR department" will return
>> the exact same value as AVG(age) currently does (i.e. ages not known to
>> HR department are disregarded by the former, and NULL ages are
>> disregarded by the latter).
>
>I can't see any problem there. You can take the average over any set
>of numeric values as long as you are clear what the set actually is.

Hi David,

Neither can I. But aggregate functions are one of the two arguments I most often hear used against NULL - people claim that NULLs in SQL are evil because "SELECT AVG(age) FROM employees;" does not return the average employee age.

Next time someone asks me to explain this, I'll use your explanation that the column age doesn't store data from "<Employee> is <age> years old" propositions, but from "It is known to the HR department that <employee> is <age> years old" propositions. And that hence AVG(age) correctly calculates the average of all ages known to the HR department.

>> * "Is it known to HR department that JJ is older than Mary or that JJ is
>> not older than Mary?" - neither is known, so this would evaluate to
>> False OR False = False. While technicallly possibly correct, I'm quite
>> sure that this result will enocunter the same amount of opposition as
>> the Unknown result that SQL will produce on (simplified) "32 > NULL OR
>> NOT (32 > NULL)" [which of coourse is again treated the same as False].
>
>The following are quite different queries
>
>A = (It is known to HR department that JJ is older than Mary)
> OR
> (It is known to HR department that JJ is not older than Mary)
>
>
>B = It is known to HR department that
> ( (JJ is older than Mary) OR
> (JJ is not older than Mary) )
>
>
>A = false and B = true. Only B is the tautology.
>
>In practice the RM/RA will answer A instead of B. The answer A =
>false is ok because there is no tautology in A.

And yet, the second argument usually made against NULL is that employees with the age column set to NULL are not included in the results of the query "SELECT employee FROM employees WHERE age > 40 OR age <= 40;"

>> So, while you technically succeeded in answering the questions without
>> using 3VL, this didn't solve the problems usually associated with NULL
>> and 3VL. Instead, you found a way to introduce these problems into 2VL.
>
>I don't see any problems!

Great. Neither do I. So, what problems does this leave for NULL and 3VL?

Best, Hugo Received on Tue Jan 08 2008 - 00:21:16 CET