The following bug has been logged online:
Bug reference: 6146
Logged by: Matthias Kurz
Email address: m.k...@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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
