bk commit into 5.0 tree (anozdrin:1.2242) BUG#16676

[Alexander Nozdrin]

Below is the list of changes that have just been committed into a local
5.0 repository of alik. When alik does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2006-07-27 17:57:43+04:00, anozdrin@booka. +11 -0
  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@stripped, 2006-07-27 17:57:38+04:00, anozdrin@booka. +1 -1
    Updated result file.

  mysql-test/r/sp.result@stripped, 2006-07-27 17:57:39+04:00, anozdrin@booka. +153 -0
    Updated result file.

  mysql-test/t/sp.test@stripped, 2006-07-27 17:57:39+04:00, anozdrin@booka. +158 -0
    Provided test cases for BUG#16211, BUG#16676.

  sql/mysql_priv.h@stripped, 2006-07-27 17:57:39+04:00, anozdrin@booka. +3 -0
    Added two convenient functions for work with databases.

  sql/sp.cc@stripped, 2006-07-27 17:57:39+04:00, anozdrin@booka. +12 -0
    1. Add CHARSET-clause to CREATE-statement if it has been explicitly specified.
    2. Polishing -- provided some comments.

  sql/sp_head.cc@stripped, 2006-07-27 17:57:39+04:00, anozdrin@booka. +35 -18
    Use database charset as default charset of sp-variable.

  sql/sp_head.h@stripped, 2006-07-27 17:57:39+04:00, anozdrin@booka. +5 -1
    Move init_sp_name() out of init_strings().

  sql/sql_db.cc@stripped, 2006-07-27 17:57:39+04:00, anozdrin@booka. +84 -10
    Two new functions created:
      - load_db_opt_by_name();
      - check_db_dir_existence();

  sql/sql_show.cc@stripped, 2006-07-27 17:57:39+04:00, anozdrin@booka. +10 -30
    Eliminate duplicated code by using
    check_db_dir_existence() and load_db_opt_by_name()

  sql/sql_table.cc@stripped, 2006-07-27 17:57:39+04:00, anozdrin@booka. +3 -4
    Eliminate duplicated code by using
    check_db_dir_existence() and load_db_opt_by_name()

  sql/sql_yacc.yy@stripped, 2006-07-27 17:57:39+04:00, anozdrin@booka. +8 -5
    Call sp_head::init_sp_name() to initialize stored routine name.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	anozdrin
# Host:	booka.
# Root:	/home/alik/MySQL/devel/5.0-rt-bug16211

--- 1.398/sql/mysql_priv.h	2006-07-27 17:57:49 +04:00
+++ 1.399/sql/mysql_priv.h	2006-07-27 17:57:49 +04:00
@@ -1138,7 +1138,10 @@ uint check_word(TYPELIB *lib, const char
 bool is_keyword(const char *name, uint len);
 
 #define MY_DB_OPT_FILE "db.opt"
+bool check_db_dir_existence(const char *db_name);
 bool load_db_opt(THD *thd, const char *path, HA_CREATE_INFO *create);
+bool load_db_opt_by_name(THD *thd, const char *db_name,
+                         HA_CREATE_INFO *db_create_info);
 bool my_dbopt_init(void);
 void my_dbopt_cleanup(void);
 void my_dbopt_free(void);

--- 1.130/sql/sql_db.cc	2006-07-27 17:57:49 +04:00
+++ 1.131/sql/sql_db.cc	2006-07-27 17:57:49 +04:00
@@ -295,7 +295,6 @@ static bool write_db_opt(THD *thd, const
   create	Where to store the read options
 
   DESCRIPTION
-    For now, only default-character-set is read.
 
   RETURN VALUES
   0	File found
@@ -384,6 +383,50 @@ err1:
 
 
 /*
+  Retrieve database options by name. Load database options file or fetch from
+  cache.
+
+  SYNOPSIS
+    load_db_opt_by_name()
+    db_name         Database name
+    db_create_info  Where to store the database options
+
+  DESCRIPTION
+    load_db_opt_by_name() is a shortcut for load_db_opt().
+
+  NOTE
+    Although load_db_opt_by_name() (and load_db_opt()) returns status of
+    the operation, it is useless usually and should be ignored. The problem
+    is that there are 1) system databases ("mysql") and 2) virtual
+    databases ("information_schema"), which do not contain options file.
+    So, load_db_opt[_by_name]() returns FALSE for these databases, but this
+    is not an error.
+
+    load_db_opt[_by_name]() clears db_create_info structure in any case, so
+    even on failure it contains valid data. So, common use case is just
+    call load_db_opt[_by_name]() without checking return value and use
+    db_create_info right after that.
+
+  RETURN VALUES (read NOTE!)
+    FALSE   Success
+    TRUE    Failed to retrieve options
+*/
+
+bool load_db_opt_by_name(THD *thd, const char *db_name,
+                         HA_CREATE_INFO *db_create_info)
+{
+  char db_opt_path[FN_REFLEN];
+
+  strxnmov(db_opt_path, sizeof (db_opt_path) - 1, mysql_data_home, "/",
+           db_name, "/", MY_DB_OPT_FILE, NullS);
+
+  unpack_filename(db_opt_path, db_opt_path);
+
+  return load_db_opt(thd, db_opt_path, db_create_info);
+}
+
+
+/*
   Create a database
 
   SYNOPSIS
@@ -1126,8 +1169,6 @@ bool mysql_change_db(THD *thd, const cha
 {
   int path_length, db_length;
   char *db_name;
-  char	path[FN_REFLEN];
-  HA_CREATE_INFO create;
   bool system_db= 0;
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
   ulong db_access;
@@ -1196,16 +1237,14 @@ bool mysql_change_db(THD *thd, const cha
     }
   }
 #endif
-  (void) sprintf(path,"%s/%s", mysql_data_home, db_name);
-  path_length= unpack_dirname(path, path);      // Convert if not UNIX
-  if (path_length && path[path_length-1] == FN_LIBCHAR)
-    path[path_length-1]= '\0';                  // remove ending '\'
-  if (my_access(path,F_OK))
+
+  if (check_db_dir_existence(db_name))
   {
     my_error(ER_BAD_DB_ERROR, MYF(0), db_name);
     my_free(db_name, MYF(0));
     DBUG_RETURN(1);
   }
+
 end:
   x_free(thd->db);
   DBUG_ASSERT(db_name == NULL || db_name[0] != '\0');
@@ -1221,12 +1260,47 @@ end:
   }
   else
   {
-    strmov(path+unpack_dirname(path,path), MY_DB_OPT_FILE);
-    load_db_opt(thd, path, &create);
+    HA_CREATE_INFO create;
+
+    load_db_opt_by_name(thd, db_name, &create);
+
     thd->db_charset= create.default_table_charset ?
       create.default_table_charset :
       thd->variables.collation_server;
     thd->variables.collation_database= thd->db_charset;
   }
   DBUG_RETURN(0);
+}
+
+
+/*
+  Check if there is directory for the database name.
+
+  SYNOPSIS
+    check_db_dir_existence()
+    db_name   database name
+
+  RETURN VALUES
+    FALSE   There is directory for the specified database name.
+    TRUE    The directory does not exist.
+*/
+
+bool check_db_dir_existence(const char *db_name)
+{
+  char db_dir_path[FN_REFLEN];
+  uint db_dir_path_len;
+
+  strxnmov(db_dir_path, sizeof (db_dir_path) - 1, mysql_data_home, "/",
+           db_name, NullS);
+
+  db_dir_path_len= unpack_dirname(db_dir_path, db_dir_path);
+
+  /* Remove trailing '/' or '\' if exists. */
+
+  if (db_dir_path_len && db_dir_path[db_dir_path_len - 1] == FN_LIBCHAR)
+    db_dir_path[db_dir_path_len - 1]= 0;
+
+  /* Check access. */
+
+  return my_access(db_dir_path, F_OK);
 }

--- 1.323/sql/sql_show.cc	2006-07-27 17:57:49 +04:00
+++ 1.324/sql/sql_show.cc	2006-07-27 17:57:49 +04:00
@@ -439,13 +439,11 @@ bool mysqld_show_create_db(THD *thd, cha
 {
   Security_context *sctx= thd->security_ctx;
   int length;
-  char path[FN_REFLEN];
   char buff[2048];
   String buffer(buff, sizeof(buff), system_charset_info);
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
   uint db_access;
 #endif
-  bool found_libchar;
   HA_CREATE_INFO create;
   uint create_options = create_info ? create_info->options : 0;
   Protocol *protocol=thd->protocol;
@@ -480,23 +478,13 @@ bool mysqld_show_create_db(THD *thd, cha
   }
   else
   {
-    (void) sprintf(path,"%s/%s",mysql_data_home, dbname);
-    length=unpack_dirname(path,path);		// Convert if not unix
-    found_libchar= 0;
-    if (length && path[length-1] == FN_LIBCHAR)
-    {
-      found_libchar= 1;
-      path[length-1]=0;				// remove ending '\'
-    }
-    if (access(path,F_OK))
+    if (check_db_dir_existence(dbname))
     {
       my_error(ER_BAD_DB_ERROR, MYF(0), dbname);
       DBUG_RETURN(TRUE);
     }
-    if (found_libchar)
-      path[length-1]= FN_LIBCHAR;
-    strmov(path+length, MY_DB_OPT_FILE);
-    load_db_opt(thd, path, &create);
+
+    load_db_opt_by_name(thd, dbname, &create);
   }
   List<Item> field_list;
   field_list.push_back(new Item_empty_string("Database",NAME_LEN));
@@ -2319,8 +2307,11 @@ bool store_schema_shemata(THD* thd, TABL
 
 int fill_schema_shemata(THD *thd, TABLE_LIST *tables, COND *cond)
 {
-  char path[FN_REFLEN];
-  bool found_libchar;
+  /*
+    TODO: fill_schema_shemata() is called when new client is connected.
+    Returning error status in this case leads to client hangup.
+  */
+
   INDEX_FIELD_VALUES idx_field_vals;
   List<char> files;
   char *file_name;
@@ -2352,20 +2343,9 @@ int fill_schema_shemata(THD *thd, TABLE_
 	(grant_option && !check_grant_db(thd, file_name)))
 #endif
     {
-      strxmov(path, mysql_data_home, "/", file_name, NullS);
-      length=unpack_dirname(path,path);		// Convert if not unix
-      found_libchar= 0;
-      if (length && path[length-1] == FN_LIBCHAR)
-      {
-	found_libchar= 1;
-	path[length-1]=0;			// remove ending '\'
-      }
+      load_db_opt_by_name(thd, file_name, &create);
 
-      if (found_libchar)
-	path[length-1]= FN_LIBCHAR;
-      strmov(path+length, MY_DB_OPT_FILE);
-      load_db_opt(thd, path, &create);
-      if (store_schema_shemata(thd, table, file_name, 
+      if (store_schema_shemata(thd, table, file_name,
                                create.default_table_charset))
         DBUG_RETURN(1);
     }

--- 1.319/sql/sql_table.cc	2006-07-27 17:57:49 +04:00
+++ 1.320/sql/sql_table.cc	2006-07-27 17:57:49 +04:00
@@ -1631,10 +1631,9 @@ bool mysql_create_table(THD *thd,const c
   if (!create_info->default_table_charset)
   {
     HA_CREATE_INFO db_info;
-    char  path[FN_REFLEN];
-    /* Abuse build_table_path() to build the path to the db.opt file */
-    build_table_path(path, sizeof(path), db, MY_DB_OPT_FILE, "");
-    load_db_opt(thd, path, &db_info);
+
+    load_db_opt_by_name(thd, db, &db_info);
+
     create_info->default_table_charset= db_info.default_table_charset;
   }
 

--- 1.475/sql/sql_yacc.yy	2006-07-27 17:57:49 +04:00
+++ 1.476/sql/sql_yacc.yy	2006-07-27 17:57:49 +04:00
@@ -1285,6 +1285,7 @@ create_function_tail:
 	    sp= new sp_head();
 	    sp->reset_thd_mem_root(YYTHD);
 	    sp->init(lex);
+            sp->init_sp_name(YYTHD, lex->spname);
 
 	    sp->m_type= TYPE_ENUM_FUNCTION;
 	    lex->sphead= sp;
@@ -1339,7 +1340,7 @@ create_function_tail:
               YYABORT;
 
 	    lex->sql_command= SQLCOM_CREATE_SPFUNCTION;
-	    sp->init_strings(YYTHD, lex, lex->spname);
+	    sp->init_strings(YYTHD, lex);
             if (!(sp->m_flags & sp_head::HAS_RETURN))
             {
               my_error(ER_SP_NORETURN, MYF(0), sp->m_qname.str);
@@ -9100,6 +9101,7 @@ trigger_tail:
 	    YYABORT;
 	  sp->reset_thd_mem_root(YYTHD);
 	  sp->init(lex);
+          sp->init_sp_name(YYTHD, $3);
 	
 	  lex->stmt_definition_begin= $2;
           lex->ident.str= $7;
@@ -9128,7 +9130,7 @@ trigger_tail:
 	  sp_head *sp= lex->sphead;
 	  
 	  lex->sql_command= SQLCOM_CREATE_TRIGGER;
-	  sp->init_strings(YYTHD, lex, $3);
+	  sp->init_strings(YYTHD, lex);
 	  /* Restore flag if it was cleared above */
 	  if (sp->m_old_cmq)
 	    YYTHD->client_capabilities |= CLIENT_MULTI_QUERIES;
@@ -9176,13 +9178,14 @@ sp_tail:
 	    my_error(ER_SP_NO_RECURSIVE_CREATE, MYF(0), "PROCEDURE");
 	    YYABORT;
 	  }
-	  
+
 	  lex->stmt_definition_begin= $2;
-	  
+
 	  /* Order is important here: new - reset - init */
 	  sp= new sp_head();
 	  sp->reset_thd_mem_root(YYTHD);
 	  sp->init(lex);
+          sp->init_sp_name(YYTHD, $3);
 
 	  sp->m_type= TYPE_ENUM_PROCEDURE;
 	  lex->sphead= sp;
@@ -9220,7 +9223,7 @@ sp_tail:
 	  LEX *lex= Lex;
 	  sp_head *sp= lex->sphead;
 
-	  sp->init_strings(YYTHD, lex, $3);
+	  sp->init_strings(YYTHD, lex);
 	  lex->sql_command= SQLCOM_CREATE_PROCEDURE;
 	  /* Restore flag if it was cleared above */
 	  if (sp->m_old_cmq)

--- 1.203/mysql-test/r/sp.result	2006-07-27 17:57:49 +04:00
+++ 1.204/mysql-test/r/sp.result	2006-07-27 17:57:49 +04:00
@@ -5069,4 +5069,157 @@ END |
 SET @a = _latin2"aaaaaaaaaa" |
 CALL bug21013(10) |
 DROP PROCEDURE bug21013 |
+DROP DATABASE IF EXISTS mysqltest1|
+DROP DATABASE IF EXISTS mysqltest2|
+CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8|
+CREATE DATABASE mysqltest2 DEFAULT CHARACTER SET utf8|
+use mysqltest1|
+CREATE FUNCTION bug16211_f1() RETURNS CHAR(10)
+RETURN ""|
+CREATE FUNCTION bug16211_f2() RETURNS CHAR(10) CHARSET koi8r
+RETURN ""|
+CREATE FUNCTION mysqltest2.bug16211_f3() RETURNS CHAR(10)
+RETURN ""|
+CREATE FUNCTION mysqltest2.bug16211_f4() RETURNS CHAR(10) CHARSET koi8r
+RETURN ""|
+SHOW CREATE FUNCTION bug16211_f1|
+Function	sql_mode	Create Function
+bug16211_f1		CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f1`() RETURNS char(10)
CHARSET utf8
+RETURN ""
+SHOW CREATE FUNCTION bug16211_f2|
+Function	sql_mode	Create Function
+bug16211_f2		CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f2`() RETURNS char(10)
CHARSET koi8r
+RETURN ""
+SHOW CREATE FUNCTION mysqltest2.bug16211_f3|
+Function	sql_mode	Create Function
+bug16211_f3		CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f3`() RETURNS char(10)
CHARSET utf8
+RETURN ""
+SHOW CREATE FUNCTION mysqltest2.bug16211_f4|
+Function	sql_mode	Create Function
+bug16211_f4		CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f4`() RETURNS char(10)
CHARSET koi8r
+RETURN ""
+SELECT dtd_identifier
+FROM INFORMATION_SCHEMA.ROUTINES
+WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f1"|
+dtd_identifier
+char(10) CHARSET utf8
+SELECT dtd_identifier
+FROM INFORMATION_SCHEMA.ROUTINES
+WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f2"|
+dtd_identifier
+char(10) CHARSET koi8r
+SELECT dtd_identifier
+FROM INFORMATION_SCHEMA.ROUTINES
+WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f3"|
+dtd_identifier
+char(10) CHARSET utf8
+SELECT dtd_identifier
+FROM INFORMATION_SCHEMA.ROUTINES
+WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f4"|
+dtd_identifier
+char(10) CHARSET koi8r
+SELECT CHARSET(bug16211_f1())|
+CHARSET(bug16211_f1())
+utf8
+SELECT CHARSET(bug16211_f2())|
+CHARSET(bug16211_f2())
+koi8r
+SELECT CHARSET(mysqltest2.bug16211_f3())|
+CHARSET(mysqltest2.bug16211_f3())
+utf8
+SELECT CHARSET(mysqltest2.bug16211_f4())|
+CHARSET(mysqltest2.bug16211_f4())
+koi8r
+ALTER DATABASE mysqltest1 CHARACTER SET cp1251|
+ALTER DATABASE mysqltest2 CHARACTER SET cp1251|
+SHOW CREATE FUNCTION bug16211_f1|
+Function	sql_mode	Create Function
+bug16211_f1		CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f1`() RETURNS char(10)
CHARSET utf8
+RETURN ""
+SHOW CREATE FUNCTION bug16211_f2|
+Function	sql_mode	Create Function
+bug16211_f2		CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f2`() RETURNS char(10)
CHARSET koi8r
+RETURN ""
+SHOW CREATE FUNCTION mysqltest2.bug16211_f3|
+Function	sql_mode	Create Function
+bug16211_f3		CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f3`() RETURNS char(10)
CHARSET utf8
+RETURN ""
+SHOW CREATE FUNCTION mysqltest2.bug16211_f4|
+Function	sql_mode	Create Function
+bug16211_f4		CREATE DEFINER=`root`@`localhost` FUNCTION `bug16211_f4`() RETURNS char(10)
CHARSET koi8r
+RETURN ""
+SELECT dtd_identifier
+FROM INFORMATION_SCHEMA.ROUTINES
+WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f1"|
+dtd_identifier
+char(10) CHARSET utf8
+SELECT dtd_identifier
+FROM INFORMATION_SCHEMA.ROUTINES
+WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f2"|
+dtd_identifier
+char(10) CHARSET koi8r
+SELECT dtd_identifier
+FROM INFORMATION_SCHEMA.ROUTINES
+WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f3"|
+dtd_identifier
+char(10) CHARSET utf8
+SELECT dtd_identifier
+FROM INFORMATION_SCHEMA.ROUTINES
+WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f4"|
+dtd_identifier
+char(10) CHARSET koi8r
+SELECT CHARSET(bug16211_f1())|
+CHARSET(bug16211_f1())
+utf8
+SELECT CHARSET(bug16211_f2())|
+CHARSET(bug16211_f2())
+koi8r
+SELECT CHARSET(mysqltest2.bug16211_f3())|
+CHARSET(mysqltest2.bug16211_f3())
+utf8
+SELECT CHARSET(mysqltest2.bug16211_f4())|
+CHARSET(mysqltest2.bug16211_f4())
+koi8r
+use test|
+DROP DATABASE mysqltest1|
+DROP DATABASE mysqltest2|
+DROP DATABASE IF EXISTS mysqltest1|
+CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8|
+use mysqltest1|
+CREATE PROCEDURE bug16676_p1(
+IN p1 CHAR(10),
+INOUT p2 CHAR(10),
+OUT p3 CHAR(10))
+BEGIN
+SELECT CHARSET(p1), COLLATION(p1);
+SELECT CHARSET(p2), COLLATION(p2);
+SELECT CHARSET(p3), COLLATION(p3);
+END|
+CREATE PROCEDURE bug16676_p2(
+IN p1 CHAR(10) CHARSET koi8r,
+INOUT p2 CHAR(10) CHARSET cp1251,
+OUT p3 CHAR(10) CHARSET greek)
+BEGIN
+SELECT CHARSET(p1), COLLATION(p1);
+SELECT CHARSET(p2), COLLATION(p2);
+SELECT CHARSET(p3), COLLATION(p3);
+END|
+SET @v2 = 'b'|
+SET @v3 = 'c'|
+CALL bug16676_p1('a', @v2, @v3)|
+CHARSET(p1)	COLLATION(p1)
+utf8	utf8_general_ci
+CHARSET(p2)	COLLATION(p2)
+utf8	utf8_general_ci
+CHARSET(p3)	COLLATION(p3)
+utf8	utf8_general_ci
+CALL bug16676_p2('a', @v2, @v3)|
+CHARSET(p1)	COLLATION(p1)
+koi8r	koi8r_general_ci
+CHARSET(p2)	COLLATION(p2)
+cp1251	cp1251_general_ci
+CHARSET(p3)	COLLATION(p3)
+greek	greek_general_ci
+use test|
+DROP DATABASE mysqltest1|
 drop table t1,t2;

--- 1.191/mysql-test/t/sp.test	2006-07-27 17:57:49 +04:00
+++ 1.192/mysql-test/t/sp.test	2006-07-27 17:57:49 +04:00
@@ -5990,6 +5990,164 @@ DROP PROCEDURE bug21013 |
 
 
 #
+# BUG#16211: Stored function return type for strings is ignored
+#
+
+# Prepare: create database with fixed, pre-defined character set.
+
+--disable_warnings
+DROP DATABASE IF EXISTS mysqltest1|
+DROP DATABASE IF EXISTS mysqltest2|
+--enable_warnings
+
+CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8|
+CREATE DATABASE mysqltest2 DEFAULT CHARACTER SET utf8|
+
+# Test case:
+
+use mysqltest1|
+
+#   - Create two stored functions -- with and without explicit CHARSET-clause
+#     for return value;
+
+CREATE FUNCTION bug16211_f1() RETURNS CHAR(10)
+  RETURN ""|
+
+CREATE FUNCTION bug16211_f2() RETURNS CHAR(10) CHARSET koi8r
+  RETURN ""|
+
+CREATE FUNCTION mysqltest2.bug16211_f3() RETURNS CHAR(10)
+  RETURN ""|
+
+CREATE FUNCTION mysqltest2.bug16211_f4() RETURNS CHAR(10) CHARSET koi8r
+  RETURN ""|
+
+#   - Check that CHARSET-clause is specified for the second function;
+
+SHOW CREATE FUNCTION bug16211_f1|
+SHOW CREATE FUNCTION bug16211_f2|
+
+SHOW CREATE FUNCTION mysqltest2.bug16211_f3|
+SHOW CREATE FUNCTION mysqltest2.bug16211_f4|
+
+SELECT dtd_identifier
+FROM INFORMATION_SCHEMA.ROUTINES
+WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f1"|
+
+SELECT dtd_identifier
+FROM INFORMATION_SCHEMA.ROUTINES
+WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f2"|
+
+SELECT dtd_identifier
+FROM INFORMATION_SCHEMA.ROUTINES
+WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f3"|
+
+SELECT dtd_identifier
+FROM INFORMATION_SCHEMA.ROUTINES
+WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f4"|
+
+SELECT CHARSET(bug16211_f1())|
+SELECT CHARSET(bug16211_f2())|
+
+SELECT CHARSET(mysqltest2.bug16211_f3())|
+SELECT CHARSET(mysqltest2.bug16211_f4())|
+
+#   - Alter database character set.
+
+ALTER DATABASE mysqltest1 CHARACTER SET cp1251|
+ALTER DATABASE mysqltest2 CHARACTER SET cp1251|
+
+#   - Check that CHARSET-clause has not changed.
+
+SHOW CREATE FUNCTION bug16211_f1|
+SHOW CREATE FUNCTION bug16211_f2|
+
+SHOW CREATE FUNCTION mysqltest2.bug16211_f3|
+SHOW CREATE FUNCTION mysqltest2.bug16211_f4|
+
+SELECT dtd_identifier
+FROM INFORMATION_SCHEMA.ROUTINES
+WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f1"|
+
+SELECT dtd_identifier
+FROM INFORMATION_SCHEMA.ROUTINES
+WHERE ROUTINE_SCHEMA = "mysqltest1" AND ROUTINE_NAME = "bug16211_f2"|
+
+SELECT dtd_identifier
+FROM INFORMATION_SCHEMA.ROUTINES
+WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f3"|
+
+SELECT dtd_identifier
+FROM INFORMATION_SCHEMA.ROUTINES
+WHERE ROUTINE_SCHEMA = "mysqltest2" AND ROUTINE_NAME = "bug16211_f4"|
+
+SELECT CHARSET(bug16211_f1())|
+SELECT CHARSET(bug16211_f2())|
+
+SELECT CHARSET(mysqltest2.bug16211_f3())|
+SELECT CHARSET(mysqltest2.bug16211_f4())|
+
+# Cleanup.
+
+use test|
+
+DROP DATABASE mysqltest1|
+DROP DATABASE mysqltest2|
+
+
+#
+# BUG#16676: Database CHARSET not used for stored procedures
+#
+
+# Prepare: create database with fixed, pre-defined character set.
+
+--disable_warnings
+DROP DATABASE IF EXISTS mysqltest1|
+--enable_warnings
+
+CREATE DATABASE mysqltest1 DEFAULT CHARACTER SET utf8|
+
+# Test case:
+
+use mysqltest1|
+
+#   - Create two stored procedures -- with and without explicit CHARSET-clause;
+
+CREATE PROCEDURE bug16676_p1(
+  IN p1 CHAR(10),
+  INOUT p2 CHAR(10),
+  OUT p3 CHAR(10))
+BEGIN
+  SELECT CHARSET(p1), COLLATION(p1);
+  SELECT CHARSET(p2), COLLATION(p2);
+  SELECT CHARSET(p3), COLLATION(p3);
+END|
+
+CREATE PROCEDURE bug16676_p2(
+  IN p1 CHAR(10) CHARSET koi8r,
+  INOUT p2 CHAR(10) CHARSET cp1251,
+  OUT p3 CHAR(10) CHARSET greek)
+BEGIN
+  SELECT CHARSET(p1), COLLATION(p1);
+  SELECT CHARSET(p2), COLLATION(p2);
+  SELECT CHARSET(p3), COLLATION(p3);
+END|
+
+#   - Call procedures.
+
+SET @v2 = 'b'|
+SET @v3 = 'c'|
+
+CALL bug16676_p1('a', @v2, @v3)|
+CALL bug16676_p2('a', @v2, @v3)|
+
+# Cleanup.
+
+use test|
+
+DROP DATABASE mysqltest1|
+
+#
 # BUG#NNNN: New bug synopsis
 #
 #--disable_warnings

--- 1.113/sql/sp.cc	2006-07-27 17:57:49 +04:00
+++ 1.114/sql/sp.cc	2006-07-27 17:57:49 +04:00
@@ -495,6 +495,13 @@ sp_returns_type(THD *thd, String &result
   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_n
     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));

--- 1.219/sql/sp_head.cc	2006-07-27 17:57:49 +04:00
+++ 1.220/sql/sp_head.cc	2006-07-27 17:57:49 +04:00
@@ -470,7 +470,7 @@ sp_head::init(LEX *lex)
   lex->trg_table_fields.empty();
   my_init_dynamic_array(&m_instr, sizeof(sp_instr *), 16, 8);
   m_param_begin= m_param_end= m_body_begin= 0;
-  m_qname.str= m_db.str= m_name.str= m_params.str= 
+  m_qname.str= m_db.str= m_name.str= m_params.str=
     m_body.str= m_defstr.str= 0;
   m_qname.length= m_db.length= m_name.length= m_params.length=
     m_body.length= m_defstr.length= 0;
@@ -478,29 +478,42 @@ sp_head::init(LEX *lex)
   DBUG_VOID_RETURN;
 }
 
+
 void
-sp_head::init_strings(THD *thd, LEX *lex, sp_name *name)
+sp_head::init_sp_name(THD *thd, sp_name *spname)
+{
+  DBUG_ENTER("sp_head::init_sp_name");
+
+  /* Must be initialized in the parser. */
+
+  DBUG_ASSERT(spname && spname->m_db.str && spname->m_db.length);
+
+  /* We have to copy strings to get them into the right memroot. */
+
+  m_db.length= spname->m_db.length;
+  m_db.str= strmake_root(thd->mem_root, spname->m_db.str, spname->m_db.length);
+
+  m_name.length= spname->m_name.length;
+  m_name.str= strmake_root(thd->mem_root, spname->m_name.str,
+                           spname->m_name.length);
+
+  if (spname->m_qname.length == 0)
+    spname->init_qname(thd);
+
+  m_qname.length= spname->m_qname.length;
+  m_qname.str= strmake_root(thd->mem_root, spname->m_qname.str,
+                            m_qname.length);
+}
+
+
+void
+sp_head::init_strings(THD *thd, LEX *lex)
 {
   DBUG_ENTER("sp_head::init_strings");
   uchar *endp;                  /* Used to trim the end */
   /* During parsing, we must use thd->mem_root */
   MEM_ROOT *root= thd->mem_root;
 
-  DBUG_ASSERT(name);
-  /* Must be initialized in the parser */
-  DBUG_ASSERT(name->m_db.str && name->m_db.length);
-
-  /* We have to copy strings to get them into the right memroot */
-  m_db.length= name->m_db.length;
-  m_db.str= strmake_root(root, name->m_db.str, name->m_db.length);
-  m_name.length= name->m_name.length;
-  m_name.str= strmake_root(root, name->m_name.str, name->m_name.length);
-
-  if (name->m_qname.length == 0)
-    name->init_qname(thd);
-  m_qname.length= name->m_qname.length;
-  m_qname.str= strmake_root(root, name->m_qname.str, m_qname.length);
-
   if (m_param_begin && m_param_end)
   {
     m_params.length= m_param_end - m_param_begin;
@@ -1856,14 +1869,18 @@ sp_head::fill_field_definition(THD *thd,
                                enum enum_field_types field_type,
                                create_field *field_def)
 {
+  HA_CREATE_INFO sp_db_info;
   LEX_STRING cmt = { 0, 0 };
   uint unused1= 0;
   int unused2= 0;
 
+  load_db_opt_by_name(thd, m_db.str, &sp_db_info);
+
   if (field_def->init(thd, (char*) "", field_type, lex->length, lex->dec,
                       lex->type, (Item*) 0, (Item*) 0, &cmt, 0,
                       &lex->interval_list,
-                      (lex->charset ? lex->charset : default_charset_info),
+                      (lex->charset ? lex->charset :
+                                      sp_db_info.default_table_charset),
                       lex->uint_geom_type))
     return TRUE;
 

--- 1.86/sql/sp_head.h	2006-07-27 17:57:49 +04:00
+++ 1.87/sql/sp_head.h	2006-07-27 17:57:49 +04:00
@@ -193,9 +193,13 @@ public:
   void
   init(LEX *lex);
 
+  /* Copy sp name from parser. */
+  void
+  init_sp_name(THD *thd, sp_name *spname);
+
   // Initialize strings after parsing header
   void
-  init_strings(THD *thd, LEX *lex, sp_name *name);
+  init_strings(THD *thd, LEX *lex);
 
   int
   create(THD *thd);

--- 1.100/mysql-test/r/mysqldump.result	2006-07-27 17:57:49 +04:00
+++ 1.101/mysql-test/r/mysqldump.result	2006-07-27 17:57:49 +04:00
@@ -2248,7 +2248,7 @@ RETURN a+b */;;
 /*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
 /*!50003 DROP FUNCTION IF EXISTS `bug9056_func2` */;;
 /*!50003 SET SESSION SQL_MODE=""*/;;
-/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 FUNCTION
`bug9056_func2`(f1 char binary) RETURNS char(1)
+/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 FUNCTION
`bug9056_func2`(f1 char binary) RETURNS char(1) CHARSET latin1
 begin
 set f1= concat( 'hello', f1 );
 return f1;