Merge bk-internal.mysql.com:/home/bk/mysql-5.0

into mysql.com:/home/my/mysql-5.0


sql/mysqld.cc:
  Auto merged

81 files changed, 2657 insertions, 1220 deletions

diff --git a/BUILD/SETUP.sh b/BUILD/SETUP.sh
index 403857f403f..fbb0936c4b5 100755
--- a/BUILD/SETUP.sh
+++ b/BUILD/SETUP.sh
@@ -48,8 +48,8 @@ global_warnings="-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wch
 c_warnings="$global_warnings -Wunused"
 cxx_warnings="$global_warnings -Woverloaded-virtual -Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor"
 
-base_max_configs="--with-innodb --with-berkeley-db --with-ndbcluster --with-archive-storage-engine --with-raid --with-openssl --with-raid --with-vio"
-max_leave_isam_configs="--with-innodb --with-berkeley-db --with-ndbcluster --with-archive-storage-engine --with-federated-storage-engine --with-raid --with-openssl --with-raid --with-vio --with-embedded-server"
+base_max_configs="--with-innodb --with-berkeley-db --with-ndbcluster --with-archive-storage-engine --with-raid --with-openssl --with-raid"
+max_leave_isam_configs="--with-innodb --with-berkeley-db --with-ndbcluster --with-archive-storage-engine --with-federated-storage-engine --with-raid --with-openssl --with-raid --with-embedded-server"
 max_no_es_configs="$max_leave_isam_configs --without-isam"
 max_configs="$max_no_es_configs --with-embedded-server"
 
diff --git a/BUILD/compile-pentium64-valgrind-max b/BUILD/compile-pentium64-valgrind-max
index 7f78089c3e8..2e4ff8e0082 100644
--- a/BUILD/compile-pentium64-valgrind-max
+++ b/BUILD/compile-pentium64-valgrind-max
@@ -9,7 +9,7 @@ cxx_warnings="$cxx_warnings $debug_extra_warnings"
 extra_configs="$pentium_configs $debug_configs"
 
 # We want to test isam when building with valgrind
-extra_configs="$extra_configs --with-berkeley-db --with-innodb --with-isam --with-embedded-server --with-openssl --with-vio --with-raid --with-ndbcluster"
+extra_configs="$extra_configs --with-berkeley-db --with-innodb --with-isam --with-embedded-server --with-openssl --with-raid --with-ndbcluster"
 
 . "$path/FINISH.sh"
 
diff --git a/BitKeeper/etc/logging_ok b/BitKeeper/etc/logging_ok
index 89e1ff4bd48..4150e9ce2b2 100644
--- a/BitKeeper/etc/logging_ok
+++ b/BitKeeper/etc/logging_ok
@@ -47,6 +47,7 @@ dlenev@build.mysql.com
 dlenev@jabberwock.localdomain
 dlenev@mysql.com
 ejonore@mc03.ndb.mysql.com
+gbichot@quadita2.mysql.com
 georg@beethoven.local
 georg@beethoven.site
 gerberb@ou800.zenez.com
@@ -71,6 +72,7 @@ hf@deer.(none)
 hf@deer.mysql.r18.ru
 hf@genie.(none)
 igor@hundin.mysql.fi
+igor@linux.local
 igor@rurik.mysql.com
 ingo@mysql.com
 jan@hundin.mysql.fi
diff --git a/config/ac-macros/misc.m4 b/config/ac-macros/misc.m4
index ec0c296a8b2..556cc7625cc 100644
--- a/config/ac-macros/misc.m4
+++ b/config/ac-macros/misc.m4
@@ -594,22 +594,13 @@ AC_MSG_RESULT($ac_cv_conv_longlong_to_float)
 ])
 
 AC_DEFUN([MYSQL_CHECK_VIO], [
-  AC_ARG_WITH([vio],
-              [  --with-vio              Include the Virtual IO support],
-              [vio="$withval"],
-              [vio=no])
-
-  if test "$vio" = "yes"
-  then
-    vio_dir="vio"
-    vio_libs="../vio/libvio.la"
-    AC_DEFINE(HAVE_VIO, 1)
-  else
-    vio_dir=""
-    vio_libs=""
-  fi
-  AC_SUBST([vio_dir])
-  AC_SUBST([vio_libs])
+dnl
+dnl we always use vio: no need for special defines 
+dnl
+  AC_DEFINE([HAVE_VIO_READ_BUFF], [1],
+            [Define to enable buffered read. This works only if syscalls
+            read/recv return as soon as there is some data in the kernel
+            buffer, no matter how big the given buffer is.])
 ])
 
 # Local version of _AC_PROG_CXX_EXIT_DECLARATION that does not
diff --git a/config/ac-macros/openssl.m4 b/config/ac-macros/openssl.m4
index 9da0e38f2ba..6541a492247 100644
--- a/config/ac-macros/openssl.m4
+++ b/config/ac-macros/openssl.m4
@@ -89,9 +89,6 @@ AC_MSG_CHECKING(for OpenSSL)
 	fi
     MYSQL_FIND_OPENSSL([$openssl_includes], [$openssl_libs])
     #force VIO use
-    vio_dir="vio"
-    vio_libs="../vio/libvio.la"
-    AC_DEFINE([HAVE_VIO], [1], [Virtual IO])
     AC_MSG_RESULT(yes)
     openssl_libs="-L$OPENSSL_LIB -lssl -lcrypto"
     # Don't set openssl_includes to /usr/include as this gives us a lot of
diff --git a/include/config-win.h b/include/config-win.h
index e86e0f08596..4ef5c9323e7 100644
--- a/include/config-win.h
+++ b/include/config-win.h
@@ -308,7 +308,7 @@ inline double ulonglong2double(ulonglong value)
 #define HAVE_QUERY_CACHE
 #define SPRINTF_RETURNS_INT
 #define HAVE_SETFILEPOINTER
-#define HAVE_VIO
+#define HAVE_VIO_READ_BUFF
 
 #ifdef NOT_USED
 #define HAVE_SNPRINTF		/* Gave link error */
diff --git a/include/my_global.h b/include/my_global.h
index 30b98b9fbeb..e9470ac48aa 100644
--- a/include/my_global.h
+++ b/include/my_global.h
@@ -523,6 +523,9 @@ typedef SOCKET_SIZE_TYPE size_socket;
 #ifndef O_SHORT_LIVED
 #define O_SHORT_LIVED	0
 #endif
+#ifndef O_NOFOLLOW
+#define O_NOFOLLOW      0
+#endif
 
 /* #define USE_RECORD_LOCK	*/
 
diff --git a/include/my_sys.h b/include/my_sys.h
index ce785b58da4..ddac316b5c6 100644
--- a/include/my_sys.h
+++ b/include/my_sys.h
@@ -279,6 +279,7 @@ enum loglevel {
 enum cache_type
 {
   READ_CACHE,WRITE_CACHE,
+  APPEND_CACHE,                 /* Like WRITE_CACHE, but only append */
   SEQ_READ_APPEND		/* sequential read or append */,
   READ_FIFO, READ_NET,WRITE_NET};
 
diff --git a/include/violite.h b/include/violite.h
index 855d8a3d490..a62fe37f45d 100644
--- a/include/violite.h
+++ b/include/violite.h
@@ -37,7 +37,12 @@ enum enum_vio_type
   VIO_TYPE_SSL, VIO_TYPE_SHARED_MEMORY
 };
 
-Vio*	vio_new(my_socket sd, enum enum_vio_type type, my_bool localhost);
+
+#define VIO_LOCALHOST 1                         /* a localhost connection */
+#define VIO_BUFFERED_READ 2                     /* use buffered read */
+#define VIO_READ_BUFFER_SIZE 16384              /* size of read buffer */
+
+Vio*	vio_new(my_socket sd, enum enum_vio_type type, uint flags);
 #ifdef __WIN__
 Vio* vio_new_win32pipe(HANDLE hPipe);
 Vio* vio_new_win32shared_memory(NET *net,HANDLE handle_file_map,
@@ -57,8 +62,9 @@ int vio_close_pipe(Vio * vio);
 void	vio_delete(Vio* vio);
 int	vio_close(Vio* vio);
 void    vio_reset(Vio* vio, enum enum_vio_type type,
-                  my_socket sd, HANDLE hPipe, my_bool localhost);
+                  my_socket sd, HANDLE hPipe, uint flags);
 int	vio_read(Vio *vio, gptr	buf, int size);
+int     vio_read_buff(Vio *vio, gptr buf, int size);
 int	vio_write(Vio *vio, const gptr buf, int size);
 int	vio_blocking(Vio *vio, my_bool onoff, my_bool *old_mode);
 my_bool	vio_is_blocking(Vio *vio);
@@ -135,7 +141,7 @@ int vio_close_shared_memory(Vio * vio);
 }
 #endif
 
-#if defined(HAVE_VIO) && !defined(DONT_MAP_VIO)
+#if !defined(DONT_MAP_VIO)
 #define vio_delete(vio) 			(vio)->viodelete(vio)
 #define vio_errno(vio)	 			(vio)->vioerrno(vio)
 #define vio_read(vio, buf, size) 		(vio)->read(vio,buf,size)
@@ -150,7 +156,7 @@ int vio_close_shared_memory(Vio * vio);
 #define vio_peer_addr(vio, buf, prt)		(vio)->peer_addr(vio, buf, prt)
 #define vio_in_addr(vio, in)			(vio)->in_addr(vio, in)
 #define vio_timeout(vio, seconds)			(vio)->timeout(vio, seconds)
-#endif /* defined(HAVE_VIO) && !defined(DONT_MAP_VIO) */
+#endif /* !defined(DONT_MAP_VIO) */
 
 /* This enumerator is used in parser - should be always visible */
 enum SSL_type
@@ -175,7 +181,10 @@ struct st_vio
   struct sockaddr_in	remote;		/* Remote internet address */
   enum enum_vio_type	type;		/* Type of connection */
   char			desc[30];	/* String description */
-#ifdef HAVE_VIO
+  char                  *read_buffer;   /* buffer for vio_read_buff */
+  char                  *read_pos;      /* start of unfetched data in the
+                                           read buffer */
+  char                  *read_end;      /* end of unfetched data */
   /* function pointers. They are similar for socket/SSL/whatever */
   void    (*viodelete)(Vio*);
   int     (*vioerrno)(Vio*);
@@ -203,6 +212,5 @@ struct st_vio
   char    *shared_memory_pos;
   NET     *net;
 #endif /* HAVE_SMEM */
-#endif /* HAVE_VIO */
 };
 #endif /* vio_violite_h_ */
diff --git a/innobase/trx/trx0trx.c b/innobase/trx/trx0trx.c
index 344fe280711..dd4da33abb5 100644
--- a/innobase/trx/trx0trx.c
+++ b/innobase/trx/trx0trx.c
@@ -262,6 +262,20 @@ trx_free(
 		putc('\n', stderr);
 	}
 
+	if (trx->n_mysql_tables_in_use != 0
+	    || trx->mysql_n_tables_locked != 0) {
+
+		ut_print_timestamp(stderr);
+		fprintf(stderr,
+"  InnoDB: Error: MySQL is freeing a thd\n"
+"InnoDB: though trx->n_mysql_tables_in_use is %lu\n"
+"InnoDB: and trx->mysql_n_tables_locked is %lu.\n",
+			(ulong)trx->n_mysql_tables_in_use,
+			(ulong)trx->mysql_n_tables_locked);
+
+		trx_print(stderr, trx);		
+	}
+
 	ut_a(trx->magic_n == TRX_MAGIC_N);
 
 	trx->magic_n = 11112222;
@@ -272,9 +286,6 @@ trx_free(
 
 	ut_a(trx->insert_undo == NULL); 
 	ut_a(trx->update_undo == NULL); 
-
-	ut_a(trx->n_mysql_tables_in_use == 0);
-	ut_a(trx->mysql_n_tables_locked == 0);
 	
 	if (trx->undo_no_arr) {
 		trx_undo_arr_free(trx->undo_no_arr);
diff --git a/innobase/ut/ut0mem.c b/innobase/ut/ut0mem.c
index 9e026ed0011..3e8fd79a739 100644
--- a/innobase/ut/ut0mem.c
+++ b/innobase/ut/ut0mem.c
@@ -14,6 +14,7 @@ Created 5/11/1994 Heikki Tuuri
 
 #include "mem0mem.h"
 #include "os0sync.h"
+#include "os0thread.h"
 
 /* This struct is placed first in every allocated memory block */
 typedef struct ut_mem_block_struct ut_mem_block_t;
@@ -66,6 +67,7 @@ ut_malloc_low(
 	ibool	assert_on_error) /* in: if TRUE, we crash mysqld if the memory
 				cannot be allocated */
 {
+	ulint	retry_count	= 0;
 	void*	ret;
 
 	ut_ad((sizeof(ut_mem_block_t) % 8) == 0); /* check alignment ok */
@@ -73,24 +75,26 @@ ut_malloc_low(
 	if (!ut_mem_block_list_inited) {
 	        ut_mem_block_list_init();
 	}
-
+retry:
 	os_fast_mutex_lock(&ut_list_mutex);
 
 	ret = malloc(n + sizeof(ut_mem_block_t));
 
-	if (ret == NULL) {
-		ut_print_timestamp(stderr);
-		fprintf(stderr,
-		"  InnoDB: Fatal error: cannot allocate %lu bytes of\n"
+	if (ret == NULL && retry_count < 60) {
+		if (retry_count == 0) {
+			ut_print_timestamp(stderr);
+
+			fprintf(stderr,
+		"  InnoDB: Error: cannot allocate %lu bytes of\n"
 		"InnoDB: memory with malloc! Total allocated memory\n"
 		"InnoDB: by InnoDB %lu bytes. Operating system errno: %lu\n"
-		"InnoDB: Cannot continue operation!\n"
 		"InnoDB: Check if you should increase the swap file or\n"
 		"InnoDB: ulimits of your operating system.\n"
 		"InnoDB: On FreeBSD check you have compiled the OS with\n"
 		"InnoDB: a big enough maximum process size.\n"
 		"InnoDB: Note that in most 32-bit computers the process\n"
-		"InnoDB: memory space is limited to 2 GB or 4 GB.\n",
+		"InnoDB: memory space is limited to 2 GB or 4 GB.\n"
+		"InnoDB: We keep retrying the allocation for 60 seconds...\n",
 		                  (ulong) n, (ulong) ut_total_allocated_memory,
 #ifdef __WIN__
 			(ulong) GetLastError()
@@ -98,7 +102,21 @@ ut_malloc_low(
 			(ulong) errno
 #endif
 			);
+		}
+
+		os_fast_mutex_unlock(&ut_list_mutex);
 
+		/* Sleep for a second and retry the allocation; maybe this is
+		just a temporary shortage of memory */
+
+		os_thread_sleep(1000000);
+		
+		retry_count++;
+
+		goto retry;
+	}
+
+	if (ret == NULL) {
 		/* Flush stderr to make more probable that the error
 		message gets in the error file before we generate a seg
 		fault */
@@ -113,8 +131,10 @@ ut_malloc_low(
 		by graceful exit handling in ut_a(). */
 #if (!defined __NETWARE__) 
 		if (assert_on_error) {
+			ut_print_timestamp(stderr);
+
 			fprintf(stderr,
-		"InnoDB: We now intentionally generate a seg fault so that\n"
+		"  InnoDB: We now intentionally generate a seg fault so that\n"
 		"InnoDB: on Linux we get a stack trace.\n");
 
 			if (*ut_mem_null_ptr) ut_mem_null_ptr = 0;
diff --git a/myisam/ft_boolean_search.c b/myisam/ft_boolean_search.c
index 6236f837c42..530f0d56c4c 100644
--- a/myisam/ft_boolean_search.c
+++ b/myisam/ft_boolean_search.c
@@ -68,7 +68,7 @@ struct st_ftb_expr
   my_off_t  docid[2];
   float     weight;
   float     cur_weight;
-  byte     *quot, *qend;
+  LIST     *phrase;               /* phrase words */
   uint      yesses;               /* number of "yes" words matched */
   uint      nos;                  /* number of "no"  words matched */
   uint      ythresh;              /* number of "yes" words in expr */
@@ -132,20 +132,22 @@ static int FTB_WORD_cmp_list(CHARSET_INFO *cs, FTB_WORD **a, FTB_WORD **b)
 }
 
 static void _ftb_parse_query(FTB *ftb, byte **start, byte *end,
-                      FTB_EXPR *up, uint depth)
+                      FTB_EXPR *up, uint depth, byte *up_quot)
 {
   byte        res;
   FTB_PARAM   param;
   FT_WORD     w;
   FTB_WORD   *ftbw;
   FTB_EXPR   *ftbe;
+  FT_WORD    *phrase_word;
+  LIST       *phrase_list;
   uint  extra=HA_FT_WLEN+ftb->info->s->rec_reflength; /* just a shortcut */
 
   if (ftb->state != UNINITIALIZED)
     return;
 
   param.prev=' ';
-  param.quot=up->quot;
+  param.quot= up_quot;
   while ((res=ft_get_word(ftb->charset,start,end,&w,&param)))
   {
     int   r=param.plusminus;
@@ -172,6 +174,14 @@ static void _ftb_parse_query(FTB *ftb, byte **start, byte *end,
         if (param.yesno > 0) up->ythresh++;
         queue_insert(& ftb->queue, (byte *)ftbw);
         ftb->with_scan|=(param.trunc & FTB_FLAG_TRUNC);
+      case 4: /* not indexed word (stopword or too short/long) */
+        if (! up_quot) break;
+        phrase_word= (FT_WORD *)alloc_root(&ftb->mem_root, sizeof(FT_WORD));
+        phrase_list= (LIST *)alloc_root(&ftb->mem_root, sizeof(LIST));
+        phrase_word->pos= w.pos;
+        phrase_word->len= w.len;
+        phrase_list->data= (void *)phrase_word;
+        up->phrase= list_add(up->phrase, phrase_list);
         break;
       case 2: /* left bracket */
         ftbe=(FTB_EXPR *)alloc_root(&ftb->mem_root, sizeof(FTB_EXPR));
@@ -182,13 +192,14 @@ static void _ftb_parse_query(FTB *ftb, byte **start, byte *end,
         ftbe->up=up;
         ftbe->ythresh=ftbe->yweaks=0;
         ftbe->docid[0]=ftbe->docid[1]=HA_OFFSET_ERROR;
-        if ((ftbe->quot=param.quot)) ftb->with_scan|=2;
+        ftbe->phrase= NULL;
+        if (param.quot) ftb->with_scan|=2;
         if (param.yesno > 0) up->ythresh++;
-        _ftb_parse_query(ftb, start, end, ftbe, depth+1);
+        _ftb_parse_query(ftb, start, end, ftbe, depth+1, param.quot);
         param.quot=0;
         break;
       case 3: /* right bracket */
-        if (up->quot) up->qend=param.quot;
+        if (up_quot) up->phrase= list_reverse(up->phrase);
         return;
     }
   }
@@ -410,12 +421,12 @@ FT_INFO * ft_init_boolean_search(MI_INFO *info, uint keynr, byte *query,
   ftbe->weight=1;
   ftbe->flags=FTB_FLAG_YES;
   ftbe->nos=1;
-  ftbe->quot=0;
   ftbe->up=0;
   ftbe->ythresh=ftbe->yweaks=0;
   ftbe->docid[0]=ftbe->docid[1]=HA_OFFSET_ERROR;
+  ftbe->phrase= NULL;
   ftb->root=ftbe;
-  _ftb_parse_query(ftb, &query, query+query_len, ftbe, 0);
+  _ftb_parse_query(ftb, &query, query+query_len, ftbe, 0, NULL);
   ftb->list=(FTB_WORD **)alloc_root(&ftb->mem_root,
                                      sizeof(FTB_WORD *)*ftb->queue.elements);
   memcpy(ftb->list, ftb->queue.root+1, sizeof(FTB_WORD *)*ftb->queue.elements);
@@ -431,29 +442,45 @@ err:
 }
 
 
-/* returns 1 if str0 ~= /\bstr1\b/ */
-static int _ftb_strstr(const byte *s0, const byte *e0,
-                const byte *s1, const byte *e1,
-                CHARSET_INFO *cs)
+/*
+  Checks if given buffer matches phrase list.
+
+  SYNOPSIS
+    _ftb_check_phrase()
+    s0     start of buffer
+    e0     end of buffer
+    phrase broken into list phrase
+    cs     charset info
+
+  RETURN VALUE
+    1 is returned if phrase found, 0 else.
+*/
+
+static int _ftb_check_phrase(const byte *s0, const byte *e0,
+                LIST *phrase, CHARSET_INFO *cs)
 {
-  const byte *p0= s0;
-  my_bool s_after= true_word_char(cs, s1[0]);
-  my_bool e_before= true_word_char(cs, e1[-1]);
-  uint p0_len;
-  my_match_t m[2];
+  FT_WORD h_word;
+  const byte *h_start= s0;
+  DBUG_ENTER("_ftb_strstr");
+  DBUG_ASSERT(phrase);
 
-  while (p0 < e0)
+  while (ft_simple_get_word(cs, (byte **)&h_start, e0, &h_word, FALSE))
   {
-    if (cs->coll->instr(cs, p0, e0 - p0, s1, e1 - s1, m, 2) != 2)
-      return(0);
-    if ((!s_after || p0 + m[1].beg == s0 || !true_word_char(cs, p0[m[1].beg-1])) &&
-        (!e_before || p0 + m[1].end == e0 || !true_word_char(cs, p0[m[1].end])))
-      return(1);
-    p0+= m[1].beg;
-    p0+= (p0_len= my_mbcharlen(cs, *(uchar *)p0)) ? p0_len : 1;
+    FT_WORD *n_word;
+    LIST *phrase_element= phrase;
+    const byte *h_start1= h_start;
+    for (;;)
+    {
+      n_word= (FT_WORD *)phrase_element->data;
+      if (my_strnncoll(cs, h_word.pos, h_word.len, n_word->pos, n_word->len))
+        break;
+      if (! (phrase_element= phrase_element->next))
+        DBUG_RETURN(1);
+      if (! ft_simple_get_word(cs, (byte **)&h_start1, e0, &h_word, FALSE))
+        DBUG_RETURN(0);
+    }
   }
-
-  return(0);
+  DBUG_RETURN(0);
 }
 
 
@@ -484,7 +511,7 @@ static void _ftb_climb_the_tree(FTB *ftb, FTB_WORD *ftbw, FT_SEG_ITERATOR *ftsi_
       {
         yn=ftbe->flags;
         weight=ftbe->cur_weight*ftbe->weight;
-        if (mode && ftbe->quot)
+        if (mode && ftbe->phrase)
         {
           int not_found=1;
 
@@ -493,8 +520,8 @@ static void _ftb_climb_the_tree(FTB *ftb, FTB_WORD *ftbw, FT_SEG_ITERATOR *ftsi_
           {
             if (!ftsi.pos)
               continue;
-            not_found = ! _ftb_strstr(ftsi.pos, ftsi.pos+ftsi.len,
-                                      ftbe->quot, ftbe->qend, ftb->charset);
+            not_found = ! _ftb_check_phrase(ftsi.pos, ftsi.pos+ftsi.len,
+                                      ftbe->phrase, ftb->charset);
           }
           if (not_found) break;
         } /* ftbe->quot */
@@ -642,8 +669,8 @@ float ft_boolean_find_relevance(FT_INFO *ftb, byte *record, uint length)
       continue;
 
     end=ftsi.pos+ftsi.len;
-    while (ft_simple_get_word(ftb->charset,
-                              (byte **) &ftsi.pos, (byte *) end, &word))
+    while (ft_simple_get_word(ftb->charset, (byte **) &ftsi.pos,
+                              (byte *) end, &word, TRUE))
     {
       int a, b, c;
       for (a=0, b=ftb->queue.elements, c=(a+b)/2; b-a>1; c=(a+b)/2)
diff --git a/myisam/ft_parser.c b/myisam/ft_parser.c
index 543cf998a82..3184cc6644c 100644
--- a/myisam/ft_parser.c
+++ b/myisam/ft_parser.c
@@ -93,12 +93,14 @@ my_bool ft_boolean_check_syntax_string(const byte *str)
   return 0;
 }
 
-/* returns:
- * 0 - eof
- * 1 - word found
- * 2 - left bracket
- * 3 - right bracket
- */
+/*
+  RETURN VALUE
+  0 - eof
+  1 - word found
+  2 - left bracket
+  3 - right bracket
+  4 - stopword found
+*/
 byte ft_get_word(CHARSET_INFO *cs, byte **start, byte *end,
                  FT_WORD *word, FTB_PARAM *param)
 {
@@ -161,6 +163,11 @@ byte ft_get_word(CHARSET_INFO *cs, byte **start, byte *end,
       *start=doc;
       return 1;
     }
+    else if (length) /* make sure length > 0 (if start contains spaces only) */
+    {
+      *start= doc;
+      return 4;
+    }
   }
   if (param->quot)
   {
@@ -170,18 +177,19 @@ byte ft_get_word(CHARSET_INFO *cs, byte **start, byte *end,
   return 0;
 }
 
-byte ft_simple_get_word(CHARSET_INFO *cs, byte **start, byte *end,
-                        FT_WORD *word)
+byte ft_simple_get_word(CHARSET_INFO *cs, byte **start, const byte *end,
+                        FT_WORD *word, my_bool skip_stopwords)
 {
   byte *doc= *start;
   uint mwc, length, mbl;
   DBUG_ENTER("ft_simple_get_word");
 
-  while (doc<end)
+  do
   {
-    for (;doc<end;doc++)
+    for (;; doc++)
     {
-      if (true_word_char(cs,*doc)) break;
+      if (doc >= end) DBUG_RETURN(0);
+      if (true_word_char(cs, *doc)) break;
     }
 
     mwc= length= 0;
@@ -193,13 +201,14 @@ byte ft_simple_get_word(CHARSET_INFO *cs, byte **start, byte *end,
 
     word->len= (uint)(doc-word->pos) - mwc;
 
-    if (length >= ft_min_word_len && length < ft_max_word_len &&
-        !is_stopword(word->pos, word->len))
+    if (skip_stopwords == FALSE ||
+        (length >= ft_min_word_len && length < ft_max_word_len &&
+         !is_stopword(word->pos, word->len)))
     {
       *start= doc;
       DBUG_RETURN(1);
     }
-  }
+  } while (doc < end);
   DBUG_RETURN(0);
 }
 
@@ -217,7 +226,7 @@ int ft_parse(TREE *wtree, byte *doc, int doclen, my_bool with_alloc)
   FT_WORD w;
   DBUG_ENTER("ft_parse");
 
-  while (ft_simple_get_word(wtree->custom_arg, &doc,end,&w))
+  while (ft_simple_get_word(wtree->custom_arg, &doc, end, &w, TRUE))
   {
     if (with_alloc)
     {
diff --git a/myisam/ft_stopwords.c b/myisam/ft_stopwords.c
index a4bce6ad4e8..ab51afb0e82 100644
--- a/myisam/ft_stopwords.c
+++ b/myisam/ft_stopwords.c
@@ -81,7 +81,7 @@ int ft_init_stopwords()
       goto err0;
     len=my_read(fd, buffer, len, MYF(MY_WME));
     end=start+len;
-    while (ft_simple_get_word(default_charset_info, &start, end, &w))
+    while (ft_simple_get_word(default_charset_info, &start, end, &w, TRUE))
     {
       if (ft_add_stopword(my_strdup_with_length(w.pos, w.len, MYF(0))))
         goto err1;
diff --git a/myisam/ftdefs.h b/myisam/ftdefs.h
index ddb9fbfead2..91c679a1e58 100644
--- a/myisam/ftdefs.h
+++ b/myisam/ftdefs.h
@@ -112,7 +112,8 @@ int is_stopword(char *word, uint len);
 uint _ft_make_key(MI_INFO *, uint , byte *, FT_WORD *, my_off_t);
 
 byte ft_get_word(CHARSET_INFO *, byte **, byte *, FT_WORD *, FTB_PARAM *);
-byte ft_simple_get_word(CHARSET_INFO *, byte **, byte *, FT_WORD *);
+byte ft_simple_get_word(CHARSET_INFO *, byte **, const byte *,
+                        FT_WORD *, my_bool);
 
 typedef struct _st_ft_seg_iterator {
   uint        num, len;
diff --git a/myisam/mi_create.c b/myisam/mi_create.c
index 3e144cfcbfb..8635d6bcf36 100644
--- a/myisam/mi_create.c
+++ b/myisam/mi_create.c
@@ -39,7 +39,7 @@ int mi_create(const char *name,uint keys,MI_KEYDEF *keydefs,
 {
   register uint i,j;
   File dfile,file;
-  int errpos,save_errno;
+  int errpos,save_errno, create_mode= O_RDWR | O_TRUNC;
   myf create_flag;
   uint fields,length,max_key_length,packed,pointer,real_length_diff,
        key_length,info_length,key_segs,options,min_key_length_skip,
@@ -173,7 +173,10 @@ int mi_create(const char *name,uint keys,MI_KEYDEF *keydefs,
   if (!(options & (HA_OPTION_PACK_RECORD | HA_OPTION_COMPRESS_RECORD)))
     min_pack_length+= varchar_length;
   if (flags & HA_CREATE_TMP_TABLE)
+  {
     options|= HA_OPTION_TMP_TABLE;
+    create_mode|= O_EXCL | O_NOFOLLOW;
+  }
   if (flags & HA_CREATE_CHECKSUM || (options & HA_OPTION_CHECKSUM))
   {
     options|= HA_OPTION_CHECKSUM;
@@ -573,9 +576,7 @@ int mi_create(const char *name,uint keys,MI_KEYDEF *keydefs,
     goto err;
   }
 
-  if ((file= my_create_with_symlink(linkname_ptr,
-				    filename,
-				    0, O_RDWR | O_TRUNC,
+  if ((file= my_create_with_symlink(linkname_ptr, filename, 0, create_mode,
 				    MYF(MY_WME | create_flag))) < 0)
     goto err;
   errpos=1;
@@ -586,7 +587,7 @@ int mi_create(const char *name,uint keys,MI_KEYDEF *keydefs,
     if (share.base.raid_type)
     {
       (void) fn_format(filename,name,"",MI_NAME_DEXT,2+4);
-      if ((dfile=my_raid_create(filename,0,O_RDWR | O_TRUNC,
+      if ((dfile=my_raid_create(filename, 0, create_mode,
 				share.base.raid_type,
 				share.base.raid_chunks,
 				share.base.raid_chunksize,
@@ -610,8 +611,7 @@ int mi_create(const char *name,uint keys,MI_KEYDEF *keydefs,
 	create_flag=MY_DELETE_OLD;
       }
       if ((dfile=
-	   my_create_with_symlink(linkname_ptr, filename,
-				  0,O_RDWR | O_TRUNC,
+	   my_create_with_symlink(linkname_ptr, filename, 0, create_mode,
 				  MYF(MY_WME | create_flag))) < 0)
 	goto err;
     }
diff --git a/myisammrg/myrg_create.c b/myisammrg/myrg_create.c
index 5fc3c60ff32..7ddb7ecb3b9 100644
--- a/myisammrg/myrg_create.c
+++ b/myisammrg/myrg_create.c
@@ -34,7 +34,7 @@ int myrg_create(const char *name, const char **table_names,
 
   errpos=0;
   if ((file = my_create(fn_format(buff,name,"",MYRG_NAME_EXT,4),0,
-       O_RDWR | O_TRUNC,MYF(MY_WME))) < 0)
+       O_RDWR | O_EXCL | O_NOFOLLOW,MYF(MY_WME))) < 0)
     goto err;
   errpos=1;
   if (table_names)
diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result
index 57821699f68..b015648534b 100644
--- a/mysql-test/r/cast.result
+++ b/mysql-test/r/cast.result
@@ -203,3 +203,9 @@ cast(@v1 as decimal(22, 2))
 select cast(-1e18 as decimal(22,2));
 cast(-1e18 as decimal(22,2))
 -1000000000000000000.00
+create table t1(s1 time);
+insert into t1 values ('11:11:11');
+select cast(s1 as decimal(7,2)) from t1;
+cast(s1 as decimal(7,2))
+111111.00
+drop table t1;
diff --git a/mysql-test/r/ctype_collate.result b/mysql-test/r/ctype_collate.result
index 201e1c6de08..e3130888a05 100644
--- a/mysql-test/r/ctype_collate.result
+++ b/mysql-test/r/ctype_collate.result
@@ -514,7 +514,7 @@ Variable_name	Value
 character_set_client	latin1
 SELECT charset('a'),collation('a'),coercibility('a'),'a'='A';
 charset('a')	collation('a')	coercibility('a')	'a'='A'
-latin1	latin1_swedish_ci	3	1
+latin1	latin1_swedish_ci	4	1
 explain extended SELECT charset('a'),collation('a'),coercibility('a'),'a'='A';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
@@ -525,7 +525,7 @@ SHOW VARIABLES LIKE 'collation_client';
 Variable_name	Value
 SELECT charset('a'),collation('a'),coercibility('a'),'a'='A';
 charset('a')	collation('a')	coercibility('a')	'a'='A'
-latin1	latin1_swedish_ci	3	1
+latin1	latin1_swedish_ci	4	1
 SET CHARACTER SET 'DEFAULT';
 ERROR 42000: Unknown character set: 'DEFAULT'
 DROP TABLE t1;
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index ea299455364..46dba6cdfa9 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -832,3 +832,52 @@ id	stddev_pop(value1)	var_pop(value1)	stddev_samp(value1)	var_samp(value1)
 1	0.816497	0.666667	1.000000	1.000000
 2	1.118034	1.250000	1.290994	1.666667
 DROP TABLE t1;
+CREATE TABLE t1 (col1 decimal(16,12));
+INSERT INTO t1 VALUES (-5.00000000001),(-5.00000000002),(-5.00000000003),(-5.00000000000),(-5.00000000001),(-5.00000000002);
+insert into t1 select * from t1;
+select col1,count(col1),sum(col1),avg(col1) from t1 group by col1;
+col1	count(col1)	sum(col1)	avg(col1)
+-5.000000000030	2	-10.000000000060	-5.0000000000300000
+-5.000000000020	4	-20.000000000080	-5.0000000000200000
+-5.000000000010	4	-20.000000000040	-5.0000000000100000
+-5.000000000000	2	-10.000000000000	-5.0000000000000000
+DROP TABLE t1;
+create table t1 (col1 decimal(16,12));
+insert into t1 values (-5.00000000001);
+insert into t1 values (-5.00000000001);
+select col1,sum(col1),max(col1),min(col1) from t1 group by col1;
+col1	sum(col1)	max(col1)	min(col1)
+-5.000000000010	-10.000000000020	-5.000000000010	-5.000000000010
+delete from t1;
+insert into t1 values (5.00000000001);
+insert into t1 values (5.00000000001);
+select col1,sum(col1),max(col1),min(col1) from t1 group by col1;
+col1	sum(col1)	max(col1)	min(col1)
+5.000000000010	10.000000000020	5.000000000010	5.000000000010
+DROP TABLE t1;
+CREATE TABLE t1(
+id int PRIMARY KEY,
+a  int,
+b  int,
+INDEX i_b_id(a,b,id),
+INDEX i_id(a,id)
+);
+INSERT INTO t1 VALUES 
+(1,1,4), (2,2,1), (3,1,3), (4,2,1), (5,1,1);
+SELECT MAX(id) FROM t1 WHERE id < 3 AND a=2 AND b=6;
+MAX(id)
+NULL
+DROP TABLE t1;
+CREATE TABLE t1(
+id int PRIMARY KEY,
+a  int,
+b  int,
+INDEX i_id(a,id),
+INDEX i_b_id(a,b,id)
+);
+INSERT INTO t1 VALUES 
+(1,1,4), (2,2,1), (3,1,3), (4,2,1), (5,1,1);
+SELECT MAX(id) FROM t1 WHERE id < 3 AND a=2 AND b=6;
+MAX(id)
+NULL
+DROP TABLE t1;
diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result
index 85a0c2c55f9..9b93fe2ad1c 100644
--- a/mysql-test/r/func_str.result
+++ b/mysql-test/r/func_str.result
@@ -467,97 +467,97 @@ select _latin1'B' COLLATE latin1_general_ci in (_latin1'a',_latin1'b' COLLATE la
 ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT), (latin1_swedish_ci,COERCIBLE), (latin1_bin,EXPLICIT) for operation ' IN '
 select collation(bin(130)), coercibility(bin(130));
 collation(bin(130))	coercibility(bin(130))
-latin1_swedish_ci	3
+latin1_swedish_ci	4
 select collation(oct(130)), coercibility(oct(130));
 collation(oct(130))	coercibility(oct(130))
-latin1_swedish_ci	3
+latin1_swedish_ci	4
 select collation(conv(130,16,10)), coercibility(conv(130,16,10));
 collation(conv(130,16,10))	coercibility(conv(130,16,10))
-latin1_swedish_ci	3
+latin1_swedish_ci	4
 select collation(hex(130)), coercibility(hex(130));
 collation(hex(130))	coercibility(hex(130))
-latin1_swedish_ci	3
+latin1_swedish_ci	4
 select collation(char(130)), coercibility(hex(130));
 collation(char(130))	coercibility(hex(130))
-latin1_swedish_ci	3
+latin1_swedish_ci	4
 select collation(format(130,10)), coercibility(format(130,10));
 collation(format(130,10))	coercibility(format(130,10))
-latin1_swedish_ci	3
+latin1_swedish_ci	4
 select collation(lcase(_latin2'a')), coercibility(lcase(_latin2'a'));
 collation(lcase(_latin2'a'))	coercibility(lcase(_latin2'a'))
-latin2_general_ci	3
+latin2_general_ci	4
 select collation(ucase(_latin2'a')), coercibility(ucase(_latin2'a'));
 collation(ucase(_latin2'a'))	coercibility(ucase(_latin2'a'))
-latin2_general_ci	3
+latin2_general_ci	4
 select collation(left(_latin2'a',1)), coercibility(left(_latin2'a',1));
 collation(left(_latin2'a',1))	coercibility(left(_latin2'a',1))
-latin2_general_ci	3
+latin2_general_ci	4
 select collation(right(_latin2'a',1)), coercibility(right(_latin2'a',1));
 collation(right(_latin2'a',1))	coercibility(right(_latin2'a',1))
-latin2_general_ci	3
+latin2_general_ci	4
 select collation(substring(_latin2'a',1,1)), coercibility(substring(_latin2'a',1,1));
 collation(substring(_latin2'a',1,1))	coercibility(substring(_latin2'a',1,1))
-latin2_general_ci	3
+latin2_general_ci	4
 select collation(concat(_latin2'a',_latin2'b')), coercibility(concat(_latin2'a',_latin2'b'));
 collation(concat(_latin2'a',_latin2'b'))	coercibility(concat(_latin2'a',_latin2'b'))
-latin2_general_ci	3
+latin2_general_ci	4
 select collation(lpad(_latin2'a',4,_latin2'b')), coercibility(lpad(_latin2'a',4,_latin2'b'));
 collation(lpad(_latin2'a',4,_latin2'b'))	coercibility(lpad(_latin2'a',4,_latin2'b'))
-latin2_general_ci	3
+latin2_general_ci	4
 select collation(rpad(_latin2'a',4,_latin2'b')), coercibility(rpad(_latin2'a',4,_latin2'b'));
 collation(rpad(_latin2'a',4,_latin2'b'))	coercibility(rpad(_latin2'a',4,_latin2'b'))
-latin2_general_ci	3
+latin2_general_ci	4
 select collation(concat_ws(_latin2'a',_latin2'b')), coercibility(concat_ws(_latin2'a',_latin2'b'));
 collation(concat_ws(_latin2'a',_latin2'b'))	coercibility(concat_ws(_latin2'a',_latin2'b'))
-latin2_general_ci	3
+latin2_general_ci	4
 select collation(make_set(255,_latin2'a',_latin2'b',_latin2'c')), coercibility(make_set(255,_latin2'a',_latin2'b',_latin2'c'));
 collation(make_set(255,_latin2'a',_latin2'b',_latin2'c'))	coercibility(make_set(255,_latin2'a',_latin2'b',_latin2'c'))
-latin2_general_ci	3
+latin2_general_ci	4
 select collation(export_set(255,_latin2'y',_latin2'n',_latin2' ')), coercibility(export_set(255,_latin2'y',_latin2'n',_latin2' '));
 collation(export_set(255,_latin2'y',_latin2'n',_latin2' '))	coercibility(export_set(255,_latin2'y',_latin2'n',_latin2' '))
-binary	3
+binary	4
 select collation(trim(_latin2' a ')), coercibility(trim(_latin2' a '));
 collation(trim(_latin2' a '))	coercibility(trim(_latin2' a '))
-latin2_general_ci	3
+latin2_general_ci	4
 select collation(ltrim(_latin2' a ')), coercibility(ltrim(_latin2' a '));
 collation(ltrim(_latin2' a '))	coercibility(ltrim(_latin2' a '))
-latin2_general_ci	3
+latin2_general_ci	4
 select collation(rtrim(_latin2' a ')), coercibility(rtrim(_latin2' a '));
 collation(rtrim(_latin2' a '))	coercibility(rtrim(_latin2' a '))
-latin2_general_ci	3
+latin2_general_ci	4
 select collation(trim(LEADING _latin2' ' FROM _latin2'a')), coercibility(trim(LEADING _latin2'a' FROM _latin2'a'));
 collation(trim(LEADING _latin2' ' FROM _latin2'a'))	coercibility(trim(LEADING _latin2'a' FROM _latin2'a'))
-latin2_general_ci	3
+latin2_general_ci	4
 select collation(trim(TRAILING _latin2' ' FROM _latin2'a')), coercibility(trim(TRAILING _latin2'a' FROM _latin2'a'));
 collation(trim(TRAILING _latin2' ' FROM _latin2'a'))	coercibility(trim(TRAILING _latin2'a' FROM _latin2'a'))
-latin2_general_ci	3
+latin2_general_ci	4
 select collation(trim(BOTH _latin2' ' FROM _latin2'a')), coercibility(trim(BOTH _latin2'a' FROM _latin2'a'));
 collation(trim(BOTH _latin2' ' FROM _latin2'a'))	coercibility(trim(BOTH _latin2'a' FROM _latin2'a'))
-latin2_general_ci	3
+latin2_general_ci	4
 select collation(repeat(_latin2'a',10)), coercibility(repeat(_latin2'a',10));
 collation(repeat(_latin2'a',10))	coercibility(repeat(_latin2'a',10))
-latin2_general_ci	3
+latin2_general_ci	4
 select collation(reverse(_latin2'ab')), coercibility(reverse(_latin2'ab'));
 collation(reverse(_latin2'ab'))	coercibility(reverse(_latin2'ab'))
-latin2_general_ci	3
+latin2_general_ci	4
 select collation(quote(_latin2'ab')), coercibility(quote(_latin2'ab'));
 collation(quote(_latin2'ab'))	coercibility(quote(_latin2'ab'))
-latin2_general_ci	3
+latin2_general_ci	4
 select collation(soundex(_latin2'ab')), coercibility(soundex(_latin2'ab'));
 collation(soundex(_latin2'ab'))	coercibility(soundex(_latin2'ab'))
-latin2_general_ci	3
+latin2_general_ci	4
 select collation(substring(_latin2'ab',1)), coercibility(substring(_latin2'ab',1));
 collation(substring(_latin2'ab',1))	coercibility(substring(_latin2'ab',1))
-latin2_general_ci	3
+latin2_general_ci	4
 select collation(insert(_latin2'abcd',2,3,_latin2'ef')), coercibility(insert(_latin2'abcd',2,3,_latin2'ef'));
 collation(insert(_latin2'abcd',2,3,_latin2'ef'))	coercibility(insert(_latin2'abcd',2,3,_latin2'ef'))
-latin2_general_ci	3
+latin2_general_ci	4
 select collation(replace(_latin2'abcd',_latin2'b',_latin2'B')), coercibility(replace(_latin2'abcd',_latin2'b',_latin2'B'));
 collation(replace(_latin2'abcd',_latin2'b',_latin2'B'))	coercibility(replace(_latin2'abcd',_latin2'b',_latin2'B'))
-latin2_general_ci	3
+latin2_general_ci	4
 select collation(encode('abcd','ab')), coercibility(encode('abcd','ab'));
 collation(encode('abcd','ab'))	coercibility(encode('abcd','ab'))
-binary	3
+binary	4
 create table t1 
 select
 bin(130),
diff --git a/mysql-test/r/func_system.result b/mysql-test/r/func_system.result
index 0f827913780..9aa936f1c78 100644
--- a/mysql-test/r/func_system.result
+++ b/mysql-test/r/func_system.result
@@ -68,3 +68,11 @@ drop table t1;
 select TRUE,FALSE,NULL;
 TRUE	FALSE	NULL
 1	0	NULL
+create table t1 (a char(10)) character set latin1;
+select * from t1 where a=version();
+a
+select * from t1 where a=database();
+a
+select * from t1 where a=user();
+a
+drop table t1;
diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result
index 8e49a3df4c8..0e72de7ab12 100644
--- a/mysql-test/r/information_schema.result
+++ b/mysql-test/r/information_schema.result
@@ -1,7 +1,8 @@
 show variables where variable_name like "skip_show_database";
 Variable_name	Value
 skip_show_database	OFF
-grant all privileges on test.* to mysqltest_1@localhost;
+grant select, update, execute on test.* to mysqltest_2@localhost;
+grant select, update on test.* to mysqltest_1@localhost;
 select * from information_schema.SCHEMATA where schema_name > 'm';
 CATALOG_NAME	SCHEMA_NAME	DEFAULT_CHARACTER_SET_NAME	SQL_PATH
 NULL	mysql	latin1	NULL
@@ -229,6 +230,44 @@ sel2	sel2
 select count(*) from information_schema.ROUTINES;
 count(*)
 2
+select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES;
+ROUTINE_NAME	ROUTINE_DEFINITION
+show create function sub1;
+ERROR 42000: FUNCTION sub1 does not exist
+select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES;
+ROUTINE_NAME	ROUTINE_DEFINITION
+sel2	
+sub1	
+grant all privileges on test.* to mysqltest_1@localhost;
+select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES;
+ROUTINE_NAME	ROUTINE_DEFINITION
+sel2	
+sub1	
+create function sub2(i int) returns int
+return i+1;
+select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES;
+ROUTINE_NAME	ROUTINE_DEFINITION
+sel2	
+sub1	
+sub2	return i+1
+show create procedure sel2;
+Procedure	sql_mode	Create Procedure
+sel2		
+show create function sub1;
+Function	sql_mode	Create Function
+sub1		
+show create function sub2;
+Function	sql_mode	Create Function
+sub2		CREATE FUNCTION `test`.`sub2`(i int) RETURNS int
+return i+1
+drop function sub2;
+show create procedure sel2;
+Procedure	sql_mode	Create Procedure
+sel2		CREATE PROCEDURE `test`.`sel2`()
+begin
+select * from t1;
+select * from t2;
+end
 create view v0 (c) as select schema_name from information_schema.schemata;
 select * from v0;
 c
@@ -311,8 +350,8 @@ GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRAN
 'mysqltest_1'@'localhost'	NULL	test	t1	a	INSERT	NO
 'mysqltest_1'@'localhost'	NULL	test	t1	a	UPDATE	NO
 'mysqltest_1'@'localhost'	NULL	test	t1	a	REFERENCES	NO
-delete from mysql.user where user='mysqltest_1';
-delete from mysql.db where user='mysqltest_1';
+delete from mysql.user where user='mysqltest_1' or user='mysqltest_2';
+delete from mysql.db where user='mysqltest_1' or user='mysqltest_2';
 delete from mysql.tables_priv where user='mysqltest_1';
 delete from mysql.columns_priv where user='mysqltest_1';
 flush privileges;
diff --git a/mysql-test/r/lock.result b/mysql-test/r/lock.result
index db2842061b4..16c92fa201f 100644
--- a/mysql-test/r/lock.result
+++ b/mysql-test/r/lock.result
@@ -42,7 +42,7 @@ check table t2;
 Table	Op	Msg_type	Msg_text
 test.t2	check	error	Table 't2' was not locked with LOCK TABLES
 insert into t1 select index1,nr from t1;
-ERROR 42000: INSERT command denied to user 'root'@'localhost' for column 'index1' in table 't1'
+ERROR HY000: Table 't1' was not locked with LOCK TABLES
 unlock tables;
 lock tables t1 write, t1 as t1_alias read;
 insert into t1 select index1,nr from t1 as t1_alias;
diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result
index 17c0f32d666..ac7f182b4c4 100644
--- a/mysql-test/r/mysqldump.result
+++ b/mysql-test/r/mysqldump.result
@@ -1,5 +1,6 @@
 DROP TABLE IF EXISTS t1, `"t"1`, t1aa,t2aa;
 drop database if exists mysqldump_test_db;
+drop view if exists v1;
 CREATE TABLE t1(a int);
 INSERT INTO t1 VALUES (1), (2);
 <?xml version="1.0"?>
diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result
index 803cb89cecf..2ee912aaea7 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -237,6 +237,13 @@ insert into t2 values ("a", 1, 1.1), ("b", 2, 1.2), ("c", 3, 1.3)|
 drop procedure if exists sub1|
 create procedure sub1(id char(16), x int)
 insert into test.t1 values (id, x)|
+drop procedure if exists sub2|
+create procedure sub2(id char(16))
+begin
+declare x int;
+set x = (select sum(t.i) from test.t2 t);
+insert into test.t1 values (id, x);
+end|
 drop procedure if exists sub3|
 create function sub3(i int) returns int
 return i+1|
@@ -244,16 +251,19 @@ call sub1("sub1a", (select 7))|
 call sub1("sub1b", (select max(i) from t2))|
 call sub1("sub1c", (select i,d from t2 limit 1))|
 call sub1("sub1d", (select 1 from (select 1) a))|
+call sub2("sub2");
 select * from t1|
 id	data
 sub1a	7
 sub1b	3
 sub1c	1
 sub1d	1
+sub2	6
 select sub3((select max(i) from t2))|
 sub3((select max(i) from t2))
 4
 drop procedure sub1|
+drop procedure sub2|
 drop function sub3|
 delete from t2|
 drop procedure if exists a0|
@@ -269,6 +279,7 @@ sub1a	7
 sub1b	3
 sub1c	1
 sub1d	1
+sub2	6
 a0	2
 a0	1
 a0	0
@@ -1045,6 +1056,200 @@ select row_count()|
 row_count()
 -1
 drop procedure rc|
+drop function if exists f0|
+drop function if exists f1|
+drop function if exists f2|
+drop function if exists f3|
+drop function if exists f4|
+drop function if exists f5|
+drop function if exists f6|
+drop function if exists f7|
+drop function if exists f8|
+drop view if exists v0|
+drop view if exists v1|
+drop view if exists v2|
+delete from t1|
+delete from t2|
+insert into t1 values ("a", 1), ("b", 2) |
+insert into t2 values ("a", 1, 1.0), ("b", 2, 2.0), ("c", 3, 3.0) |
+create function f1() returns int
+return (select sum(data) from t1)|
+select f1()|
+f1()
+3
+select id, f1() from t1|
+id	f1()
+a	3
+b	3
+create function f2() returns int
+return (select data from t1 where data <= (select sum(data) from t1) limit 1)|
+select f2()|
+f2()
+1
+select id, f2() from t1|
+id	f2()
+a	1
+b	1
+create function f3() returns int
+begin
+declare n int;
+declare m int;
+set n:= (select min(data) from t1);
+set m:= (select max(data) from t1);
+return n < m;
+end|
+select f3()|
+f3()
+1
+select id, f3() from t1|
+id	f3()
+a	1
+b	1
+select f1(), f3()|
+f1()	f3()
+3	1
+select id, f1(), f3() from t1|
+id	f1()	f3()
+a	3	1
+b	3	1
+create function f4() returns double 
+return (select d from t1, t2 where t1.data = t2.i and t1.id= "b")|
+select f4()|
+f4()
+2
+select s, f4() from t2|
+s	f4()
+a	2
+b	2
+c	2
+create function f5(i int) returns int
+begin
+if i <= 0 then
+return 0;
+elseif i = 1  then
+return (select count(*) from t1 where data = i);
+else
+return (select count(*) + f5( i - 1) from t1 where data = i);
+end if;
+end|
+select f5(1)|
+f5(1)
+1
+select f5(2)|
+ERROR HY000: Table 't1' was not locked with LOCK TABLES
+create function f6() returns int
+begin
+declare n int;
+set n:= f1();
+return (select count(*) from t1 where data <= f7() and data <= n);
+end|
+create function f7() returns int
+return (select sum(data) from t1 where data <= f1())|
+select f6()|
+f6()
+2
+select id, f6() from t1|
+id	f6()
+a	2
+b	2
+create view v1 (a) as select f1()|
+select * from v1|
+a
+3
+select id, a from t1, v1|
+id	a
+a	3
+b	3
+select * from v1, v1 as v|
+a	a
+3	3
+create view v2 (a) as select a*10 from v1|
+select * from v2|
+a
+30
+select id, a from t1, v2|
+id	a
+a	30
+b	30
+select * from v1, v2|
+a	a
+3	30
+create function f8 () returns int
+return (select count(*) from v2)|
+select *, f8() from v1|
+a	f8()
+3	1
+drop function f1|
+select * from v1|
+ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s)
+create function f1() returns int
+return (select sum(data) from t1) + (select sum(data) from v1)|
+drop function f1|
+create function f1() returns int
+return (select sum(data) from t1)|
+create function f0() returns int
+return (select * from (select 100) as r)|
+select f0()|
+f0()
+100
+select *, f0() from (select 1) as t|
+1	f0()
+1	100
+create view v0 as select f0()|
+select * from v0|
+f0()
+100
+select *, f0() from v0|
+f0()	f0()
+100	100
+lock tables t1 read, t1 as t11 read, mysql.proc read|
+select f3()|
+f3()
+1
+select id, f3() from t1 as t11|
+id	f3()
+a	1
+b	1
+select f0()|
+f0()
+100
+select * from v0|
+f0()
+100
+select *, f0() from v0, (select 123) as d1|
+f0()	123	f0()
+100	123	100
+select id, f3() from t1|
+ERROR HY000: Table 't1' was not locked with LOCK TABLES
+select f4()|
+ERROR HY000: Table 't2' was not locked with LOCK TABLES
+unlock tables|
+lock tables v2 read, mysql.proc read|
+select * from v2|
+a
+30
+select * from v1|
+a
+3
+select * from v1, v2|
+ERROR HY000: Table 't1' was not locked with LOCK TABLES
+select f4()|
+ERROR HY000: Table 't2' was not locked with LOCK TABLES
+unlock tables|
+drop function f0|
+drop function f1|
+drop function f2|
+drop function f3|
+drop function f4|
+drop function f5|
+drop function f6|
+drop function f7|
+drop function f8|
+drop view v0|
+drop view v1|
+drop view v2|
+delete from t1 |
+delete from t2 |
 drop procedure if exists bug822|
 create procedure bug822(a_id char(16), a_data int)
 begin
@@ -1177,56 +1382,6 @@ select @x2|
 @x2
 2
 drop procedure bug2260|
-drop procedure if exists bug2267_1|
-create procedure bug2267_1()
-begin
-show procedure status;
-end|
-drop procedure if exists bug2267_2|
-create procedure bug2267_2()
-begin
-show function status;
-end|
-drop procedure if exists bug2267_3|
-create procedure bug2267_3()
-begin
-show create procedure bug2267_1;
-end|
-drop procedure if exists bug2267_4|
-create procedure bug2267_4()
-begin
-show create function fac;
-end|
-call bug2267_1()|
-Db	Name	Type	Definer	Modified	Created	Security_type	Comment
-test	bug2267_1	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
-test	bug2267_2	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
-test	bug2267_3	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
-test	bug2267_4	PROCEDURE	root@localhost	0000-00-00 00:00:00	0000-00-00 00:00:00	DEFINER	
-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	sql_mode	Create Procedure
-bug2267_1		CREATE PROCEDURE `test`.`bug2267_1`()
-begin
-show procedure status;
-end
-call bug2267_4()|
-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
-set f = f * n;
-set n = n - 1;
-end while;
-return f;
-end
-drop procedure bug2267_1|
-drop procedure bug2267_2|
-drop procedure bug2267_3|
-drop procedure bug2267_4|
 drop procedure if exists bug2227|
 create procedure bug2227(x int)
 begin
@@ -1331,7 +1486,7 @@ declare t2 int;
 declare t3 int;
 declare rc int default 0;
 declare continue handler for 1065 set rc = 1;
-drop table if exists temp_t1;
+drop temporary table if exists temp_t1;
 create temporary table temp_t1 (
 f1 int auto_increment, f2 varchar(20), primary key (f1)
 );
@@ -1349,6 +1504,7 @@ f1	rc	t3
 2	0	NULL
 2	0	NULL
 drop procedure bug1863|
+drop temporary table temp_t1;
 drop table t3, t4|
 drop table if exists t3, t4|
 create table t3 ( 
@@ -2515,7 +2671,7 @@ delete from t1|
 insert into t1 values ("answer", 42)|
 select id, bug5240() from t1|
 id	bug5240()
-42	42
+answer	42
 drop function bug5240|
 drop function if exists bug5278|
 create function bug5278 () returns char
diff --git a/mysql-test/r/type_blob.result b/mysql-test/r/type_blob.result
index 7b9a94ba998..a9b90617bcc 100644
--- a/mysql-test/r/type_blob.result
+++ b/mysql-test/r/type_blob.result
@@ -509,7 +509,7 @@ charset(load_file('../../std_data/words.dat')),
 collation(load_file('../../std_data/words.dat')),
 coercibility(load_file('../../std_data/words.dat'));
 charset(load_file('../../std_data/words.dat'))	collation(load_file('../../std_data/words.dat'))	coercibility(load_file('../../std_data/words.dat'))
-binary	binary	3
+binary	binary	4
 explain extended select 
 charset(load_file('../../std_data/words.dat')),
 collation(load_file('../../std_data/words.dat')),
diff --git a/mysql-test/r/type_newdecimal.result b/mysql-test/r/type_newdecimal.result
index 5b6612572cb..bb5f9e7f3b0 100644
--- a/mysql-test/r/type_newdecimal.result
+++ b/mysql-test/r/type_newdecimal.result
@@ -733,6 +733,9 @@ abs(9999999999999999999999)
 select abs(-9999999999999999999999);
 abs(-9999999999999999999999)
 9999999999999999999999
+select ceiling(999999999999999999);
+ceiling(999999999999999999)
+999999999999999999
 select ceiling(99999999999999999999);
 ceiling(99999999999999999999)
 99999999999999999999
@@ -741,13 +744,16 @@ ceiling(9.9999999999999999999)
 10
 select ceiling(-9.9999999999999999999);
 ceiling(-9.9999999999999999999)
--10
+-9
+select floor(999999999999999999);
+floor(999999999999999999)
+999999999999999999
 select floor(9999999999999999999999);
 floor(9999999999999999999999)
 9999999999999999999999
 select floor(9.999999999999999999999);
 floor(9.999999999999999999999)
-10
+9
 select floor(-9.999999999999999999999);
 floor(-9.999999999999999999999)
 -10
diff --git a/mysql-test/t/cast.test b/mysql-test/t/cast.test
index 5866431e687..94d9590dd5f 100644
--- a/mysql-test/t/cast.test
+++ b/mysql-test/t/cast.test
@@ -126,3 +126,8 @@ select cast(cast('1.2' as decimal(3,2)) as signed);
 set @v1=1e18;
 select cast(@v1 as decimal(22, 2));
 select cast(-1e18 as decimal(22,2));
+
+create table t1(s1 time);
+insert into t1 values ('11:11:11');
+select cast(s1 as decimal(7,2)) from t1;
+drop table t1;
diff --git a/mysql-test/t/func_group.test b/mysql-test/t/func_group.test
index 96fd297dc20..de9c7ff3ecb 100644
--- a/mysql-test/t/func_group.test
+++ b/mysql-test/t/func_group.test
@@ -537,3 +537,57 @@ CREATE TABLE t1 (id int(11),value1 float(10,2));
 INSERT INTO t1 VALUES (1,0.00),(1,1.00), (1,2.00), (2,10.00), (2,11.00), (2,12.00), (2,13.00);
 select id, stddev_pop(value1), var_pop(value1), stddev_samp(value1), var_samp(value1) from t1 group by id;
 DROP TABLE t1;
+
+#
+# BUG#8464 decimal AVG returns incorrect result
+#
+
+CREATE TABLE t1 (col1 decimal(16,12));
+INSERT INTO t1 VALUES (-5.00000000001),(-5.00000000002),(-5.00000000003),(-5.00000000000),(-5.00000000001),(-5.00000000002);
+insert into t1 select * from t1;
+select col1,count(col1),sum(col1),avg(col1) from t1 group by col1;
+DROP TABLE t1;
+
+#
+# BUG#8465 decimal MIN and MAX return incorrect result
+#
+
+create table t1 (col1 decimal(16,12));
+insert into t1 values (-5.00000000001);
+insert into t1 values (-5.00000000001);
+select col1,sum(col1),max(col1),min(col1) from t1 group by col1;
+delete from t1;
+insert into t1 values (5.00000000001);
+insert into t1 values (5.00000000001);
+select col1,sum(col1),max(col1),min(col1) from t1 group by col1;
+DROP TABLE t1;
+
+#
+# Bug 8893: wrong result for min/max optimization with 2 indexes
+#
+
+CREATE TABLE t1(
+  id int PRIMARY KEY,
+  a  int,
+  b  int,
+  INDEX i_b_id(a,b,id),
+  INDEX i_id(a,id)
+);
+INSERT INTO t1 VALUES 
+  (1,1,4), (2,2,1), (3,1,3), (4,2,1), (5,1,1);
+SELECT MAX(id) FROM t1 WHERE id < 3 AND a=2 AND b=6;
+DROP TABLE t1;
+
+# change the order of the last two index definitions
+
+CREATE TABLE t1(
+  id int PRIMARY KEY,
+  a  int,
+  b  int,
+  INDEX i_id(a,id),
+  INDEX i_b_id(a,b,id)
+);
+INSERT INTO t1 VALUES 
+  (1,1,4), (2,2,1), (3,1,3), (4,2,1), (5,1,1);
+SELECT MAX(id) FROM t1 WHERE id < 3 AND a=2 AND b=6;
+DROP TABLE t1;
diff --git a/mysql-test/t/func_system.test b/mysql-test/t/func_system.test
index a05b80ca56b..7fff165e057 100644
--- a/mysql-test/t/func_system.test
+++ b/mysql-test/t/func_system.test
@@ -30,3 +30,12 @@ show create table t1;
 drop table t1;
 
 select TRUE,FALSE,NULL;
+
+#
+# Bug#8291 Illegal collation mix with USER() function
+#
+create table t1 (a char(10)) character set latin1;
+select * from t1 where a=version();
+select * from t1 where a=database();
+select * from t1 where a=user();
+drop table t1;
diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test
index 9ff0b1fdf61..4319fec258c 100644
--- a/mysql-test/t/information_schema.test
+++ b/mysql-test/t/information_schema.test
@@ -3,7 +3,8 @@
 # show databases
 
 show variables where variable_name like "skip_show_database";
-grant all privileges on test.* to mysqltest_1@localhost;
+grant select, update, execute on test.* to mysqltest_2@localhost;
+grant select, update on test.* to mysqltest_1@localhost;
 
 select * from information_schema.SCHEMATA where schema_name > 'm';
 select schema_name from information_schema.schemata;
@@ -104,6 +105,30 @@ select a.ROUTINE_NAME, b.name from information_schema.ROUTINES a,
 mysql.proc b where a.ROUTINE_NAME = convert(b.name using utf8);
 select count(*) from information_schema.ROUTINES;
 
+connect (user1,localhost,mysqltest_1,,);
+connect (user3,localhost,mysqltest_2,,);
+connection user1;
+select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES;
+--error 1305
+show create function sub1;
+connection user3;
+select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES;
+connection default;
+grant all privileges on test.* to mysqltest_1@localhost;
+connect (user2,localhost,mysqltest_1,,);
+connection user2;
+select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES;
+create function sub2(i int) returns int
+  return i+1;
+select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES;
+show create procedure sel2;
+show create function sub1;
+show create function sub2;
+connection default;
+disconnect user1;
+drop function sub2;
+show create procedure sel2;
+
 #
 # Test for views
 #
@@ -138,8 +163,8 @@ select * from information_schema.USER_PRIVILEGES where grantee like '%mysqltest_
 select * from information_schema.SCHEMA_PRIVILEGES where grantee like '%mysqltest_1%';
 select * from information_schema.TABLE_PRIVILEGES where grantee like '%mysqltest_1%';
 select * from information_schema.COLUMN_PRIVILEGES where grantee like '%mysqltest_1%';
-delete from mysql.user where user='mysqltest_1';
-delete from mysql.db where user='mysqltest_1';
+delete from mysql.user where user='mysqltest_1' or user='mysqltest_2';
+delete from mysql.db where user='mysqltest_1' or user='mysqltest_2';
 delete from mysql.tables_priv where user='mysqltest_1';
 delete from mysql.columns_priv where user='mysqltest_1';
 flush privileges;
@@ -160,13 +185,11 @@ TABLE_SCHEMA= "test";
 select * from information_schema.KEY_COLUMN_USAGE where
 TABLE_SCHEMA= "test";
 
-
-connect (user1,localhost,mysqltest_1,,);
-connection user1;
+connection user2;
 select table_name from information_schema.TABLES where table_schema like "test%";
 select table_name,column_name from information_schema.COLUMNS where table_schema like "test%";
 select ROUTINE_NAME from information_schema.ROUTINES;
-disconnect user1;
+disconnect user2;
 connection default;
 delete from mysql.user where user='mysqltest_1';
 drop table t1;
diff --git a/mysql-test/t/lock.test b/mysql-test/t/lock.test
index 80da2cad192..faa1fa3ac25 100644
--- a/mysql-test/t/lock.test
+++ b/mysql-test/t/lock.test
@@ -53,7 +53,7 @@ check table t1;
 # Check error message
 lock tables t1 write;
 check table t2;
---error 1143
+--error 1100
 insert into t1 select index1,nr from t1;
 unlock tables;
 lock tables t1 write, t1 as t1_alias read;
diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test
index 43599e3b3a9..be24c380e0d 100644
--- a/mysql-test/t/mysqldump.test
+++ b/mysql-test/t/mysqldump.test
@@ -1,6 +1,7 @@
 --disable_warnings
 DROP TABLE IF EXISTS t1, `"t"1`, t1aa,t2aa;
 drop database if exists mysqldump_test_db;
+drop view if exists v1;
 --enable_warnings
 
 # XML output
diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test
index 69bdeb7a059..3c98e5ca4ba 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -339,16 +339,15 @@ drop procedure if exists sub1|
 create procedure sub1(id char(16), x int)
   insert into test.t1 values (id, x)|
 
-# QQ This doesn't work yet
-#--disable_warnings
-#drop procedure if exists sub2|
-#--enable_warnings
-#create procedure sub2(id char(16))
-#begin
-#  declare x int;
-#  set x = (select sum(t.x) from test.t2 t);
-#  insert into test.t1 values (id, x);
-#end|
+--disable_warnings
+drop procedure if exists sub2|
+--enable_warnings
+create procedure sub2(id char(16))
+begin
+  declare x int;
+  set x = (select sum(t.i) from test.t2 t);
+  insert into test.t1 values (id, x);
+end|
 
 --disable_warnings
 drop procedure if exists sub3|
@@ -360,11 +359,11 @@ call sub1("sub1a", (select 7))|
 call sub1("sub1b", (select max(i) from t2))|
 call sub1("sub1c", (select i,d from t2 limit 1))|
 call sub1("sub1d", (select 1 from (select 1) a))|
-#call sub2("sub2");
+call sub2("sub2");
 select * from t1|
 select sub3((select max(i) from t2))|
 drop procedure sub1|
-#drop procedure sub2|
+drop procedure sub2|
 drop function sub3|
 delete from t2|
 
@@ -1279,6 +1278,202 @@ drop procedure rc|
 
 
 #
+# Let us test how well new locking scheme works.
+#
+
+# Let us prepare playground
+--disable_warnings
+drop function if exists f0|
+drop function if exists f1|
+drop function if exists f2|
+drop function if exists f3|
+drop function if exists f4|
+drop function if exists f5|
+drop function if exists f6|
+drop function if exists f7|
+drop function if exists f8|
+drop view if exists v0|
+drop view if exists v1|
+drop view if exists v2|
+--enable_warnings
+delete from t1|
+delete from t2|
+insert into t1 values ("a", 1), ("b", 2) |
+insert into t2 values ("a", 1, 1.0), ("b", 2, 2.0), ("c", 3, 3.0) |
+
+# Test the simplest function using tables
+create function f1() returns int
+  return (select sum(data) from t1)|
+select f1()|
+# This should work too (and give 2 rows as result)
+select id, f1() from t1|
+
+# Function which uses two instances of table simultaneously
+create function f2() returns int
+  return (select data from t1 where data <= (select sum(data) from t1) limit 1)|
+select f2()|
+select id, f2() from t1|
+
+# Function which uses the same table twice in different queries
+create function f3() returns int
+begin
+  declare n int;
+  declare m int;
+  set n:= (select min(data) from t1);
+  set m:= (select max(data) from t1);
+  return n < m;
+end|
+select f3()|
+select id, f3() from t1|
+
+# Calling two functions using same table
+select f1(), f3()|
+select id, f1(), f3() from t1|
+
+# Function which uses two different tables
+create function f4() returns double 
+  return (select d from t1, t2 where t1.data = t2.i and t1.id= "b")|
+select f4()|
+select s, f4() from t2|
+
+# Recursive functions which due to this recursion require simultaneous
+# access to several instance of the same table won't work
+create function f5(i int) returns int
+begin
+  if i <= 0 then
+    return 0;
+  elseif i = 1  then
+    return (select count(*) from t1 where data = i);
+  else
+    return (select count(*) + f5( i - 1) from t1 where data = i);
+  end if;
+end|
+select f5(1)|
+# This should generate an error about insuficient number of tables locked
+--error 1100
+select f5(2)|
+# But now it simply miserably fails because we are trying to use the same
+# lex on the next iteration :/ It should generate some error too...
+# select f5(3)|
+
+# OTOH this should work 
+create function f6() returns int
+begin
+  declare n int;
+  set n:= f1();
+  return (select count(*) from t1 where data <= f7() and data <= n);
+end|
+create function f7() returns int
+  return (select sum(data) from t1 where data <= f1())|
+select f6()|
+select id, f6() from t1|
+
+# TODO Test temporary table handling
+
+#
+# Let us test how new locking work with views
+#
+# The most trivial view
+create view v1 (a) as select f1()|
+select * from v1|
+select id, a from t1, v1|
+select * from v1, v1 as v|
+# A bit more complex construction
+create view v2 (a) as select a*10 from v1|
+select * from v2|
+select id, a from t1, v2|
+select * from v1, v2|
+
+# Nice example where the same view is used on
+# on different expression levels
+create function f8 () returns int
+  return (select count(*) from v2)|
+
+select *, f8() from v1|
+
+# Let us test what will happen if function is missing
+drop function f1|
+--error 1356
+select * from v1|
+
+# And what will happen if we have recursion which involves
+# views and functions ?
+create function f1() returns int
+  return (select sum(data) from t1) + (select sum(data) from v1)|
+# FIXME All these just exceed file limit for me :)
+#select f1()|
+#select * from v1|
+#select * from v2|
+# Back to the normal cases
+drop function f1|
+create function f1() returns int
+  return (select sum(data) from t1)|
+
+# Let us also test some weird cases where no real tables is used
+create function f0() returns int
+  return (select * from (select 100) as r)|
+select f0()|
+select *, f0() from (select 1) as t|
+create view v0 as select f0()|
+select * from v0|
+select *, f0() from v0|
+
+#
+# Let us test how well prelocking works with explicit LOCK TABLES.
+#
+# Nowdays we have to lock mysql.proc to be able to read SP definitions.
+# But Monty was going to fix this.
+lock tables t1 read, t1 as t11 read, mysql.proc read|
+# These should work well
+select f3()|
+select id, f3() from t1 as t11|
+# Degenerate cases work too :)
+select f0()|
+select * from v0|
+select *, f0() from v0, (select 123) as d1|
+# But these should not !
+--error 1100
+select id, f3() from t1|
+--error 1100
+select f4()|
+unlock tables|
+
+# Let us test how LOCK TABLES which implicitly depends on functions
+# works
+lock tables v2 read, mysql.proc read|
+select * from v2|
+select * from v1|
+# These should not work as we have too little instances of tables locked
+--error 1100
+select * from v1, v2|
+--error 1100
+select f4()|
+unlock tables|
+
+
+# TODO We also should test integration with triggers
+
+
+# Cleanup
+drop function f0|
+drop function f1|
+drop function f2|
+drop function f3|
+drop function f4|
+drop function f5|
+drop function f6|
+drop function f7|
+drop function f8|
+drop view v0|
+drop view v1|
+drop view v2|
+delete from t1 |
+delete from t2 |
+
+# End of non-bug tests
+
+
+#
 # Test cases for old bugs
 #
 
@@ -1453,49 +1648,56 @@ drop procedure bug2260|
 #
 # BUG#2267
 #
---disable_warnings
-drop procedure if exists bug2267_1|
---enable_warnings
-create procedure bug2267_1()
-begin
-  show procedure status;
-end|
-
---disable_warnings
-drop procedure if exists bug2267_2|
---enable_warnings
-create procedure bug2267_2()
-begin
-  show function status;
-end|
-
---disable_warnings
-drop procedure if exists bug2267_3|
---enable_warnings
-create procedure bug2267_3()
-begin
-  show create procedure bug2267_1;
-end|
-
---disable_warnings
-drop procedure if exists bug2267_4|
---enable_warnings
-create procedure bug2267_4()
-begin
-  show create function fac;
-end|
-
---replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
-call bug2267_1()|
---replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
-call bug2267_2()|
-call bug2267_3()|
-call bug2267_4()|
-
-drop procedure bug2267_1|
-drop procedure bug2267_2|
-drop procedure bug2267_3|
-drop procedure bug2267_4|
+# NOTE: This test case will be fixed as soon as Monty
+#       will allow to open mysql.proc table under LOCK TABLES
+#       without mentioning in lock list.
+#       
+# FIXME: Other solution would be to use preopened proc table
+#        instead of opening it anew.
+# 
+#--disable_warnings
+#drop procedure if exists bug2267_1|
+#--enable_warnings
+#create procedure bug2267_1()
+#begin
+#  show procedure status;
+#end|
+#
+#--disable_warnings
+#drop procedure if exists bug2267_2|
+#--enable_warnings
+#create procedure bug2267_2()
+#begin
+#  show function status;
+#end|
+#
+#--disable_warnings
+#drop procedure if exists bug2267_3|
+#--enable_warnings
+#create procedure bug2267_3()
+#begin
+#  show create procedure bug2267_1;
+#end|
+#
+#--disable_warnings
+#drop procedure if exists bug2267_4|
+#--enable_warnings
+#create procedure bug2267_4()
+#begin
+#  show create function fac;
+#end|
+#
+#--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+#call bug2267_1()|
+#--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
+#call bug2267_2()|
+#call bug2267_3()|
+#call bug2267_4()|
+#
+#drop procedure bug2267_1|
+#drop procedure bug2267_2|
+#drop procedure bug2267_3|
+#drop procedure bug2267_4|
 
 #
 # BUG#2227
@@ -1529,7 +1731,7 @@ drop procedure bug2227|
 #--enable_warnings
 #create procedure bug2614()
 #begin
-#  drop table if exists t3;
+#  drop temporary table if exists t3;
 #  create temporary table t3 (id int default '0' not null);
 #  insert into t3 select 12;
 #  insert into t3 select * from t3;
@@ -1539,7 +1741,7 @@ drop procedure bug2227|
 #call bug2614()|
 #--enable_warnings
 #call bug2614()|
-#drop table t3|
+#drop temporary table t3|
 #drop procedure bug2614|
 
 #
@@ -1680,7 +1882,7 @@ begin
   declare rc int default 0;
   declare continue handler for 1065 set rc = 1;
 
-  drop table if exists temp_t1;
+  drop temporary table if exists temp_t1;
   create temporary table temp_t1 (
     f1 int auto_increment, f2 varchar(20), primary key (f1)
   );
@@ -1702,6 +1904,7 @@ call bug1863(10)|
 select * from t4|
 
 drop procedure bug1863|
+drop temporary table temp_t1;
 drop table t3, t4|
 
 #
@@ -3006,15 +3209,6 @@ drop table t3|
 drop function getcount|
 
 #
-# Former BUG#1654
-# QQ Currently crashes
-#
-#create function bug1654() returns int
-#  return (select sum(t1.data) from test.t1 t)|
-#
-#select bug1654()|
-
-#
 # BUG#5240: Stored procedure crash if function has cursor declaration
 #
 # The following test case fails in --ps-protocol mode due to some bugs
@@ -3039,7 +3233,6 @@ end|
 
 delete from t1|
 insert into t1 values ("answer", 42)|
-# QQ BUG: This returns the wrong result, id=42 instead of "answer".
 select id, bug5240() from t1|
 drop function bug5240|
 
diff --git a/mysql-test/t/type_newdecimal.test b/mysql-test/t/type_newdecimal.test
index 3922a0448e9..d116d56fa48 100644
--- a/mysql-test/t/type_newdecimal.test
+++ b/mysql-test/t/type_newdecimal.test
@@ -601,6 +601,7 @@ select abs(9999999999999999999999);
 select abs(-9999999999999999999999);
 #-- should return 9999999999999999999999
 #
+select ceiling(999999999999999999);
 select ceiling(99999999999999999999);
 #-- should return 99999999999999999999
 #
@@ -610,6 +611,7 @@ select ceiling(9.9999999999999999999);
 select ceiling(-9.9999999999999999999);
 #-- should return 9
 #
+select floor(999999999999999999);
 select floor(9999999999999999999999);
 #-- should return 9999999999999999999999
 #
diff --git a/mysys/mf_iocache.c b/mysys/mf_iocache.c
index b86e9daf92d..6fa64aa2d47 100644
--- a/mysys/mf_iocache.c
+++ b/mysys/mf_iocache.c
@@ -87,7 +87,7 @@ static void my_aiowait(my_aio_result *result);
 void setup_io_cache(IO_CACHE* info)
 {
   /* Ensure that my_b_tell() and my_b_bytes_in_cache works */
-  if (info->type == WRITE_CACHE)
+  if (info->type == WRITE_CACHE || info->type == APPEND_CACHE)
   {
     info->current_pos= &info->write_pos;
     info->current_end= &info->write_end;
@@ -247,7 +247,7 @@ int init_io_cache(IO_CACHE *info, File file, uint cachesize,
   }
 #endif
 
-  if (type == WRITE_CACHE)
+  if (type == WRITE_CACHE || type == APPEND_CACHE)
     info->write_end=
       info->buffer+info->buffer_length- (seek_offset & (IO_SIZE-1));
   else
@@ -318,6 +318,7 @@ my_bool reinit_io_cache(IO_CACHE *info, enum cache_type type,
   /* One can't do reinit with the following types */
   DBUG_ASSERT(type != READ_NET && info->type != READ_NET &&
 	      type != WRITE_NET && info->type != WRITE_NET &&
+	      type != APPEND_CACHE && info->type != APPEND_CACHE &&
 	      type != SEQ_READ_APPEND && info->type != SEQ_READ_APPEND);
 
   /* If the whole file is in memory, avoid flushing to disk */
@@ -1123,7 +1124,8 @@ int my_b_flush_io_cache(IO_CACHE *info, int need_append_buffer_lock)
   my_off_t pos_in_file;
   DBUG_ENTER("my_b_flush_io_cache");
 
-  if (!(append_cache = (info->type == SEQ_READ_APPEND)))
+  if (!(append_cache = (info->type == SEQ_READ_APPEND ||
+                        info->type == APPEND_CACHE)))
     need_append_buffer_lock=0;
 
   if (info->type == WRITE_CACHE || append_cache)
@@ -1170,7 +1172,13 @@ int my_b_flush_io_cache(IO_CACHE *info, int need_append_buffer_lock)
       else
       {
 	info->end_of_file+=(info->write_pos-info->append_read_pos);
-	DBUG_ASSERT(info->end_of_file == my_tell(info->file,MYF(0)));
+        /*
+          We only need to worry that info->end_of_file is really accurate
+          for SEQ_READ_APPEND. For APPEND_CACHE, it is possible that the
+          file is non-seekable, like a FIFO.
+         */
+	DBUG_ASSERT(info->type != SEQ_READ_APPEND ||
+                    info->end_of_file == my_tell(info->file,MYF(0)));
       }
 
       info->append_read_pos=info->write_pos=info->write_buffer;
diff --git a/mysys/mf_tempfile.c b/mysys/mf_tempfile.c
index af9ff0d6711..a15bda4da6d 100644
--- a/mysys/mf_tempfile.c
+++ b/mysys/mf_tempfile.c
@@ -70,7 +70,7 @@ File create_temp_file(char *to, const char *dir, const char *prefix,
     {
       strmake(to,res,FN_REFLEN-1);
       (*free)(res);
-      file=my_create(to,0, mode, MyFlags);
+      file=my_create(to,0, mode | O_EXCL | O_NOFOLLOW, MyFlags);
     }
     environ=old_env;
   }
@@ -81,7 +81,7 @@ File create_temp_file(char *to, const char *dir, const char *prefix,
   {
     strmake(to,res,FN_REFLEN-1);
     (*free)(res);
-    file=my_create(to, 0, mode, MyFlags);
+    file=my_create(to, 0, mode | O_EXCL | O_NOFOLLOW, MyFlags);
   }
 #elif defined(HAVE_MKSTEMP) && !defined(__NETWARE__)
   {
@@ -143,7 +143,7 @@ File create_temp_file(char *to, const char *dir, const char *prefix,
       strmake(to,res,FN_REFLEN-1);
       (*free)(res);
       file=my_create(to,0,
-		     (int) (O_RDWR | O_BINARY | O_TRUNC |
+		     (int) (O_RDWR | O_BINARY | O_TRUNC | O_EXCL | O_NOFOLLOW |
 			    O_TEMPORARY | O_SHORT_LIVED),
 		     MYF(MY_WME));
 
@@ -186,7 +186,7 @@ File create_temp_file(char *to, const char *dir, const char *prefix,
       }
       (void) strmov(end_pos,TMP_EXT);
       file=my_create(to,0,
-		     (int) (O_RDWR | O_BINARY | O_TRUNC |
+		     (int) (O_RDWR | O_BINARY | O_TRUNC | O_EXCL | O_NOFOLLOW |
 			    O_TEMPORARY | O_SHORT_LIVED),
 		     MYF(MY_WME));
     }
diff --git a/sql-common/client.c b/sql-common/client.c
index aece4230fe0..2c73cb4d07c 100644
--- a/sql-common/client.c
+++ b/sql-common/client.c
@@ -1681,7 +1681,8 @@ CLI_MYSQL_REAL_CONNECT(MYSQL *mysql,const char *host, const char *user,
                   ER(net->last_errno),socket_errno);
       goto error;
     }
-    net->vio = vio_new(sock, VIO_TYPE_SOCKET, TRUE);
+    net->vio= vio_new(sock, VIO_TYPE_SOCKET,
+                      VIO_LOCALHOST | VIO_BUFFERED_READ);
     bzero((char*) &UNIXaddr,sizeof(UNIXaddr));
     UNIXaddr.sun_family = AF_UNIX;
     strmake(UNIXaddr.sun_path, unix_socket, sizeof(UNIXaddr.sun_path)-1);
@@ -1756,7 +1757,7 @@ CLI_MYSQL_REAL_CONNECT(MYSQL *mysql,const char *host, const char *user,
                   ER(net->last_errno),socket_errno);
       goto error;
     }
-    net->vio = vio_new(sock,VIO_TYPE_TCPIP,FALSE);
+    net->vio= vio_new(sock, VIO_TYPE_TCPIP, VIO_BUFFERED_READ);
     bzero((char*) &sock_addr,sizeof(sock_addr));
     sock_addr.sin_family = AF_INET;
 
diff --git a/sql/field.cc b/sql/field.cc
index 1538edc59a3..fd4d83a8b1c 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -626,7 +626,6 @@ int Field_str::store_decimal(const my_decimal *d)
 
 my_decimal *Field_str::val_decimal(my_decimal *decimal_value)
 {
-  DBUG_ASSERT(result_type() == INT_RESULT);
   longlong nr= val_int();
   int2my_decimal(E_DEC_FATAL_ERROR, nr, 0, decimal_value);
   return decimal_value;
diff --git a/sql/ha_federated.h b/sql/ha_federated.h
index 22fc03e9eec..22fc03e9eec 100755..100644
--- a/sql/ha_federated.h
+++ b/sql/ha_federated.h
diff --git a/sql/ha_innodb.cc b/sql/ha_innodb.cc
index 93876a49a5c..4dee14c27b4 100644
--- a/sql/ha_innodb.cc
+++ b/sql/ha_innodb.cc
@@ -2632,6 +2632,7 @@ ha_innobase::write_row(
                 table->timestamp_field->set_time();
 
 	if ((user_thd->lex->sql_command == SQLCOM_ALTER_TABLE
+	    || user_thd->lex->sql_command == SQLCOM_OPTIMIZE
 	    || user_thd->lex->sql_command == SQLCOM_CREATE_INDEX
 	    || user_thd->lex->sql_command == SQLCOM_DROP_INDEX)
 	    && num_write_row >= 10000) {
diff --git a/sql/ha_myisam.cc b/sql/ha_myisam.cc
index ad7e775aacf..2179eaa7f8f 100644
--- a/sql/ha_myisam.cc
+++ b/sql/ha_myisam.cc
@@ -1350,7 +1350,7 @@ int ha_myisam::create(const char *name, register TABLE *table_arg,
 		      HA_CREATE_INFO *info)
 {
   int error;
-  uint i,j,recpos,minpos,fieldpos,temp_length,length;
+  uint i,j,recpos,minpos,fieldpos,temp_length,length, create_flags= 0;
   bool found_real_auto_increment=0;
   enum ha_base_keytype type;
   char buff[FN_REFLEN];
@@ -1538,17 +1538,21 @@ int ha_myisam::create(const char *name, register TABLE *table_arg,
   create_info.data_file_name=  info->data_file_name;
   create_info.index_file_name= info->index_file_name;
 
+  if (info->options & HA_LEX_CREATE_TMP_TABLE)
+    create_flags|= HA_CREATE_TMP_TABLE;
+  if (options & HA_OPTION_PACK_RECORD)
+    create_flags|= HA_PACK_RECORD;
+  if (options & HA_OPTION_CHECKSUM)
+    create_flags|= HA_CREATE_CHECKSUM;
+  if (options & HA_OPTION_DELAY_KEY_WRITE)
+    create_flags|= HA_CREATE_DELAY_KEY_WRITE;
+
   /* TODO: Check that the following fn_format is really needed */
   error=mi_create(fn_format(buff,name,"","",2+4),
 		  share->keys,keydef,
 		  (uint) (recinfo_pos-recinfo), recinfo,
 		  0, (MI_UNIQUEDEF*) 0,
-		  &create_info,
-		  (((options & HA_OPTION_PACK_RECORD) ? HA_PACK_RECORD : 0) |
-		   ((options & HA_OPTION_CHECKSUM) ? HA_CREATE_CHECKSUM : 0) |
-		   ((options & HA_OPTION_DELAY_KEY_WRITE) ?
-		    HA_CREATE_DELAY_KEY_WRITE : 0)));
-
+		  &create_info, create_flags);
 
   my_free((gptr) recinfo,MYF(0));
   DBUG_RETURN(error);
diff --git a/sql/item.cc b/sql/item.cc
index 2e8b16a4f9d..ef3cc5105ef 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -724,7 +724,6 @@ void Item::split_sum_func2(THD *thd, Item **ref_pointer_array,
 */
 bool DTCollation::aggregate(DTCollation &dt, uint flags)
 {
-  nagg++;
   if (!my_charset_same(collation, dt.collation))
   {
     /* 
@@ -740,7 +739,6 @@ bool DTCollation::aggregate(DTCollation &dt, uint flags)
       else
       {
 	set(dt); 
-        strong= nagg;
       }
     }
     else if (dt.collation == &my_charset_bin)
@@ -748,7 +746,6 @@ bool DTCollation::aggregate(DTCollation &dt, uint flags)
       if (dt.derivation <= derivation)
       {
         set(dt);
-        strong= nagg;
       }
       else
        ; // Do nothing
@@ -764,20 +761,18 @@ bool DTCollation::aggregate(DTCollation &dt, uint flags)
              dt.collation->state & MY_CS_UNICODE)
     {
       set(dt);
-      strong= nagg;
     }
     else if ((flags & MY_COLL_ALLOW_COERCIBLE_CONV) &&
              derivation < dt.derivation &&
-             dt.derivation >= DERIVATION_COERCIBLE)
+             dt.derivation >= DERIVATION_SYSCONST)
     {
       // Do nothing;
     }
     else if ((flags & MY_COLL_ALLOW_COERCIBLE_CONV) &&
              dt.derivation < derivation &&
-             derivation >= DERIVATION_COERCIBLE)
+             derivation >= DERIVATION_SYSCONST)
     {
       set(dt);
-      strong= nagg;
     }
     else
     {
@@ -793,7 +788,6 @@ bool DTCollation::aggregate(DTCollation &dt, uint flags)
   else if (dt.derivation < derivation)
   {
     set(dt);
-    strong= nagg;
   }
   else
   { 
diff --git a/sql/item.h b/sql/item.h
index 157c48393ba..97913e40916 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -32,8 +32,9 @@ class Item_field;
 
 enum Derivation
 {
-  DERIVATION_IGNORABLE= 4,
-  DERIVATION_COERCIBLE= 3,
+  DERIVATION_IGNORABLE= 5,
+  DERIVATION_COERCIBLE= 4,
+  DERIVATION_SYSCONST= 3,
   DERIVATION_IMPLICIT= 2,
   DERIVATION_NONE= 1,
   DERIVATION_EXPLICIT= 0
@@ -62,22 +63,16 @@ class DTCollation {
 public:
   CHARSET_INFO     *collation;
   enum Derivation derivation;
-  uint nagg;    // Total number of aggregated collations.
-  uint strong;  // Number of the strongest collation.
   
   DTCollation()
   {
     collation= &my_charset_bin;
     derivation= DERIVATION_NONE;
-    nagg= 0;
-    strong= 0;
   }
   DTCollation(CHARSET_INFO *collation_arg, Derivation derivation_arg)
   {
     collation= collation_arg;
     derivation= derivation_arg;
-    nagg= 0;
-    strong= 0;
   }
   void set(DTCollation &dt)
   { 
@@ -103,6 +98,7 @@ public:
       case DERIVATION_IGNORABLE: return "IGNORABLE";
       case DERIVATION_COERCIBLE: return "COERCIBLE";
       case DERIVATION_IMPLICIT:  return "IMPLICIT";
+      case DERIVATION_SYSCONST:  return "SYSCONST";
       case DERIVATION_EXPLICIT:  return "EXPLICIT";
       case DERIVATION_NONE:      return "NONE";
       default: return "UNKNOWN";
diff --git a/sql/item_create.cc b/sql/item_create.cc
index 6bd5c0c9a52..35cc46989f7 100644
--- a/sql/item_create.cc
+++ b/sql/item_create.cc
@@ -434,7 +434,7 @@ Item *create_func_version(void)
 {
   return new Item_static_string_func("version()", server_version,
 			 (uint) strlen(server_version),
-			 system_charset_info, DERIVATION_IMPLICIT);
+			 system_charset_info, DERIVATION_SYSCONST);
 }
 
 Item *create_func_weekday(Item* a)
diff --git a/sql/item_func.cc b/sql/item_func.cc
index 37e59fa89d3..cc4d25bc5af 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -82,8 +82,6 @@ bool Item_func::agg_arg_collations(DTCollation &c, Item **av, uint count,
                                    uint flags)
 {
   uint i;
-  c.nagg= 0;
-  c.strong= 0;
   c.set(av[0]->collation);
   for (i= 1; i < count; i++)
   {
@@ -1629,13 +1627,25 @@ void Item_func_int_val::find_num_type()
 
 longlong Item_func_ceiling::int_op()
 {
-  /*
-    the volatile's for BUG #3051 to calm optimizer down (because of gcc's
-    bug)
-  */
-  volatile double value= args[0]->val_real();
-  null_value= args[0]->null_value;
-  return (longlong) ceil(value);
+  longlong result;
+  switch (args[0]->result_type()) {
+  case INT_RESULT:
+    result= args[0]->val_int();
+    null_value= args[0]->null_value;
+    break;
+  case DECIMAL_RESULT:
+  {
+    my_decimal dec_buf, *dec;
+    if ((dec= Item_func_ceiling::decimal_op(&dec_buf)))
+      my_decimal2int(E_DEC_FATAL_ERROR, dec, unsigned_flag, &result);
+    else
+      result= 0;
+    break;
+  }
+  default:
+    result= (longlong)Item_func_ceiling::real_op();
+  };
+  return result;
 }
 
 
@@ -1664,13 +1674,25 @@ my_decimal *Item_func_ceiling::decimal_op(my_decimal *decimal_value)
 
 longlong Item_func_floor::int_op()
 {
-  /*
-    the volatile's for BUG #3051 to calm optimizer down (because of gcc's
-    bug)
-  */
-  volatile double value= args[0]->val_real();
-  null_value= args[0]->null_value;
-  return (longlong) floor(value);
+  longlong result;
+  switch (args[0]->result_type()) {
+  case INT_RESULT:
+    result= args[0]->val_int();
+    null_value= args[0]->null_value;
+    break;
+  case DECIMAL_RESULT:
+  {
+    my_decimal dec_buf, *dec;
+    if ((dec= Item_func_floor::decimal_op(&dec_buf)))
+      my_decimal2int(E_DEC_FATAL_ERROR, dec, unsigned_flag, &result);
+    else
+      result= 0;
+    break;
+  }
+  default:
+    result= (longlong)Item_func_floor::real_op();
+  };
+  return result;
 }
 
 
@@ -4414,11 +4436,6 @@ Item_func_sp::execute(Item **itp)
   }
 #endif
 
-  /*
-    We don't need to suppress sending of OK packet here (by setting
-    thd->net.no_send_ok to true), because we are not allowing statements
-    in functions now.
-  */
   res= m_sp->execute_function(thd, args, arg_count, itp);
 
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
diff --git a/sql/item_strfunc.cc b/sql/item_strfunc.cc
index 536ee46f4db..03aa56128ca 100644
--- a/sql/item_strfunc.cc
+++ b/sql/item_strfunc.cc
@@ -1515,6 +1515,23 @@ String *Item_func_decode::val_str(String *str)
 }
 
 
+Item *Item_func_sysconst::safe_charset_converter(CHARSET_INFO *tocs)
+{
+  Item_string *conv;
+  uint conv_errors;
+  String tmp, cstr, *ostr= val_str(&tmp);
+  cstr.copy(ostr->ptr(), ostr->length(), ostr->charset(), tocs, &conv_errors);
+  if (conv_errors || !(conv= new Item_string(cstr.ptr(), cstr.length(),
+                                             cstr.charset(),
+                                             collation.derivation)))
+  {
+    return NULL;
+  }
+  conv->str_value.copy();
+  return conv;
+}
+
+
 String *Item_func_database::val_str(String *str)
 {
   DBUG_ASSERT(fixed == 1);
diff --git a/sql/item_strfunc.h b/sql/item_strfunc.h
index dc50c9a4ccd..ea8a78c528a 100644
--- a/sql/item_strfunc.h
+++ b/sql/item_strfunc.h
@@ -337,10 +337,18 @@ public:
 };
 
 
-class Item_func_database :public Item_str_func
+class Item_func_sysconst :public Item_str_func
 {
 public:
-  Item_func_database() { collation.set(system_charset_info,DERIVATION_IMPLICIT); }
+  Item_func_sysconst()
+  { collation.set(system_charset_info,DERIVATION_SYSCONST); }
+  Item *safe_charset_converter(CHARSET_INFO *tocs);
+};
+
+class Item_func_database :public Item_func_sysconst
+{
+public:
+  Item_func_database() :Item_func_sysconst() {}
   String *val_str(String *);
   void fix_length_and_dec()
   {
@@ -350,10 +358,10 @@ public:
   const char *func_name() const { return "database"; }
 };
 
-class Item_func_user :public Item_str_func
+class Item_func_user :public Item_func_sysconst
 {
 public:
-  Item_func_user() { collation.set(system_charset_info, DERIVATION_IMPLICIT); }
+  Item_func_user() :Item_func_sysconst() {}
   String *val_str(String *);
   void fix_length_and_dec() 
   { 
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 97331d3c1a6..b978ffe175b 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -453,6 +453,7 @@ bool check_procedure_access(THD *thd,ulong want_access,char *db,char *name,
 bool check_some_access(THD *thd, ulong want_access, TABLE_LIST *table);
 bool check_merge_table_access(THD *thd, char *db,
 			      TABLE_LIST *table_list);
+bool check_some_routine_access(THD *thd, char *db, char *name);
 bool multi_update_precheck(THD *thd, TABLE_LIST *tables);
 bool multi_delete_precheck(THD *thd, TABLE_LIST *tables, uint *table_count);
 bool mysql_multi_update_prepare(THD *thd);
@@ -870,7 +871,7 @@ int setup_conds(THD *thd, TABLE_LIST *tables, TABLE_LIST *leaves,
 int setup_ftfuncs(SELECT_LEX* select);
 int init_ftfuncs(THD *thd, SELECT_LEX* select, bool no_order);
 void wait_for_refresh(THD *thd);
-int open_tables(THD *thd, TABLE_LIST *tables, uint *counter);
+int open_tables(THD *thd, TABLE_LIST **tables, uint *counter);
 int simple_open_n_lock_tables(THD *thd,TABLE_LIST *tables);
 bool open_and_lock_tables(THD *thd,TABLE_LIST *tables);
 bool open_normal_and_derived_tables(THD *thd, TABLE_LIST *tables);
@@ -1064,7 +1065,7 @@ extern my_bool opt_sql_bin_update, opt_safe_user_create, opt_no_mix_types;
 extern my_bool opt_safe_show_db, opt_local_infile;
 extern my_bool opt_slave_compressed_protocol, use_temp_pool;
 extern my_bool opt_readonly, lower_case_file_system;
-extern my_bool opt_enable_named_pipe, opt_sync_frm;
+extern my_bool opt_enable_named_pipe, opt_sync_frm, opt_allow_suspicious_udfs;
 extern my_bool opt_secure_auth;
 extern my_bool sp_automatic_privileges;
 extern my_bool opt_old_style_user_limits;
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index cfd1b87a722..2c1451a95a4 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -305,7 +305,7 @@ const char *opt_ndb_mgmd;
 ulong opt_ndb_nodeid;
 #endif
 my_bool opt_readonly, use_temp_pool, relay_log_purge;
-my_bool opt_sync_bdb_logs, opt_sync_frm;
+my_bool opt_sync_bdb_logs, opt_sync_frm, opt_allow_suspicious_udfs;
 my_bool opt_secure_auth= 0;
 my_bool opt_short_log_format= 0;
 my_bool opt_log_queries_not_using_indexes= 0;
@@ -3745,7 +3745,7 @@ extern "C" pthread_handler_decl(handle_connections_sockets,
     if (!(vio_tmp=vio_new(new_sock,
 			  sock == unix_sock ? VIO_TYPE_SOCKET :
 			  VIO_TYPE_TCPIP,
-			  sock == unix_sock)) ||
+			  sock == unix_sock ? VIO_LOCALHOST: 0)) ||
 	my_net_init(&thd->net,vio_tmp))
     {
       if (vio_tmp)
@@ -4220,7 +4220,7 @@ enum options_mysqld
   OPT_BDB_MAX_LOCK,
   OPT_ERROR_LOG_FILE,
   OPT_DEFAULT_WEEK_FORMAT,
-  OPT_RANGE_ALLOC_BLOCK_SIZE,
+  OPT_RANGE_ALLOC_BLOCK_SIZE, OPT_ALLOW_SUSPICIOUS_UDFS,
   OPT_QUERY_ALLOC_BLOCK_SIZE, OPT_QUERY_PREALLOC_SIZE,
   OPT_TRANS_ALLOC_BLOCK_SIZE, OPT_TRANS_PREALLOC_SIZE,
   OPT_SYNC_FRM, OPT_SYNC_BINLOG,
@@ -4268,6 +4268,13 @@ struct my_option my_long_options[] =
 #endif /* HAVE_REPLICATION */
   {"ansi", 'a', "Use ANSI SQL syntax instead of MySQL syntax. This mode will also set transaction isolation level 'serializable'.", 0, 0, 0,
    GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0},
+  {"allow-suspicious-udfs", OPT_ALLOW_SUSPICIOUS_UDFS,
+   "Allows use of UDFs consisting of only one symbol xxx() "
+   "without corresponding xxx_init() or xxx_deinit(). That also means "
+   "that one can load any function from any library, for example exit() "
+   "from libc.so",
+   (gptr*) &opt_allow_suspicious_udfs, (gptr*) &opt_allow_suspicious_udfs,
+   0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
   {"auto-increment-increment", OPT_AUTO_INCREMENT,
    "Auto-increment columns are incremented by this",
    (gptr*) &global_system_variables.auto_increment_increment,
@@ -5411,7 +5418,7 @@ The minimum value for this variable is 4096.",
    "it failed with a deadlock or elapsed lock wait timeout, "
    "before giving up and stopping.",
    (gptr*) &slave_trans_retries, (gptr*) &slave_trans_retries, 0,
-   GET_ULONG, REQUIRED_ARG, 0L, 0L, (longlong) ULONG_MAX, 0, 1, 0},
+   GET_ULONG, REQUIRED_ARG, 10L, 0L, (longlong) ULONG_MAX, 0, 1, 0},
 #endif /* HAVE_REPLICATION */
   {"slow_launch_time", OPT_SLOW_LAUNCH_TIME,
    "If creating the thread takes longer than this value (in seconds), the Slow_launch_threads counter will be incremented.",
diff --git a/sql/opt_sum.cc b/sql/opt_sum.cc
index ef9babf7713..134d3564ef8 100644
--- a/sql/opt_sum.cc
+++ b/sql/opt_sum.cc
@@ -638,7 +638,6 @@ static bool find_key_for_maxmin(bool max_fl, TABLE_REF *ref,
 {
   if (!(field->flags & PART_KEY_FLAG))
     return 0;                                        // Not key field
-  *prefix_len= 0;
 
   TABLE *table= field->table;
   uint idx= 0;
@@ -651,6 +650,7 @@ static bool find_key_for_maxmin(bool max_fl, TABLE_REF *ref,
     KEY_PART_INFO *part,*part_end;
     key_part_map key_part_to_use= 0;
     uint jdx= 0;
+    *prefix_len= 0;
     for (part= keyinfo->key_part, part_end= part+keyinfo->key_parts ;
          part != part_end ;
          part++, jdx++, key_part_to_use= (key_part_to_use << 1) | 1)
diff --git a/sql/sp.cc b/sql/sp.cc
index a57d90dbf7a..3f6d4d0bf1b 100644
--- a/sql/sp.cc
+++ b/sql/sp.cc
@@ -307,8 +307,6 @@ db_find_routine(THD *thd, int type, sp_name *name, sp_head **sphp)
 	goto done;
       if (sp)
       {
-	if (oldlex != newlex)
-	  sp->restore_lex(thd);
 	delete sp;
 	newlex->sphead= NULL;
       }
@@ -720,8 +718,29 @@ sp_drop_db_routines(THD *thd, char *db)
   PROCEDURE
 ******************************************************************************/
 
+/*
+  Obtain object representing stored procedure by its name from
+  stored procedures cache and looking into mysql.proc if needed.
+
+  SYNOPSIS
+    sp_find_procedure()
+      thd        - thread context
+      name       - name of procedure
+      cache_only - if true perform cache-only lookup
+                   (Don't look in mysql.proc).
+
+  TODO
+    We should consider merging of sp_find_procedure() and
+    sp_find_function() into one sp_find_routine() function
+    (the same applies to other similarly paired functions).
+
+  RETURN VALUE
+    Non-0 pointer to sp_head object for the procedure, or
+    0 - in case of error.
+*/
+
 sp_head *
-sp_find_procedure(THD *thd, sp_name *name)
+sp_find_procedure(THD *thd, sp_name *name, bool cache_only)
 {
   sp_head *sp;
   DBUG_ENTER("sp_find_procedure");
@@ -729,7 +748,7 @@ sp_find_procedure(THD *thd, sp_name *name)
 		       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)))
+  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);
@@ -855,6 +874,25 @@ sp_show_status_procedure(THD *thd, const char *wild)
   FUNCTION
 ******************************************************************************/
 
+/*
+  Obtain object representing stored function by its name from
+  stored functions cache and looking into mysql.proc if needed.
+
+  SYNOPSIS
+    sp_find_function()
+      thd        - thread context
+      name       - name of function
+      cache_only - if true perform cache-only lookup
+                   (Don't look in mysql.proc).
+
+  NOTE
+    See TODO section for sp_find_procedure().
+
+  RETURN VALUE
+    Non-0 pointer to sp_head object for the function, or
+    0 - in case of error.
+*/
+
 sp_head *
 sp_find_function(THD *thd, sp_name *name, bool cache_only)
 {
@@ -988,79 +1026,120 @@ sp_add_to_hash(HASH *h, sp_name *fun)
 }
 
 
-void
+/*
+  Merge contents of two hashes containing LEX_STRING's
+
+  SYNOPSIS
+    sp_merge_hash()
+      dst - hash to which elements should be added
+      src - hash from which elements merged
+
+  RETURN VALUE
+    TRUE  - if we have added some new elements to destination hash.
+    FALSE - there were no new elements in src.
+*/
+
+bool
 sp_merge_hash(HASH *dst, HASH *src)
 {
+  bool res= FALSE;
   for (uint i=0 ; i < src->records ; i++)
   {
     LEX_STRING *ls= (LEX_STRING *)hash_element(src, i);
 
     if (! hash_search(dst, (byte *)ls->str, ls->length))
+    {
       my_hash_insert(dst, (byte *)ls);
+      res= TRUE;
+    }
   }
+  return res;
 }
 
 
-int
-sp_cache_routines(THD *thd, LEX *lex, int type)
+/*
+  Cache all routines implicitly or explicitly used by query
+  (or whatever object is represented by LEX).
+
+  SYNOPSIS
+    sp_cache_routines()
+      thd - thread context
+      lex - LEX representing query
+
+  NOTE
+    If some function is missing this won't be reported here.
+    Instead this fact will be discovered during query execution.
+
+  TODO
+    Currently if after passing through routine hashes we discover
+    that we have added something to them, we do one more pass to
+    process all routines which were missed on previous pass because
+    of these additions. We can avoid this if along with hashes
+    we use lists holding routine names and iterate other these
+    lists instead of hashes (since addition to the end of list
+    does not reorder elements in it).
+*/
+
+void
+sp_cache_routines(THD *thd, LEX *lex)
 {
-  HASH *h= (type == TYPE_ENUM_FUNCTION ? &lex->spfuns : &lex->spprocs);
-  int ret= 0;
+  bool routines_added= TRUE;
 
-  for (uint i=0 ; i < h->records ; i++)
+  DBUG_ENTER("sp_cache_routines");
+
+  while (routines_added)
   {
-    LEX_STRING *ls= (LEX_STRING *)hash_element(h, i);
-    sp_name name(*ls);
+    routines_added= FALSE;
 
-    name.m_qname= *ls;
-    if (! sp_cache_lookup((type == TYPE_ENUM_FUNCTION ?
-			   &thd->sp_func_cache : &thd->sp_proc_cache),
-			  &name))
+    for (int type= TYPE_ENUM_FUNCTION; type < TYPE_ENUM_TRIGGER; type++)
     {
-      sp_head *sp;
-      LEX *oldlex= thd->lex;
-      LEX *newlex= new st_lex;
-
-      thd->lex= newlex;
-      newlex->proc_table= oldlex->proc_table; // hint if mysql.oper is opened
-      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, name.m_db.length);
-      name.m_name.str+= 1;
-      name.m_name.length= name.m_qname.length - name.m_db.length - 1;
-
-      if (db_find_routine(thd, type, &name, &sp) == SP_OK)
-      {
-	if (type == TYPE_ENUM_FUNCTION)
-	  sp_cache_insert(&thd->sp_func_cache, sp);
-	else
-	  sp_cache_insert(&thd->sp_proc_cache, sp);
-	ret= sp_cache_routines(thd, newlex, TYPE_ENUM_FUNCTION);
-	if (!ret)
-	{
-	  sp_merge_hash(&lex->spfuns, &newlex->spfuns);
-	  ret= sp_cache_routines(thd, newlex, TYPE_ENUM_PROCEDURE);
-	}
-	if (!ret)
-	{
-	  sp_merge_hash(&lex->spprocs, &newlex->spprocs);
-	  sp_merge_table_hash(&lex->sptabs, &sp->m_sptabs);
-	}
-	delete newlex;
-	thd->lex= oldlex;
-	if (ret)
-	  break;
-      }
-      else
+      HASH *h= (type == TYPE_ENUM_FUNCTION ? &lex->spfuns : &lex->spprocs);
+
+      for (uint i=0 ; i < h->records ; i++)
       {
-	delete newlex;
-	thd->lex= oldlex;
+        LEX_STRING *ls= (LEX_STRING *)hash_element(h, i);
+        sp_name name(*ls);
+        sp_head *sp;
+
+        name.m_qname= *ls;
+        if (!(sp= sp_cache_lookup((type == TYPE_ENUM_FUNCTION ?
+                                   &thd->sp_func_cache : &thd->sp_proc_cache),
+                                  &name)))
+        {
+          LEX *oldlex= thd->lex;
+          LEX *newlex= new st_lex;
+
+          thd->lex= newlex;
+          /* Pass hint pointer to mysql.proc table */
+          newlex->proc_table= oldlex->proc_table;
+          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,
+                                      name.m_db.length);
+          name.m_name.str+= 1;
+          name.m_name.length= name.m_qname.length - name.m_db.length - 1;
+
+          if (db_find_routine(thd, type, &name, &sp) == SP_OK)
+          {
+            if (type == TYPE_ENUM_FUNCTION)
+              sp_cache_insert(&thd->sp_func_cache, sp);
+            else
+              sp_cache_insert(&thd->sp_proc_cache, sp);
+          }
+          delete newlex;
+          thd->lex= oldlex;
+        }
+
+        if (sp)
+        {
+          routines_added|= sp_merge_hash(&lex->spfuns, &sp->m_spfuns);
+          routines_added|= sp_merge_hash(&lex->spprocs, &sp->m_spprocs);
+        }
       }
     }
   }
-  return ret;
+  DBUG_VOID_RETURN;
 }
 
 /*
diff --git a/sql/sp.h b/sql/sp.h
index 6290324bb86..00dd8416c1d 100644
--- a/sql/sp.h
+++ b/sql/sp.h
@@ -34,7 +34,7 @@ int
 sp_drop_db_routines(THD *thd, char *db);
 
 sp_head *
-sp_find_procedure(THD *thd, sp_name *name);
+sp_find_procedure(THD *thd, sp_name *name, bool cache_only = 0);
 
 int
 sp_exists_routine(THD *thd, TABLE_LIST *procs, bool any, bool no_error);
@@ -82,10 +82,10 @@ sp_function_exists(THD *thd, sp_name *name);
  */
 void
 sp_add_to_hash(HASH *h, sp_name *fun);
-void
+bool
 sp_merge_hash(HASH *dst, HASH *src);
-int
-sp_cache_routines(THD *thd, LEX *lex, int type);
+void
+sp_cache_routines(THD *thd, LEX *lex);
 
 
 //
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index ad527d39d21..21ef89aea72 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -279,12 +279,16 @@ sp_head::sp_head()
 {
   extern byte *
     sp_table_key(const byte *ptr, uint *plen, my_bool first);
+  extern byte 
+    *sp_lex_sp_key(const byte *ptr, uint *plen, my_bool first);
   DBUG_ENTER("sp_head::sp_head");
 
   state= INITIALIZED;
   m_backpatch.empty();
   m_lex.empty();
   hash_init(&m_sptabs, system_charset_info, 0, 0, 0, sp_table_key, 0, 0);
+  hash_init(&m_spfuns, system_charset_info, 0, 0, 0, sp_lex_sp_key, 0, 0);
+  hash_init(&m_spprocs, system_charset_info, 0, 0, 0, sp_lex_sp_key, 0, 0);
   DBUG_VOID_RETURN;
 }
 
@@ -455,13 +459,25 @@ sp_head::destroy()
   delete_dynamic(&m_instr);
   m_pcont->destroy();
   free_items(free_list);
+
+  /*
+    If we have non-empty LEX stack then we just came out of parser with
+    error. Now we should delete all auxilary LEXes and restore original
+    THD::lex (In this case sp_head::restore_thd_mem_root() was not called
+    too, so m_thd points to the current thread context).
+    It is safe to not update LEX::ptr because further query string parsing
+    and execution will be stopped anyway.
+  */
+  DBUG_ASSERT(m_lex.is_empty() || m_thd);
   while ((lex= (LEX *)m_lex.pop()))
   {
-    if (lex != &m_thd->main_lex) // We got interrupted and have lex'es left
-      delete lex;
+    delete m_thd->lex;
+    m_thd->lex= lex;
   }
-  if (m_sptabs.array.buffer)
-    hash_free(&m_sptabs);
+
+  hash_free(&m_sptabs);
+  hash_free(&m_spfuns);
+  hash_free(&m_spprocs);
   DBUG_VOID_RETURN;
 }
 
@@ -475,6 +491,11 @@ sp_head::execute(THD *thd)
   int ret= 0;
   uint ip= 0;
   Item_arena *old_arena;
+  query_id_t old_query_id;
+  TABLE *old_derived_tables;
+  LEX *old_lex;
+  Item_change_list old_change_list;
+  String old_packet;
 
 
 #ifndef EMBEDDED_LIBRARY
@@ -495,6 +516,34 @@ sp_head::execute(THD *thd)
   old_arena= thd->current_arena;
   thd->current_arena= this;
 
+  /*
+    We have to save/restore this info when we are changing call level to
+    be able properly do close_thread_tables() in instructions.
+  */
+  old_query_id= thd->query_id;
+  old_derived_tables= thd->derived_tables;
+  thd->derived_tables= 0;
+  /*
+    It is also more efficient to save/restore current thd->lex once when
+    do it in each instruction
+  */
+  old_lex= thd->lex;
+  /*
+    We should also save Item tree change list to avoid rollback something
+    too early in the calling query.
+  */
+  old_change_list= thd->change_list;
+  thd->change_list.empty();
+  /*
+    Cursors will use thd->packet, so they may corrupt data which was prepared
+    for sending by upper level. OTOH cursors in the same routine can share this
+    buffer safely so let use use routine-local packet instead of having own
+    packet buffer for each cursor.
+
+    It is probably safe to use same thd->convert_buff everywhere.
+  */
+  old_packet.swap(thd->packet);
+
   do
   {
     sp_instr *i;
@@ -506,7 +555,6 @@ sp_head::execute(THD *thd)
     DBUG_PRINT("execute", ("Instruction %u", ip));
     thd->set_time();		// Make current_time() et al work
     ret= i->execute(thd, &ip);
-    thd->rollback_item_tree_changes();
     if (i->free_list)
       cleanup_items(i->free_list);
     // Check if an exception has occurred and a handler has been found
@@ -535,6 +583,17 @@ sp_head::execute(THD *thd)
     }
   } while (ret == 0 && !thd->killed);
 
+  /* Restore all saved */
+  old_packet.swap(thd->packet);
+  DBUG_ASSERT(thd->change_list.is_empty());
+  thd->change_list= old_change_list;
+  /* To avoid wiping out thd->change_list on old_change_list destruction */
+  old_change_list.empty();
+  thd->lex= old_lex;
+  thd->query_id= old_query_id;
+  DBUG_ASSERT(!thd->derived_tables);
+  thd->derived_tables= old_derived_tables;
+
   cleanup_items(thd->current_arena->free_list);
   thd->current_arena= old_arena;
 
@@ -592,14 +651,6 @@ sp_head::execute_function(THD *thd, Item **argp, uint argcount, Item **resp)
       DBUG_RETURN(-1);
     }
   }
-#ifdef NOT_WORKING
-  /*
-    Close tables opened for subselect in argument list
-    This can't be done as this will close all other tables used
-    by the query.
-  */
-  close_thread_tables(thd);
-#endif
   // 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).
@@ -705,10 +756,6 @@ sp_head::execute_procedure(THD *thd, List<Item> *args)
 	  nctx->set_oindex(i, static_cast<Item_splocal *>(it)->get_offset());
       }
     }
-    // Clean up the joins before closing the tables.
-    thd->lex->unit.cleanup();
-    // Close tables opened for subselect in argument list
-    close_thread_tables(thd);
 
     // The rest of the frame are local variables which are all IN.
     // Default all variables to null (those with default clauses will
@@ -828,11 +875,17 @@ sp_head::restore_lex(THD *thd)
   oldlex->next_state= sublex->next_state;
   oldlex->trg_table_fields.push_back(&sublex->trg_table_fields);
 
-  // Collect some data from the sub statement lex.
-  sp_merge_hash(&oldlex->spfuns, &sublex->spfuns);
-  sp_merge_hash(&oldlex->spprocs, &sublex->spprocs);
-  // Merge used tables
-  sp_merge_table_list(thd, &m_sptabs, sublex->query_tables, sublex);
+  /*
+    Add routines which are used by statement to respective sets for
+    this routine
+  */
+  sp_merge_hash(&m_spfuns, &sublex->spfuns);
+  sp_merge_hash(&m_spprocs, &sublex->spprocs);
+  /*
+    Merge tables used by this statement (but not by its functions or
+    procedures) to multiset of tables used by this routine.
+  */
+  merge_table_list(thd, sublex->query_tables, sublex);
   if (! sublex->sp_lex_in_use)
     delete sublex;
   thd->lex= oldlex;
@@ -962,6 +1015,27 @@ sp_head::restore_thd_mem_root(THD *thd)
 }
 
 
+bool check_show_routine_acceess(THD *thd, sp_head *sp, bool *full_access)
+{
+  TABLE_LIST tables;
+  bzero((char*) &tables,sizeof(tables));
+  tables.db= (char*) "mysql";
+  tables.table_name= tables.alias= (char*) "proc";
+  *full_access= !check_table_access(thd, SELECT_ACL, &tables, 1);
+  if (!(*full_access))
+    *full_access= (!strcmp(sp->m_definer_user.str, thd->priv_user) &&
+                   !strcmp(sp->m_definer_host.str, thd->priv_host));
+  if (!(*full_access))
+  {
+#ifndef NO_EMBEDDED_ACCESS_CHECKS
+    return check_some_routine_access(thd, (char * )sp->m_db.str,
+                                     (char * ) sp->m_name.str);
+#endif
+  }
+  return 0;
+}
+
+
 int
 sp_head::show_create_procedure(THD *thd)
 {
@@ -974,11 +1048,15 @@ sp_head::show_create_procedure(THD *thd)
   sys_var *sql_mode_var;
   byte *sql_mode_str;
   ulong sql_mode_len;
+  bool full_access;
 
   DBUG_ENTER("sp_head::show_create_procedure");
   DBUG_PRINT("info", ("procedure %s", m_name.str));
   LINT_INIT(sql_mode_str);
   LINT_INIT(sql_mode_len);
+
+  if (check_show_routine_acceess(thd, this, &full_access))
+    return 1;
   
   old_sql_mode= thd->variables.sql_mode;
   thd->variables.sql_mode= m_sql_mode;
@@ -1005,7 +1083,8 @@ sp_head::show_create_procedure(THD *thd)
   protocol->store(m_name.str, m_name.length, system_charset_info);
   if (sql_mode_var)
     protocol->store((char*) sql_mode_str, sql_mode_len, system_charset_info);
-  protocol->store(m_defstr.str, m_defstr.length, system_charset_info);
+  if (full_access)
+    protocol->store(m_defstr.str, m_defstr.length, system_charset_info);
   res= protocol->write();
   send_eof(thd);
 
@@ -1043,11 +1122,15 @@ sp_head::show_create_function(THD *thd)
   sys_var *sql_mode_var;
   byte *sql_mode_str;
   ulong sql_mode_len;
+  bool full_access;
   DBUG_ENTER("sp_head::show_create_function");
   DBUG_PRINT("info", ("procedure %s", m_name.str));
   LINT_INIT(sql_mode_str);
   LINT_INIT(sql_mode_len);
 
+  if (check_show_routine_acceess(thd, this, &full_access))
+    return 1;
+
   old_sql_mode= thd->variables.sql_mode;
   thd->variables.sql_mode= m_sql_mode;
   sql_mode_var= find_sys_var("SQL_MODE", 8);
@@ -1072,7 +1155,8 @@ sp_head::show_create_function(THD *thd)
   protocol->store(m_name.str, m_name.length, system_charset_info);
   if (sql_mode_var)
     protocol->store((char*) sql_mode_str, sql_mode_len, system_charset_info);
-  protocol->store(m_defstr.str, m_defstr.length, system_charset_info);
+  if (full_access)
+    protocol->store(m_defstr.str, m_defstr.length, system_charset_info);
   res= protocol->write();
   send_eof(thd);
 
@@ -1134,22 +1218,121 @@ sp_head::opt_mark(uint ip)
 
 // ------------------------------------------------------------------
 
+
+/*
+  Prepare LEX and thread for execution of instruction, if requested open
+  and lock LEX's tables, execute instruction's core function, perform
+  cleanup afterwards.
+
+  SYNOPSIS
+    reset_lex_and_exec_core()
+      thd         - thread context
+      nextp       - out - next instruction
+      open_tables - if TRUE then check read access to tables in LEX's table
+                    list and open and lock them (used in instructions which
+                    need to calculate some expression and don't execute
+                    complete statement).
+      sp_instr    - instruction for which we prepare context, and which core
+                    function execute by calling its exec_core() method.
+
+  NOTE
+    We are not saving/restoring some parts of THD which may need this because
+    we do this once for whole routine execution in sp_head::execute().
+
+  RETURN VALUE
+    0/non-0 - Success/Failure
+*/
+
+int
+sp_lex_keeper::reset_lex_and_exec_core(THD *thd, uint *nextp,
+                                       bool open_tables, sp_instr* instr)
+{
+  int res= 0;
+
+  DBUG_ASSERT(!thd->derived_tables);
+  DBUG_ASSERT(thd->change_list.is_empty());
+  /*
+    Use our own lex.
+    We should not save old value since it is saved/restored in
+    sp_head::execute() when we are entering/leaving routine.
+  */
+  thd->lex= m_lex;
+
+  VOID(pthread_mutex_lock(&LOCK_thread_count));
+  thd->query_id= next_query_id();
+  VOID(pthread_mutex_unlock(&LOCK_thread_count));
+
+  /*
+    FIXME. Resetting statement (and using it) is not reentrant, thus recursive
+           functions which try to use the same LEX twice will crash server.
+           We should prevent such situations by tracking if LEX is already
+           in use and throwing error about unallowed recursion if needed.
+           OTOH it is nice to allow recursion in cases when LEX is not really
+           used (e.g. in mathematical functions), so such tracking should be
+           implemented at the same time as ability not to store LEX for
+           instruction if it is not really used.
+  */
+  reset_stmt_for_execute(thd, m_lex);
+
+  /*
+    If requested check whenever we have access to tables in LEX's table list
+    and open and lock them before executing instructtions core function.
+  */
+  if (open_tables &&
+      (check_table_access(thd, SELECT_ACL, m_lex->query_tables, 0) ||
+       open_and_lock_tables(thd, m_lex->query_tables)))
+      res= -1;
+
+  if (!res)
+    res= instr->exec_core(thd, nextp);
+
+  m_lex->unit.cleanup();
+
+  thd->proc_info="closing tables";
+  close_thread_tables(thd);
+
+  thd->rollback_item_tree_changes();
+
+  /*
+    Unlike for PS we should not call Item's destructors for newly created
+    items after execution of each instruction in stored routine. This is
+    because SP often create Item (like Item_int, Item_string etc...) when
+    they want to store some value in local variable, pass return value and
+    etc... So their life time should be longer than one instruction.
+
+    Probably we can call destructors for most of them then we are leaving
+    routine. But this won't help much as they are allocated in main query
+    MEM_ROOT anyway. So they all go to global thd->free_list.
+
+    May be we can use some other MEM_ROOT for this purprose ???
+
+    What else should we do for cleanup ?
+    cleanup_items() is called in sp_head::execute()
+  */
+  return res;
+}
+
+
 //
-// sp_instr_stmt
+// sp_instr
 //
-sp_instr_stmt::~sp_instr_stmt()
+int sp_instr::exec_core(THD *thd, uint *nextp)
 {
-  if (m_lex)
-    delete m_lex;
+  DBUG_ASSERT(0);
+  return 0;
 }
 
+
+//
+// sp_instr_stmt
+//
 int
 sp_instr_stmt::execute(THD *thd, uint *nextp)
 {
   char *query;
   uint32 query_length;
   DBUG_ENTER("sp_instr_stmt::execute");
-  DBUG_PRINT("info", ("command: %d", m_lex->sql_command));
+  DBUG_PRINT("info", ("command: %d", m_lex_keeper.sql_command()));
   int res;
 
   query= thd->query;
@@ -1159,13 +1342,14 @@ sp_instr_stmt::execute(THD *thd, uint *nextp)
     if (query_cache_send_result_to_client(thd,
 					  thd->query, thd->query_length) <= 0)
     {
-      res= exec_stmt(thd, m_lex);
+      res= m_lex_keeper.reset_lex_and_exec_core(thd, nextp, FALSE, this);
       query_cache_end_of_result(thd);
     }
+    else
+      *nextp= m_ip+1;
     thd->query= query;
     thd->query_length= query_length;
   }
-  *nextp = m_ip+1;
   DBUG_RETURN(res);
 }
 
@@ -1174,39 +1358,15 @@ sp_instr_stmt::print(String *str)
 {
   str->reserve(12);
   str->append("stmt ");
-  str->qs_append((uint)m_lex->sql_command);
+  str->qs_append((uint)m_lex_keeper.sql_command());
 }
 
 
 int
-sp_instr_stmt::exec_stmt(THD *thd, LEX *lex)
+sp_instr_stmt::exec_core(THD *thd, uint *nextp)
 {
-  LEX *olex;			// The other lex
-  int res;
-
-  olex= thd->lex;		// Save the other lex
-  thd->lex= lex;		// Use my own lex
-  thd->lex->thd = thd;		// QQ Not reentrant!
-  thd->lex->unit.thd= thd;	// QQ Not reentrant
-  thd->free_list= NULL;
-
-  VOID(pthread_mutex_lock(&LOCK_thread_count));
-  thd->query_id= next_query_id();
-  VOID(pthread_mutex_unlock(&LOCK_thread_count));
-
-  reset_stmt_for_execute(thd, lex);
-
-  res= mysql_execute_command(thd);
-
-  lex->unit.cleanup();
-  if (thd->lock || thd->open_tables || thd->derived_tables)
-  {
-    thd->proc_info="closing tables";
-    close_thread_tables(thd);			/* Free tables */
-  }
-
-  thd->lex= olex;		// Restore the other lex
-
+  int res= mysql_execute_command(thd);
+  *nextp= m_ip+1;
   return res;
 }
 
@@ -1218,14 +1378,16 @@ sp_instr_set::execute(THD *thd, uint *nextp)
 {
   DBUG_ENTER("sp_instr_set::execute");
   DBUG_PRINT("info", ("offset: %u", m_offset));
+
+  DBUG_RETURN(m_lex_keeper.reset_lex_and_exec_core(thd, nextp, TRUE, this));
+}
+
+int
+sp_instr_set::exec_core(THD *thd, uint *nextp)
+{
   Item *it;
   int res;
 
-  if (tables &&
-      ((res= check_table_access(thd, SELECT_ACL, tables, 0)) ||
-       (res= open_and_lock_tables(thd, tables))))
-    DBUG_RETURN(res);
-
   it= sp_eval_func_item(thd, m_value, m_type);
   if (! it)
     res= -1;
@@ -1235,9 +1397,8 @@ sp_instr_set::execute(THD *thd, uint *nextp)
     thd->spcont->set_item(m_offset, it);
   }
   *nextp = m_ip+1;
-  if (tables && (thd->lock || thd->open_tables || thd->derived_tables))
-    close_thread_tables(thd);
-  DBUG_RETURN(res);
+
+  return res;
 }
 
 void
@@ -1250,32 +1411,6 @@ sp_instr_set::print(String *str)
   m_value->print(str);
 }
 
-//
-// sp_instr_set_user_var
-//
-int
-sp_instr_set_user_var::execute(THD *thd, uint *nextp)
-{
-  int res= 0;
-
-  DBUG_ENTER("sp_instr_set_user_var::execute");
-  /*
-    It is ok to pass 0 as 3rd argument to fix_fields() since
-    Item_func_set_user_var::fix_fields() won't use it.
-    QQ: Still unsure what should we return in case of error 1 or -1 ?
-  */
-  if (!m_set_var_item.fixed && m_set_var_item.fix_fields(thd, 0, 0) ||
-      m_set_var_item.check() || m_set_var_item.update())
-    res= -1;
-  *nextp= m_ip + 1;
-  DBUG_RETURN(res);
-}
-
-void
-sp_instr_set_user_var::print(String *str)
-{
-  m_set_var_item.print_as_stmt(str);
-}
 
 //
 // sp_instr_set_trigger_field
@@ -1368,19 +1503,21 @@ sp_instr_jump::opt_move(uint dst, List<sp_instr> *bp)
 //
 // sp_instr_jump_if
 //
+
 int
 sp_instr_jump_if::execute(THD *thd, uint *nextp)
 {
   DBUG_ENTER("sp_instr_jump_if::execute");
   DBUG_PRINT("info", ("destination: %u", m_dest));
+  DBUG_RETURN(m_lex_keeper.reset_lex_and_exec_core(thd, nextp, TRUE, this));
+}
+
+int
+sp_instr_jump_if::exec_core(THD *thd, uint *nextp)
+{
   Item *it;
   int res;
 
-  if (tables &&
-      ((res= check_table_access(thd, SELECT_ACL, tables, 0)) ||
-       (res= open_and_lock_tables(thd, tables))))
-    DBUG_RETURN(res);
-
   it= sp_eval_func_item(thd, m_expr, MYSQL_TYPE_TINY);
   if (!it)
     res= -1;
@@ -1392,9 +1529,8 @@ sp_instr_jump_if::execute(THD *thd, uint *nextp)
     else
       *nextp = m_ip+1;
   }
-  if (tables && (thd->lock || thd->open_tables || thd->derived_tables))
-    close_thread_tables(thd);
-  DBUG_RETURN(res);
+
+  return res;
 }
 
 void
@@ -1430,14 +1566,16 @@ sp_instr_jump_if_not::execute(THD *thd, uint *nextp)
 {
   DBUG_ENTER("sp_instr_jump_if_not::execute");
   DBUG_PRINT("info", ("destination: %u", m_dest));
+  DBUG_RETURN(m_lex_keeper.reset_lex_and_exec_core(thd, nextp, TRUE, this));
+}
+
+
+int
+sp_instr_jump_if_not::exec_core(THD *thd, uint *nextp)
+{
   Item *it;
   int res;
 
-  if (tables &&
-      ((res= check_table_access(thd, SELECT_ACL, tables, 0)) ||
-       (res= open_and_lock_tables(thd, tables))))
-    DBUG_RETURN(res);
-
   it= sp_eval_func_item(thd, m_expr, MYSQL_TYPE_TINY);
   if (! it)
     res= -1;
@@ -1449,9 +1587,8 @@ sp_instr_jump_if_not::execute(THD *thd, uint *nextp)
     else
       *nextp = m_ip+1;
   }
-  if (tables && (thd->lock || thd->open_tables || thd->derived_tables))
-    close_thread_tables(thd);
-  DBUG_RETURN(res);
+
+  return res;
 }
 
 void
@@ -1482,18 +1619,21 @@ sp_instr_jump_if_not::opt_mark(sp_head *sp)
 //
 // sp_instr_freturn
 //
+
 int
 sp_instr_freturn::execute(THD *thd, uint *nextp)
 {
   DBUG_ENTER("sp_instr_freturn::execute");
+  DBUG_RETURN(m_lex_keeper.reset_lex_and_exec_core(thd, nextp, TRUE, this));
+}
+
+
+int
+sp_instr_freturn::exec_core(THD *thd, uint *nextp)
+{
   Item *it;
   int res;
 
-  if (tables &&
-      ((res= check_table_access(thd, SELECT_ACL, tables, 0)) ||
-       (res= open_and_lock_tables(thd, tables))))
-    DBUG_RETURN(res);
-
   it= sp_eval_func_item(thd, m_value, m_type);
   if (! it)
     res= -1;
@@ -1503,7 +1643,8 @@ sp_instr_freturn::execute(THD *thd, uint *nextp)
     thd->spcont->set_result(it);
   }
   *nextp= UINT_MAX;
-  DBUG_RETURN(res);
+
+  return res;
 }
 
 void
@@ -1637,17 +1778,11 @@ int
 sp_instr_cpush::execute(THD *thd, uint *nextp)
 {
   DBUG_ENTER("sp_instr_cpush::execute");
-  thd->spcont->push_cursor(m_lex);
+  thd->spcont->push_cursor(&m_lex_keeper);
   *nextp= m_ip+1;
   DBUG_RETURN(0);
 }
 
-sp_instr_cpush::~sp_instr_cpush()
-{
-  if (m_lex)
-    delete m_lex;
-}
-
 void
 sp_instr_cpush::print(String *str)
 {
@@ -1694,19 +1829,30 @@ sp_instr_copen::execute(THD *thd, uint *nextp)
     res= -1;
   else
   {
-    LEX *lex= c->pre_open(thd);
+    sp_lex_keeper *lex_keeper= c->pre_open(thd);
 
-    if (! lex)
+    if (!lex_keeper)
+    {
       res= -1;
+      *nextp= m_ip+1;
+    }
     else
-      res= exec_stmt(thd, lex);
-    c->post_open(thd, (lex ? TRUE : FALSE));
+      res= lex_keeper->reset_lex_and_exec_core(thd, nextp, FALSE, this);
+
+    c->post_open(thd, (lex_keeper ? TRUE : FALSE));
   }
 
-  *nextp= m_ip+1;
   DBUG_RETURN(res);
 }
 
+int
+sp_instr_copen::exec_core(THD *thd, uint *nextp)
+{
+  int res= mysql_execute_command(thd);
+  *nextp= m_ip+1;
+  return res;
+}
+
 void
 sp_instr_copen::print(String *str)
 {
@@ -1858,14 +2004,17 @@ sp_restore_security_context(THD *thd, sp_head *sp, st_sp_security_context *ctxp)
 #endif /* NO_EMBEDDED_ACCESS_CHECKS */
 
 /*
- *  Table merge hash table
- *
- */
+  Structure that represent all instances of one table
+  in optimized multi-set of tables used by routine.
+*/
+
 typedef struct st_sp_table
 {
   LEX_STRING qname;
   bool temp;
   TABLE_LIST *table;
+  uint lock_count;
+  uint query_lock_count;
 } SP_TABLE;
 
 byte *
@@ -1876,23 +2025,47 @@ sp_table_key(const byte *ptr, uint *plen, my_bool first)
   return (byte *)tab->qname.str;
 }
 
+
 /*
- *  Merge the table list into the hash table.
- *  If the optional lex is provided, it's used to check and set
- *  the flag for creation of a temporary table.
- */
+  Merge the list of tables used by some query into the multi-set of
+  tables used by routine.
+
+  SYNOPSIS
+    merge_table_list()
+      thd               - thread context
+      table             - table list
+      lex_for_tmp_check - LEX of the query for which we are merging
+                          table list.
+
+  NOTE
+    This method will use LEX provided to check whenever we are creating
+    temporary table and mark it as such in target multi-set.
+
+  RETURN VALUE
+    TRUE  - Success
+    FALSE - Error
+*/
+
 bool
-sp_merge_table_list(THD *thd, HASH *h, TABLE_LIST *table,
-		    LEX *lex_for_tmp_check)
+sp_head::merge_table_list(THD *thd, TABLE_LIST *table, LEX *lex_for_tmp_check)
 {
+  SP_TABLE *tab;
+
+  if (lex_for_tmp_check->sql_command == SQLCOM_DROP_TABLE &&
+      lex_for_tmp_check->drop_temporary)
+    return TRUE;
+
+  for (uint i= 0 ; i < m_sptabs.records ; i++)
+  {
+    tab= (SP_TABLE *)hash_element(&m_sptabs, i);
+    tab->query_lock_count= 0;
+  }
+
   for (; table ; table= table->next_global)
-    if (!table->derived &&
-	(!table->select_lex ||
-	 !(table->select_lex->options & OPTION_SCHEMA_TABLE)))
+    if (!table->derived && !table->schema_table)
     {
       char tname[64+1+64+1+64+1];	// db.table.alias\0
       uint tlen, alen;
-      SP_TABLE *tab;
 
       tlen= table->db_length;
       memcpy(tname, table->db, tlen);
@@ -1905,10 +2078,17 @@ sp_merge_table_list(THD *thd, HASH *h, TABLE_LIST *table,
       tlen+= alen;
       tname[tlen]= '\0';
 
-      if ((tab= (SP_TABLE *)hash_search(h, (byte *)tname, tlen)))
+      /*
+        It is safe to store pointer to table list elements in hash,
+        since they are supposed to have the same lifetime.
+      */
+      if ((tab= (SP_TABLE *)hash_search(&m_sptabs, (byte *)tname, tlen)))
       {
 	if (tab->table->lock_type < table->lock_type)
 	  tab->table= table;	// Use the table with the highest lock type
+        tab->query_lock_count++;
+        if (tab->query_lock_count > tab->lock_count)
+          tab->lock_count++;
       }
       else
       {
@@ -1918,152 +2098,102 @@ sp_merge_table_list(THD *thd, HASH *h, TABLE_LIST *table,
 	tab->qname.str= (char *)thd->strmake(tname, tab->qname.length);
 	if (!tab->qname.str)
 	  return FALSE;
-	if (lex_for_tmp_check &&
-	    lex_for_tmp_check->sql_command == SQLCOM_CREATE_TABLE &&
+	if (lex_for_tmp_check->sql_command == SQLCOM_CREATE_TABLE &&
 	    lex_for_tmp_check->query_tables == table &&
 	    lex_for_tmp_check->create_info.options & HA_LEX_CREATE_TMP_TABLE)
 	  tab->temp= TRUE;
 	tab->table= table;
-	my_hash_insert(h, (byte *)tab);
+        tab->lock_count= tab->query_lock_count= 1;
+	my_hash_insert(&m_sptabs, (byte *)tab);
       }
     }
   return TRUE;
 }
 
-void
-sp_merge_routine_tables(THD *thd, LEX *lex)
-{
-  uint i;
 
-  for (i= 0 ; i < lex->spfuns.records ; i++)
-  {
-    sp_head *sp;
-    LEX_STRING *ls= (LEX_STRING *)hash_element(&lex->spfuns, i);
-    sp_name name(*ls);
+/*
+  Add tables used by routine to the table list.
 
-    name.m_qname= *ls;
-    if ((sp= sp_cache_lookup(&thd->sp_func_cache, &name)))
-      sp_merge_table_hash(&lex->sptabs, &sp->m_sptabs);
-  }
-  for (i= 0 ; i < lex->spprocs.records ; i++)
-  {
-    sp_head *sp;
-    LEX_STRING *ls= (LEX_STRING *)hash_element(&lex->spprocs, i);
-    sp_name name(*ls);
+  SYNOPSIS
+    add_used_tables_to_table_list()
+      thd                   - thread context
+      query_tables_last_ptr - (in/out) pointer the next_global member of last
+                              element of the list where tables will be added
+                              (or to its root).
 
-    name.m_qname= *ls;
-    if ((sp= sp_cache_lookup(&thd->sp_proc_cache, &name)))
-      sp_merge_table_hash(&lex->sptabs, &sp->m_sptabs);
-  }
-}
+  DESCRIPTION
+    Converts multi-set of tables used by this routine to table list and adds
+    this list to the end of table list specified by 'query_tables_last_ptr'.
 
-void
-sp_merge_table_hash(HASH *hdst, HASH *hsrc)
-{
-  for (uint i=0 ; i < hsrc->records ; i++)
-  {
-    SP_TABLE *tabdst;
-    SP_TABLE *tabsrc= (SP_TABLE *)hash_element(hsrc, i);
+    Elements of list will be allocated in PS memroot, so this list will be
+    persistent between PS executions.
 
-    if (! (tabdst= (SP_TABLE *)hash_search(hdst,
-					   (byte *) tabsrc->qname.str,
-					   tabsrc->qname.length)))
-    {
-      my_hash_insert(hdst, (byte *)tabsrc);
-    }
-    else
-    {
-      if (tabdst->table->lock_type < tabsrc->table->lock_type)
-	tabdst->table= tabsrc->table; // Use the highest lock type
-    }
-  }
-}
+  RETURN VALUE
+    TRUE - if some elements were added, FALSE - otherwise.
+*/
 
-TABLE_LIST *
-sp_hash_to_table_list(THD *thd, HASH *h)
+bool
+sp_head::add_used_tables_to_table_list(THD *thd,
+                                       TABLE_LIST ***query_tables_last_ptr)
 {
   uint i;
-  TABLE_LIST *tables= NULL;
-  DBUG_ENTER("sp_hash_to_table_list");
+  Item_arena *arena, backup;
+  bool result= FALSE;
+  DBUG_ENTER("sp_head::add_used_tables_to_table_list");
+
+  /*
+    Use persistent arena for table list allocation to be PS friendly.
+  */
+  arena= thd->change_arena_if_needed(&backup);
 
-  for (i=0 ; i < h->records ; i++)
+  for (i=0 ; i < m_sptabs.records ; i++)
   {
-    SP_TABLE *stab= (SP_TABLE *)hash_element(h, i);
+    char *tab_buff;
+    TABLE_LIST *table, *otable;
+    SP_TABLE *stab= (SP_TABLE *)hash_element(&m_sptabs, i);
     if (stab->temp)
       continue;
-    TABLE_LIST *table, *otable= stab->table;
-
-    if (! (table= (TABLE_LIST *)thd->calloc(sizeof(TABLE_LIST))))
-      return NULL;
-    table->db= otable->db;
-    table->db_length= otable->db_length;
-    table->alias= otable->alias;
-    table->table_name= otable->table_name;
-    table->table_name_length= otable->table_name_length;
-    table->lock_type= otable->lock_type;
-    table->updating= otable->updating;
-    table->force_index= otable->force_index;
-    table->ignore_leaves= otable->ignore_leaves;
-    table->derived= otable->derived;
-    table->schema_table= otable->schema_table;
-    table->select_lex= otable->select_lex;
-    table->cacheable_table= otable->cacheable_table;
-    table->use_index= otable->use_index;
-    table->ignore_index= otable->ignore_index;
-    table->option= otable->option;
-
-    table->next_global= tables;
-    tables= table;
-  }
-  DBUG_RETURN(tables);
-}
 
-bool
-sp_open_and_lock_tables(THD *thd, TABLE_LIST *tables)
-{
-  DBUG_ENTER("sp_open_and_lock_tables");
-  bool ret;
+    otable= stab->table;
 
-  thd->in_lock_tables= 1;
-  thd->options|= OPTION_TABLE_LOCK;
-  if (simple_open_n_lock_tables(thd, tables))
-  {
-    thd->options&= ~(ulong)(OPTION_TABLE_LOCK);
-    ret= FALSE;
-  }
-  else
-  {
-#if 0
-    // QQ What about this?
-#ifdef HAVE_QUERY_CACHE
-    if (thd->variables.query_cache_wlock_invalidate)
-      query_cache.invalidate_locked_for_write(first_table); // QQ first_table?
-#endif /* HAVE_QUERY_CACHE */
-#endif
-    thd->locked_tables= thd->lock;
-    thd->lock= 0;
-    ret= TRUE;
-  }
-  thd->in_lock_tables= 0;
-  DBUG_RETURN(ret);
-}
+    if (!(tab_buff= (char *)thd->calloc(ALIGN_SIZE(sizeof(TABLE_LIST)) *
+                                        stab->lock_count)))
+      DBUG_RETURN(FALSE);
 
-void
-sp_unlock_tables(THD *thd)
-{
-  thd->lock= thd->locked_tables;
-  thd->locked_tables= 0;
-  close_thread_tables(thd);			// Free tables
-  if (thd->options & OPTION_TABLE_LOCK)
-  {
-#if 0
-    // QQ What about this?
-    end_active_trans(thd);
-#endif
-    thd->options&= ~(ulong)(OPTION_TABLE_LOCK);
+    for (uint j= 0; j < stab->lock_count; j++)
+    {
+      table= (TABLE_LIST *)tab_buff;
+
+      /*
+        It's enough to just copy the pointers as the data will not change
+        during the lifetime of the SP. If the SP is used by PS, we assume
+        that the PS will be invalidated if the functions is deleted or
+        changed.
+      */
+      table->db= otable->db;
+      table->db_length= otable->db_length;
+      table->alias= otable->alias;
+      table->table_name= otable->table_name;
+      table->table_name_length= otable->table_name_length;
+      table->lock_type= otable->lock_type;
+      table->cacheable_table= 1;
+      table->prelocking_placeholder= 1;
+
+      /* Everyting else should be zeroed */
+
+      **query_tables_last_ptr= table;
+      table->prev_global= *query_tables_last_ptr;
+      *query_tables_last_ptr= &table->next_global;
+
+      tab_buff+= ALIGN_SIZE(sizeof(TABLE_LIST));
+      result= TRUE;
+    }
   }
-  if (thd->global_read_lock)
-    unlock_global_read_lock(thd);
+
+  if (arena)
+    thd->restore_backup_item_arena(arena, &backup);
+
+  DBUG_RETURN(result);
 }
 
 /*
@@ -2095,3 +2225,73 @@ sp_add_to_query_tables(THD *thd, LEX *lex,
   lex->add_to_query_tables(table);
   return table;
 }
+
+
+/*
+  Auxilary function for adding tables used by routines used in query
+  to table lists.
+
+  SYNOPSIS
+    sp_add_sp_tables_to_table_list_aux()
+      thd       - thread context
+      lex       - LEX to which table list tables will be added
+      func_hash - routines for which tables should be added
+      func_cache- SP cache in which this routines should be looked up
+
+  NOTE
+    See sp_add_sp_tables_to_table_list() for more info.
+
+  RETURN VALUE
+    TRUE  - some tables were added
+    FALSE - no tables were added.
+*/
+
+static bool
+sp_add_sp_tables_to_table_list_aux(THD *thd, LEX *lex, HASH *func_hash,
+                                   sp_cache **func_cache)
+{
+  uint i;
+  bool result= FALSE;
+
+  for (i= 0 ; i < func_hash->records ; i++)
+  {
+    sp_head *sp;
+    LEX_STRING *ls= (LEX_STRING *)hash_element(func_hash, i);
+    sp_name name(*ls);
+
+    name.m_qname= *ls;
+    if ((sp= sp_cache_lookup(func_cache, &name)))
+      result|= sp->add_used_tables_to_table_list(thd, &lex->query_tables_last);
+  }
+
+  return result;
+}
+
+
+/*
+  Add tables used by routines used in query to table list.
+
+  SYNOPSIS
+    sp_add_sp_tables_to_table_list()
+      thd      - thread context
+      lex      - LEX to which table list tables will be added
+      func_lex - LEX for which functions we get tables
+                 (useful for adding tables used by view routines)
+
+  NOTE
+    Elements of list will be allocated in PS memroot, so this
+    list will be persistent between PS execetutions.
+
+  RETURN VALUE
+    TRUE  - some tables were added
+    FALSE - no tables were added.
+*/
+
+bool
+sp_add_sp_tables_to_table_list(THD *thd, LEX *lex, LEX *func_lex)
+{
+  return (sp_add_sp_tables_to_table_list_aux(thd, lex, &func_lex->spfuns,
+                                             &thd->sp_func_cache) |
+          sp_add_sp_tables_to_table_list_aux(thd, lex, &func_lex->spprocs,
+                                             &thd->sp_proc_cache));
+}
diff --git a/sql/sp_head.h b/sql/sp_head.h
index 5df9c753048..60979a438cb 100644
--- a/sql/sp_head.h
+++ b/sql/sp_head.h
@@ -103,7 +103,14 @@ public:
   LEX_STRING m_definer_host;
   longlong m_created;
   longlong m_modified;
-  HASH m_sptabs;		/* Merged table lists */
+  /*
+    Sets containing names of SP and SF used by this routine.
+
+    TODO Probably we should combine these two hashes in one. It will
+    decrease memory overhead ans simplify algorithms using them. The
+    same applies to similar hashes in LEX.
+  */
+  HASH m_spfuns, m_spprocs;
   // Pointers set during parsing
   uchar *m_param_begin, *m_param_end, *m_returns_begin, *m_returns_end,
     *m_body_begin;
@@ -225,6 +232,10 @@ public:
     return ip;
   }
 
+  /* Add tables used by routine to the table list. */
+  bool add_used_tables_to_table_list(THD *thd,
+                                     TABLE_LIST ***query_tables_last_ptr);
+
 private:
 
   MEM_ROOT *m_thd_root;		// Temp. store for thd's mem_root
@@ -240,10 +251,20 @@ private:
     sp_instr *instr;
   } bp_t;
   List<bp_t> m_backpatch;	// Instructions needing backpatching
+  /*
+    Multi-set representing optimized list of tables to be locked by this
+    routine. Does not include tables which are used by invoked routines.
+  */
+  HASH m_sptabs;
 
   int
   execute(THD *thd);
 
+  /*
+    Merge the list of tables used by query into the multi-set of tables used
+    by routine.
+  */
+  bool merge_table_list(THD *thd, TABLE_LIST *table, LEX *lex_for_tmp_check);
 }; // class sp_head : public Sql_alloc
 
 
@@ -277,6 +298,17 @@ public:
   // Returns 0 on success, non-zero if some error occured.
   virtual int execute(THD *thd, uint *nextp) = 0;
 
+  /*
+    Execute core function of instruction after all preparations (e.g.
+    setting of proper LEX, saving part of the thread context have been
+    done).
+
+    Should be implemented for instructions using expressions or whole
+    statements (thus having to have own LEX). Used in concert with
+    sp_lex_keeper class and its descendants.
+  */
+  virtual int exec_core(THD *thd, uint *nextp);
+
   virtual void print(String *str) = 0;
 
   virtual void backpatch(uint dest, sp_pcontext *dst_ctx)
@@ -301,6 +333,60 @@ public:
 }; // class sp_instr : public Sql_alloc
 
 
+/*
+  Auxilary class to which instructions delegate responsibility
+  for handling LEX and preparations before executing statement
+  or calculating complex expression.
+
+  Exist mainly to avoid having double hierarchy between instruction
+  classes.
+
+  TODO: Add ability to not store LEX and do any preparations if
+        expression used is simple.
+*/
+
+class sp_lex_keeper
+{
+  /* Prevent use of these */
+  sp_lex_keeper(const sp_lex_keeper &);
+  void operator=(sp_lex_keeper &);
+public:
+
+  sp_lex_keeper(LEX *lex, bool lex_resp)
+    : m_lex(lex), m_lex_resp(lex_resp)
+  {
+    lex->sp_lex_in_use= TRUE;
+  }
+  virtual ~sp_lex_keeper()
+  {
+    if (m_lex_resp)
+      delete m_lex;
+  }
+
+  /*
+    Prepare execution of instruction using LEX, if requested check whenever
+    we have read access to tables used and open/lock them, call instruction's
+    exec_core() method, perform cleanup afterwards.
+  */
+  int reset_lex_and_exec_core(THD *thd, uint *nextp, bool open_tables,
+                              sp_instr* instr);
+
+  inline uint sql_command() const
+  {
+    return (uint)m_lex->sql_command;
+  }
+
+private:
+
+  LEX *m_lex;
+  /*
+    Indicates whenever this sp_lex_keeper instance responsible
+    for LEX deletion.
+  */
+  bool m_lex_resp;
+};
+
+
 //
 // Call out to some prepared SQL statement.
 //
@@ -313,38 +399,25 @@ public:
 
   LEX_STRING m_query;		// For thd->query
 
-  sp_instr_stmt(uint ip, sp_pcontext *ctx)
-    : sp_instr(ip, ctx), m_lex(NULL)
+  sp_instr_stmt(uint ip, sp_pcontext *ctx, LEX *lex)
+    : sp_instr(ip, ctx), m_lex_keeper(lex, TRUE)
   {
     m_query.str= 0;
     m_query.length= 0;
   }
 
-  virtual ~sp_instr_stmt();
+  virtual ~sp_instr_stmt()
+  {};
 
   virtual int execute(THD *thd, uint *nextp);
 
-  virtual void print(String *str);
-
-  inline void
-  set_lex(LEX *lex)
-  {
-    m_lex= lex;
-  }
-
-  inline LEX *
-  get_lex()
-  {
-    return m_lex;
-  }
+  virtual int exec_core(THD *thd, uint *nextp);
 
-protected:
-
-  int exec_stmt(THD *thd, LEX *lex); // Execute a statement
+  virtual void print(String *str);
 
 private:
 
-  LEX *m_lex;			// My own lex
+  sp_lex_keeper m_lex_keeper;
 
 }; // class sp_instr_stmt : public sp_instr
 
@@ -356,12 +429,11 @@ class sp_instr_set : public sp_instr
 
 public:
 
-  TABLE_LIST *tables;
-
   sp_instr_set(uint ip, sp_pcontext *ctx,
-	       uint offset, Item *val, enum enum_field_types type)
-    : sp_instr(ip, ctx),
-      tables(NULL), m_offset(offset), m_value(val), m_type(type)
+	       uint offset, Item *val, enum enum_field_types type,
+               LEX *lex, bool lex_resp)
+    : sp_instr(ip, ctx), m_offset(offset), m_value(val), m_type(type),
+      m_lex_keeper(lex, lex_resp)
   {}
 
   virtual ~sp_instr_set()
@@ -369,6 +441,8 @@ public:
 
   virtual int execute(THD *thd, uint *nextp);
 
+  virtual int exec_core(THD *thd, uint *nextp);
+
   virtual void print(String *str);
 
 private:
@@ -376,42 +450,12 @@ private:
   uint m_offset;		// Frame offset
   Item *m_value;
   enum enum_field_types m_type;	// The declared type
+  sp_lex_keeper m_lex_keeper;
 
 }; // class sp_instr_set : public sp_instr
 
 
 /*
-  Set user variable instruction.
-  Used in functions and triggers to set user variables because we don't
-  want use sp_instr_stmt + "SET @a:=..." statement in this case since
-  latter will close all tables and thus will ruin execution of statement
-  calling/invoking this function/trigger.
-*/
-class sp_instr_set_user_var : public sp_instr
-{
-  sp_instr_set_user_var(const sp_instr_set_user_var &);
-  void operator=(sp_instr_set_user_var &);
-
-public:
-
-  sp_instr_set_user_var(uint ip, sp_pcontext *ctx, LEX_STRING var, Item *val)
-    : sp_instr(ip, ctx), m_set_var_item(var, val)
-  {}
-
-  virtual ~sp_instr_set_user_var()
-  {}
-
-  virtual int execute(THD *thd, uint *nextp);
-
-  virtual void print(String *str);
-
-private:
-
-  Item_func_set_user_var m_set_var_item;
-}; // class sp_instr_set_user_var : public sp_instr
-
-
-/*
   Set NEW/OLD row field value instruction. Used in triggers.
 */
 class sp_instr_set_trigger_field : public sp_instr
@@ -492,14 +536,12 @@ class sp_instr_jump_if : public sp_instr_jump
 
 public:
 
-  TABLE_LIST *tables;
-
-  sp_instr_jump_if(uint ip, sp_pcontext *ctx, Item *i)
-    : sp_instr_jump(ip, ctx), tables(NULL), m_expr(i)
+  sp_instr_jump_if(uint ip, sp_pcontext *ctx, Item *i, LEX *lex)
+    : sp_instr_jump(ip, ctx), m_expr(i), m_lex_keeper(lex, TRUE)
   {}
 
-  sp_instr_jump_if(uint ip, sp_pcontext *ctx, Item *i, uint dest)
-    : sp_instr_jump(ip, ctx, dest), tables(NULL), m_expr(i)
+  sp_instr_jump_if(uint ip, sp_pcontext *ctx, Item *i, uint dest, LEX *lex)
+    : sp_instr_jump(ip, ctx, dest), m_expr(i), m_lex_keeper(lex, TRUE)
   {}
 
   virtual ~sp_instr_jump_if()
@@ -507,6 +549,8 @@ public:
 
   virtual int execute(THD *thd, uint *nextp);
 
+  virtual int exec_core(THD *thd, uint *nextp);
+
   virtual void print(String *str);
 
   virtual uint opt_mark(sp_head *sp);
@@ -519,6 +563,7 @@ public:
 private:
 
   Item *m_expr;			// The condition
+  sp_lex_keeper m_lex_keeper;
 
 }; // class sp_instr_jump_if : public sp_instr_jump
 
@@ -530,14 +575,12 @@ class sp_instr_jump_if_not : public sp_instr_jump
 
 public:
 
-  TABLE_LIST *tables;
-
-  sp_instr_jump_if_not(uint ip, sp_pcontext *ctx, Item *i)
-    : sp_instr_jump(ip, ctx), tables(NULL), m_expr(i)
+  sp_instr_jump_if_not(uint ip, sp_pcontext *ctx, Item *i, LEX *lex)
+    : sp_instr_jump(ip, ctx), m_expr(i), m_lex_keeper(lex, TRUE)
   {}
 
-  sp_instr_jump_if_not(uint ip, sp_pcontext *ctx, Item *i, uint dest)
-    : sp_instr_jump(ip, ctx, dest), tables(NULL), m_expr(i)
+  sp_instr_jump_if_not(uint ip, sp_pcontext *ctx, Item *i, uint dest, LEX *lex)
+    : sp_instr_jump(ip, ctx, dest), m_expr(i), m_lex_keeper(lex, TRUE)
   {}
 
   virtual ~sp_instr_jump_if_not()
@@ -545,6 +588,8 @@ public:
 
   virtual int execute(THD *thd, uint *nextp);
 
+  virtual int exec_core(THD *thd, uint *nextp);
+
   virtual void print(String *str);
 
   virtual uint opt_mark(sp_head *sp);
@@ -557,6 +602,7 @@ public:
 private:
 
   Item *m_expr;			// The condition
+  sp_lex_keeper m_lex_keeper;
 
 }; // class sp_instr_jump_if_not : public sp_instr_jump
 
@@ -568,11 +614,9 @@ class sp_instr_freturn : public sp_instr
 
 public:
 
-  TABLE_LIST *tables;
-
   sp_instr_freturn(uint ip, sp_pcontext *ctx,
-		   Item *val, enum enum_field_types type)
-    : sp_instr(ip, ctx), tables(NULL), m_value(val), m_type(type)
+		   Item *val, enum enum_field_types type, LEX *lex)
+    : sp_instr(ip, ctx), m_value(val), m_type(type), m_lex_keeper(lex, TRUE)
   {}
 
   virtual ~sp_instr_freturn()
@@ -580,6 +624,8 @@ public:
 
   virtual int execute(THD *thd, uint *nextp);
 
+  virtual int exec_core(THD *thd, uint *nextp);
+
   virtual void print(String *str);
 
   virtual uint opt_mark(sp_head *sp)
@@ -592,6 +638,7 @@ protected:
 
   Item *m_value;
   enum enum_field_types m_type;
+  sp_lex_keeper m_lex_keeper;
 
 }; // class sp_instr_freturn : public sp_instr
 
@@ -710,10 +757,11 @@ class sp_instr_cpush : public sp_instr
 public:
 
   sp_instr_cpush(uint ip, sp_pcontext *ctx, LEX *lex)
-    : sp_instr(ip, ctx), m_lex(lex)
+    : sp_instr(ip, ctx), m_lex_keeper(lex, TRUE)
   {}
 
-  virtual ~sp_instr_cpush();
+  virtual ~sp_instr_cpush()
+  {}
 
   virtual int execute(THD *thd, uint *nextp);
 
@@ -721,7 +769,7 @@ public:
 
 private:
 
-  LEX *m_lex;
+  sp_lex_keeper m_lex_keeper;
 
 }; // class sp_instr_cpush : public sp_instr
 
@@ -760,7 +808,7 @@ private:
 }; // class sp_instr_cpop : public sp_instr
 
 
-class sp_instr_copen : public sp_instr_stmt
+class sp_instr_copen : public sp_instr
 {
   sp_instr_copen(const sp_instr_copen &); /* Prevent use of these */
   void operator=(sp_instr_copen &);
@@ -768,7 +816,7 @@ class sp_instr_copen : public sp_instr_stmt
 public:
 
   sp_instr_copen(uint ip, sp_pcontext *ctx, uint c)
-    : sp_instr_stmt(ip, ctx), m_cursor(c)
+    : sp_instr(ip, ctx), m_cursor(c)
   {}
 
   virtual ~sp_instr_copen()
@@ -776,6 +824,8 @@ public:
 
   virtual int execute(THD *thd, uint *nextp);
 
+  virtual int exec_core(THD *thd, uint *nextp);
+
   virtual void print(String *str);
 
 private:
@@ -893,22 +943,11 @@ void
 sp_restore_security_context(THD *thd, sp_head *sp,st_sp_security_context *ctxp);
 #endif /* NO_EMBEDDED_ACCESS_CHECKS */
 
-bool
-sp_merge_table_list(THD *thd, HASH *h, TABLE_LIST *table,
-		    LEX *lex_for_tmp_check = 0);
-void
-sp_merge_routine_tables(THD *thd, LEX *lex);
-void
-sp_merge_table_hash(HASH *hdst, HASH *hsrc);
-TABLE_LIST *
-sp_hash_to_table_list(THD *thd, HASH *h);
-bool
-sp_open_and_lock_tables(THD *thd, TABLE_LIST *tables);
-void
-sp_unlock_tables(THD *thd);
 TABLE_LIST *
 sp_add_to_query_tables(THD *thd, LEX *lex,
 		       const char *db, const char *name,
 		       thr_lock_type locktype);
+bool
+sp_add_sp_tables_to_table_list(THD *thd, LEX *lex, LEX *func_lex);
 
 #endif /* _SP_HEAD_H_ */
diff --git a/sql/sp_rcontext.cc b/sql/sp_rcontext.cc
index 5b177650726..9b29c173856 100644
--- a/sql/sp_rcontext.cc
+++ b/sql/sp_rcontext.cc
@@ -125,9 +125,9 @@ sp_rcontext::restore_variables(uint fp)
 }
 
 void
-sp_rcontext::push_cursor(LEX *lex)
+sp_rcontext::push_cursor(sp_lex_keeper *lex_keeper)
 {
-  m_cstack[m_ccount++]= new sp_cursor(lex);
+  m_cstack[m_ccount++]= new sp_cursor(lex_keeper);
 }
 
 void
@@ -148,7 +148,7 @@ sp_rcontext::pop_cursors(uint count)
 
 // We have split this in two to make it easy for sp_instr_copen
 // to reuse the sp_instr::exec_stmt() code.
-LEX *
+sp_lex_keeper*
 sp_cursor::pre_open(THD *thd)
 {
   if (m_isopen)
@@ -168,7 +168,7 @@ sp_cursor::pre_open(THD *thd)
 
   m_nseof= thd->net.no_send_eof;
   thd->net.no_send_eof= TRUE;
-  return m_lex;
+  return m_lex_keeper;
 }
 
 void
diff --git a/sql/sp_rcontext.h b/sql/sp_rcontext.h
index 8e818ab76d1..37d718048a0 100644
--- a/sql/sp_rcontext.h
+++ b/sql/sp_rcontext.h
@@ -25,6 +25,7 @@
 struct sp_cond_type;
 class sp_cursor;
 struct sp_pvar;
+class sp_lex_keeper;
 
 #define SP_HANDLER_NONE      0
 #define SP_HANDLER_EXIT      1
@@ -164,7 +165,7 @@ class sp_rcontext : public Sql_alloc
   restore_variables(uint fp);
 
   void
-  push_cursor(LEX *lex);
+  push_cursor(sp_lex_keeper *lex_keeper);
 
   void
   pop_cursors(uint count);
@@ -207,8 +208,8 @@ class sp_cursor : public Sql_alloc
 {
 public:
 
-  sp_cursor(LEX *lex)
-    : m_lex(lex), m_prot(NULL), m_isopen(0), m_current_row(NULL)
+  sp_cursor(sp_lex_keeper *lex_keeper)
+    : m_lex_keeper(lex_keeper), m_prot(NULL), m_isopen(0), m_current_row(NULL)
   {
     /* Empty */
   }
@@ -220,7 +221,7 @@ public:
 
   // We have split this in two to make it easy for sp_instr_copen
   // to reuse the sp_instr::exec_stmt() code.
-  LEX *
+  sp_lex_keeper *
   pre_open(THD *thd);
   void
   post_open(THD *thd, my_bool was_opened);
@@ -240,7 +241,7 @@ public:
 private:
 
   MEM_ROOT m_mem_root;		// My own mem_root
-  LEX *m_lex;
+  sp_lex_keeper *m_lex_keeper;
   Protocol_cursor *m_prot;
   my_bool m_isopen;
   my_bool m_nseof;		// Original no_send_eof
diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc
index 87c83771ec8..3db219b5fdc 100644
--- a/sql/sql_acl.cc
+++ b/sql/sql_acl.cc
@@ -138,7 +138,6 @@ my_bool acl_init(THD *org_thd, bool dont_read_acl_tables)
   TABLE_LIST tables[3];
   TABLE *table;
   READ_RECORD read_record_info;
-  MYSQL_LOCK *lock;
   my_bool return_val=1;
   bool check_no_resolve= specialflag & SPECIAL_NO_RESOLVE;
   char tmp_name[NAME_LEN+1];
@@ -176,20 +175,9 @@ my_bool acl_init(THD *org_thd, bool dont_read_acl_tables)
   tables[0].lock_type=tables[1].lock_type=tables[2].lock_type=TL_READ;
   tables[0].db=tables[1].db=tables[2].db=thd->db;
 
-  uint counter;
-  if (open_tables(thd, tables, &counter))
-  {
-    sql_print_error("Fatal error: Can't open privilege tables: %s",
-		    thd->net.last_error);
-    goto end;
-  }
-  TABLE *ptr[3];				// Lock tables for quick update
-  ptr[0]= tables[0].table;
-  ptr[1]= tables[1].table;
-  ptr[2]= tables[2].table;
-  if (!(lock=mysql_lock_tables(thd,ptr,3)))
+  if (simple_open_n_lock_tables(thd, tables))
   {
-    sql_print_error("Fatal error: Can't lock privilege tables: %s",
+    sql_print_error("Fatal error: Can't open and lock privilege tables: %s",
 		    thd->net.last_error);
     goto end;
   }
@@ -459,7 +447,6 @@ my_bool acl_init(THD *org_thd, bool dont_read_acl_tables)
   freeze_size(&acl_dbs);
   init_check_host();
 
-  mysql_unlock_tables(thd, lock);
   initialized=1;
   thd->version--;				// Force close to free memory
   return_val=0;
@@ -3112,7 +3099,6 @@ my_bool grant_init(THD *org_thd)
 {
   THD  *thd;
   TABLE_LIST tables[3];
-  MYSQL_LOCK *lock;
   MEM_ROOT *memex_ptr;
   my_bool return_val= 1;
   TABLE *t_table, *c_table, *p_table;
@@ -3146,15 +3132,7 @@ my_bool grant_init(THD *org_thd)
   tables[0].lock_type=tables[1].lock_type=tables[2].lock_type=TL_READ;
   tables[0].db=tables[1].db=tables[2].db=thd->db;
 
-  uint counter;
-  if (open_tables(thd, tables, &counter))
-    goto end;
-
-  TABLE *ptr[3];				// Lock tables for quick update
-  ptr[0]= tables[0].table;
-  ptr[1]= tables[1].table;
-  ptr[2]= tables[2].table;
-  if (!(lock=mysql_lock_tables(thd,ptr,3)))
+  if (simple_open_n_lock_tables(thd, tables))
     goto end;
 
   t_table = tables[0].table; c_table = tables[1].table;
@@ -3244,7 +3222,6 @@ my_bool grant_init(THD *org_thd)
 end_unlock:
   t_table->file->ha_index_end();
   p_table->file->ha_index_end();
-  mysql_unlock_tables(thd, lock);
   thd->version--;				// Force close to free memory
 
 end:
@@ -3606,6 +3583,37 @@ err:
 }
 
 
+/*
+  Check if routine has any of the 
+  procedure level grants
+  
+  SYNPOSIS
+   bool    check_routine_level_acl()
+   thd	        Thread handler
+   db           Database name
+   name         Routine name
+
+  RETURN
+   1            error
+   0            Ok 
+*/
+
+bool check_routine_level_acl(THD *thd, char *db, char *name)
+{
+  bool no_routine_acl= 1;
+  if (grant_option)
+  {
+    GRANT_NAME *grant_proc;
+    rw_rdlock(&LOCK_grant);
+    if ((grant_proc= proc_hash_search(thd->priv_host, thd->ip, db,
+                                      thd->priv_user, name, 0)))
+      no_routine_acl= !(grant_proc->privs & SHOW_PROC_ACLS);
+    rw_unlock(&LOCK_grant);
+  }
+  return no_routine_acl;
+}
+
+
 /*****************************************************************************
   Functions to retrieve the grant for a table/column  (for SHOW functions)
 *****************************************************************************/
diff --git a/sql/sql_acl.h b/sql/sql_acl.h
index 3a9df84a35d..30e335c7afd 100644
--- a/sql/sql_acl.h
+++ b/sql/sql_acl.h
@@ -63,6 +63,9 @@
 #define PROC_ACLS \
 (ALTER_PROC_ACL | EXECUTE_ACL | GRANT_ACL)
 
+#define SHOW_PROC_ACLS \
+(ALTER_PROC_ACL | EXECUTE_ACL | CREATE_PROC_ACL)
+
 #define GLOBAL_ACLS \
 (SELECT_ACL | INSERT_ACL | UPDATE_ACL | DELETE_ACL | CREATE_ACL | DROP_ACL | \
  RELOAD_ACL | SHUTDOWN_ACL | PROCESS_ACL | FILE_ACL | GRANT_ACL | \
@@ -216,6 +219,7 @@ void fill_effective_table_privileges(THD *thd, GRANT_INFO *grant,
                                      const char *db, const char *table);
 bool sp_revoke_privileges(THD *thd, const char *sp_db, const char *sp_name);
 bool sp_grant_privileges(THD *thd, const char *sp_db, const char *sp_name);
+bool check_routine_level_acl(THD *thd, char *db, char *name);
 
 #ifdef NO_EMBEDDED_ACCESS_CHECKS
 #define check_grant(A,B,C,D,E,F) 0
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 530ef567aaa..4fd943c08f4 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -20,6 +20,7 @@
 #include "mysql_priv.h"
 #include "sql_select.h"
 #include "sp_head.h"
+#include "sp.h"
 #include "sql_trigger.h"
 #include <m_ctype.h>
 #include <my_dir.h>
@@ -359,7 +360,30 @@ bool close_cached_tables(THD *thd, bool if_wait_for_refresh,
 
 
 /*
-  Close all tables used by thread
+  Mark all tables in the list which were used by current substatement
+  as free for reuse.
+
+  SYNOPSIS
+    mark_used_tables_as_free_for_reuse()
+      thd   - thread context
+      table - head of the list of tables
+
+  DESCRIPTION
+    Marks all tables in the list which were used by current substatement
+    (they are marked by its query_id) as free for reuse.
+*/
+
+static void mark_used_tables_as_free_for_reuse(THD *thd, TABLE *table)
+{
+  for (; table ; table= table->next)
+    if (table->query_id == thd->query_id)
+      table->query_id= 0;
+}
+
+
+/*
+  Close all tables used by the current substatement, or all tables
+  used by this thread if we are on the upper level.
 
   SYNOPSIS
     close_thread_tables()
@@ -372,14 +396,31 @@ bool close_cached_tables(THD *thd, bool if_wait_for_refresh,
   IMPLEMENTATION
     Unlocks tables and frees derived tables.
     Put all normal tables used by thread in free list.
+
+    When in prelocked mode it will only close/mark as free for reuse
+    tables opened by this substatement, it will also check if we are
+    closing tables after execution of complete query (i.e. we are on
+    upper level) and will leave prelocked mode if needed.
 */
 
 void close_thread_tables(THD *thd, bool lock_in_use, bool skip_derived, 
                          TABLE *stopper)
 {
   bool found_old_table;
+  prelocked_mode_type prelocked_mode= thd->prelocked_mode;
   DBUG_ENTER("close_thread_tables");
 
+  /*
+    We are assuming here that thd->derived_tables contains ONLY derived
+    tables for this substatement. i.e. instead of approach which uses
+    query_id matching for determining which of the derived tables belong
+    to this substatement we rely on the ability of substatements to
+    save/restore thd->derived_tables during their execution.
+
+    TODO: Probably even better approach is to simply associate list of
+          derived tables with (sub-)statement instead of thread and destroy
+          them at the end of its execution.
+  */
   if (thd->derived_tables && !skip_derived)
   {
     TABLE *table, *next;
@@ -394,10 +435,50 @@ void close_thread_tables(THD *thd, bool lock_in_use, bool skip_derived,
     }
     thd->derived_tables= 0;
   }
-  if (thd->locked_tables)
+
+  if (prelocked_mode)
   {
-    ha_commit_stmt(thd);			// If select statement
-    DBUG_VOID_RETURN;				// LOCK TABLES in use
+    /*
+      Mark all temporary tables used by this substatement as free for reuse.
+    */
+    mark_used_tables_as_free_for_reuse(thd, thd->temporary_tables);
+  }
+
+  if (thd->locked_tables || prelocked_mode)
+  {
+    /*
+      TODO: It is not 100% clear whenever we should do ha_commit_stmt() for
+            sub-statements. This issue needs additional investigation.
+    */
+    ha_commit_stmt(thd);
+
+    /* We are under simple LOCK TABLES so should not do anything else. */
+    if (!prelocked_mode)
+      DBUG_VOID_RETURN;
+
+    if (!thd->lex->requires_prelocking())
+    {
+      /*
+        If we are executing one of substatements we have to mark
+        all tables which it used as free for reuse.
+      */
+      mark_used_tables_as_free_for_reuse(thd, thd->open_tables);
+      DBUG_VOID_RETURN;
+    }
+
+    DBUG_ASSERT(prelocked_mode);
+    /*
+      We are in prelocked mode, so we have to leave it now with doing
+      implicit UNLOCK TABLES if need.
+    */
+    thd->prelocked_mode= NON_PRELOCKED;
+
+    if (prelocked_mode == PRELOCKED_UNDER_LOCK_TABLES)
+      DBUG_VOID_RETURN;
+
+    thd->lock= thd->locked_tables;
+    thd->locked_tables= 0;
+    /* Fallthrough */
   }
 
   if (thd->lock)
@@ -441,6 +522,17 @@ void close_thread_tables(THD *thd, bool lock_in_use, bool skip_derived,
   if (!lock_in_use)
     VOID(pthread_mutex_unlock(&LOCK_open));
   /*  VOID(pthread_sigmask(SIG_SETMASK,&thd->signals,NULL)); */
+
+  if (prelocked_mode == PRELOCKED)
+  {
+    /*
+      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());
+    thd->options&= ~(ulong) (OPTION_TABLE_LOCK);
+  }
+
   DBUG_VOID_RETURN;
 }
 
@@ -910,7 +1002,8 @@ TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root,
 	  !memcmp(table->s->table_cache_key, key,
 		  key_length + TMP_TABLE_KEY_EXTRA))
       {
-	if (table->query_id == thd->query_id)
+	if (table->query_id == thd->query_id ||
+            thd->prelocked_mode && table->query_id)
 	{
 	  my_error(ER_CANT_REOPEN_TABLE, MYF(0), table->alias);
 	  DBUG_RETURN(0);
@@ -924,16 +1017,17 @@ TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT *mem_root,
     }
   }
 
-  if (thd->locked_tables)
+  if (thd->locked_tables || thd->prelocked_mode)
   {						// Using table locks
     for (table=thd->open_tables; table ; table=table->next)
     {
       if (table->s->key_length == key_length &&
-	  !memcmp(table->s->table_cache_key,key,key_length) &&
-	  !my_strcasecmp(system_charset_info, table->alias, alias))
+          !memcmp(table->s->table_cache_key, key, key_length) &&
+          !my_strcasecmp(system_charset_info, table->alias, alias) &&
+          table->query_id != thd->query_id && /* skip tables already used by this query */
+          !(thd->prelocked_mode && table->query_id))
       {
-	if (table->query_id != thd->query_id)
-	  table->query_id=thd->query_id;
+        table->query_id= thd->query_id;
         DBUG_PRINT("info",("Using locked table"));
 	goto reset;
       }
@@ -1625,21 +1719,34 @@ err:
   SYNOPSIS
     open_tables()
     thd - thread handler
-    start - list of tables
+    start - list of tables in/out
     counter - number of opened tables will be return using this parameter
 
+  NOTE
+    Unless we are already in prelocked mode, this function will also precache
+    all SP/SFs explicitly or implicitly (via views and triggers) used by the
+    query and add tables needed for their execution to table list. If resulting
+    tables list will be non empty it will mark query as requiring precaching.
+    Prelocked mode will be enabled for such query during lock_tables() call.
+
+    If query for which we are opening tables is already marked as requiring
+    prelocking it won't do such precaching and will simply reuse table list
+    which is already built.
+
   RETURN
     0  - OK
     -1 - error
 */
 
-int open_tables(THD *thd, TABLE_LIST *start, uint *counter)
+int open_tables(THD *thd, TABLE_LIST **start, uint *counter)
 {
   TABLE_LIST *tables;
   bool refresh;
   int result=0;
-  DBUG_ENTER("open_tables");
   MEM_ROOT new_frm_mem;
+  /* Also used for indicating that prelocking is need */
+  TABLE_LIST **query_tables_last_own;
+  DBUG_ENTER("open_tables");
   /*
     temporary mem_root for new .frm parsing.
     TODO: variables for size
@@ -1649,8 +1756,51 @@ int open_tables(THD *thd, TABLE_LIST *start, uint *counter)
   thd->current_tablenr= 0;
  restart:
   *counter= 0;
+  query_tables_last_own= 0;
   thd->proc_info="Opening tables";
-  for (tables= start; tables ;tables= tables->next_global)
+
+  /*
+    If we are not already executing prelocked statement and don't have
+    statement for which table list for prelocking is already built, let
+    us cache routines and try to build such table list.
+
+    NOTE: If we want queries with functions to work under explicit
+    LOCK TABLES we have to additionaly lock mysql.proc table in it.
+    At least until Monty will fix SP loading :)
+
+    NOTE: We can't delay prelocking until we will met some sub-statement
+    which really uses tables, since this will imply that we have to restore
+    its table list to be able execute it in some other context.
+    And current views implementation assumes that view tables are added to
+    global table list only once during PS preparing/first SP execution.
+    Also locking at earlier stage is probably faster altough may decrease
+    concurrency a bit.
+
+    NOTE: We will mark statement as requiring prelocking only if we will
+    have non empty table list. But this does not guarantee that in prelocked
+    mode we will have some locked tables, because queries which use only
+    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->spfuns.records || thd->lex->spprocs.records))
+  {
+    TABLE_LIST **save_query_tables_last;
+
+    sp_cache_routines(thd, thd->lex);
+    save_query_tables_last= thd->lex->query_tables_last;
+
+    DBUG_ASSERT(thd->lex->query_tables == *start);
+
+    if (sp_add_sp_tables_to_table_list(thd, thd->lex, thd->lex) ||
+        *start)
+    {
+      query_tables_last_own= save_query_tables_last;
+      *start= thd->lex->query_tables;
+    }
+  }
+
+  for (tables= *start; tables ;tables= tables->next_global)
   {
     /*
       Ignore placeholders for derived tables. After derived tables
@@ -1671,8 +1821,27 @@ int open_tables(THD *thd, TABLE_LIST *start, uint *counter)
       free_root(&new_frm_mem, MYF(MY_KEEP_PREALLOC));
       if (tables->view)
       {
+        /* VIEW placeholder */
 	(*counter)--;
-	continue; //VIEW placeholder
+        /*
+          Again if needed we have to get cache all routines used by this view
+          and add tables used by them to table list.
+        */
+        if (!thd->prelocked_mode && !thd->lex->requires_prelocking() &&
+            (tables->view->spfuns.records || tables->view->spprocs.records))
+        {
+          // FIXME We should catch recursion for both views and funcs here
+          sp_cache_routines(thd, tables->view);
+
+          /* We have at least one table in TL here */
+          if (!query_tables_last_own)
+            query_tables_last_own= thd->lex->query_tables_last;
+          sp_add_sp_tables_to_table_list(thd, thd->lex, tables->view);
+        }
+        /* Cleanup hashes because destructo for this LEX is never called */
+        hash_free(&tables->view->spfuns);
+        hash_free(&tables->view->spprocs);
+	continue;
       }
 
       if (refresh)				// Refresh in progress
@@ -1684,7 +1853,12 @@ int open_tables(THD *thd, TABLE_LIST *start, uint *counter)
 	thd->version=refresh_version;
 	TABLE **prev_table= &thd->open_tables;
 	bool found=0;
-	for (TABLE_LIST *tmp= start; tmp; tmp= tmp->next_global)
+        /*
+          QQ: What we should do if we have started building of table list
+          for prelocking ??? Probably throw it away ? But before we should
+          mark all temporary tables as free? How about locked ?
+        */
+	for (TABLE_LIST *tmp= *start; tmp; tmp= tmp->next_global)
 	{
 	  /* Close normal (not temporary) changed tables */
 	  if (tmp->table && ! tmp->table->s->tmp_table)
@@ -1713,7 +1887,27 @@ int open_tables(THD *thd, TABLE_LIST *start, uint *counter)
       break;
     }
     else
+    {
+      /*
+        If we are not already in prelocked mode and extended table list is not
+        yet built and we have trigger for table being opened then we should
+        cache all routines used by its triggers and add their tables to
+        prelocking list.
+        If we lock table for reading we won't update it so there is no need to
+        process its triggers since they never will be activated.
+
+        FIXME Now we are simply turning on prelocking. Proper integration
+        and testing is to be done later.
+      */
+      if (!thd->prelocked_mode && !thd->lex->requires_prelocking() &&
+          tables->table->triggers &&
+          tables->lock_type >= TL_WRITE_ALLOW_WRITE)
+      {
+        if (!query_tables_last_own)
+            query_tables_last_own= thd->lex->query_tables_last;
+      }
       free_root(&new_frm_mem, MYF(MY_KEEP_PREALLOC));
+    }
 
     if (tables->lock_type != TL_UNLOCK && ! thd->locked_tables)
       tables->table->reginfo.lock_type=tables->lock_type;
@@ -1721,6 +1915,10 @@ int open_tables(THD *thd, TABLE_LIST *start, uint *counter)
   }
   thd->proc_info=0;
   free_root(&new_frm_mem, MYF(0));              // Free pre-alloced block
+
+  if (query_tables_last_own)
+    thd->lex->mark_as_requiring_prelocking(query_tables_last_own);
+
   DBUG_RETURN(result);
 }
 
@@ -1769,6 +1967,11 @@ static bool check_lock_and_start_stmt(THD *thd, TABLE *table,
     table_list		Table to open is first table in this list
     lock_type		Lock to use for open
 
+  NOTE
+    This function don't do anything like SP/SF/views/triggers analysis done
+    in open_tables(). It is intended for opening of only one concrete table.
+    And used only in special contexts.
+
   RETURN VALUES
     table		Opened table
     0			Error
@@ -1843,7 +2046,7 @@ int simple_open_n_lock_tables(THD *thd, TABLE_LIST *tables)
 {
   DBUG_ENTER("simple_open_n_lock_tables");
   uint counter;
-  if (open_tables(thd, tables, &counter) || lock_tables(thd, tables, counter))
+  if (open_tables(thd, &tables, &counter) || lock_tables(thd, tables, counter))
     DBUG_RETURN(-1);				/* purecov: inspected */
   DBUG_RETURN(0);
 }
@@ -1870,7 +2073,7 @@ bool open_and_lock_tables(THD *thd, TABLE_LIST *tables)
 {
   uint counter;
   DBUG_ENTER("open_and_lock_tables");
-  if (open_tables(thd, tables, &counter) ||
+  if (open_tables(thd, &tables, &counter) ||
       lock_tables(thd, tables, counter) ||
       mysql_handle_derived(thd->lex, &mysql_derived_prepare) ||
       (thd->fill_derived_tables() &&
@@ -1903,7 +2106,7 @@ bool open_normal_and_derived_tables(THD *thd, TABLE_LIST *tables)
   uint counter;
   DBUG_ENTER("open_normal_and_derived_tables");
   DBUG_ASSERT(!thd->fill_derived_tables());
-  if (open_tables(thd, tables, &counter) ||
+  if (open_tables(thd, &tables, &counter) ||
       mysql_handle_derived(thd->lex, &mysql_derived_prepare))
     DBUG_RETURN(TRUE); /* purecov: inspected */
   relink_tables_for_multidelete(thd);           // Not really needed, but
@@ -1937,6 +2140,27 @@ static void relink_tables_for_multidelete(THD *thd)
 
 
 /*
+  Mark all real tables in the list as free for reuse.
+
+  SYNOPSIS
+    mark_real_tables_as_free_for_reuse()
+      thd   - thread context
+      table - head of the list of tables
+
+  DESCRIPTION
+    Marks all real tables in the list (i.e. not views, derived
+    or schema tables) as free for reuse.
+*/
+
+static void mark_real_tables_as_free_for_reuse(TABLE_LIST *table)
+{
+  for (; table; table= table->next_global)
+    if (!table->placeholder() && !table->schema_table)
+      table->table->query_id= 0;
+}
+
+
+/*
   Lock all tables in list
 
   SYNOPSIS
@@ -1950,6 +2174,10 @@ static void relink_tables_for_multidelete(THD *thd)
     handling thr_lock gives us.  You most always get all needed locks at
     once.
 
+    If query for which we are calling this function marked as requring
+    prelocking, this function will do implicit LOCK TABLES and change
+    thd::prelocked_mode accordingly.
+
   RETURN VALUES
    0	ok
    -1	Error
@@ -1958,36 +2186,125 @@ static void relink_tables_for_multidelete(THD *thd)
 int lock_tables(THD *thd, TABLE_LIST *tables, uint count)
 {
   TABLE_LIST *table;
+
+  DBUG_ENTER("lock_tables");
+  /*
+    We can't meet statement requiring prelocking if we already
+    in prelocked mode.
+  */
+  DBUG_ASSERT(!thd->prelocked_mode || !thd->lex->requires_prelocking());
+  /*
+    If statement requires prelocking then it has non-empty table list.
+    So it is safe to shortcut.
+  */
+  DBUG_ASSERT(!thd->lex->requires_prelocking() || tables);
+
   if (!tables)
-    return 0;
+    DBUG_RETURN(0);
 
-  if (!thd->locked_tables)
+  /*
+    We need this extra check for thd->prelocked_mode because we want to avoid
+    attempts to lock tables in substatements. Checking for thd->locked_tables
+    is not enough in some situations. For example for SP containing
+    "drop table t3; create temporary t3 ..; insert into t3 ...;"
+    thd->locked_tables may be 0 after drop tables, and without this extra
+    check insert will try to lock temporary table t3, that will lead
+    to memory leak...
+  */
+  if (!thd->locked_tables && !thd->prelocked_mode)
   {
     DBUG_ASSERT(thd->lock == 0);	// You must lock everything at once
     TABLE **start,**ptr;
+
     if (!(ptr=start=(TABLE**) thd->alloc(sizeof(TABLE*)*count)))
-      return -1;
+      DBUG_RETURN(-1);
     for (table= tables; table; table= table->next_global)
     {
       if (!table->placeholder() && !table->schema_table)
 	*(ptr++)= table->table;
     }
+
+    /* We have to emulate LOCK TABLES if we are statement needs prelocking. */
+    if (thd->lex->requires_prelocking())
+    {
+      thd->in_lock_tables=1;
+      thd->options|= OPTION_TABLE_LOCK;
+    }
+
     if (!(thd->lock=mysql_lock_tables(thd,start, (uint) (ptr - start))))
-      return -1;				/* purecov: inspected */
+    {
+      if (thd->lex->requires_prelocking())
+      {
+        thd->options&= ~(ulong) (OPTION_TABLE_LOCK);
+        thd->in_lock_tables=0;
+      }
+      DBUG_RETURN(-1);
+    }
+    if (thd->lex->requires_prelocking() &&
+        thd->lex->sql_command != SQLCOM_LOCK_TABLES)
+    {
+      TABLE_LIST *first_not_own= thd->lex->first_not_own_table();
+      /*
+        We just have done implicit LOCK TABLES, and now we have
+        to emulate first open_and_lock_tables() after it.
+
+        Note that "LOCK TABLES" can also be marked as requiring prelocking
+        (e.g. if one locks view which uses functions). We should not emulate
+        such open_and_lock_tables() in this case. We also should not set
+        THD::prelocked_mode or first close_thread_tables() call will do
+        "UNLOCK TABLES".
+      */
+      thd->locked_tables= thd->lock;
+      thd->lock= 0;
+      thd->in_lock_tables=0;
+
+      for (table= tables; table != first_not_own; table= table->next_global)
+      {
+        if (!table->placeholder() && !table->schema_table)
+        {
+          table->table->query_id= thd->query_id;
+          if (check_lock_and_start_stmt(thd, table->table, table->lock_type))
+          {
+            ha_rollback_stmt(thd);
+            mysql_unlock_tables(thd, thd->locked_tables);
+            thd->locked_tables= 0;
+            thd->options&= ~(ulong) (OPTION_TABLE_LOCK);
+            DBUG_RETURN(-1);
+          }
+        }
+      }
+      /*
+        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);
+      thd->prelocked_mode= PRELOCKED;
+    }
   }
   else
   {
-    for (table= tables; table; table= table->next_global)
+    TABLE_LIST *first_not_own= thd->lex->first_not_own_table();
+    for (table= tables; table != first_not_own; table= table->next_global)
     {
-      if (!table->placeholder() && 
+      if (!table->placeholder() && !table->schema_table &&
 	  check_lock_and_start_stmt(thd, table->table, table->lock_type))
       {
 	ha_rollback_stmt(thd);
-	return -1;
+	DBUG_RETURN(-1);
       }
     }
+    /*
+      If we are under explicit LOCK TABLES and our statement requires
+      prelocking, we should mark all "additional" tables as free for use
+      and enter prelocked mode.
+    */
+    if (thd->lex->requires_prelocking())
+    {
+      mark_real_tables_as_free_for_reuse(first_not_own);
+      thd->prelocked_mode= PRELOCKED_UNDER_LOCK_TABLES;
+    }
   }
-  return 0;
+  DBUG_RETURN(0);
 }
 
 
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 527f349038c..bac3e42ed62 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -251,8 +251,9 @@ THD::THD()
   protocol_prep.init(this);
 
   tablespace_op=FALSE;
-    ulong tmp=sql_rnd_with_mutex();
-    randominit(&rand, tmp + (ulong) &rand, tmp + (ulong) ::query_id);
+  ulong tmp=sql_rnd_with_mutex();
+  randominit(&rand, tmp + (ulong) &rand, tmp + (ulong) ::query_id);
+  prelocked_mode= NON_PRELOCKED;
 }
 
 
diff --git a/sql/sql_class.h b/sql/sql_class.h
index e88205568c2..aaf617ec493 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -293,13 +293,13 @@ public:
   {
     char buf[FN_REFLEN];
     return open(generate_name(log_name, ".log", 0, buf),
-                LOG_NORMAL, 0, WRITE_CACHE, 0, 0, 0);
+                LOG_NORMAL, 0, APPEND_CACHE, 0, 0, 0);
   }
   bool open_slow_log(const char *log_name)
   {
     char buf[FN_REFLEN];
     return open(generate_name(log_name, "-slow.log", 0, buf),
-                LOG_NORMAL, 0, WRITE_CACHE, 0, 0, 0);
+                LOG_NORMAL, 0, APPEND_CACHE, 0, 0, 0);
   }
   bool open_index_file(const char *index_file_name_arg,
                        const char *log_name);
@@ -926,6 +926,15 @@ typedef I_List<Item_change_record> Item_change_list;
 
 
 /*
+  Type of prelocked mode.
+  See comment for THD::prelocked_mode for complete description.
+*/
+
+enum prelocked_mode_type {NON_PRELOCKED= 0, PRELOCKED= 1,
+                          PRELOCKED_UNDER_LOCK_TABLES= 2};
+
+
+/*
   For each client connection we create a separate thread with THD serving as
   a thread/connection descriptor
 */
@@ -1025,7 +1034,13 @@ public:
     See also lock_tables() for details.
   */
   MYSQL_LOCK	*lock;				/* Current locks */
-  MYSQL_LOCK	*locked_tables;			/* Tables locked with LOCK */
+  /*
+    Tables that were locked with explicit or implicit LOCK TABLES.
+    (Implicit LOCK TABLES happens when we are prelocking tables for
+     execution of statement which uses stored routines. See description
+     THD::prelocked_mode for more info.)
+  */
+  MYSQL_LOCK	*locked_tables;
   HASH		handler_tables_hash;
   /*
     One thread can hold up to one named user-level lock. This variable
@@ -1192,8 +1207,6 @@ public:
   sp_rcontext *spcont;		// SP runtime context
   sp_cache   *sp_proc_cache;
   sp_cache   *sp_func_cache;
-  bool       shortcut_make_view; /* Don't do full mysql_make_view()
-				    during pre-opening of tables. */
 
   /*
     If we do a purge of binary logs, log index info of the threads
@@ -1209,6 +1222,31 @@ public:
     long    long_value;
   } sys_var_tmp;
 
+  /*
+    prelocked_mode_type enum and prelocked_mode member are used for
+    indicating whenever "prelocked mode" is on, and what type of
+    "prelocked mode" is it.
+
+    Prelocked mode is used for execution of queries which explicitly
+    or implicitly (via views or triggers) use functions, thus may need
+    some additional tables (mentioned in query table list) for their
+    execution.
+
+    First open_tables() call for such query will analyse all functions
+    used by it and add all additional tables to table its list. It will
+    also mark this query as requiring prelocking. After that lock_tables()
+    will issue implicit LOCK TABLES for the whole table list and change
+    thd::prelocked_mode to non-0. All queries called in functions invoked
+    by the main query will use prelocked tables. Non-0 prelocked_mode
+    will also surpress mentioned analysys in those queries thus saving
+    cycles. Prelocked mode will be turned off once close_thread_tables()
+    for the main query will be called.
+
+    Note: Since not all "tables" present in table list are really locked
+    thd::relocked_mode does not imply thd::locked_tables.
+  */
+  prelocked_mode_type prelocked_mode;
+
   THD();
   ~THD();
 
diff --git a/sql/sql_handler.cc b/sql/sql_handler.cc
index dd2ac3c013b..bb48b7ada77 100644
--- a/sql/sql_handler.cc
+++ b/sql/sql_handler.cc
@@ -187,7 +187,7 @@ bool mysql_ha_open(THD *thd, TABLE_LIST *tables, bool reopen)
 
   /* for now HANDLER can be used only for real TABLES */
   tables->required_type= FRMTYPE_TABLE;
-  error= open_tables(thd, tables, &counter);
+  error= open_tables(thd, &tables, &counter);
 
   HANDLER_TABLES_HACK(thd);
   if (error)
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 738a6e0dbbd..91c4dc40c01 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -169,13 +169,12 @@ void lex_start(THD *thd, uchar *buf,uint length)
   lex->sphead= NULL;
   lex->spcont= NULL;
   lex->proc_list.first= 0;
+  lex->query_tables_own_last= 0;
 
   if (lex->spfuns.records)
     my_hash_reset(&lex->spfuns);
   if (lex->spprocs.records)
     my_hash_reset(&lex->spprocs);
-  if (lex->sptabs.records)
-    my_hash_reset(&lex->sptabs);
   DBUG_VOID_RETURN;
 }
 
@@ -1868,6 +1867,8 @@ TABLE_LIST *st_lex::unlink_first_table(bool *link_to_local)
     */
     if ((query_tables= query_tables->next_global))
       query_tables->prev_global= &query_tables;
+    else
+      query_tables_last= &query_tables;
     first->next_global= 0;
 
     /*
@@ -1973,6 +1974,8 @@ void st_lex::link_first_table_back(TABLE_LIST *first,
   {
     if ((first->next_global= query_tables))
       query_tables->prev_global= &first->next_global;
+    else
+      query_tables_last= &first->next_global;
     query_tables= first;
 
     if (link_to_local)
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index a0145dcaccf..3588f376d2f 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -784,7 +784,6 @@ typedef struct st_lex
   sp_pcontext *spcont;
   HASH spfuns;		/* Called functions */
   HASH spprocs;		/* Called procedures */
-  HASH sptabs;		/* Merged table lists */
   st_sp_chistics sp_chistics;
   bool only_view;       /* used for SHOW CREATE TABLE/VIEW */
   /*
@@ -803,23 +802,25 @@ typedef struct st_lex
   */
   SQL_LIST trg_table_fields;
 
-  st_lex() :result(0), sql_command(SQLCOM_END)
+  /*
+    If non-0 then indicates that query requires prelocking and points to
+    next_global member of last own element in query table list (i.e. last
+    table which was not added to it as part of preparation to prelocking).
+    0 - indicates that this query does not need prelocking.
+  */
+  TABLE_LIST **query_tables_own_last;
+
+  st_lex() :result(0), sql_command(SQLCOM_END), query_tables_own_last(0)
   {
     extern byte *sp_lex_sp_key(const byte *ptr, uint *plen, my_bool first);
-    extern byte *sp_table_key(const byte *ptr, uint *plen, my_bool first);
     hash_init(&spfuns, system_charset_info, 0, 0, 0, sp_lex_sp_key, 0, 0);
     hash_init(&spprocs, system_charset_info, 0, 0, 0, sp_lex_sp_key, 0, 0);
-    hash_init(&sptabs, system_charset_info, 0, 0, 0, sp_table_key, 0, 0);
   }
-  
-  ~st_lex()
+
+  virtual ~st_lex()
   {
-    if (spfuns.array.buffer)
-      hash_free(&spfuns);
-    if (spprocs.array.buffer)
-      hash_free(&spprocs);
-    if (sptabs.array.buffer)
-      hash_free(&sptabs);
+    hash_free(&spfuns);
+    hash_free(&spprocs);
   }
 
   inline void uncacheable(uint8 cause)
@@ -856,6 +857,21 @@ typedef struct st_lex
   bool can_not_use_merged();
   bool only_view_structure();
   bool need_correct_ident();
+
+  inline bool requires_prelocking()
+  {
+    return query_tables_own_last;
+  }
+  inline void mark_as_requiring_prelocking(TABLE_LIST **tables_own_last)
+  {
+    query_tables_own_last= tables_own_last;
+  }
+  /* Return pointer to first not-own table in query-tables or 0 */
+  TABLE_LIST* first_not_own_table()
+  {
+    return ( query_tables_own_last ? *query_tables_own_last : 0);
+  }
+
 } LEX;
 
 struct st_lex_local: public st_lex
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 2f38f96c976..f328d31161a 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -1453,105 +1453,6 @@ bool do_command(THD *thd)
 }
 #endif  /* EMBEDDED_LIBRARY */
 
-static void release_local_lock(THD *thd, TABLE_LIST *locked_tables,
-                               bool old_innodb_table_locks)
-{
-  if (locked_tables)
-  {
-#ifdef HAVE_INNOBASE_DB
-    thd->variables.innodb_table_locks= old_innodb_table_locks;
-#endif
-    if (thd->locked_tables)
-      sp_unlock_tables(thd);
-  }
-
-}
-
-static bool process_nested_sp(THD *thd, LEX *lex, TABLE_LIST** locked_tables)
-{
-  DBUG_ENTER("process_nested_sp");
-  while (1)
-  {
-    if (sp_cache_routines(thd, lex, TYPE_ENUM_FUNCTION))
-      DBUG_RETURN(TRUE);
-    if (sp_cache_routines(thd, lex, TYPE_ENUM_PROCEDURE))
-      DBUG_RETURN(TRUE);
-    if (!thd->locked_tables &&
-        lex->sql_command != SQLCOM_CREATE_TABLE &&
-        lex->sql_command != SQLCOM_CREATE_VIEW)
-    {
-      MEM_ROOT *thdmemroot= NULL;
-
-      sp_merge_routine_tables(thd, lex);
-      // QQ Preopen tables to find views and triggers.
-      // This means we open, close and open again, which sucks, but
-      // right now it's the easiest way to get it to work. A better
-      // solution will hopefully be found soon...
-      if (lex->sptabs.records || lex->query_tables)
-      {
-        uint procs, funs, tabs;
-
-        if (thd->mem_root != thd->current_arena->mem_root)
-        {
-          thdmemroot= thd->mem_root;
-          thd->mem_root= thd->current_arena->mem_root;
-        }
-        if (!sp_merge_table_list(thd, &lex->sptabs, lex->query_tables))
-          DBUG_RETURN(TRUE);
-        procs= lex->spprocs.records;
-        funs= lex->spfuns.records;
-        tabs= lex->sptabs.records;
-
-        if (((*locked_tables)= sp_hash_to_table_list(thd, &lex->sptabs)))
-        {
-          // We don't want these updated now
-          uint ctmpdtabs= thd->status_var.created_tmp_disk_tables;
-          uint ctmptabs= thd->status_var.created_tmp_tables;
-          uint count;
-
-          thd->shortcut_make_view= TRUE;
-          open_tables(thd, *locked_tables, &count);
-          thd->shortcut_make_view= FALSE;
-          close_thread_tables(thd);
-          thd->status_var.created_tmp_disk_tables= ctmpdtabs;
-          thd->status_var.created_tmp_tables= ctmptabs;
-          thd->clear_error();
-          mysql_reset_errors(thd);
-          (*locked_tables)= NULL;
-        }
-        // A kludge: Decrease all temp. table's query ids to allow a
-        // second opening.
-        for (TABLE *table= thd->temporary_tables; table ; table=table->next)
-          table->query_id-= 1;
-        if (procs < lex->spprocs.records ||
-            funs < lex->spfuns.records ||
-            tabs < lex->sptabs.records)
-        {
-          if (thdmemroot)
-            thd->mem_root= thdmemroot;
-          continue;		// Found more SPs or tabs, try again
-        }
-      }
-      if (lex->sptabs.records &&
-          (lex->spfuns.records || lex->spprocs.records) &&
-          sp_merge_table_list(thd, &lex->sptabs, lex->query_tables))
-      {
-        if (((*locked_tables)= sp_hash_to_table_list(thd, &lex->sptabs)))
-        {
-#ifdef HAVE_INNOBASE_DB
-          thd->variables.innodb_table_locks= FALSE;
-#endif
-          sp_open_and_lock_tables(thd, *locked_tables);
-        }
-      }
-      if (thdmemroot)
-        thd->mem_root= thdmemroot;
-    }
-    break;
-  } // while (1)
-  DBUG_RETURN(FALSE);
-}
-
 
 /*
    Perform one connection-level (COM_XXXX) command.
@@ -1752,7 +1653,8 @@ bool dispatch_command(enum enum_server_command command, THD *thd,
 	in embedded server - just store them to be executed later 
       */
 #ifndef EMBEDDED_LIBRARY
-      if (thd->lock || thd->open_tables || thd->derived_tables)
+      if (thd->lock || thd->open_tables || thd->derived_tables ||
+          thd->prelocked_mode)
         close_thread_tables(thd);
 #endif
       ulong length= (ulong)(packet_end-packet);
@@ -1855,19 +1757,12 @@ bool dispatch_command(enum enum_server_command command, THD *thd,
       select_lex.table_list.link_in_list((byte*) &table_list,
                                          (byte**) &table_list.next_local);
     thd->lex->query_tables= &table_list;
-    thd->shortcut_make_view= 0;
-    process_nested_sp(thd, thd->lex, &locked_tables);
 
     /* switch on VIEW optimisation: do not fill temporary tables */
     thd->lex->sql_command= SQLCOM_SHOW_FIELDS;
     mysqld_list_fields(thd,&table_list,fields);
     thd->lex->unit.cleanup();
     thd->cleanup_after_query();
-#ifdef HAVE_INNOBASE_DB
-    release_local_lock(thd, locked_tables, old_innodb_table_locks);
-#else
-    release_local_lock(thd, locked_tables, false);
-#endif
     break;
   }
 #endif
@@ -2089,7 +1984,8 @@ bool dispatch_command(enum enum_server_command command, THD *thd,
     my_message(ER_UNKNOWN_COM_ERROR, ER(ER_UNKNOWN_COM_ERROR), MYF(0));
     break;
   }
-  if (thd->lock || thd->open_tables || thd->derived_tables)
+  if (thd->lock || thd->open_tables || thd->derived_tables ||
+      thd->prelocked_mode)
   {
     thd->proc_info="closing tables";
     close_thread_tables(thd);			/* Free tables */
@@ -2334,11 +2230,7 @@ mysql_execute_command(THD *thd)
   TABLE_LIST *all_tables;
   /* most outer SELECT_LEX_UNIT of query */
   SELECT_LEX_UNIT *unit= &lex->unit;
-  /* Locked closure of all tables */
-  TABLE_LIST *locked_tables= NULL;
   /* Saved variable value */
-  my_bool old_innodb_table_locks=
-            IF_INNOBASE_DB(thd->variables.innodb_table_locks, FALSE);
   DBUG_ENTER("mysql_execute_command");
   thd->net.no_send_error= 0;
 
@@ -2361,26 +2253,14 @@ mysql_execute_command(THD *thd)
   /* should be assigned after making first tables same */
   all_tables= lex->query_tables;
 
-  thd->shortcut_make_view= 0;
-  if (lex->sql_command != SQLCOM_CREATE_PROCEDURE &&
-      lex->sql_command != SQLCOM_CREATE_SPFUNCTION &&
-      lex->sql_command != SQLCOM_LOCK_TABLES &&
-      lex->sql_command != SQLCOM_UNLOCK_TABLES)
-  {
-    thd->no_warnings_for_error= 1;
-    res= process_nested_sp(thd, lex, &locked_tables);
-    thd->no_warnings_for_error= 0;
-    if (res)
-      DBUG_RETURN(TRUE);
-  }
-
   /*
     Reset warning count for each query that uses tables
     A better approach would be to reset this for any commands
     that is not a SHOW command or a select that only access local
     variables, but for now this is probably good enough.
   */
-  if (all_tables || &lex->select_lex != lex->all_selects_list)
+  if (all_tables || &lex->select_lex != lex->all_selects_list ||
+      lex->spfuns.records || lex->spprocs.records)
     mysql_reset_errors(thd);
 
 #ifdef HAVE_REPLICATION
@@ -2614,9 +2494,8 @@ mysql_execute_command(THD *thd)
     break;
   }
   case SQLCOM_DO:
-    if (all_tables &&
-	(check_table_access(thd, SELECT_ACL, all_tables, 0) ||
-         open_and_lock_tables(thd, all_tables)))
+    if (check_table_access(thd, SELECT_ACL, all_tables, 0) ||
+        open_and_lock_tables(thd, all_tables))
       goto error;
 
     res= mysql_do(thd, *lex->insert_list);
@@ -3485,8 +3364,7 @@ unsent_create_error:
   case SQLCOM_SET_OPTION:
   {
     List<set_var_base> *lex_var_list= &lex->var_list;
-    if (all_tables &&
-	(check_table_access(thd, SELECT_ACL, all_tables, 0) ||
+    if ((check_table_access(thd, SELECT_ACL, all_tables, 0) ||
 	 open_and_lock_tables(thd, all_tables)))
       goto error;
     if (lex->one_shot_set && not_all_support_one_shot(lex_var_list))
@@ -3532,7 +3410,7 @@ unsent_create_error:
     thd->in_lock_tables=1;
     thd->options|= OPTION_TABLE_LOCK;
 
-    if (!(res= open_and_lock_tables(thd, all_tables)))
+    if (!(res= simple_open_n_lock_tables(thd, all_tables)))
     {
 #ifdef HAVE_QUERY_CACHE
       if (thd->variables.query_cache_wlock_invalidate)
@@ -4115,7 +3993,20 @@ unsent_create_error:
     {
       sp_head *sp;
 
-      if (!(sp= sp_find_procedure(thd, lex->spname)))
+      /*
+        This will cache all SP and SF and open and lock all tables
+        required for execution.
+      */
+      if (check_table_access(thd, SELECT_ACL, all_tables, 0) ||
+	  open_and_lock_tables(thd, all_tables))
+       goto error;
+
+      /*
+        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
+      */
+      if (!(sp= sp_find_procedure(thd, lex->spname, TRUE)))
       {
 	my_error(ER_SP_DOES_NOT_EXIST, MYF(0), "PROCEDURE",
                  lex->spname->m_qname.str);
@@ -4130,12 +4021,6 @@ unsent_create_error:
         /* bits that should be cleared in thd->server_status */
 	uint bits_to_be_cleared= 0;
 
-	/* In case the arguments are subselects... */
-	if (all_tables &&
-	    (check_table_access(thd, SELECT_ACL, all_tables, 0) ||
-	     open_and_lock_tables(thd, all_tables)))
-          goto error;
-
 #ifndef EMBEDDED_LIBRARY
 	my_bool nsok= thd->net.no_send_ok;
 	thd->net.no_send_ok= TRUE;
@@ -4606,11 +4491,6 @@ cleanup:
     if (thd->lock == thd->locked_tables)
       thd->lock= 0;
   }
-#ifdef HAVE_INNOBASE_DB
-  release_local_lock(thd, locked_tables, old_innodb_table_locks);
-#else
-  release_local_lock(thd, locked_tables, false);
-#endif
   DBUG_RETURN(res || thd->net.report_error);
 }
 
@@ -4865,6 +4745,38 @@ check_procedure_access(THD *thd, ulong want_access,char *db, char *name,
   return FALSE;
 }
 
+
+/*
+  Check if the routine has any of the routine privileges
+
+  SYNOPSIS
+    check_some_routine_access()
+    thd		 Thread handler
+    db           Database name
+    name         Routine name
+
+  RETURN
+    0            ok
+    1            error
+*/
+
+bool check_some_routine_access(THD *thd, char *db, char *name)
+{
+  ulong save_priv;
+  if (thd->master_access & SHOW_PROC_ACLS)
+    return FALSE;
+  if (!check_access(thd, SHOW_PROC_ACLS, db, &save_priv, 0, 1) ||
+      (save_priv & SHOW_PROC_ACLS))
+    return FALSE;
+#ifndef NO_EMBEDDED_ACCESS_CHECKS
+  if (grant_option)
+    return check_routine_level_acl(thd, db, name);
+#endif
+
+  return FALSE;
+}
+
+
 /*
   Check if the given table has any of the asked privileges
 
@@ -5203,8 +5115,6 @@ void mysql_parse(THD *thd, char *inBuf, uint length)
 	{
 	  if (thd->lex->sphead)
 	  {
-	    if (lex != thd->lex)
-	      thd->lex->sphead->restore_lex(thd);
 	    delete thd->lex->sphead;
 	    thd->lex->sphead= NULL;
 	  }
@@ -5240,8 +5150,6 @@ void mysql_parse(THD *thd, char *inBuf, uint length)
       if (thd->lex->sphead)
       {
 	/* Clean up after failed stored procedure/function */
-	if (lex != thd->lex)
-	  thd->lex->sphead->restore_lex(thd);
 	delete thd->lex->sphead;
 	thd->lex->sphead= NULL;
       }
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index d1594edc3f9..8006c61f233 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -1004,7 +1004,7 @@ static int mysql_test_update(Prepared_statement *stmt,
   if (update_precheck(thd, table_list))
     DBUG_RETURN(1);
 
-  if (!open_tables(thd, table_list, &table_count))
+  if (!open_tables(thd, &table_list, &table_count))
   {
     if (table_list->ancestor && table_list->ancestor->next_local)
     {
@@ -1545,22 +1545,6 @@ static int check_prepared_statement(Prepared_statement *stmt,
   lex->first_lists_tables_same();
   tables= lex->query_tables;
 
-  /*
-    Preopen 'proc' system table and cache all functions used in this
-    statement. We must do that before we open ordinary tables to avoid
-    deadlocks. We can't open and lock any table once query tables were
-    opened.
-  */
-  if (lex->sql_command != SQLCOM_CREATE_PROCEDURE &&
-      lex->sql_command != SQLCOM_CREATE_SPFUNCTION)
-  {
-    /* The error is printed inside */
-    if (sp_cache_routines(thd, lex, TYPE_ENUM_FUNCTION))
-      DBUG_RETURN(-1);
-    if (sp_cache_routines(thd, lex, TYPE_ENUM_PROCEDURE))
-      DBUG_RETURN(-1);
-  }
-
   switch (sql_command) {
   case SQLCOM_REPLACE:
   case SQLCOM_INSERT:
@@ -1787,15 +1771,13 @@ bool mysql_stmt_prepare(THD *thd, char *packet, uint packet_length,
     my_pthread_setprio(pthread_self(),WAIT_PRIOR);
   if (error && thd->lex->sphead)
   {
-    if (lex != thd->lex)
-      thd->lex->sphead->restore_lex(thd);
     delete thd->lex->sphead;
     thd->lex->sphead= NULL;
   }
   lex_end(lex);
+  close_thread_tables(thd);
   thd->restore_backup_statement(stmt, &thd->stmt_backup);
   cleanup_items(stmt->free_list);
-  close_thread_tables(thd);
   thd->rollback_item_tree_changes();
   thd->cleanup_after_query();
   thd->current_arena= thd;
@@ -1885,6 +1867,11 @@ void reset_stmt_for_execute(THD *thd, LEX *lex)
     to indicate the table is altered, and re-do the setup_* 
     and open the tables back.
   */
+  /*
+    NOTE: We should reset whole table list here including all tables added
+    by prelocking algorithm (it is not a problem for substatements since
+    they have their own table list).
+  */
   for (TABLE_LIST *tables= lex->query_tables;
 	 tables;
 	 tables= tables->next_global)
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index 7a4ee9f5de3..4ffe7110cfa 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -2468,32 +2468,41 @@ int fill_schema_coll_charset_app(THD *thd, TABLE_LIST *tables, COND *cond)
 }
 
 
-void store_schema_proc(THD *thd, TABLE *table,
-		       TABLE *proc_table,
-		       const char *wild)
+void store_schema_proc(THD *thd, TABLE *table, TABLE *proc_table,
+                       const char *wild, bool full_access, const char *sp_user)
 {
   String tmp_string;
   TIME time;
   LEX *lex= thd->lex;
   CHARSET_INFO *cs= system_charset_info;
-  restore_record(table, s->default_values);
+  const char *sp_db, *sp_name, *definer;
+  sp_db= get_field(thd->mem_root, proc_table->field[0]);
+  sp_name= get_field(thd->mem_root, proc_table->field[1]);
+  definer= get_field(thd->mem_root, proc_table->field[11]);
+  if (!full_access)
+    full_access= !strcmp(sp_user, definer);
+  if (!full_access)
+  {
+#ifndef NO_EMBEDDED_ACCESS_CHECKS
+    if (check_some_routine_access(thd, (char * )sp_db, (char * )sp_name))
+      return;
+#endif
+  }
+
   if (lex->orig_sql_command == SQLCOM_SHOW_STATUS_PROC &&
       proc_table->field[2]->val_int() == TYPE_ENUM_PROCEDURE ||
       lex->orig_sql_command == SQLCOM_SHOW_STATUS_FUNC &&
       proc_table->field[2]->val_int() == TYPE_ENUM_FUNCTION ||
       lex->orig_sql_command == SQLCOM_END)
   {
-    tmp_string.length(0);
-    get_field(thd->mem_root, proc_table->field[1], &tmp_string);
-    if (!wild || !wild[0] || !wild_compare(tmp_string.ptr(), wild, 0))
+    restore_record(table, s->default_values);
+    if (!wild || !wild[0] || !wild_compare(sp_name, wild, 0))
     {
-      table->field[3]->store(tmp_string.ptr(), tmp_string.length(), cs);
+      table->field[3]->store(sp_name, strlen(sp_name), cs);
       tmp_string.length(0);
       get_field(thd->mem_root, proc_table->field[3], &tmp_string);
       table->field[0]->store(tmp_string.ptr(), tmp_string.length(), cs);
-      tmp_string.length(0);
-      get_field(thd->mem_root, proc_table->field[0], &tmp_string);
-      table->field[2]->store(tmp_string.ptr(), tmp_string.length(), cs);
+      table->field[2]->store(sp_db, strlen(sp_db), cs);
       tmp_string.length(0);
       get_field(thd->mem_root, proc_table->field[2], &tmp_string);
       table->field[4]->store(tmp_string.ptr(), tmp_string.length(), cs);
@@ -2504,10 +2513,13 @@ void store_schema_proc(THD *thd, TABLE *table,
         table->field[5]->store(tmp_string.ptr(), tmp_string.length(), cs);
         table->field[5]->set_notnull();
       }
+      if (full_access)
+      {
+        tmp_string.length(0);
+        get_field(thd->mem_root, proc_table->field[10], &tmp_string);
+        table->field[7]->store(tmp_string.ptr(), tmp_string.length(), cs);
+      }
       table->field[6]->store("SQL", 3, cs);
-      tmp_string.length(0);
-      get_field(thd->mem_root, proc_table->field[10], &tmp_string);
-      table->field[7]->store(tmp_string.ptr(), tmp_string.length(), cs);
       table->field[10]->store("SQL", 3, cs);
       tmp_string.length(0);
       get_field(thd->mem_root, proc_table->field[6], &tmp_string);
@@ -2531,9 +2543,7 @@ void store_schema_proc(THD *thd, TABLE *table,
       tmp_string.length(0);
       get_field(thd->mem_root, proc_table->field[15], &tmp_string);
       table->field[18]->store(tmp_string.ptr(), tmp_string.length(), cs);
-      tmp_string.length(0);
-      get_field(thd->mem_root, proc_table->field[11], &tmp_string);
-      table->field[19]->store(tmp_string.ptr(), tmp_string.length(), cs);
+      table->field[19]->store(definer, strlen(definer), cs);
       table->file->write_row(table->record[0]);
     }
   }
@@ -2547,14 +2557,18 @@ int fill_schema_proc(THD *thd, TABLE_LIST *tables, COND *cond)
   const char *wild= thd->lex->wild ? thd->lex->wild->ptr() : NullS;
   int res= 0;
   TABLE *table= tables->table, *old_open_tables= thd->open_tables;
+  bool full_access;
+  char definer[HOSTNAME_LENGTH+USERNAME_LENGTH+2];
   DBUG_ENTER("fill_schema_proc");
 
+  strxmov(definer, thd->priv_user, "@", thd->priv_host, NullS);
   bzero((char*) &proc_tables,sizeof(proc_tables));
   proc_tables.db= (char*) "mysql";
   proc_tables.db_length= 5;
   proc_tables.table_name= proc_tables.alias= (char*) "proc";
   proc_tables.table_name_length= 4;
   proc_tables.lock_type= TL_READ;
+  full_access= !check_table_access(thd, SELECT_ACL, &proc_tables, 1);
   if (!(proc_table= open_ltable(thd, &proc_tables, TL_READ)))
   {
     DBUG_RETURN(1);
@@ -2565,9 +2579,9 @@ int fill_schema_proc(THD *thd, TABLE_LIST *tables, COND *cond)
     res= (res == HA_ERR_END_OF_FILE) ? 0 : 1;
     goto err;
   }
-  store_schema_proc(thd, table, proc_table, wild);
+  store_schema_proc(thd, table, proc_table, wild, full_access, definer);
   while (!proc_table->file->index_next(proc_table->record[0]))
-    store_schema_proc(thd, table, proc_table, wild);
+    store_schema_proc(thd, table, proc_table, wild, full_access, definer);
 
 err:
   proc_table->file->ha_index_end();
diff --git a/sql/sql_trigger.cc b/sql/sql_trigger.cc
index f61ff12f365..110841e8fd4 100644
--- a/sql/sql_trigger.cc
+++ b/sql/sql_trigger.cc
@@ -415,9 +415,8 @@ bool Table_triggers_list::check_n_load(THD *thd, const char *db,
           */
           if (lex.sphead)
           {
-            if (&lex != thd->lex)
-              thd->lex->sphead->restore_lex(thd);
             delete lex.sphead;
+            lex.sphead= 0;
           }
           goto err_with_lex_cleanup;
         }
diff --git a/sql/sql_trigger.h b/sql/sql_trigger.h
index 82e7c1ce023..7dd6734eb89 100644
--- a/sql/sql_trigger.h
+++ b/sql/sql_trigger.h
@@ -42,14 +42,21 @@ public:
 
     if (bodies[event][time_type])
     {
-      /*
-        Similar to function invocation we don't need to surpress sending of
-        ok packets here because don't allow execute statements from trigger.
+#ifndef EMBEDDED_LIBRARY
+      /* Surpress OK packets in case if we will execute statements */
+      my_bool nsok= thd->net.no_send_ok;
+      thd->net.no_send_ok= TRUE;
+#endif
 
+      /*
         FIXME: We should juggle with security context here (because trigger
         should be invoked with creator rights).
       */
       res= bodies[event][time_type]->execute_function(thd, 0, 0, 0);
+
+#ifndef EMBEDDED_LIBRARY
+      thd->net.no_send_ok= nsok;
+#endif
     }
 
     return res;
diff --git a/sql/sql_udf.cc b/sql/sql_udf.cc
index ec47fb055e2..1d14d037c47 100644
--- a/sql/sql_udf.cc
+++ b/sql/sql_udf.cc
@@ -17,15 +17,15 @@
 /* This implements 'user defined functions' */
 
 /*
-** Known bugs:
-**
-** Memory for functions are never freed!
-** Shared libraries are not closed before mysqld exists;
-**   - This is because we can't be sure if some threads is using
-**     a functions.
-**
-** The buggs only affects applications that creates and frees a lot of
-** dynamic functions, so this shouldn't be a real problem.
+   Known bugs:
+  
+   Memory for functions is never freed!
+   Shared libraries are not closed before mysqld exits;
+     - This is because we can't be sure if some threads are using
+       a function.
+  
+   The bugs only affect applications that create and free a lot of
+   dynamic functions, so this shouldn't be a real problem.
 */
 
 #ifdef __GNUC__
@@ -74,32 +74,49 @@ static HASH udf_hash;
 static rw_lock_t THR_LOCK_udf;
 
 
-static udf_func *add_udf(LEX_STRING *name, Item_result ret, char *dl,
-			 Item_udftype typ);
+static udf_func *add_udf(LEX_STRING *name, Item_result ret,
+                         char *dl, Item_udftype typ);
 static void del_udf(udf_func *udf);
 static void *find_udf_dl(const char *dl);
 
-
-static void init_syms(udf_func *tmp)
+static char *init_syms(udf_func *tmp, char *nm)
 {
-  char nm[MAX_FIELD_NAME+16],*end;
+  char *end;
+
+  if (!((tmp->func= dlsym(tmp->dlhandle, tmp->name.str))))
+    return tmp->name.str;
 
-  tmp->func = dlsym(tmp->dlhandle, tmp->name.str);
   end=strmov(nm,tmp->name.str);
-  (void) strmov(end,"_init");
-  tmp->func_init = dlsym(tmp->dlhandle, nm);
-  (void) strmov(end,"_deinit");
-  tmp->func_deinit = dlsym(tmp->dlhandle, nm);
+
   if (tmp->type == UDFTYPE_AGGREGATE)
   {
-    (void)strmov( end, "_clear" );
-    tmp->func_clear = dlsym( tmp->dlhandle, nm );
-    (void)strmov( end, "_add" );
-    tmp->func_add = dlsym( tmp->dlhandle, nm );
-    /* Give error if _clear and _add doesn't exists */
-    if (!tmp->func_clear || ! tmp->func_add)
-      tmp->func= 0;
+    (void)strmov(end, "_clear");
+    if (!((tmp->func_clear= dlsym(tmp->dlhandle, nm))))
+      return nm;
+    (void)strmov(end, "_add");
+    if (!((tmp->func_add= dlsym(tmp->dlhandle, nm))))
+      return nm;
+  }
+
+  (void) strmov(end,"_deinit");
+  tmp->func_deinit= dlsym(tmp->dlhandle, nm);
+
+  (void) strmov(end,"_init");
+  tmp->func_init= dlsym(tmp->dlhandle, nm);
+
+  /*
+    to prefent loading "udf" from, e.g. libc.so
+    let's ensure that at least one auxiliary symbol is defined
+  */
+  if (!tmp->func_init && !tmp->func_deinit && tmp->type != UDFTYPE_AGGREGATE)
+  {
+    if (opt_allow_suspicious_udfs)
+      sql_print_error(ER(ER_CANT_FIND_DL_ENTRY), nm);
+    else
+      return nm;
   }
+
+  return 0;
 }
 
 extern "C" byte* get_hash_key(const byte *buff,uint *length,
@@ -111,7 +128,7 @@ extern "C" byte* get_hash_key(const byte *buff,uint *length,
 }
 
 /*
-** Read all predeclared functions from func@mysql and accept all that
+** Read all predeclared functions from mysql.func and accept all that
 ** can be used.
 */
 
@@ -153,7 +170,7 @@ void udf_init()
   if (simple_open_n_lock_tables(new_thd, &tables))
   {
     DBUG_PRINT("error",("Can't open udf table"));
-    sql_print_error("Can't open the mysql/func table. Please run the mysql_install_db script to create it.");
+    sql_print_error("Can't open the mysql.func table. Please run the mysql_install_db script to create it.");
     goto end;
   }
 
@@ -171,10 +188,23 @@ void udf_init()
     if (table->s->fields >= 4)			// New func table
       udftype=(Item_udftype) table->field[3]->val_int();
 
-    if (!(tmp = add_udf(&name,(Item_result) table->field[1]->val_int(),
-			dl_name, udftype)))
+    /*
+      Ensure that the .dll doesn't have a path
+      This is done to ensure that only approved dll from the system
+      directories are used (to make this even remotely secure).
+    */
+    if (strchr(dl_name, '/') || name.length > NAME_LEN)
+    {
+      sql_print_error("Invalid row in mysql.func table for function '%.64s'",
+                      name.str);
+      continue;
+    }
+
+
+    if (!(tmp= add_udf(&name,(Item_result) table->field[1]->val_int(),
+                       dl_name, udftype)))
     {
-      sql_print_error("Can't alloc memory for udf function: name");
+      sql_print_error("Can't alloc memory for udf function: '%.64s'", name.str);
       continue;
     }
 
@@ -191,13 +221,15 @@ void udf_init()
       new_dl=1;
     }
     tmp->dlhandle = dl;
-    init_syms(tmp);
-    if (!tmp->func)
     {
-      sql_print_error(ER(ER_CANT_FIND_DL_ENTRY), name);
-      del_udf(tmp);
-      if (new_dl)
-	dlclose(dl);
+      char buf[MAX_FIELD_NAME+16], *missing;
+      if ((missing= init_syms(tmp, buf)))
+      {
+        sql_print_error(ER(ER_CANT_FIND_DL_ENTRY), missing);
+        del_udf(tmp);
+        if (new_dl)
+          dlclose(dl);
+      }
     }
   }
   if (error > 0)
@@ -239,7 +271,7 @@ void udf_free()
   {
     initialized= 0;
     rwlock_destroy(&THR_LOCK_udf);
-  }    
+  }
   DBUG_VOID_RETURN;
 }
 
@@ -407,12 +439,13 @@ int mysql_create_function(THD *thd,udf_func *udf)
     new_dl=1;
   }
   udf->dlhandle=dl;
-  init_syms(udf);
-
-  if (udf->func == NULL)
   {
-    my_error(ER_CANT_FIND_DL_ENTRY, MYF(0), udf->name);
-    goto err;
+    char buf[MAX_FIELD_NAME+16], *missing;
+    if ((missing= init_syms(udf, buf)))
+    {
+      my_error(ER_CANT_FIND_DL_ENTRY, MYF(0), missing);
+      goto err;
+    }
   }
   udf->name.str=strdup_root(&mem,udf->name.str);
   udf->dl=strdup_root(&mem,udf->dl);
@@ -425,7 +458,7 @@ int mysql_create_function(THD *thd,udf_func *udf)
   u_d->func_clear=udf->func_clear;
   u_d->func_add=udf->func_add;
 
-  /* create entry in mysql/func table */
+  /* create entry in mysql.func table */
 
   bzero((char*) &tables,sizeof(tables));
   tables.db= (char*) "mysql";
@@ -445,7 +478,7 @@ int mysql_create_function(THD *thd,udf_func *udf)
   close_thread_tables(thd);
   if (error)
   {
-    my_error(ER_ERROR_ON_WRITE, MYF(0), "func@mysql", error);
+    my_error(ER_ERROR_ON_WRITE, MYF(0), "mysql.func", error);
     del_udf(u_d);
     goto err;
   }
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index 54a976fe2b0..477a283448a 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -136,7 +136,7 @@ int mysql_update(THD *thd,
 
   LINT_INIT(timestamp_query_id);
 
-  if (open_tables(thd, table_list, &table_count))
+  if (open_tables(thd, &table_list, &table_count))
     DBUG_RETURN(1);
 
   if (table_list->ancestor && table_list->ancestor->next_local)
@@ -635,7 +635,7 @@ bool mysql_multi_update_prepare(THD *thd)
   thd->lex->sql_command= SQLCOM_UPDATE_MULTI;
 
   /* open tables and create derived ones, but do not lock and fill them */
-  if ((original_multiupdate && open_tables(thd, table_list, & table_count)) ||
+  if ((original_multiupdate && open_tables(thd, &table_list, & table_count)) ||
       mysql_handle_derived(lex, &mysql_derived_prepare))
     DBUG_RETURN(TRUE);
   /*
diff --git a/sql/sql_view.cc b/sql/sql_view.cc
index 96a0dc2f636..31277452118 100644
--- a/sql/sql_view.cc
+++ b/sql/sql_view.cc
@@ -19,6 +19,7 @@
 #include "sql_select.h"
 #include "parse_file.h"
 #include "sp.h"
+#include "sp_head.h"
 
 #define MD5_BUFF_LENGTH 33
 
@@ -615,10 +616,7 @@ mysql_make_view(File_parser *parser, TABLE_LIST *table)
   table->view= lex= thd->lex= (LEX*) new(thd->mem_root) st_lex_local;
   lex_start(thd, (uchar*)table->query.str, table->query.length);
   view_select= &lex->select_lex;
-  /* Only if we're not in the pre-open phase */
-  if (!thd->shortcut_make_view)
-    view_select->select_number= ++thd->select_number;
-  old_lex->derived_tables|= DERIVED_VIEW;
+  view_select->select_number= ++thd->select_number;
   {
     ulong options= thd->options;
     /* switch off modes which can prevent normal parsing of VIEW
@@ -662,35 +660,13 @@ mysql_make_view(File_parser *parser, TABLE_LIST *table)
     TABLE_LIST *view_tables_tail= 0;
     TABLE_LIST *tbl;
 
-    /* move SP to main LEX */
-    if (lex->spfuns.records)
-      sp_merge_hash(&old_lex->spfuns, &lex->spfuns);
-
-    /* cleanup LEX */
-    if (lex->spfuns.array.buffer)
-      hash_free(&lex->spfuns);
-    if (lex->spprocs.array.buffer)
-      hash_free(&lex->spprocs);
-    if (lex->sptabs.array.buffer)
-      hash_free(&lex->sptabs);
-
-    /* If we're pre-opening tables to find SPs and tables we need
-       not go any further; doing so will cause an infinite loop. */
-    if (thd->shortcut_make_view)
-    {
-      extern bool
-	sp_merge_table_list(THD *thd, HASH *h, TABLE_LIST *table,
-			    LEX *lex_for_tmp_check = 0);
-
-      sp_merge_table_list(thd, &old_lex->sptabs, view_tables);
-      goto ok;
-    }
-
     /*
-      check rights to run commands (EXPLAIN SELECT & SHOW CREATE) which show
-      underlying tables
+      Check rights to run commands (EXPLAIN SELECT & SHOW CREATE) which show
+      underlying tables.
+      Skip this step if we are opening view for prelocking only.
     */
-    if ((old_lex->sql_command == SQLCOM_SELECT && old_lex->describe))
+    if (!table->prelocking_placeholder &&
+        (old_lex->sql_command == SQLCOM_SELECT && old_lex->describe))
     {
       if (check_table_access(thd, SELECT_ACL, view_tables, 1) &&
           check_table_access(thd, SHOW_VIEW_ACL, table, 1))
@@ -699,7 +675,8 @@ mysql_make_view(File_parser *parser, TABLE_LIST *table)
         goto err;
       }
     }
-    else if (old_lex->sql_command == SQLCOM_SHOW_CREATE)
+    else if (!table->prelocking_placeholder &&
+             old_lex->sql_command == SQLCOM_SHOW_CREATE)
     {
       if (check_table_access(thd, SHOW_VIEW_ACL, table, 0))
         goto err;
@@ -717,13 +694,6 @@ mysql_make_view(File_parser *parser, TABLE_LIST *table)
       tbl->belong_to_view= top_view;
     }
 
-    /* move SQL_NO_CACHE & Co to whole query */
-    old_lex->safe_to_cache_query= (old_lex->safe_to_cache_query &&
-				   lex->safe_to_cache_query);
-    /* move SQL_CACHE to whole query */
-    if (view_select->options & OPTION_TO_QUERY_CACHE)
-      old_lex->select_lex.options|= OPTION_TO_QUERY_CACHE;
-
     /*
       Put tables of VIEW after VIEW TABLE_LIST
 
@@ -740,13 +710,30 @@ mysql_make_view(File_parser *parser, TABLE_LIST *table)
       }
       else
       {
-        lex->query_tables_last= &view_tables_tail->next_global;
+        old_lex->query_tables_last= &view_tables_tail->next_global;
       }
       view_tables->prev_global= &table->next_global;
       table->next_global= view_tables;
     }
 
     /*
+      If we are opening this view as part of implicit LOCK TABLES, then
+      this view serves as simple placeholder and we should not continue
+      further processing.
+    */
+    if (table->prelocking_placeholder)
+      goto ok2;
+
+    old_lex->derived_tables|= DERIVED_VIEW;
+
+    /* move SQL_NO_CACHE & Co to whole query */
+    old_lex->safe_to_cache_query= (old_lex->safe_to_cache_query &&
+				   lex->safe_to_cache_query);
+    /* move SQL_CACHE to whole query */
+    if (view_select->options & OPTION_TO_QUERY_CACHE)
+      old_lex->select_lex.options|= OPTION_TO_QUERY_CACHE;
+
+    /*
       check MERGE algorithm ability
       - algorithm is not explicit TEMPORARY TABLE
       - VIEW SELECT allow merging
@@ -850,8 +837,6 @@ mysql_make_view(File_parser *parser, TABLE_LIST *table)
     goto err;
 
 ok:
-  if (arena)
-    thd->restore_backup_item_arena(arena, &backup);
   /* global SELECT list linking */
   end= view_select;	// primary SELECT_LEX is always last
   end->link_next= old_lex->all_selects_list;
@@ -860,12 +845,16 @@ ok:
   lex->all_selects_list->link_prev=
     (st_select_lex_node**)&old_lex->all_selects_list;
 
+ok2:
+  if (arena)
+    thd->restore_backup_item_arena(arena, &backup);
   thd->lex= old_lex;
   DBUG_RETURN(0);
 
 err:
   if (arena)
     thd->restore_backup_item_arena(arena, &backup);
+  delete table->view;
   table->view= 0;	// now it is not VIEW placeholder
   thd->lex= old_lex;
   DBUG_RETURN(1);
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index a69b6a96982..fae01502a8d 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -1548,12 +1548,12 @@ sp_opt_inout:
 
 sp_proc_stmts:
 	  /* Empty */ {}
-	| sp_proc_stmts  { Lex->query_tables= 0; } sp_proc_stmt ';'
+	| sp_proc_stmts  sp_proc_stmt ';'
 	;
 
 sp_proc_stmts1:
 	  sp_proc_stmt ';' {}
-	| sp_proc_stmts1  { Lex->query_tables= 0; } sp_proc_stmt ';'
+	| sp_proc_stmts1  sp_proc_stmt ';'
 	;
 
 sp_decls:
@@ -1587,13 +1587,15 @@ sp_decls:
 	;
 
 sp_decl:
-	  DECLARE_SYM sp_decl_idents type sp_opt_default
+	  DECLARE_SYM sp_decl_idents type 
+          { Lex->sphead->reset_lex(YYTHD); }
+          sp_opt_default
 	  {
 	    LEX *lex= Lex;
 	    sp_pcontext *ctx= lex->spcont;
 	    uint max= ctx->context_pvars();
 	    enum enum_field_types type= (enum enum_field_types)$3;
-	    Item *it= $4;
+	    Item *it= $5;
 
 	    for (uint i = max-$2 ; i < max ; i++)
 	    {
@@ -1605,15 +1607,19 @@ sp_decl:
 	        sp_instr_set *in= new sp_instr_set(lex->sphead->instructions(),
 	                                           ctx,
 						   ctx->pvar_context2index(i),
-						   it, type);
+                                                   it, type, lex,
+                                                   (i == max - 1));
 
-		in->tables= lex->query_tables;
-		lex->query_tables= 0;
+                /*
+                  The last instruction is assigned to be responsible for
+                  freeing LEX.
+                */
 	        lex->sphead->add_instr(in);
 	        ctx->set_isset(i, TRUE);
 		ctx->set_default(i, it);
 	      }
 	    }
+            lex->sphead->restore_lex(YYTHD);
 	    $$.vars= $2;
 	    $$.conds= $$.hndlrs= $$.curs= 0;
 	  }
@@ -1865,36 +1871,39 @@ sp_proc_stmt:
 	      my_message(ER_SP_NO_USE, ER(ER_SP_NO_USE), MYF(0));
 	      YYABORT;
 	    }
-	    /* Don't add an instruction for empty SET statements.
-	    ** (This happens if the SET only contained local variables,
-	    **  which get their set instructions generated separately.)
+	    /*
+              Don't add an instruction for SET statements, since all
+              instructions for them were already added during processing
+              of "set" rule.
 	    */
-	    if (lex->sql_command != SQLCOM_SET_OPTION ||
-		! lex->var_list.is_empty())
+            DBUG_ASSERT(lex->sql_command != SQLCOM_SET_OPTION ||
+                        lex->var_list.is_empty());
+            if (lex->sql_command != SQLCOM_SET_OPTION)
 	    {
-		sp_instr_stmt *i=new sp_instr_stmt(sp->instructions(),
-						   lex->spcont);
-
-		/* Extract the query statement from the tokenizer:
-                   The end is either lex->tok_end or tok->ptr. */
-		if (lex->ptr - lex->tok_end > 1)
-		  i->m_query.length= lex->ptr - sp->m_tmp_query;
-		else
-		  i->m_query.length= lex->tok_end - sp->m_tmp_query;
-		i->m_query.str= strmake_root(YYTHD->mem_root,
-					     (char *)sp->m_tmp_query,
-					     i->m_query.length);
-		i->set_lex(lex);
-		sp->add_instr(i);
-		lex->sp_lex_in_use= TRUE;
-	      }
+              sp_instr_stmt *i=new sp_instr_stmt(sp->instructions(),
+                                                 lex->spcont, lex);
+
+              /* Extract the query statement from the tokenizer:
+                 The end is either lex->tok_end or tok->ptr. */
+              if (lex->ptr - lex->tok_end > 1)
+                i->m_query.length= lex->ptr - sp->m_tmp_query;
+              else
+                i->m_query.length= lex->tok_end - sp->m_tmp_query;
+              i->m_query.str= strmake_root(YYTHD->mem_root,
+                                           (char *)sp->m_tmp_query,
+                                           i->m_query.length);
+              sp->add_instr(i);
+            }
 	    sp->restore_lex(YYTHD);
           }
-	| RETURN_SYM expr
+          | RETURN_SYM 
+          { Lex->sphead->reset_lex(YYTHD); }
+          expr
 	  {
 	    LEX *lex= Lex;
+	    sp_head *sp= lex->sphead;
 
-	    if (lex->sphead->m_type == TYPE_ENUM_PROCEDURE)
+	    if (sp->m_type == TYPE_ENUM_PROCEDURE)
 	    {
 	      my_message(ER_SP_BADRETURN, ER(ER_SP_BADRETURN), MYF(0));
 	      YYABORT;
@@ -1903,12 +1912,12 @@ sp_proc_stmt:
 	    {
 	      sp_instr_freturn *i;
 
-	      i= new sp_instr_freturn(lex->sphead->instructions(),
-				      lex->spcont,
-		                      $2, lex->sphead->m_returns);
-	      lex->sphead->add_instr(i);
-	      lex->sphead->m_has_return= TRUE;
+	      i= new sp_instr_freturn(sp->instructions(), lex->spcont,
+		                      $3, sp->m_returns, lex);
+	      sp->add_instr(i);
+	      sp->m_has_return= TRUE;
 	    }
+	    sp->restore_lex(YYTHD);
 	  }
 	| IF sp_if END IF {}
 	| CASE_SYM WHEN_SYM
@@ -1916,7 +1925,9 @@ sp_proc_stmt:
 	    Lex->sphead->m_simple_case= FALSE;
 	  }
 	  sp_case END CASE_SYM {}
-	| CASE_SYM expr WHEN_SYM
+        | CASE_SYM
+          { Lex->sphead->reset_lex(YYTHD); }
+          expr WHEN_SYM
 	  {
 	    /* We "fake" this by using an anonymous variable which we
 	       set to the expression. Note that all WHENs are evaluate
@@ -1925,15 +1936,14 @@ sp_proc_stmt:
 	    LEX *lex= Lex;
 	    uint offset= lex->spcont->current_pvars();
 	    sp_instr_set *i = new sp_instr_set(lex->sphead->instructions(),
-					       lex->spcont,
-	                                       offset, $2, MYSQL_TYPE_STRING);
+                                               lex->spcont, offset, $3,
+                                               MYSQL_TYPE_STRING, lex, TRUE);
 	    LEX_STRING dummy={(char*)"", 0};
 
 	    lex->spcont->push_pvar(&dummy, MYSQL_TYPE_STRING, sp_param_in);
-	    i->tables= lex->query_tables;
-	    lex->query_tables= 0;
 	    lex->sphead->add_instr(i);
 	    lex->sphead->m_simple_case= TRUE;
+            lex->sphead->restore_lex(YYTHD);
 	  }
 	  sp_case END CASE_SYM
 	  {
@@ -2187,18 +2197,19 @@ sp_fetch_list:
 	;
 
 sp_if:
-	  expr THEN_SYM
+          { Lex->sphead->reset_lex(YYTHD); }
+          expr THEN_SYM
 	  {
 	    LEX *lex= Lex;
 	    sp_head *sp= lex->sphead;
 	    sp_pcontext *ctx= lex->spcont;
 	    uint ip= sp->instructions();
-	    sp_instr_jump_if_not *i = new sp_instr_jump_if_not(ip, ctx, $1);
+	    sp_instr_jump_if_not *i = new sp_instr_jump_if_not(ip, ctx,
+                                                               $2, lex);
 
-	    i->tables= lex->query_tables;
-	    lex->query_tables= 0;
 	    sp->push_backpatch(i, ctx->push_label((char *)"", 0));
             sp->add_instr(i);
+            sp->restore_lex(YYTHD);
 	  }
 	  sp_proc_stmts1
 	  {
@@ -2226,7 +2237,8 @@ sp_elseifs:
 	;
 
 sp_case:
-	  expr THEN_SYM
+	  { Lex->sphead->reset_lex(YYTHD); }
+          expr THEN_SYM
 	  {
             LEX *lex= Lex;
 	    sp_head *sp= lex->sphead;
@@ -2235,7 +2247,7 @@ sp_case:
 	    sp_instr_jump_if_not *i;
 
 	    if (! sp->m_simple_case)
-	      i= new sp_instr_jump_if_not(ip, ctx, $1);
+	      i= new sp_instr_jump_if_not(ip, ctx, $2, lex);
 	    else
 	    { /* Simple case: <caseval> = <whenval> */
 	      LEX_STRING ivar;
@@ -2244,15 +2256,14 @@ sp_case:
 	      ivar.length= 5;
 	      Item *var= (Item*) new Item_splocal(ivar, 
 						  ctx->current_pvars()-1);
-	      Item *expr= new Item_func_eq(var, $1);
+	      Item *expr= new Item_func_eq(var, $2);
 
-	      i= new sp_instr_jump_if_not(ip, ctx, expr);
+	      i= new sp_instr_jump_if_not(ip, ctx, expr, lex);
               lex->variables_used= 1;
 	    }
 	    sp->push_backpatch(i, ctx->push_label((char *)"", 0));
-	    i->tables= lex->query_tables;
-	    lex->query_tables= 0;
             sp->add_instr(i);
+            sp->restore_lex(YYTHD);
 	  }
 	  sp_proc_stmts1
 	  {
@@ -2368,19 +2379,20 @@ sp_unlabeled_control:
 
 	    lex->sphead->add_instr(i);
 	  }
-	| WHILE_SYM expr DO_SYM
+        | WHILE_SYM 
+          { Lex->sphead->reset_lex(YYTHD); }
+          expr DO_SYM
 	  {
 	    LEX *lex= Lex;
 	    sp_head *sp= lex->sphead;
 	    uint ip= sp->instructions();
 	    sp_instr_jump_if_not *i = new sp_instr_jump_if_not(ip, lex->spcont,
-							       $2);
+							       $3, lex);
 
 	    /* Jumping forward */
 	    sp->push_backpatch(i, lex->spcont->last_label());
-	    i->tables= lex->query_tables;
-	    lex->query_tables= 0;
             sp->add_instr(i);
+            sp->restore_lex(YYTHD);
 	  }
 	  sp_proc_stmts1 END WHILE_SYM
 	  {
@@ -2391,17 +2403,18 @@ sp_unlabeled_control:
 
 	    lex->sphead->add_instr(i);
 	  }
-	| REPEAT_SYM sp_proc_stmts1 UNTIL_SYM expr END REPEAT_SYM
+        | REPEAT_SYM sp_proc_stmts1 UNTIL_SYM 
+          { Lex->sphead->reset_lex(YYTHD); }
+          expr END REPEAT_SYM
 	  {
 	    LEX *lex= Lex;
 	    uint ip= lex->sphead->instructions();
 	    sp_label_t *lab= lex->spcont->last_label();  /* Jumping back */
 	    sp_instr_jump_if_not *i = new sp_instr_jump_if_not(ip, lex->spcont,
-							       $4, lab->ip);
-
-	    i->tables= lex->query_tables;
-	    lex->query_tables= 0;
+                                                               $5, lab->ip,
+                                                               lex);
             lex->sphead->add_instr(i);
+            lex->sphead->restore_lex(YYTHD);
 	  }
 	;
 
@@ -7168,8 +7181,75 @@ opt_option:
 	| OPTION {};
 
 option_value_list:
+	option_type_value
+	| option_value_list ',' option_type_value;
+
+option_type_value:
+        {
+          if (Lex->sphead)
+          {
+            /*
+              If we are in SP we want have own LEX for each assignment.
+              This is mostly because it is hard for several sp_instr_set
+              and sp_instr_set_trigger instructions share one LEX. 
+              (Well, it is theoretically possible but adds some extra 
+               overhead on preparation for execution stage and IMO less
+               robust).
+
+              QQ: May be we should simply prohibit group assignments in SP?
+            */
+            LEX *lex;
+            Lex->sphead->reset_lex(YYTHD);
+            lex= Lex;
+            
+            /* Set new LEX as if we at start of set rule. */
+	    lex->sql_command= SQLCOM_SET_OPTION;
+	    mysql_init_select(lex);
+	    lex->option_type=OPT_SESSION;
+	    lex->var_list.empty();
+            lex->one_shot_set= 0;
+	    lex->sphead->m_tmp_query= lex->tok_start;
+          }
+        }
 	option_type option_value
-	| option_value_list ',' option_type option_value;
+        {
+          LEX *lex= Lex;
+          
+          if (lex->sphead)
+          {
+            sp_head *sp= lex->sphead;
+            
+	    if (!lex->var_list.is_empty())
+	    {
+              /*
+                We have assignment to user or system variable or
+                option setting, so we should construct sp_instr_stmt
+                for it.
+              */
+              LEX_STRING qbuff;
+	      sp_instr_stmt *i;
+              
+              if (!(i= new sp_instr_stmt(sp->instructions(), lex->spcont,
+                                         lex)))
+                YYABORT;
+                
+              if (lex->ptr - lex->tok_end > 1)
+                qbuff.length= lex->ptr - sp->m_tmp_query;
+              else
+                qbuff.length= lex->tok_end - sp->m_tmp_query;
+              
+              if (!(qbuff.str= alloc_root(YYTHD->mem_root, qbuff.length + 5)))
+                YYABORT;
+                
+              strmake(strmake(qbuff.str, "SET ", 4), (char *)sp->m_tmp_query,
+                      qbuff.length);
+              qbuff.length+= 4;
+              i->m_query= qbuff;
+              sp->add_instr(i);
+            }
+            lex->sphead->restore_lex(YYTHD);
+          }
+        };
 
 option_type:
 	/* empty */	{}
@@ -7196,31 +7276,7 @@ opt_var_ident_type:
 option_value:
 	  '@' ident_or_text equal expr
 	  {
-            LEX *lex= Lex;
-
-            if (lex->sphead && lex->sphead->m_type != TYPE_ENUM_PROCEDURE)
-            {
-              /*
-                We have to use special instruction in functions and triggers
-                because sp_instr_stmt will close all tables and thus ruin
-                execution of statement invoking function or trigger.
-
-                We also do not want to allow expression with subselects in
-                this case.
-              */
-              if (lex->query_tables)
-              {
-                my_message(ER_SP_SUBSELECT_NYI, ER(ER_SP_SUBSELECT_NYI),
-                           MYF(0));
-                YYABORT;
-              }
-              sp_instr_set_user_var *i= 
-                new sp_instr_set_user_var(lex->sphead->instructions(),
-                                          lex->spcont, $2, $4);
-	      lex->sphead->add_instr(i);
-            }
-            else
-              lex->var_list.push_back(new set_var_user(new Item_func_set_user_var($2,$4)));
+            Lex->var_list.push_back(new set_var_user(new Item_func_set_user_var($2,$4)));
 	  }
 	| internal_variable_name equal set_expr_or_default
 	  {
@@ -7281,9 +7337,7 @@ option_value:
 	      else
 	        it= new Item_null();
               i= new sp_instr_set(lex->sphead->instructions(), ctx,
-	                          spv->offset, it, spv->type);
-	      i->tables= lex->query_tables;
-	      lex->query_tables= 0;
+	                          spv->offset, it, spv->type, lex, TRUE);
 	      lex->sphead->add_instr(i);
 	      spv->isset= TRUE;
 	    }
diff --git a/sql/table.cc b/sql/table.cc
index 31d20271707..63da10c687a 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -1276,6 +1276,10 @@ File create_frm(register my_string name, uint reclength, uchar *fileinfo,
   uint key_length;
   ulong length;
   char fill[IO_SIZE];
+  int create_flags= O_RDWR | O_TRUNC;
+
+  if (create_info->options & HA_LEX_CREATE_TMP_TABLE)
+    create_flags|= O_EXCL | O_NOFOLLOW;
 
 #if SIZEOF_OFF_T > 4
   /* Fix this when we have new .frm files;  Current limit is 4G rows (QQ) */
@@ -1290,7 +1294,7 @@ File create_frm(register my_string name, uint reclength, uchar *fileinfo,
   */
   set_if_smaller(create_info->raid_chunks, 255);
 
-  if ((file=my_create(name,CREATE_MODE,O_RDWR | O_TRUNC,MYF(MY_WME))) >= 0)
+  if ((file= my_create(name, CREATE_MODE, create_flags, MYF(MY_WME))) >= 0)
   {
     bzero((char*) fileinfo,64);
     /* header */
diff --git a/sql/table.h b/sql/table.h
index 4306d3733c4..49ead2cb0b7 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -429,6 +429,11 @@ typedef struct st_table_list
   /* FRMTYPE_ERROR if any type is acceptable */
   enum frm_type_enum required_type;
   char		timestamp_buffer[20];	/* buffer for timestamp (19+1) */
+  /*
+    This TABLE_LIST object is just placeholder for prelocking, it will be
+    used for implicit LOCK TABLES only and won't be used in real statement.
+  */
+  bool          prelocking_placeholder;
 
   void calc_md5(char *buffer);
   void set_ancestor();
diff --git a/sql/tztime.cc b/sql/tztime.cc
index b2b3576e221..bd9d49f0ab0 100644
--- a/sql/tztime.cc
+++ b/sql/tztime.cc
@@ -1524,7 +1524,6 @@ my_tz_init(THD *org_thd, const char *default_tzname, my_bool bootstrap)
   TZ_NAMES_ENTRY *tmp_tzname;
   my_bool return_val= 1;
   int res;
-  uint counter;
   DBUG_ENTER("my_tz_init");
 
   /*
@@ -1593,8 +1592,7 @@ my_tz_init(THD *org_thd, const char *default_tzname, my_bool bootstrap)
   last_global_next_ptr= &(tables_buff[0].next_global);
   tz_init_table_list(tables_buff + 1, &last_global_next_ptr);
 
-  if (open_tables(thd, tables_buff, &counter) ||
-      lock_tables(thd, tables_buff, counter))
+  if (simple_open_n_lock_tables(thd, tables_buff))
   {
     sql_print_warning("Can't open and lock time zone table: %s "
                       "trying to live without them", thd->net.last_error);
diff --git a/strings/decimal.c b/strings/decimal.c
index e6d2d9b14dd..90e64ae892f 100644
--- a/strings/decimal.c
+++ b/strings/decimal.c
@@ -1199,7 +1199,10 @@ int decimal2bin(decimal *from, char *to, int precision, int frac)
   else if (fsize0 > fsize1 && frac1x)
   {
     if (frac0 == frac1)
+    {
       frac1x=frac0x;
+      fsize0= fsize1;
+    }
     else
     {
       frac1++;
diff --git a/vio/vio.c b/vio/vio.c
index ea254e2ed5a..6227493b994 100644
--- a/vio/vio.c
+++ b/vio/vio.c
@@ -27,20 +27,23 @@
  * Helper to fill most of the Vio* with defaults.
  */
 
-void vio_reset(Vio* vio, enum enum_vio_type type,
-		      my_socket sd, HANDLE hPipe,
-		      my_bool localhost)
+static void vio_init(Vio* vio, enum enum_vio_type type,
+                     my_socket sd, HANDLE hPipe, uint flags)
 {
-  DBUG_ENTER("vio_reset");
-  DBUG_PRINT("enter", ("type: %d  sd: %d  localhost: %d", type, sd,
-                       localhost));
+  DBUG_ENTER("vio_init");
+  DBUG_PRINT("enter", ("type: %d  sd: %d  flags: %d", type, sd, flags));
 
+#ifndef HAVE_VIO_READ_BUFF
+  flags&= ~VIO_BUFFERED_READ;
+#endif
   bzero((char*) vio, sizeof(*vio));
   vio->type	= type;
   vio->sd	= sd;
   vio->hPipe	= hPipe;
-  vio->localhost= localhost;
-#ifdef HAVE_VIO  
+  vio->localhost= flags & VIO_LOCALHOST;
+  if ((flags & VIO_BUFFERED_READ) &&
+      !(vio->read_buffer= (char*)my_malloc(VIO_READ_BUFFER_SIZE, MYF(MY_WME))))
+    flags&= ~VIO_BUFFERED_READ;
 #ifdef __WIN__ 
   if (type == VIO_TYPE_NAMEDPIPE)
   {
@@ -101,7 +104,7 @@ void vio_reset(Vio* vio, enum enum_vio_type type,
   {
     vio->viodelete	=vio_delete;
     vio->vioerrno	=vio_errno;
-    vio->read		=vio_read;
+    vio->read= (flags & VIO_BUFFERED_READ) ? vio_read_buff : vio_read;
     vio->write		=vio_write;
     vio->fastsend	=vio_fastsend;
     vio->viokeepalive	=vio_keepalive;
@@ -113,21 +116,30 @@ void vio_reset(Vio* vio, enum enum_vio_type type,
     vio->is_blocking	=vio_is_blocking;
     vio->timeout	=vio_timeout;
   }
-#endif /* HAVE_VIO */
   DBUG_VOID_RETURN;
 }
 
 
+/* Reset initialized VIO to use with another transport type */
+
+void vio_reset(Vio* vio, enum enum_vio_type type,
+               my_socket sd, HANDLE hPipe, uint flags)
+{
+  my_free(vio->read_buffer, MYF(MY_ALLOW_ZERO_PTR));
+  vio_init(vio, type, sd, hPipe, flags);
+}
+
+
 /* Open the socket or TCP/IP connection and read the fnctl() status */
 
-Vio *vio_new(my_socket sd, enum enum_vio_type type, my_bool localhost)
+Vio *vio_new(my_socket sd, enum enum_vio_type type, uint flags)
 {
   Vio *vio;
   DBUG_ENTER("vio_new");
   DBUG_PRINT("enter", ("sd: %d", sd));
   if ((vio = (Vio*) my_malloc(sizeof(*vio),MYF(MY_WME))))
   {
-    vio_reset(vio, type, sd, 0, localhost);
+    vio_init(vio, type, sd, 0, flags);
     sprintf(vio->desc,
 	    (vio->type == VIO_TYPE_SOCKET ? "socket (%d)" : "TCP/IP (%d)"),
 	    vio->sd);
@@ -163,7 +175,7 @@ Vio *vio_new_win32pipe(HANDLE hPipe)
   DBUG_ENTER("vio_new_handle");
   if ((vio = (Vio*) my_malloc(sizeof(Vio),MYF(MY_WME))))
   {
-    vio_reset(vio, VIO_TYPE_NAMEDPIPE, 0, hPipe, TRUE);
+    vio_init(vio, VIO_TYPE_NAMEDPIPE, 0, hPipe, VIO_LOCALHOST);
     strmov(vio->desc, "named pipe");
   }
   DBUG_RETURN(vio);
@@ -179,7 +191,7 @@ Vio *vio_new_win32shared_memory(NET *net,HANDLE handle_file_map, HANDLE handle_m
   DBUG_ENTER("vio_new_win32shared_memory");
   if ((vio = (Vio*) my_malloc(sizeof(Vio),MYF(MY_WME))))
   {
-    vio_reset(vio, VIO_TYPE_SHARED_MEMORY, 0, 0, TRUE);
+    vio_init(vio, VIO_TYPE_SHARED_MEMORY, 0, 0, VIO_LOCALHOST);
     vio->handle_file_map= handle_file_map;
     vio->handle_map= handle_map;
     vio->event_server_wrote= event_server_wrote;
@@ -204,11 +216,8 @@ void vio_delete(Vio* vio)
   if (vio)
   {
     if (vio->type != VIO_CLOSED)
-#ifdef HAVE_VIO /*WAX*/
       vio->vioclose(vio);
-#else
-      vio_close(vio);
-#endif
+    my_free((gptr) vio->read_buffer, MYF(MY_ALLOW_ZERO_PTR));
     my_free((gptr) vio,MYF(0));
   }
 }
diff --git a/vio/viosocket.c b/vio/viosocket.c
index 43bd4e82013..ea85a69e2d4 100644
--- a/vio/viosocket.c
+++ b/vio/viosocket.c
@@ -35,6 +35,8 @@ int vio_read(Vio * vio, gptr buf, int size)
   DBUG_ENTER("vio_read");
   DBUG_PRINT("enter", ("sd: %d, buf: 0x%p, size: %d", vio->sd, buf, size));
 
+  /* Ensure nobody uses vio_read_buff and vio_read simultaneously */
+  DBUG_ASSERT(vio->read_end == vio->read_pos);
 #ifdef __WIN__
   r = recv(vio->sd, buf, size,0);
 #else
@@ -52,6 +54,50 @@ int vio_read(Vio * vio, gptr buf, int size)
 }
 
 
+/*
+  Buffered read: if average read size is small it may
+  reduce number of syscalls.
+*/
+
+int vio_read_buff(Vio *vio, gptr buf, int size)
+{
+  int rc;
+#define VIO_UNBUFFERED_READ_MIN_SIZE 2048
+  DBUG_ENTER("vio_read_buff");
+  DBUG_PRINT("enter", ("sd: %d, buf: 0x%p, size: %d", vio->sd, buf, size));
+
+  if (vio->read_pos < vio->read_end)
+  {
+    rc= min(vio->read_end - vio->read_pos, size);
+    memcpy(buf, vio->read_pos, rc);
+    vio->read_pos+= rc;
+    /*
+      Do not try to read from the socket now even if rc < size:
+      vio_read can return -1 due to an error or non-blocking mode, and
+      the safest way to handle it is to move to a separate branch.
+    */
+  }
+  else if (size < VIO_UNBUFFERED_READ_MIN_SIZE)
+  {
+    rc= vio_read(vio, vio->read_buffer, VIO_READ_BUFFER_SIZE);
+    if (rc > 0)
+    {
+      if (rc > size)
+      {
+        vio->read_pos= vio->read_buffer + size;
+        vio->read_end= vio->read_buffer + rc;
+        rc= size;
+      }
+      memcpy(buf, vio->read_buffer, rc);
+    }
+  }
+  else
+    rc= vio_read(vio, buf, size);
+  DBUG_RETURN(rc);
+#undef VIO_UNBUFFERED_READ_MIN_SIZE
+}
+
+
 int vio_write(Vio * vio, const gptr buf, int size)
 {
   int r;