Weirdness (bug?) with aggregates and subqueries
От | Laurenz Albe |
---|---|
Тема | Weirdness (bug?) with aggregates and subqueries |
Дата | |
Msg-id | 895f34d19556dda5ff3319b584198df20085e596.camel@cybertec.at обсуждение исходный текст |
Ответы |
Re: Weirdness (bug?) with aggregates and subqueries
|
Список | 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
В списке pgsql-general по дате отправления: