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

On 9/8/16, Vitaly Burovoy <vitaly(dot)burovoy(at)gmail(dot)com> wrote:
> On 9/8/16, ozzi_99(at)hotmail(dot)com <ozzi_99(at)hotmail(dot)com> wrote:
>> The following bug has been logged on the website:
>>
>> Bug reference: 14316
>> Logged by: Ossi Kasurinen
>> Email address: ozzi_99(at)hotmail(dot)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