bk commit into 5.0 tree (acurtis:1.1958) BUG#10100

[antony]

Below is the list of changes that have just been committed into a local
5.0 repository of antony. When antony 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
  1.1958 05/09/08 15:49:40 acurtis@stripped +11 -0
  Bug#10100
    "function (and stored procedure?) recursivity problem"
    Permit recursion, tests for bug/feature

  sql/sql_class.h
    1.263 05/09/08 15:47:41 acurtis@stripped +1 -0
    new sp_recursion_levels 

  sql/sp_head.h
    1.68 05/09/08 15:47:41 acurtis@stripped +3 -0
    new sp_head variables - m_recursion_level, m_next_cached_sp

  sql/sp_head.cc
    1.180 05/09/08 15:47:41 acurtis@stripped +2 -1
    initialize new sp_head variables

  sql/sp.cc
    1.93 05/09/08 15:47:41 acurtis@stripped +137 -88
    refactor code for recursion.
    temporary LEX on stack, not on heap.

  sql/set_var.cc
    1.138 05/09/08 15:47:40 acurtis@stripped +4 -0
    sp_recursion_levels system variable

  sql/mysqld.cc
    1.492 05/09/08 15:47:40 acurtis@stripped +6 -0
    recursion-levels option

  sql/item_func.cc
    1.249 05/09/08 15:47:40 acurtis@stripped +3 -1
    changes for recursion

  mysql-test/t/variables.test
    1.49 05/09/08 15:47:39 acurtis@stripped +4 -0
    tests for recursion

  mysql-test/t/sp.test
    1.144 05/09/08 15:47:39 acurtis@stripped +47 -0
    tests for recursion

  mysql-test/r/variables.result
    1.73 05/09/08 15:47:39 acurtis@stripped +8 -0
    tests for recursion

  mysql-test/r/sp.result
    1.149 05/09/08 15:47:39 acurtis@stripped +53 -0
    tests for recursion

# 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:	acurtis
# Host:	ltantony.xiphis.org
# Root:	/usr/home/antony/work2/p3-bug10100.1

--- 1.248/sql/item_func.cc	2005-09-03 00:13:09 +01:00
+++ 1.249/sql/item_func.cc	2005-09-08 15:47:40 +01:00
@@ -4704,7 +4704,9 @@
   st_sp_security_context save_ctx;
 #endif
 
-  if (! m_sp && ! (m_sp= sp_find_function(thd, m_name, TRUE)))
+  if ((!m_sp || (m_sp->m_flags & sp_head::IS_INVOKED) ||
+      (thd->variables.sp_recursion_levels > m_sp->m_recursion_level)) &&

+      !(m_sp= sp_find_function(thd, m_name, TRUE)))
   {
     my_error(ER_SP_DOES_NOT_EXIST, MYF(0), "FUNCTION", m_name->m_qname.str);
     goto error;

--- 1.491/sql/mysqld.cc	2005-09-01 19:25:13 +01:00
+++ 1.492/sql/mysqld.cc	2005-09-08 15:47:40 +01:00
@@ -4422,6 +4422,7 @@
   OPT_OPTIMIZER_PRUNE_LEVEL,
   OPT_UPDATABLE_VIEWS_WITH_LIMIT,
   OPT_SP_AUTOMATIC_PRIVILEGES,
+  OPT_SP_RECURSION_LEVELS,
   OPT_AUTO_INCREMENT, OPT_AUTO_INCREMENT_OFFSET,
   OPT_ENABLE_LARGE_PAGES,
   OPT_TIMED_MUTEXES,
@@ -5612,6 +5613,11 @@
    (gptr*) &global_system_variables.read_buff_size,
    (gptr*) &max_system_variables.read_buff_size,0, GET_ULONG, REQUIRED_ARG,
    128*1024L, IO_SIZE*2+MALLOC_OVERHEAD, ~0L, MALLOC_OVERHEAD, IO_SIZE, 0},
+  {"recursion-levels", OPT_SP_RECURSION_LEVELS,
+   "Stored procedure recursion level",
+   (gptr*) &global_system_variables.sp_recursion_levels,
+   (gptr*) &max_system_variables.sp_recursion_levels, 0, GET_ULONG,
+   OPT_ARG, 0, 0, 255, 0, 1, 0 },
 #ifdef HAVE_REPLICATION
   {"relay_log_purge", OPT_RELAY_LOG_PURGE,
    "0 = do not purge relay logs. 1 = purge them as soon as they are no more needed.",

--- 1.262/sql/sql_class.h	2005-09-02 14:21:08 +01:00
+++ 1.263/sql/sql_class.h	2005-09-08 15:47:41 +01:00
@@ -528,6 +528,7 @@
   ulong completion_type;
   /* Determines which non-standard SQL behaviour should be enabled */
   ulong sql_mode;
+  ulong sp_recursion_levels;
   /* check of key presence in updatable view */
   ulong updatable_views_with_limit;
   ulong default_week_format;

--- 1.72/mysql-test/r/variables.result	2005-09-02 20:41:58 +01:00
+++ 1.73/mysql-test/r/variables.result	2005-09-08 15:47:39 +01:00
@@ -351,6 +351,14 @@
 set global server_id=100;
 set global slow_launch_time=100;
 set sort_buffer_size=100;
+set @@sp_recursion_levels=10;
+select @@sp_recursion_levels;
+@@sp_recursion_levels
+10
+set @@sp_recursion_levels=0;
+select @@sp_recursion_levels;
+@@sp_recursion_levels
+0
 set sql_auto_is_null=1;
 select @@sql_auto_is_null;
 @@sql_auto_is_null

--- 1.48/mysql-test/t/variables.test	2005-09-02 20:40:59 +01:00
+++ 1.49/mysql-test/t/variables.test	2005-09-08 15:47:39 +01:00
@@ -237,6 +237,10 @@
 set global server_id=100;
 set global slow_launch_time=100;
 set sort_buffer_size=100;
+set @@sp_recursion_levels=10;
+select @@sp_recursion_levels;
+set @@sp_recursion_levels=0;
+select @@sp_recursion_levels;
 set sql_auto_is_null=1;
 select @@sql_auto_is_null;
 set @@sql_auto_is_null=0;

--- 1.137/sql/set_var.cc	2005-09-02 20:40:59 +01:00
+++ 1.138/sql/set_var.cc	2005-09-08 15:47:40 +01:00
@@ -362,6 +362,8 @@
 				       &SV::table_type);
 sys_var_thd_storage_engine sys_storage_engine("storage_engine",
 				       &SV::table_type);
+sys_var_thd_ulong	sys_sp_recursion_levels("sp_recursion_levels",
+						&SV::sp_recursion_levels);
 #ifdef HAVE_REPLICATION
 sys_var_sync_binlog_period sys_sync_binlog_period("sync_binlog",
&sync_binlog_period);
 sys_var_thd_ulong	sys_sync_replication("sync_replication",
@@ -682,6 +684,7 @@
   &sys_sql_warnings,
   &sys_sql_notes,
   &sys_storage_engine,
+  &sys_sp_recursion_levels,
 #ifdef HAVE_REPLICATION
   &sys_sync_binlog_period,
   &sys_sync_replication,
@@ -972,6 +975,7 @@
   {"sql_notes",               (char*) &sys_sql_notes,               SHOW_BOOL},
   {"sql_warnings",            (char*) &sys_sql_warnings,            SHOW_BOOL},
   {sys_storage_engine.name,   (char*) &sys_storage_engine,          SHOW_SYS},
+  {sys_sp_recursion_levels.name, (char*) &sys_sp_recursion_levels,  SHOW_SYS},
 #ifdef HAVE_REPLICATION
   {sys_sync_binlog_period.name,(char*) &sys_sync_binlog_period,     SHOW_SYS},
 #endif

--- 1.148/mysql-test/r/sp.result	2005-08-27 11:29:29 +01:00
+++ 1.149/mysql-test/r/sp.result	2005-09-08 15:47:39 +01:00
@@ -3193,4 +3193,57 @@
 return f1;
 end|
 drop function bug9048|
+drop function if exists bug10100f|
+drop procedure if exists bug10100p|
+drop procedure if exists bug10100t|
+create function bug10100f(prm int) returns int
+begin
+if prm > 1 then
+return prm * bug10100f(prm - 1);
+end if;
+return 1;
+end|
+create procedure bug10100p(prm int, inout res int)
+begin
+set res = res * prm;
+if prm > 1 then
+call bug10100p(prm - 1, res);  
+end if;
+end|
+create procedure bug10100t(prm int)
+begin
+declare res int;
+set res = 1;
+call bug10100p(prm, res);
+select res;
+end|
+set @@sp_recursion_levels=4|
+select @@sp_recursion_levels|
+@@sp_recursion_levels
+4
+select bug10100f(3)|
+bug10100f(3)
+6
+select bug10100f(4)|
+bug10100f(4)
+24
+select bug10100f(5)|
+bug10100f(5)
+120
+select bug10100f(6)|
+ERROR HY000: Recursive stored routines are not allowed.
+call bug10100t(5)|
+res
+120
+set @@sp_recursion_levels=0|
+select @@sp_recursion_levels|
+@@sp_recursion_levels
+0
+select bug10100f(5)|
+ERROR HY000: Recursive stored routines are not allowed.
+call bug10100t(5)|
+ERROR HY000: Recursive stored routines are not allowed.
+drop function bug10100f|
+drop procedure bug10100p|
+drop procedure bug10100t|
 drop table t1,t2;

--- 1.143/mysql-test/t/sp.test	2005-09-01 19:02:04 +01:00
+++ 1.144/mysql-test/t/sp.test	2005-09-08 15:47:39 +01:00
@@ -4044,6 +4044,53 @@
 drop function bug9048|
 
 #
+# BUG#10100: function (and stored procedure?) recursivity problem
+#
+--disable_warnings
+drop function if exists bug10100f|
+drop procedure if exists bug10100p|
+drop procedure if exists bug10100t|
+--enable_warnings
+create function bug10100f(prm int) returns int
+begin
+  if prm > 1 then
+    return prm * bug10100f(prm - 1);
+  end if;
+  return 1;
+end|
+create procedure bug10100p(prm int, inout res int)
+begin
+  set res = res * prm;
+  if prm > 1 then
+    call bug10100p(prm - 1, res);  
+  end if;
+end|
+create procedure bug10100t(prm int)
+begin
+  declare res int;
+  set res = 1;
+  call bug10100p(prm, res);
+  select res;
+end|
+set @@sp_recursion_levels=4|
+select @@sp_recursion_levels|
+select bug10100f(3)|
+select bug10100f(4)|
+select bug10100f(5)|
+--error 1424
+select bug10100f(6)|
+call bug10100t(5)|
+set @@sp_recursion_levels=0|
+select @@sp_recursion_levels|
+--error 1424
+select bug10100f(5)|
+--error 1424
+call bug10100t(5)|
+drop function bug10100f|
+drop procedure bug10100p|
+drop procedure bug10100t|
+
+#
 # BUG#NNNN: New bug synopsis
 #
 #--disable_warnings

--- 1.92/sql/sp.cc	2005-09-02 14:21:07 +01:00
+++ 1.93/sql/sp.cc	2005-09-08 15:47:41 +01:00
@@ -29,6 +29,14 @@
 	      const char *returns, ulong returnslen,
 	      const char *body, ulong bodylen,
 	      st_sp_chistics *chistics);
+static int
+db_load_routine(THD *thd, int type, sp_name *name, sp_head **sphp,
+                ulong sql_mode, const char *params, const char *returns,
+                const char *body, st_sp_chistics &chistics,
+                const char *definer, longlong created, longlong modified);
+static sp_head *
+sp_find_routine(THD *thd, int type, sp_name *name, sp_cache **cp,
+                bool cache_only);
 
 /*
  *
@@ -377,83 +385,90 @@
   close_proc_table(thd, &open_tables_state_backup);
   table= 0;
 
-  {
-    String defstr;
-    LEX *oldlex= thd->lex;
-    char olddb[128];
-    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;
-
-    thd->variables.sql_mode= sql_mode;
-    thd->variables.select_limit= HA_POS_ERROR;
-
-    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;
-    }
+  ret= db_load_routine(thd, type, name, sphp,
+                       sql_mode, params, returns, body, chistics,
+                       definer, created, modified);
+                       
+ done:
+  if (table)
+    close_proc_table(thd, &open_tables_state_backup);
+  DBUG_RETURN(ret);
+}
 
-    dbchanged= FALSE;
-    if ((ret= sp_use_new_db(thd, name->m_db.str, olddb, sizeof(olddb),
-			    1, &dbchanged)))
-      goto done;
 
-    {
-      /* This is something of a kludge. We need to initialize some fields
-       * in thd->lex (the unit and master stuff), and the easiest way to
-       * do it is, is to call mysql_init_query(), but this unfortunately
-       * resets teh value_list where we keep the CALL parameters. So we
-       * copy the list and then restore it. (... and found_semicolon too).
-       */
-      List<Item> tmpvals= thd->lex->value_list;
-      char *tmpfsc= thd->lex->found_semicolon;
-
-      lex_start(thd, (uchar*)defstr.c_ptr(), defstr.length());
-      thd->lex->value_list= tmpvals;
-      thd->lex->found_semicolon= tmpfsc;
-    }
+static int
+db_load_routine(THD *thd, int type, sp_name *name, sp_head **sphp,
+                ulong sql_mode, const char *params, const char *returns,
+                const char *body, st_sp_chistics &chistics,
+                const char *definer, longlong created, longlong modified)
+{
+  LEX *oldlex= thd->lex, newlex;
+  String defstr;
+  char olddb[128];
+  bool dbchanged;
+  ulong old_sql_mode= thd->variables.sql_mode;
+  ha_rows select_limit= thd->variables.select_limit;
+  int ret= SP_INTERNAL_ERROR;
+
+  thd->variables.sql_mode= sql_mode;
+  thd->variables.select_limit= HA_POS_ERROR;
+
+  thd->lex= &newlex;
+  newlex.current_select= NULL;
+
+  defstr.set_charset(system_charset_info);
+  if (!create_string(thd, &defstr,
+		     type,
+		     name,
+		     params, strlen(params),
+		     returns, strlen(returns),
+		     body, strlen(body),
+		     &chistics))
+    goto end;
 
-    if (yyparse(thd) || thd->is_fatal_error || thd->lex->sphead == NULL)
-    {
-      LEX *newlex= thd->lex;
-      sp_head *sp= newlex->sphead;
+  dbchanged= FALSE;
+  if ((ret= sp_use_new_db(thd, name->m_db.str, olddb, sizeof(olddb),
+			  1, &dbchanged)))
+    goto end;
 
-      if (dbchanged && (ret= mysql_change_db(thd, olddb, 1)))
-	goto done;
-      if (sp)
-      {
-	delete sp;
-	newlex->sphead= NULL;
-      }
-      ret= SP_PARSE_ERROR;
-    }
-    else
-    {
-      if (dbchanged && (ret= mysql_change_db(thd, olddb, 1)))
-	goto done;
-      *sphp= thd->lex->sphead;
-      (*sphp)->set_info((char *)definer, (uint)strlen(definer),
-			created, modified, &chistics, sql_mode);
-      (*sphp)->optimize();
-    }
-    thd->lex->sql_command= oldcmd;
-    thd->variables.sql_mode= old_sql_mode;
-    thd->variables.select_limit= select_limit;
+  {
+    /* This is something of a kludge. We need to initialize some fields
+     * in thd->lex (the unit and master stuff), and the easiest way to
+     * do it is, is to call mysql_init_query(), but this unfortunately
+     * resets teh value_list where we keep the CALL parameters. So we
+     * copy the list and then restore it. (... and found_semicolon too).
+     */
+    List<Item> tmpvals= newlex.value_list;
+    char *tmpfsc= newlex.found_semicolon;
+
+    lex_start(thd, (uchar*)defstr.c_ptr(), defstr.length());
+    newlex.value_list= tmpvals;
+    newlex.found_semicolon= tmpfsc;
   }
 
- done:
-  if (table)
-    close_proc_table(thd, &open_tables_state_backup);
-  DBUG_RETURN(ret);
+  if (yyparse(thd) || thd->is_fatal_error || newlex.sphead == NULL)
+  {
+    sp_head *sp= newlex.sphead;
+
+    if (dbchanged && (ret= mysql_change_db(thd, olddb, 1)))
+      goto end;
+    delete sp;
+    ret= SP_PARSE_ERROR;
+  }
+  else
+  {
+    if (dbchanged && (ret= mysql_change_db(thd, olddb, 1)))
+      goto end;
+    *sphp= newlex.sphead;
+    (*sphp)->set_info((char *)definer, (uint)strlen(definer),
+		      created, modified, &chistics, sql_mode);
+    (*sphp)->optimize();
+  }
+  thd->variables.sql_mode= old_sql_mode;
+  thd->variables.select_limit= select_limit;
+end:  
+  thd->lex= oldlex;
+  return ret;
 }
 
 
@@ -924,13 +939,8 @@
   DBUG_PRINT("enter", ("name: %*s.%*s",
 		       name->m_db.length, name->m_db.str,
 		       name->m_name.length, name->m_name.str));
-
-  if (!(sp= sp_cache_lookup(&thd->sp_proc_cache, name)) && !cache_only)
-  {
-    if (db_find_routine(thd, TYPE_ENUM_PROCEDURE, name, &sp) == SP_OK)
-      sp_cache_insert(&thd->sp_proc_cache, sp);
-  }
-
+  sp= sp_find_routine(thd, TYPE_ENUM_PROCEDURE, name, &thd->sp_proc_cache,
+                      cache_only);
   DBUG_RETURN(sp);
 }
 
@@ -1072,12 +1082,57 @@
   sp_head *sp;
   DBUG_ENTER("sp_find_function");
   DBUG_PRINT("enter", ("name: %*s", name->m_name.length, name->m_name.str));
+  sp= sp_find_routine(thd, TYPE_ENUM_FUNCTION, name, &thd->sp_func_cache, 
+                      cache_only);
+  DBUG_RETURN(sp);
+}
+
+
+static sp_head *
+sp_find_routine(THD *thd, int type, sp_name *name, sp_cache **cp,
+                bool cache_only)
+{
+  sp_head *sp;
+  DBUG_ENTER("sp_find_function");
+  DBUG_PRINT("enter", ("name: %*s", name->m_name.length, name->m_name.str));
 
-  if (!(sp= sp_cache_lookup(&thd->sp_func_cache, name)) &&
-      !cache_only)
+  if (!(sp= sp_cache_lookup(cp, name)) && !cache_only)
   {
-    if (db_find_routine(thd, TYPE_ENUM_FUNCTION, name, &sp) == SP_OK)
-      sp_cache_insert(&thd->sp_func_cache, sp);
+    if (db_find_routine(thd, type, name, &sp) == SP_OK)
+      sp_cache_insert(cp, sp);
+  }
+  else
+  {
+    for (sp_head *sp2= sp; sp2; sp2= sp2->m_next_cached_sp)
+    {
+      if (sp2->m_recursion_level >= thd->variables.sp_recursion_levels)
+        DBUG_RETURN(sp);
+      if (!(sp2->m_flags & sp_head::IS_INVOKED))
+        DBUG_RETURN(sp2);
+      if (sp2->m_next_cached_sp)
+        continue;
+
+      /* Last iteration, need to create a new sp instance */
+      const char *returns= "";
+      char definer[HOSTNAME_LENGTH+USERNAME_LENGTH+2];
+      String retstr(64);
+      strxmov(definer, sp->m_definer_user.str, "@",
+                       sp->m_definer_host.str, NullS);
+      if (type == TYPE_ENUM_FUNCTION)
+      {
+        sp_returns_type(thd, retstr, sp);
+        returns= retstr.ptr();
+      }
+
+      if (db_load_routine(thd, type, name, &sp,
+                          sp->m_sql_mode, sp->m_params.str, returns,
+                          sp->m_body.str, *sp->m_chistics, definer,
+                          sp->m_created, sp->m_modified) == SP_OK)
+      {
+        sp2->m_next_cached_sp= sp;
+        sp->m_recursion_level= 1 + sp2->m_recursion_level;
+      }
+    }
   }
   DBUG_RETURN(sp);
 }
@@ -1385,10 +1440,6 @@
                               &thd->sp_func_cache : &thd->sp_proc_cache),
                               &name)))
     {
-      LEX *oldlex= thd->lex;
-      LEX *newlex= new st_lex;
-      thd->lex= newlex;
-      newlex->current_select= NULL;
       name.m_name.str= strchr(name.m_qname.str, '.');
       name.m_db.length= name.m_name.str - name.m_qname.str;
       name.m_db.str= strmake_root(thd->mem_root, name.m_qname.str,
@@ -1403,8 +1454,6 @@
         else
           sp_cache_insert(&thd->sp_proc_cache, sp);
       }
-      delete newlex;
-      thd->lex= oldlex;
     }
     if (sp)
     {

--- 1.179/sql/sp_head.cc	2005-09-03 00:13:09 +01:00
+++ 1.180/sql/sp_head.cc	2005-09-08 15:47:41 +01:00
@@ -395,7 +395,7 @@
 
 sp_head::sp_head()
   :Query_arena(&main_mem_root, INITIALIZED_FOR_SP),
-   m_flags(0), m_returns_cs(NULL)
+   m_flags(0), m_returns_cs(NULL), m_recursion_level(0), m_next_cached_sp(NULL)
 {
   extern byte *
     sp_table_key(const byte *ptr, uint *plen, my_bool first);
@@ -573,6 +573,7 @@
 sp_head::~sp_head()
 {
   destroy();
+  delete m_next_cached_sp;
   if (m_thd)
     restore_thd_mem_root(m_thd);
 }

--- 1.67/sql/sp_head.h	2005-09-03 00:13:09 +01:00
+++ 1.68/sql/sp_head.h	2005-09-08 15:47:41 +01:00
@@ -138,6 +138,9 @@
   LEX_STRING m_definer_host;
   longlong m_created;
   longlong m_modified;
+  ulong m_recursion_level;
+  sp_head *m_next_cached_sp;
+  
   /*
     Set containing names of stored routines used by this routine.
     Note that unlike elements of similar set for statement elements of this