Hi

We have a strange problem when searching for a string using <= and >=
operators on Solaris.
It did work with 7.3, but not with 7.3.3, 7.3.4 and 7.4.1.
The database have been initialized with locale en_US.ISO8859-1

Have a simple table testtbl:
create table testtbl ( id int, name varchar(20));
Insert this record:
insert into testtbl values(10, 'PEREZ');
Now do that search:
select * from testtbl where name >= 'PERE' and name <= 'PERE\377';

It should return the record of the table, but it will not return it with
version 7.3.3 and later.
We still found that workaround works:
select * from testtbl where name >= 'PERE' and name <= 'PEREzz';

Which is pretty strange...
Any comments about that? Is it a bug?

Thanks,
Patrick

_________________________________________________________________
Add photos to your messages with MSN 8. Get 2 months FREE*.
http://join.msn.com/?page=dept/features&pgmarket=en-ca&RU=http%3a%2f%2fjoin.msn.com%2f%3fpage%3dmisc%2fspecialoffers%26pgmarket%3den-ca

Search Discussions

  • Tom Lane at Feb 26, 2004 at 7:33 pm

    "Patrick Vachon" <kaleidelf@hotmail.com> writes:
    We have a strange problem when searching for a string using <= and >=
    operators on Solaris.
    It did work with 7.3, but not with 7.3.3, 7.3.4 and 7.4.1.
    The database have been initialized with locale en_US.ISO8859-1
    Have a simple table testtbl:
    create table testtbl ( id int, name varchar(20));
    Insert this record:
    insert into testtbl values(10, 'PEREZ');
    Now do that search:
    select * from testtbl where name >= 'PERE' and name <= 'PERE\377';
    I would expect this not to find PEREZ in that locale, no matter which PG
    version you use. Try putting the three strings in a file and sorting
    them with the sort(1) command-line tool --- in en_US locale you'll
    likely find that 'PERE\377' comes between the others. For instance,
    on HPUX I get:

    $ cat zzz
    PEREZ
    PERE
    PEREÿ
    $ LC_ALL=C sort zzz
    PERE
    PEREZ
    PEREÿ
    $ LC_ALL=en_US.iso88591 sort zzz
    PERE
    PEREÿ
    PEREZ
    $

    My guess is that your 7.3 installation used C locale, and you failed to
    reproduce that setting when you installed the newer versions.

    regards, tom lane
  • Patrick Vachon at Feb 26, 2004 at 10:32 pm
    It is quite possible!
    I will have to test that to confirm.
    Is there any way to change the locale without having to re-initdb?

    Thanks
    Patrick

    From: Tom Lane <tgl@sss.pgh.pa.us>
    To: "Patrick Vachon" <kaleidelf@hotmail.com>
    CC: pgsql-ports@postgresql.org
    Subject: Re: [PORTS] String operators <= and >= Date: Thu, 26 Feb 2004
    14:33:19 -0500

    "Patrick Vachon" writes:
    We have a strange problem when searching for a string using <= and >=
    operators on Solaris.
    It did work with 7.3, but not with 7.3.3, 7.3.4 and 7.4.1.
    The database have been initialized with locale en_US.ISO8859-1
    Have a simple table testtbl:
    create table testtbl ( id int, name varchar(20));
    Insert this record:
    insert into testtbl values(10, 'PEREZ');
    Now do that search:
    select * from testtbl where name >= 'PERE' and name <= 'PERE\377';
    I would expect this not to find PEREZ in that locale, no matter which PG
    version you use. Try putting the three strings in a file and sorting
    them with the sort(1) command-line tool --- in en_US locale you'll
    likely find that 'PERE\377' comes between the others. For instance,
    on HPUX I get:

    $ cat zzz
    PEREZ
    PERE
    PEREÿ
    $ LC_ALL=C sort zzz
    PERE
    PEREZ
    PEREÿ
    $ LC_ALL=en_US.iso88591 sort zzz
    PERE
    PEREÿ
    PEREZ
    $

    My guess is that your 7.3 installation used C locale, and you failed to
    reproduce that setting when you installed the newer versions.

    regards, tom lane
    _________________________________________________________________
    Tired of spam? Get advanced junk mail protection with MSN 8.
    http://join.msn.com/?page=dept/bcomm&pgmarket=en-ca&RU=http%3a%2f%2fjoin.msn.com%2f%3fpage%3dmisc%2fspecialoffers%26pgmarket%3den-ca
  • Tom Lane at Feb 26, 2004 at 10:47 pm

    "Patrick Vachon" <kaleidelf@hotmail.com> writes:
    Is there any way to change the locale without having to re-initdb?
    Nope :-(.

    regards, tom lane

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-ports @
categoriespostgresql
postedFeb 26, '04 at 7:17p
activeFeb 26, '04 at 10:47p
posts4
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Patrick Vachon: 2 posts Tom Lane: 2 posts

People

Translate

site design / logo © 2022 Grokbase