Re: NULLs

On Jan 4, 7:55 am, Hugo Kornelis
<h..._at_perFact.REMOVETHIS.info.INVALID> wrote:
> 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?

I don't know anywhere near enough about SQL to answer that.

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

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.

> * "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.

> 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! Received on Fri Jan 04 2008 - 02:21:12 CET