Re: Bug with view definitions?

On Thu, Jul 01, 2004 at 15:19:32 +0200, Dennis Bjorklund <db@zigo.dhs.org> wrote:
> 
> Looking again at the doc and the example I now know why it can't parse 
> it. The example when simplified is:
> 
> SELECT *
>   FROM (select 1 ORDER BY 1
>         UNION ALL
>         select 2) AS x;
> 
> and it does not parse since the there is an ORDER BY in the first query. 
> If we look at the doc page then the UNION comes before the ORDER BY, so it 
> is in fact an invalid query (I've not checke the standard, just the select 
> doc page).
> 
> If you put a () around the first (inner) select it all works. But why 
> is the order by there at all? The order of the rows from the UNION ALL can 
> (in theory) be random anyway, right?

If DISTINCT ON or LIMIT was used in inner select, then the ORDER BY would
be relevant; so you can't just blindly remove ORDER BY when it is part of
a union.