From: | Daniel Grace <dgrace(at)wingsnw(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Query with aggregate over subselects fails with ERROR: plan should not reference subplan's variable |
Date: | 2009-04-25 00:03:54 |
Message-ID: | 8a80df380904241703i3e2fb96bk380ab25657a5e6b1@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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;
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2009-04-25 00:38:13 | Re: Query with aggregate over subselects fails with ERROR: plan should not reference subplan's variable |
Previous Message | Kevin Grittner | 2009-04-24 16:35:22 | Re: BUG #4780: Aggregate functions are unaware of LIMIT clauses in SELECTs |