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.
