MySQL Bugs: #12903: upper function does not work inside a function

Bug #12903	upper function does not work inside a function
Submitted:	31 Aug 2005 9:16	Modified:	7 Dec 2005 20:41
Reporter:	Rupert Barnes	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.0.11 beta	OS:	Linux (Linux, Windows)
Assigned to:	Alexander Nozdrin	CPU Architecture:	Any

Description:
the upper (and lower) function(s) does not appear to work when used within another function

How to repeat:
create function to_upper
(
  p_param varchar(128)
)
begin
  return upper(p_param);
end;
//

select to_upper('abc')

returns

'abc'

Test case (slightly corrected syntax):

delimiter //
create function to_upper (p_param varchar(128)) returns varchar(128)
begin
  return upper(p_param);
end;
//

delimiter ;

mysql> select to_upper('abc');
+-----------------+
| to_upper('abc') |
+-----------------+
| abc             |
+-----------------+
1 row in set (0.00 sec)

mysql> create table ta (ca varchar(255) binary);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into ta values('this is a test');
Query OK, 1 row affected (0.05 sec)

mysql> select * from ta;
+----------------+
| ca             |
+----------------+
| this is a test |
+----------------+
1 row in set (0.02 sec)

mysql> select upper(ca) from ta;
+----------------+
| upper(ca)      |
+----------------+
| THIS IS A TEST |
+----------------+
1 row in set (0.00 sec)

mysql> select to_upper('abc');
+-----------------+
| to_upper('abc') |
+-----------------+
| abc             |
+-----------------+
1 row in set (0.00 sec)

mysql> select to_upper(ca) from ta;
+----------------+
| to_upper(ca)   |
+----------------+
| this is a test |
+----------------+
1 row in set (0.00 sec)

mysql> select version();
+----------------+
| version()      |
+----------------+
| 5.0.11-beta-nt |
+----------------+
1 row in set (0.00 sec)

So, the questions really are: why upper() does not work inside a function and how to make it work.

The following function gives the same results:

create function to_upper4 (p_param varchar(128) ascii) returns varchar(128) ascii
begin
  return upper(p_param);
end;
//

I've also noticed upper does not work within a stored proc. Tried various things, including:-

/* buffering results into temp variable:*/
CREATE PROCEDURE hospitals_AddHospital (IN HospitalName char(150))
BEGIN
    DECLARE strHospital char(150);
    SET strHospital = UPPER(HospitalName);
    SELECT strHospital AS hospital;
END

/* inserting to table using upper inside a select statement (this was where I first noticed the problem: */
CREATE PROCEDURE hospitals_AddHospital (IN HospitalName char(150))
BEGIN
    INSERT INTO HOSPITALS
    (hospital_name)
    SELECT UPPER(HospitalName);
END

as well as combining the two methods.

Not only does it not work within stored functions, it doesn't seem to work on function results:
Consider this function.. 
drop function if exists regorder// 
create function regorder(regname char) 
returns char(5) 
deterministic 
begin 
declare regtype character; 
declare regorder character; 
declare regnumber integer; 
set regtype = substring(regname,1,1); 
if regtype = 'W' or regtype = 'w' then 
set regnumber = conv(substring(regname,2),16,10); 
else 
set regnumber = substring(regname,2); 
end if; 
set regorder = cast(regnumber as char); 
return upper(concat(regtype,lpad(regorder,4,'0'))); 
end 
// 
It's purpose is to take a data register (from a shop floor data collection system) in the form of 'D1100', D1120' etc. or 'WA', WC, W10'. The D registers have decimal extensions (1100, 1120 etc) and the W registers are coded hexadecimally. The function is supposed to rebuild the register code, converting the hexadecimal register numbers to decimal, and padding with zeros to the same length so that sort order presents data in the correct register order. 
now with that function, 
select regorder('wa') should return 'W0010', and select regorder('d1102') should return 'D1102') respectively. 
In fact, they return 'w0010' and 'd1102' respectively. . .? 
even 'select upper(regorder('wa'))' and select upper(regorder('d1102')) return the same values. 
The only way I can seem to get the correct result is: 
set @a = regorder('wa') 
select upper(@a) 

Environment: 
MySQL version 5.0.15-nt server running on Windows server 2003, 
client running on Windows XP professional service pack 2. 

Regards .. Alan

CREATE PROCEDURE testUpper(
    IN firstName VARCHAR(50)
) 
BEGIN 
    SELECT UPPER(firstName);
    SELECT UPPER(CONVERT(firstName USING latin1));
END

Output:
+------------------+
| UPPER(firstName) |
+------------------+
| mysql            |
+------------------+
1 row in set (0.00 sec)

+----------------------------------------+
| UPPER(CONVERT(firstName USING latin1)) |
+----------------------------------------+
| MYSQL                                  |
+----------------------------------------+
1 row in set (0.00 sec)

The latter seems to work...

Noted in 5.0.18 changelog.