Re: why isn't this subquery wrong?

On Thu, Apr 20, 2017 at 3:43 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> jonathan vanasco <postgres(at)2xlp(dot)com> writes:
> > Can anyone explain to me why the following is valid (running 9.6) ?
>
> > SELECT foo_id
> > FROM example_a__data
> > WHERE foo_id IN (SELECT bar_id FROM example_a__rollup)
> > ;
>
> Per the SQL standard, bar_id is interpreted as an "outer reference"
> to example_a__data.bar_id. This trips people up all the time, but
> (a) it's required by spec and (b) there are cases where it's really
> hard to do what you want without an outer reference.
>
> Cautious SQL programmers qualify all references inside sub-selects
> to avoid getting caught by this accidentally.
>

​I added an FAQ entry to the wiki​ for this question:

https://wiki.postgresql.org/wiki/FAQ#Why_doesn.27t_PostgreSQL_report_a_column_not_found_error_when_using_the_wrong_name_in_a_subquery.3F

David J.