Thread

1. exists <==> not exists ???

   Christian Breimann <chr@math.uni-muenster.de> — 2001-05-10T08:25:10Z

   Hello,
   
   I think, that I've found one bug in PostgreSQL 7.1 on
   sparc-sun-Solaris2.7, compilied by GCC 2.7.2.3:
   
   
   When executing a SQL-Query with a "where not exists (...)" Statement,
   the result is wrong. It's the same result as I execute "where exists
   (...)" This mustn't be true.
   
   Is this a known bug?
   
   Greetings
   
   Christian Breimann
   
   -- 
   Christian Breimann
   chr@math.uni-muenster.de       Institut fuer Informatik
   Tel.: (+49) 251 - 83 38442     Westfaelische Wilhelms-Universitaet
   FAX.: (+49) 251 - 83 33755     Einsteinstr. 62, 48149 Muenster, Germany
   
   

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

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-05-11T13:35:00Z

   Christian Breimann <chr@math.uni-muenster.de> writes:
   > When executing a SQL-Query with a "where not exists (...)" Statement,
   > the result is wrong. It's the same result as I execute "where exists
   > (...)" This mustn't be true.
   
   This is a completely unhelpful bug report.  We need sufficient details
   to let us reproduce the problem.
   
   			regards, tom lane
   
   

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

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-05-11T18:52:17Z

   Christian Breimann <chr@math.uni-muenster.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