Re: NULLs: theoretical problems?

"Bob Badour" <bbadour_at_pei.sympatico.ca> wrote in message news:46c7201e$0$4038$9a566e8b_at_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
> ;
>

How? You haven't specified keys for A and B, so the assumption must be that there can be multiple rows where blah = 1 and multiple rows where clam = 2. In addition, due to the fact that the domains are the same (or at least have the same supertype, int), the query is completely valid, and returns neither a tautology nor 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.
>

How can a value that is omitted 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 - 19:01:13 CEST