Re: BUG #14316: union all with order by "missing from-clause" - Mailing list pgsql-bugs

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