Description of problem:
create table a (v int); create table b (w int); insert into a values(1); insert
into b values(2);
select v from a where a.v NOT IN (SELECT w FROM b);
will print two rows but should only one.
Version-Release number of selected component (if applicable):
Steps to Reproduce:
1. As above.
Strangely enough, fresh install of postgresql-8.1.4-1.1 as in FC6 is unaffected.
[ retrieves eyebrows from ceiling... ] What you describe would be an incredibly fundamental breakage, and
it's not been reported by anyone else. What client code are you seeing this in?
If it's not a client-side bug, I wonder whether your test database might include a child table of "a" that is
providing the extra row.
Well, I am suprised, too. The problem was originally in a much more complex
program, and its appearance was not connected with the postgresql upgrade in
June (or whenever you released it). I decided to report it since it could be
reproduced with much simpler queries as above and I thought it would be useful
to keep track of it. I just hope it's not connected to some data corruption, or
something similar. This problem can be reproduced with psql command line tool. I
can try running these queries on another database. The table names were chosen
at random and with no connection to the original layout.
I am trying to reproduce it again now - and the problem is gone! There was no
change to the database that I know of, not even the server was restarted (but I
did it couple of times when I originally debugged the problem). I am tempted to
close this report as a NOTABUG...
I think these days we are using CANTFIX for problems that can't be reproduced,
so I'm going to close the report that way. Please reopen if you are able to
gather more info.