pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMisnamed field in subquery does not cause error when field name exists in parent query
3+ messages / 2 participants
[nested] [flat]
* Misnamed field in subquery does not cause error when field name exists in parent query
@ 2007-12-22 22:12 Rick Innis <rick.innis@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Rick Innis @ 2007-12-22 22:12 UTC (permalink / raw)
To: pgsql-sql
Wondering if this is actually a bug, before I file a bug report on it.
Consider this sequence:
=# create table public.test1 (id int);
CREATE TABLE
=# create table public.test2 (it int);
CREATE TABLE
=# select * from test1 where id in (select id from test2);
id
----
(0 rows)
Note that the select from test2, which references a non-existent
field, doesn't cause an error when that statement is used as a
subselect AND the non-existent field does exist in a table referenced
in the outer select.
This is particularly catastrophic when you replace 'select *' by
'delete', as illustrated below.
=# insert into test1 values(1);
INSERT 0 1
=# insert into test1 values(2);
INSERT 0 1
=# insert into test1 values(3);
INSERT 0 1
=# delete from test1 where id in (select id from test2);
DELETE 0
=# select * From test1;
id
----
1
2
3
(3 rows)
=# insert into test2 values(11);
INSERT 0 1
=# insert into test2 values(12);
INSERT 0 1
=# insert into test2 values(13);
INSERT 0 1
=# delete from test1 where id in (select id from test2);
DELETE 3
=# select * From test1;
id
----
(0 rows)
This has bitten me twice, recently, with consequent (though
recoverable) data loss. It may be that SQL is doing exactly as it
should, since 'id' is in scope within the subselect, but if that's
the case it's a nasty gotcha.
Obviously the subselect, when used as a select, does generate an error.
=# select id from test2;
ERROR: column "id" does not exist
LINE 1: select id from test2;
^
Referencing a field that doesn't exist in either also causes an error.
=# select * from test1 where id in (select if from test2);
ERROR: column "if" does not exist
LINE 1: select * from test1 where id in (select if from test2);
^
Insight from wiser SQL heads than mine would be appreciated.
--Rick.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Misnamed field in subquery does not cause error when field name exists in parent query
@ 2007-12-23 00:14 Michael Glaesemann <grzm@seespotcode.net>
parent: Rick Innis <rick.innis@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Michael Glaesemann @ 2007-12-23 00:14 UTC (permalink / raw)
To: Rick Innis <rick.innis@gmail.com>; +Cc: pgsql-sql
On Dec 22, 2007, at 17:12 , Rick Innis wrote:
> It may be that SQL is doing exactly as it should, since 'id' is in
> scope within the subselect, but if that's the case it's a nasty
> gotcha.
Yes, and yes.
Michael Glaesemann
grzm seespotcode net
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Misnamed field in subquery does not cause error when field name exists in parent query
@ 2007-12-24 19:25 Rick Innis <rick.innis@gmail.com>
parent: Michael Glaesemann <grzm@seespotcode.net>
0 siblings, 0 replies; 3+ messages in thread
From: Rick Innis @ 2007-12-24 19:25 UTC (permalink / raw)
To: Michael Glaesemann <grzm@seespotcode.net>; +Cc: pgsql-sql
On 22-Dec-07, at 7:14 PM, Michael Glaesemann wrote:
>
>> It may be that SQL is doing exactly as it should, since 'id' is in
>> scope within the subselect, but if that's the case it's a nasty
>> gotcha.
>
> Yes, and yes.
Ow. Thanks for confirming both my suspicions and my fears :-)
R.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-12-24 19:25 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-12-22 22:12 Misnamed field in subquery does not cause error when field name exists in parent query Rick Innis <rick.innis@gmail.com>
2007-12-23 00:14 ` Michael Glaesemann <grzm@seespotcode.net>
2007-12-24 19:25 ` Rick Innis <rick.innis@gmail.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