[BUGS] 8.0b3: problems with ILIKE and ~* on multibyte-chars

[peter pilsl]

8.0 beta3
the caseinsensitive patternmatch-operators seems not to work with 
multibyte, while lower() and upper()-functions finally works perfect.

provided the correct locale-setting on initdb (LC_CTYPE=de_AT.UTF-8 in 
the example) the following happens:

example :   
test=# \l
List of databases
   Name|  Owner   | Encoding
---+--+---
 test  | postgres | UNICODE
test=# \d test
Table "public.test"
 Column | Type | Modifiers
+--+---
 t  | text |
Indexes:
"t_idx" btree (t)
test=# select t from test;
 t
---
 ä
 Ä
 o
 O
(4 rows)
test=# select t from test where t~*'ä';
 t
---
 ä
(1 row)
> expected is to give 'ä' and 'Ä' as result, cause search is 
caseinsesitive and 'Ä' is uppercase of 'ä'

test=# select t from test where t~*'Ä';
 t
---
 Ä
(1 row)
==> same as above
test=# select t from test where  lower(t)~'ä';
 t
---
 ä
 Ä
(2 rows)

=> the correct result using a workaround

test=# select t from test where t~*'o';
 t
---
 o
 O
(2 rows)
==> it works with "normal" chars

# /usr/local/pgsql8/bin/pg_controldata /data/postgres/postgres8/ | grep LC
LC_COLLATE:   de_AT.UTF-8
LC_CTYPE: de_AT.UTF-8

general feedback on multibytes:
things work just great. I put 8.0b3 in productional for my 
unicode-databases two days ago, cause lower() finally works. No problems 
so far. Things work just great. THNX A LOT !!!

a big featurerequest is different locales for different databases.  A 
locale LC_COLLATE "de_AT.UTF-8" simply makes no sense for 
non-unicode-databases and gives very odd results !!

If one wants to use locales and unicode, he needs to run two instances 
of postgres. One for his unicodedatabases on one for its standard 
databases to get correct sorting in his locale.

GREAT JOB !! THNX A LOT !!
peter

--
mag. peter pilsl
goldfisch.at
IT-management
tel +43 699 1 3574035
fax +43 699 4 3574035
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [BUGS] 8.0b3: problems with ILIKE and ~* on multibyte-chars

[Tom Lane]

peter pilsl <[EMAIL PROTECTED]> writes:
> the caseinsensitive patternmatch-operators seems not to work with 
> multibyte, while lower() and upper()-functions finally works perfect.

It looks to me like iwchareq() in src/backend/utils/adt/like.c still needs
work to handle multibyte characters in a sane fashion.  Feel free to
submit a patch ...

regards, tom lane

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [BUGS] semicolon not required on END statement

[Neil Conway]

Bruce Momjian wrote:
Improved wording that doesn't telegraph, "Look at me!":
Each declaration and each statement within a block is terminated by a
semicolon, though the final END that concludes a
function does not require one.
Patch applied.
-Neil
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[BUGS] BUG #1277: plpgsql EXECUTE bug in beta3

[PostgreSQL Bugs List]

The following bug has been logged online:

Bug reference:  1277
Logged by:  Tom Hebbron

Email address:  [EMAIL PROTECTED]

PostgreSQL version: 8.0 Beta

Operating system:   n/a

Description:plpgsql EXECUTE bug in beta3

Details: 

In beta2, the following script runs as expected:

--begin SQL script
drop table a cascade;

create or replace function execute_sql(text) returns void AS $$begin execute 
$1; return; end; $$ language plpgsql; 

select execute_sql('create table a (i integer); insert into a(i) 
values(1);'); 


select * from a;
--eof SQL script

The create table and insert commands are executed OK, and table 'a' contains 
a single row with value 1 in the column i. 


Under beta3, the following behaviour is observed:

test=# drop table a cascade;
ERROR:  table "a" does not exist
test=#
test=# create or replace function execute_sql(text) returns void AS $$begin 
execute $1; return; end; $$ language plpgsql; 
CREATE FUNCTION
test=#
test=# select execute_sql('create table a (i integer); insert into a(i) 
values(1);'); 
ERROR:  relation "a" does not exist
CONTEXT:  SQL query "create table a (i integer); insert into a(i) 
values(1);" 
PL/pgSQL function "execute_sql" line 1 at execute statement
test=#
test=# select * from a;
ERROR:  relation "a" does not exist
test=#

As demonstrated, some change in the plpgsql EXECUTE handler between beta2 
and beta3 has caused multiple statement EXECUTE calls to be unable to see 
the effects of earlier statements in the same EXECUTE call. 

calling select execute_sql('begin; create table a (i integer); insert into 
a(i) values(1); commit;'); 

has the same results.



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

   http://www.postgresql.org/docs/faqs/FAQ.html