Обсуждение: BUG #14316: union all with order by "missing from-clause"
VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDMxNgpMb2dnZWQgYnk6ICAg ICAgICAgIE9zc2kgS2FzdXJpbmVuCkVtYWlsIGFkZHJlc3M6ICAgICAgb3p6 aV85OUBob3RtYWlsLmNvbQpQb3N0Z3JlU1FMIHZlcnNpb246IDkuNS40Ck9w ZXJhdGluZyBzeXN0ZW06ICAgd2luZG93cyA4LjEgcHJvCkRlc2NyaXB0aW9u OiAgICAgICAgCgovKkkgY2Fubm90IGRvICJ1bmlvbiBhbGwiIG9wZXJhdGlv biB3aXRoICJvcmRlciBieSIgdG8gYW5vdGhlciB0YWJsZSB0aGFuCnRoZSBw cmltYXJ5Lg0KDQpleGFtcGxlIHRhYmxlcyBhbmQgc2VsZWN0IGNsYXVzZToq Lw0KLS10YWJsZSBjcmVhdGlvbjogYmFyDQpDUkVBVEUgVEFCTEUgcHVibGlj LmJhcg0KKA0KICBpZCBpbnRlZ2VyIE5PVCBOVUxMLA0KICBzb3J0Y29sdW1u IGludGVnZXIsDQogIENPTlNUUkFJTlQgcGtfYmFyIFBSSU1BUlkgS0VZIChp ZCkNCikNCi0tdGFibGUgY3JlYXRpb246Zm9vDQpDUkVBVEUgVEFCTEUgcHVi bGljLmZvbw0KKA0KICBpZCBpbnRlZ2VyIE5PVCBOVUxMLA0KICBiYXJpZCBp bnRlZ2VyLA0KICBzb3J0Y29sdW1uIGludGVnZXIsDQogIENPTlNUUkFJTlQg cGtfZm9vIFBSSU1BUlkgS0VZIChpZCksDQogIENPTlNUUkFJTlQgZmtfc2Vj b25kIEZPUkVJR04gS0VZIChiYXJpZCkNCiAgICAgIFJFRkVSRU5DRVMgcHVi bGljLmJhciAoaWQpIE1BVENIIFNJTVBMRQ0KICAgICAgT04gVVBEQVRFIENB U0NBREUgT04gREVMRVRFIENBU0NBREUNCikNCg0KLS0gaW5zZXJ0IHNvbWUg ZHVtbXkgZGF0YS4NCi0tIHRoZSBmb2xsb3dpbmcgZG9lc24ndCB3b3JrDQoN CnNlbGVjdCBvcmlnaW4uaWQsIG9yaWdpbi5iYXJpZCwgb3JpZ2luLnNvcnRj b2x1bW4gZnJvbSBmb28gb3JpZ2luDQoJaW5uZXIgam9pbiBiYXIgb24gb3Jp Z2luLmJhcklkID0gYmFyLklkDQp1bmlvbiBhbGwNCnNlbGVjdCBhLmlkLCBh LmJhcmlkLCBhLnNvcnRjb2x1bW4gZnJvbSBmb28gYXMgYQ0KCWlubmVyIGpv aW4gYmFyIGFzIGIgb24gYS5iYXJpZCA9IGIuaWQNCglvcmRlciBieSBiLnNv cnRjb2x1bW4NCglsaW1pdCAxDQoNCi8qIElmIEkgdGFrZSAiYiIgYXdheSBm cm9tIG9yZGVyIGJ5LCBpdCB3aWxsIG9yZGVyIGJ5IHRhYmxlIGZvbywgYW5k IGl0CmdpdmVzIGluY29ycmVjdCByZXN1bHRzLiAqLwoK
On 9/8/16, ozzi_99@hotmail.com <ozzi_99@hotmail.com> wrote: > The following bug has been logged on the website: > > Bug reference: 14316 > Logged by: Ossi Kasurinen > Email address: ozzi_99@hotmail.com > PostgreSQL version: 9.5.4 > Operating system: windows 8.1 pro > Description: > > /*I cannot do "union all" operation with "order by" to another table than > the primary. > > example tables and select clause:*/ > --table creation: bar > CREATE TABLE public.bar > ( > id integer NOT NULL, > sortcolumn integer, > CONSTRAINT pk_bar PRIMARY KEY (id) > ) > --table creation:foo > CREATE TABLE public.foo > ( > id integer NOT NULL, > barid integer, > sortcolumn integer, > CONSTRAINT pk_foo PRIMARY KEY (id), > CONSTRAINT fk_second FOREIGN KEY (barid) > REFERENCES public.bar (id) MATCH SIMPLE > ON UPDATE CASCADE ON DELETE CASCADE > ) > > -- insert some dummy data. > -- the following doesn't work > > select origin.id, origin.barid, origin.sortcolumn from foo origin > inner join bar on origin.barId = bar.Id > union all > select a.id, a.barid, a.sortcolumn from foo as a > inner join bar as b on a.barid = b.id > order by b.sortcolumn > limit 1 > > /* If I take "b" away from order by, it will order by table foo, and it > gives incorrect results. */ > It is not a bug. You have to wrap union by parenthesis if you want to order result of union. It solves ambiguousness to which part order and limit should be applied ( select origin.id, origin.barid, origin.sortcolumn from foo origin inner join bar on origin.barId = bar.Id union all select a.id, a.barid, a.sortcolumn from foo as a inner join bar as b on a.barid = b.id ) order by sortcolumn limit 1 -- Best regards, Vitaly Burovoy
On 9/8/16, Vitaly Burovoy <vitaly.burovoy@gmail.com> wrote: > On 9/8/16, ozzi_99@hotmail.com <ozzi_99@hotmail.com> wrote: >> The following bug has been logged on the website: >> >> Bug reference: 14316 >> Logged by: Ossi Kasurinen >> Email address: ozzi_99@hotmail.com >> PostgreSQL version: 9.5.4 >> Operating system: windows 8.1 pro >> Description: >> >> /*I cannot do "union all" operation with "order by" to another table than >> the primary. >> >> example tables and select clause:*/ >> --table creation: bar >> CREATE TABLE public.bar >> ( >> id integer NOT NULL, >> sortcolumn integer, >> CONSTRAINT pk_bar PRIMARY KEY (id) >> ) >> --table creation:foo >> CREATE TABLE public.foo >> ( >> id integer NOT NULL, >> barid integer, >> sortcolumn integer, >> CONSTRAINT pk_foo PRIMARY KEY (id), >> CONSTRAINT fk_second FOREIGN KEY (barid) >> REFERENCES public.bar (id) MATCH SIMPLE >> ON UPDATE CASCADE ON DELETE CASCADE >> ) >> >> -- insert some dummy data. >> -- the following doesn't work >> >> select origin.id, origin.barid, origin.sortcolumn from foo origin >> inner join bar on origin.barId = bar.Id >> union all >> select a.id, a.barid, a.sortcolumn from foo as a >> inner join bar as b on a.barid = b.id >> order by b.sortcolumn >> limit 1 >> >> /* If I take "b" away from order by, it will order by table foo, and it >> gives incorrect results. */ >> > > It is not a bug. > > You have to wrap union by parenthesis if you want to order result of > union. It solves ambiguousness to which part order and limit should be > applied > > ( > select origin.id, origin.barid, origin.sortcolumn from foo origin > inner join bar on origin.barId = bar.Id > union all > select a.id, a.barid, a.sortcolumn from foo as a > inner join bar as b on a.barid = b.id > ) > order by sortcolumn > limit 1 P.S.: of course, if you want to select unsorted data from the first table and ordered data from the second one, you just have to wrap the second query: select origin.id, origin.barid, origin.sortcolumn from foo origin inner join bar on origin.barId = bar.Id union all ( select a.id, a.barid, a.sortcolumn from foo as a inner join bar as b on a.barid = b.id order by b.sortcolumn limit 1 ) -- Best regards, Vitaly Burovoy