Bug #16330 CHARACTER SET still not working in stored functions

• Submitted: 10 Jan 2006 10:09
• Modified: 22 Jun 2006 15:09
• Reporter: Gleb Paharenko
• Status: Duplicate
• Category: MySQL Server: Stored Routines
• Severity: S3 (Non-critical)
• Version: 5.0.19-BK, 5.0.18
• OS: Linux (Linux)
• Assigned to: Assigned Account
• CPU Architecture: Any

[10 Jan 2006 10:09] Gleb Paharenko

Description:
See how to repeat

How to repeat:
DROP FUNCTION IF EXISTS tokyo;
DROP PROCEDURE IF EXISTS ptokyo;
DELIMITER //
CREATE FUNCTION tokyo() RETURNS VARCHAR(20) CHARACTER SET utf8
DETERMINISTIC
BEGIN
DECLARE TVAR VARCHAR(20) CHARACTER SET utf8;
SET TVAR = "hello";
        RETURN TVAR;
END//
CREATE PROCEDURE ptokyo(OUT PAR1 VARCHAR(20) CHARACTER SET utf8)
DETERMINISTIC
BEGIN
DECLARE TVAR VARCHAR(20) CHARACTER SET utf8;
SET TVAR = "hello";
SET PAR1 = TVAR;
END//
DELIMITER ;
select charset(tokyo());
call ptokyo(@a);
select charset(@a);

[gleb@blend mysql-debug-5.0.18-linux-i686-glibc23]$ lmysql <res
charset(tokyo())
binary
charset(@a)
utf8

We see that CHARACTER SET doesn't work for functions, while it works fing for stored procedures

Suggested fix:
Fix functions as well

[10 Jan 2006 10:42] Valeriy Kravchuk

Thank you for a bug report. Verified just as described with 5.0.19-BK (ChangeSet@1.2020, 2006-01-06 08:34:55-08:00) on Linux:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.19

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

mysql> DELIMITER //
mysql> CREATE FUNCTION tokyo() RETURNS VARCHAR(20) CHARACTER SET utf8
    -> DETERMINISTIC
    -> BEGIN
    -> DECLARE TVAR VARCHAR(20) CHARACTER SET utf8;
    -> SET TVAR = "hello";
    ->         RETURN TVAR;
    -> END//
Query OK, 0 rows affected (0.12 sec)

mysql> CREATE PROCEDURE ptokyo(OUT PAR1 VARCHAR(20) CHARACTER SET utf8)
    -> DETERMINISTIC
    -> BEGIN
    -> DECLARE TVAR VARCHAR(20) CHARACTER SET utf8;
    -> SET TVAR = "hello";
    -> SET PAR1 = TVAR;
    -> END//
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> select charset(tokyo());
+------------------+
| charset(tokyo()) |
+------------------+
| binary           |
+------------------+
1 row in set (0.02 sec)

omysql> call ptokyo(@a);
Query OK, 0 rows affected (0.01 sec)

semysql> select charset(@a);
+-------------+
| charset(@a) |
+-------------+
| utf8        |
+-------------+
1 row in set (0.00 sec)

[22 Jun 2006 15:09] Alexander Nozdrin

This is a duplicate of BUG#16211 (Stored function
return type for strings is ignored).