Обсуждение: exists <==> not exists ???

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

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

How do I stop this information from coming to me?

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, May 11, 2001 9:35 AM
To: Christian Breimann
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] exists <==> not exists ???


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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

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