Thread: BUG #13875: Error explaining query
The following bug has been logged on the website:
Bug reference: 13875
Logged by: Travis Smith
Email address: travis.smith@iriworldwide.com
PostgreSQL version: 9.5.0
Operating system: Linux lnx0409.ch3.prod.i.com 2.6.32-358.el6.x86_64
Description:
=# explain select count(1) from (SELECT DISTINCT
"IRI_IT_M_2782_7472_O"."ATTR_VALUE", "IRI_IT_M_2782_7472_O"."SORT_ORDER",
"IT_DIM_IRI_7472"."M_2796_KEY", "IT_DIM_IRI_7472"."M_2782_KEY" FROM
"IT_DIM_IRI_7472", "IRI_IT_M_2782_7472_O" WHERE
("IT_DIM_IRI_7472"."S_848_KEY" = 4527492) AND
("IT_DIM_IRI_7472"."M_2782_KEY" = "IRI_IT_M_2782_7472_O"."AVP_KEY") ORDER BY
"IT_DIM_IRI_7472"."M_2796_KEY" ASC, "IRI_IT_M_2782_7472_O"."SORT_ORDER" ASC,
"IT_DIM_IRI_7472"."M_2782_KEY" ASC ) t;
ERROR: variable not found in subplan target list
travis.smith@iriworldwide.com writes:
> =# explain select count(1) from (SELECT DISTINCT
> "IRI_IT_M_2782_7472_O"."ATTR_VALUE", "IRI_IT_M_2782_7472_O"."SORT_ORDER",
> "IT_DIM_IRI_7472"."M_2796_KEY", "IT_DIM_IRI_7472"."M_2782_KEY" FROM
> "IT_DIM_IRI_7472", "IRI_IT_M_2782_7472_O" WHERE
> ("IT_DIM_IRI_7472"."S_848_KEY" = 4527492) AND
> ("IT_DIM_IRI_7472"."M_2782_KEY" = "IRI_IT_M_2782_7472_O"."AVP_KEY") ORDER BY
> "IT_DIM_IRI_7472"."M_2796_KEY" ASC, "IRI_IT_M_2782_7472_O"."SORT_ORDER" ASC,
> "IT_DIM_IRI_7472"."M_2782_KEY" ASC ) t;
> ERROR: variable not found in subplan target list
What I get is
ERROR: relation "IT_DIM_IRI_7472" does not exist
LINE 4: "IT_DIM_IRI_7472", "IRI_IT_M_2782_7472_O" WHERE
^
This does indeed sound like a bug, but without a self-contained
test case, there's not a lot we can do about it.
regards, tom lane
On Tue, Jan 19, 2016 at 1:17 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > This does indeed sound like a bug, but without a self-contained > test case, there's not a lot we can do about it. Yeah, I was flattening this test case before you sent this email, coming with the attached: create table aa1 (s1 int, m1 int, m2 int); create table aa2 (key int, attr int, sort int); explain select count(1) from (SELECT DISTINCT aa2.attr, aa2.sort, aa1.m1, aa1.m2 FROM aa1, aa2 WHERE (aa1.s1 = 4527492) AND (aa1.m2 = aa2.key) ORDER BY aa1.m1 ASC, aa2.sort ASC, aa1.m2 ASC) t; But this is proving to work. There is not much doable here without a self-contained test case... -- Michael
HI Tom,
Sounds good. I would like to help with the self-contained case. What =
do you need from me?
Thank you,
Travis
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20
Sent: Monday, January 18, 2016 10:18 PM
To: Smith, Travis
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #13875: Error explaining query
travis.smith@iriworldwide.com writes:
> =3D# explain select count(1) from (SELECT DISTINCT=20
> "IRI_IT_M_2782_7472_O"."ATTR_VALUE",=20
> "IRI_IT_M_2782_7472_O"."SORT_ORDER",
> "IT_DIM_IRI_7472"."M_2796_KEY", "IT_DIM_IRI_7472"."M_2782_KEY" FROM=20
> "IT_DIM_IRI_7472", "IRI_IT_M_2782_7472_O" WHERE=20
> ("IT_DIM_IRI_7472"."S_848_KEY" =3D 4527492) AND=20
> ("IT_DIM_IRI_7472"."M_2782_KEY" =3D "IRI_IT_M_2782_7472_O"."AVP_KEY")=20
> ORDER BY "IT_DIM_IRI_7472"."M_2796_KEY" ASC,=20
> "IRI_IT_M_2782_7472_O"."SORT_ORDER" ASC,=20
> "IT_DIM_IRI_7472"."M_2782_KEY" ASC ) t;
> ERROR: variable not found in subplan target list
What I get is
ERROR: relation "IT_DIM_IRI_7472" does not exist LINE 4: "IT_DIM_IRI_7472"=
, "IRI_IT_M_2782_7472_O" WHERE
^
This does indeed sound like a bug, but without a self-contained test case, =
there's not a lot we can do about it.
regards, tom lane
On Tue, Jan 19, 2016 at 7:22 AM, Smith, Travis <
Travis.Smith@iriworldwide.com> wrote:
> HI Tom,
>
> Sounds good. I would like to help with the self-contained case. Wha=
t
> do you need from me?
>
>
>
> Thank you,
> Travis
>
>
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Monday, January 18, 2016 10:18 PM
> To: Smith, Travis
> Cc: pgsql-bugs@postgresql.org
> Subject: Re: [BUGS] BUG #13875: Error explaining query
>
> travis.smith@iriworldwide.com writes:
> > =3D# explain select count(1) from (SELECT DISTINCT
> > "IRI_IT_M_2782_7472_O"."ATTR_VALUE",
> > "IRI_IT_M_2782_7472_O"."SORT_ORDER",
> > "IT_DIM_IRI_7472"."M_2796_KEY", "IT_DIM_IRI_7472"."M_2782_KEY" FROM
> > "IT_DIM_IRI_7472", "IRI_IT_M_2782_7472_O" WHERE
> > ("IT_DIM_IRI_7472"."S_848_KEY" =3D 4527492) AND
> > ("IT_DIM_IRI_7472"."M_2782_KEY" =3D "IRI_IT_M_2782_7472_O"."AVP_KEY")
> > ORDER BY "IT_DIM_IRI_7472"."M_2796_KEY" ASC,
> > "IRI_IT_M_2782_7472_O"."SORT_ORDER" ASC,
> > "IT_DIM_IRI_7472"."M_2782_KEY" ASC ) t;
>
> > ERROR: variable not found in subplan target list
>
> What I get is
>
> ERROR: relation "IT_DIM_IRI_7472" does not exist LINE 4:
> "IT_DIM_IRI_7472", "IRI_IT_M_2782_7472_O" WHERE
> ^
>
=E2=80=8BIdeally, something like:
WITH data_cte AS (
=E2=80=8BVALUES (), (), (), ...=E2=80=8B
)=E2=80=8B
SELECT *
FROM data_cte
Where the main select causes the relevant error.
Otherwise a "psql" script file with the various and sundry CREATE TABLE,
CREATE INDEX, INSERT, SELECT statements that, when fed into "psql", cause
the error.
David J.
VGhpcyB0YWJsZSBpcyB1c2luZyB0aGUgQ29sdW1uYXIgc3RvcmUgZXh0ZW5zaW9uLiAgVGhpcyB3 b3JrZWQgaW4gOS41IGJldGEgbGlrZSBhIGNoYXJtLCBJIHVwZ3JhZGUgYW5kIG5vdyB0aGlzLiAg IEkgY2FuIHByb3ZpZGUgc2NyaXB0IGFuZCBkYXRhLiAgIEFueSBsaW1pdCBvbiBmaWxlIHNpemVz Pw0KDQoNClRoYW5rIHlvdSwNClRyYXZpcw0KDQoNCkZyb206IERhdmlkIEcuIEpvaG5zdG9uIFtt YWlsdG86ZGF2aWQuZy5qb2huc3RvbkBnbWFpbC5jb21dDQpTZW50OiBUdWVzZGF5LCBKYW51YXJ5 IDE5LCAyMDE2IDg6MzUgQU0NClRvOiBTbWl0aCwgVHJhdmlzDQpDYzogVG9tIExhbmU7IHBnc3Fs LWJ1Z3NAcG9zdGdyZXNxbC5vcmcNClN1YmplY3Q6IFJlOiBbQlVHU10gQlVHICMxMzg3NTogRXJy b3IgZXhwbGFpbmluZyBxdWVyeQ0KDQpPbiBUdWUsIEphbiAxOSwgMjAxNiBhdCA3OjIyIEFNLCBT bWl0aCwgVHJhdmlzIDxUcmF2aXMuU21pdGhAaXJpd29ybGR3aWRlLmNvbTxtYWlsdG86VHJhdmlz LlNtaXRoQGlyaXdvcmxkd2lkZS5jb20+PiB3cm90ZToNCkhJIFRvbSwNCg0KU291bmRzIGdvb2Qu ICAgIEkgd291bGQgbGlrZSB0byBoZWxwIHdpdGggdGhlIHNlbGYtY29udGFpbmVkIGNhc2UuICAg IFdoYXQgZG8geW91IG5lZWQgZnJvbSBtZT8NCg0KDQoNClRoYW5rIHlvdSwNClRyYXZpcw0KDQoN Ci0tLS0tT3JpZ2luYWwgTWVzc2FnZS0tLS0tDQpGcm9tOiBUb20gTGFuZSBbbWFpbHRvOnRnbEBz c3MucGdoLnBhLnVzPG1haWx0bzp0Z2xAc3NzLnBnaC5wYS51cz5dDQpTZW50OiBNb25kYXksIEph bnVhcnkgMTgsIDIwMTYgMTA6MTggUE0NClRvOiBTbWl0aCwgVHJhdmlzDQpDYzogcGdzcWwtYnVn c0Bwb3N0Z3Jlc3FsLm9yZzxtYWlsdG86cGdzcWwtYnVnc0Bwb3N0Z3Jlc3FsLm9yZz4NClN1Ympl Y3Q6IFJlOiBbQlVHU10gQlVHICMxMzg3NTogRXJyb3IgZXhwbGFpbmluZyBxdWVyeQ0KDQp0cmF2 aXMuc21pdGhAaXJpd29ybGR3aWRlLmNvbTxtYWlsdG86dHJhdmlzLnNtaXRoQGlyaXdvcmxkd2lk ZS5jb20+IHdyaXRlczoNCj4gPSMgZXhwbGFpbiBzZWxlY3QgY291bnQoMSkgZnJvbSAoU0VMRUNU IERJU1RJTkNUDQo+ICJJUklfSVRfTV8yNzgyXzc0NzJfTyIuIkFUVFJfVkFMVUUiLA0KPiAiSVJJ X0lUX01fMjc4Ml83NDcyX08iLiJTT1JUX09SREVSIiwNCj4gIklUX0RJTV9JUklfNzQ3MiIuIk1f Mjc5Nl9LRVkiLCAiSVRfRElNX0lSSV83NDcyIi4iTV8yNzgyX0tFWSIgRlJPTQ0KPiAiSVRfRElN X0lSSV83NDcyIiwgIklSSV9JVF9NXzI3ODJfNzQ3Ml9PIiBXSEVSRQ0KPiAoIklUX0RJTV9JUklf NzQ3MiIuIlNfODQ4X0tFWSIgPSA0NTI3NDkyKSBBTkQNCj4gKCJJVF9ESU1fSVJJXzc0NzIiLiJN XzI3ODJfS0VZIiA9ICJJUklfSVRfTV8yNzgyXzc0NzJfTyIuIkFWUF9LRVkiKQ0KPiBPUkRFUiBC WSAiSVRfRElNX0lSSV83NDcyIi4iTV8yNzk2X0tFWSIgQVNDLA0KPiAiSVJJX0lUX01fMjc4Ml83 NDcyX08iLiJTT1JUX09SREVSIiBBU0MsDQo+ICJJVF9ESU1fSVJJXzc0NzIiLiJNXzI3ODJfS0VZ IiBBU0MgICkgdDsNCg0KPiBFUlJPUjogIHZhcmlhYmxlIG5vdCBmb3VuZCBpbiBzdWJwbGFuIHRh cmdldCBsaXN0DQoNCldoYXQgSSBnZXQgaXMNCg0KRVJST1I6ICByZWxhdGlvbiAiSVRfRElNX0lS SV83NDcyIiBkb2VzIG5vdCBleGlzdCBMSU5FIDQ6ICJJVF9ESU1fSVJJXzc0NzIiLCAiSVJJX0lU X01fMjc4Ml83NDcyX08iIFdIRVJFDQogICAgICAgIF4NCg0K4oCLSWRlYWxseSwgc29tZXRoaW5n IGxpa2U6DQoNCldJVEggZGF0YV9jdGUgQVMgKA0K4oCLVkFMVUVTICgpLCAoKSwgKCksIC4uLuKA iw0KKeKAiw0KU0VMRUNUICoNCkZST00gZGF0YV9jdGUNCg0KV2hlcmUgdGhlIG1haW4gc2VsZWN0 IGNhdXNlcyB0aGUgcmVsZXZhbnQgZXJyb3IuDQoNCk90aGVyd2lzZSBhICJwc3FsIiBzY3JpcHQg ZmlsZSB3aXRoIHRoZSB2YXJpb3VzIGFuZCBzdW5kcnkgQ1JFQVRFIFRBQkxFLCBDUkVBVEUgSU5E RVgsIElOU0VSVCwgU0VMRUNUIHN0YXRlbWVudHMgdGhhdCwgd2hlbiBmZWQgaW50byAicHNxbCIs IGNhdXNlIHRoZSBlcnJvci4NCg0KRGF2aWQgSi4NCg==
"Smith, Travis" <Travis.Smith@IRIWorldwide.com> writes:
> This table is using the Columnar store extension.
Oh. In that case, the very first thing you should do is inquire whether
that extension is known compatible with 9.5.0. Better yet, try to
reproduce the problem without that extension.
regards, tom lane
HI Tom,
I am doing that now. I was planning a reply, thankfully enough it appears=
a release was done recently.
Thank you,
Travis
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20
Sent: Tuesday, January 19, 2016 11:09 AM
To: Smith, Travis
Cc: David G. Johnston; pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #13875: Error explaining query
"Smith, Travis" <Travis.Smith@IRIWorldwide.com> writes:
> This table is using the Columnar store extension.
Oh. In that case, the very first thing you should do is inquire whether th=
at extension is known compatible with 9.5.0. Better yet, try to reproduce =
the problem without that extension.
regards, tom lane
VGhhbmtzIGd1eXMuICBQcm9ibGVtIHJlc29sdmVkIG5ldyBleHRlbnNpb24gcmVsZWFzZWQgYSBm ZXcgZGF5cyBhZ28uICAgU29ycnkgdG8gYm90aGVyLg0KDQoNClRyYXZpcw0KDQoNCg0KRnJvbTog RGF2aWQgRy4gSm9obnN0b24gW21haWx0bzpkYXZpZC5nLmpvaG5zdG9uQGdtYWlsLmNvbV0NClNl bnQ6IFR1ZXNkYXksIEphbnVhcnkgMTksIDIwMTYgODozNSBBTQ0KVG86IFNtaXRoLCBUcmF2aXMN CkNjOiBUb20gTGFuZTsgcGdzcWwtYnVnc0Bwb3N0Z3Jlc3FsLm9yZw0KU3ViamVjdDogUmU6IFtC VUdTXSBCVUcgIzEzODc1OiBFcnJvciBleHBsYWluaW5nIHF1ZXJ5DQoNCk9uIFR1ZSwgSmFuIDE5 LCAyMDE2IGF0IDc6MjIgQU0sIFNtaXRoLCBUcmF2aXMgPFRyYXZpcy5TbWl0aEBpcml3b3JsZHdp ZGUuY29tPG1haWx0bzpUcmF2aXMuU21pdGhAaXJpd29ybGR3aWRlLmNvbT4+IHdyb3RlOg0KSEkg VG9tLA0KDQpTb3VuZHMgZ29vZC4gICAgSSB3b3VsZCBsaWtlIHRvIGhlbHAgd2l0aCB0aGUgc2Vs Zi1jb250YWluZWQgY2FzZS4gICAgV2hhdCBkbyB5b3UgbmVlZCBmcm9tIG1lPw0KDQoNCg0KVGhh bmsgeW91LA0KVHJhdmlzDQoNCg0KLS0tLS1PcmlnaW5hbCBNZXNzYWdlLS0tLS0NCkZyb206IFRv bSBMYW5lIFttYWlsdG86dGdsQHNzcy5wZ2gucGEudXM8bWFpbHRvOnRnbEBzc3MucGdoLnBhLnVz Pl0NClNlbnQ6IE1vbmRheSwgSmFudWFyeSAxOCwgMjAxNiAxMDoxOCBQTQ0KVG86IFNtaXRoLCBU cmF2aXMNCkNjOiBwZ3NxbC1idWdzQHBvc3RncmVzcWwub3JnPG1haWx0bzpwZ3NxbC1idWdzQHBv c3RncmVzcWwub3JnPg0KU3ViamVjdDogUmU6IFtCVUdTXSBCVUcgIzEzODc1OiBFcnJvciBleHBs YWluaW5nIHF1ZXJ5DQoNCnRyYXZpcy5zbWl0aEBpcml3b3JsZHdpZGUuY29tPG1haWx0bzp0cmF2 aXMuc21pdGhAaXJpd29ybGR3aWRlLmNvbT4gd3JpdGVzOg0KPiA9IyBleHBsYWluIHNlbGVjdCBj b3VudCgxKSBmcm9tIChTRUxFQ1QgRElTVElOQ1QNCj4gIklSSV9JVF9NXzI3ODJfNzQ3Ml9PIi4i QVRUUl9WQUxVRSIsDQo+ICJJUklfSVRfTV8yNzgyXzc0NzJfTyIuIlNPUlRfT1JERVIiLA0KPiAi SVRfRElNX0lSSV83NDcyIi4iTV8yNzk2X0tFWSIsICJJVF9ESU1fSVJJXzc0NzIiLiJNXzI3ODJf S0VZIiBGUk9NDQo+ICJJVF9ESU1fSVJJXzc0NzIiLCAiSVJJX0lUX01fMjc4Ml83NDcyX08iIFdI RVJFDQo+ICgiSVRfRElNX0lSSV83NDcyIi4iU184NDhfS0VZIiA9IDQ1Mjc0OTIpIEFORA0KPiAo IklUX0RJTV9JUklfNzQ3MiIuIk1fMjc4Ml9LRVkiID0gIklSSV9JVF9NXzI3ODJfNzQ3Ml9PIi4i QVZQX0tFWSIpDQo+IE9SREVSIEJZICJJVF9ESU1fSVJJXzc0NzIiLiJNXzI3OTZfS0VZIiBBU0Ms DQo+ICJJUklfSVRfTV8yNzgyXzc0NzJfTyIuIlNPUlRfT1JERVIiIEFTQywNCj4gIklUX0RJTV9J UklfNzQ3MiIuIk1fMjc4Ml9LRVkiIEFTQyAgKSB0Ow0KDQo+IEVSUk9SOiAgdmFyaWFibGUgbm90 IGZvdW5kIGluIHN1YnBsYW4gdGFyZ2V0IGxpc3QNCg0KV2hhdCBJIGdldCBpcw0KDQpFUlJPUjog IHJlbGF0aW9uICJJVF9ESU1fSVJJXzc0NzIiIGRvZXMgbm90IGV4aXN0IExJTkUgNDogIklUX0RJ TV9JUklfNzQ3MiIsICJJUklfSVRfTV8yNzgyXzc0NzJfTyIgV0hFUkUNCiAgICAgICAgXg0KDQri gItJZGVhbGx5LCBzb21ldGhpbmcgbGlrZToNCg0KV0lUSCBkYXRhX2N0ZSBBUyAoDQrigItWQUxV RVMgKCksICgpLCAoKSwgLi4u4oCLDQop4oCLDQpTRUxFQ1QgKg0KRlJPTSBkYXRhX2N0ZQ0KDQpX aGVyZSB0aGUgbWFpbiBzZWxlY3QgY2F1c2VzIHRoZSByZWxldmFudCBlcnJvci4NCg0KT3RoZXJ3 aXNlIGEgInBzcWwiIHNjcmlwdCBmaWxlIHdpdGggdGhlIHZhcmlvdXMgYW5kIHN1bmRyeSBDUkVB VEUgVEFCTEUsIENSRUFURSBJTkRFWCwgSU5TRVJULCBTRUxFQ1Qgc3RhdGVtZW50cyB0aGF0LCB3 aGVuIGZlZCBpbnRvICJwc3FsIiwgY2F1c2UgdGhlIGVycm9yLg0KDQpEYXZpZCBKLg0K