Re: NULLs: theoretical problems?

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Sat, 18 Aug 2007 13:37:52 -0300
Message-ID: <46c7201e$0$4038$9a566e8b@news.aliant.net>

David Cressey wrote:

> "Bob Badour" <bbadour_at_pei.sympatico.ca> wrote in message
> news:46c6ddd4$0$4031$9a566e8b_at_news.aliant.net...
>

>>David Cressey wrote:

>

>>Tautologies (and contradictions) arise often when evaluating queries
>>against views.

>
> I don't understand this (although maybe I should). Could you give one or
> two examples?

create view C(x int, y int)
as select blah as x, yech as y
from A
where blah = 1
union
select clam as x, mussel as y
from B
where clam = 2
;

select *
from C
where x = 2 -- causes both a tautology and a contradiction ;

>>>In situations where I've observed other programmers having trouble with
>>>3-valued logic,  it nearly always boils down to using possibly omitted
>>>values in a WHERE clause.
>>
>>Don't you mean subordinate clauses in the WHERE clause? Like "or x is
>>null" ?

>
> I certainly don't mean "or x is null". Whenever I've seen this in the SQL
> written by colleagues it has turned out to be a case of misbegotten database
> design or misbegotten query design, or possibly misbegotten query
> requisites. Whenever I've seen the the dreaded "or x is null", it means
> that the query author is seeking to retrieve two or more different
> propositions in the same operation. That's the underlying source of the
> problem and the confusion with SQL in this regard. Or maybe I mean
> "predicates", I'm not sure.
>
> Correction to the above: It can also mean that the data base designer used
> the dreaded null to convey a message other than "no value here". In that
> case, the query author is the victim rather than the perpetrator.

Oh, I see what you were saying now. Instead, I would object: Since one cannot predict every ad hoc query, every attribute is potentially useful in a where clause, which necessarily means when the table allows NULL possibly omitted values will eventually appear in a where clause.

> Perhaps you know a query language where propositions (or maybe predicates)
> are expressed as such rather than cast into the expressions of operations on
> tables. If so, I'd venture to guess that the clumsiness of the attempt to
> retrieve two different kinds of facts in a single operation would stand out
> starkly in such a language.

It should require two relations and probably a union in the case you hypothesize.

>>I respectfully suggest it is pointless to argue that NULL is easy to use
>>after Date and Darwen have written several books worth of material
>>explaining all of the subtle gotchas. See the various _Writings...._

>
> books.
>
> You may be right about it being pointless to argue. I am going to continue
> to assert that, for at least one practitioner, they have been easy to manage
> compared to most of the other gotchas that have come down the pike in the
> last forty years of computing.
>
> I don't even know the list of all the gotchas that you reference. At this
> point, the only reason I would bother to learn the list is to win arguments.
> And I've already conceded that that's probably a lost cause.

The one that I usually start with is SUM(A) + SUM(B) != SUM(A+B) in the presence of NULL, but D&D have basically written entire books on the subject. Received on Sat Aug 18 2007 - 11:37:52 CDT