Re: [HACKERS] Severe SUBSELECT bug in 6.5 CVS

> > > It appears to me that to follow the SQL spec, a NULL found in a.i
> > > should return NULL for both IN and NOT IN (the spec appears to say that
> > > the result of IN is "unknown" in that case, and we are using NULL to
> > > represent "unknown"):
> >
> > I would be interested to see how other databases handle this.
> >
> 
> ----------------------------------------------
> create table a (i int, aa char(10));
> create table b (i int, bb char(10));
> insert into a values(1, 'foo');
> insert into b values(null, 'bar');
> select * from a where i not in (select i from b);
> -----------------------------------------------
> I tried the above script on:
> 
>     Informix-SE
>     Oracle8
> 
> and both of them return 0 rows, like PostgreSQL.
> 

Yes, this is how I remembered Informix doing it.  Returning a NULL in
the subselect does not match anything, so hopefully we don't have a bug.

--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026