Date: Tue, 26 Nov 2002 01:43:02 -0800
From: patrick <patrick+pgsql@boxsoft.com>
To: pgsql-sql@postgresql.org
Subject: Are sub-select error suppressed?
Message-ID: <20021126014302.L3699@3eye.boxsoft.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.2.5.1i

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 the column/field rename from title_id to title_number


create table
ttab_title_selection
  (
  title_id          int4      references ttab_title( title_id ),
  ranking           int4
  -- some other fields ...
  );

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_selection
 where  title_id in ( select  title_id
                        from  tview_title );
  --
  -- Notice how instead of title_number we made the mistake and used
  -- title_id.
  --
  -- We intended to only delete titles with ids: 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>