Re: Possible wrong result with some "in" subquery with non-existing columns

Thanks.  Tom.
I think it just makes sense. It doesn't matter if the user knows how to express the real query.
Possibly it's better to emphasize this scenario  as an example in the official document :-)

Regards,
Xiong

------------------ Original ------------------
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us&gt;;
Date:&nbsp;Tue, Jan 17, 2023 04:04 PM
To:&nbsp;"Xiong He"<iihero(at)qq(dot)com&gt;;
Cc:&nbsp;"pgsql-bugs"<pgsql-bugs(at)lists(dot)postgresql(dot)org&gt;;
Subject:&nbsp;Re: Possible wrong result with some "in" subquery with non-existing columns

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

&gt; In the above query: id1 is not a column of table test2.

Nope, but it's a legal outer reference.

&gt; 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.&nbsp; Yes, it trips up novices all the time, but it does
have valid use-cases.

regards, tom lane