Re: ORDER BY col is NULL in UNION causes error?

Tom Lane wrote:
> Michael Glaesemann <grzm(at)seespotcode(dot)net> writes:
> > On Dec 26, 2006, at 18:39 , Mike Benoit wrote:
> >> ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of
> >> the result columns
>
> > Even though state is a column in both tables, the order by is using
> > an expression, rather than a column.
> > ...
> > I'm not sure of the underlying reasons why your query doesn't work,
> > but give these a shot.
>
> There are some implementation reasons for not supporting expressions
> computed on a UNION result without an intervening sub-SELECT. It's too
> late at night for me to recall exactly what they are :-( --- one is that
> an Append plan node doesn't do any expression evaluation, but I think
> there are some more-subtle issues too. Suffice it to say that we could
> support this if we wanted to throw enough effort at it, but so far other
> problems have seemed more pressing.
>
> In the meantime, it seems like the above-quoted error message is not
> clear enough, since Mike failed to get the point that "the ORDER BY
> item has to be just a simple column name of the UNION output". Anyone
> have a suggestion for better wording?

I have updated the wording from

"ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of the result columns")));

to:

"ORDER BY on a UNION/INTERSECT/EXCEPT result must match existing result columns")));

The 'match' wording might help, rather then 'use'.

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +