Re: ERROR: variable not found in subplan target lists - Mailing list pgsql-general

Here they are:


CREATE TABLE invoice
(
  id int4 NOT NULL,
  description text NOT NULL,
  textcontent text NOT NULL,
  itemnumber int4 NOT NULL,
  fiscalyear int2 NOT NULL,
  identification varchar(25) NOT NULL,
  partneridentificationid int4,
  executoruserid int2 NOT NULL,
  partnerid int4,
  variablesymbol varchar(20) NOT NULL,
  datesent date,
  dateissued date,
  personpositionid int4,
  totaldiscount numeric(15,2) NOT NULL,
  vatlower numeric(15,2) NOT NULL,
  vatnormal numeric(15,2) NOT NULL,
  totalprice numeric(15,2) NOT NULL,
  datematurity date,
  datereceived date,
  datevat date,
  extrasignatureuser1id int2,
  extrasignatureuser2id int2,
  invoicegroupid int2 NOT NULL,
  invoicepaymenttype int2 NOT NULL,
  invoicerejectionreasonid int2,
  invoicetypeitemid int2 NOT NULL,
  lefttopay numeric NOT NULL,
  relatedinvoiceid int4,
  supplierinvoicenumber varchar(30),
  vatbaselower numeric(15,2) NOT NULL,
  vatbaselowerandvatlower numeric(15,2) NOT NULL,
  vatbasenormal numeric(15,2) NOT NULL,
  vatbasenormalandvatnormal numeric(15,2) NOT NULL,
  vatbasetotal numeric(15,2) NOT NULL,
  vatbasezero numeric(15,2) NOT NULL,
  vattotal numeric(15,2) NOT NULL,
  createdbyuserid int2 NOT NULL,
  datecreated timestamp NOT NULL,
  datedeleted timestamp,
  datelastmodified timestamp,
  deletedbyuserid int2,
  lastmodifiedbyuserid int2,
  CONSTRAINT invoice_pkey PRIMARY KEY (id),
  CONSTRAINT invoice_createdbyuserid_fk FOREIGN KEY (createdbyuserid)
      REFERENCES user2 (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT invoice_deletedbyuserid_fk FOREIGN KEY (deletedbyuserid)
      REFERENCES user2 (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT invoice_executoruserid_fk FOREIGN KEY (executoruserid)
      REFERENCES user2 (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT invoice_extrasignatureuser1id_fk FOREIGN KEY (extrasignatureuser1id)
      REFERENCES user2 (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT invoice_extrasignatureuser2id_fk FOREIGN KEY (extrasignatureuser2id)
      REFERENCES user2 (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT invoice_invoicegroupid_fk FOREIGN KEY (invoicegroupid)
      REFERENCES invoicegroup (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT invoice_invoicerejectionreasonid_fk FOREIGN KEY (invoicerejectionreasonid)
      REFERENCES invoicerejectionreason (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT invoice_invoicetypeitemid_fk FOREIGN KEY (invoicetypeitemid)
      REFERENCES invoicetypeitem (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT invoice_lastmodifiedbyuserid_fk FOREIGN KEY (lastmodifiedbyuserid)
      REFERENCES user2 (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT invoice_partnerid_fk FOREIGN KEY (partnerid)
      REFERENCES partner (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT invoice_partneridentificationid_fk FOREIGN KEY (partneridentificationid)
      REFERENCES partneridentification (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT invoice_personpositionid_fk FOREIGN KEY (personpositionid)
      REFERENCES personposition (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT invoice_relatedinvoiceid_fk FOREIGN KEY (relatedinvoiceid)
      REFERENCES invoice (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT invoice_39d7dd12_key UNIQUE (invoicetypeitemid, partneridentificationid, supplierinvoicenumber),
  CONSTRAINT invoice_cbdea764_key UNIQUE (invoicegroupid, invoicetypeitemid, fiscalyear, itemnumber)
)
WITHOUT OIDS;
ALTER TABLE invoice OWNER TO fordfrog;


CREATE INDEX invoice_dateissued_key
  ON invoice
  USING btree
  (dateissued);

CREATE INDEX invoice_datematurity_key
  ON invoice
  USING btree
  (datematurity);

CREATE INDEX invoice_datesent_key
  ON invoice
  USING btree
  (datesent);

CREATE INDEX invoice_datevat_key
  ON invoice
  USING btree
  (datevat);

CREATE INDEX invoice_executoruserid_key
  ON invoice
  USING btree
  (executoruserid);

CREATE INDEX invoice_invoicegroupid_key
  ON invoice
  USING btree
  (invoicegroupid);

CREATE INDEX invoice_invoicepaymenttype_key
  ON invoice
  USING btree
  (invoicepaymenttype);

CREATE INDEX invoice_invoicerejectionreasonid_key
  ON invoice
  USING btree
  (invoicerejectionreasonid);

CREATE INDEX invoice_invoicetypeitemid_key
  ON invoice
  USING btree
  (invoicetypeitemid);

CREATE INDEX invoice_partnerid_key
  ON invoice
  USING btree
  (partnerid);

CREATE INDEX invoice_partneridentificationid_key
  ON invoice
  USING btree
  (partneridentificationid);

CREATE INDEX invoice_personpositionid_key
  ON invoice
  USING btree
  (personpositionid);

CREATE INDEX invoice_variablesymbol_key
  ON invoice
  USING btree
  (variablesymbol);




CREATE TABLE invoicegroup
(
  id int2 NOT NULL,
  code varchar(5) NOT NULL,
  partnerbranchid int4 NOT NULL,
  urgedaysbeforematurity int2 NOT NULL,
  urgematuritytouserroleid int2,
  useforconsumptioninvoice bool NOT NULL,
  useforinternalinvoice bool NOT NULL,
  useforissuedadvanceinvoice bool NOT NULL,
  useforissuedinvoice bool NOT NULL,
  usefornontaxablenoticeinvoice bool NOT NULL,
  useforreceivedadvanceinvoice bool NOT NULL,
  useforreceivedinvoice bool NOT NULL,
  useforshorttermitemsinvoice bool NOT NULL,
  useforstoretransferinvoice bool NOT NULL,
  usefortaxablenoticeinvoice bool NOT NULL,
  createdbyuserid int2 NOT NULL,
  datecreated timestamp NOT NULL,
  datedeleted timestamp,
  datelastmodified timestamp,
  deletedbyuserid int2,
  lastmodifiedbyuserid int2,
  CONSTRAINT invoicegroup_pkey PRIMARY KEY (id),
  CONSTRAINT invoicegroup_createdbyuserid_fk FOREIGN KEY (createdbyuserid)
      REFERENCES user2 (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT invoicegroup_deletedbyuserid_fk FOREIGN KEY (deletedbyuserid)
      REFERENCES user2 (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT invoicegroup_lastmodifiedbyuserid_fk FOREIGN KEY (lastmodifiedbyuserid)
      REFERENCES user2 (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT invoicegroup_partnerbranchid_fk FOREIGN KEY (partnerbranchid)
      REFERENCES partnerbranch (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT invoicegroup_urgematuritytouserroleid_fk FOREIGN KEY (urgematuritytouserroleid)
      REFERENCES userrole (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT invoicegroup_code_key UNIQUE (code)
)
WITHOUT OIDS;
ALTER TABLE invoicegroup OWNER TO fordfrog;

CREATE INDEX invoicegroup_partnerbranchid_key
  ON invoicegroup
  USING btree
  (partnerbranchid);



CREATE TABLE partnerbranch
(
  id int4 NOT NULL,
  partneridentificationid int4,
  createdbyuserid int2 NOT NULL,
  datecreated timestamp NOT NULL,
  datedeleted timestamp,
  datelastmodified timestamp,
  deletedbyuserid int2,
  lastmodifiedbyuserid int2,
  CONSTRAINT partnerbranch_pkey PRIMARY KEY (id),
  CONSTRAINT partnerbranch_createdbyuserid_fk FOREIGN KEY (createdbyuserid)
      REFERENCES user2 (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT partnerbranch_deletedbyuserid_fk FOREIGN KEY (deletedbyuserid)
      REFERENCES user2 (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT partnerbranch_lastmodifiedbyuserid_fk FOREIGN KEY (lastmodifiedbyuserid)
      REFERENCES user2 (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT partnerbranch_partneridentificationid_fk FOREIGN KEY (partneridentificationid)
      REFERENCES partneridentification (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITHOUT OIDS;
ALTER TABLE partnerbranch OWNER TO fordfrog;


I also tried to create simplified case (just the needed columns and few records) to reproduce the bug but the bug did not appear, I suppose because the planner chose different plan to execute it as I provided just few records.

--
Miroslav Šulc

Alvaro Herrera napsal(a):

Miroslav Šulc wrote: 

I just verified that the problem is in pg 8.2.5 too.   

Can you show us the table definitions?