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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Michael Glaesemann <grzm(at)seespotcode(dot)net>
Cc: Mike Benoit <ipso(at)snappymail(dot)ca>, pgsql-general(at)postgresql(dot)org
Subject: Re: ORDER BY col is NULL in UNION causes error?
Date: 2006-12-27 03:26:03
Message-ID: 11199.1167189963@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

regards, tom lane

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Sim Zacks 2006-12-27 06:55:24 array quotation problem
Previous Message Michael Glaesemann 2006-12-27 01:39:14 Re: ORDER BY col is NULL in UNION causes error?