Обсуждение: Are sub-select error suppressed?
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? Best Regards, sidster -- They who would sacrifice freedom for security will have neither. -Ben Franklin Working example (with comments) follows: <example> <seen_on> <postgresql version="7.1.2" /> <postgresql version="7.1.3" /> <comments> I don't yet have access to a newer PostgreSQL build. </comments> </seen_on> <sql> begin; create table ttab_title ( title_id int4 primary key, can_delete bool ); create sequence tseq_title_id; insert into ttab_title values ( nextval( 'tseq_title_id' ), false ); insert into ttab_title values ( nextval( 'tseq_title_id' ), false ); insert into ttab_title values ( nextval( 'tseq_title_id' ), true ); -- rm able insert into ttab_title values ( nextval( 'tseq_title_id' ), true ); -- rm able insert into ttab_title values ( nextval( 'tseq_title_id' ), true ); -- rm able insert into ttab_title values ( nextval( 'tseq_title_id' ), false ); insert into ttab_title values ( nextval( 'tseq_title_id' ), false ); insert into ttab_title values ( nextval( 'tseq_title_id' ), false ); insert into ttab_title values ( nextval( 'tseq_title_id' ), false ); create view tview_title as select ttab_title.title_id as title_number from ttab_title where ttab_title.can_delete = true ; -- -- Notice thecolumn/field rename from title_id to title_number create table ttab_title_selection ( title_id int4 references ttab_title( title_id ), ranking int4 -- some otherfields ... ); create sequence tseq_title_rank; insert into ttab_title_selection select ttab_title.title_id, nextval( 'tseq_title_rank' ) from ttab_title; end; -- Now lets look at this delete statement. delete from ttab_title_selectionwhere title_id in ( select title_id from tview_title ); -- -- Noticehow instead of title_number we made the mistake and used -- title_id. -- -- We intended to only delete titles withids: 3, 4 and 5 but this -- delete statement deletes all 9 titles! -- Drop statements for clean up /* drop table ttab_title_selection; drop sequence tseq_title_rank; drop view tview_title; drop sequence tseq_title_id; drop table ttab_title; */ -- -- Local variables: -- c-basic-offset: 2 -- indent-tabs-mode: nil -- End: -- -- ex: ai et sw=2 ts=2 </sql> </example>
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
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).
> 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). >> 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). Ah - of course. Otherwise you couldn't do a subselect where foo=outer_foo. It tries to bind within the subselect, fails, then binds to the outer clause. Obvious now Stephan's pointed it out. Also reminds me why I like table aliases for any complicated queries. - Richard Huxton
On Tue, 26 Nov 2002 dev@archonet.com wrote: > > 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). > > >> 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). > > Ah - of course. Otherwise you couldn't do a subselect where foo=outer_foo. > It tries to bind within the subselect, fails, then binds to the outer > clause. > > Obvious now Stephan's pointed it out. Also reminds me why I like table > aliases for any complicated queries. Yeah, they could have (or at least if they did I couldn't find it this morning) required at least table qualifying outer references. That would have let the same functionality at the cost of only a few extra characters while being more obvious. It'd mean that you'd have to table alias things in subselects where you wanted to get to the same tablename in a higher scope, but it wouldn't have been that bad.