> -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of jose > Sent: Wednesday, May 22, 2002 9:38 AM > To: [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED] > Subject: [BUGS] in(NULL) > > > Hi all, > > I think I found a bug using the IN operator. > > > I have a table t populated as follow: > a | b > ---+---- > 1 | 11 > 2 | 22 > 3 | 33 > 4 | NULL > (4 rows) > select * from t where b in (11,22,NULL); > a | b > ---+---- > 1 | 11 > 2 | 22 > (2 rows) > select * from t where b not in (11,22,NULL); > a | b > ---+---- > (0 rows) > ----------------------------------------------- > I tried the same quesry in mysql and it give me > a different result. Who are right? > select * from t where b not in (11,22,NULL); > +---+---+ > | a | b | > +---+---+ > | 3 | 33| > +---+---+ > 1 row in set (0.00 sec)
As usual, we are right, of course. How can they be sure that any column isn't in (11,22,NULL), when they can't specific what the NULL value is? (More specifically: the logical result of 'a IN (11,22,NULL)' for a=10 is unknown, rather than false or true. Therefore, the WHERE clause fails (unknown values fail). For a=11 or 22, it is true. So, it works as expected for IN. For NOT IN, however, the result of 'a NOT IN (11,22,NULL) for a=11 is false. For a=10, it is unknown -- we don't know if the NULL is 10, therefore we can't say that it isn't. Therefore, for a NOT IN (xx,yy,NULL), all evaluations are either false or unknown). SQL specs are clear on this point, and its covered well in Celko's _SQL_for_Smarties_: NOT IN ( xx, yy, NULL) returns nothing. At least MySQL has a cute logo. And Monty's not bad, either. - J. Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton Knowledge Management & Technology Consultant ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
