Re: Weirdness (bug?) with aggregates and subqueries - Mailing list pgsql-general

From Laurenz Albe
Subject Re: Weirdness (bug?) with aggregates and subqueries
Date
Msg-id 6d7876248ac584fc6d977f0f032a6e12b3547dc1.camel@cybertec.at
Whole thread Raw
In response to Re: Weirdness (bug?) with aggregates and subqueries  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
On Wed, 2023-11-08 at 16:36 -0500, Tom Lane wrote:
> Laurenz Albe <laurenz.albe@cybertec.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



pgsql-general by date:

Previous
From: Chris Travers
Date:
Subject: NUMA, PostgreSQL and docker images
Next
From: Dave Cramer
Date:
Subject: Re: Right version of jdbc