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
Date: Fri, 12 Dec 2025 10:26:12 -0500
Message-ID: <988549.1765553172@sss.pgh.pa.us> (raw)
In-Reply-To: <BMXPR01MB36873720906F9A703D235C37EFAEA@BMXPR01MB3687.INDPRD01.PROD.OUTLOOK.COM>
References: <BMXPR01MB36873720906F9A703D235C37EFAEA@BMXPR01MB3687.INDPRD01.PROD.OUTLOOK.COM>
Veeranjaneya Vara Prasad Peddireddy <varaprasad.peddireddy@valuelabs.com> writes:
> --query 2:
> select * from admin.emp where location_id_location=108
> and sno in (select dim_id from admin.emp_dimension where location_id_location=108)
> --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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, varaprasad.peddireddy@valuelabs.com, pgsql-bugs@lists.postgresql.org
Subject: Re: Bug in sub-query
In-Reply-To: <988549.1765553172@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox