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
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 |