Обсуждение: BUG #1931: ILIKE and LIKE fails on Turkish locale

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-bugs/2005-09/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_list.cgi?buglist=1354

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

"Devrim GUNDUZ" <devrim@gunduz.org> writes:
> http://sourceware.org/bugzilla/long_list.cgi?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

Hi Tom,

On Sat, 1 Oct 2005, Tom Lane wrote:

<snip>
> 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 ...

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

Regards,
--
Devrim GUNDUZ
Kivi Biliþim Teknolojileri - http://www.kivi.com.tr
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
                       http://www.gunduz.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

Hi,

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.

Yeah, I and Nicolai Tufar volunteer to fix it.

Regards,
--
Devrim GUNDUZ
Kivi Biliþim Teknolojileri - http://www.kivi.com.tr
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
                       http://www.gunduz.org

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_list.cgi?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

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_list.cgi?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. +

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

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. +

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.

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

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.

"Devrim GUNDUZ" <devrim@gunduz.org> writes:
> Like the bug report that was submitted a few days ago,
> http://archives.postgresql.org/pgsql-bugs/2005-09/msg00233.php
> I have the same thing for Turkish locale.

Would you confirm that this is fixed by my recent commit?

            regards, tom lane

Hello Tom,

On Mon, 2006-09-04 at 14:35 -0400, Tom Lane wrote:
> "Devrim GUNDUZ" <devrim@gunduz.org> writes:
> > Like the bug report that was submitted a few days ago,
> > http://archives.postgresql.org/pgsql-bugs/2005-09/msg00233.php
> > I have the same thing for Turkish locale.
>=20
> Would you confirm that this is fixed by my recent commit?

If I did not miss any test; I can confirm that this bug is fixed for
Turkish locale. I does return the correct results for each query, each
special char.

Thanks Tom.

Regards,
--=20
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

On Sep 04 02:35, Tom Lane wrote:
> "Devrim GUNDUZ" <devrim@gunduz.org> writes:
> > Like the bug report that was submitted a few days ago,
> > http://archives.postgresql.org/pgsql-bugs/2005-09/msg00233.php
> > I have the same thing for Turkish locale.
>
> Would you confirm that this is fixed by my recent commit?

I've made some tests. [AFAICS, below results are applicable for all
latinN encodings.]

  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 |    OK | FAILS
     tr_TR.UTF-8 |            UTF8 |      OK |      OK |    OK | FAILS

Looks like regex code needs same modifications done to ILIKE command.


Regards.

Volkan YAZICI <yazicivo@ttnet.net.tr> writes:

> On Sep 04 02:35, Tom Lane wrote:
>> "Devrim GUNDUZ" <devrim@gunduz.org> writes:
>> > Like the bug report that was submitted a few days ago,
>> > http://archives.postgresql.org/pgsql-bugs/2005-09/msg00233.php
>> > I have the same thing for Turkish locale.
>>=20
>> Would you confirm that this is fixed by my recent commit?
>
> I've made some tests. [AFAICS, below results are applicable for all
> latinN encodings.]
>
>   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 |    OK | FAILS
>      tr_TR.UTF-8 |            UTF8 |      OK |      OK |    OK | FAILS
>
> Looks like regex code needs same modifications done to ILIKE command.

I have applied the patch for like.c: r 1.64 -> r 1.65, to my
postgresql 8.1.4 sources, and ILIKE now, obviously, works for
ru_RU.UTF-8 locale.

I still have a couple of issues, though, not sure if anyone reported
them yet.=20

1) When table or column names are multibyte, they are not displayed by
   either psql or pgAdmin. What I see is empty strings instead of
   table names. It works with latin table names and with national
   table names on single-byte encodings.

2) When I try to create a stored procedure to create table (e.g., for
   creating table only if it doesn't already exist), it fails to
   compile if i use cyrillic letter "=C8" (unicode id: 0x0445, utf-8
   representation is D1 85) with the following weird error:

ERROR:  expected dot between identifiers: =C8
CONTEXT:  compile of PL/pgSQL function "createoraltertable" near line 2

the following query fails:

CREATE OR REPLACE FUNCTION TestFunction()
RETURNS int
AS $$
BEGIN
  SELECT =C8 FROM test_table;
  RETURN 0;
END;
$$ LANGUAGE plpgsql;

(the only multibyte symbol is that 0x0445 letter, and it's between
select and from).

Simple create table and select, outside of function, works (except the
first issue).

What can I do to help to debug this?
Should I try the sources from CVS HEAD?

--=20
WBR, Victor V. Snezhko
E-mail: snezhko@indorsoft.ru

Victor Snezhko <snezhko@indorsoft.ru> writes:

> 2) When I try to create a stored procedure to create table (e.g., for
>    creating table only if it doesn't already exist), it fails to
>    compile if i use cyrillic letter "=C8" (unicode id: 0x0445, utf-8
>    representation is D1 85) with the following weird error:
>
> ERROR:  expected dot between identifiers: =C8
> CONTEXT:  compile of PL/pgSQL function "createoraltertable" near line 2
>
> the following query fails:
>
> CREATE OR REPLACE FUNCTION TestFunction()
> RETURNS int
> AS $$
> BEGIN
>   SELECT =C8 FROM test_table;
>   RETURN 0;
> END;
> $$ LANGUAGE plpgsql;

Sorry, I was a bit sloppy here: I said about creating table and
specified error output from function createoraltertable(),
which contained CREATE TABLE statement instead of SELECT, but
specified another example function.

Clarification: TestFunction() also fails with the same error about
expected dot between identifiers and can be used for debugging.

--=20
WBR, Victor V. Snezhko
E-mail: snezhko@indorsoft.ru

Volkan YAZICI <yazicivo@ttnet.net.tr> writes:
> Looks like regex code needs same modifications done to ILIKE command.

Regex is still broken but I don't think the same fix is feasible -- we
can't blindly downcase the strings outside the regex package (consider
embedded ARE options).  And even if we did that, it would fix only one
aspect of regex's locale dependence.  The only real fix is to
de-lobotomize the locale-specific functions in regc_locale.c, and then
you run up against the same problem that pg_wchar may or may not use the
same representation that the <wchar.h> functions expect.  So I fear
regex will have to stay as it is until we have our own locale support.

            regards, tom lane

Victor Snezhko <snezhko@indorsoft.ru> writes:

> ERROR:  expected dot between identifiers: =C8
> CONTEXT:  compile of PL/pgSQL function "createoraltertable" near line 2
>
> the following query fails:
>
> CREATE OR REPLACE FUNCTION TestFunction()
> RETURNS int
> AS $$
> BEGIN
>   SELECT =C8 FROM test_table;
>   RETURN 0;
> END;
> $$ LANGUAGE plpgsql;

Hmm, seems like plpgsql is also broken for multibyte encodings. it
contains some single-byte ops, like the following:

    while (*s && isspace((unsigned char) *s))
        s++;
    if (*s++ !=3D '.')
        elog(ERROR, "expected dot between identifiers: %s", sstart);

If we state that there are fixes for multibyte encodings in 8.2, we
should note that plpgsql is still broken. Being unfamiliar with
postgresql multibyte internals, I don't see a simple way to fix it
now.=20

--=20
WBR, Victor V. Snezhko
E-mail: snezhko@indorsoft.ru

Victor Snezhko <snezhko@indorsoft.ru> writes:
> Hmm, seems like plpgsql is also broken for multibyte encodings. it
> contains some single-byte ops, like the following:

>     while (*s && isspace((unsigned char) *s))
>         s++;
>     if (*s++ != '.')
>         elog(ERROR, "expected dot between identifiers: %s", sstart);

That code is not broken ... at least not unless isspace() is.

            regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

>> Hmm, seems like plpgsql is also broken for multibyte encodings. it
>> contains some single-byte ops, like the following:
>
>>     while (*s && isspace((unsigned char) *s))
>>         s++;
>>     if (*s++ != '.')
>>         elog(ERROR, "expected dot between identifiers: %s", sstart);
>
> That code is not broken ... at least not unless isspace() is.

OK, sorry for the FUD, I'm just trying to find a cause... I will not
bring such ungrounded accusations next time.

But... maybe I misunderstand something fundamental, but should isspace
work when we pass one byte of multibyte character?

I'm digging this procedure (plpgsql_convert_ident()) and will report
if I find something wrong inside it and not inside isspace.

--
WBR, Victor V. Snezhko
E-mail: snezhko@indorsoft.ru

Victor Snezhko <snezhko@indorsoft.ru> writes:
> But... maybe I misunderstand something fundamental, but should isspace
> work when we pass one byte of multibyte character?

Yes, it should, because we only allow ASCII-superset encodings inside
the backend.  That is, characters that actually occupy multiple bytes
must have the high bit set in each such byte, and we expect that
isspace, isdigit, etc will not return true for any such byte.

            regards, tom lane

Victor Snezhko <snezhko@indorsoft.ru> writes:
> 2) When I try to create a stored procedure to create table (e.g., for
>    creating table only if it doesn't already exist), it fails to
>    compile if i use cyrillic letter "È" (unicode id: 0x0445, utf-8
>    representation is D1 85) with the following weird error:

> ERROR:  expected dot between identifiers: È
> CONTEXT:  compile of PL/pgSQL function "createoraltertable" near line 2

> the following query fails:

> CREATE OR REPLACE FUNCTION TestFunction()
> RETURNS int
> AS $$
> BEGIN
>   SELECT È FROM test_table;
>   RETURN 0;
> END;
> $$ LANGUAGE plpgsql;

I tried to duplicate this without success on Fedora Core 5.  Either your
ru_RU.utf8 locale works differently from Fedora's, or something else is
wrong.  Looking at the code, the only very plausible theory is that
isspace() is doing something different than we expect it to.  I wonder
whether you have the database encoding set to something else than what
the postmaster's LC_CTYPE locale expects?

            regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Victor Snezhko <snezhko@indorsoft.ru> writes:
>> 2) When I try to create a stored procedure to create table (e.g., for
>>    creating table only if it doesn't already exist), it fails to
>>    compile if i use cyrillic letter "=C8" (unicode id: 0x0445, utf-8
>>    representation is D1 85) with the following weird error:
>
>> ERROR:  expected dot between identifiers: =C8
>> CONTEXT:  compile of PL/pgSQL function "createoraltertable" near line 2
>
>> the following query fails:
>
>> CREATE OR REPLACE FUNCTION TestFunction()
>> RETURNS int
>> AS $$
>> BEGIN
>>   SELECT =C8 FROM test_table;
>>   RETURN 0;
>> END;
>> $$ LANGUAGE plpgsql;
>
> I tried to duplicate this without success on Fedora Core 5.  Either your
> ru_RU.utf8 locale works differently from Fedora's, or something else is
> wrong.=20=20
> Looking at the code, the only very plausible theory is that
> isspace() is doing something different than we expect it to.  I wonder
> whether you have the database encoding set to something else than what
> the postmaster's LC_CTYPE locale expects?

No, the database encoding is UTF-8, the cluster was built with
ru_RU.UTF-8 locale, and I haven't modified any defaults.

It's on FreeBSD 6.1, and it's quite possible that it has something
wrong with utf-8 locale, I saw some complaints at least about
collation.

However, isspace doesn't seem like a culprit to me. Let's ensure that
I have done everything right to come to this conclusion. I have
applied the following patch to pl_funcs.c:


But, if I'm doing everything right, isspace seems not to be the
culprit to me. I have run the above query on the server with the
following applied patch (the server is 8.1.4, but I don't see any
changes to the plpgsql_convert_ident() in revs 1.46-1.54):

*** src/pl/plpgsql/src/pl_funcs.c.orig  Wed Nov 23 00:23:30 2005
--- src/pl/plpgsql/src/pl_funcs.c       Fri Sep 22 14:48:09 2006
***************
*** 363,368 ****
--- 363,381 ----
  {
        const char *sstart =3D s;
        int                     identctr =3D 0;
+       char                    *isspacebuf;
+       int                     i;
+
+       isspacebuf =3D (char*)malloc(strlen(s) + 1);
+       for (i=3D0; s[i]; ++i) {
+               if (isspace(s[i]))
+                       isspacebuf[i] =3D 't';
+               else
+                       isspacebuf[i] =3D 'f';
+       }
+
+       ereport(LOG,
+               (errmsg("plpgsql_convert_ident: ident=3D%s, isspace array=
=3D%s, isspace(0)=3D%c", s, isspacebuf, isspace(0)?'t':'f')));

        /* Outer loop over identifiers */
        while (*s)


and got the following line in the logfile:

  LOG:  plpgsql_convert_ident: ident=3D=C8, isspace array=3Dff, isspace(0)=
=3Df

The symbol after "input=3D" is that 0x0445 (D1 85 in utf-8) letter which
I mentioned above. I also double-checked this: got a stack trace (by
inserting NULL-pointer assignment instead of reporting "expected dot
between identifiers") and saw that the value passed to the
plpgsql_convert_ident points to the following bytes: D1 85 00.

So, isspace returns false on all three of them.

I have also run the same query on another cluster (and the same
binaries), initialized with a single-byte encoding: ru_RU.KOI8-R,
where everything works. isspace returns the same results:

LOG:  plpgsql_convert_ident: ident=3D=C8, isspace array=3Df, isspace(0)=3Df
LOG:  plpgsql_convert_ident: ident=3Dtest_table, isspace array=3Dffffffffff=
, isspace(0)=3Df

Strange...

--=20
WBR, Victor V. Snezhko
E-mail: snezhko@indorsoft.ru

Victor Snezhko <snezhko@indorsoft.ru> writes:

> LOG:  plpgsql_convert_ident: ident=3D=C8, isspace array=3Df, isspace(0)=
=3Df
> LOG:  plpgsql_convert_ident: ident=3Dtest_table, isspace array=3Dffffffff=
ff, isspace(0)=3Df

Hmm, there was something wrong in my utf-8 cluster setup, I
re-attached it and now see that isspace indeed fails:
LOG:  plpgsql_convert_ident: ident=3D=C8, isspace array=3Dft, isspace(0)=3Df

--=20
WBR, Victor V. Snezhko
E-mail: snezhko@indorsoft.ru

Victor Snezhko <snezhko@indorsoft.ru> writes:
> Hmm, there was something wrong in my utf-8 cluster setup, I
> re-attached it and now see that isspace indeed fails:
> LOG:  plpgsql_convert_ident: ident=È, isspace array=ft, isspace(0)=f

Hm.  Given that we've not seen this reported elsewhere, I wonder if we
shouldn't conclude that it's a FreeBSD bug.

We could fix the problem by not using isspace() but some homegrown
equivalent ... but that seems a tad ugly, especially if it's to work
around a problem on just one locale on one platform.  Can anyone else
check the result of isspace(0x85) ?

            regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

>> Hmm, there was something wrong in my utf-8 cluster setup, I
>> re-attached it and now see that isspace indeed fails:
>> LOG:  plpgsql_convert_ident: ident=3D=C8, isspace array=3Dft, isspace(0)=
=3Df
>
> Hm.  Given that we've not seen this reported elsewhere, I wonder if we
> shouldn't conclude that it's a FreeBSD bug.

OK, for 0x85 maybe, but see below.

> We could fix the problem by not using isspace() but some homegrown
> equivalent ... but that seems a tad ugly,=20

I wonder why simple create table (outside of plpgsql, with the table
name equal to the character D1 85) works? Are multibyte strings
handled differently in query parser compared to plpgsql?

> especially if it's to work around a problem on just one locale on
> one platform.  Can anyone else check the result of isspace(0x85) ?

Not sure about one platform, we need to check this. And here are some
news: MacOSX's isspace also returns 1 on 0x85. So, 1.5 platforms.

The test I have run is:

#include <locale.h>
#include <ctype.h>
#include <stdio.h>

int main()
{
  setlocale(LC_ALL, "");
  printf("%d %d\n", isspace(0x85), isspace(0xA0));
  printf("%d %d\n", iswspace(0x85), iswspace(0xA0));
  return 0;
}

0xA0 is added because is's true space, see below.

On FreeBSD & Mac this yields the following output:
1 1
1 1

On recent ALTLinux (Compact 3.0), which uses ru_RU.UTF-8 locale by
default:
0 0=20
0 0

Here is an excerpt from the unicode character database
(http://www.unicode.org/Public/UNIDATA/UnicodeData.txt):

0085;<control>;Cc;0;B;;;;;N;NEXT LINE (NEL);;;;
00A0;NO-BREAK SPACE;Zs;0;CS;<noBreak> 0020;;;;N;NON-BREAKING SPACE;;;;

0x85 is the alternative next-line, and 0xA0 is the real space (note
the mark Zs - http://www.unicode.org/Public/UNIDATA/UCD.html=20
describes symbols with such marks as "Separator, Space")

So, that linux doesn't return true for 0x85 and 0xA0 characters - but
not because it's is* functions return false for any character above
0x80, but because it's LC_CTYPE doesn't know anything about these
particular characters! (iswspace calls also return false). So linux
has working isspace, but broken iswspace for these characters...

So, could you run one more test on that Fedora to see if at least
isspace() and isalpha() meet out expectations?

#include <locale.h>
#include <ctype.h>
#include <stdio.h>

int main()
{
  int i;
  setlocale(LC_ALL, "");
  for (i=3D0x80; i <=3D 0xFF; ++i) {
    if (isspace(i) !=3D 0) printf("character 0x%x is a space\n", i);
    if (isalpha(i) !=3D 0) printf("character 0x%x is alphabetical\n", i);
    if (isdigit(i) !=3D 0) printf("character 0x%x is a digit\n", i);
  }
  return 0;
}

My FreeBSD lists a whole heck of characters:

character 0x85 is a space
character 0xa0 is a space
character 0xaa is alphabetical
character 0xb5 is alphabetical
character 0xba is alphabetical
character 0xc0 is alphabetical
... 0xc1-0xfe is alphabetical
character 0xff is alphabetical

--=20
WBR, Victor V. Snezhko
E-mail: snezhko@indorsoft.ru

Mac OS X is based on FreeBSD.  I'm not sure if this is what you want,=20=20
because I'm not in a Turkish locale, but ...

#include <stdio.h>
#include <ctype.h>

int main()
{
         printf("isspace(0x85) returned %d\n", isspace(0x85));
}

produces:

isspace(0x85) returned 0

... on my Mac OS X 10.4.7 PPC box.

On Sep 22, 2006, at 12:48 PM, Tom Lane wrote:

> Victor Snezhko <snezhko@indorsoft.ru> writes:
>> Hmm, there was something wrong in my utf-8 cluster setup, I
>> re-attached it and now see that isspace indeed fails:
>> LOG:  plpgsql_convert_ident: ident=3D=C8, isspace array=3Dft, isspace(0)=
=3Df
>
> Hm.  Given that we've not seen this reported elsewhere, I wonder if we
> shouldn't conclude that it's a FreeBSD bug.
>
> We could fix the problem by not using isspace() but some homegrown
> equivalent ... but that seems a tad ugly, especially if it's to work
> around a problem on just one locale on one platform.  Can anyone else
> check the result of isspace(0x85) ?
>
>             regards, tom lane
>
> ---------------------------(end of=20=20
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

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

Victor Snezhko <snezhko@indorsoft.ru> writes:
> My FreeBSD lists a whole heck of characters:

> character 0x85 is a space
> character 0xa0 is a space
> character 0xaa is alphabetical
> character 0xb5 is alphabetical
> character 0xba is alphabetical
> character 0xc0 is alphabetical
> ... 0xc1-0xfe is alphabetical
> character 0xff is alphabetical

Hm.  I'm still thinking that this behavior is wrong for UTF8 encoding,
but it would be reasonable in LATINn and related encodings, so we
probably ought to do something about it.

After further thought, it's not so much that we can't tolerate
locale-dependent behavior of isspace() in general, as that in this
particular case we are expecting it to match the scanner's idea
of a space: scan.l has

space            [ \t\n\r\f]

which obviously is not locale-aware.  I think we need convert_ident to
use a plpgsql_isspace() that accepts these and only these as spaces.
Any high-bit-set byte is part of an identifier according to scan.l's
rules, and convert_ident must have the same behavior regardless of locale.

There may be related risks in and around the other flex scanners
... will look.

            regards, tom lane

I wrote:
> ... I think we need convert_ident to
> use a plpgsql_isspace() that accepts these and only these as spaces.
> Any high-bit-set byte is part of an identifier according to scan.l's
> rules, and convert_ident must have the same behavior regardless of locale.

> There may be related risks in and around the other flex scanners
> ... will look.

I've committed a fix along these lines.  ecpg had the identical bug, but
I couldn't find any other places where we seemed to be assuming that
<ctype.h> macros would match the behavior of our flex scanners.

            regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

>> ... I think we need convert_ident to
>> use a plpgsql_isspace() that accepts these and only these as spaces.
>> Any high-bit-set byte is part of an identifier according to scan.l's
>> rules, and convert_ident must have the same behavior regardless of local=
e.
>
>> There may be related risks in and around the other flex scanners
>> ... will look.
>
> I've committed a fix along these lines.  ecpg had the identical bug, but
> I couldn't find any other places where we seemed to be assuming that
> <ctype.h> macros would match the behavior of our flex scanners.

Thank you, your commit has fixed this issue.=20

The issue with empty table names (when they are multibyte) remains.

The following queries work as expected:

(I use table named as "=D4" - Unicode 0442, and column named as "=CB" -
Unicode id 043A)=20

CREATE TABLE =D4 (
  =CB int NOT NULL,
  PRIMARY KEY (=CB)
);

INSERT INTO =D4 (=CB) VALUES (1);

SELECT * FROM =D4;

However, in system catalogs (SELECT * FROM pg_tables WHERE
schemaname=3D'public') there appears to be empty strings instead=20=20
of table names.

This is on patched 8.1.4 (with ILIKE and ctype.h fixes), I'm upgrading
to HEAD now to see if anything improved.

--=20
WBR, Victor V. Snezhko
E-mail: snezhko@indorsoft.ru

Victor Snezhko <snezhko@indorsoft.ru> writes:

> However, in system catalogs (SELECT * FROM pg_tables WHERE
> schemaname='public') there appears to be empty strings instead
> of table names.
>
> This is on patched 8.1.4 (with ILIKE and ctype.h fixes), I'm upgrading
> to HEAD now to see if anything improved.

Yes, upgrading to HEAD didn't change anything here.

--
WBR, Victor V. Snezhko
E-mail: snezhko@indorsoft.ru

Has this been addressed?

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

Tom Lane wrote:
> Victor Snezhko <snezhko@indorsoft.ru> writes:
> > My FreeBSD lists a whole heck of characters:
>
> > character 0x85 is a space
> > character 0xa0 is a space
> > character 0xaa is alphabetical
> > character 0xb5 is alphabetical
> > character 0xba is alphabetical
> > character 0xc0 is alphabetical
> > ... 0xc1-0xfe is alphabetical
> > character 0xff is alphabetical
>
> Hm.  I'm still thinking that this behavior is wrong for UTF8 encoding,
> but it would be reasonable in LATINn and related encodings, so we
> probably ought to do something about it.
>
> After further thought, it's not so much that we can't tolerate
> locale-dependent behavior of isspace() in general, as that in this
> particular case we are expecting it to match the scanner's idea
> of a space: scan.l has
>
> space            [ \t\n\r\f]
>
> which obviously is not locale-aware.  I think we need convert_ident to
> use a plpgsql_isspace() that accepts these and only these as spaces.
> Any high-bit-set byte is part of an identifier according to scan.l's
> rules, and convert_ident must have the same behavior regardless of locale.
>
> There may be related risks in and around the other flex scanners
> ... will look.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

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

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

Bruce Momjian <bruce@momjian.us> writes:
> Has this been addressed?

Yes, at least as far as we can take it given the current locale
infrastructure.  There doesn't seem any reasonable way to get regex
locale-dependent character classes to work for multibyte chars :-(

            regards, tom lane