pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAre sub-select error suppressed?
5+ messages / 4 participants
[nested] [flat]
* Are sub-select error suppressed?
@ 2002-11-26 09:43 patrick <patrick+pgsql@boxsoft.com>
0 siblings, 1 reply; 5+ messages in thread
From: patrick @ 2002-11-26 09:43 UTC (permalink / raw)
To: 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 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>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Are sub-select error suppressed?
@ 2002-11-26 10:41 Richard Huxton <dev@archonet.com>
parent: patrick <patrick+pgsql@boxsoft.com>
0 siblings, 1 reply; 5+ messages in thread
From: Richard Huxton @ 2002-11-26 10:41 UTC (permalink / raw)
To: patrick <patrick+pgsql@boxsoft.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Are sub-select error suppressed?
@ 2002-11-26 15:52 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 5+ messages in thread
From: Stephan Szabo @ 2002-11-26 15:52 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: patrick <patrick+pgsql@boxsoft.com>; pgsql-sql
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).
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Are sub-select error suppressed?
@ 2002-11-26 17:04 dev@archonet.com
parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 5+ messages in thread
From: dev@archonet.com @ 2002-11-26 17:04 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: Richard Huxton <dev@archonet.com>; patrick <patrick+pgsql@boxsoft.com>; pgsql-sql
> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Are sub-select error suppressed?
@ 2002-11-26 17:22 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: dev@archonet.com
0 siblings, 0 replies; 5+ messages in thread
From: Stephan Szabo @ 2002-11-26 17:22 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: patrick <patrick+pgsql@boxsoft.com>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2002-11-26 17:22 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-11-26 09:43 Are sub-select error suppressed? patrick <patrick+pgsql@boxsoft.com>
2002-11-26 10:41 ` Richard Huxton <dev@archonet.com>
2002-11-26 15:52 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-11-26 17:04 ` dev@archonet.com
2002-11-26 17:22 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox