bk commit into 5.0 tree (sergefp:1.1938)

[Sergey Petrunia]

Below is the list of changes that have just been committed into a local
5.0 repository of psergey. When psergey 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.1938 05/07/19 22:21:23 sergefp@stripped +16 -0
  Make PROCEDUREs which aren't invoked from functions to execute without table prelocking.
  (aka "prelocking-free SP-execution") 

  mysql-test/t/sp-prelocking.test
    1.1 05/07/19 22:18:25 sergefp@stripped +157 -0

  mysql-test/r/sp-prelocking.result
    1.1 05/07/19 22:18:25 sergefp@stripped +156 -0

  sql/sql_test.cc
    1.42 05/07/19 22:18:25 sergefp@stripped +24 -0
    added debug print_table_list[_arr] functions

  sql/sql_table.cc
    1.262 05/07/19 22:18:25 sergefp@stripped +22 -2
    in mysql_admin_table(), clear LEX::sroutines after processing every table. Reason for
    doing this: suppose one of the tables is a VIEW and it uses routines that cause some
    error. We don't want the routines to stay and cause the error to be printed for all
    subsequent tables. 

  sql/sql_parse.cc
    1.447 05/07/19 22:18:25 sergefp@stripped +17 -0
    Adding prelocking-free SP-execution: in 'case SQLCOM_CALL:' if the executed PROCEDURE
is 
    prelocking-free, delete it from SP cache.

  sql/sql_base.cc
    1.268 05/07/19 22:18:25 sergefp@stripped +32 -13
    Adding prelocking-free SP-execution: 
     * make routines_require_prelocking() call to check if we need to perform table 
       prelocking and if we need to treat 1st routine specially.
     * added some dbug printouts.

  sql/sp_head.h
    1.63 05/07/19 22:18:25 sergefp@stripped +15 -2
    Adding prelocking-free SP-execution: added sp_head::non_prelocked_mode 

  sql/sp_head.cc
    1.159 05/07/19 22:18:25 sergefp@stripped +15 -0
    Adding prelocking-free SP-execution: 
      When we're executing prelocking-free SP, try to leave prelocked mode after
evaluating 
      SP's arguments. 

  sql/sp_cache.h
    1.9 05/07/19 22:18:25 sergefp@stripped +13 -3
    Improved the comments

  sql/sp.h
    1.25 05/07/19 22:18:25 sergefp@stripped +1 -1
    Adding prelocking-free SP-execution

  mysql-test/t/sp-prelocking.test
    1.0 05/07/19 22:18:25 sergefp@stripped +0 -0
    BitKeeper file /home/psergey/mysql-5.0-sp-no-lock-r1/mysql-test/t/sp-prelocking.test

  mysql-test/r/sp-prelocking.result
    1.0 05/07/19 22:18:25 sergefp@stripped +0 -0
    BitKeeper file /home/psergey/mysql-5.0-sp-no-lock-r1/mysql-test/r/sp-prelocking.result

  sql/sp.cc
    1.83 05/07/19 22:18:24 sergefp@stripped +55 -7
    Adding prelocking-free SP-execution: 
    * sp_cache_routines_and_add_tables now can treat 1st passed routine specially: put it
into
      the cache, but don't compute the table list for it.
    * added routines_require_prelocking() which determines if the above mentioned handling
is 
      needed.

  sql/mysql_priv.h
    1.327 05/07/19 22:18:24 sergefp@stripped +4 -1
    added debug print_table_list[_arr] functions

  sql/lock.cc
    1.70 05/07/19 22:18:24 sergefp@stripped +4 -0
    Added debug printout of which tables are unlocked.

  mysql-test/t/sp.test
    1.130 05/07/19 22:18:24 sergefp@stripped +23 -22
    Adding prelocking-free SP-execution: 
    * Disable testcase for BUG#6063 - it still fails when the routine is loaded from
mysql.proc
      (this is reported as BUG#11986)

  mysql-test/r/sp.result
    1.135 05/07/19 22:18:24 sergefp@stripped +0 -26
    Adding prelocking-free SP-execution: 
    * Disable testcase for BUG#6063 - it still fails when the routine is loaded from
mysql.proc
      (this is reported as BUG#11986)

  mysql-test/r/sp-threads.result
    1.5 05/07/19 22:18:24 sergefp@stripped +1 -1
    Adding prelocking-free SP-execution: updated test results.

# 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:	sergefp
# Host:	newbox.mylan
# Root:	/home/psergey/mysql-5.0-sp-no-lock-r1

--- 1.69/sql/lock.cc	2005-07-13 09:48:02 +00:00
+++ 1.70/sql/lock.cc	2005-07-19 22:18:24 +00:00
@@ -221,6 +221,8 @@
 void mysql_unlock_tables(THD *thd, MYSQL_LOCK *sql_lock)
 {
   DBUG_ENTER("mysql_unlock_tables");
+  DBUG_EXECUTE("lock", print_table_list_arr("  UNLOCK", sql_lock->table,
+                                             sql_lock->table_count););
   if (sql_lock->lock_count)
     thr_multi_unlock(sql_lock->locks,sql_lock->lock_count);
   if (sql_lock->table_count)
@@ -252,6 +254,8 @@
   uint i,found;
   DBUG_ENTER("mysql_unlock_read_tables");
 
+  DBUG_EXECUTE("lock", print_table_list_arr("  UNLOCK READ", sql_lock->table,
+                                            sql_lock->table_count););
   /* Move all write locks first */
   THR_LOCK_DATA **lock=sql_lock->locks;
   for (i=found=0 ; i < sql_lock->lock_count ; i++)

--- 1.326/sql/mysql_priv.h	2005-07-13 13:50:27 +00:00
+++ 1.327/sql/mysql_priv.h	2005-07-19 22:18:24 +00:00
@@ -483,7 +483,7 @@
 void cleanup_items(Item *item);
 class THD;
 void close_thread_tables(THD *thd, bool locked=0, bool skip_derived=0,
-                         TABLE *stopper= 0);
+                         TABLE *stopper= 0, bool force_exit_prelocking=FALSE);
 bool check_one_table_access(THD *thd, ulong privilege,
 			   TABLE_LIST *tables);
 bool check_routine_access(THD *thd,ulong want_access,char *db,char *name,
@@ -1009,8 +1009,11 @@
 void TEST_filesort(SORT_FIELD *sortorder,uint s_length);
 void print_plan(JOIN* join, double read_time, double record_count,
                 uint idx, const char *info);
+void print_table_list(const char *name, TABLE_LIST *list);
+void print_table_list_arr(const char *name, TABLE **start, int count);
 #endif
 void mysql_print_status();
+
 /* key.cc */
 int find_ref_key(TABLE *form,Field *field, uint *offset);
 void key_copy(byte *to_key, byte *from_record, KEY *key_info, uint key_length);

--- 1.267/sql/sql_base.cc	2005-07-13 09:57:01 +00:00
+++ 1.268/sql/sql_base.cc	2005-07-19 22:18:25 +00:00
@@ -390,6 +390,11 @@
 			LOCK_open
     skip_derived	Set to 1 (0 = default) if we should not free derived
 			tables.
+    stopper             ??? 
+    do_exit_prelocking  Exit prelocked mode even if
+                        (!thd->lex->requires_prelocking()).
+                        This is used only when evaluating parameter of a
+                        non-prelocked PROCEDURE.
 
   IMPLEMENTATION
     Unlocks tables and frees derived tables.
@@ -402,7 +407,7 @@
 */
 
 void close_thread_tables(THD *thd, bool lock_in_use, bool skip_derived, 
-                         TABLE *stopper)
+                         TABLE *stopper, bool do_exit_prelocking)
 {
   bool found_old_table;
   prelocked_mode_type prelocked_mode= thd->prelocked_mode;
@@ -458,7 +463,8 @@
     if (!prelocked_mode)
       DBUG_VOID_RETURN;
 
-    if (!thd->lex->requires_prelocking())
+    if (!thd->lex->requires_prelocking() && 
+        !((thd->prelocked_mode != NON_PRELOCKED) & do_exit_prelocking))
     {
       /*
         If we are executing one of substatements we have to mark
@@ -473,6 +479,7 @@
       We are in prelocked mode, so we have to leave it now with doing
       implicit UNLOCK TABLES if need.
     */
+    DBUG_PRINT("info",("thd->prelocked_mode= NON_PRELOCKED"));
     thd->prelocked_mode= NON_PRELOCKED;
 
     if (prelocked_mode == PRELOCKED_UNDER_LOCK_TABLES)
@@ -531,7 +538,7 @@
       If we are here then we are leaving normal prelocked mode, so it is
       good idea to turn off OPTION_TABLE_LOCK flag.
     */
-    DBUG_ASSERT(thd->lex->requires_prelocking());
+    DBUG_ASSERT(thd->lex->requires_prelocking() || do_exit_prelocking);
     thd->options&= ~(ulong) (OPTION_TABLE_LOCK);
   }
 
@@ -1763,6 +1770,7 @@
   DBUG_RETURN(1);
 }
 
+
 /*
   Open all tables in list
 
@@ -1787,6 +1795,8 @@
     0  - OK
     -1 - error
 */
+bool routines_require_prelocking(THD *thd, SQL_LIST *routines_list, bool
+                                 *need_skip_first);
 
 int open_tables(THD *thd, TABLE_LIST **start, uint *counter)
 {
@@ -1797,6 +1807,7 @@
   /* Also used for indicating that prelocking is need */
   TABLE_LIST **query_tables_last_own;
   DBUG_ENTER("open_tables");
+  DBUG_EXECUTE("locks", print_table_list("  open_tables in=", *start););
   /*
     temporary mem_root for new .frm parsing.
     TODO: variables for size
@@ -1832,18 +1843,22 @@
     derived/information schema tables and views possible. Thus "counter"
     may be still zero for prelocked statement...
   */
-  if (!thd->prelocked_mode && !thd->lex->requires_prelocking()
&&
-      thd->lex->sroutines.records)
+  if (!thd->prelocked_mode && !thd->lex->requires_prelocking())
   {
-    TABLE_LIST **save_query_tables_last= thd->lex->query_tables_last;
+    bool need_skip_first;
+    if (routines_require_prelocking(thd, &thd->lex->sroutines_list,
+                                    &need_skip_first))
+    {
+      TABLE_LIST **save_query_tables_last= thd->lex->query_tables_last;
 
-    DBUG_ASSERT(thd->lex->query_tables == *start);
+      DBUG_ASSERT(thd->lex->query_tables == *start);
 
-    if (sp_cache_routines_and_add_tables(thd, thd->lex) ||
-        *start)
-    {
-      query_tables_last_own= save_query_tables_last;
-      *start= thd->lex->query_tables;
+      if (sp_cache_routines_and_add_tables(thd, thd->lex, need_skip_first) ||
+          *start)
+      {
+        query_tables_last_own= save_query_tables_last;
+        *start= thd->lex->query_tables;
+      }
     }
   }
 
@@ -1962,6 +1977,7 @@
   if (query_tables_last_own)
     thd->lex->mark_as_requiring_prelocking(query_tables_last_own);
 
+  DBUG_EXECUTE("locks", print_table_list("  open_tables out=", *start););
   DBUG_RETURN(result);
 }
 
@@ -2209,6 +2225,7 @@
     in prelocked mode.
   */
   DBUG_ASSERT(!thd->prelocked_mode || !thd->lex->requires_prelocking());
+  DBUG_EXECUTE("locks", print_table_list("  LOCK", tables););
   /*
     If statement requires prelocking then it has non-empty table list.
     So it is safe to shortcut.
@@ -2293,7 +2310,8 @@
         Let us mark all tables which don't belong to the statement itself,
         and was marked as occupied during open_tables() as free for reuse.
       */
-      mark_real_tables_as_free_for_reuse(first_not_own);
+      mark_real_tables_as_free_for_reuse(first_not_own); 
+      DBUG_PRINT("info",("prelocked_mode= PRELOCKED"));
       thd->prelocked_mode= PRELOCKED;
     }
   }
@@ -2317,6 +2335,7 @@
     if (thd->lex->requires_prelocking())
     {
       mark_real_tables_as_free_for_reuse(first_not_own);
+      DBUG_PRINT("info", ("thd->prelocked_mode= PRELOCKED_UNDER_LOCK_TABLES"));
       thd->prelocked_mode= PRELOCKED_UNDER_LOCK_TABLES;
     }
   }

--- 1.446/sql/sql_parse.cc	2005-07-14 10:42:31 +00:00
+++ 1.447/sql/sql_parse.cc	2005-07-19 22:18:25 +00:00
@@ -27,6 +27,7 @@
 
 #include "sp_head.h"
 #include "sp.h"
+#include "sp_cache.h"
 
 #ifdef HAVE_OPENSSL
 /*
@@ -4137,6 +4138,9 @@
         By this moment all needed SPs should be in cache so no need
         to look into DB. Moreover we may be unable to do it becuase
         we may don't have read lock on mysql.proc
+
+        psergey: The above seems to be no longer true ? (but we still
+        need to only look into the cache in the following call)
       */
       if (!(sp= sp_find_procedure(thd, lex->spname, TRUE)))
       {
@@ -4165,6 +4169,13 @@
 #ifndef EMBEDDED_LIBRARY
 	    thd->net.no_send_ok= nsok;
 #endif
+            /*
+              sp->is_invoked() may be true if this statement is a recursive 
+              call. Let the outer call statement remove the procedure from 
+              cache in this case.
+            */
+            if (!sp->is_invoked() && sp->non_prelocked_mode)
+              sp_cache_remove(&thd->sp_proc_cache, lex->spname);
 	    goto error;
 	  }
           /*
@@ -4183,6 +4194,8 @@
 #ifndef EMBEDDED_LIBRARY
 	  thd->net.no_send_ok= nsok;
 #endif
+          if (!sp->is_invoked() && sp->non_prelocked_mode)
+            sp_cache_remove(&thd->sp_proc_cache, lex->spname);
 	  goto error;
 	}
 	sp_change_security_context(thd, sp, &save_ctx);
@@ -4194,6 +4207,8 @@
 	  thd->net.no_send_ok= nsok;
 #endif
 	  sp_restore_security_context(thd, sp, &save_ctx);
+          if (!sp->is_invoked() && sp->non_prelocked_mode)
+            sp_cache_remove(&thd->sp_proc_cache, lex->spname);
 	  goto error;
 	}
 
@@ -4251,6 +4266,8 @@
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
 	sp_restore_security_context(thd, sp, &save_ctx);
 #endif
+        if (!sp->is_invoked() && sp->non_prelocked_mode)
+          sp_cache_remove(&thd->sp_proc_cache, lex->spname);
 
 #ifndef EMBEDDED_LIBRARY
 	thd->net.no_send_ok= nsok;

--- 1.261/sql/sql_table.cc	2005-07-14 11:01:40 +00:00
+++ 1.262/sql/sql_table.cc	2005-07-19 22:18:25 +00:00
@@ -2105,7 +2105,12 @@
   DBUG_RETURN(error);
 }
 
-
+static void clear_lex_sroutines(LEX *lex)
+{
+  if (lex->sroutines.records)
+    my_hash_reset(&lex->sroutines);
+  lex->sroutines_list.empty();
+}
 
 /*
   RETURN VALUES
@@ -2134,7 +2139,13 @@
   LEX *lex= thd->lex;
   int result_code;
   DBUG_ENTER("mysql_admin_table");
-
+  /* 
+    NOTE: The code below assumes that we're not inside prelocked SP when it 
+    makes calls to clear_lex_sroutines(). This assumption currently is
+    guaranteed by the parser.
+  */
+  DBUG_ASSERT(thd->prelocked_mode == NON_PRELOCKED);
+  
   field_list.push_back(item = new Item_empty_string("Table", NAME_LEN*2));
   item->maybe_null = 1;
   field_list.push_back(item = new Item_empty_string("Op", 10));
@@ -2190,6 +2201,12 @@
       switch ((*prepare_func)(thd, table, check_opt)) {
       case  1:           // error, message written to net
         close_thread_tables(thd);
+        /* 
+          It could be the current table is VIEW that uses FUNCTIONs. Remove 
+          them (failure to do it will cause error in the first processed
+          VIEW to be reported each subsequent processed table/view).
+        */
+        clear_lex_sroutines(thd->lex);
         continue;
       case -1:           // error, message could be written to net
         goto err;
@@ -2231,6 +2248,7 @@
         tables can be left opening
       */
       close_thread_tables(thd);
+      clear_lex_sroutines(thd->lex);
       if (protocol->write())
 	goto err;
       continue;
@@ -2255,6 +2273,7 @@
                           table_name);
       protocol->store(buff, length, system_charset_info);
       close_thread_tables(thd);
+      clear_lex_sroutines(thd->lex);
       table->table=0;				// For query cache
       if (protocol->write())
 	goto err;
@@ -2426,6 +2445,7 @@
       query_cache_invalidate3(thd, table->table, 0);
     }
     close_thread_tables(thd);
+    clear_lex_sroutines(thd->lex);
     table->table=0;				// For query cache
     if (protocol->write())
       goto err;

--- 1.41/sql/sql_test.cc	2005-05-06 08:39:17 +00:00
+++ 1.42/sql/sql_test.cc	2005-07-19 22:18:25 +00:00
@@ -326,6 +326,30 @@
 }
 
 
+void print_table_list(const char *name, TABLE_LIST *list)
+{
+  DBUG_LOCK_FILE;
+  fprintf(DBUG_FILE, " %s {", name);
+  for (TABLE_LIST *tbl= list; tbl; tbl= tbl->next_global)
+  {
+    fprintf(DBUG_FILE, "%s, ", tbl->table_name);
+  }
+  fprintf(DBUG_FILE, "}\n");
+  DBUG_UNLOCK_FILE;
+}
+
+void print_table_list_arr(const char *name, TABLE **start, int count)
+{
+  DBUG_LOCK_FILE;
+  fprintf(DBUG_FILE, " %s {", name);
+  for (int idx=0; idx < count; idx++)
+  {
+    fprintf(DBUG_FILE, "%s, ", start[idx]->s->table_name);
+  }
+  fprintf(DBUG_FILE, "}\n");
+  DBUG_UNLOCK_FILE;
+}
+
 static void push_locks_into_array(DYNAMIC_ARRAY *ar, THR_LOCK_DATA *data,
 				  bool wait, const char *text)
 {

--- 1.4/mysql-test/r/sp-threads.result	2005-07-13 09:48:02 +00:00
+++ 1.5/mysql-test/r/sp-threads.result	2005-07-19 22:18:24 +00:00
@@ -35,7 +35,7 @@
 show processlist;
 Id	User	Host	db	Command	Time	State	Info
 #	root	localhost	test	Sleep	#		NULL
-#	root	localhost	test	Query	#	Locked	call bug9486()
+#	root	localhost	test	Query	#	Locked	update t1, t2 set val= 1 where id1=id2
 #	root	localhost	test	Query	#	NULL	show processlist
 unlock tables;
 drop procedure bug9486;

--- 1.134/mysql-test/r/sp.result	2005-07-11 10:46:33 +00:00
+++ 1.135/mysql-test/r/sp.result	2005-07-19 22:18:24 +00:00
@@ -3036,30 +3036,4 @@
 drop procedure if exists bug6063|
 drop procedure if exists bug7088_1|
 drop procedure if exists bug7088_2|
-create procedure bug6063()
-lâbel: begin end|
-call bug6063()|
-show create procedure bug6063|
-Procedure	sql_mode	Create Procedure
-bug6063		CREATE PROCEDURE `test`.`bug6063`()
-l?bel: begin end
-set character set utf8|
-create procedure bug7088_1()
-label1: begin end label1|
-create procedure bug7088_2()
-läbel1: begin end|
-call bug7088_1()|
-call bug7088_2()|
-set character set default|
-show create procedure bug7088_1|
-Procedure	sql_mode	Create Procedure
-bug7088_1		CREATE PROCEDURE `test`.`bug7088_1`()
-label1: begin end label1
-show create procedure bug7088_2|
-Procedure	sql_mode	Create Procedure
-bug7088_2		CREATE PROCEDURE `test`.`bug7088_2`()
-läbel1: begin end
-drop procedure bug6063|
-drop procedure bug7088_1|
-drop procedure bug7088_2|
 drop table t1,t2;

--- 1.129/mysql-test/t/sp.test	2005-07-11 10:46:33 +00:00
+++ 1.130/mysql-test/t/sp.test	2005-07-19 22:18:24 +00:00
@@ -3811,29 +3811,30 @@
 drop procedure if exists bug7088_2|
 --enable_warnings
 
-create procedure bug6063()
-  lâbel: begin end|
-call bug6063()|
-# QQ Known bug: this will not show the label correctly.
-show create procedure bug6063|
+# psergey: temporarily disabled until Bar fixes BUG#11986
+#  create procedure bug6063()
+#    lâbel: begin end|
+#  call bug6063()|
+#  # QQ Known bug: this will not show the label correctly.
+#  show create procedure bug6063|
+#  
+#  set character set utf8|
+#  create procedure bug7088_1()
+#    label1: begin end label1|
+#  create procedure bug7088_2()
+#    läbel1: begin end|
+#  call bug7088_1()|
+#  call bug7088_2()|
+#  set character set default|
+#  show create procedure bug7088_1|
+#  show create procedure bug7088_2|
+#  
+#  drop procedure bug6063|
+#  drop procedure bug7088_1|
+#  drop procedure bug7088_2|
+#  
+#  
 
-set character set utf8|
-create procedure bug7088_1()
-  label1: begin end label1|
-create procedure bug7088_2()
-  läbel1: begin end|
-call bug7088_1()|
-call bug7088_2()|
-set character set default|
-show create procedure bug7088_1|
-show create procedure bug7088_2|
-
-drop procedure bug6063|
-drop procedure bug7088_1|
-drop procedure bug7088_2|
-
-
-#
 # BUG#NNNN: New bug synopsis
 #
 #--disable_warnings

--- 1.82/sql/sp.cc	2005-07-13 10:20:38 +00:00
+++ 1.83/sql/sp.cc	2005-07-19 22:18:24 +00:00
@@ -1176,6 +1176,48 @@
 
 
 /*
+  Check if routines in routines_list require prelocking.
+  routines_require_prelocking()
+    thd
+    routines 
+    need_skip_first  OUT TRUE - don't do prelocking for the 1st element in 
+                                routines list.
+                         FALSE- otherwise
+  NOTES 
+    This function makes some assumptions about how routines_list is populated 
+    by the parser.
+
+  RETURN
+    TRUE  Need to do routines prelocking
+    FALSE Otherwise.
+*/
+
+bool routines_require_prelocking(THD *thd, SQL_LIST *routines_list, 
+                                 bool *need_skip_first)
+{
+  Sroutine_hash_entry *routine;
+  routine= (Sroutine_hash_entry*)routines_list->first;
+
+  *need_skip_first= FALSE;
+  if (!routine)
+    return FALSE;
+
+  if (routine->key.str[0] != TYPE_ENUM_PROCEDURE)
+  {
+    /* FUNCTION/TRIGGERs require prelocking */
+    return TRUE;
+  }
+  *need_skip_first= TRUE;
+
+  /* 
+    Ok the 1st routine in the list is a procedure. If the second routine is
+    present, it can't be a procedure, and we need prelocking.
+  */
+  return TRUE;
+}
+
+
+/*
   Auxilary function that adds new element to the set of stored routines
   used by statement.
 
@@ -1328,10 +1370,11 @@
 
 static bool
 sp_cache_routines_and_add_tables_aux(THD *thd, LEX *lex,
-                                     Sroutine_hash_entry *start)
+                                     Sroutine_hash_entry *start, 
+                                     bool skip_first = false)
 {
   bool result= FALSE;
-
+  bool first= TRUE;
   DBUG_ENTER("sp_cache_routines_and_add_tables_aux");
 
   for (Sroutine_hash_entry *rt= start; rt; rt= rt->next)
@@ -1367,9 +1410,15 @@
     }
     if (sp)
     {
-      sp_update_stmt_used_routines(thd, lex, &sp->m_sroutines);
-      result|= sp->add_used_tables_to_table_list(thd, &lex->query_tables_last);
+      if (first && skip_first)
+        sp->non_prelocked_mode= TRUE;
+      else
+      {
+        sp_update_stmt_used_routines(thd, lex, &sp->m_sroutines);
+        result|= sp->add_used_tables_to_table_list(thd,
&lex->query_tables_last);
+      }
     }
+    first= FALSE;
   }
   DBUG_RETURN(result);
 }
@@ -1391,11 +1440,10 @@
 */
 
 bool
-sp_cache_routines_and_add_tables(THD *thd, LEX *lex)
+sp_cache_routines_and_add_tables(THD *thd, LEX *lex, bool skip_first)
 {
-
   return sp_cache_routines_and_add_tables_aux(thd, lex,
-           (Sroutine_hash_entry *)lex->sroutines_list.first);
+           (Sroutine_hash_entry *)lex->sroutines_list.first, skip_first);
 }
 
 

--- 1.24/sql/sp.h	2005-07-13 09:57:01 +00:00
+++ 1.25/sql/sp.h	2005-07-19 22:18:25 +00:00
@@ -82,7 +82,7 @@
 void sp_add_used_routine(LEX *lex, Query_arena *arena,
                          sp_name *rt, char rt_type);
 void sp_update_sp_used_routines(HASH *dst, HASH *src);
-bool sp_cache_routines_and_add_tables(THD *thd, LEX *lex);
+bool sp_cache_routines_and_add_tables(THD *thd, LEX *lex, bool skip_first);
 void sp_cache_routines_and_add_tables_for_view(THD *thd, LEX *lex,
                                                LEX *aux_lex);
 void sp_cache_routines_and_add_tables_for_triggers(THD *thd, LEX *lex,

--- 1.8/sql/sp_cache.h	2005-05-27 10:03:33 +00:00
+++ 1.9/sql/sp_cache.h	2005-07-19 22:18:25 +00:00
@@ -22,6 +22,12 @@
 #pragma interface			/* gcc class implementation */
 #endif
 
+/*
+  Stored procedures/functions cache. This is used as follows:
+   * Each thread has its own cache.
+   * When SP is used it is always in some thread's cache.
+*/
+
 class sp_head;
 class sp_cache;
 
@@ -31,16 +37,20 @@
 /* Clear the cache *cp and set *cp to NULL */
 void sp_cache_clear(sp_cache **cp);
 
-/* Insert an SP to cache. If 'cp' points to NULL, it's set to a new cache */
+/* Insert an SP into cache. If 'cp' points to NULL, it's set to a new cache */
 void sp_cache_insert(sp_cache **cp, sp_head *sp);
 
 /* Lookup an SP in cache */
 sp_head *sp_cache_lookup(sp_cache **cp, sp_name *name);
 
-/* Remove an SP from cache. Returns true if something was removed */
+/* 
+  Remove an SP from cache, and also bump the Cversion number so all other 
+  caches are invalidated. 
+  Returns true if something was removed.
+*/
 bool sp_cache_remove(sp_cache **cp, sp_name *name);
 
-/* Invalidate a cache */
+/* Invalidate all existing SP caches by bumping Cversion number. */
 void sp_cache_invalidate();
 
 

--- 1.158/sql/sp_head.cc	2005-07-09 18:04:13 +00:00
+++ 1.159/sql/sp_head.cc	2005-07-19 22:18:25 +00:00
@@ -316,6 +316,7 @@
   :Query_arena(&main_mem_root, INITIALIZED_FOR_SP),
    m_returns_cs(NULL), m_has_return(FALSE),
    m_simple_case(FALSE), m_multi_results(FALSE), m_in_handler(FALSE),
+   non_prelocked_mode(FALSE),
    m_is_invoked(FALSE)
 {
   extern byte *
@@ -879,6 +880,8 @@
       octx= new sp_rcontext(csize, hmax, cmax);
       tmp_octx= TRUE;
     }
+
+    /* Evaluate SP arguments (i.e. get the values passed as parameters) */
     // QQ: Should do type checking?
     for (i = 0 ; (it= li++) && i < params ; i++)
     {
@@ -916,6 +919,18 @@
       }
     }
 
+    /* 
+      Okay, got values for all arguments. If this is a prelocking-free SP, 
+      close tables that arguments evaluation might have used. Pass TRUE as
+      last parameter to force exit from prelocking mode (we could have entered
+      prelocked mode if the arguments of this procedure need prelocking, e.g.
+      for statement like "CALL sp_proc(sp_func())"
+    */
+    if (non_prelocked_mode)
+    {
+      close_thread_tables(thd, 0, 0, 0, TRUE);
+      thd->lock=0;
+    }
     // The rest of the frame are local variables which are all IN.
     // Default all variables to null (those with default clauses will
     // be set by an set instruction).

--- 1.62/sql/sp_head.h	2005-07-09 17:55:09 +00:00
+++ 1.63/sql/sp_head.h	2005-07-19 22:18:25 +00:00
@@ -107,7 +107,19 @@
 
   MEM_ROOT main_mem_root;
 public:
-
+  /*
+    If true, this PROCEDURE executes in non-prelocked mode. A PROCEDURE
+    executes in non-prelocked mode iff it is not invoked from a FUNCTION or
+    TRIGGER.
+    If a PROCEDURE executes in non-prelocked mode, each statement gets and 
+    releases all locks it needs, and DDL statements are allowed.
+
+    Instances of class sp_head that represent procedures that execute in
+    non-prelocked mode cannot be cached - they are kept in cache no longer
+    then their CALL statement executes. This limitation will soon be removed.
+  */
+  bool non_prelocked_mode;
+  
   int m_type;			// TYPE_ENUM_FUNCTION or TYPE_ENUM_PROCEDURE
   enum enum_field_types m_returns; // For FUNCTIONs only
   CHARSET_INFO *m_returns_cs;	// For FUNCTIONs only
@@ -263,7 +275,8 @@
   /* Add tables used by routine to the table list. */
   bool add_used_tables_to_table_list(THD *thd,
                                      TABLE_LIST ***query_tables_last_ptr);
-
+  
+  bool is_invoked() const { return m_is_invoked; }
 private:
 
   MEM_ROOT *m_thd_root;		// Temp. store for thd's mem_root
--- New file ---
+++ mysql-test/r/sp-prelocking.result	05/07/19 22:18:25
drop database if exists testdb;
drop table if exists t1, t2, t3;
drop procedure if exists sp1;
drop procedure if exists sp2;
drop procedure if exists sp3;
drop procedure if exists sp4;
drop function if exists f1;
create database testdb;
use testdb//
create procedure sp1 () 
begin
drop table if exists t1;
select 1 as "my-col";
end;
//
select database();
database()
testdb
call sp1();
my-col
1
Warnings:
Note	1051	Unknown table 't1'
select database();
database()
testdb
use test;
select database();
database()
test
call testdb.sp1();
my-col
1
Warnings:
Note	1051	Unknown table 't1'
select database();
database()
test
drop procedure testdb.sp1;
drop database testdb;
create procedure sp1() 
begin 
create table t1 (a int); 
insert into t1 values (10); 
end//
create procedure sp2()
begin
create table t2(a int);
insert into t2 values(1);
call sp1();
end//
create function f1() returns int
begin 
return (select max(a) from t1);
end//
create procedure sp3()
begin 
call sp1();
select 'func', f1();
end//
call sp1();
select 't1',a from t1;
t1	a
t1	10
drop table t1;
call sp2();
select 't1',a from t1;
t1	a
t1	10
select 't2',a from t2;
t2	a
t2	1
drop table t1, t2;
call sp3();
func	f1()
func	10
select 't1',a from t1;
t1	a
t1	10
drop table t1;
drop procedure sp1;
drop procedure sp2;
drop procedure sp3;
drop function f1;
create procedure sp1()
begin
create temporary table t2(a int);
insert into t2 select * from t1;
end//
create procedure sp2()
begin
create temporary table t1 (a int);
insert into t1 values(1);
call sp1();
select 't1', a from t1;
select 't2', b from t2;
drop table t1;
drop table t2;
end//
call sp2();
t1	a
t1	1
drop procedure sp1;
drop procedure sp2;
create table t1 (a int);
insert into t1 values(1),(2);
create table t2 as select * from t1;
create table t3 as select * from t1;
create procedure sp1(a int)
begin
select a;
end //
create function f1() returns int
begin
return (select max(a) from t1);
end //
CALL sp1(f1());
a
2
create procedure sp2(a int)
begin
select * from t3;
select a;
end //
create procedure sp3()
begin 
select * from t1;
call sp2(5);
end //
create procedure sp4()
begin 
select * from t2;
call sp3();
end //
call sp4();
a
1
1
1
2
a
1
1
2
a
1
1
2
a
5
drop table t1,t2,t3;
drop procedure sp1;
drop procedure sp2;
drop procedure sp3;
drop procedure sp4;
drop function f1;

--- New file ---
+++ mysql-test/t/sp-prelocking.test	05/07/19 22:18:25
--disable_warnings
drop database if exists testdb;
drop table if exists t1, t2, t3;
drop procedure if exists sp1;
drop procedure if exists sp2;
drop procedure if exists sp3;
drop procedure if exists sp4;
drop function if exists f1;
--enable_warnings

# BUG#8072 

create database testdb;
delimiter //;
use testdb//
create procedure sp1 () 
begin
  drop table if exists t1;
  select 1 as "my-col";
end;
//
delimiter ;//

select database();
call sp1();
select database();

use test;
select database();
call testdb.sp1();
select database();

drop procedure testdb.sp1;
drop database testdb;

# BUG#8766

delimiter //;
create procedure sp1() 
begin 
  create table t1 (a int); 
  insert into t1 values (10); 
end//

create procedure sp2()
begin
  create table t2(a int);
  insert into t2 values(1);
  call sp1();
end//

create function f1() returns int
begin 
  return (select max(a) from t1);
end//

create procedure sp3()
begin 
  call sp1();
  select 'func', f1();
end//

delimiter ;//

call sp1();
select 't1',a from t1;

drop table t1;
call sp2();
select 't1',a from t1;
select 't2',a from t2;
drop table t1, t2;

call sp3();
select 't1',a from t1;

drop table t1;

drop procedure sp1;
drop procedure sp2;
drop procedure sp3;
drop function f1;

delimiter //;
create procedure sp1()
begin
  create temporary table t2(a int);
  insert into t2 select * from t1;
end//

create procedure sp2()
begin
  create temporary table t1 (a int);
  insert into t1 values(1);
  call sp1();
  select 't1', a from t1;
  select 't2', b from t2;
  drop table t1;
  drop table t2;
end//

delimiter ;//
call sp2();

drop procedure sp1;
drop procedure sp2;

# Miscelaneous tests
create table t1 (a int);
insert into t1 values(1),(2);
create table t2 as select * from t1;
create table t3 as select * from t1;
delimiter //;
create procedure sp1(a int)
begin
  select a;
end //

create function f1() returns int
begin
  return (select max(a) from t1);
end //

delimiter ;//

CALL sp1(f1());

#############
delimiter //;
create procedure sp2(a int)
begin
  select * from t3;
  select a;
end //

create procedure sp3()
begin 
  select * from t1;
  call sp2(5);
end //

create procedure sp4()
begin 
  select * from t2;
  call sp3();
end //

delimiter ;//
call sp4();

drop table t1,t2,t3;
drop procedure sp1;
drop procedure sp2;
drop procedure sp3;
drop procedure sp4;
drop function f1;