From: Rick Innis <rick.innis@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Misnamed field in subquery does not cause error when field name exists in parent query
Date: Sat, 22 Dec 2007 17:12:45 -0500
Message-ID: <1B7BB76A-D750-4808-8104-2EC50DDB2FBA@gmail.com> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rick.innis@gmail.com
Subject: Re: Misnamed field in subquery does not cause error when field name exists in parent query
In-Reply-To: <1B7BB76A-D750-4808-8104-2EC50DDB2FBA@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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