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

On Thu, 2007-01-11 at 13:44 -0500, Bruce Momjian wrote:
> 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'.

That helps some, but I'm sure it could be even more clear.

The main issue is that you can't order by an expression computed by
unions, correct? So couldn't the error message by something like:

"ORDER BY on a UNION/INTERSECT/EXCEPT result must match existing result
columns and not be an expression")));

>
--
Mike Benoit <ipso(at)snappymail(dot)ca>