Thread: 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.
Attachment
"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