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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026