Re: [HACKERS] Severe SUBSELECT bug in 6.5 CVS

Bruce Momjian ha scritto:

> > Chris Bitmead <chris.bitmead@bigfoot.com> writes:
> > > httpd=> select * from a where i not in (select i from b);
> > > [ returns nothing if b contains any nulls in column i ]
> >
> > Of course, what's happening here is that the NOT IN is (in effect)
> > transformed to
> >       a.i != b.i1 AND a.i != b.i2 AND a.i != b.i3 ...
> > (writing i1, i2, ... for the values extracted from b).  Then, since
> > any comparison involving NULL returns FALSE, the where-clause fails
> > for all values of a.i.
> >
> > I think this actually is a bug, not because it's wrong for "x != NULL"
> > to be false, but because the SQL spec defines "a NOT IN t" as equivalent
> > to "NOT (a IN t)".  IN is implemented as
> >       a.i = b.i1 OR a.i = b.i2 OR a.i = b.i3 ...
> > which will effectively ignore nulls in b --- it'll return true if and
> > only if a.i matches one of the non-null values in b.  Our implementation
> > fails to maintain the equivalence that NOT IN is the negation of this.
> >
> > 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.

______________________________________________________________
PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Jose'