Re: Subquery uses ungrouped column

["David G. Johnston" <david.g.johnston@xxxxxxxxx>]

On Thu, May 26, 2016 at 12:02 PM, Alex Ignatov <a.ignatov@xxxxxxxxxxxxxx> 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.