| Bug #26905 | concat inside a stored function uses different collation for strings .... | ||
|---|---|---|---|
| Submitted: | 7 Mar 2007 8:09 | Modified: | 27 Sep 2007 13:57 |
| Reporter: | Peter Laursen (Basic Quality Contributor) | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Charsets | Severity: | S3 (Non-critical) |
| Version: | 5.0.36, 5.1 | OS: | Windows (win XP SP2, Linux) |
| Assigned to: | Konstantin Osipov | CPU Architecture: | Any |
| Tags: | qc | ||
[7 Mar 2007 9:58]
Sveta Smirnova
Thank you for the report. You issue 'show create function `myf`;' Please provide output of SHOW CREATE FUNCTION db2.myf\G and SHOW VARIABLES LIKE '%char%'
[7 Mar 2007 12:11]
Peter Laursen
OK then .. but why??????
mysql> use db1;
No connection. Trying to reconnect...
Connection id: 28
Current database: *** NONE ***
Database changed
mysql> SHOW VARIABLES LIKE '%char%';
+--------------------------+----------------------------------------------------
-----+
| Variable_name | Value
|
+--------------------------+----------------------------------------------------
-----+
| character_set_client | utf8
|
| character_set_connection | utf8
|
| character_set_database | utf8
|
| character_set_filesystem | binary
|
| character_set_results | utf8
|
| character_set_server | utf8
|
| character_set_system | utf8
|
| character_sets_dir | C:\Program Files\MySQL\MySQL Server 5.0\share\chars
ets\ |
+--------------------------+----------------------------------------------------
-----+
8 rows in set (0.00 sec)
mysql> use db2;
Database changed
mysql> SHOW VARIABLES LIKE '%char%';
+--------------------------+----------------------------------------------------
-----+
| Variable_name | Value
|
+--------------------------+----------------------------------------------------
-----+
| character_set_client | utf8
|
| character_set_connection | utf8
|
| character_set_database | latin1
|
| character_set_filesystem | binary
|
| character_set_results | utf8
|
| character_set_server | utf8
|
| character_set_system | utf8
|
| character_sets_dir | C:\Program Files\MySQL\MySQL Server 5.0\share\chars
ets\ |
+--------------------------+----------------------------------------------------
-----+
8 rows in set (0.00 sec)
mysql>
And the same selects from command line (where the Kannada characters are not supported on the system that I am working on right now)
mysql> use db1;
Database changed
mysql> select myf('peter');
+--------------------------+
| myf('peter') |
+--------------------------+
| ಕನಡಕನಡpeter! |
+--------------------------+
1 row in set (0.02 sec)
mysql> use db2;
Database changed
mysql> select myf('peter');
ERROR 1270 (HY000): Illegal mix of collations (utf8_general_ci,COERCIBLE), (lati
n1_swedish_ci,IMPLICIT), (utf8_general_ci,COERCIBLE) for operation 'concat'
mysql>
Further I can tell that both databases (in this setup created solely for this report) are completely empty except for the function. Databases created like 'create database <name> <charset>' and after that the create function statement was executed. The Kannada character was created using a not-yet-released SQLyog version.
[12 Apr 2007 11:33]
Sveta Smirnova
Thank you for the feedback. Verified as described on Linux using last BK sources.
[27 Sep 2007 13:54]
Konstantin Osipov
When character set of either INPUT parameter, RETURN value or DECLAREd variable in a stored routine is not specified, it is derived from the character set of the database the routine belongs to.
The character set of the database in this case is latin1 and it is not coercible with utf8.
A workaround is to specify the character set of the parameter:
CREATE FUNCTION `myf`(s CHAR(20) character set utf8) RETURNS char(50) CHARSET utf8 RETURN CONCAT('ಕನಡಕನಡ',s,'!'
[27 Sep 2007 13:54]
Konstantin Osipov
Complete paste:
CREATE FUNCTION `myf`(s CHAR(20) character set utf8) RETURNS char(50) CHARSET utf8 RETURN CONCAT('ಕನಡಕನಡ',s,'!');
[27 Sep 2007 13:55]
Konstantin Osipov
This was fixed in 5.0.25 by the fix for Bug#16776
[27 Sep 2007 13:56]
Konstantin Osipov
Sorry, Bug#16676
[27 Sep 2007 13:57]
Konstantin Osipov
This is, in fact, already documented: http://dev.mysql.com/doc/refman/5.0/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.)
Description: concat() function inside a STORED function uses different collation for strings from keyboard input depending on the default charset of currently selected database. (the example use the 'Kannada' language - the official language of Indian state Karnataka. You need support for 'complex scripts' enabled in Windows 'regional settings' to display the characters. On *nix I don't know what need to be installed...) How to repeat: use db1; show variables like 'character_set_database'; /* returns 'utf8' */ show create function `myf`; /* returns CREATE DEFINER=`root`@`%` FUNCTION `myf`(s CHAR(20)) RETURNS char(50) CHARSET utf8 RETURN CONCAT('ಕನಡಕನಡ',s,'!') */ select myf('peter'); /* returns 'ಕನಡಕನಡpeter!' */ use db2; show variables like 'character_set_database'; /* returns 'latin1' */ show create function `myf`; /* same return as above */ select myf('peter'); /* Error Code : 1270 Illegal mix of collations (utf8_general_ci,COERCIBLE), (latin1_swedish_ci,IMPLICIT), (utf8_general_ci,COERCIBLE) for operation 'concat' (0 ms taken) */ Suggested fix: I cannot see any logic in this. If this is expected behaviour then where and how is it documented?