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 ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
