Date: Tue, 26 Nov 2002 07:52:20 -0800 (PST)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Richard Huxton <dev@archonet.com>
Cc: patrick <patrick+pgsql@boxsoft.com>, <pgsql-sql@postgresql.org>
Subject: Re: Are sub-select error suppressed?
In-Reply-To: <200211261041.50766.dev@archonet.com>
Message-ID: <20021126075031.N77510-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Tue, 26 Nov 2002, Richard Huxton wrote:

> On Tuesday 26 Nov 2002 9:43 am, patrick wrote:
> > Greetings,
> >
> > I'm not sure what the correct behavior is here but the observed
> > behavior seems "wrong" (or at least undesirable).
> >
> > I have a few tables and a view on one of the tables selecting
> > entries that may be purged.
> >
> > My delete statement uses the view to delete data from one of the
> > tables.  Like so:
> >
> >   delete from tab1 where id1 in ( select id from view1 );
> >
> > Assume that the view doesn't have a field named "id".  The select
> > statement alone would cause an error.  However, in this context it
> > doesn't and the delete statement deletes everything from tab1.
> >
> > Is this a bug in PostgreSQL or an "As Designed" feature?
>
> Don't look right to me, and I still see it here in 7.2 and the 7.3 beta I've
> got (note - not most recent). I don't think it's in the subselect itself -
> what's happening is when you do

I think it's standard behavior.  The column reference is an outer
reference I believe, IIRC all the names from the outer query are in scope
in the subselect (although if there's an equivalent name in the subselect
from tables you'd have to qualify it).