Re: Possible wrong result with some "in" subquery with non-existing columns - Mailing list pgsql-bugs

"=?ISO-8859-1?B?WGlvbmcgSGU=?=" <iihero@qq.com> writes:
> mydb=# create table test1(id1 int primary key, col2 varchar(32));
> mydb=# create table test2(id2 int primary key, col2 varchar(32));
> mydb=# select * from test1 where id1 in (select id1 from test2 where id2 = 2);

> In the above query: id1 is not a column of table test2.

Nope, but it's a legal outer reference.

> Should not we expect this should be en error instead of it thinking it's a column from the table : test1?

This behavior is required by the SQL standard, and has been for
decades.  Yes, it trips up novices all the time, but it does
have valid use-cases.

            regards, tom lane