Content-Type: text/plain;
  charset="iso-8859-1"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: patrick <patrick+pgsql@boxsoft.com>, pgsql-sql@postgresql.org
Subject: Re: Are sub-select error suppressed?
Date: Tue, 26 Nov 2002 10:41:50 +0000
User-Agent: KMail/1.4.3
References: <20021126014302.L3699@3eye.boxsoft.com>
In-Reply-To: <20021126014302.L3699@3eye.boxsoft.com>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200211261041.50766.dev@archonet.com>

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'v=
e=20
got (note - not most recent). I don't think it's in the subselect itself -=
=20
what's happening is when you do

DELETE FROM ttab_title_selection=20
WHERE tsel_id IN (SELECT xxx FROM tview_title);

the xxx is binding to the outside query (the DELETE). If you change your=20
definition of ttab_title_selection to use tsel_id rather than title_id this=
=20
will be clearer. You can get the same with ranking:

DELETE FROM ttab_title_selection=20
WHERE ranking IN (SELECT ranking FROM tview_title);

I'm guessing it gets parsed down to:

DELETE FROM ttab_title_selection WHERE ranking IN (ranking);

which of course matches everything.


--=20
  Richard Huxton