Richard Huxton <dev@archonet.com> writes:
> Since the two queries work separately, I'd guess PG is trying to locate
> prod_type_id via a different route when unioned. What happens if you qualify
> all the column-names?
ORDER BY applied to a UNION result can only order by the column names
visible in the UNION result. Consider for example
SELECT x1 AS a FROM foo
UNION
SELECT y1 AS a FROM bar
ORDER BY ???
where foo and bar have no column names in common. The *only* thing
that's sensible to order by is "a" --- and no qualification, mind you.
Anything else you might try to order by is not available in one or the
other arm of the UNION.
The SQL92 spec is very rigid about this, and so is Postgres. You could
imagine ordering by, say, UPPER(a), but we don't support that extension
at present (unlike the situation for ORDER BY in non-UNION queries,
where we're quite lax).
regards, tom lane