Bug #16676 Database CHARSET not used for stored procedures
Submitted: 20 Jan 2006 15:50 Modified: 15 Aug 2006 13:00
Reporter: Richard Fearn Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.0.19-BK, 5.0.18 OS:Linux (Linux, Windows)
Assigned to: Alexander Nozdrin CPU Architecture:Any

[20 Jan 2006 15:50] Richard Fearn
Description:
Section 17.2.1 of the MySQL 5.0 manual (dated 2006-01-20, revision 944) states that this rule applies to stored procedure parameters:

"For character data types, if there is a CHARACTER SET clause in the declaration, the specified character set and its default collation are used. If there is no such clause, the database character set and collation are used. (These are given by the values of the character_set_database and collation_database system variables.)"

When a CHARACTER SET clause is omitted, the database character set does not seem to be used.

How to repeat:
Standard installation of MySQL 5.0.18 on Windows XP SP2. Default character set: latin1.

SHOW VARIABLES LIKE 'char%';

+--------------------------+----------------------+
| Variable_name            | Value                |
+--------------------------+----------------------+
| character_set_client     | latin1               |
| character_set_connection | latin1               |
| character_set_database   | latin1               |
| character_set_results    | latin1               |
| character_set_server     | latin1               |
| character_set_system     | utf8                 |
| character_sets_dir       | C:\Program Files\... |
+--------------------------+----------------------+

SHOW VARIABLES LIKE 'coll%';

+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+

(latin1 is the default character set.)

Create a database with hebrew as the default character set (hebrew chosen arbitrarily):

CREATE DATABASE chartest CHARACTER SET hebrew;

USE chartest;

SHOW VARIABLES LIKE 'char%';

+--------------------------+----------------------+
| Variable_name            | Value                |
+--------------------------+----------------------+
| character_set_client     | latin1               |
| character_set_connection | latin1               |
| character_set_database   | hebrew               |
| character_set_results    | latin1               |
| character_set_server     | latin1               |
| character_set_system     | utf8                 |
| character_sets_dir       | C:\Program Files\... |
+--------------------------+----------------------+

SHOW VARIABLES LIKE 'coll%';

+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | hebrew_general_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+

(The database character set has now changed to hebrew.)

CREATE TABLE testtable (A VARCHAR(50));

INSERT INTO testtable VALUES("test");

DELIMITER //
CREATE PROCEDURE showcolls (IN pA VARCHAR(50))
BEGIN
SELECT CHARSET(pA), COLLATION(pA), CHARSET(A), COLLATION(A) FROM testtable;
END
//
DELIMITER ;
CALL showcolls("test");

+-------------+-------------------+------------+-------------------+
| CHARSET(pA) | COLLATION(pA)     | CHARSET(A) | COLLATION(A)      |
+-------------+-------------------+------------+-------------------+
| latin1      | latin1_swedish_ci | hebrew     | hebrew_general_ci |
+-------------+-------------------+------------+-------------------+

CHARSET(pA) should be hebrew - i.e. the character set of the database.

DROP PROCEDURE showcolls;
DELIMITER //
CREATE PROCEDURE showcolls (IN pA VARCHAR(50) CHARACTER SET greek)
BEGIN
SELECT CHARSET(pA), COLLATION(pA), CHARSET(A), COLLATION(A) FROM testtable;
END
//
DELIMITER ;
CALL showcolls("test");

+-------------+------------------+------------+-------------------+
| CHARSET(pA) | COLLATION(pA)    | CHARSET(A) | COLLATION(A)      |
+-------------+------------------+------------+-------------------+
| greek       | greek_general_ci | hebrew     | hebrew_general_ci |
+-------------+------------------+------------+-------------------+

In this case, because greek (chosen arbitrarily) was specified in the declaration, it is used as the character set of the parameter.

Suggested fix:
Make stored procedure parameters inherit the character set of the database, if no character set is specified in the SP declaration.
[21 Jan 2006 11:39] Valeriy Kravchuk
Thank you for a detailed bug report. Verified just as described also with 5.0.19-BK (ChangeSet@1.1997, 2006-01-20 17:21:39+03:00) on Linux.

This is either a bug or, possibly, a request to check and change documentation on this issue.
[24 Jul 2006 11:41] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9488
[26 Jul 2006 20:00] Konstantin Osipov
Same patch as for Bug#16211, approved by email with a few minor comments.
[27 Jul 2006 13:59] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9665
[2 Aug 2006 15:51] Konstantin Osipov
Pushed into 5.0.25
[14 Aug 2006 21:14] Konstantin Osipov
Merged into 5.1.12
[15 Aug 2006 13:00] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.0.25 and 5.1.12 changelogs.