Inner query not throwing error in postgres

Question

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"

Answer 1

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