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

From: Allan Wang <allanvv(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: extra columns in intermediate nodes not being removed by top level of executor
Date: 2005-09-03 20:29:25
Message-ID: 1125779365.10675.17.camel@localhost
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Michael Fuhr 2005-09-03 20:57:12 Re: extra columns in intermediate nodes not being removed by top level of executor
Previous Message Peter Eisentraut 2005-09-03 19:18:15 Re: BUG #1860: Insert failed due to unique index