Thread: Why is it that “aggregate functions are not allowed in FROM clause of their own query level”?
Why is it that “aggregate functions are not allowed in FROM clause of their own query level”?
From
Tom Ellis
Date:
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`.
I'm writing a code generator and rewriting the former, natural form,
to the latter, artificial form, is somewhat fiddly, but ultimately
possible, I believe.
I have a few questions:
A. Am I right in thinking that the two forms are equivalent?
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?
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.
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
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
Thanks,
Tom
Re: Why is it that “aggregate functions are not allowed in FROM clause of their own query level”?
From
"David G. Johnston"
Date:
On Wednesday, May 13, 2020, Tom Ellis <tom-postgresql.org@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`.
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.
Re: Why is it that “aggregate functions are not allowed in FROM clause of their own query level”?
From
Tom Lane
Date:
Tom Ellis <tom-postgresql.org@jaguarpaw.co.uk> writes:
> A. Am I right in thinking that the two forms are equivalent?
No. In the first case the SUM() aggregate does not use any variables
belonging to the "LATERAL (SELECT ..." query level, therefore, per
SQL standard, it is not an aggregate of that query but an aggregate
of the next query level up, whose variable(s) it does use. And
within the structure of *that* query level, it's in the wrong place.
> 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?
In the particular case here, the problem is to control which query level
the aggregate is deemed to belong to. I think the issue is less about
"can I rewrite the query" and more about "do I actually understand the
semantics this is asking for", so I'd be hesitant to let a tool think
that it can rearrange things like this.
regards, tom lane