BUG #3459: Query Error : plan should not reference subplan's variable
The following bug has been logged online:
Bug reference: 3459
Logged by: David Sanchez i Gregori
Email address: delkos_2000@yahoo.es
PostgreSQL version: 8.x
Operating system: Windows XP SP2, Linux x86, Linux X86-64
Description: Query Error : plan should not reference subplan's
variable
Details:
I reported this bug some while ago, but now I've done some research, that I
think it can be useful.
If we create a database like this:
CREATE DATABASE "Agenda"
WITH OWNER = postgres
ENCODING = 'UTF8'
TABLESPACE = pg_default;
An schema :
CREATE SCHEMA "Seguretat"
AUTHORIZATION postgres;
COMMENT ON SCHEMA "Seguretat" IS 'Esquema on resideixen les funcions taules
i procediments relacionants amb la segretat.';
A Table :
CREATE TABLE "Seguretat"."Usuarios"
(
"Nom" varchar(255) NOT NULL, -- Nom del usuari
"ID" numeric NOT NULL, -- Identificador del usuari
"Cognom1" varchar(255) NOT NULL, -- Primer cognom del usuari
CONSTRAINT "PK_Usuarios" PRIMARY KEY ("ID")
)
WITHOUT OIDS;
ALTER TABLE "Seguretat"."Usuarios" OWNER TO postgres;
COMMENT ON COLUMN "Seguretat"."Usuarios"."Nom" IS 'Nom del usuari';
COMMENT ON COLUMN "Seguretat"."Usuarios"."ID" IS 'Identificador del
usuari';
COMMENT ON COLUMN "Seguretat"."Usuarios"."Cognom1" IS 'Primer cognom del
usuari';
And do this query :
select min("ID") as nid from "Seguretat"."Usuarios" as j
where not exists
( select 1 from "Seguretat"."Usuarios" as k where k."ID"=j."ID")
The result is the expected one, but if we do this query :
select * from(
select min("ID") as nid from "Seguretat"."Usuarios" as j
where not exists
( select 1 from "Seguretat"."Usuarios" as k where k."ID"=j."ID")
) as b
We get an error, when we should get the same result.
The problem, also appears with max, instead of min, but there is no error,
with avg, or count.
I found these error in all 8.x on Windows XP SP 2 (32 bits) , Linux 32 bits,
and Linux AMD64.
Again, sorry for my English.
"David Sanchez i Gregori" <delkos_2000@yahoo.es> writes:
select * from(
select min("ID") as nid from "Seguretat"."Usuarios" as j
where not exists
( select 1 from "Seguretat"."Usuarios" as k where k."ID"=j."ID")
) as b
Confirmed here, as far back as 8.1. It sort of looks like the
indexes-for-min-and-max patch has confused the older code for Param
assignment. I'm surprised this wasn't reported earlier...
regards, tom lane
"David Sanchez i Gregori" <delkos_2000@yahoo.es> writes:
Description: Query Error : plan should not reference subplan's
variable
If it helps, I've applied the attached patch to fix this.
I found these error in all 8.x on Windows XP SP 2 (32 bits) , Linux 32 bits,
and Linux AMD64.
AFAICT the bug is new in 8.1. If you can reproduce something of the
sort in 8.0.x, please submit a test case.
regards, tom lane
Index: src/backend/optimizer/plan/subselect.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/optimizer/plan/subselect.c,v
retrieving revision 1.112.2.1
diff -c -r1.112.2.1 subselect.c
*** src/backend/optimizer/plan/subselect.c 6 Dec 2006 19:40:08 -0000 1.112.2.1
--- src/backend/optimizer/plan/subselect.c 18 Jul 2007 21:23:41 -0000
***************
*** 1306,1315 ****
Param *prm; /*
! * Set up for a new level of subquery. This is just to keep
! * SS_finalize_plan from becoming confused.
*/
- PlannerQueryLevel++;
PlannerInitPlan = NIL;
/*
--- 1306,1319 ----
Param *prm; /*
! * We must run SS_finalize_plan(), since that's normally done before a
! * subplan gets put into the initplan list. However it will try to attach
! * any pre-existing initplans to this one, which we don't want (they are
! * siblings not children of this initplan). So, a quick kluge to hide
! * them. (This is something else that could perhaps be cleaner if we did
! * extParam/allParam processing in setrefs.c instead of here? See notes
! * for materialize_finished_plan.)
*/
PlannerInitPlan = NIL;
/*
***************
*** 1317,1324 ****
*/
SS_finalize_plan(plan, root->parse->rtable);
! /* Return to outer subquery context */
! PlannerQueryLevel--;
PlannerInitPlan = saved_initplan;
/*
--- 1321,1327 ----
*/
SS_finalize_plan(plan, root->parse->rtable);! /* Restore outer initplan list */
PlannerInitPlan = saved_initplan;
/*