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

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

--
Best regards,
Vitaly Burovoy