MySQL Bugs: #16185: Lower(), Upper(), UCase() and LCase() do not work in a stored function

Bug #16185	Lower(), Upper(), UCase() and LCase() do not work in a stored function
Submitted:	4 Jan 2006 13:54	Modified:	4 Jan 2006 14:24
Reporter:	Edward R	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.17-nt	OS:	Windows (Windows XP Media Center)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
When creating a stored function to manipulate string values it is not possible to get Lower(), LCase(), Upper() or UCase() functions to work.  Inclusion of these functions does not cause the stored function to not compile but running the stored function shows that these string functions are not actually doing anything.  Either they are being ignored or they are simply passing on the input'd variable to the next function in sequence.

Example:

set tempString = LCASE( trim(oldString) );

Does nothing while:

set tempString = LCASE( trim( 'HELLO' ) );

will result in 'hello'.

It's very odd.

How to repeat:
DELIMITER $$

DROP FUNCTION IF EXISTS `ConvertWord` $$

CREATE FUNCTION `ConvertWord` (oldString varchar(256)) RETURNS varchar(256)
BEGIN
  declare tempString varchar(256) default '';

  set tempString = LCASE( trim(oldString) );

  return tempString;
END $$

DELIMITER ;

Suggested fix:
Not a clue.

Please do not submit the same bug more than once. An existing
bug report already describes this very problem. Even if you feel
that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments
to the original bug instead.

Thank you for your interest in MySQL.

Additional info:

http://bugs.mysql.com/bug.php?id=12903

C:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10 to server version: 5.0.18-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DELIMITER $$
mysql>
mysql> DROP FUNCTION IF EXISTS `ConvertWord` $$
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql>
mysql> CREATE FUNCTION `ConvertWord` (oldString varchar(256)) RETURNS varchar(256)
    -> BEGIN
    ->   declare tempString varchar(256) default '';
    ->
    ->   set tempString = LCASE( trim(oldString) );
    ->
    ->   return tempString;
    -> END $$
Query OK, 0 rows affected (0.06 sec)

mysql>
mysql> DELIMITER ;

mysql> select ConvertWord("MR JOHN");
+------------------------+
| ConvertWord("MR JOHN") |
+------------------------+
| mr john                |
+------------------------+
1 row in set (0.00 sec)

mysql>