From: Richard Huxton <dev@archonet.com>
To: patrick <patrick+pgsql@boxsoft.com>
To: pgsql-sql@postgresql.org
Subject: Re: Are sub-select error suppressed?
Date: Tue, 26 Nov 2002 10:41:50 +0000
Message-ID: <200211261041.50766.dev@archonet.com> (raw)
In-Reply-To: <20021126014302.L3699@3eye.boxsoft.com>
References: <20021126014302.L3699@3eye.boxsoft.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've
got (note - not most recent). I don't think it's in the subselect itself -
what's happening is when you do
DELETE FROM ttab_title_selection
WHERE tsel_id IN (SELECT xxx FROM tview_title);
the xxx is binding to the outside query (the DELETE). If you change your
definition of ttab_title_selection to use tsel_id rather than title_id this
will be clearer. You can get the same with ranking:
DELETE FROM ttab_title_selection
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.
--
Richard Huxton
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, patrick+pgsql@boxsoft.com
Subject: Re: Are sub-select error suppressed?
In-Reply-To: <200211261041.50766.dev@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox