Bug 217305

Summary:	Incorrect handling of nested queries.
Product:	[Fedora] Fedora	Reporter:	Pawel Salek <pawsa>
Component:	postgresql	Assignee:	Tom Lane <tgl>
Status:	CLOSED CANTFIX	QA Contact:	David Lawrence <dkl>
Severity:	medium	Docs Contact:
Priority:	medium
Version:	5	CC:	hhorak
Target Milestone:	---
Target Release:	---
Hardware:	All
OS:	Linux
Whiteboard:
Fixed In Version:		Doc Type:	Bug Fix
Doc Text:		Story Points:	---
Clone Of:		Environment:
Last Closed:	2006-11-28 17:27:21 UTC	Type:	---
Regression:	---	Mount Type:	---
Documentation:	---	CRM:
Verified Versions:		Category:	---
oVirt Team:	---	RHEL 7.3 requirements from Atomic Host:
Cloudforms Team:	---	Target Upstream Version:
Embargoed:

Description Pawel Salek 2006-11-26 20:43:07 UTC

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):
postgresql-8.1.4-1.FC5.1

How reproducible:
Always.

Steps to Reproduce:
1. As above.

Additional info:
Strangely enough, fresh install of postgresql-8.1.4-1.1 as in FC6 is unaffected.

Comment 1 Tom Lane 2006-11-27 02:59:57 UTC

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

Comment 2 Pawel Salek 2006-11-27 07:16:49 UTC

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

Comment 3 Tom Lane 2006-11-28 17:27:21 UTC

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.