Re: PostgreSQL query with FULL OUTER JOIN and subquery using column of GROUP BY expression ERROR: subquery uses ungrouped column from outer query

"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> My first impression is that this is a bug. Especially since the query
> apparently executes in both left-join and right-join modes.

Well, it's a longstanding deficiency anyway. The problem is that
the full-join-using merged column "id" is represented as
"COALESCE(t2.id, t3.id)" not as a single Var. That should be okay,
because we can handle grouping by an expression, but
substitute_grouped_columns (and check_ungrouped_columns before it)
doesn't handle the case in subqueries:

* 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.

This commentary dates to 2003 (and it's from a patch that replaced an
older implementation with the same limitation; it doesn't look to me
like the case ever worked). AFAIR, the number of complaints we've
gotten about this limitation in the past 30 years could be counted
without running out of thumbs.

Still, it is annoying. I wonder if there's a way to do it without
either a large amount of new code or exponential time spent
trying useless subexpression matches...

regards, tom lane