pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBug in sub-query
2+ messages / 2 participants
[nested] [flat]
* Bug in sub-query
@ 2025-12-12 10:59 Veeranjaneya Vara Prasad Peddireddy <varaprasad.peddireddy@valuelabs.com>
0 siblings, 1 reply; 2+ messages in thread
From: Veeranjaneya Vara Prasad Peddireddy @ 2025-12-12 10:59 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Hi Team,
I have observed one bug in postgres
How to reproduce:
create table admin.emp (sno integer,name varchar(100),location_id_location integer);
create table admin.emp_dimension (dim_id integer,employee_name varchar(100),location_id integer);
insert into admin.emp select 1,'vara',108;
insert into admin.emp select 2,'saasthra',108;
insert into admin.emp_dimension select 1,'varaprasad',108;
insert into admin.emp_dimension select 2,'saasthra peddireddy',108;
--query 1:
select * from admin.emp_dimension where location_id_location=108;
--Note: it failed because "ERROR: column "location_id_location" does not exist
--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.
select version()--
"PostgreSQL 16.8 on x86_64-pc-linux-gnu, compiled by x86_64-pc-linux-gnu-gcc (GCC) 10.5.0, 64-bit"
The issue is present in other versions as well. Please check and fix it.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Bug in sub-query
@ 2025-12-12 15:26 Tom Lane <tgl@sss.pgh.pa.us>
parent: Veeranjaneya Vara Prasad Peddireddy <varaprasad.peddireddy@valuelabs.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2025-12-12 15:26 UTC (permalink / raw)
To: Veeranjaneya Vara Prasad Peddireddy <varaprasad.peddireddy@valuelabs.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-12-12 15:26 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-12-12 10:59 Bug in sub-query Veeranjaneya Vara Prasad Peddireddy <varaprasad.peddireddy@valuelabs.com>
2025-12-12 15:26 ` Tom Lane <tgl@sss.pgh.pa.us>
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