The following bug has been logged online:

Bug reference: 1931
Logged by: Devrim GUNDUZ
Email address: devrim@gunduz.org
PostgreSQL version: 8.1beta2
Operating system: Linux (RHEL 4, Debian 3.1)
Description: ILIKE and LIKE fails on Turkish locale
Details:

Hi,

Like the bug report that was submitted a few days ago,

http://archives.postgresql.org/pgsql...9/msg00233.php

I have the same thing for Turkish locale. First I thought that it was a
glibc error but glibc guys guided me here:

http://sourceware.org/bugzilla/long_...i?buglist=1354

So it is PostgreSQL's bug or Glibc's?

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

"Devrim GUNDUZ" <devrim@gunduz.org> writes:
> http://sourceware.org/bugzilla/long_...i?buglist=1354
> So it is PostgreSQL's bug or Glibc's?

Just offhand, iwchareq() seems several bricks shy of a load:

/*
* if one of them is an ASCII while the other is not, then they must
* be different characters
*/
else if ((unsigned char) *p1 < CHARMAX || (unsigned char) *p2 < CHARMAX)
return (0);

This test is wrong per Jakub's observation. Also, the code right below
that is using tolower() not towlower() on wide characters, which seems
pretty wrong. For that matter, towlower would be wrong too :-( because
there is no certainty that libc's idea of wide characters is the same as
pg_mb2wchar_with_len's.

So yeah, ILIKE looks just about completely broken for multibyte encodings.
Maybe it would be best to pass both strings through lower() and then
do a normal LIKE comparison?

The regexp code doesn't look better, btw, just differently broken ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Devrim GUNDUZ <devrim@gunduz.org> writes:
> On Sat, 1 Oct 2005, Tom Lane wrote:
>> So yeah, ILIKE looks just about completely broken for multibyte encodings.
>> The regexp code doesn't look better, btw, just differently broken ...

> So any chance to fix it before beta 3 (and also backpatch?) ?

Are you volunteering?

This stuff has never worked, so I don't feel that it qualifies as "must
fix for 8.1". If you can come up with a believable low-impact patch,
I'd support putting it in now ... but my guess is that this is a
significant bit of work that will not get done in this release cycle.

regards, tom lane

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

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

This has been saved for the 8.2 release:

http://momjian.postgresql.org/cgi-bin/pgpatches_hold

---------------------------------------------------------------------------

Tom Lane wrote:
> "Devrim GUNDUZ" <devrim@gunduz.org> writes:
> > http://sourceware.org/bugzilla/long_...i?buglist=1354
> > So it is PostgreSQL's bug or Glibc's?
>
> Just offhand, iwchareq() seems several bricks shy of a load:
>
> /*
> * if one of them is an ASCII while the other is not, then they must
> * be different characters
> */
> else if ((unsigned char) *p1 < CHARMAX || (unsigned char) *p2 < CHARMAX)
> return (0);
>
> This test is wrong per Jakub's observation. Also, the code right below
> that is using tolower() not towlower() on wide characters, which seems
> pretty wrong. For that matter, towlower would be wrong too :-( because
> there is no certainty that libc's idea of wide characters is the same as
> pg_mb2wchar_with_len's.
>
> So yeah, ILIKE looks just about completely broken for multibyte encodings.
> Maybe it would be best to pass both strings through lower() and then
> do a normal LIKE comparison?
>
> The regexp code doesn't look better, btw, just differently broken ...
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Did we make any progress on this? If so, I can't find it.

---------------------------------------------------------------------------

Tom Lane wrote:
> "Devrim GUNDUZ" <devrim@gunduz.org> writes:
> > http://sourceware.org/bugzilla/long_...i?buglist=1354
> > So it is PostgreSQL's bug or Glibc's?
>
> Just offhand, iwchareq() seems several bricks shy of a load:
>
> /*
> * if one of them is an ASCII while the other is not, then they must
> * be different characters
> */
> else if ((unsigned char) *p1 < CHARMAX || (unsigned char) *p2 < CHARMAX)
> return (0);
>
> This test is wrong per Jakub's observation. Also, the code right below
> that is using tolower() not towlower() on wide characters, which seems
> pretty wrong. For that matter, towlower would be wrong too :-( because
> there is no certainty that libc's idea of wide characters is the same as
> pg_mb2wchar_with_len's.
>
> So yeah, ILIKE looks just about completely broken for multibyte encodings.
> Maybe it would be best to pass both strings through lower() and then
> do a normal LIKE comparison?
>
> The regexp code doesn't look better, btw, just differently broken ...
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> So yeah, ILIKE looks just about completely broken for multibyte encodings.

> Did we make any progress on this? If so, I can't find it.

No, nobody submitted a patch AFAIR. There should be a TODO item for
this:

* fix ILIKE and regular expressions to handle case insensitivity
properly in multibyte encodings

BTW, while looking to see if TODO had anything about this, I noted
the following items that shouldn't be there anymore --- they are
done and pushed out:

o Add new version of PQescapeString() that doesn't double backslashes
that are part of a client-only multibyte sequence

Single-quote is not a valid byte in any supported client-only
encoding. This requires using mblen() to determine if the
backslash is inside or outside a multi-byte sequence.

o Add new version of PQescapeString() that doesn't double
backslashes when standard_conforming_strings is true and
non-E strings are used



regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Thanks, both done.

---------------------------------------------------------------------------

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom Lane wrote:
> >> So yeah, ILIKE looks just about completely broken for multibyte encodings.
>
> > Did we make any progress on this? If so, I can't find it.
>
> No, nobody submitted a patch AFAIR. There should be a TODO item for
> this:
>
> * fix ILIKE and regular expressions to handle case insensitivity
> properly in multibyte encodings
>
> BTW, while looking to see if TODO had anything about this, I noted
> the following items that shouldn't be there anymore --- they are
> done and pushed out:
>
> o Add new version of PQescapeString() that doesn't double backslashes
> that are part of a client-only multibyte sequence
>
> Single-quote is not a valid byte in any supported client-only
> encoding. This requires using mblen() to determine if the
> backslash is inside or outside a multi-byte sequence.
>
> o Add new version of PQescapeString() that doesn't double
> backslashes when standard_conforming_strings is true and
> non-E strings are used
>
>
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

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

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

On Jun 14 05:00, Bruce Momjian wrote:
> Did we make any progress on this? If so, I can't find it.

I've made some tests for upper(), lower(), ILIKE and ~* using cvs tip.
Below are the details:

Cluster Locale | client_encoding | upper() | lower() | ILIKE | ~*
-----------------+-----------------+---------+---------+-------+----
tr_TR.iso8859-9 | LATIN5 | OK | OK | OK | OK
tr_TR.iso8859-9 | UTF8 | OK | OK | OK | OK
tr_TR.UTF-8 | LATIN5 | OK | OK | FAILS | FAILS
tr_TR.UTF-8 | UTF8 | OK | OK | FAILS | FAILS

Also, if you'd wish, I can prepare an ad-hoc regression tests patch
for LATIN5 and UTF-8 support of Turkish characters.


Regards.

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

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

Volkan YAZICI <yazicivo@ttnet.net.tr> writes:
> Also, if you'd wish, I can prepare an ad-hoc regression tests patch
> for LATIN5 and UTF-8 support of Turkish characters.

We know it's broken. What's needed is a patch.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On Jun 15 09:33, Tom Lane wrote:
> Volkan YAZICI <yazicivo@ttnet.net.tr> writes:
> > Also, if you'd wish, I can prepare an ad-hoc regression tests patch
> > for LATIN5 and UTF-8 support of Turkish characters.
>
> We know it's broken. What's needed is a patch.

I couldn't understand why you're so aggressive. I'm just trying to help.
And, IMNSHO, posted test results are quite helpful to determine the
exact problem.

As I understand from the tests, ILIKE and ~* don't work properly while
using UTF-8, despite lower() and upper() work without any problem.
Therefore, I've tried to imitate the code of lower() to form a working
iwchareq() method. [Related patch is attached.] It succeded in all of my
previous tests (and plus in regression tests).

As you can see, it's a quite ad-hoc patch. (No win32 support added yet.)
Also, it needs a HAVE_MBTOWC definition too. I just wanted to give it a
V0 speed.

I think, like.c and oracle_compat.c files should be written from
scratch by somebody with more experience. They look like deprecated in
some aspects. (For instance, like.c is still using CHARMAX despite Bruce
generalized it as HIGHBIT in c.h)


Regards.


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly