WL#1363: Update the mysql.proc table and add new fields.

Also made the parsing and handling of SP characteristics
more general and extendable, and added a few ch:istics.


Docs/sp-imp-spec.txt:
  Updated spec with new schema.
Docs/sp-implemented.txt:
  Added info about ALTER and SHOW.
mysql-test/r/sp.result:
  Minor change in SHOW FUNCTION|PROCEDURE STATUS output.
scripts/mysql_create_system_tables.sh:
  New mysql.proc schema.
scripts/mysql_fix_privilege_tables.sql:
  New mysql.proc schema.
sql/lex.h:
  New lex words for SP characteristics.
sql/sp.cc:
  New mysql.proc schema.
  Also made the characteristics handling slightly more extendable.
sql/sp.h:
  Made the characteristics handling slightly more extendable.
sql/sp_head.cc:
  Made the characteristics handling slightly more extendable.
sql/sp_head.h:
  Made the characteristics handling slightly more extendable.
sql/sql_lex.h:
  Made the characteristics handling slightly more extendable.
sql/sql_parse.cc:
  Made the characteristics handling slightly more extendable.
sql/sql_yacc.yy:
  Made the characteristics handling slightly more extendable
  and made the parsing of characteristics more general, and
  added a few new dito. (LANGUAGE SQL, and [NOT] DETERMINISTIC
  for starters).

13 files changed, 310 insertions, 175 deletions

diff --git a/Docs/sp-imp-spec.txt b/Docs/sp-imp-spec.txt
index 6fee125fbea..b93416df2f7 100644
--- a/Docs/sp-imp-spec.txt
+++ b/Docs/sp-imp-spec.txt
@@ -1052,4 +1052,46 @@
       /* Remove an SP from cache */
       void sp_cache_remove(sp_cache **cp, sp_head *sp);
 
+
+  - The mysql.proc schema:
+
+    CREATE TABLE proc (
+      schema            char(64) binary DEFAULT '' NOT NULL,
+      name              char(64) binary DEFAULT '' NOT NULL,
+      type              enum('FUNCTION','PROCEDURE') NOT NULL,
+      specific_name     char(64) binary DEFAULT '' NOT NULL,
+      language          enum('SQL') DEFAULT 'SQL' NOT NULL,
+      sql_data_access   enum('CONTAINS_SQL') DEFAULT 'CONTAINS_SQL' NOT NULL,
+      is_deterministic  enum('YES','NO') DEFAULT 'NO' NOT NULL,
+      definition        blob DEFAULT '' NOT NULL,
+      security_type     enum('INVOKER','DEFINER') DEFAULT 'INVOKER' NOT NULL,
+      definer           char(77) binary DEFAULT '' NOT NULL,
+      created           timestamp,
+      modified          timestamp,
+      sql_mode          set(
+                            'REAL_AS_FLOAT',
+                            'PIPES_AS_CONCAT',
+                            'ANSI_QUOTES',
+                            'IGNORE_SPACE',
+                            'NOT_USED',
+                            'ONLY_FULL_GROUP_BY',
+                            'NO_UNSIGNED_SUBTRACTION',
+                            'NO_DIR_IN_CREATE',
+                            'POSTGRESQL',
+                            'ORACLE',
+                            'MSSQL',
+                            'DB2',
+                            'MAXDB',
+                            'NO_KEY_OPTIONS',
+                            'NO_TABLE_OPTIONS',
+                            'NO_FIELD_OPTIONS',
+                            'MYSQL323',
+                            'MYSQL40',
+                            'ANSI',
+                            'NO_AUTO_VALUE_ON_ZERO'
+                        ) DEFAULT 0 NOT NULL,
+      comment           char(64) binary DEFAULT '' NOT NULL,
+      PRIMARY KEY (schema,name,type)
+    ) comment='Stored Procedures';
+    
 --
diff --git a/Docs/sp-implemented.txt b/Docs/sp-implemented.txt
index c9112d75e43..e64b2476c31 100644
--- a/Docs/sp-implemented.txt
+++ b/Docs/sp-implemented.txt
@@ -1,9 +1,10 @@
-Stored Procedures implemented 2003-09-16:
+Stored Procedures implemented 2003-12-10:
 
 
 Summary of Not Yet Implemented:
 
-  - SQL queries (like SELECT, INSERT, UPDATE etc) in FUNCTION bodies
+  - SQL statements using table (like SELECT, INSERT, UPDATE etc)
+    in FUNCTIONs
   - External languages
   - Access control
   - Routine characteristics (mostly used for external languages)
@@ -25,18 +26,28 @@ Summary of what's implemented:
   - Prepared SP caching
   - CONDITIONs and HANDLERs
   - Simple read-only CURSORs.
+  - SHOW DECLARE PROCEDURE/FUNCTION and SHOW PROCEDURE/FUNCTION STATUS
 
-List of what's implemented:
 
-  - CREATE PROCEDURE|FUNCTION name ( args ) body
-    No routine characteristics yet.
+List of what's implemented:
 
-  - ALTER PROCEDURE|FUNCTION name ...
-    Is parsed, but a no-op (as there are no characteristics implemented yet).
-    CASCADE/RESTRICT is not implemented (and CASCADE probably will not be).
+  - CREATE PROCEDURE|FUNCTION name ( args ) characteristics body
+    where characteristics is:
+      LANGUAGE SQL |
+      [NOT] DETERMINISTIC |
+      SQL SECURITY [DEFINER|INVOKER] |
+      COMMENT string
+    However the DETERMINISTIC setting is not currently used.
+
+  - ALTER PROCEDURE|FUNCTION name characteristics
+    CASCADE/RESTRICT is not implemented.
+    characteristics is:
+      COMMENT string |
+      SQL SECURITY [DEFINER|INVOKER] |
+      NAME newname
 
   - DROP PROCEDURE|FUNCTION [IF EXISTS] name
-    CASCADE/RESTRICT is not implemented (and CASCADE probably will not be).
+    CASCADE/RESTRICT is not implemented.
 
   - CALL name (args)
     OUT and INOUT parameters are only supported for local variables, and
@@ -92,23 +103,9 @@ List of what's implemented:
       (The additional syntax will be added for completeness, but for the
        most part unsupported with the current underlying cursor mechanism.)
 
-Closed questions:
-
-  - What is the expected result when creating a procedure with a name that
-    already exists? An error or overwrite?
-  Answer: Error
-
-  - Do PROCEDUREs and FUNCTIONs share namespace or not? I think not, but the
-    we need to flag the type in the mysql.proc table and the name alone is
-    not a unique key any more, or, we have separate tables.
-    (Unfortunately, mysql.func is already taken. Use "sfunc" and maybe even
-     rename "proc" into "sproc" while we still can, for consistency?)
-  Answer: Same tables, with an additional key-field for the type.
-
-
-Open questions/issues:
-
-  - SQL-99 variables and parameters are typed. For the present we don't do
-    any type checking, since this is the way MySQL works. I still don't know
-    if we should keep it this way, or implement type checking. Possibly we
-    should have optional, uset-settable, type checking.
+  - SHOW procedures and functions
+    SHOW DECLARE PROCEDURE|FUNCTION <name>
+      returns the definition of a routine.
+    SHOW PROCEDURE|FUNCTION STATUS [LIKE <pattern>]
+      returns characteristics of routines, like the name, type, creator,
+      creation and modification dates, etc.
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index b8581674e5a..722d8f3773b 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -852,12 +852,12 @@ n	f
 20	2432902008176640000
 drop table fac;
 show function status like '%f%';
-Name	Type	Creator	Modified	Created	Suid	Comment
-fac	function	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	Y	
+Name	Type	Definer	Modified	Created	Security_type	Comment
+fac	FUNCTION	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	INVOKER	
 drop procedure ifac;
 drop function fac;
 show function status like '%f%';
-Name	Type	Creator	Modified	Created	Suid	Comment
+Name	Type	Definer	Modified	Created	Security_type	Comment
 drop table if exists primes;
 create table primes (
 i int unsigned not null primary key,
@@ -945,9 +945,9 @@ end if;
 end loop;
 end
 show procedure status like '%p%';
-Name	Type	Creator	Modified	Created	Suid	Comment
-ip	procedure	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	Y	
-opp	procedure	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	Y	
+Name	Type	Definer	Modified	Created	Security_type	Comment
+ip	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	INVOKER	
+opp	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	INVOKER	
 call ip(200);
 select * from primes where i=45 or i=100 or i=199;
 i	p
@@ -958,7 +958,7 @@ drop table primes;
 drop procedure opp;
 drop procedure ip;
 show procedure status like '%p%';
-Name	Type	Creator	Modified	Created	Suid	Comment
+Name	Type	Definer	Modified	Created	Security_type	Comment
 drop table if exists fib;
 create table fib ( f bigint unsigned not null );
 insert into fib values (1), (1);
@@ -1008,8 +1008,8 @@ create procedure bar(x char(16), y int)
 comment "111111111111" sql security invoker
 insert into test.t1 values (x, y);
 show procedure status like 'bar';
-Name	Type	Creator	Modified	Created	Suid	Comment
-bar	procedure	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	N	111111111111
+Name	Type	Definer	Modified	Created	Security_type	Comment
+bar	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	INVOKER	111111111111
 alter procedure bar name bar2 comment "2222222222" sql security definer;
 alter procedure bar2 name bar comment "3333333333";
 alter procedure bar;
@@ -1019,8 +1019,8 @@ bar	create procedure bar(x char(16), y int)
 comment "111111111111" sql security invoker
 insert into test.t1 values (x, y)
 show procedure status like 'bar';
-Name	Type	Creator	Modified	Created	Suid	Comment
-bar	procedure	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	Y	3333333333
+Name	Type	Definer	Modified	Created	Security_type	Comment
+bar	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	3333333333
 drop procedure bar;
 drop table t1;
 drop table t2;
diff --git a/scripts/mysql_create_system_tables.sh b/scripts/mysql_create_system_tables.sh
index c702a97f93d..7ae6773c1cf 100644
--- a/scripts/mysql_create_system_tables.sh
+++ b/scripts/mysql_create_system_tables.sh
@@ -289,19 +289,45 @@ fi
 if test ! -f $mdata/proc.frm
 then
   c_p="$c_p CREATE TABLE proc ("
-  c_p="$c_p   name char(64) binary DEFAULT '' NOT NULL,"
-  c_p="$c_p   type enum('function','procedure') NOT NULL,"
-  c_p="$c_p   body blob DEFAULT '' NOT NULL,"
-  c_p="$c_p   creator char(77) binary DEFAULT '' NOT NULL,"
-  c_p="$c_p   modified timestamp,"
-  c_p="$c_p   created timestamp,"
-  c_p="$c_p   suid enum ('N', 'Y') DEFAULT 'Y' NOT NULL,"
-  c_p="$c_p   comment char(64) binary DEFAULT '' NOT NULL,"
-  c_p="$c_p   PRIMARY KEY (name,type)"
-  c_p="$c_p )"
-  c_p="$c_p   comment='Stored Procedures';"
+  c_p="$c_p   schema            char(64) binary DEFAULT '' NOT NULL,"
+  c_p="$c_p   name              char(64) binary DEFAULT '' NOT NULL,"
+  c_p="$c_p   type              enum('FUNCTION','PROCEDURE') NOT NULL,"
+  c_p="$c_p   specific_name     char(64) binary DEFAULT '' NOT NULL,"
+  c_p="$c_p   language          enum('SQL') DEFAULT 'SQL' NOT NULL,"
+  c_p="$c_p   sql_data_access   enum('CONTAINS_SQL') DEFAULT 'CONTAINS_SQL' NOT NULL,"
+  c_p="$c_p   is_deterministic  enum('YES','NO') DEFAULT 'NO' NOT NULL,"
+  c_p="$c_p   definition        blob DEFAULT '' NOT NULL,"
+  c_p="$c_p   security_type     enum('INVOKER','DEFINER') DEFAULT 'INVOKER' NOT NULL,"
+  c_p="$c_p   definer           char(77) binary DEFAULT '' NOT NULL,"
+  c_p="$c_p   created           timestamp,"
+  c_p="$c_p   modified          timestamp,"
+  c_p="$c_p   sql_mode          set("
+  c_p="$c_p                         'REAL_AS_FLOAT',"
+  c_p="$c_p                         'PIPES_AS_CONCAT',"
+  c_p="$c_p                         'ANSI_QUOTES',"
+  c_p="$c_p                         'IGNORE_SPACE',"
+  c_p="$c_p                         'NOT_USED',"
+  c_p="$c_p                         'ONLY_FULL_GROUP_BY',"
+  c_p="$c_p                         'NO_UNSIGNED_SUBTRACTION',"
+  c_p="$c_p                         'NO_DIR_IN_CREATE',"
+  c_p="$c_p                         'POSTGRESQL',"
+  c_p="$c_p                         'ORACLE',"
+  c_p="$c_p                         'MSSQL',"
+  c_p="$c_p                         'DB2',"
+  c_p="$c_p                         'MAXDB',"
+  c_p="$c_p                         'NO_KEY_OPTIONS',"
+  c_p="$c_p                         'NO_TABLE_OPTIONS',"
+  c_p="$c_p                         'NO_FIELD_OPTIONS',"
+  c_p="$c_p                         'MYSQL323',"
+  c_p="$c_p                         'MYSQL40',"
+  c_p="$c_p                         'ANSI',"
+  c_p="$c_p                         'NO_AUTO_VALUE_ON_ZERO'"
+  c_p="$c_p                     ) DEFAULT 0 NOT NULL,"
+  c_p="$c_p   comment           char(64) binary DEFAULT '' NOT NULL,"
+  c_p="$c_p   PRIMARY KEY (schema,name,type)"
+  c_p="$c_p ) comment='Stored Procedures';"
 fi
-	      
+
 cat << END_OF_DATA
 use mysql;
 $c_d
diff --git a/scripts/mysql_fix_privilege_tables.sql b/scripts/mysql_fix_privilege_tables.sql
index 4b571722bac..e342e08ea9d 100644
--- a/scripts/mysql_fix_privilege_tables.sql
+++ b/scripts/mysql_fix_privilege_tables.sql
@@ -140,14 +140,40 @@ unique index (name)
 #
 
 CREATE TABLE IF NOT EXISTS proc (
-  name char(64) binary DEFAULT '' NOT NULL,
-  type enum('function','procedure') NOT NULL,
-  body blob DEFAULT '' NOT NULL,
-  creator char(77) binary DEFAULT '' NOT NULL,
-  modified timestamp,
-  created timestamp,
-  suid enum ('N', 'Y') DEFAULT 'Y' NOT NULL,
-  comment char(64) binary DEFAULT '' NOT NULL,
-  PRIMARY KEY (name,type)
+  schema            char(64) binary DEFAULT '' NOT NULL,
+  name              char(64) binary DEFAULT '' NOT NULL,
+  type              enum('FUNCTION','PROCEDURE') NOT NULL,
+  specific_name     char(64) binary DEFAULT '' NOT NULL,
+  language          enum('SQL') DEFAULT 'SQL' NOT NULL,
+  sql_data_access   enum('CONTAINS_SQL') DEFAULT 'CONTAINS_SQL' NOT NULL,
+  is_deterministic  enum('YES','NO') DEFAULT 'NO' NOT NULL,
+  definition        blob DEFAULT '' NOT NULL,
+  security_type     enum('INVOKER','DEFINER') DEFAULT 'INVOKER' NOT NULL,
+  definer           char(77) binary DEFAULT '' NOT NULL,
+  created           timestamp,
+  modified          timestamp,
+  sql_mode          set(
+                        'REAL_AS_FLOAT',
+                        'PIPES_AS_CONCAT',
+                        'ANSI_QUOTES',
+                        'IGNORE_SPACE',
+                        'NOT_USED',
+                        'ONLY_FULL_GROUP_BY',
+                        'NO_UNSIGNED_SUBTRACTION',
+                        'NO_DIR_IN_CREATE',
+                        'POSTGRESQL',
+                        'ORACLE',
+                        'MSSQL',
+                        'DB2',
+                        'MAXDB',
+                        'NO_KEY_OPTIONS',
+                        'NO_TABLE_OPTIONS',
+                        'NO_FIELD_OPTIONS',
+                        'MYSQL323',
+                        'MYSQL40',
+                        'ANSI',
+                        'NO_AUTO_VALUE_ON_ZERO'
+                    ) DEFAULT 0 NOT NULL,
+  comment           char(64) binary DEFAULT '' NOT NULL,
+  PRIMARY KEY (schema,name,type)
 ) comment='Stored Procedures';
-
diff --git a/sql/lex.h b/sql/lex.h
index 9334b7e22cf..9dba8268e59 100644
--- a/sql/lex.h
+++ b/sql/lex.h
@@ -136,6 +136,7 @@ static SYMBOL symbols[] = {
   { "DELETE",		SYM(DELETE_SYM),0,0},
   { "DESC",		SYM(DESC),0,0},
   { "DESCRIBE",		SYM(DESCRIBE),0,0},
+  { "DETERMINISTIC",    SYM(DETERMINISTIC_SYM),0,0},
   { "DIRECTORY",	SYM(DIRECTORY_SYM),0,0},
   { "DISABLE",		SYM(DISABLE_SYM),0,0},
   { "DISCARD",		SYM(DISCARD),0,0},
@@ -240,6 +241,7 @@ static SYMBOL symbols[] = {
   { "KEY",		SYM(KEY_SYM),0,0},
   { "KEYS",		SYM(KEYS),0,0},
   { "KILL",		SYM(KILL_SYM),0,0},
+  { "LANGUAGE",         SYM(LANGUAGE_SYM),0,0},
   { "LAST",		SYM(LAST_SYM),0,0},
   { "LEADING",		SYM(LEADING),0,0},
   { "LEAVE",		SYM(LEAVE_SYM),0,0},
diff --git a/sql/sp.cc b/sql/sp.cc
index b725614dfb8..d33114d50fd 100644
--- a/sql/sp.cc
+++ b/sql/sp.cc
@@ -26,15 +26,21 @@
  *
  */
 
-#define MYSQL_PROC_FIELD_NAME       0
-#define MYSQL_PROC_FIELD_TYPE       1
-#define MYSQL_PROC_FIELD_DEFINITION 2
-#define MYSQL_PROC_FIELD_CREATOR    3
-#define MYSQL_PROC_FIELD_MODIFIED   4
-#define MYSQL_PROC_FIELD_CREATED    5
-#define MYSQL_PROC_FIELD_SUID       6
-#define MYSQL_PROC_FIELD_COMMENT    7
-#define MYSQL_PROC_FIELD_COUNT      8
+#define MYSQL_PROC_FIELD_SCHEMA         0
+#define MYSQL_PROC_FIELD_NAME           1
+#define MYSQL_PROC_FIELD_TYPE           2
+#define MYSQL_PROC_FIELD_SPECIFIC_NAME  3
+#define MYSQL_PROC_FIELD_LANGUAGE       4
+#define MYSQL_PROC_FIELD_ACCESS         5
+#define MYSQL_PROC_FIELD_DETERMINISTIC  6
+#define MYSQL_PROC_FIELD_DEFINITION     7
+#define MYSQL_PROC_FIELD_SECURITY_TYPE  8
+#define MYSQL_PROC_FIELD_DEFINER        9
+#define MYSQL_PROC_FIELD_CREATED       10
+#define MYSQL_PROC_FIELD_MODIFIED      11
+#define MYSQL_PROC_FIELD_SQL_MODE      12
+#define MYSQL_PROC_FIELD_COMMENT       13
+#define MYSQL_PROC_FIELD_COUNT         14
 
 /* *opened=true means we opened ourselves */
 static int
@@ -44,17 +50,18 @@ db_find_routine_aux(THD *thd, int type, char *name, uint namelen,
   DBUG_ENTER("db_find_routine_aux");
   DBUG_PRINT("enter", ("type: %d name: %*s", type, namelen, name));
   TABLE *table;
-  byte key[65];			// We know name is 64 and the enum is 1 byte
+  byte key[64+64+1];		// schema, name, type
   uint keylen;
   int ret;
 
   // Put the key together
+  memset(key, (int)' ', 64);	// QQ Empty schema for now
   keylen= namelen;
-  if (keylen > sizeof(key)-1)
-    keylen= sizeof(key)-1;
-  memcpy(key, name, keylen);
-  memset(key+keylen, (int)' ', sizeof(key)-1 - keylen);	// Pad with space
-  key[sizeof(key)-1]= type;
+  if (keylen > 64)
+    keylen= 64;
+  memcpy(key+64, name, keylen);
+  memset(key+64+keylen, (int)' ', 64-keylen); // Pad with space
+  key[128]= type;
   keylen= sizeof(key);
 
   for (table= thd->open_tables ; table ; table= table->next)
@@ -128,7 +135,7 @@ db_find_routine(THD *thd, int type, char *name, uint namelen, sp_head **sphp)
 
   // Get additional information
   if ((creator= get_field(&thd->mem_root,
-			  table->field[MYSQL_PROC_FIELD_CREATOR])) == NULL)
+			  table->field[MYSQL_PROC_FIELD_DEFINER])) == NULL)
   {
     ret= SP_GET_FIELD_FAILED;
     goto done;
@@ -138,7 +145,7 @@ db_find_routine(THD *thd, int type, char *name, uint namelen, sp_head **sphp)
   created= table->field[MYSQL_PROC_FIELD_CREATED]->val_int();
 
   if ((ptr= get_field(&thd->mem_root,
-		      table->field[MYSQL_PROC_FIELD_SUID])) == NULL)
+		      table->field[MYSQL_PROC_FIELD_SECURITY_TYPE])) == NULL)
   {
     ret= SP_GET_FIELD_FAILED;
     goto done;
@@ -180,9 +187,9 @@ db_find_routine(THD *thd, int type, char *name, uint namelen, sp_head **sphp)
     else
     {
       *sphp= thd->lex->sphead;
-      (*sphp)->sp_set_info((char *) creator, (uint) strlen(creator),
-			   created, modified, suid,
-			   ptr, length);
+      (*sphp)->set_info((char *) creator, (uint) strlen(creator),
+			created, modified, suid,
+			ptr, length);
     }
     thd->lex->sql_command= oldcmd;
   }
@@ -196,7 +203,7 @@ db_find_routine(THD *thd, int type, char *name, uint namelen, sp_head **sphp)
 static int
 db_create_routine(THD *thd, int type,
 		  char *name, uint namelen, char *def, uint deflen,
-		  char *comment, uint commentlen, bool suid)
+		  st_sp_chistics *chistics)
 {
   DBUG_ENTER("db_create_routine");
   DBUG_PRINT("enter", ("type: %d name: %*s def: %*s", type, namelen, name, deflen, def));
@@ -224,17 +231,24 @@ db_create_routine(THD *thd, int type,
     table->field[MYSQL_PROC_FIELD_NAME]->store(name, namelen,
 					       system_charset_info);
     table->field[MYSQL_PROC_FIELD_TYPE]->store((longlong)type);
+    table->field[MYSQL_PROC_FIELD_SPECIFIC_NAME]->store(name, namelen,
+							system_charset_info);
     table->field[MYSQL_PROC_FIELD_DEFINITION]->store(def, deflen,
 						     system_charset_info);
-    table->field[MYSQL_PROC_FIELD_CREATOR]->store(creator,
+    table->field[MYSQL_PROC_FIELD_DEFINER]->store(creator,
 						  (uint)strlen(creator),
 						  system_charset_info);
     ((Field_timestamp *)table->field[MYSQL_PROC_FIELD_CREATED])->set_time();
-    if (!suid)
-      table->field[MYSQL_PROC_FIELD_SUID]->store((longlong) 1);
-    if (comment)
-      table->field[MYSQL_PROC_FIELD_COMMENT]->store(comment, commentlen,
-						    system_charset_info);
+    if (chistics->suid != IS_DEFAULT_SUID)
+      table->field[MYSQL_PROC_FIELD_SECURITY_TYPE]->store(
+                                                     (longlong)chistics->suid);
+    table->field[MYSQL_PROC_FIELD_SQL_MODE]->store(
+					   (longlong)thd->variables.sql_mode);
+    if (chistics->comment.str)
+      table->field[MYSQL_PROC_FIELD_COMMENT]->store(
+		                               chistics->comment.str,
+					       chistics->comment.length,
+					       system_charset_info);
 
     if (table->file->write_row(table->record[0]))
       ret= SP_WRITE_ROW_FAILED;
@@ -271,7 +285,7 @@ db_drop_routine(THD *thd, int type, char *name, uint namelen)
 static int
 db_update_routine(THD *thd, int type, char *name, uint namelen,
 		  char *newname, uint newnamelen,
-		  char *comment, uint commentlen, enum suid_behaviour suid)
+		  st_sp_chistics *chistics)
 {
   DBUG_ENTER("db_update_routine");
   DBUG_PRINT("enter", ("type: %d name: %*s", type, namelen, name));
@@ -284,15 +298,15 @@ db_update_routine(THD *thd, int type, char *name, uint namelen,
   {
     store_record(table,record[1]);
     ((Field_timestamp *)table->field[MYSQL_PROC_FIELD_MODIFIED])->set_time();
-    if (suid)
-      table->field[MYSQL_PROC_FIELD_SUID]->store((longlong) suid);
+    if (chistics->suid)
+      table->field[MYSQL_PROC_FIELD_SECURITY_TYPE]->store((longlong)chistics->suid);
     if (newname)
       table->field[MYSQL_PROC_FIELD_NAME]->store(newname,
 						 newnamelen,
 						 system_charset_info);
-    if (comment)
-      table->field[MYSQL_PROC_FIELD_COMMENT]->store(comment,
-						    commentlen,
+    if (chistics->comment.str)
+      table->field[MYSQL_PROC_FIELD_COMMENT]->store(chistics->comment.str,
+						    chistics->comment.length,
 						    system_charset_info);
     if ((table->file->update_row(table->record[1],table->record[0])))
       ret= SP_WRITE_ROW_FAILED;
@@ -314,10 +328,10 @@ static struct st_used_field init_fields[]=
 {
   { "Name",     NAME_LEN, MYSQL_TYPE_STRING,    0},
   { "Type",            9, MYSQL_TYPE_STRING,    0},
-  { "Creator",        77, MYSQL_TYPE_STRING,    0},
+  { "Definer",        77, MYSQL_TYPE_STRING,    0},
   { "Modified",        0, MYSQL_TYPE_TIMESTAMP, 0},
   { "Created",         0, MYSQL_TYPE_TIMESTAMP, 0},
-  { "Suid",            1, MYSQL_TYPE_STRING,    0},
+  { "Security_type",   1, MYSQL_TYPE_STRING,    0},
   { "Comment",  NAME_LEN, MYSQL_TYPE_STRING,    0},
   { 0,                 0, MYSQL_TYPE_STRING,    0}
 };
@@ -347,6 +361,8 @@ print_field_values(THD *thd, TABLE *table,
 	case MYSQL_TYPE_TIMESTAMP:
 	  {
 	    TIME tmp_time;
+
+	    bzero((char *)&tmp_time, sizeof(tmp_time));
 	    ((Field_timestamp *) used_field->field)->get_time(&tmp_time);
 	    protocol->store(&tmp_time);
 	  }
@@ -354,6 +370,7 @@ print_field_values(THD *thd, TABLE *table,
 	default:
 	  {
 	    String *tmp_string1= new String();
+
 	    get_field(&thd->mem_root, used_field->field, tmp_string1);
 	    protocol->store(tmp_string1);
 	  }
@@ -490,14 +507,14 @@ sp_find_procedure(THD *thd, LEX_STRING *name)
 
 int
 sp_create_procedure(THD *thd, char *name, uint namelen, char *def, uint deflen,
-		    char *comment, uint commentlen, bool suid)
+		    st_sp_chistics *chistics)
 {
   DBUG_ENTER("sp_create_procedure");
   DBUG_PRINT("enter", ("name: %*s def: %*s", namelen, name, deflen, def));
   int ret;
 
   ret= db_create_routine(thd, TYPE_ENUM_PROCEDURE, name, namelen, def, deflen,
-			 comment, commentlen, suid);
+			 chistics);
 
   DBUG_RETURN(ret);
 }
@@ -521,7 +538,7 @@ sp_drop_procedure(THD *thd, char *name, uint namelen)
 int
 sp_update_procedure(THD *thd, char *name, uint namelen,
 		    char *newname, uint newnamelen,
-		    char *comment, uint commentlen, enum suid_behaviour suid)
+		    st_sp_chistics *chistics)
 {
   DBUG_ENTER("sp_update_procedure");
   DBUG_PRINT("enter", ("name: %*s", namelen, name));
@@ -533,7 +550,7 @@ sp_update_procedure(THD *thd, char *name, uint namelen,
     delete sp;
   ret= db_update_routine(thd, TYPE_ENUM_PROCEDURE, name, namelen,
 			 newname, newnamelen,
-			 comment, commentlen, suid);
+			 chistics);
 
   DBUG_RETURN(ret);
 }
@@ -585,14 +602,14 @@ sp_find_function(THD *thd, LEX_STRING *name)
 
 int
 sp_create_function(THD *thd, char *name, uint namelen, char *def, uint deflen,
-		   char *comment, uint commentlen, bool suid)
+		   st_sp_chistics *chistics)
 {
   DBUG_ENTER("sp_create_function");
   DBUG_PRINT("enter", ("name: %*s def: %*s", namelen, name, deflen, def));
   int ret;
 
   ret= db_create_routine(thd, TYPE_ENUM_FUNCTION, name, namelen, def, deflen,
-			 comment, commentlen, suid);
+			 chistics);
 
   DBUG_RETURN(ret);
 }
@@ -616,7 +633,7 @@ sp_drop_function(THD *thd, char *name, uint namelen)
 int
 sp_update_function(THD *thd, char *name, uint namelen,
 		    char *newname, uint newnamelen,
-		    char *comment, uint commentlen, enum suid_behaviour suid)
+		    st_sp_chistics *chistics)
 {
   DBUG_ENTER("sp_update_procedure");
   DBUG_PRINT("enter", ("name: %*s", namelen, name));
@@ -628,7 +645,7 @@ sp_update_function(THD *thd, char *name, uint namelen,
     delete sp;
   ret= db_update_routine(thd, TYPE_ENUM_FUNCTION, name, namelen,
 			 newname, newnamelen,
-			 comment, commentlen, suid);
+			 chistics);
 
   DBUG_RETURN(ret);
 }
diff --git a/sql/sp.h b/sql/sp.h
index f957dd3c692..9bce886336d 100644
--- a/sql/sp.h
+++ b/sql/sp.h
@@ -33,7 +33,7 @@ sp_find_procedure(THD *thd, LEX_STRING *name);
 
 int
 sp_create_procedure(THD *thd, char *name, uint namelen, char *def, uint deflen,
-		    char *comment, uint commentlen, bool suid);
+		    st_sp_chistics *chistics);
 
 int
 sp_drop_procedure(THD *thd, char *name, uint namelen);
@@ -42,7 +42,7 @@ sp_drop_procedure(THD *thd, char *name, uint namelen);
 int
 sp_update_procedure(THD *thd, char *name, uint namelen,
 		    char *newname, uint newnamelen,
-		    char *comment, uint commentlen, enum suid_behaviour suid);
+		    st_sp_chistics *chistics);
 
 int
 sp_show_create_procedure(THD *thd, LEX_STRING *name);
@@ -55,7 +55,7 @@ sp_find_function(THD *thd, LEX_STRING *name);
 
 int
 sp_create_function(THD *thd, char *name, uint namelen, char *def, uint deflen,
-		   char *comment, uint commentlen, bool suid);
+		   st_sp_chistics *chistics);
 
 int
 sp_drop_function(THD *thd, char *name, uint namelen);
@@ -63,7 +63,7 @@ sp_drop_function(THD *thd, char *name, uint namelen);
 int
 sp_update_function(THD *thd, char *name, uint namelen,
 		   char *newname, uint newnamelen,
-		   char *comment, uint commentlen, enum suid_behaviour suid);
+		   st_sp_chistics *chistics);
 
 int
 sp_show_create_function(THD *thd, LEX_STRING *name);
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index 78829896324..39669c5b86f 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -190,14 +190,12 @@ sp_head::create(THD *thd)
     ret= sp_create_function(thd,
 			    m_name.str, m_name.length,
 			    m_defstr.str, m_defstr.length,
-			    m_comment.str, m_comment.length,
-			    m_suid);
+			    m_chistics);
   else
     ret= sp_create_procedure(thd,
 			     m_name.str, m_name.length,
 			     m_defstr.str, m_defstr.length,
-			     m_comment.str, m_comment.length,
-			     m_suid);
+			     m_chistics);
 
   DBUG_RETURN(ret);
 }
diff --git a/sql/sp_head.h b/sql/sp_head.h
index 2a8f8a1e017..0f0c618ffb4 100644
--- a/sql/sp_head.h
+++ b/sql/sp_head.h
@@ -50,6 +50,7 @@ public:
   my_bool m_simple_case;	// TRUE if parsing simple case, FALSE otherwise
   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;
 #if NOT_USED_NOW
   // QQ We're not using this at the moment.
   List<char *> m_calls;		// Called procedures.
@@ -68,20 +69,6 @@ public:
   void
   init(LEX_STRING *name, LEX *lex);
 
-  void
-  init_options(LEX_STRING *comment, enum suid_behaviour suid)
-  {
-    m_comment.length= 0;
-    m_comment.str= 0;
-    if (comment)
-    {
-      m_comment.length= comment->length;
-      m_comment.str= comment->str;
-    }
-    m_suid= suid ? suid - 1 : 1;
-  }
-
-
   int
   create(THD *thd);
   
@@ -154,17 +141,17 @@ public:
     return sp_map_result_type(m_returns);
   }
 
-  void sp_set_info(char *creator, uint creatorlen,
-		   longlong created, longlong modified,
-		   bool suid, char *comment, uint commentlen)
+  void set_info(char *creator, uint creatorlen,
+		longlong created, longlong modified,
+		bool suid, char *comment, uint commentlen)
   {
     m_creator= creator;
     m_creatorlen= creatorlen;
     m_created= created;
     m_modified= modified;
-    m_comment.length= commentlen;
-    m_comment.str= comment;
-    m_suid= suid;
+    m_chistics->comment.length= commentlen;
+    m_chistics->comment.str= comment;
+    m_chistics->suid= (suid ? IS_SUID : IS_NOT_SUID);
   }
 
   inline void reset_thd_mem_root(THD *thd)
@@ -195,12 +182,10 @@ private:
 
   LEX_STRING m_name;
   LEX_STRING m_defstr;
-  LEX_STRING m_comment;
   char *m_creator;
   uint m_creatorlen;
   longlong m_created;
   longlong m_modified;
-  bool m_suid;
 
   sp_pcontext *m_pcont;		// Parse context
   List<LEX> m_lex;		// Temp. store for the other lex
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 4199bb18548..181db61013f 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -517,6 +517,13 @@ public:
 typedef class st_select_lex SELECT_LEX;
 
 
+struct st_sp_chistics
+{
+  LEX_STRING comment;
+  enum suid_behaviour suid;
+  bool detistic;
+};
+
 /* The state of the lex parsing. This is saved in the THD struct */
 
 typedef struct st_lex
@@ -583,7 +590,6 @@ typedef struct st_lex
   enum enum_enable_or_disable alter_keys_onoff;
   enum enum_var_type option_type;
   enum tablespace_op_type tablespace_op;
-  enum suid_behaviour suid;
   uint uint_geom_type;
   uint grant, grant_tot_col, which_columns;
   uint fk_delete_opt, fk_update_opt, fk_match_option;
@@ -598,6 +604,7 @@ typedef struct st_lex
   bool sp_lex_in_use;	/* Keep track on lex usage in SPs for error handling */
   sp_pcontext *spcont;
   HASH spfuns;		/* Called functions */
+  st_sp_chistics sp_chistics;
 
   st_lex()
   {
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index c8d5ebe0743..08a11d7c729 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -3558,12 +3558,10 @@ mysql_execute_command(THD *thd)
       }
       if (lex->sql_command == SQLCOM_ALTER_PROCEDURE)
 	res= sp_update_procedure(thd, lex->udf.name.str, lex->udf.name.length,
-				lex->name, newname_len, lex->comment->str,
-				lex->comment->length, lex->suid);
+				 lex->name, newname_len, &lex->sp_chistics);
       else
 	res= sp_update_function(thd, lex->udf.name.str, lex->udf.name.length,
-			       lex->name, newname_len, lex->comment->str,
-			       lex->comment->length, lex->suid);
+				lex->name, newname_len,	&lex->sp_chistics);
       switch (res)
       {
       case SP_OK:
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index a9035b18c23..369880486f9 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -139,6 +139,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize);
 %token  CUBE_SYM
 %token  DEFINER_SYM
 %token	DELETE_SYM
+%token  DETERMINISTIC_SYM
 %token	DUAL_SYM
 %token	DO_SYM
 %token	DROP
@@ -283,6 +284,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize);
 %token	LEAVES
 %token	LEVEL_SYM
 %token	LEX_HOSTNAME
+%token  LANGUAGE_SYM
 %token	LIKE
 %token	LINES
 %token	LOCAL_SYM
@@ -630,7 +632,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize);
 	LEX_HOSTNAME ULONGLONG_NUM field_ident select_alias ident ident_or_text
         UNDERSCORE_CHARSET IDENT_sys TEXT_STRING_sys TEXT_STRING_literal
 	NCHAR_STRING opt_component key_cache_name
-        SP_FUNC ident_or_spfunc sp_opt_label sp_comment sp_newname
+        SP_FUNC ident_or_spfunc sp_opt_label
 
 %type <lex_str_ptr>
 	opt_table_alias
@@ -761,6 +763,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize);
 	subselect_end select_var_list select_var_list_init help opt_len
 	opt_extended_describe
 	statement sp_suid
+	sp_c_chistics sp_a_chistics sp_chistic sp_c_chistic sp_a_chistic
 END_OF_INPUT
 
 %type <NONE> call sp_proc_stmts sp_proc_stmt
@@ -1051,11 +1054,16 @@ create:
 	  }
           '(' sp_pdparam_list ')'
 	  {
-	    Lex->spcont->set_params();
+	    LEX *lex= Lex;
+
+	    lex->spcont->set_params();
+	    bzero((char *)&lex->sp_chistics, sizeof(st_sp_chistics));
 	  }
-	  sp_comment sp_suid
+	  sp_c_chistics
 	  {
-	    Lex->sphead->init_options(&$9, Lex->suid);
+	    LEX *lex= Lex;
+
+	    lex->sphead->m_chistics= &lex->sp_chistics;
 	  }
 	  sp_proc_stmt
 	  {
@@ -1113,17 +1121,22 @@ create_function_tail:
 	  }
 	  RETURNS_SYM type
 	  {
-	    Lex->sphead->m_returns= (enum enum_field_types)$7;
+	    LEX *lex= Lex;
+
+	    lex->sphead->m_returns= (enum enum_field_types)$7;
+	    bzero((char *)&lex->sp_chistics, sizeof(st_sp_chistics));
 	  }
-	  sp_comment sp_suid
+	  sp_c_chistics
 	  {
-	    Lex->sphead->init_options(&$9, Lex->suid);
-          }
+	    LEX *lex= Lex;
+
+	    lex->sphead->m_chistics= &lex->sp_chistics;
+	  }
 	  sp_proc_stmt
 	  {
 	    LEX *lex= Lex;
 
-	    lex->sql_command = SQLCOM_CREATE_SPFUNCTION;
+	    lex->sql_command= SQLCOM_CREATE_SPFUNCTION;
 	    /* Restore flag if it was cleared above */
 	    if (lex->sphead->m_old_cmq)
 	      YYTHD->client_capabilities |= CLIENT_MULTI_QUERIES;
@@ -1131,21 +1144,45 @@ create_function_tail:
 	  }
 	;
 
-sp_comment:
-	  /* Empty */			{ $$.str= 0; $$.length= 0; }
-	| COMMENT_SYM TEXT_STRING_sys	{ $$= $2; }
+sp_a_chistics:
+	  /* Empty */ {}
+	| sp_a_chistics sp_a_chistic {}
+	;
+
+sp_c_chistics:
+	  /* Empty */ {}
+	| sp_c_chistics sp_c_chistic {}
+	;
+
+/* Characteristics for both create and alter */
+sp_chistic:
+	  COMMENT_SYM TEXT_STRING_sys { Lex->sp_chistics.comment= $2; }
+	| sp_suid { }
 	;
 
-sp_newname:
-	  /* Empty */			{ $$.str= 0; $$.length= 0; }
-	| NAME_SYM ident		{ $$= $2; }
+/* Alter characteristics */
+sp_a_chistic:
+	  sp_chistic     { }
+	| NAME_SYM ident { Lex->name= $2.str; }
 	;
 
+/* Create characteristics */
+sp_c_chistic:
+	  sp_chistic            { }
+	| LANGUAGE_SYM SQL_SYM  { }
+	| DETERMINISTIC_SYM     { Lex->sp_chistics.detistic= TRUE; }
+	| NOT DETERMINISTIC_SYM { Lex->sp_chistics.detistic= FALSE; }
+	;
 
 sp_suid:
-	  /* Empty */			    { Lex->suid= IS_DEFAULT_SUID; }
-	| SQL_SYM SECURITY_SYM DEFINER_SYM  { Lex->suid= IS_SUID; }
-	| SQL_SYM SECURITY_SYM INVOKER_SYM  { Lex->suid= IS_NOT_SUID; }
+	  SQL_SYM SECURITY_SYM DEFINER_SYM
+	  {
+	    Lex->sp_chistics.suid= IS_SUID;
+	  }
+	| SQL_SYM SECURITY_SYM INVOKER_SYM
+	  {
+	    Lex->sp_chistics.suid= IS_NOT_SUID;
+	  }
 	;
 
 call:
@@ -2648,36 +2685,35 @@ alter:
 	    lex->sql_command=SQLCOM_ALTER_DB;
 	    lex->name=$3.str;
 	  }
-	| ALTER PROCEDURE ident sp_newname sp_comment sp_suid
-	  opt_restrict
+	| ALTER PROCEDURE ident
+	  {
+	    LEX *lex= Lex;
+
+	    bzero((char *)&lex->sp_chistics, sizeof(st_sp_chistics));
+	    Lex->name= 0;
+          }
+	  sp_a_chistics
 	  {
 	    THD *thd= YYTHD;
 	    LEX *lex=Lex;
 
 	    lex->sql_command= SQLCOM_ALTER_PROCEDURE;
 	    lex->udf.name= $3;
-	    lex->name= $4.str;
-	    /* $5 is a yacc/bison internal struct, so we can't keep
-	       the pointer to it for use outside the parser. */
-	    lex->comment= (LEX_STRING *)thd->alloc(sizeof(LEX_STRING));
-	    lex->comment->str= $5.str;
-	    lex->comment->length= $5.length;
 	  }
-	| ALTER FUNCTION_SYM ident sp_newname sp_comment sp_suid
-	  opt_restrict
+	| ALTER FUNCTION_SYM ident
+	  {
+	    LEX *lex= Lex;
+
+	    bzero((char *)&lex->sp_chistics, sizeof(st_sp_chistics));
+	    lex->name= 0;
+          }
+	  sp_a_chistics
 	  {
 	    THD *thd= YYTHD;
 	    LEX *lex=Lex;
 
 	    lex->sql_command= SQLCOM_ALTER_FUNCTION;
 	    lex->udf.name= $3;
-	    lex->name= $4.str;
-	    /* $5 is a yacc/bison internal struct, so we can't keep
-	       the pointer to it for use outside the parser. */
-	    lex->comment= (LEX_STRING *)thd->alloc(sizeof(LEX_STRING));
-	    lex->comment= (LEX_STRING *)thd->alloc(sizeof(LEX_STRING));
-	    lex->comment->str= $5.str;
-	    lex->comment->length= $5.length;
 	  }
 	;
 
@@ -5775,6 +5811,7 @@ keyword:
 	| INNOBASE_SYM		{}
 	| INSERT_METHOD		{}
 	| RELAY_THREAD		{}
+	| LANGUAGE_SYM          {}
 	| LAST_SYM		{}
 	| LEAVES                {}
 	| LEVEL_SYM		{}