Horton

Thread

  1. Re: [HACKERS] Severe SUBSELECT bug in 6.5 CVS

    Tom Lane <tgl@sss.pgh.pa.us> — 1999-06-26T17:22:50Z

    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"):
            c) If the implied <comparison predicate> is true for at least
              one row RT in T, then "R <comp op> <some> T" is true.
            d) If T is empty or if the implied <comparison predicate> is
              false for every row RT in T, then "R <comp op> <some> T" is
              false.
            e) If "R <comp op> <quantifier> T" is neither true nor false,
              then it is unknown.
(recall that null compared to anything yields unknown, not false).
I don't believe we currently have that behavior, but it seems
reasonable.

More subtly, it looks like for a non-null a.i, IN should return TRUE
if there is a match in b, even if b also contains nulls (fine), but if
there is no match in b and b contains nulls then the spec seems to
require NULL, *not* FALSE, to be returned!  I'm not sure I like that
conclusion...

In the meantime, a workaround for Chris is to use NOT (i IN ...) instead
of NOT IN.  That should work as he expects, at least for nulls in b.

			regards, tom lane

  2. Re: [HACKERS] Severe SUBSELECT bug in 6.5 CVS

    Bruce Momjian <maillist@candle.pha.pa.us> — 1999-06-26T18:55:03Z

    > 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.

-- 
  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, Pennsylvania 19026

  3. Re: [HACKERS] Severe SUBSELECT bug in 6.5 CVS

    Jose Soares <jose@sferacarta.com> — 1999-06-28T12:52:42Z

    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'

  4. Re: [HACKERS] Severe SUBSELECT bug in 6.5 CVS

    Bruce Momjian <maillist@candle.pha.pa.us> — 1999-06-28T18:39:28Z

    > > > 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, Pennsylvania 19026

  5. Re: [HACKERS] Severe SUBSELECT bug in 6.5 CVS

    Chris Bitmead <cbitmead@ozemail.com.au> — 1999-06-28T23:51:40Z

    Bruce Momjian wrote:

> >     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.

What is the general policy? Follow the SQL standard, or do what all the
other databases do?

  6. CVS, Java etc

    Chris Bitmead <chris@tech.com.au> — 1999-07-06T12:24:36Z

    Several problems - Java and CVS.

CVS has stopped working for me. I get the error...
Fatal error, aborting.
: no such user

I've tried logging in and out to no avail. It was working for me before.
As an aside I did an strace cvs update and saw "I love you" in the
trace. (??!)

Java - I tried to build JDBC to teach myself Java. I'm getting the
following build errors. While I'm only teaching myself Java the brackets
don't even seem to match. I'm using Java 1.2 Linux.

javac postgresql/Driver.java
postgresql/Driver.java:107: Identifier expected.
    } catch(PSQLException(ex1) {
                              ^
postgresql/Driver.java:111: 'catch' without 'try'.
    } catch(Exception ex2) {
      ^
2 errors
make[1]: *** [postgresql/Driver.class] Error 1
make[1]: Leaving directory
`/usr/local/src/postgres-cvs/pgsql/src/interfaces/jdbc'
make: *** [all] Error 2

  7. Re: [HACKERS] CVS, Java etc

    Mark Hollomon <mhh@nortelnetworks.com> — 1999-07-07T12:10:27Z

    Chris Bitmead wrote:
> 
> 
> CVS has stopped working for me. I get the error...
> Fatal error, aborting.
> : no such user

I have been seeing this as well.
I started seeing this just after doing a restore of my hard drive,
so I thought it was just me. Anybody got any clues?

-- 

Mark Hollomon
mhh@nortelnetworks.com

  8. Re: [HACKERS] CVS, Java etc

    Clark C. Evans <clark.evans@manhattanproject.com> — 1999-07-07T12:25:36Z

    Mark Hollomon wrote:
> 
> Chris Bitmead wrote:
> >
> >
> > CVS has stopped working for me. I get the error...
> > Fatal error, aborting.
> > : no such user
> 
> I have been seeing this as well.
> I started seeing this just after doing a restore of my hard drive,
> so I thought it was just me. Anybody got any clues?

It is failing for me as well.  Sorry, no clue.

Clark