Обсуждение: 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