Thread: ERROR: variable not found in subplan target lists
Hi, I get this error when running this query (generated by code so it is not ideal though still valid I guess): SELECT Invoice.* FROM Invoice WHERE false AND InvoiceGroupId IN (SELECT InvoiceGroup.Id FROM InvoiceGroup INNER JOIN PartnerBranch ON InvoiceGroup.PartnerBranchId = PartnerBranch.Id WHERE PartnerBranch.PartnerIdentificationId IN (132)); Running these modified queries works without problems: SELECT Invoice.* FROM Invoice WHERE InvoiceGroupId IN (SELECT InvoiceGroup.Id FROM InvoiceGroup INNER JOIN PartnerBranch ON InvoiceGroup.PartnerBranchId = PartnerBranch.Id WHERE PartnerBranch.PartnerIdentificationId IN (132)); SELECT Invoice.* FROM Invoice WHERE false AND InvoiceGroupId IN (SELECT InvoiceGroup.Id FROM InvoiceGroup INNER JOIN PartnerBranch ON InvoiceGroup.PartnerBranchId = PartnerBranch.Id); SELECT Invoice.* FROM Invoice WHERE false AND InvoiceGroupId IN (SELECT InvoiceGroup.Id FROM InvoiceGroup INNER JOIN PartnerBranch ON InvoiceGroup.PartnerBranchId = PartnerBranch.Id WHERE PartnerBranch.Id IN (132)); The weird thing is it occurs only on production server, not on any other of pg installations. All of them are pg 8.2.4 on Gentoo Linux. I'd appreciate any help as the problem occurs on production server and I need to solve it ASAP. -- Miroslav Šulc
Just copied client database from production server to my dev database and ran the query on my dev database and it fails too so it seems it also depends on whether some data are contained in the tables or not. -- Miroslav Šulc Miroslav Šulc napsal(a): > Hi, > > I get this error when running this query (generated by code so it is not > ideal though still valid I guess): > > SELECT Invoice.* FROM Invoice WHERE false AND InvoiceGroupId IN (SELECT > InvoiceGroup.Id FROM InvoiceGroup INNER JOIN PartnerBranch ON > InvoiceGroup.PartnerBranchId = PartnerBranch.Id WHERE > PartnerBranch.PartnerIdentificationId IN (132)); > > Running these modified queries works without problems: > > SELECT Invoice.* FROM Invoice WHERE InvoiceGroupId IN (SELECT > InvoiceGroup.Id FROM InvoiceGroup INNER JOIN PartnerBranch ON > InvoiceGroup.PartnerBranchId = PartnerBranch.Id WHERE > PartnerBranch.PartnerIdentificationId IN (132)); > > SELECT Invoice.* FROM Invoice WHERE false AND InvoiceGroupId IN (SELECT > InvoiceGroup.Id FROM InvoiceGroup INNER JOIN PartnerBranch ON > InvoiceGroup.PartnerBranchId = PartnerBranch.Id); > > SELECT Invoice.* FROM Invoice WHERE false AND InvoiceGroupId IN (SELECT > InvoiceGroup.Id FROM InvoiceGroup INNER JOIN PartnerBranch ON > InvoiceGroup.PartnerBranchId = PartnerBranch.Id WHERE PartnerBranch.Id > IN (132)); > > The weird thing is it occurs only on production server, not on any other > of pg installations. All of them are pg 8.2.4 on Gentoo Linux. > > I'd appreciate any help as the problem occurs on production server and I > need to solve it ASAP. > > -- > Miroslav Šul
I just verified that the problem is in pg 8.2.5 too. -- Miroslav Šulc Miroslav Šulc napsal(a): > Just copied client database from production server to my dev database > and ran the query on my dev database and it fails too so it seems it > also depends on whether some data are contained in the tables or not. > > -- > Miroslav Šulc
Miroslav Šulc wrote: > I just verified that the problem is in pg 8.2.5 too. Can you show us the table definitions? -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
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):
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?
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Miroslav Šulc wrote:
>> I just verified that the problem is in pg 8.2.5 too.
> Can you show us the table definitions?
We need a *complete* test case, ie, a SQL script to provoke the error
starting from an empty database.
regards, tom lane
Tom Lane napsal(a):
OK, I'll try to create it from the client's data, will take some time though.Alvaro Herrera <alvherre@commandprompt.com> writes:Miroslav Ĺ ulc wrote:I just verified that the problem is in pg 8.2.5 too.Can you show us the table definitions?We need a *complete* test case, ie, a SQL script to provoke the error starting from an empty database.
Miroslavregards, tom lane
Tom Lane napsal(a):
# SELECT Invoice.* FROM Invoice WHERE false AND InvoiceGroupId IN (SELECT
InvoiceGroup.Id FROM InvoiceGroup INNER JOIN PartnerBranch ON
InvoiceGroup.PartnerBranchId = PartnerBranch.Id WHERE
PartnerBranch.PartnerIdentificationId IN (132));
ERROR: variable not found in subplan target lists
Here is the complete dump and the query. In my case the bug is reproducible using it.Alvaro Herrera <alvherre@commandprompt.com> writes:Miroslav Ĺ ulc wrote:I just verified that the problem is in pg 8.2.5 too.Can you show us the table definitions?We need a *complete* test case, ie, a SQL script to provoke the error starting from an empty database.
# SELECT Invoice.* FROM Invoice WHERE false AND InvoiceGroupId IN (SELECT
InvoiceGroup.Id FROM InvoiceGroup INNER JOIN PartnerBranch ON
InvoiceGroup.PartnerBranchId = PartnerBranch.Id WHERE
PartnerBranch.PartnerIdentificationId IN (132));
ERROR: variable not found in subplan target lists
Miroslavregards, tom lane
Attachment
=?ISO-8859-2?Q?Miroslav_=A9ulc?= <miroslav.sulc@startnet.cz> writes:
> Here is the complete dump and the query. In my case the bug is
> reproducible using it.
> # SELECT Invoice.* FROM Invoice WHERE false AND InvoiceGroupId IN (SELECT
> InvoiceGroup.Id FROM InvoiceGroup INNER JOIN PartnerBranch ON
> InvoiceGroup.PartnerBranchId = PartnerBranch.Id WHERE
> PartnerBranch.PartnerIdentificationId IN (132));
> ERROR: variable not found in subplan target lists
<spock>Fascinating.</spock>
What we've got here is that eval_const_expressions knows that "false AND
anything-at-all" can be simplified to "false", so it merrily reduces the
top-level WHERE to just "WHERE false". However, at that point we have
already done pull_up_IN_clauses(), so the sub-select doesn't disappear
entirely --- we're already committed to forming a join between it and
Invoice. If you run the example without having put any data in the
tables, you get a rather silly-looking plan involving a top-level Result
node with "One-Time Filter: false", and underneath it an unconstrained
(cartesian) Nested Loop IN Join between Invoice and the
InvoiceGroup/PartnerBranch join. The reason it fails with the data
loaded is that in that case the planner decides that the best bet is to
unique-ify the output of the InvoiceGroup/PartnerBranch join, so it
generates a HashAgg node that's trying to merge like values of
InvoiceGroup.Id (which it got from the in_info_list entry). Trouble is,
that variable is no longer mentioned anywhere in the main WHERE clause,
so the sub-join didn't think it needed to emit the variable, whence the
failure.
In a perfect world we'd not have this problem because
const-simplification would have got rid of the IN altogether, and we'd
have a plan equivalent to "SELECT Invoice.* FROM Invoice WHERE false".
However making that happen seems quite difficult/risky because of
order-of-operations issues --- we really want to do jointree
rearrangement before we do expression simplification. Since it's
such a hokey query (how many applications really write "WHERE false"?),
I'm not willing to invest a lot of blood sweat and tears in the case.
Given that the only part of the resulting plan that ever gets executed
is the gating one-time-filter Result, all we'd be saving is planning
time anyway.
Bottom line seems to be that we should run through the in_info_list and
force Vars mentioned therein to be propagated up at least to the
"righthand" join level, ensuring they're available if we decide to
unique-ify above that point. This is a kluge, but it will take minimal
added cycles and not require major planner rework to fix what's really
a corner case that will seldom be of interest in the real world.
Or has anyone got a better idea?
regards, tom lane
PS: this bug seems to go clear back to 7.4 :-(
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Tom Lane wrote:
> =?ISO-8859-2?Q?Miroslav_=A9ulc?= <miroslav.sulc@startnet.cz> writes:
>> Here is the complete dump and the query. In my case the bug is
>> reproducible using it.
> In a perfect world we'd not have this problem because
> const-simplification would have got rid of the IN altogether, and we'd
> have a plan equivalent to "SELECT Invoice.* FROM Invoice WHERE false".
> However making that happen seems quite difficult/risky because of
> order-of-operations issues --- we really want to do jointree
> rearrangement before we do expression simplification. Since it's
> such a hokey query (how many applications really write "WHERE false"?),
Three letters :)... O.R.M
I see this type of weirdness all the time... The most recent was
something like this:
AND ((lower(a.firstName) LIKE NULL OR NULL IS NULL)
AND (lower(a.middleName) LIKE NULL OR NULL IS NULL)
AND (lower(a.lastName) LIKE '%foo%' OR '%foo%' IS NULL)
AND (lower(b.emailAddress) LIKE NULL OR NULL IS NULL) )
Granted this isn't WHERE FALSE, but I certainly see WHERE TRUE all the
time in similar scenarios and I have seen WHERE FALSE.
Sincerely,
Joshua D. Drake
Joshua D. Drake
- --
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFHBSJaATb/zqfZUUQRAnPFAJ9Ut8H9MrC22xBqL4FXqNe9WpMefACdHdc8
To8QKvTQcgoicOSfAzhGYC0=
=qnfG
-----END PGP SIGNATURE-----
"Joshua D. Drake" <jd@commandprompt.com> writes:
> Tom Lane wrote:
>> such a hokey query (how many applications really write "WHERE false"?),
> Granted this isn't WHERE FALSE, but I certainly see WHERE TRUE all the
> time in similar scenarios and I have seen WHERE FALSE.
In what context? Either "WHERE TRUE AND ..." or "WHERE FALSE OR ..."
strike me as perfectly sensible, but "WHERE FALSE AND ..." doesn't
seem to have obvious usefulness.
regards, tom lane
Just an explanation why I create such a weird WHERE clause. I have a form definition that should on initialization load no records in list, that's the reason why 'false' is there. But every query goes through layer that adds extra clause to the original one to allow access only to records the user is allowed to see. That's the reason "WHERE false AND ...." appears here. When user selects some item from list above this one, the list is reloaded with "WHERE <filter_by_selected_value> AND ...". I think I could fix that in my code that draws to form and check for "false" value in filter and if it is there then issue no query at all. But I think this kind of weird clauses can appear in any code where the clause is created (concatenated) by code in different layers. I would vote for a fix that would be easy and transparent instead of some major rewrite of the planner, mainly because the fix would be available much sooner. If needed, optimization could be done in next step in some future when the optimization will have high enough priority to make sense to code it. -- Miroslav Tom Lane napsal(a): > =?ISO-8859-2?Q?Miroslav_=A9ulc?= <miroslav.sulc@startnet.cz> writes: > >> Here is the complete dump and the query. In my case the bug is >> reproducible using it. >> > > >> # SELECT Invoice.* FROM Invoice WHERE false AND InvoiceGroupId IN (SELECT >> InvoiceGroup.Id FROM InvoiceGroup INNER JOIN PartnerBranch ON >> InvoiceGroup.PartnerBranchId = PartnerBranch.Id WHERE >> PartnerBranch.PartnerIdentificationId IN (132)); >> ERROR: variable not found in subplan target lists >> > > <spock>Fascinating.</spock> > > What we've got here is that eval_const_expressions knows that "false AND > anything-at-all" can be simplified to "false", so it merrily reduces the > top-level WHERE to just "WHERE false". However, at that point we have > already done pull_up_IN_clauses(), so the sub-select doesn't disappear > entirely --- we're already committed to forming a join between it and > Invoice. If you run the example without having put any data in the > tables, you get a rather silly-looking plan involving a top-level Result > node with "One-Time Filter: false", and underneath it an unconstrained > (cartesian) Nested Loop IN Join between Invoice and the > InvoiceGroup/PartnerBranch join. The reason it fails with the data > loaded is that in that case the planner decides that the best bet is to > unique-ify the output of the InvoiceGroup/PartnerBranch join, so it > generates a HashAgg node that's trying to merge like values of > InvoiceGroup.Id (which it got from the in_info_list entry). Trouble is, > that variable is no longer mentioned anywhere in the main WHERE clause, > so the sub-join didn't think it needed to emit the variable, whence the > failure. > > In a perfect world we'd not have this problem because > const-simplification would have got rid of the IN altogether, and we'd > have a plan equivalent to "SELECT Invoice.* FROM Invoice WHERE false". > However making that happen seems quite difficult/risky because of > order-of-operations issues --- we really want to do jointree > rearrangement before we do expression simplification. Since it's > such a hokey query (how many applications really write "WHERE false"?), > I'm not willing to invest a lot of blood sweat and tears in the case. > Given that the only part of the resulting plan that ever gets executed > is the gating one-time-filter Result, all we'd be saving is planning > time anyway. > > Bottom line seems to be that we should run through the in_info_list and > force Vars mentioned therein to be propagated up at least to the > "righthand" join level, ensuring they're available if we decide to > unique-ify above that point. This is a kluge, but it will take minimal > added cycles and not require major planner rework to fix what's really > a corner case that will seldom be of interest in the real world. > > Or has anyone got a better idea? > > regards, tom lane > > PS: this bug seems to go clear back to 7.4 :-(
I wrote: > Bottom line seems to be that we should run through the in_info_list and > force Vars mentioned therein to be propagated up at least to the > "righthand" join level, ensuring they're available if we decide to > unique-ify above that point. I've committed a patch along these lines --- if you need it right now see http://archives.postgresql.org/pgsql-committers/2007-10/msg00093.php regards, tom lane
Thank you for the fix.
--
Miroslav
Tom Lane napsal(a):
--
Miroslav
Tom Lane napsal(a):
I wrote:Bottom line seems to be that we should run through the in_info_list and force Vars mentioned therein to be propagated up at least to the "righthand" join level, ensuring they're available if we decide to unique-ify above that point.I've committed a patch along these lines --- if you need it right now see http://archives.postgresql.org/pgsql-committers/2007-10/msg00093.php regards, tom lane
"Tom Lane" <tgl@sss.pgh.pa.us> writes: > "Joshua D. Drake" <jd@commandprompt.com> writes: >> Tom Lane wrote: >>> such a hokey query (how many applications really write "WHERE false"?), > >> Granted this isn't WHERE FALSE, but I certainly see WHERE TRUE all the >> time in similar scenarios and I have seen WHERE FALSE. > > In what context? Either "WHERE TRUE AND ..." or "WHERE FALSE OR ..." > strike me as perfectly sensible, but "WHERE FALSE AND ..." doesn't > seem to have obvious usefulness. I frequently wrote queries like WHERE (?=1 OR col1 LIKE ?) AND (?=1 OR col2 LIKE ?) and then substituted parameters in pairs, one to indicate if a search on a column was necessary and a second to pass the search parameter. That's easier than building up a query with whole sections which might disappear. It's also safer since it's easier to see that the query doesn't have any unquoted strings interpolated into it. Now, as long as the driver's using bound parameters it won't hit this issue but if the queries were complex it might have made sense to have the driver do the variable substitution and execute the query without parameters. Also, if in the future we do handle multiple prepared plans for prepared statements it would run into this kind of problem. But then even in that case your solution still works. I'm just mentioning that there are indeed queries like this out there. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com