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

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

On Sat, Sep 03, 2005 at 04:29:25PM -0400, Allan Wang wrote:
> 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

Here's a simplified, complete test case:

CREATE TABLE foo (
    x  integer,
    y  integer
);

INSERT INTO foo (x, y) VALUES (1, 2);

SELECT * FROM (SELECT sum(x), (SELECT y) AS yy FROM foo GROUP BY y) AS s LIMIT 1;
 sum | yy |
-----+----+---
   1 |  2 | 2
(1 row)

SELECT * FROM (SELECT sum(x), (SELECT y) AS yy FROM foo GROUP BY yy) AS s LIMIT 1;
 sum | yy
-----+----
   1 |  2
(1 row)

SELECT * FROM (SELECT sum(x), (SELECT y) AS yy FROM foo GROUP BY y) AS s;
 sum | yy
-----+----
   1 |  2
(1 row)

SELECT * FROM (SELECT sum(x), y AS yy FROM foo GROUP BY y) AS s LIMIT 1;
 sum | yy
-----+----
   1 |  2
(1 row)

SELECT * FROM (SELECT x, (SELECT y) AS yy FROM foo) AS s LIMIT 1;
 x | yy
---+----
 1 |  2
(1 row)

--
Michael Fuhr

Michael Fuhr <mike@fuhr.org> writes:
> On Sat, Sep 03, 2005 at 04:29:25PM -0400, Allan Wang wrote:
>> Extra columns seem to be on sum(plays.length), videos.path, videoid

> Here's a simplified, complete test case:

Thanks for the test case.  The bug seems to be introduced by the code
I added a couple months ago to eliminate unnecessary SubqueryScan plan
nodes: the Limit node ends up with a targetlist different from its
immediate input node, which is wrong because Limit doesn't do any
projection, only pass on its input tuples (or not).  There are probably
related cases involving Sort nodes (ORDER BY on a sub-select) and other
plan nodes that don't do projection.  Haven't decided on an appropriate
fix yet --- seems we have to either prevent the SubqueryScan from being
removed in this context, or fix the tlist of the parent node.  Don't
know which will be less messy.

            regards, tom lane

Michael Fuhr <mike@fuhr.org> writes:
> On Sat, Sep 03, 2005 at 04:29:25PM -0400, Allan Wang wrote:
>> Extra columns seem to be on sum(plays.length), videos.path, videoid

> Here's a simplified, complete test case:

Patch applied.  Thanks for the test case.

            regards, tom lane