Thread: Subquery uses ungrouped column

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?

-- 
Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> From observation PostgreSQL doesn't recognize the equivalency of the
> outer "group by substring(fileid from -1)" and the subquery expression.

Well, it would without the subselect in between.  There's a code comment
in parse_agg.c about this:

 * NOTE: we recognize grouping expressions in the main query, but only
 * grouping Vars in subqueries.  For example, this will be rejected,
 * although it could be allowed:
 *        SELECT
 *            (SELECT x FROM bar where y = (foo.a + foo.b))
 *        FROM foo
 *        GROUP BY a + b;
 * The difficulty is the need to account for different sublevels_up.
 * This appears to require a whole custom version of equal(), which is
 * way more pain than the feature seems worth.

It'd probably be possible to fix parse_agg.c if you didn't mind expending
lots of cycles on such cases.  I'm not sure offhand whether there would
be implications in the planner, or what it would take to fix them if so.

            regards, tom lane