Re: Subquery uses ungrouped column

On Thu, May 26, 2016 at 12:02 PM, Alex Ignatov <a(dot)ignatov(at)postgrespro(dot)ru>
wrote:

> Hello!
>
> Why the following query:
>
> SELECT (select msc_id
> from collectors
> where id = substring(fileid from -1)
> ) msc_id
> from ip_data_records
> group by substring(fileid from -1)
>
> gives me:
>
> ERROR: subquery uses ungrouped column "ip_data_records.fileid" from outer
> query
> LINE 3: where id = substring(fileid from -1)
>
> but the following query:
>
> SELECT (select msc_id
> from collectors
> where id = fileid
> ) msc_id
> from ip_data_records
> group by fileid
> is working ok
>

From observation ​PostgreSQL​

​doesn't recognize the equivalency of the outer "group by substring(fileid
from -1)" and the subquery expression. What PostgreSQL does is push the
column ip_data_records.fieldid​ into the subquery where it just happens to
be used in the expression "substring(fileid from -1)". For all PostgreSQL
cares the subquery could have the expression "where id = lower(fileid)" and
the execution mechanics, and error, would be identical.

IOW, columns are the unit of interchange between a parent query and its
correlated subqueries.​

David J.