Re: confusing error message

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> I'm having trouble parsing this:
> ERROR: aggregate functions are not allowed in FROM clause of their own query level

> The example in the regression tests is:

> -- LATERAL can be used to put an aggregate into the FROM clause of its query
> select 1 from tenk1 a, lateral (select max(a.unique1) from int4_tbl b) ss;
> ERROR: aggregate functions are not allowed in FROM clause of their own query level
> LINE 1: select 1 from tenk1 a, lateral (select max(a.unique1) from i...
> ^
> I think the "own query level" of the max aggregate function in this case
> is the subquery "ss", and so it's not in the FROM clause of its own
> query level.

No. The max() aggregate function is on a column of table "a", so it
belongs to the outer query level, and it is within the FROM clause of
that level. (Don't blame me, this is per SQL spec ...)

> It's understandable why this is not allowed, but I don't think the error
> message explains it. Could we come up with a better wording?

I'm not sure either. The main point here is that an aggregate's semantic
level is normally the natural level of its argument expression, not the
textual location of the call. The "natural level" is the level of the
lowest-level variable used in the argument. But what if the argument is
variable-free? Then you *do* take the textual location as the semantic
level. This is all sufficiently bizarre that I don't know if there's an
easy explanation.

regards, tom lane