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.