There is a scenario in which we are retrieving some result from inner query and using the result to perform some operation
create table test1(key integer,value varchar)
insert into test1 values(1,'value 1');
insert into test1 values(2,'value 2');
insert into test1 values(3,'value 3');
second table as
create table test2(key1 integer, valuein varchar);
insert into test2 values (2,'value inside is 2');
insert into test2 values (4,'value inside is 4');
insert into test2 values (5,'value inside is 5');
the below query is giving result but in my view it should give an error
select * from test1 where key in
(select key from test2)
because key column does not exist in test2 table.
but it is giving result in postgres
but when run in oracle it is giving error as
ORA-00904: "KEY": invalid identifier 00904. 00000 - "%s: invalid identifier"
This is the correct behavior as specified in the SQL standard. The inner query has access to all columns of the outer query - and because test1 has a column named key (which, btw is a horrible name for a column) the inner select is valid.
See these discussions on the Postgres mailing list:
http://postgresql.nabble.com/BUG-13336-Unexpected-result-from-invalid-query-td5850684.html