On Wednesday, May 13, 2020, Tom Ellis <tom-postgresql....@jaguarpaw.co.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.
