Обсуждение: BUG #6146: COLLATE in ORDER BY not working with column names defined with "AS"

The following bug has been logged online:

Bug reference:      6146
Logged by:          Matthias Kurz
Email address:      m.kurz@irregular.at
PostgreSQL version: 9.1beta3
Operating system:   Ubuntu 11.04 64 bit
Description:        COLLATE in ORDER BY not working with column names
defined with "AS"
Details:

Running following script:
---
DROP TABLE IF EXISTS myAddress;

CREATE TABLE myAddress (
    id SERIAL NOT NULL,
    companyname CHARACTER VARYING(50),
    street CHARACTER VARYING(50) NOT NULL,
    zip CHARACTER VARYING(50) NOT NULL,
    city CHARACTER VARYING(50) NOT NULL,
    country CHARACTER VARYING(50)
);

INSERT INTO myAddress VALUES (DEFAULT, 'Company 2', 'Street a', '1001',
'Austria');
INSERT INTO myAddress VALUES (DEFAULT, 'Company 1', 'Street ä', '1002',
'Switzerland');
INSERT INTO myAddress VALUES (DEFAULT, 'Company 3', 'Street A', '1003',
'Italy');
INSERT INTO myAddress VALUES (DEFAULT, 'Company 4', 'Street Ä', '1004',
'France');

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,
---

When removing the COLLATE "C" for line 26 (in this line only! the others are
still present), it works.
After this change I tried different things e.g. changed ASC to DESC or used
"en_GB.utf8" instead of "C" collate. Everything worked as expected.

So the COLLATE statement is honored in the ORDER BY clause except when using
it on a column which was defined explicitly with an "AS" statement.

Is this the right behaviour?
Or a bug?


Greetings,

Matthias

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.