Weirdness (bug?) with aggregates and subqueries

From: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Weirdness (bug?) with aggregates and subqueries
Date: 2023-11-08 21:12:59
Message-ID: 895f34d19556dda5ff3319b584198df20085e596.camel@cybertec.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

SELECT (SELECT sum(14) FROM xx LIMIT 1) FROM aa;

sum
═════
42
42
42
(3 rows)

Looking at the plan of the weird query, the aggregate seems to be in the wrong place:

EXPLAIN (COSTS OFF) SELECT (SELECT sum(a) FROM xx LIMIT 1) FROM aa;

QUERY PLAN
══════════════════════════════
Aggregate
-> Seq Scan on aa
SubPlan 1
-> Limit
-> Seq Scan on xx
(5 rows)

And this gives an error:

SELECT a, (SELECT sum(a) FROM xx LIMIT 1) FROM aa;
ERROR: column "aa.a" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT a, (SELECT sum(a) FROM xx LIMIT 1) FROM aa;
^

I think that the optimizer is going astray here...

But perhaps I am missing something obvious.

Yours,
Laurenz Albe

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2023-11-08 21:36:31 Re: Weirdness (bug?) with aggregates and subqueries
Previous Message Laurenz Albe 2023-11-08 20:59:48 Re: Maximum xid increasing