Re: Weirdness (bug?) with aggregates and subqueries

On Wed, 2023-11-08 at 16:36 -0500, Tom Lane wrote:
> Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> writes:
> > I found this in a blog (https://buttondown.email/jaffray/archive/sql-scoping-is-surprisingly-subtle-and-semantic/)
> > CREATE TABLE aa (a INT);
> > INSERT INTO aa VALUES (1), (2), (3);
> > CREATE TABLE xx (x INT);
> > INSERT INTO xx VALUES (10), (20), (30);
>
> > SELECT (SELECT sum(a) FROM xx LIMIT 1) FROM aa;
>
> > sum
> > ═════
> > 6
> > (1 row)
>
> > Huh? Shouldn't that return three rows, just like
>
> No. The aggregate function is semantically of the closest query level
> that contributes a Var to its argument, so it's evaluated at the "FROM
> aa" level, causing that level to become an aggregated query that
> returns just one row. Then it acts like an outer reference as far
> as the sub-select is concerned. This is documented at the end of
> Section 4.2.7 in our manual,
>
> https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-AGGREGATES
>
> Thank the SQL spec for that weirdness.

Thanks for the explanation. Seems like another instance of the standard
committee smoking the wrong stuff.

Yours,
Laurenz Albe