Re: union all bug?

Probably this explains the ERROR for the last query... The ORDER BY
and LIMIT clauses are expected to end a query (except for subqueries,
of course), and hence the keyword UNION is not expected after the
LIMIT clause...

On 6/18/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Joe Conway <mail@joeconway.com> writes:
> > I was trying to work around limitations with "partitioning" of tables
> > using constraint exclusion, when I ran across this little oddity:
>
> I think you're under a misimpression about the syntax behavior of ORDER
> BY and UNION.  Per spec, ORDER BY binds less tightly than UNION, thus
>
>         select foo union select bar order by x
>
> means
>
>         (select foo union select bar) order by x
>
> If you want to apply ORDER BY to either arm of a union individually,
> you need parentheses, eg
>
>         (select foo order by x) union (select bar order by x)
>
> (Note that this construct fails to guarantee that the output of the
> union will be sorted by x!)  LIMIT is not in the spec but we treat
> it like ORDER BY for this purpose.

To guarantee the ordering, one can use

select * from (select foo from A union select bar from B) order by x