Обсуждение: BUG #3459: Query Error : plan should not reference subplan's variable
BUG #3459: Query Error : plan should not reference subplan's variable
От
"David Sanchez i Gregori"
Дата:
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; /*