Thread: BUG #3394: Partial search not working
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 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 Regards Murali Doss T.S.
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. >=20 > I like to know whether i need to run some patch in linux OS to make the > partial search to work. >=20 > 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?=20 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]=20 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? --=20 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]=20
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,
>=20
> 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.
>=20
> SELECT * FROM tablename WHERE colname BETWEEN 'B' AND 'B~';
>=20
>=20
> Any query to find the local settings and encoding.
>=20
Try
SELECT name, setting FROM pg_settings WHERE name LIKE 'lc_%';
The psql -l command shows you database encoding. Client and server=20
encoding is possible get by the following commands:
SHOW client_encoding and SHOW server_encoding
=09
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.
=20
SELECT * FROM tablename WHERE colname BETWEEN 'B' AND 'B~';
=20
=20
________________________________
From: Douglas Toltzman [mailto:doug@oakstreetsoftware.com]=20
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
=20
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.
=20
Of course, you'll need to dump and reload your data.
=20
On Jun 19, 2007, at 8:51 AM, Murali Doss wrote:
Hi Zdenek,
=20
Thanks for your effort.
=20
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?
=20
Regards
Murali Doss T.S.
=20
=20
=20
-----Original Message-----
From: Zdenek.Kotala@Sun.COM [mailto:Zdenek.Kotala@Sun.COM]=20
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
=20
Murali Doss wrote:
Hi Michael,
=09=20
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.
=09=20
SELECT * FROM tablename WHERE colname BETWEEN 'B' AND 'B~';
=09=20
=09=20
Any query to find the local settings and encoding.
=09=20
=20
Try
=20
SELECT name, setting FROM pg_settings WHERE name LIKE 'lc_%';
=20
The psql -l command shows you database encoding. Client and server=20
encoding is possible get by the following commands:
=20
SHOW client_encoding and SHOW server_encoding
=20
=20=20=20=20=20=20=20=20=20=20=20=20
Zdenek
=20
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
=20
http://www.postgresql.org/docs/faq
=20
Douglas Toltzman
doug@oakstreetsoftware.com
(910) 526-5938
=20
=20
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
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 expecte= d results. Regards Murali -----Original Message----- From: Alvaro Herrera [mailto:alvherre@commandprompt.com]=20 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. >=20 > 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. --=20 Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVH= XC "Saca el libro que tu religi=F3n considere como el indicado para encontrar = la oraci=F3n que traiga paz a tu alma. Luego rebootea el computador y ve si funciona" (Carlos Ducl=F3s)
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]=20
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=20
"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