MySQL Bugs: #20937: Instance collation instead of current database collation is used in SPs

Bug #20937	Instance collation instead of current database collation is used in SPs
Submitted:	10 Jul 2006 12:25	Modified:	15 Aug 2006 7:50
Reporter:	Andrey Kazachkov	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.22, 5.1	OS:	Linux (Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
If instance default collation and character set differ from database collation and character set there is no way to work with string columns in stored procedures. 
MySQL Server seems to use instance's default collation instead of current database's one while according to documentation (17.2.1. CREATE PROCEDURE and CREATE FUNCTION Syntax) "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."

How to repeat:
Following code doesn't work if instance default character set is latin1. Though it works if instance default character set is utf8. 
As specifying collation of stored procedure parameters is not supported we can't work with string columns. 

DELIMITER GO

USE `mysql`
GO

DROP DATABASE IF EXISTS `testdb`
GO

CREATE DATABASE `testdb` DEFAULT CHARACTER SET `ascii` COLLATE `ascii_general_ci`
GO

USE `testdb`
GO

CREATE TABLE `t_hosts` (
  `nId` int PRIMARY KEY auto_increment,
  `strName` varchar(256) UNIQUE COLLATE latin1_general_ci
) ENGINE=InnoDB DEFAULT CHARSET = ascii
GO

INSERT INTO `t_hosts` (`strName`) VALUES('xxx')
GO

CREATE PROCEDURE `hst_resolve_host_proc` (
    IN __vtmp_name VARCHAR(256)
)
BEGIN
    SELECT * FROM `t_hosts` WHERE `t_hosts`.`strName` < __vtmp_name;
END
GO

CALL `hst_resolve_host_proc`('xxy')
GO

ERROR 1267 (HY000): Illegal mix of collations (latin1_general_ci,IMPLICIT) and (
latin1_swedish_ci,IMPLICIT) for operation '<'

I've run into this problem myself, using version 5.0.18-nt, several times in the past but never investigated it thoroughly until recently. I have found as a workaround it is possible to add "COLLATE <collation-name> in the SP wherever the error comes up to coerce the collation to the correct one. I find this more of an annoyance than a show-stopper. My test case includes setting the collation of a parameter explicitly in the CALL statement, e.g. "CALL test('xyz' collate latin1_general_ci)", and this does not work around the issue, only coercing the collation inside of the sproc every time a parameter is referenced.

My requests:

-- The default character set and collation of a trigger, stored procedure or function should match that of the database it is created in.

-- It should be possible to specify in the header of the trigger, stored procedure or function, an alternate default character set and collation to apply within said routine. The syntax should be similar to the default charset/collate clauses on database and table creation statements.

-- It should be possible to specify in the header of a stored procedure or function, the specific character set and/or collation of an individual parameter. The syntax should match that used in table creation statements.

Thank you for the report.

Verified on Linux using last 5.0 and 5.1 BK sources as described:

mysql> set collation_connection=ascii_general_ci;
Query OK, 0 rows affected (0.00 sec)
mysql> set collation_database=ascii_general_ci;
Query OK, 0 rows affected (0.00 sec)
mysql> set collation_server=ascii_general_ci;
Query OK, 0 rows affected (0.00 sec)
mysql>  show variables like '%coll%';
+----------------------+------------------+
| Variable_name        | Value            |
+----------------------+------------------+
| collation_connection | ascii_general_ci |
| collation_database   | ascii_general_ci |
| collation_server     | ascii_general_ci |
+----------------------+------------------+
3 rows in set (0.00 sec)
mysql> DELIMITER GO
mysql> CREATE DATABASE `testdb` DEFAULT CHARACTER SET `ascii` COLLATE
    -> `ascii_general_ci`
    -> GO
Query OK, 1 row affected (0.01 sec)
mysql> USE `testdb`
Database changed
mysql> CREATE TABLE `t_hosts` (
    ->   `nId` int PRIMARY KEY auto_increment,
    ->   `strName` varchar(256) UNIQUE
    -> )
    -> GO
Query OK, 0 rows affected (0.20 sec)
mysql> INSERT INTO `t_hosts` (`strName`) VALUES('xxx')
    -> GO
Query OK, 1 row affected (0.00 sec)
mysql> CREATE PROCEDURE `hst_resolve_host_proc` (
    ->     IN __vtmp_name VARCHAR(256)
    -> )
    -> BEGIN
    ->     SELECT * FROM `t_hosts` WHERE `t_hosts`.`strName` < __vtmp_name;
    -> END
    -> GO
Query OK, 0 rows affected (0.00 sec)
mysql> CALL `hst_resolve_host_proc`('xxy')
    -> GO
ERROR 1267 (HY000): Illegal mix of collations (ascii_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '<'
mysql> SELECT * FROM `t_hosts` WHERE `t_hosts`.`strName` < cast('xxy' as char(255));
+-----+---------+
| nId | strName |
+-----+---------+
|   1 | xxx     |
+-----+---------+
1 row in set (0.00 sec)
mysql> set collation_connection=latin1_general_ci;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%coll%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_general_ci |
| collation_database   | ascii_general_ci  |
| collation_server     | ascii_general_ci  |
+----------------------+-------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM `t_hosts` WHERE `t_hosts`.`strName` < cast('xxy' as char(255));
ERROR 1267 (HY000): Illegal mix of collations (ascii_general_ci,IMPLICIT) and (latin1_general_ci,IMPLICIT) for operation '<'

This bug is a duplicate of BUG#16676: Database CHARSET not used for stored procedures.

Support of COLLATE-clause for stored-routine-variables is a new feature.
It's planned, but will not be implemented in scope of this bug.