Skip site navigation (1) Skip section navigation (2)

Re: exists <==> not exists ???

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Christian Breimann <chr(at)math(dot)uni-muenster(dot)de>
Cc: pgsql-bugs(at)postgreSQL(dot)org
Subject: Re: exists <==> not exists ???
Date: 2001-05-11 18:52:17
Message-ID: 6394.989607137@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
Christian Breimann <chr(at)math(dot)uni-muenster(dot)de> writes:
> select distinct S.customer_name 
> 	from depositor as S
> 	where not exists(
> 	(select branch_name 
> 		from branch 
> 		where branch_city='Brooklyn')
> 	except
> 	(select R.branch_name 
> 		from depositor as T, account as R 
> 		where T.account_number = R.account_number
> 		and S.customer_name = T.customer_name)
> );

Ah.  In current sources I get correct (I think) results:

    customer_name
----------------------
 Johnson
(1 row)

    customer_name
----------------------
 Hayes
 Jones
 Lindsay
 Smith
 Turner
(5 rows)

Your query is running into the same bug someone else discovered a few
days ago:

2001-05-08 15:47  tgl

	* src/backend/executor/: nodeAppend.c, nodeSubqueryscan.c
	(REL7_1_STABLE), nodeAppend.c, nodeSubqueryscan.c: Append and
	SubqueryScan nodes were not passing changed-parameter signals down
	to their children, leading to misbehavior if they had any children
	that paid attention to chgParam (most plan node types don't). 
	Append's bug has been there a long time, but nobody had noticed
	because it used to be difficult to create a query where an Append
	would be used below the top level of a plan; so there were never
	any parameters getting passed down.  SubqueryScan is new in 7.1 ...
	and I'd modeled its behavior on Append :-(

The fix will be in 7.1.2 (due out real soon now), or you can pull
current REL7_1_STABLE sources from our CVS server if you are in a hurry.

Thanks for the report!

			regards, tom lane

In response to

pgsql-bugs by date

Next:From: Peter EisentrautDate: 2001-05-11 21:20:22
Subject: Re: instalation problems!
Previous:From: Stephan SzaboDate: 2001-05-11 18:08:22
Subject: Re: Terrible perfomance during nested "... where x in (select ...)" operator

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group