From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Weirdness (bug?) with aggregates and subqueries |
Date: | 2023-11-09 07:26:29 |
Message-ID: | 6d7876248ac584fc6d977f0f032a6e12b3547dc1.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Dave Cramer | 2023-11-09 13:12:50 | Re: Right version of jdbc |
Previous Message | Chris Travers | 2023-11-09 05:08:09 | NUMA, PostgreSQL and docker images |