Re: BUG #14316: union all with order by "missing from-clause"

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