Re: Bug in sub-query

Veeranjaneya Vara Prasad Peddireddy <varaprasad(dot)peddireddy(at)valuelabs(dot)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