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.