Обсуждение: Bug in sub-query
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.
From ,
Vara Prasad
+91 9951074005
India,
Hyderabad,
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
intable 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