Обсуждение: BUG #6146: COLLATE in ORDER BY not working with column names defined with "AS"
BUG #6146: COLLATE in ORDER BY not working with column names defined with "AS"
От
"Matthias Kurz"
Дата:
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
Re: BUG #6146: COLLATE in ORDER BY not working with column names defined with "AS"
От
Peter Eisentraut
Дата:
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.