1 files changed, 149 insertions, 0 deletions
diff --git a/mysql-test/t/sp-ucs2.test b/mysql-test/t/sp-ucs2.test
index 7dd88b04871..f6f70c3c103 100644
--- a/mysql-test/t/sp-ucs2.test
+++ b/mysql-test/t/sp-ucs2.test
@@ -25,4 +25,153 @@ 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 ;|
+
+#
+# Bug#48766 SHOW CREATE FUNCTION returns extra data in return clause
+#
+SET NAMES utf8;
+--disable_warnings
+DROP FUNCTION IF EXISTS bug48766;
+--enable_warnings
+#
+# Test that Latin letters are not prepended with extra '\0'.
+#
+CREATE FUNCTION bug48766 ()
+  RETURNS ENUM( 'w' ) CHARACTER SET ucs2
+  RETURN 0;
+SHOW CREATE FUNCTION bug48766;
+SELECT DTD_IDENTIFIER FROM INFORMATION_SCHEMA.ROUTINES
+WHERE ROUTINE_NAME='bug48766';
+DROP FUNCTION bug48766;
+#
+# Test non-Latin characters
+#
+CREATE FUNCTION bug48766 ()
+  RETURNS ENUM('а','б','в','г') CHARACTER SET ucs2
+  RETURN 0;
+SHOW CREATE FUNCTION bug48766;
+SELECT DTD_IDENTIFIER FROM INFORMATION_SCHEMA.ROUTINES
+WHERE ROUTINE_NAME='bug48766';
+
+DROP FUNCTION bug48766;