Thread: Misnamed field in subquery does not cause error when field name exists in parent query

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.

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

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.