BUG #3394: Partial search not working

Murali Doss wrote:

Partial search query is not working Linux OS with postgresql 8.2.4 but the
same is working fine in windows and solaris OS.

I like to know whether i need to run some patch in linux OS to make the
partial search to work.

SELECT * FROM tablename WHERE colname BETWEEN 'B-' AND 'B~' ORDER BY
colname

What do you mean by "not working"? Does it give an error message?
Unexpected results?

Are you perhaps using a different locale on different platforms?

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Murali Doss wrote:

The following bug has been logged online:

Bug reference: 3394
Logged by: Murali Doss
Email address: murali.doss@mphasis.com
PostgreSQL version: 8.2.4
Operating system: Linux
Description: Partial search not working
Details:

Dear All,

Partial search query is not working Linux OS with postgresql 8.2.4 but the
same is working fine in windows and solaris OS.

I don't think that it depends on OS. What is difference between working
and not working search?

I like to know whether i need to run some patch in linux OS to make the
partial search to work.

SELECT * FROM tablename WHERE colname BETWEEN 'B-' AND 'B~' ORDER BY
colname

Can you run EXPLAIN on your query and look if it is same on all OS?

Zdenek

No result but the matching data is available in table

-----Original Message-----
From: Heikki Linnakangas [mailto:hlinnaka@gmail.com] On Behalf Of Heikki
Linnakangas
Sent: Tuesday, June 19, 2007 1:17 PM
To: Murali Doss
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #3394: Partial search not working

Murali Doss wrote:

Partial search query is not working Linux OS with postgresql 8.2.4 but

the

same is working fine in windows and solaris OS.

I like to know whether i need to run some patch in linux OS to make

the

partial search to work.

SELECT * FROM tablename WHERE colname BETWEEN 'B-' AND 'B~' ORDER BY
colname

What do you mean by "not working"? Does it give an error message?
Unexpected results?

Are you perhaps using a different locale on different platforms?

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Tue, Jun 19, 2007 at 02:55:07PM +0530, Murali Doss wrote:

No result but the matching data is available in table

What values are not matching that you expect to match? Can you
provide a reproducible test case? What are your locale settings
and encoding?

--
Michael Fuhr

Hi Michael,

Same database dump is there Solaris, Windows and Linux and the partial
search query is displaying the expected results in Solaris and Windows
OS but the query returning 0 row in Linux OS.

SELECT * FROM tablename WHERE colname BETWEEN 'B' AND 'B~';

Any query to find the local settings and encoding.

Regards
Murali Doss T.S.

-----Original Message-----
From: Michael Fuhr [mailto:mike@fuhr.org]
Sent: Tuesday, June 19, 2007 3:51 PM
To: Murali Doss
Cc: Heikki Linnakangas; pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #3394: Partial search not working

On Tue, Jun 19, 2007 at 02:55:07PM +0530, Murali Doss wrote:

No result but the matching data is available in table

What values are not matching that you expect to match? Can you
provide a reproducible test case? What are your locale settings
and encoding?

--
Michael Fuhr

Murali Doss wrote:

Hi Michael,

Same database dump is there Solaris, Windows and Linux and the partial
search query is displaying the expected results in Solaris and Windows
OS but the query returning 0 row in Linux OS.

SELECT * FROM tablename WHERE colname BETWEEN 'B' AND 'B~';

Any query to find the local settings and encoding.

Try

SELECT name, setting FROM pg_settings WHERE name LIKE 'lc_%';

The psql -l command shows you database encoding. Client and server
encoding is possible get by the following commands:

SHOW client_encoding and SHOW server_encoding

Zdenek

Hi Zdenek,

Thanks for your effort.

Database encoding for Solaris OS is SQL_ASCII but it's UTF8 in Linux OS.
How to change the database encoding to sql_ascii for linux OS?

Regards
Murali Doss T.S.

-----Original Message-----
From: Zdenek.Kotala@Sun.COM [mailto:Zdenek.Kotala@Sun.COM]
Sent: Tuesday, June 19, 2007 5:31 PM
To: Murali Doss
Cc: Michael Fuhr; Heikki Linnakangas; pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #3394: Partial search not working

Murali Doss wrote:

Hi Michael,

Same database dump is there Solaris, Windows and Linux and the partial
search query is displaying the expected results in Solaris and Windows
OS but the query returning 0 row in Linux OS.

SELECT * FROM tablename WHERE colname BETWEEN 'B' AND 'B~';

Any query to find the local settings and encoding.

Try

SELECT name, setting FROM pg_settings WHERE name LIKE 'lc_%';

The psql -l command shows you database encoding. Client and server
encoding is possible get by the following commands:

SHOW client_encoding and SHOW server_encoding

Zdenek

AFAIK, you will need to recreate the database. See "CREATE DATABASE"
command for details, but something like "CREATE DATABASE FOO WITH
ENCODING 'SQL_ASCII'" should do it... if my memory serves me.

Of course, you'll need to dump and reload your data.

On Jun 19, 2007, at 8:51 AM, Murali Doss wrote:

Hi Zdenek,

Thanks for your effort.

Database encoding for Solaris OS is SQL_ASCII but it's UTF8 in
Linux OS.
How to change the database encoding to sql_ascii for linux OS?

Regards
Murali Doss T.S.

-----Original Message-----
From: Zdenek.Kotala@Sun.COM [mailto:Zdenek.Kotala@Sun.COM]
Sent: Tuesday, June 19, 2007 5:31 PM
To: Murali Doss
Cc: Michael Fuhr; Heikki Linnakangas; pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #3394: Partial search not working

Murali Doss wrote:

Hi Michael,

Same database dump is there Solaris, Windows and Linux and the
partial
search query is displaying the expected results in Solaris and
Windows
OS but the query returning 0 row in Linux OS.

SELECT * FROM tablename WHERE colname BETWEEN 'B' AND 'B~';

Any query to find the local settings and encoding.

Try

SELECT name, setting FROM pg_settings WHERE name LIKE 'lc_%';

The psql -l command shows you database encoding. Client and server
encoding is possible get by the following commands:

SHOW client_encoding and SHOW server_encoding

Zdenek

---------------------------(end of
broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Douglas Toltzman
doug@oakstreetsoftware.com
(910) 526-5938

I have created new database with encoding as sql_ascii and reloaded the
data but still its returning 0 rows in Linux.

SELECT * FROM tablename WHERE colname BETWEEN 'B' AND 'B~';

________________________________

From: Douglas Toltzman [mailto:doug@oakstreetsoftware.com]
Sent: Tuesday, June 19, 2007 6:44 PM
To: Murali Doss
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #3394: Partial search not working

AFAIK, you will need to recreate the database. See "CREATE DATABASE"
command for details, but something like "CREATE DATABASE FOO WITH
ENCODING 'SQL_ASCII'" should do it... if my memory serves me.

Of course, you'll need to dump and reload your data.

On Jun 19, 2007, at 8:51 AM, Murali Doss wrote:

Hi Zdenek,

Thanks for your effort.

Database encoding for Solaris OS is SQL_ASCII but it's UTF8 in Linux OS.

How to change the database encoding to sql_ascii for linux OS?

Regards

Murali Doss T.S.

-----Original Message-----

From: Zdenek.Kotala@Sun.COM [mailto:Zdenek.Kotala@Sun.COM]

Sent: Tuesday, June 19, 2007 5:31 PM

To: Murali Doss

Cc: Michael Fuhr; Heikki Linnakangas; pgsql-bugs@postgresql.org

Subject: Re: [BUGS] BUG #3394: Partial search not working

Murali Doss wrote:

Hi Michael,

Same database dump is there Solaris, Windows and Linux and the
partial

search query is displaying the expected results in Solaris and
Windows

OS but the query returning 0 row in Linux OS.

SELECT * FROM tablename WHERE colname BETWEEN 'B' AND 'B~';

Any query to find the local settings and encoding.

Try

SELECT name, setting FROM pg_settings WHERE name LIKE 'lc_%';

The psql -l command shows you database encoding. Client and server

encoding is possible get by the following commands:

SHOW client_encoding and SHOW server_encoding

Zdenek

---------------------------(end of broadcast)---------------------------

TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Douglas Toltzman

doug@oakstreetsoftware.com

(910) 526-5938

Murali Doss wrote:

I have created new database with encoding as sql_ascii and reloaded the
data but still its returning 0 rows in Linux.

SELECT * FROM tablename WHERE colname BETWEEN 'B' AND 'B~';

You probably didn't set the appropriate collation, which is the thing
that affects this type of query. Beware that collation and encoding are
closely related so it still won't work if you choose mismatching
settings. You probably want C locale as well as SQL_ASCII encoding.

The query is probably poorly thought out though. Why are you not using
WHERE colname LIKE 'B%'? How do you know what does your collation
consider as being between B and B~? Remember, text values are not
necessarily compared using byte comparison. The C locale uses byte
comparison but I think it's the only one that does so.

--
Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC
"Saca el libro que tu religi�n considere como el indicado para encontrar la
oraci�n que traiga paz a tu alma. Luego rebootea el computador
y ve si funciona" (Carlos Ducl�s)

Hi,

Output of pg_setting in Linux OS

"lc_collate";"C"
"lc_ctype";"C"
"lc_messages";"C"
"lc_monetary";"C"
"lc_numeric";"C"
"lc_time";"C"

Client Encoding

"SQL_ASCII"

Server Encoding

"SQL_ASCII"

And it's same in Solaris and Window OS where the query is returning expected results.

Regards
Murali

-----Original Message-----
From: Alvaro Herrera [mailto:alvherre@commandprompt.com]
Sent: Tuesday, June 19, 2007 7:30 PM
To: Murali Doss
Cc: Douglas Toltzman; pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #3394: Partial search not working

Murali Doss wrote:

I have created new database with encoding as sql_ascii and reloaded the
data but still its returning 0 rows in Linux.

SELECT * FROM tablename WHERE colname BETWEEN 'B' AND 'B~';

You probably didn't set the appropriate collation, which is the thing
that affects this type of query. Beware that collation and encoding are
closely related so it still won't work if you choose mismatching
settings. You probably want C locale as well as SQL_ASCII encoding.

The query is probably poorly thought out though. Why are you not using
WHERE colname LIKE 'B%'? How do you know what does your collation
consider as being between B and B~? Remember, text values are not
necessarily compared using byte comparison. The C locale uses byte
comparison but I think it's the only one that does so.

--
Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC
"Saca el libro que tu religión considere como el indicado para encontrar la
oración que traiga paz a tu alma. Luego rebootea el computador
y ve si funciona" (Carlos Duclós)

"Murali Doss" <Murali.Doss@mphasis.com> writes:

I have created new database with encoding as sql_ascii and reloaded the
data but still its returning 0 rows in Linux.

It's locale, not encoding (or not only encoding) that determines sort
order. It sounds to me like you are using C locale on the other
installations but some non-C locale on the Linux one. In C locale
"BA" is between "B-" and "B~" but in most other locales it's not.

Check LC_COLLATE setting to find out. If it's wrong you'll have to
re-initdb :-(

regards, tom lane

Dear All,

How to change the pg_setting from en_US.UTF-8 to "C".

lc_collate - en_US.UTF-8
lc_ctype - en_US.UTF-8
lc_messages - en_US.UTF-8
lc_monetary - en_US.UTF-8
lc_numeric - en_US.UTF-8
lc_time - en_US.UTF-8

Regards
Murali Doss T.S.

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, June 19, 2007 7:50 PM
To: Murali Doss
Cc: Douglas Toltzman; pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #3394: Partial search not working

"Murali Doss" <Murali.Doss@mphasis.com> writes:

I have created new database with encoding as sql_ascii and reloaded

the

data but still its returning 0 rows in Linux.

It's locale, not encoding (or not only encoding) that determines sort
order. It sounds to me like you are using C locale on the other
installations but some non-C locale on the Linux one. In C locale
"BA" is between "B-" and "B~" but in most other locales it's not.

Check LC_COLLATE setting to find out. If it's wrong you'll have to
re-initdb :-(

regards, tom lane