Re: NULLs

On Fri, 28 Dec 2007 17:59:13 -0800 (PST), David BL wrote:

(snip)
>In the presence of missing information the query
>
> "List all employees aged 40 and above"
>
>cannot be answered at all. Instead the query should be
>
> "List all employees known by the HR department to be aged 40 and
>above"

(snip)

>In the case of the question
>
> "Is JJ older than Mary?"
>
>This should be
>
> "Is it known to HR department that JJ is older than Mary?"
>
>in which case the answer is no. See, no 3VL required.
>
>We can also ask whether it is known to HR department that JJ is not
>older than Mary". The answer is no as well; so we have been able to
>tell (using only efficient 2VL) that the DB doesn't know who it older.

Hi David,

Happy new year to you and all else still readin gthis thread.

You make some fair points. Thanks for that.

In a purely pragmatic sense, are there any situations where this way of looking at things produces different results than the traditional SQL approach? It doesn't in the case of the examples above:

• "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.
• "Is it known to HR department that JJ is older than Mary?" will result in "no", so that the ELSE part of a conditional statement is executed, just as "IF (SELECT age FROM employees WHERE name = 'JJ') > (SELECT age FROM employees WHERE name = 'Mary')" will evaluate to unknown, skip the conditional action and execute the ELSE action.

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).
• "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].

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.

Best, Hugo Received on Thu Jan 03 2008 - 23:55:54 CET