extra columns in intermediate nodes not being removed by top level of executor

Поиск
Список
Период
Сортировка
От Allan Wang
Тема extra columns in intermediate nodes not being removed by top level of executor
Дата
Msg-id 1125779365.10675.17.camel@localhost
обсуждение исходный текст
Ответы Re: extra columns in intermediate nodes not being removed by top level of executor
Список pgsql-bugs
I'm using 8.1 from CVS head of about two days ago.

Extra columns seem to be on sum(plays.length), videos.path, videoid

create or replace view niceplays as SELECT count(*) AS plays,
summary("substring"(v.path, '[^/]+$'::text), 50) AS filename,
avg(p.length)::interval(0) AS avg, sum(p.length)::interval(0) AS sum,
(( SELECT now() - plays.playtimestamp
           FROM plays
          WHERE plays.videoid = v.videoid
          ORDER BY plays.playid DESC
         LIMIT 1))::interval(0) AS lastplay
   FROM plays p
   LEFT JOIN videos v USING (videoid)
  WHERE p.length <> '00:00:00'::interval AND v.path ~~ '/home/allan/TransGaming_Drive/libp/%'::text
  GROUP BY v.path, v.videoid
  ORDER BY sum(p.length::interval(0)) DESC;

allan=# select * from niceplays;
 plays | filename |   avg    |   sum    |     lastplay
-------+----------+----------+----------+-----------------
    13 |          | 00:06:07 | 01:19:26 | 20 days 18:33:51
     8 |          | 00:07:18 | 00:58:23 | 17 days 23:49:38
     8 |          | 00:04:13 | 00:33:44 | 31 days 22:55:20
     3 |          | 00:10:56 | 00:32:47 | 17 days 00:04:18
     7 |          | 00:04:34 | 00:32:00 | 31 days 22:55:36
     6 |          | 00:05:04 | 00:30:22 | 17 days 23:46:32
[...]
(868 rows)

allan=# select * from niceplays order by sum desc limit 3;
 plays | filename |   avg    |   sum    |     lastplay     |          | |
-------+----------+----------+----------+------------------+----------+-+-----
    13 |          | 00:06:07 | 01:19:26 | 20 days 18:35:59 | 01:19:26 | |   43
     8 |          | 00:07:18 | 00:58:23 | 17 days 23:51:47 | 00:58:23 | | 1988
     8 |          | 00:04:13 | 00:33:44 | 31 days 22:57:29 | 00:33:43 | | 1961

Allan Wang

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Peter Eisentraut
Дата:
Сообщение: Re: BUG #1860: Insert failed due to unique index
Следующее
От: Michael Fuhr
Дата:
Сообщение: Re: extra columns in intermediate nodes not being removed by top level of executor