Skip site navigation (1) Skip section navigation (2)

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

From: Peter Eisentraut <peter_e(at)gmx(dot)net>
To: Matthias Kurz <m(dot)kurz(at)irregular(dot)at>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #6146: COLLATE in ORDER BY not working with column names defined with "AS"
Date: 2011-08-06 08:53:45
Message-ID: 1312620825.24721.8.camel@vanquo.pezone.net (view raw or flat)
Thread:
Lists: pgsql-bugs
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.



In response to

pgsql-bugs by date

Next:From: John Carriel GomezDate: 2011-08-07 20:10:33
Subject: Help-PGRES_FATAL_ERROR
Previous:From: Craig RingerDate: 2011-08-06 02:02:38
Subject: Re: BUG #6143: connection problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group