#

# 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, 120 insertions, 0 deletions

diff --git a/mysql-test/t/sp-ucs2.test b/mysql-test/t/sp-ucs2.test
index 7dd88b04871..0480d8f0799 100644
--- a/mysql-test/t/sp-ucs2.test
+++ b/mysql-test/t/sp-ucs2.test
@@ -25,4 +25,124 @@ drop function bug17615|
 drop table t3|
 
 
+#
+# Testing COLLATE clause in
+# - IN parameter
+# - RETURNS
+# - DELCARE
+#
+
+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')|
+SELECT collation(f('a'))|
+DROP FUNCTION f|
+
+#
+# Testing keywords UNICODE + BINARY
+#
+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|
+SHOW CREATE FUNCTION f;
+DROP FUNCTION f;
+
+
+#
+# Testing keywords ASCII + BINARY
+#
+CREATE FUNCTION f()
+  RETURNS VARCHAR(64) ASCII BINARY
+BEGIN
+  RETURN '';
+END|
+SHOW CREATE FUNCTION f;
+DROP FUNCTION f;
+
+CREATE FUNCTION f()
+  RETURNS VARCHAR(64) BINARY ASCII
+BEGIN
+  RETURN '';
+END|
+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|
+
+
+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()|
+DROP PROCEDURE p1|
+DROP FUNCTION f1|
+
+
+#
+# COLLATE with no CHARACTER SET in IN param
+#
+--error ER_NOT_SUPPORTED_YET
+CREATE FUNCTION f(f1 VARCHAR(64) COLLATE ucs2_unicode_ci)
+  RETURNS VARCHAR(64) CHARACTER SET ucs2
+BEGIN
+  RETURN 'str';
+END|
+
+
+#
+# COLLATE with no CHARACTER SET in RETURNS
+#
+--error ER_NOT_SUPPORTED_YET
+CREATE FUNCTION f(f1 VARCHAR(64) CHARACTER SET ucs2)
+  RETURNS VARCHAR(64) COLLATE ucs2_unicode_ci
+BEGIN
+  RETURN 'str';
+END|
+
+
+#
+# COLLATE with no CHARACTER SET in DECLARE
+#
+--error ER_NOT_SUPPORTED_YET
+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|
+
+
 delimiter ;|