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

Поиск
Список
Период
Сортировка
От Daniel Grace
Тема Query with aggregate over subselects fails with ERROR: plan should not reference subplan's variable
Дата
Msg-id 8a80df380904241703i3e2fb96bk380ab25657a5e6b1@mail.gmail.com
обсуждение исходный текст
Ответы Re: Query with aggregate over subselects fails with ERROR: plan should not reference subplan's variable  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: Query with aggregate over subselects fails with ERROR: plan should not reference subplan's variable  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-bugs
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

BEGIN;

CREATE SCHEMA bug_schema;
SET SEARCH_PATH='bug_schema';

CREATE FUNCTION AGG_GROUP_CONCAT_SFUNC(IN _state TEXT, IN _str TEXT, IN _sep
TEXT) RETURNS TEXT
SECURITY INVOKER
LANGUAGE PLPGSQL
IMMUTABLE
CALLED ON NULL INPUT
AS $PROC$
BEGIN
    IF _str IS NULL THEN RETURN _state; END IF;
    IF _state IS NULL THEN RETURN _str; END IF;
    RETURN _state || _sep || _str;
END;
$PROC$;


CREATE AGGREGATE GROUP_CONCAT(TEXT, TEXT) (
    STYPE = TEXT,
    SFUNC = AGG_GROUP_CONCAT_SFUNC
);

CREATE TABLE foo (
    id    serial        NOT NULL,
    fname    varchar(64)        NOT NULL,
    PRIMARY KEY (id)
);

-- Fails: ERROR:  plan should not reference subplan's variable
SELECT (SELECT GROUP_CONCAT((SELECT s2.fname FROM foo AS s2 WHERE
s2.id=s.idORDER BY fname), '; ')) AS foolist FROM foo AS s;

-- Also fails, same error
SELECT (SELECT MAX((SELECT s2.fname FROM foo AS s2 WHERE s2.id=s.id ORDER BY
fname))) AS foomaxFROM foo AS s;

ROLLBACK;

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

Предыдущее
От: "Kevin Grittner"
Дата:
Сообщение: Re: BUG #4780: Aggregate functions are unaware of LIMIT clauses in SELECTs
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Query with aggregate over subselects fails with ERROR: plan should not reference subplan's variable