whitespaces and upper()

============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================

Your name : Flavio
Your email address : fcasadei(at)prato(dot)linux(dot)it

System Configuration
---------------------
Architecture (example: Intel Pentium) : Intel pantium & Motorola PPC

Operating System (example: Linux 2.4.18) : linux 2.4.{18,21,26} 2.6.5 and darvin 7.0

PostgreSQL version (example: PostgreSQL-7.4.2): PostgreSQL-7.4.2 and 7.4.1

Compiler used (example: gcc 2.95.2) : various

Please enter a FULL description of your problem:
------------------------------------------------

Hi,
i have a problem with upper() / lower() and "whitespace" conversion,
this is a 7.4.* behaviour; string with whitespaces are not correctly
"matched".

Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
create table foo (
id integer not null primary key,
name char(6) not null,
value varchar(255)
);
insert into foo values (1,'FLAVIO','hi!');
insert into foo values (2,'FLA ' ,'hi!');
insert into foo values (3,' ','hi!');
insert into foo values (4,' d ','hi!');
test=> SELECT * from foo ;
id | name | value
----+--------+-------
1 | FLAVIO | hi!
2 | FLA | hi!
3 | | hi!
4 | d | hi!
(4 righe)
------->(righe means row)<---------

test=> SHOW client_encoding ;
client_encoding
-----------------
LATIN1
(1 riga)

test=> SHOW server_encoding ;
server_encoding
-----------------
LATIN1
(1 riga)

test=> SELECT * from foo where upper(name) like '% ';
id | name | value
----+------+-------
(0 righe)

----> Strange no? look at this (no upper)<----
test=> SELECT * from foo where name like '% ';
id | name | value
----+--------+-------
2 | FLA | hi!
3 | | hi!
4 | d | hi!
(3 righe)
that's ok
---->look at this now .... (ilike instead of like) <----
test=> SELECT * from foo where name ilike 'f% ';
id | name | value
----+--------+-------
2 | FLA | hi!
(1 riga)
test=> SELECT * from foo where name ilike '% ';
id | name | value
----+--------+-------
2 | FLA | hi!
3 | | hi!
4 | d | hi!
(3 righe)

i think there is a problem with upper() and lower() whitespace conversion
the problem arises also if i use = instead of like.

i tried this on
7.4.1 red hat 7.3
7.4.2 debian sid
7.4.2 mac os X
the problem persists also if i change the encoding in unicode.

Is a bug? or a "normal behaviour"?

Ciao

If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------
don't know, i wonder wy ilike works and upper() (and lower!!) fail.
With 7.3 is all ok!