Bug#48766 SHOW CREATE FUNCTION returns extra data in return clause

Problem: SHOW CREATE FUNCTION and SELECT DTD_IDENTIFIER FROM I_S.ROUTINES
returned wrong values in case of ENUM return data type and UCS2
character set.

Fix: the string to collect returned data type was incorrectly set to
"binary" character set, therefore UCS2 values where returned with
extra '\0' characters.
Setting string character set to creation_ctx->get_client_cs()
in sp_find_routine(), and to system_charset_info in sp_create_routine
fixes the problem.

Adding tests:
- the original test with Latin letters
- an extra test with non-Latin letters

3 files changed, 57 insertions, 0 deletions

diff --git a/mysql-test/r/sp-ucs2.result b/mysql-test/r/sp-ucs2.result
index ce6be5b0a65..1c266e38d97 100644
--- a/mysql-test/r/sp-ucs2.result
+++ b/mysql-test/r/sp-ucs2.result
@@ -12,3 +12,29 @@ a
 foo string
 drop function bug17615|
 drop table t3|
+SET NAMES utf8;
+DROP FUNCTION IF EXISTS bug48766;
+CREATE FUNCTION bug48766 ()
+RETURNS ENUM( 'w' ) CHARACTER SET ucs2
+RETURN 0;
+SHOW CREATE FUNCTION bug48766;
+Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
+bug48766		CREATE DEFINER=`root`@`localhost` FUNCTION `bug48766`() RETURNS enum('w') CHARSET ucs2
+RETURN 0	utf8	utf8_general_ci	latin1_swedish_ci
+SELECT DTD_IDENTIFIER FROM INFORMATION_SCHEMA.ROUTINES
+WHERE ROUTINE_NAME='bug48766';
+DTD_IDENTIFIER
+enum('w') CHARSET ucs2
+DROP FUNCTION bug48766;
+CREATE FUNCTION bug48766 ()
+RETURNS ENUM('а','б','в','г') CHARACTER SET ucs2
+RETURN 0;
+SHOW CREATE FUNCTION bug48766;
+Function	sql_mode	Create Function	character_set_client	collation_connection	Database Collation
+bug48766		CREATE DEFINER=`root`@`localhost` FUNCTION `bug48766`() RETURNS enum('а','б','в','г') CHARSET ucs2
+RETURN 0	utf8	utf8_general_ci	latin1_swedish_ci
+SELECT DTD_IDENTIFIER FROM INFORMATION_SCHEMA.ROUTINES
+WHERE ROUTINE_NAME='bug48766';
+DTD_IDENTIFIER
+enum('а','б','в','г') CHARSET ucs2
+DROP FUNCTION bug48766;
diff --git a/mysql-test/t/sp-ucs2.test b/mysql-test/t/sp-ucs2.test
index 7dd88b04871..7d6b62dfea0 100644
--- a/mysql-test/t/sp-ucs2.test
+++ b/mysql-test/t/sp-ucs2.test
@@ -26,3 +26,32 @@ drop table t3|
 
 
 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;
diff --git a/sql/sp.cc b/sql/sp.cc
index d3c5dfb96d0..6fad1d70ffd 100644
--- a/sql/sp.cc
+++ b/sql/sp.cc
@@ -900,6 +900,7 @@ sp_create_routine(THD *thd, int type, sp_head *sp)
   DBUG_PRINT("enter", ("type: %d  name: %.*s",type, (int) sp->m_name.length,
                        sp->m_name.str));
   String retstr(64);
+  retstr.set_charset(system_charset_info);
 
   DBUG_ASSERT(type == TYPE_ENUM_PROCEDURE ||
               type == TYPE_ENUM_FUNCTION);
@@ -1403,6 +1404,7 @@ sp_find_routine(THD *thd, int type, sp_name *name, sp_cache **cp,
       64 -- size of "returns" column of mysql.proc.
     */
     String retstr(64);
+    retstr.set_charset(sp->get_creation_ctx()->get_client_cs());
 
     DBUG_PRINT("info", ("found: 0x%lx", (ulong)sp));
     if (sp->m_first_free_instance)