[BUGS] PostgreSQL 8.3.7: soundex function returns UTF-16 characters
Hello,
We have just discovered a problem with the soundex function in
PostgreSQL 8.3.7. The problem is easy to reproduce. The following query
returns the ASCII code of the soundex representation of the Greek letter Pi:
select ascii (soundex('Π'));
In PostgreSQL 8.2.6 the result would be 0 (character null). In
PostgreSQL 8.3.7 the return value is 944, which is the UTF-16 code of
this letter. Because PostgreSQL does not support UTF-16, this behaviour
causes problems when the return values from the soundex function are
stored in a database column. For instance, creating an index on such a
column or doing VACUUM FULL on the table results in errors like:
ERROR: could not convert string to UTF-16
Regards,
Frans
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] PostgreSQL 8.3.7: soundex function returns UTF-16 characters
Tom Lane wrote:
Frans writes:
We have just discovered a problem with the soundex function in
PostgreSQL 8.3.7. The problem is easy to reproduce. The following query
returns the ASCII code of the soundex representation of the Greek letter Pi:
select ascii (soundex('Î '));
In PostgreSQL 8.2.6 the result would be 0 (character null). In
PostgreSQL 8.3.7 the return value is 944, which is the UTF-16 code of
this letter.
Hm, I take it you are working in database encoding utf8?
That is correct. I should have mentioned it. It is the default encoding
we use because we often deal with non-English languages. And it is
because of multilingualism that the fuzzystrmatch functions are handy.
The
fuzzystrmatch module doesn't really work with utf8 (nor any other
multibyte encoding), because it depends on the functions.
What you'll probably get when applying it to non-ascii utf8 is
an invalidly encoded string.
Well, in 8.2.6 the result for non-ASCII UTF-8 was an empty string (ASCII
code 0). You could argue that this is a valid way of expressing that the
input string could not be processed (especially if it were documented).
The nice thing about this approach is that the result is valid ASCII
(and UTF-8).
This is a known limitation that probably should be better documented.
It was just as broken in 8.2 (and every previous version), though.
But it seems there has been a recent change in the handling of non-ASCII
strings. And the result of this change is that further handling or
storing of the function output has become more difficult.
regards, tom lane
Best regards,
Frans
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] PostgreSQL 8.3.7: soundex function returns UTF-16 characters
Tom Lane wrote:
Frans writes:
Tom Lane wrote:
The
fuzzystrmatch module doesn't really work with utf8 (nor any other
multibyte encoding), because it depends on the functions.
What you'll probably get when applying it to non-ascii utf8 is
an invalidly encoded string.
Well, in 8.2.6 the result for non-ASCII UTF-8 was an empty string (ASCII
code 0).
A comparison of the 8.2 and 8.3 fuzzystrmatch sources shows no
difference. The behavior of the ascii() function has indeed changed,
but soundex() is no more nor less broken than it was before.
[ thinks for a bit... ] If you are seeing a difference in what soundex
itself does, the most likely explanation is a difference in the behavior
of isalpha() or perhaps toupper(). Are you running on the same
underlying C library as before? Are you quite sure you have the same
encoding and locale selected?
Thank you for pointing me in the right direction. I have done some more
research now.. I have installed 8.2.13 and 8.3.7 on the same
workstation, selecting locale=C and character encoding=UTF-8 in both
cases. In both cases soundex() behaved as desired, i.e. it produces a
null string if it can not handle the input. It looks like the difference
in behaviour I noticed was not caused by the different PostgreSQL
versions after all, but by a different locale setting. I see (in
postgresql.ini) that for the database in which soundex() produces the
'wrong' output the locale apparently was set to 'Dutch_Netherlands'. I
can not recall consciously selecting this locale but it might have been
set by the installer. Does it make sense that the locale setting
influences the workings of the soundex function?
In the database where I noticed the undesired soundex() behaviour I did
a further test, using the bit_length() function. The query "(select
bit_length(soundex('?'))" returns a value of 0 where ascii() also
returns 0 but it returns a value of 32 in the other case (where ascii()
returns 944). So it seems soundex() really has a different output in
both cases.
I don't know now if this issue should still be regarded as a bug.. At
least it seems to me that the locale setting is also affecting the
soundex function should be documented.
regards, tom lane
