Home -> Usenet -> comp.databases.theory -> projection with nulls

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:



R:


S P


- -


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