Thread: collation problem on 9.1-beta1

collation problem on 9.1-beta1

From
Marc Cousin
Date:
Hi,

I've been starting to work on a 'what's new in 9.1' like i did last
year, and am faced with what I feel is a bug, while building a demo case
for collation.

Here it is:

SELECT * from (values ('llegar'),('llorer'),('lugar')) as tmp
order by 1 collate "es_ES.utf8";
ERROR:  collations are not supported by type integer at character 74
STATEMENT:  SELECT * from (values ('llegar'),('llorer'),('lugar')) as
tmp
order by 1 collate "es_ES.utf8";
                                                             ^
marc=# SELECT * from (values ('llegar'),('llorer'),('lugar')) as tmp
order by column1 collate "es_ES.utf8";
 column1
---------
 llegar
 llorer
 lugar
(3 rows)

Of course, without the collate keyword, the «order by 1» works as usual.

Regards

Marc

Re: collation problem on 9.1-beta1

From
Tom Lane
Date:
Marc Cousin <cousinmarc@gmail.com> writes:
> I've been starting to work on a 'what's new in 9.1' like i did last
> year, and am faced with what I feel is a bug, while building a demo case
> for collation.

> Here it is:

> SELECT * from (values ('llegar'),('llorer'),('lugar')) as tmp
> order by 1 collate "es_ES.utf8";
> ERROR:  collations are not supported by type integer at character 74

This isn't a bug, or at least not one we're going to fix.  ORDER BY
column-number is a legacy syntax that doesn't support many options, and
COLLATE is one of the ones that it doesn't support.

(The actual technical reason for this is that COLLATE turns the argument
into a general expression, not something we can special-case.  You would
get the same error from writing "1 COLLATE something" anyplace else.)

            regards, tom lane

Re: collation problem on 9.1-beta1

From
Peter Eisentraut
Date:
On ons, 2011-05-11 at 14:58 -0400, Tom Lane wrote:
> Marc Cousin <cousinmarc@gmail.com> writes:
> > I've been starting to work on a 'what's new in 9.1' like i did last
> > year, and am faced with what I feel is a bug, while building a demo case
> > for collation.
>
> > Here it is:
>
> > SELECT * from (values ('llegar'),('llorer'),('lugar')) as tmp
> > order by 1 collate "es_ES.utf8";
> > ERROR:  collations are not supported by type integer at character 74
>
> This isn't a bug, or at least not one we're going to fix.  ORDER BY
> column-number is a legacy syntax that doesn't support many options, and
> COLLATE is one of the ones that it doesn't support.
>
> (The actual technical reason for this is that COLLATE turns the argument
> into a general expression, not something we can special-case.  You would
> get the same error from writing "1 COLLATE something" anyplace else.)

Well, I'm just counting how many more people are going to complain about
this before fixing it.  Possibly in 9.2.

Re: collation problem on 9.1-beta1

From
Marc Cousin
Date:
On 02/06/2011 14:09, Peter Eisentraut wrote:
> On ons, 2011-05-11 at 14:58 -0400, Tom Lane wrote:
>> Marc Cousin<cousinmarc@gmail.com>  writes:
>>> I've been starting to work on a 'what's new in 9.1' like i did last
>>> year, and am faced with what I feel is a bug, while building a demo case
>>> for collation.
>>
>>> Here it is:
>>
>>> SELECT * from (values ('llegar'),('llorer'),('lugar')) as tmp
>>> order by 1 collate "es_ES.utf8";
>>> ERROR:  collations are not supported by type integer at character 74
>>
>> This isn't a bug, or at least not one we're going to fix.  ORDER BY
>> column-number is a legacy syntax that doesn't support many options, and
>> COLLATE is one of the ones that it doesn't support.
>>
>> (The actual technical reason for this is that COLLATE turns the argument
>> into a general expression, not something we can special-case.  You would
>> get the same error from writing "1 COLLATE something" anyplace else.)
>
> Well, I'm just counting how many more people are going to complain about
> this before fixing it.  Possibly in 9.2.
I wasn't complaining at all, just wondering if this was intended or not :)

But I'm sure that I won't be the only one to be caught by this, as it
took me about ten minutes to fall in this trap. That was the main reason
I reported this problem in the first place :)

Re: collation problem on 9.1-beta1

From
Bruce Momjian
Date:
Peter Eisentraut wrote:
> On ons, 2011-05-11 at 14:58 -0400, Tom Lane wrote:
> > Marc Cousin <cousinmarc@gmail.com> writes:
> > > I've been starting to work on a 'what's new in 9.1' like i did last
> > > year, and am faced with what I feel is a bug, while building a demo case
> > > for collation.
> >
> > > Here it is:
> >
> > > SELECT * from (values ('llegar'),('llorer'),('lugar')) as tmp
> > > order by 1 collate "es_ES.utf8";
> > > ERROR:  collations are not supported by type integer at character 74
> >
> > This isn't a bug, or at least not one we're going to fix.  ORDER BY
> > column-number is a legacy syntax that doesn't support many options, and
> > COLLATE is one of the ones that it doesn't support.

Column numbers in ORDER BY is ANSI syntax so I don't think calling them
"legacy" is accurate.  "limited functionality"?

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

Re: collation problem on 9.1-beta1

From
Peter Eisentraut
Date:
On tor, 2011-06-09 at 13:30 -0400, Bruce Momjian wrote:
> Column numbers in ORDER BY is ANSI syntax so I don't think calling
> them "legacy" is accurate.  "limited functionality"?

It was in SQL 92, but removed in 99, so it's technically no longer part
of the standard.

Re: collation problem on 9.1-beta1

From
Robert Haas
Date:
On Thu, Jun 9, 2011 at 2:19 PM, Peter Eisentraut <peter_e@gmx.net> wrote:
> On tor, 2011-06-09 at 13:30 -0400, Bruce Momjian wrote:
>> Column numbers in ORDER BY is ANSI syntax so I don't think calling
>> them "legacy" is accurate. =A0"limited functionality"?
>
> It was in SQL 92, but removed in 99, so it's technically no longer part
> of the standard.

It's still extremely widely used though, I think, and very useful.  I
don't feel we have to support GROUP BY 1 COLLATE whatever, but it
might be worth the trouble to at least emit a decent HINT.

--=20
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: collation problem on 9.1-beta1

From
Bruce Momjian
Date:
Robert Haas wrote:
> On Thu, Jun 9, 2011 at 2:19 PM, Peter Eisentraut <peter_e@gmx.net> wrote:
> > On tor, 2011-06-09 at 13:30 -0400, Bruce Momjian wrote:
> >> Column numbers in ORDER BY is ANSI syntax so I don't think calling
> >> them "legacy" is accurate. ?"limited functionality"?
> >
> > It was in SQL 92, but removed in 99, so it's technically no longer part
> > of the standard.

Wow, they removed it;  I use it all the time.

> It's still extremely widely used though, I think, and very useful.  I
> don't feel we have to support GROUP BY 1 COLLATE whatever, but it
> might be worth the trouble to at least emit a decent HINT.

True.  Seems now these numbers are PG extensions!

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +