From: Tom Lane <tgl@sss.pgh.pa.us>
To: "=?ISO-8859-1?B?WGlvbmcgSGU=?=" <iihero@qq.com>
cc: "=?ISO-8859-1?B?cGdzcWwtYnVncw==?=" <pgsql-bugs@lists.postgresql.org>
Subject: Re: Possible wrong result with some "in" subquery with non-existing
 columns
In-reply-to: <tencent_E8F863DB3246FD47EBEE85FFF5E56B020508@qq.com>
References: <tencent_E8F863DB3246FD47EBEE85FFF5E56B020508@qq.com>
Comments: In-reply-to "=?ISO-8859-1?B?WGlvbmcgSGU=?=" <iihero@qq.com>
	message dated "Tue, 17 Jan 2023 08:00:09 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2915685.1673942658.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Tue, 17 Jan 2023 03:04:18 -0500
Message-ID: <2915686.1673942658@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2915686.1673942658%40sss.pgh.pa.us>
Precedence: bulk

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