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 по дате отправления:

Предыдущее
От: Laurenz Albe
Дата:
Сообщение: Re: Maximum xid increasing
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Weirdness (bug?) with aggregates and subqueries