Fix for BUG#16211: Stored function return type for strings is ignored.

Fix for BUG#16676: Database CHARSET not used for stored procedures

The problem in BUG#16211 is that CHARSET-clause of the return type for
stored functions is just ignored.

The problem in BUG#16676 is that if character set is not explicitly
specified for sp-variable, the server character set is used instead
of the database one.

The fix has two parts:

  - always store CHARSET-clause of the return type along with the
    type definition in mysql.proc.returns column. "Always" means that
    CHARSET-clause is appended even if it has not been explicitly
    specified in CREATE FUNCTION statement (this affects BUG#16211 only).

    Storing CHARSET-clause if it is not specified is essential to avoid
    changing character set if the database character set is altered in
    the future.

    NOTE: this change is not backward compatible with the previous releases.

  - use database default character set if CHARSET-clause is not explicitly
    specified (this affects both BUG#16211 and BUG#16676).

    NOTE: this also breaks backward compatibility.


mysql-test/r/mysqldump.result:
  Updated result file.
mysql-test/r/sp.result:
  Updated result file.
mysql-test/t/sp.test:
  Provided test cases for BUG#16211, BUG#16676.
sql/mysql_priv.h:
  Added two convenient functions for work with databases.
sql/sp.cc:
  1. Add CHARSET-clause to CREATE-statement if it has been explicitly specified.
  2. Polishing -- provided some comments.
sql/sp_head.cc:
  Use database charset as default charset of sp-variable.
sql/sp_head.h:
  Move init_sp_name() out of init_strings().
sql/sql_db.cc:
  Two new functions created:
    - load_db_opt_by_name();
    - check_db_dir_existence();
sql/sql_show.cc:
  Eliminate duplicated code by using
  check_db_dir_existence() and load_db_opt_by_name()
sql/sql_table.cc:
  Eliminate duplicated code by using
  check_db_dir_existence() and load_db_opt_by_name()
sql/sql_yacc.yy:
  Call sp_head::init_sp_name() to initialize stored routine name.

1 files changed, 12 insertions, 0 deletions

diff --git a/sql/sp.cc b/sql/sp.cc
index 553465ebff8..b5a4f8bad8f 100644
--- a/sql/sp.cc
+++ b/sql/sp.cc
@@ -495,6 +495,13 @@ sp_returns_type(THD *thd, String &result, sp_head *sp)
   table.s = &table.share_not_to_be_used;
   field= sp->create_result_field(0, 0, &table);
   field->sql_type(result);
+
+  if (field->has_charset())
+  {
+    result.append(STRING_WITH_LEN(" CHARSET "));
+    result.append(field->charset()->csname);
+  }
+
   delete field;
 }
 
@@ -974,6 +981,11 @@ sp_find_routine(THD *thd, int type, sp_name *name, sp_cache **cp,
     sp_head *new_sp;
     const char *returns= "";
     char definer[USER_HOST_BUFF_SIZE];
+
+    /*
+      String buffer for RETURNS data type must have system charset;
+      64 -- size of "returns" column of mysql.proc.
+    */
     String retstr(64);
 
     DBUG_PRINT("info", ("found: 0x%lx", (ulong)sp));