Re: Query with aggregate over subselects fails with ERROR: plan should not reference subplan's variable

On Fri, Apr 24, 2009 at 5:38 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Daniel Grace <dgrace@wingsnw.com> writes:
> > The following nonsensical query causes PostgreSQL to fail with ERROR:
> plan
> > should not reference subplan's variable.  (This was stripped down from an
> > 'useful' query that triggered the same bug).  First encountered on 8.3.4,
> > reproduced on 8.3.7
>
> Hmmm ... I guess something is getting confused about the level of query
> nesting.  FWIW, you can avoid the bug in these two examples by omitting
> the inner "SELECT" keyword, which is useless anyway.  Perhaps it is
> needed in your real query though ...
>
>                        regards, tom lane
>
It's required in my case to force the aggregate function to evaluate its
inputs in a set order.  I'm trying to replace MySQL's GROUP_CONCAT function,
including the ORDER BY option.

I had another variation (that did not use sub-SELECTs, but instead joining
something along the lines of joining (SELECT * FROM foo ORDER BY fname) AS
foo that partially worked -- however, it had the side effect (due to the
nature of the query) of having some duplicate data and not the type that
could be fixed simply by adding DISTINCT.

I'm not going to spam the list with all of the table definitions for the
real query, but I will paste it by itself to give a better idea of what I
was originally attempting:


SELECT
    s.fid, c.flags, c.id, c.title, cs.ut_start, cs.ut_end, cr.full_name,
    cal.title AS cancel_reason,
    (SELECT GROUP_CONCAT((SELECT s2.fname FROM student AS s2 WHERE s2.id=
s.id ORDER BY fname), '; ')) AS students,
    (SELECT GROUP_CONCAT((SELECT p.gname FROM course_teacher AS ct INNER
JOIN person AS p ON ct.tid=p.id WHERE ct.cid=c.id ORDER BY p.gname), '; '))
AS teacher
FROM
    student AS s
    INNER JOIN student_course_session AS scs ON scs.sid=s.id
    INNER JOIN course_session AS cs ON cs.id=scs.csid
    INNER JOIN course AS c ON c.id=cs.cid
    LEFT JOIN course_room AS cr ON cr.id=c.room_id
    LEFT JOIN calendar AS cal ON cal.id=cs.cancelled_by
GROUP BY s.fid, cs.id, c.flags, c.id, c.title, cs.ut_start, cs.ut_end,
cr.full_name, cal.title, cs.tsstart
ORDER BY s.fid, cs.tsstart, c.title;

-- Daniel Grace