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

From: Tom Ellis <tom-postgresql(dot)org(at)jaguarpaw(dot)co(dot)uk>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Why is it that “aggregate functions are not allowed in FROM clause of their own query level”?
Date: 2020-05-13 11:02:51
Message-ID: 20200513110251.GC24083@cloudinit-builder
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Laurenz Albe 2020-05-13 11:35:06 Re: what is the best way to access cold data on another server?
Previous Message Ravi Krishna 2020-05-13 10:42:25 Re: ESQL/C: a ROLLBACK rolls back a COMMITED transaction