projection with nulls

From: paul c <toledobysea_at_ac.ooyah>
Date: Sun, 16 Mar 2008 17:34:14 GMT
Message-ID: <qScDj.90937$pM4.36949_at_pd7urf1no>

(Just my stab at moving from the recent topics towards more basic questions, sorry if there's not much newspeak in it):

Say you have R with (S)upplier, (P)art, (Q)uantity:

- -
a 2
b _

with predicate: Supplier S supplies Part P. The underscore in the second 'row' stands for 'NULL', ie., SQL's null.

For argument's sake, say the first tuple means that 'if Supplier a carries any parts, it carries Part 2' and the second tuple might mean that 'if Supplier b carries any part, we do not know what that part's number is'. Not everybody might agree that this meaning is essentially the same as the predicate given but as long as the meaning or predicate is the same for all tuples, picking one or the other shouldn't matter to the dbms.

Maybe not a realistic example, it's just for argument's sake (I don't think it is illegal as far as the SQL standard is concerned).

It's always seemed to me that the projection R{S}, without any other operands, ought to return only {{S, a}}, ie., 'what suppliers supply followedsome part', just one tuple in this case which is the same as saying 'what suppliers supply some part if they supply any parts'. That is, if one allows nulls (which I'm not advocating).

I gather that most or maybe all SQL products return {{S a}, {S b}}, ie., two tuples. I don't see how Supplier b should be included because it's not known (in this example) whether Supplier b supplies any parts. Maybe somebody wants to answer what should happen if the projection is R{P} instead?

Secondly, it seems that traditional logic demands the closed-world-assumption (CWA) in order to support negation. If the domain of part numbers were the set {1,2}, the complement of R above would include the tuples {S b, P 1} and {S b, P 2}. In other words, there aren't any parts that Supplier b supplies. Does this mean that SQL gives wrong answers if the CWA is assumed?

I guess one could argue that this is a kind of trick predicate but my attitude towards that is so what? We don't expect a logic engine to be aware of predicates.

As far as conventional projection is concerned, it seems that R is overloaded in the sense that when it allows nulls it also allows tuples that aren't opaque/blunt enough to project against, ie., it's not very clear whether the two tuples always share the same predicate. Makes me wonder if a P attribute that is set-valued would be a less-intricate way to dispense with nulls. Sometimes I even wonder whether conventional projection doesn't have enough operands! Received on Sun Mar 16 2008 - 18:34:14 CET

Original text of this message