Merge mysql.com:/usr/local/bk/mysql-5.0

into mysql.com:/home/pem/work/mysql-5.0-merge


sql/sp.cc:
  Auto merged

6 files changed, 197 insertions, 69 deletions

diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index 8595f1ecebd..1e1558f91af 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -734,8 +734,8 @@ chistics	1
 delete from t1|
 alter procedure chistics sql security invoker name chistics2|
 show create procedure chistics2|
-Procedure	Create Procedure
-chistics2	CREATE PROCEDURE `test`.`chistics2`()
+Procedure	sql_mode	Create Procedure
+chistics2		CREATE PROCEDURE `test`.`chistics2`()
     SQL SECURITY INVOKER
     COMMENT 'Characteristics procedure test'
 insert into t1 values ("chistics", 1)
@@ -751,8 +751,8 @@ chistics()
 42
 alter function chistics name chistics2 comment 'Characteristics function test'|
 show create function chistics2|
-Function	Create Function
-chistics2	CREATE FUNCTION `test`.`chistics2`() RETURNS int
+Function	sql_mode	Create Function
+chistics2		CREATE FUNCTION `test`.`chistics2`() RETURNS int
     DETERMINISTIC
     SQL SECURITY INVOKER
     COMMENT 'Characteristics function test'
@@ -980,14 +980,14 @@ call bug2267_2()|
 Db	Name	Type	Definer	Modified	Created	Security_type	Comment
 test	fac	FUNCTION	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
 call bug2267_3()|
-Procedure	Create Procedure
-bug2267_1	CREATE PROCEDURE `test`.`bug2267_1`()
+Procedure	sql_mode	Create Procedure
+bug2267_1		CREATE PROCEDURE `test`.`bug2267_1`()
 begin
 show procedure status;
 end
 call bug2267_4()|
-Function	Create Function
-fac	CREATE FUNCTION `test`.`fac`(n int unsigned) RETURNS bigint unsigned
+Function	sql_mode	Create Function
+fac		CREATE FUNCTION `test`.`fac`(n int unsigned) RETURNS bigint unsigned
 begin
 declare f bigint unsigned default 1;
 while n > 1 do
@@ -1318,6 +1318,41 @@ s1
 drop procedure bug2460_1|
 drop procedure bug2460_2|
 drop table t3|
+set @@sql_mode = ''|
+create procedure bug2564_1()
+comment 'Joe''s procedure'
+  insert into `t1` values ("foo", 1)|
+set @@sql_mode = 'ANSI_QUOTES'|
+create procedure bug2564_2()
+insert into "t1" values ('foo', 1)|
+set @@sql_mode = ''$
+create function bug2564_3(x int, y int) returns int
+return x || y$
+set @@sql_mode = 'ANSI'$
+create function bug2564_4(x int, y int) returns int
+return x || y$
+set @@sql_mode = ''|
+show create procedure bug2564_1|
+Procedure	sql_mode	Create Procedure
+bug2564_1		CREATE PROCEDURE `test`.`bug2564_1`()
+    COMMENT 'Joe''s procedure'
+insert into `t1` values ("foo", 1)
+show create procedure bug2564_2|
+Procedure	sql_mode	Create Procedure
+bug2564_2	ANSI_QUOTES	CREATE PROCEDURE "test"."bug2564_2"()
+insert into "t1" values ('foo', 1)
+show create function bug2564_3|
+Function	sql_mode	Create Function
+bug2564_3		CREATE FUNCTION `test`.`bug2564_3`(x int, y int) RETURNS int
+return x || y
+show create function bug2564_4|
+Function	sql_mode	Create Function
+bug2564_4	REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI	CREATE FUNCTION "test"."bug2564_4"(x int, y int) RETURNS int
+return x || y
+drop procedure bug2564_1|
+drop procedure bug2564_2|
+drop function bug2564_3|
+drop function bug2564_4|
 drop table if exists fac|
 create table fac (n int unsigned not null primary key, f bigint unsigned)|
 create procedure ifac(n int unsigned)
@@ -1423,8 +1458,8 @@ end;
 end while;
 end|
 show create procedure opp|
-Procedure	Create Procedure
-opp	CREATE PROCEDURE `test`.`opp`(n bigint unsigned, out pp bool)
+Procedure	sql_mode	Create Procedure
+opp		CREATE PROCEDURE `test`.`opp`(n bigint unsigned, out pp bool)
 begin
 declare r double;
 declare b, s bigint unsigned default 0;
@@ -1520,8 +1555,8 @@ alter procedure bar name bar2 comment "2222222222" sql security definer|
 alter procedure bar2 name bar comment "3333333333"|
 alter procedure bar|
 show create procedure bar|
-Procedure	Create Procedure
-bar	CREATE PROCEDURE `test`.`bar`(x char(16), y int)
+Procedure	sql_mode	Create Procedure
+bar		CREATE PROCEDURE `test`.`bar`(x char(16), y int)
     COMMENT '3333333333'
 insert into test.t1 values (x, y)
 show procedure status like 'bar'|
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index 05847ba220c..81b7b440c4d 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -1519,6 +1519,40 @@ drop table t3|
 
 
 #
+# BUG#2564
+#
+set @@sql_mode = ''|
+create procedure bug2564_1()
+    comment 'Joe''s procedure'
+  insert into `t1` values ("foo", 1)|
+
+set @@sql_mode = 'ANSI_QUOTES'|
+create procedure bug2564_2()
+  insert into "t1" values ('foo', 1)|
+
+delimiter $|
+set @@sql_mode = ''$
+create function bug2564_3(x int, y int) returns int
+  return x || y$
+
+set @@sql_mode = 'ANSI'$
+create function bug2564_4(x int, y int) returns int
+  return x || y$
+delimiter |$
+
+set @@sql_mode = ''|
+show create procedure bug2564_1|
+show create procedure bug2564_2|
+show create function bug2564_3|
+show create function bug2564_4|
+
+drop procedure bug2564_1|
+drop procedure bug2564_2|
+drop function bug2564_3|
+drop function bug2564_4|
+
+
+#
 # Some "real" examples
 #
 
diff --git a/sql/sp.cc b/sql/sp.cc
index 30f99a4af75..b77f60a32ad 100644
--- a/sql/sp.cc
+++ b/sql/sp.cc
@@ -21,8 +21,8 @@
 #include "sp_head.h"
 #include "sp_cache.h"
 
-static char *
-create_string(THD *thd, ulong *lenp,
+static bool
+create_string(THD *thd, String *buf,
 	      int sp_type,
 	      sp_name *name,
 	      const char *params, ulong paramslen,
@@ -227,11 +227,10 @@ db_find_routine(THD *thd, int type, sp_name *name, sp_head **sphp)
   }
 
   {
-    char *defstr;
-    ulong deflen;
+    String defstr;
     LEX *oldlex= thd->lex;
     char olddb[128];
-    char *olddbptr;
+    bool dbchanged;
     enum enum_sql_command oldcmd= thd->lex->sql_command;
     ulong old_sql_mode= thd->variables.sql_mode;
     ha_rows select_limit= thd->variables.select_limit;
@@ -239,20 +238,22 @@ db_find_routine(THD *thd, int type, sp_name *name, sp_head **sphp)
     thd->variables.sql_mode= sql_mode;
     thd->variables.select_limit= HA_POS_ERROR;
 
-    if (!(defstr= create_string(thd, &deflen,
-				type,
-				name,
-				params, strlen(params),
-				returns, strlen(returns),
-				body, strlen(body),
-				&chistics)))
+    defstr.set_charset(system_charset_info);
+    if (!create_string(thd, &defstr,
+		       type,
+		       name,
+		       params, strlen(params),
+		       returns, strlen(returns),
+		       body, strlen(body),
+		       &chistics))
     {
       ret= SP_INTERNAL_ERROR;
       goto done;
     }
 
-    olddbptr= thd->db;
-    if ((ret= sp_use_new_db(thd, name->m_db.str, olddb, sizeof(olddb), 1)))
+    dbchanged= FALSE;
+    if ((ret= sp_use_new_db(thd, name->m_db.str, olddb, sizeof(olddb),
+			    1, &dbchanged)))
       goto done;
 
     {
@@ -265,7 +266,7 @@ db_find_routine(THD *thd, int type, sp_name *name, sp_head **sphp)
       List<Item> vals= thd->lex->value_list;
 
       mysql_init_query(thd, TRUE);
-      lex_start(thd, (uchar*)defstr, deflen);
+      lex_start(thd, (uchar*)defstr.c_ptr(), defstr.length());
       thd->lex->value_list= vals;
     }
 
@@ -274,8 +275,7 @@ db_find_routine(THD *thd, int type, sp_name *name, sp_head **sphp)
       LEX *newlex= thd->lex;
       sp_head *sp= newlex->sphead;
 
-      if (olddbptr != thd->db &&
-	  (ret= sp_change_db(thd, olddb, 1)))
+      if (dbchanged && (ret= sp_change_db(thd, olddb, 1)))
 	goto done;
       if (sp)
       {
@@ -288,12 +288,11 @@ db_find_routine(THD *thd, int type, sp_name *name, sp_head **sphp)
     }
     else
     {
-      if (olddbptr != thd->db &&
-	  (ret= sp_change_db(thd, olddb, 1)))
+      if (dbchanged && (ret= sp_change_db(thd, olddb, 1)))
 	goto done;
       *sphp= thd->lex->sphead;
       (*sphp)->set_info((char *)definer, (uint)strlen(definer),
-			created, modified, &chistics);
+			created, modified, &chistics, sql_mode);
     }
     thd->lex->sql_command= oldcmd;
     thd->variables.sql_mode= old_sql_mode;
@@ -955,9 +954,12 @@ sp_cache_functions(THD *thd, LEX *lex)
   return ret;
 }
 
-
-static char *
-create_string(THD *thd, ulong *lenp,
+/*
+ * Generates the CREATE... string from the table information.
+ * Returns TRUE on success, FALSE on (alloc) failure.
+ */
+static bool
+create_string(THD *thd, String *buf,
 	      int type,
 	      sp_name *name,
 	      const char *params, ulong paramslen,
@@ -965,35 +967,40 @@ create_string(THD *thd, ulong *lenp,
 	      const char *body, ulong bodylen,
 	      st_sp_chistics *chistics)
 {
-  char *buf, *ptr;
-  ulong buflen;
-
-  buflen= 100 + name->m_qname.length + paramslen + returnslen + bodylen +
-    chistics->comment.length;
-  if (!(buf= thd->alloc(buflen)))
-    return 0;
-
-  ptr= strxmov(buf, "CREATE ",
-	       (type == TYPE_ENUM_FUNCTION) ? "FUNCTION" : "PROCEDURE",
-	       " `", name->m_db.str, "`.`", name->m_name.str, "`(", params, ")",
-	       NullS);
+  /* Make some room to begin with */
+  if (buf->alloc(100 + name->m_qname.length + paramslen + returnslen + bodylen +
+		 chistics->comment.length))
+    return FALSE;
 
+  buf->append("CREATE ", 7);
   if (type == TYPE_ENUM_FUNCTION)
-    ptr= strxmov(ptr, " RETURNS ", returns, NullS);
-  *ptr++= '\n';
-
+    buf->append("FUNCTION ", 9);
+  else
+    buf->append("PROCEDURE ", 10);
+  append_identifier(thd, buf, name->m_db.str, name->m_db.length);
+  buf->append('.');
+  append_identifier(thd, buf, name->m_name.str, name->m_name.length);
+  buf->append('(');
+  buf->append(params, paramslen);
+  buf->append(')');
+  if (type == TYPE_ENUM_FUNCTION)
+  {
+    buf->append(" RETURNS ", 9);
+    buf->append(returns, returnslen);
+  }
+  buf->append('\n');
   if (chistics->detistic)
-    ptr= strmov(ptr, "    DETERMINISTIC\n");
+    buf->append( "    DETERMINISTIC\n", 18);
   if (chistics->suid == IS_NOT_SUID)
-    ptr= strmov(ptr, "    SQL SECURITY INVOKER\n");
+    buf->append("    SQL SECURITY INVOKER\n", 25);
   if (chistics->comment.length)
   {
-    ptr= strmov(strnmov(strmov(ptr, "    COMMENT '"),chistics->comment.str,
-			chistics->comment.length),"'\n");
+    buf->append("    COMMENT ");
+    append_unescaped(buf, chistics->comment.str, chistics->comment.length);
+    buf->append('\n');
   }
-  ptr= strmov(ptr, body);
-  *lenp= (ptr-buf);
-  return buf;
+  buf->append(body, bodylen);
+  return TRUE;
 }
 
 
@@ -1003,7 +1010,7 @@ create_string(THD *thd, ulong *lenp,
 
 int
 sp_use_new_db(THD *thd, char *newdb, char *olddb, uint olddblen,
-	      bool no_access_check)
+	      bool no_access_check, bool *dbchangedp)
 {
   bool changeit;
   DBUG_ENTER("sp_use_new_db");
@@ -1029,12 +1036,15 @@ sp_use_new_db(THD *thd, char *newdb, char *olddb, uint olddblen,
   }
   if (!changeit)
   {
+    *dbchangedp= FALSE;
     DBUG_RETURN(0);
   }
   else
   {
     int ret= sp_change_db(thd, newdb, no_access_check);
 
+    if (! ret)
+      *dbchangedp= TRUE;
     DBUG_RETURN(ret);
   }
 }
diff --git a/sql/sp.h b/sql/sp.h
index a4fec50aca2..a6f76876f33 100644
--- a/sql/sp.h
+++ b/sql/sp.h
@@ -93,9 +93,10 @@ sp_cache_functions(THD *thd, LEX *lex);
 
 // Do a "use newdb". The current db is stored at olddb.
 // If newdb is the same as the current one, nothing is changed.
+// dbchangedp is set to true if the db was actually changed.
 int
 sp_use_new_db(THD *thd, char *newdb, char *olddb, uint olddbmax,
-	      bool no_access_check);
+	      bool no_access_check, bool *dbchangedp);
 
 // Like mysql_change_db() but handles empty db name and the  send_ok() problem.
 int
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index c22e57830f8..41955f12b5d 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -376,7 +376,7 @@ sp_head::execute(THD *thd)
 {
   DBUG_ENTER("sp_head::execute");
   char olddb[128];
-  char *olddbptr;
+  bool dbchanged;
   sp_rcontext *ctx= thd->spcont;
   int ret= 0;
   uint ip= 0;
@@ -388,8 +388,8 @@ sp_head::execute(THD *thd)
   }
 #endif
 
-  olddbptr= thd->db;
-  if ((ret= sp_use_new_db(thd, m_db.str, olddb, sizeof(olddb), 0)))
+  dbchanged= FALSE;
+  if ((ret= sp_use_new_db(thd, m_db.str, olddb, sizeof(olddb), 0, &dbchanged)))
     goto done;
 
   if (ctx)
@@ -445,7 +445,7 @@ sp_head::execute(THD *thd)
     ret= -1;
   /* If the DB has changed, the pointer has changed too, but the
      original thd->db will then have been freed */
-  if (olddbptr != thd->db)
+  if (dbchanged)
   {
     if (! thd->killed)
       ret= sp_change_db(thd, olddb, 0);
@@ -780,7 +780,7 @@ sp_head::backpatch(sp_label_t *lab)
 void
 sp_head::set_info(char *definer, uint definerlen,
 		  longlong created, longlong modified,
-		  st_sp_chistics *chistics)
+		  st_sp_chistics *chistics, ulong sql_mode)
 {
   char *p= strchr(definer, '@');
   uint len;
@@ -803,6 +803,7 @@ sp_head::set_info(char *definer, uint definerlen,
     m_chistics->comment.str= strmake_root(&mem_root,
 					  m_chistics->comment.str,
 					  m_chistics->comment.length);
+  m_sql_mode= sql_mode;
 }
 
 void
@@ -846,21 +847,44 @@ sp_head::show_create_procedure(THD *thd)
   String buffer(buff, sizeof(buff), system_charset_info);
   int res;
   List<Item> field_list;
+  ulong old_sql_mode;
+  sys_var *sql_mode_var;
+  byte *sql_mode_str;
+  ulong sql_mode_len;
 
   DBUG_ENTER("sp_head::show_create_procedure");
   DBUG_PRINT("info", ("procedure %s", m_name.str));
 
-  field_list.push_back(new Item_empty_string("Procedure",NAME_LEN));
+  old_sql_mode= thd->variables.sql_mode;
+  thd->variables.sql_mode= m_sql_mode;
+  sql_mode_var= find_sys_var("SQL_MODE", 8);
+  if (sql_mode_var)
+  {
+    sql_mode_str= sql_mode_var->value_ptr(thd, OPT_SESSION, 0);
+    sql_mode_len= strlen(sql_mode_str);
+  }
+
+  field_list.push_back(new Item_empty_string("Procedure", NAME_LEN));
+  if (sql_mode_var)
+    field_list.push_back(new Item_empty_string("sql_mode", sql_mode_len));
   // 1024 is for not to confuse old clients
   field_list.push_back(new Item_empty_string("Create Procedure",
-					     max(buffer.length(),1024)));
+					     max(buffer.length(), 1024)));
   if (protocol->send_fields(&field_list, 1))
-    DBUG_RETURN(1);
+  {
+    res= 1;
+    goto done;
+  }
   protocol->prepare_for_resend();
   protocol->store(m_name.str, m_name.length, system_charset_info);
+  if (sql_mode_var)
+    protocol->store(sql_mode_str, sql_mode_len, system_charset_info);
   protocol->store(m_defstr.str, m_defstr.length, system_charset_info);
   res= protocol->write();
   send_eof(thd);
+
+ done:
+  thd->variables.sql_mode= old_sql_mode;
   DBUG_RETURN(res);
 }
 
@@ -889,20 +913,43 @@ sp_head::show_create_function(THD *thd)
   String buffer(buff, sizeof(buff), system_charset_info);
   int res;
   List<Item> field_list;
+  ulong old_sql_mode;
+  sys_var *sql_mode_var;
+  byte *sql_mode_str;
+  ulong sql_mode_len;
 
   DBUG_ENTER("sp_head::show_create_function");
   DBUG_PRINT("info", ("procedure %s", m_name.str));
 
+  old_sql_mode= thd->variables.sql_mode;
+  thd->variables.sql_mode= m_sql_mode;
+  sql_mode_var= find_sys_var("SQL_MODE", 8);
+  if (sql_mode_var)
+  {
+    sql_mode_str= sql_mode_var->value_ptr(thd, OPT_SESSION, 0);
+    sql_mode_len= strlen(sql_mode_str);
+  }
+
   field_list.push_back(new Item_empty_string("Function",NAME_LEN));
+  if (sql_mode_var)
+    field_list.push_back(new Item_empty_string("sql_mode", sql_mode_len));
   field_list.push_back(new Item_empty_string("Create Function",
 					     max(buffer.length(),1024)));
   if (protocol->send_fields(&field_list, 1))
-    DBUG_RETURN(1);
+  {
+    res= 1;
+    goto done;
+  }
   protocol->prepare_for_resend();
   protocol->store(m_name.str, m_name.length, system_charset_info);
+  if (sql_mode_var)
+    protocol->store(sql_mode_str, sql_mode_len, system_charset_info);
   protocol->store(m_defstr.str, m_defstr.length, system_charset_info);
   res= protocol->write();
   send_eof(thd);
+
+ done:
+  thd->variables.sql_mode= old_sql_mode;
   DBUG_RETURN(res);
 }
 // ------------------------------------------------------------------
diff --git a/sql/sp_head.h b/sql/sp_head.h
index e9ec7068783..fd6ecfd7320 100644
--- a/sql/sp_head.h
+++ b/sql/sp_head.h
@@ -85,6 +85,7 @@ public:
   my_bool m_multi_results;	// TRUE if a procedure with SELECT(s)
   uint m_old_cmq;		// Old CLIENT_MULTI_QUERIES value
   st_sp_chistics *m_chistics;
+  ulong m_sql_mode;		// For SHOW CREATE
 #if NOT_USED_NOW
   // QQ We're not using this at the moment.
   List<char *> m_calls;		// Called procedures.
@@ -194,7 +195,7 @@ public:
 
   void set_info(char *definer, uint definerlen,
 		longlong created, longlong modified,
-		st_sp_chistics *chistics);
+		st_sp_chistics *chistics, ulong sql_mode);
 
   void reset_thd_mem_root(THD *thd);