Re: Why is it that “aggregate functions are not allowed in FROM clause of their own query level”?

On Wednesday, May 13, 2020, Tom Ellis <tom-postgresql(dot)org(at)jaguarpaw(dot)co(dot)uk>
wrote:

> Hello,
>
> The code under 1 gives me the error message "aggregate functions are
> not allowed in FROM clause of their own query level" whereas the code
> under 2 is permitted. Unless I am much mistaken the latter is
> equivalent to the former because it just makes a new "local" name for
> `v`.
>

https://www.postgresql.org/docs/12/sql-select.html

Because step 2 precedes step 4.

> A. Am I right in thinking that the two forms are equivalent?

In so far as if the first one could be executed it would provide the same
result, yes...i think

>
> A1. And am I right to presume that it's always possibly to rewrite
> more complicated examples that yield the same error to valid versions,
> just by coming up with a local name for the problematic fields?

Don’t feel like figuring out a counter-example, your given example is not
compelling enough

>
> B. If they are indeed equivalent, what is the rationale for forbidding
> the former? It seems like it would be more convenient to allow users
> to write the former form.

It wasn’t directly intentional. Lateral came long after from/group by.
But since it extends the from clause the processing order puts it before
aggregation.

> 1. Causes error:
>
> SELECT * FROM
> ((SELECT 0 as "v") as "T1"
> INNER JOIN LATERAL
> (SELECT
> SUM("v") as "r"
> FROM (SELECT 0) as "T1") as "T2"
> ON TRUE) as "T1"
>
> 2. Runs successfully
>
> SELECT * FROM
> ((SELECT 0 as "v") as "T1"
> INNER JOIN LATERAL
> (SELECT
> SUM("v_again") as "r"
> FROM (SELECT "v" as "v_again") as "T1") as "T2"
> ON TRUE) as "T1"
>
>
> By the way, the only reference to this issue that I can find on the
> entire internet is the following old mailing list post:
>
> https://www.postgresql.org/message-id/1375925710.17807.
> 13.camel%40vanquo.pezone.net

Yeah, because usually one just writes your example:

Select sum(“v”) from (select 0 as “v”) as t1 group by “v”;

> I also posted on DBA.StackExchange
> https://dba.stackexchange.com/questions/266988/why-is-it-
> that-aggregate-functions-are-not-allowed-in-from-clause-of-their-own
>
>
I did not.

David J.