Thread: 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;

      /*