From: Tom Lane <tgl@sss.pgh.pa.us>
To: Veeranjaneya Vara Prasad Peddireddy <varaprasad.peddireddy@valuelabs.com>
cc: "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: Re: Bug in sub-query
In-reply-to: 
 <BMXPR01MB36873720906F9A703D235C37EFAEA@BMXPR01MB3687.INDPRD01.PROD.OUTLOOK.COM>
References: 
 <BMXPR01MB36873720906F9A703D235C37EFAEA@BMXPR01MB3687.INDPRD01.PROD.OUTLOOK.COM>
Comments: In-reply-to Veeranjaneya Vara Prasad Peddireddy
 <varaprasad.peddireddy@valuelabs.com>
	message dated "Fri, 12 Dec 2025 10:59:25 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <988548.1765553172.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 12 Dec 2025 10:26:12 -0500
Message-ID: <988549.1765553172@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/988549.1765553172%40sss.pgh.pa.us>
Precedence: bulk

Veeranjaneya Vara Prasad Peddireddy <varaprasad.peddireddy@valuelabs.com> =
writes:
> --query 2:
> select * from admin.emp where location_id_location=3D108
> and sno in (select dim_id from admin.emp_dimension where location_id_loc=
ation=3D108)
> --Note2: it returned two records and no error given.
> --But if you observe above query 2; the sub-query is not correct. In sub=
-query; location_id_location is not present in table admin.emp_dimension. =
But same sub-query failed in query 1.

This is not a bug, it is behavior required by the SQL standard.
If location_id_location is not found among the columns available
from the sub-query's FROM clause, it will be treated as an
"outer reference" to columns available from the outer query.

The usual way to avoid falling into this trap is to always
relation-qualify column references, especially in sub-queries.

			regards, tom lane