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