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
