On Thu, 1 Jul 2004, Dennis Bjorklund wrote:
>> \d information_schema.constraint_column_usage
> The thing that does not work is that the SELECT to the left of the UNION
> ALL needs to be put inside (), then it works and the parser can parse it.
>
> Looking at the doc page it looks like the () should not be needed but one
> need to check real grammar (specification) to really know how it should be
> parsed.
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?
I've still not checked any code. I don't even know what part of pg it is
that produce that bad SQL. The view itself works, so it must be the pretty
printer that is broken (where ever that is hidden away in the code).
--
/Dennis Björklund