#

# 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.

1 files changed, 108 insertions, 0 deletions

diff --git a/mysql-test/r/sp-ucs2.result b/mysql-test/r/sp-ucs2.result
index ce6be5b0a65..3806a84a849 100644
--- a/mysql-test/r/sp-ucs2.result
+++ b/mysql-test/r/sp-ucs2.result
@@ -12,3 +12,111 @@ a
 foo string
 drop function bug17615|
 drop table t3|
+CREATE FUNCTION f(f1 VARCHAR(64) CHARACTER SET ucs2 COLLATE ucs2_unicode_ci)
+RETURNS VARCHAR(64) CHARACTER SET ucs2 COLLATE ucs2_danish_ci
+BEGIN
+DECLARE f2 VARCHAR(64) CHARACTER SET ucs2 COLLATE ucs2_swedish_ci;
+DECLARE f3 VARCHAR(64) CHARACTER SET ucs2 COLLATE ucs2_bin;
+SET f1= concat(collation(f1), ' ', collation(f2), ' ', collation(f3));
+RETURN f1;
+END|
+SELECT f('a')|
+f('a')
+ucs2_unicode_ci ucs2_swedish_ci ucs2_bin
+SELECT collation(f('a'))|
+collation(f('a'))
+ucs2_danish_ci
+DROP FUNCTION f|
+CREATE FUNCTION f()
+RETURNS VARCHAR(64) UNICODE BINARY
+BEGIN
+RETURN '';
+END|
+SHOW CREATE FUNCTION f;
+DROP FUNCTION f;
+CREATE FUNCTION f()
+RETURNS VARCHAR(64) BINARY UNICODE
+BEGIN
+RETURN '';
+END|
+Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
+f		CREATE DEFINER=`root`@`localhost` FUNCTION `f`() RETURNS varchar(64) CHARSET ucs2 COLLATE ucs2_bin
+BEGIN
+RETURN '';
+END	latin1	latin1_swedish_ci	latin1_swedish_ci
+SHOW CREATE FUNCTION f;
+DROP FUNCTION f;
+#
+# Testing keywords ASCII + BINARY
+#
+CREATE FUNCTION f()
+RETURNS VARCHAR(64) ASCII BINARY
+BEGIN
+RETURN '';
+END|
+Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
+f		CREATE DEFINER=`root`@`localhost` FUNCTION `f`() RETURNS varchar(64) CHARSET ucs2 COLLATE ucs2_bin
+BEGIN
+RETURN '';
+END	latin1	latin1_swedish_ci	latin1_swedish_ci
+SHOW CREATE FUNCTION f;
+DROP FUNCTION f;
+CREATE FUNCTION f()
+RETURNS VARCHAR(64) BINARY ASCII
+BEGIN
+RETURN '';
+END|
+Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
+f		CREATE DEFINER=`root`@`localhost` FUNCTION `f`() RETURNS varchar(64) CHARSET latin1 COLLATE latin1_bin
+BEGIN
+RETURN '';
+END	latin1	latin1_swedish_ci	latin1_swedish_ci
+SHOW CREATE FUNCTION f;
+DROP FUNCTION f;
+#
+# Testing COLLATE in OUT parameter
+#
+CREATE PROCEDURE p1(IN  f1 VARCHAR(64) CHARACTER SET ucs2 COLLATE ucs2_czech_ci,
+OUT f2 VARCHAR(64) CHARACTER SET ucs2 COLLATE ucs2_polish_ci)
+BEGIN
+SET f2= f1;
+SET f2= concat(collation(f1), ' ', collation(f2));
+END|
+Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
+f		CREATE DEFINER=`root`@`localhost` FUNCTION `f`() RETURNS varchar(64) CHARSET latin1 COLLATE latin1_bin
+BEGIN
+RETURN '';
+END	latin1	latin1_swedish_ci	latin1_swedish_ci
+CREATE FUNCTION f1()
+RETURNS VARCHAR(64) CHARACTER SET ucs2
+BEGIN
+DECLARE f1 VARCHAR(64) CHARACTER SET ucs2;
+DECLARE f2 VARCHAR(64) CHARACTER SET ucs2;
+SET f1='str';
+CALL p1(f1, f2);
+RETURN f2;
+END|
+SELECT f1()|
+f1()
+ucs2_czech_ci ucs2_polish_ci
+DROP PROCEDURE p1|
+DROP FUNCTION f1|
+CREATE FUNCTION f(f1 VARCHAR(64) COLLATE ucs2_unicode_ci)
+RETURNS VARCHAR(64) CHARACTER SET ucs2
+BEGIN
+RETURN 'str';
+END|
+ERROR 42000: This version of MySQL doesn't yet support 'COLLATE with no CHARACTER SET in SP parameters, RETURNS, DECLARE'
+CREATE FUNCTION f(f1 VARCHAR(64) CHARACTER SET ucs2)
+RETURNS VARCHAR(64) COLLATE ucs2_unicode_ci
+BEGIN
+RETURN 'str';
+END|
+ERROR 42000: This version of MySQL doesn't yet support 'COLLATE with no CHARACTER SET in SP parameters, RETURNS, DECLARE'
+CREATE FUNCTION f(f1 VARCHAR(64) CHARACTER SET ucs2)
+RETURNS VARCHAR(64) CHARACTER SET ucs2
+BEGIN
+DECLARE f2 VARCHAR(64) COLLATE ucs2_unicode_ci;
+RETURN 'str';
+END|
+ERROR 42000: This version of MySQL doesn't yet support 'COLLATE with no CHARACTER SET in SP parameters, RETURNS, DECLARE'