To: Josh Berkus <josh@agliodbs.com>
Cc: Mathieu Arnold <m@mat.cc>, pgsql-sql@postgresql.org
Subject: Re: problem with subselect 
In-reply-to: <200303070948.45070.josh@agliodbs.com> 
References: <2063882566.1047045882@andromede.reaumur.absolight.net>
	<200303070948.45070.josh@agliodbs.com>
Comments: In-reply-to Josh Berkus <josh@agliodbs.com>
	message dated "Fri, 07 Mar 2003 09:48:45 -0800"
Date: Fri, 07 Mar 2003 13:31:47 -0500
Message-ID: <1593.1047061907@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Josh Berkus <josh@agliodbs.com> writes:
>> "Sub-SELECT uses un-GROUPed attribute f.date_creation from outer query"
>> Which is not really true, but some way is...

> Hmmm ... this looks like a minor parser bug.

No, it's operating as designed.  Note the comments in CVS tip
parse_agg.c:

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

Previous versions implemented the check for ungrouped vars a little
differently, but the net effect was the same.  (Given that SQL99 allows
only a simple column reference as a GROUP BY element, this isn't a spec
violation, merely a limitation on how far we are willing to extend the
spec.)

There are a number of straightforward ways to rewrite the query to avoid
this, but I can't help wondering whether the basic approach isn't wrong.
The subselect seems an ugly and inefficient way to do it, because it's
re-executing the entire join for each group ... but I can't quite put
my finger on a better way ...

			regards, tom lane