| Bug #51015 | LIKE with function fails, Illegal mix of collations | ||
|---|---|---|---|
| Submitted: | 9 Feb 2010 8:55 | Modified: | 9 Feb 2010 16:41 |
| Reporter: | Richard Teubel | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Charsets | Severity: | S2 (Serious) |
| Version: | 5.0.89, 5.1.41 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | function like charset collation_connection latin1_german2_ci | ||
[9 Feb 2010 9:05]
Richard Teubel
mysql> show variables like 'collation%'; +----------------------+-------------------+ | Variable_name | Value | +----------------------+-------------------+ | collation_connection | latin1_german2_ci | | collation_database | latin1_german2_ci | | collation_server | latin1_german2_ci | +----------------------+-------------------+ 3 rows in set (0.00 sec)
[9 Feb 2010 9:29]
Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php Please read at http://dev.mysql.com/doc/refman/5.1/en/create-procedure.html: 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 that are in effect at the time the routine is created are used. (These are given by the values of the character_set_database and collation_database system variables.) The COLLATE attribute is not supported. (This includes use of BINARY, because in this context BINARY specifies the binary collation of the character set.) As you specified CHARSET in function definition function will return value in default collation for this character set.
[9 Feb 2010 15:30]
Richard Teubel
But also without CHARSET in declaration of function it does not work !?
DELIMITER //;
CREATE FUNCTION `get_Name`(p_iUserID INTEGER(11))
RETURNS char(50)
DETERMINISTIC
CONTAINS SQL
SQL SECURITY INVOKER
COMMENT ''
BEGIN
DECLARE f_sReturn CHAR(50);
SELECT Name
INTO f_sReturn
FROM user
WHERE ID = p_iUserID;
RETURN f_sReturn;
END //;
DELIMITER ;
mysql> SELECT IF(get_Name(1) LIKE 'Müller', 1, 0);
ERROR 1267 (HY000): Unerlaubte Mischung von Sortierreihenfolgen (latin1_swedish_ci, COERCIBLE) und (latin1_german2_ci, COERCIBLE) für Operation 'like'
mysql> show variables LIKE 'collation_database';
+--------------------+-------------------+
| Variable_name | Value |
+--------------------+-------------------+
| collation_database | latin1_german2_ci |
+--------------------+-------------------+
1 row in set (0.00 sec)
mysql> show variables LIKE 'character_set_database';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| character_set_database | latin1 |
+------------------------+--------+
1 row in set (0.00 sec)
[9 Feb 2010 16:41]
Sveta Smirnova
Thank you for the feedback. > Or, should I open a new Bugreport with new terms? Not, you should not. You should read user manual about how to create database, stored procedure and which effect has collation and character set specified. We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug. Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/ Thank you for your interest in MySQL.
Description: I work with latin1_german2_ci. A query check the result of function with like and fails on illegal mix of collations. I think it's a bug. SELECT IF(get_Name(1) LIKE 'Müller', 1, 0); ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin1_german2_ci,COERCIBLE) for operation 'like' How to repeat: SET collation_connection=latin1_german2_ci; show variables like 'char%'; +--------------------------+--------------------------------------------------------------- ---------+ | Variable_name | Value | +--------------------------+--------------------------------------------------------------- ---------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql-5.0.89-linux-x86_64-icc-glibc23/share/mysql/c harsets/ | +--------------------------+--------------------------------------------------------------- ---------+ create database chartest; use chartest; CREATE TABLE `user` ( `ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `Name` VARCHAR( 50 ) NOT NULL ) ENGINE = MYISAM CHARACTER SET latin1 COLLATE latin1_german2_ci; INSERT INTO `user` (`ID` ,`Name` ) VALUES ('1', 'Müller'), ('2', 'Mayer'); DELIMITER //; CREATE FUNCTION `get_Name`(p_iUserID INTEGER(11)) RETURNS char(50) CHARSET latin1 DETERMINISTIC CONTAINS SQL SQL SECURITY INVOKER COMMENT '' BEGIN DECLARE f_sReturn CHAR(50); SELECT Name INTO f_sReturn FROM user WHERE ID = p_iUserID; RETURN f_sReturn; END //; DELIMITER ; SELECT IF(get_Name(1) LIKE 'Müller', 1, 0); ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin1_german2_ci,COERCIBLE) for operation 'like' SELECT IF(get_Name(1) = 'Müller', 1, 0); ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (latin1_german2_ci,COERCIBLE) for operation 'like' ->> Workaround!! SELECT IF(CONVERT(get_Name(1) USING latin1) = 'Müller', 1, 0); +--------------------------------------------------------+ | IF(CONVERT(get_Name(1) USING latin1) = 'Müller', 1, 0) | +--------------------------------------------------------+ | 1 | +--------------------------------------------------------+ 1 row in set (0.00 sec)