Re: BUG #6146: COLLATE in ORDER BY not working with column names defined with "AS"

On ons, 2011-08-03 at 21:02 +0000, Matthias Kurz wrote:
> SELECT
>     a.companyname AS a_companyname,
>     a.street,
>     a.zip,
>     a.city,
>     a.country
> FROM
>     myAddress a
> ORDER BY
>      a_companyName COLLATE "C" DESC,
>     a.street COLLATE "C" ASC,
>     a.zip COLLATE "C" ASC,
>     a.city COLLATE "C" ASC,
>     a.country COLLATE "C" ASC
> ---
>
> Gives me following error:
> ---
> ERROR:  column "a_companyname" does not exist
> LINE 26:   a_companyName COLLATE "C" ASC,
> ---

ORDER BY can only refer to output columns by themselves, not as part of
an expression.  This is the same issue that

SELECT a AS x FROM foo ORDER BY x

works but

SELECT a AS x FROM foo ORDER BY x + 1

doesn't.

What is perhaps not obvious is that (a_companyName COLLATE "C") is an
expression.  The COLLATE clause is not specifically part of the ORDER BY
syntax, but a general expression.

> Is this the right behaviour?
> Or a bug?

Well, it works as designed and documented, and it is consistent with
other behaviors, as I showed.  The SQL standard is sufficiently murky on
this subject, however, that I can't tell right now whether this is how
it is supposed to work.  But it looks like someone researched this
carefully in the past, so probably yes.