Bug #24690 Stored functions: RETURNing UTF8 strings do not return UTF8_UNICODE_CI collation
Submitted: 29 Nov 2006 12:54 Modified: 1 Jun 2010 8:05
Reporter: Eric Jay Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.0, 5.1 OS:Linux (Linux, Windows)
Assigned to: Alexander Barkov CPU Architecture:Any
Tags: bfsm_2007_01_18, bfsm_2007_02_01, collation, stored function, utf8_unicode_ci

[29 Nov 2006 12:54] Eric Jay
Description:
If you create a stored function returning a UTF8 string, there is no simple way of telling that UTF8_UNICODE_CI strings should be returned and used in further expressions calling the function

Documentation says that the default collation for returned string is used (which is UTF8_GENERAL_CI in case of UTF8 character set).

However if you want your string expressions (calling your function) to use UTF8_UNICODE_CI you have to mention "collate UTF8_UNICODE_CI" in all terms of the expression.

Don't know if this is a bug or a feature request, but MySql seems not very consistent

How to repeat:
DELIMITER |

select version()|

SET collation_connection=utf8_unicode_ci|

SHOW VARIABLES LIKE '%character%'|
SHOW VARIABLES LIKE '%collation%'|

DROP DATABASE IF EXISTS mysqltest1|

CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8 COLLATE UTF8_UNICODE_CI|

SHOW CREATE DATABASE mysqltest1|

use mysqltest1|

--   - Create one stored function -- with explicit CHARSET-clause
--     for return value;

CREATE FUNCTION f1() RETURNS CHAR(10) CHARSET UTF8 DETERMINISTIC
  RETURN "abcd" collate utf8_unicode_ci|

--   - Check that CHARSET-clause is specified;

SHOW CREATE FUNCTION f1|

SELECT dtd_identifier
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "f1"|

--   - See what collation is understood;

SELECT CHARSET(f1()), COLLATION(f1())|

select collation(f1()) as test1|

select collation(concat("qwerty",f1())) as test2|

select collation(concat("qwerty" collate utf8_unicode_ci,f1())) as test3|

-- Cleanup.

use test|

DROP DATABASE mysqltest1|

Suggested fix:
1/ either allow to define in the "returns" clause of the "create function" the desired collation

2/ or pass back to the function caller the actual collation returned by the function
[29 Nov 2006 13:04] Eric Jay
here is the result I get on windows, 5.1.12-beta-community-nt-log; same happens on enterprise linux version 5.0.28
--------------
select version()
--------------

version()
5.1.12-beta-community-nt-log
--------------
SET collation_connection=utf8_unicode_ci
--------------

--------------
SHOW VARIABLES LIKE '%character%'
--------------

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.1\\share\\charsets\\
--------------
SHOW VARIABLES LIKE '%collation%'
--------------

Variable_name	Value
collation_connection	utf8_unicode_ci
collation_database	utf8_unicode_ci
collation_server	utf8_unicode_ci
--------------
DROP DATABASE IF EXISTS mysqltest1
--------------

--------------
CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8 COLLATE UTF8_UNICODE_CI
--------------

--------------
SHOW CREATE DATABASE mysqltest1
--------------

Database	Create Database
mysqltest1	CREATE DATABASE `mysqltest1` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */
--------------
CREATE FUNCTION f1() RETURNS CHAR(10) CHARSET UTF8 DETERMINISTIC
  RETURN "abcd" collate utf8_unicode_ci
--------------

--------------
SHOW CREATE FUNCTION f1
--------------

Function	sql_mode	Create Function
f1		CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS char(10) CHARSET utf8\n    DETERMINISTIC\nRETURN "abcd" collate utf8_unicode_ci
--------------
SELECT dtd_identifier
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "f1"
--------------

dtd_identifier
char(10) CHARSET utf8
--------------
SELECT CHARSET(f1()), COLLATION(f1())
--------------

CHARSET(f1())	COLLATION(f1())
utf8	utf8_general_ci
--------------
select collation(f1()) as test1
--------------

test1
utf8_general_ci
--------------
select collation(concat("qwerty",f1())) as test2
--------------

test2
utf8_bin
--------------
select collation(concat("qwerty" collate utf8_unicode_ci,f1())) as test3
--------------

test3
utf8_unicode_ci
--------------
DROP DATABASE mysqltest1
[29 Nov 2006 13:09] Eric Jay
on version 5.1.9 results where more consistent (see below); things apparently went wrong after fixing bugs 16676 and 16211

--------------
select version()
--------------

version()
5.1.9-beta-log
--------------
SET collation_connection=utf8_unicode_ci
--------------

--------------
SHOW VARIABLES LIKE '%character%'
--------------

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	/usr/bin/mysql/share/mysql/charsets/
--------------
SHOW VARIABLES LIKE '%collation%'
--------------

Variable_name	Value
collation_connection	utf8_unicode_ci
collation_database	utf8_unicode_ci
collation_server	utf8_unicode_ci
--------------
DROP DATABASE IF EXISTS mysqltest1
--------------

--------------
CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8 COLLATE UTF8_UNICODE_CI
--------------

--------------
SHOW CREATE DATABASE mysqltest1
--------------

Database	Create Database
mysqltest1	CREATE DATABASE `mysqltest1` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */
--------------
CREATE FUNCTION f1() RETURNS CHAR(10) CHARSET UTF8 DETERMINISTIC
  RETURN "abcd" collate utf8_unicode_ci
--------------

--------------
SHOW CREATE FUNCTION f1
--------------

Function	sql_mode	Create Function
f1		CREATE DEFINER=`root`@`%` FUNCTION `f1`() RETURNS char(10)\n    DETERMINISTIC\nRETURN "abcd" collate utf8_unicode_ci
--------------
SELECT dtd_identifier
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "f1"
--------------

dtd_identifier
char(10)
--------------
SELECT CHARSET(f1()), COLLATION(f1())
--------------

CHARSET(f1())	COLLATION(f1())
utf8	utf8_unicode_ci
--------------
select collation(f1()) as test1
--------------

test1
utf8_unicode_ci
--------------
select collation(concat("qwerty",f1())) as test2
--------------

test2
utf8_unicode_ci
--------------
select collation(concat("qwerty" collate utf8_unicode_ci,f1())) as test3
--------------

test3
utf8_unicode_ci
--------------
DROP DATABASE mysqltest1
--------------
[1 Dec 2006 20:44] Sveta Smirnova
Thank you for the report.

According to http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html I mark this report as feature request.

----<START QUOTE>----
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.)
----<END QUOTE>----
[1 Dec 2006 20:46] Sveta Smirnova
See also Aug, 15 comment by Alexander Nozdrin to bug #20937
[4 Dec 2006 9:56] Eric Jay
there still is a discrepency between the documentation and the reality

documentation says "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."

the example below shows the documentation not being correct or the code not working as documented

this example creates a function where character set is utf8 and collation is utf8_unicode_ci at the time the function is created; using this function shows the function does not return utf8_unicode_ci but utf8_general_ci:

DELIMITER |

select version()|

SET collation_connection=utf8_unicode_ci|

SHOW VARIABLES LIKE '%character%'|
SHOW VARIABLES LIKE '%collation%'|

DROP DATABASE IF EXISTS mysqltest2|

CREATE DATABASE mysqltest2 DEFAULT CHARACTER SET utf8 COLLATE UTF8_UNICODE_CI|

SHOW CREATE DATABASE mysqltest2|

use mysqltest2|

--   - Create one stored function -- no explicit CHARSET-clause
--     for return value;

CREATE FUNCTION f2() RETURNS CHAR(10)  DETERMINISTIC RETURN "abcd" |

--   - Check what CHARSET-clause was unterstood;

SHOW CREATE FUNCTION f2|

SELECT dtd_identifier
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "f2"|

--   - See what collation is understood; if 'utf8_general_ci' is shown it is not as documented

select 'if utf8_general_ci is shown it is not as documented' as comment, collation(f2()) as test1|

select 'if utf8_bin is shown is it as documented?' as comment,collation(concat("qwerty",f2())) as test2|

select 'if utf8_unicode_ci is shown why not in test2 above?' as comment, collation(concat("qwerty" collate utf8_unicode_ci,f2())) as test3|

-- Cleanup.

use test|

DROP DATABASE mysqltest2|

the result is as follows:

--------------
select version()
--------------

version()
5.1.12-beta-community-nt-log
--------------
SET collation_connection=utf8_unicode_ci
--------------

--------------
SHOW VARIABLES LIKE '%character%'
--------------

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.1\\share\\charsets\\
--------------
SHOW VARIABLES LIKE '%collation%'
--------------

Variable_name	Value
collation_connection	utf8_unicode_ci
collation_database	utf8_unicode_ci
collation_server	utf8_unicode_ci
--------------
DROP DATABASE IF EXISTS mysqltest2
--------------

--------------
CREATE DATABASE mysqltest2 DEFAULT CHARACTER SET utf8 COLLATE UTF8_UNICODE_CI
--------------

--------------
SHOW CREATE DATABASE mysqltest2
--------------

Database	Create Database
mysqltest2	CREATE DATABASE `mysqltest2` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */
--------------
CREATE FUNCTION f2() RETURNS CHAR(10)  DETERMINISTIC RETURN "abcd"
--------------

--------------
SHOW CREATE FUNCTION f2
--------------

Function	sql_mode	Create Function
f2		CREATE DEFINER=`root`@`localhost` FUNCTION `f2`() RETURNS char(10) CHARSET utf8\n    DETERMINISTIC\nRETURN "abcd"
--------------
SELECT dtd_identifier
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "f2"
--------------

dtd_identifier
char(10) CHARSET utf8
--------------
select 'if utf8_general_ci is shown it is not as documented' as comment, collation(f2()) as test1
--------------

comment	test1
if utf8_general_ci is shown it is not as documented	utf8_general_ci
--------------
select 'if utf8_bin is shown is it as documented?' as comment,collation(concat("qwerty",f2())) as test2
--------------

comment	test2
if utf8_bin is shown is it as documented?	utf8_bin
--------------
select 'if utf8_unicode_ci is shown why not in test2 above?' as comment, collation(concat("qwerty" collate utf8_unicode_ci,f2())) as test3
--------------

comment	test3
if utf8_unicode_ci is shown why not in test2 above?	utf8_unicode_ci
--------------
DROP DATABASE mysqltest2
--------------

are we talking about a feature request?, a bug in code?, a bug in documentation?
[4 Dec 2006 19:52] Sveta Smirnova
Thank you for explanation.

Seems to documentation be not clear about using collations. So I'll mark it as documentation bug.
[29 Dec 2008 5:40] MySQL Verification Team
This also affects functions using latin1/latin1_general_cs.
[28 Jan 2009 15:35] 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/64334

2983 Alexander Barkov	2009-01-28
      #
      # Bug#24690 Stored functions: RETURNing UTF8 strings
      # do not return UTF8_UNICODE_CI collation
      #
      
      mysql-test/r/sp-ucs2.result:
      mysql-test/t/sp-ucs2.test:
      
        Adding tests
      
      sql/mysql_priv.h:
        Adding prototype
      
      sql/sp.cc
        Remember COLLATE clause for non-default collations
      
      sql/sql_parse.cc
        Adding new helper function
      
      sql/sql_yacc.yy
        - Allow "CHARACTER SET cs COLLATE cl" in
          SP parameters, RETURNS, DECLARE
        - Minor reorganization for "ASCII" and "UNICODE" 
          related rules, to make the code more readable,
          also to allow these aliases:
          * "VARCHAR(10) ASCII BINARY"   -> CHARACTER SET latin1 COLLATE latin1_bin 
          * "VARCHAR(10) BINARY ASCII"   -> CHARACTER SET latin1 COLLATE latin1_bin 
          * "VARCHAR(10) UNICODE BINARY" -> CHARACTER SET ucs2 COLLATE ucs2_bin 
          * "VARCHAR(10) BINARY UNICODE" -> CHARACTER SET ucs2 COLLATE ucs2_bin  
          Previously these four aliases returned the error
          "This version of MySQL does not yet support return value collation".
      
      Note:
      
         This patch allows  "VARCHAR(10) CHARACTER SET cs COLLATE cl"
         and the above four aliases.
      
         "VARCHAR(10) COLLATE cl" is still not allowed
         i.e. when COLLATE is given without CHARACTER SET.
         If we want to support this, we need an architecture decision
         which character set to use by default.
[6 Apr 2009 13:05] 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/71450

3183 Alexander Barkov	2009-04-06
      #
      # Bug#24690 Stored functions: RETURNing UTF8 strings
      # do not return UTF8_UNICODE_CI collation
      #
            
            mysql-test/r/sp-ucs2.result:
            mysql-test/t/sp-ucs2.test:
            
              Adding tests
            
            sql/mysql_priv.h:
              Adding prototype
            
            sql/sp.cc
              Remember COLLATE clause for non-default collations
            
            sql/sql_parse.cc
              Adding new helper function
            
            sql/sql_yacc.yy
              - Allow "CHARACTER SET cs COLLATE cl" in
                SP parameters, RETURNS, DECLARE
              - Minor reorganization for "ASCII" and "UNICODE" 
                related rules, to make the code more readable,
                also to allow these aliases:
                * "VARCHAR(10) ASCII BINARY"   -> CHARACTER SET latin1 COLLATE latin1_bin 
                * "VARCHAR(10) BINARY ASCII"   -> CHARACTER SET latin1 COLLATE latin1_bin 
                * "VARCHAR(10) UNICODE BINARY" -> CHARACTER SET ucs2 COLLATE ucs2_bin 
                * "VARCHAR(10) BINARY UNICODE" -> CHARACTER SET ucs2 COLLATE ucs2_bin  
                Previously these four aliases returned the error
                "This version of MySQL does not yet support return value collation".
      
      Note:
      
               This patch allows  "VARCHAR(10) CHARACTER SET cs COLLATE cl"
               and the above four aliases.
            
               "VARCHAR(10) COLLATE cl" is still not allowed
               i.e. when COLLATE is given without CHARACTER SET.
               If we want to support this, we need an architecture decision
               which character set to use by default.
[6 Apr 2009 13:06] Alexander Barkov
Pushed into mysql-6.0-bugteam, currently marked as 6.0.11.
[8 Apr 2009 10:44] 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/71631

3191 Alexander Barkov	2009-04-08
      Post-fix for bug#24690 "Stored functions: RETURNing UTF8 strings do not return UTF8_UNICODE_CI collation"
      
      There was a mistake in the test file (wrong delimiter)
      which made "mysql-test-run --ps" fails. 
      Fixing the delimiter.
      
      modified:
        mysql-test/r/sp-ucs2.result
        mysql-test/t/sp-ucs2.test
[6 May 2009 14:10] Bugs System
Pushed into 6.0.12-alpha (revid:svoj@sun.com-20090506125450-yokcmvqf2g7jhujq) (version source revid:bar@mysql.com-20090408103406-7f9pswd9brptolzl) (merge vers: 6.0.11-alpha) (pib:6)
[15 May 2009 7:59] MySQL Verification Team
Hi Bar,

Do you have a plan to fix this bug on 5.1? This bug is impacting a customer.

Kind regards,
Mikiya
[15 May 2009 8:39] Konstantin Osipov
Mikiya,
if you beliefe the bug should be fixed in an earlier version, please request a re-triage.
[26 May 2009 19:52] Paul Dubois
Noted in 6.0.12 changelog.

Previously, the server handled character data types for a routine
parameter, local routine variable created with DECLARE, or function
return value as follows: If there was no CHARACTER SET attribute, the
database character set and its default collation were used. If the
CHARACTER SET attribute was present, the COLLATE attribute was not
supported, so the character set's default collation was used. (This
includes use of BINARY, because in this context BINARY specifies the
binary collation of the character set.)
          
Now for character data types, if there is a CHARACTER SET attribute
in the declaration, the specified character set and its default
collation is used. If the COLLATE is also present, that collation is
used rather than the default collation. If there is no CHARACTER SET
attribute, the database character set and collation in effect at
routine creation time are used. (The database character set and
collation are given by the value of the character_set_database and
collation_database system variables.)

Also updated http://dev.mysql.com/doc/refman/6.0/en/create-procedure.html accordingly.
[4 Jun 2009 22:10] Carsten Brandt
Is there a workaround for using functions with utf8_unicode_ci in Verion 5.1 or 5.4?
Could be something like changing the default collation of utf8 to utf8_unicode_ci for the whole server or tell him not to use utf8_general_ci at all.
I did not find any information about that in the doku and also google didn't help me so far...
[5 Jun 2009 9:37] Carsten Brandt
I found one, I tested on version 5.4 but should work on other versions too.

You have to CAST the value the function returns everytime you call it:

CAST(func_my_function(1, 'test') AS CHAR CHARACTER SET utf8) COLLATE utf8_unicode_ci;

http://dev.mysql.com/doc/refman/5.4/en/charset-convert.html

Hopefull this helps someone to easy find a solution, it took 3 full workdays for me to find the problem and solve it :-)
[24 Jul 2009 11:39] Konstantin Osipov
This bug was fixed.
[28 Oct 2009 9:26] 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/88462

2907 Alexander Barkov	2009-10-28
      #
      # Bug#24690 Stored functions: RETURNing UTF8 strings
      # do not return UTF8_UNICODE_CI collation
      #
      # Bug#17903: cast to char results in binary
      # Regression. The character set was not being properly initialized
      # for CAST() with a type like CHAR(2) BINARY, which resulted in
      # incorrect results or even a server crash.
      #
      
      Backporting from mysql-6.0-codebase.
      
      mysql-test/r/sp-ucs2.result:
      mysql-test/t/sp-ucs2.test:
      
        Adding tests
      
      sql/mysql_priv.h:
        Adding prototype
      
      sql/sp.cc
        Remember COLLATE clause for non-default collations
      
      sql/sql_parse.cc
        Adding a new helper function
      
      sql/sql_yacc.yy
        - Allow "CHARACTER SET cs COLLATE cl" in
          SP parameters, RETURNS, DECLARE
        - Minor reorganization for "ASCII" and "UNICODE"
          related rules, to make the code more readable,
          also to allow these aliases:
          * "VARCHAR(10) ASCII BINARY"   -> CHARACTER SET latin1 COLLATE latin1_bin
          * "VARCHAR(10) BINARY ASCII"   -> CHARACTER SET latin1 COLLATE latin1_bin
          * "VARCHAR(10) UNICODE BINARY" -> CHARACTER SET ucs2 COLLATE ucs2_bin
          * "VARCHAR(10) BINARY UNICODE" -> CHARACTER SET ucs2 COLLATE ucs2_bin
          Previously these four aliases returned the error
          "This version of MySQL does not yet support return value collation".
      
      Note:
      
         This patch allows  "VARCHAR(10) CHARACTER SET cs COLLATE cl"
         and the above four aliases.
      
         "VARCHAR(10) COLLATE cl" is still not allowed
         i.e. when COLLATE is given without CHARACTER SET.
         If we want to support this, we need an architecture decision
         which character set to use by default.
[30 Oct 2009 5:01] 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/88668

2912 Alexander Barkov	2009-10-30
      #
      # Bug#24690 Stored functions: RETURNing UTF8 strings
      # do not return UTF8_UNICODE_CI collation
      #
      # Bug#17903: cast to char results in binary
      # Regression. The character set was not being properly initialized
      # for CAST() with a type like CHAR(2) BINARY, which resulted in
      # incorrect results or even a server crash.
      #
      
      Backporting from mysql-6.0-codebase.
      
      mysql-test/r/sp-ucs2.result:
      mysql-test/t/sp-ucs2.test:
      
        Adding tests
      
      sql/mysql_priv.h:
        Adding prototype
      
      sql/sp.cc
        Remember COLLATE clause for non-default collations
      
      sql/sql_parse.cc
        Adding a new helper function
      
      sql/sql_yacc.yy
        - Allow "CHARACTER SET cs COLLATE cl" in
          SP parameters, RETURNS, DECLARE
        - Minor reorganization for "ASCII" and "UNICODE"
          related rules, to make the code more readable,
          also to allow these aliases:
          * "VARCHAR(10) ASCII BINARY"   -> CHARACTER SET latin1 COLLATE latin1_bin
          * "VARCHAR(10) BINARY ASCII"   -> CHARACTER SET latin1 COLLATE latin1_bin
          * "VARCHAR(10) UNICODE BINARY" -> CHARACTER SET ucs2 COLLATE ucs2_bin
          * "VARCHAR(10) BINARY UNICODE" -> CHARACTER SET ucs2 COLLATE ucs2_bin
          Previously these four aliases returned the error
          "This version of MySQL does not yet support return value collation".
      
      Note:
      
         This patch allows  "VARCHAR(10) CHARACTER SET cs COLLATE cl"
         and the above four aliases.
      
         "VARCHAR(10) COLLATE cl" is still not allowed
         i.e. when COLLATE is given without CHARACTER SET.
         If we want to support this, we need an architecture decision
         which character set to use by default.
[30 Oct 2009 5:08] 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/88671

3654 Alexander Barkov	2009-10-30 [merge]
      Null-merging Bug#24690 from mysql-next-mr-bar
[30 Oct 2009 14:40] Paul Dubois
This is in 5.4.4.
[9 Nov 2009 11:21] 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/89772

2943 Alexander Barkov	2009-11-09
      #
      # Bug#24690 Stored functions: RETURNing UTF8 strings
      # do not return UTF8_UNICODE_CI collation
      #
      # Bug#17903: cast to char results in binary
      # Regression. The character set was not being properly initialized
      # for CAST() with a type like CHAR(2) BINARY, which resulted in
      # incorrect results or even a server crash.
      #
      
      Backporting from mysql-6.0-codebase.
      
      mysql-test/r/sp-ucs2.result:
      mysql-test/t/sp-ucs2.test:
      
        Adding tests
      
      sql/mysql_priv.h:
        Adding prototype
      
      sql/sp.cc
        Remember COLLATE clause for non-default collations
      
      sql/sql_parse.cc
        Adding a new helper function
      
      sql/sql_yacc.yy
        - Allow "CHARACTER SET cs COLLATE cl" in
          SP parameters, RETURNS, DECLARE
        - Minor reorganization for "ASCII" and "UNICODE"
          related rules, to make the code more readable,
          also to allow these aliases:
          * "VARCHAR(10) ASCII BINARY"   -> CHARACTER SET latin1 COLLATE latin1_bin
          * "VARCHAR(10) BINARY ASCII"   -> CHARACTER SET latin1 COLLATE latin1_bin
          * "VARCHAR(10) UNICODE BINARY" -> CHARACTER SET ucs2 COLLATE ucs2_bin
          * "VARCHAR(10) BINARY UNICODE" -> CHARACTER SET ucs2 COLLATE ucs2_bin
          Previously these four aliases returned the error
          "This version of MySQL does not yet support return value collation".
      
      Note:
      
         This patch allows  "VARCHAR(10) CHARACTER SET cs COLLATE cl"
         and the above four aliases.
      
         "VARCHAR(10) COLLATE cl" is still not allowed
         i.e. when COLLATE is given without CHARACTER SET.
         If we want to support this, we need an architecture decision
         which character set to use by default.
[9 Nov 2009 11:34] 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/89775

3702 Alexander Barkov	2009-11-09 [merge]
      Null-merging Bug#24690 Bug#17903
[20 Nov 2009 12:57] Bugs System
Pushed into 6.0.14-alpha (revid:kostja@sun.com-20091120124947-yi6h2jbgw0kbciwm) (version source revid:epotemkin@mysql.com-20091109132131-ad1gk2d2tn9o5i3l) (merge vers: 6.0.14-alpha) (pib:13)
[23 Nov 2009 16:44] Paul Dubois
Already fixed in 6.0.x.

Setting report to NDI pending push to 5.6.x.
[11 Dec 2009 6:03] Bugs System
Pushed into 5.6.0-beta (revid:alik@sun.com-20091211055628-ltr7fero363uev7r) (version source revid:alik@sun.com-20091211055453-717czhtezc74u8db) (merge vers: 5.6.0-beta) (pib:13)
[11 Dec 2009 19:30] Paul Dubois
Noted in 5.6.0 changelog.
[6 Mar 2010 11:01] Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20091211201717-03qf8ckwiw0np80p) (merge vers: 5.6.0-beta) (pib:16)
[7 Mar 2010 18:48] Paul Dubois
Moved 5.6.0 changelog entry to 5.5.3.