Are sub-select error suppressed? - Mailing list pgsql-sql

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>