Fixed BUG#3583: query cache doesn't work for stored procedures.

mysql-test/r/sp.result:
  New test case for BUG#3583.
  (And current query is now set correctly.)
mysql-test/t/sp.test:
  New test case for BUG#3583.
sql/sp_head.cc:
  Set the thd->query and try to use cached query, if any, when executing a statement.
sql/sp_head.h:
  Extract the sub-query and store in sp_instr_stmt.
sql/sql_cache.cc:
  Keep the net->pkt_nr up-to-date when using query cache.
  This makes it work with stored procedures too.
sql/sql_cache.h:
  Keep the net->pkt_nr up-to-date when using query cache.
  This makes it work with stored procedures too.
sql/sql_yacc.yy:
  Extract the sub-query and store in sp_instr_stmt.
  (And it's never safe to cache references to local variables.)

5 files changed, 47 insertions, 8 deletions

diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index 6e4269ad8a5..6a17bc189c3 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -1153,9 +1153,25 @@ sp_instr_stmt::~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));
-  int res= exec_stmt(thd, m_lex);
+  int res;
+
+  query= thd->query;
+  query_length= thd->query_length;
+  if (!(res= alloc_query(thd, m_query.str, m_query.length+1)))
+  {
+    if (query_cache_send_result_to_client(thd,
+					  thd->query, thd->query_length) <= 0)
+    {
+      res= exec_stmt(thd, m_lex);
+      query_cache_end_of_result(thd);
+    }
+    thd->query= query;
+    thd->query_length= query_length;
+  }
   *nextp = m_ip+1;
   DBUG_RETURN(res);
 }
diff --git a/sql/sp_head.h b/sql/sp_head.h
index 9c308961aa4..6aa05838291 100644
--- a/sql/sp_head.h
+++ b/sql/sp_head.h
@@ -88,6 +88,7 @@ public:
   my_bool m_simple_case;	// TRUE if parsing simple case, FALSE otherwise
   my_bool m_multi_results;	// TRUE if a procedure with SELECT(s)
   my_bool m_in_handler;		// TRUE if parser in a handler body
+  uchar *m_tmp_query;		// Temporary pointer to sub query string
   uint m_old_cmq;		// Old CLIENT_MULTI_QUERIES value
   st_sp_chistics *m_chistics;
   ulong m_sql_mode;		// For SHOW CREATE
@@ -314,9 +315,14 @@ class sp_instr_stmt : public sp_instr
 
 public:
 
+  LEX_STRING m_query;		// For thd->query
+
   sp_instr_stmt(uint ip, sp_pcontext *ctx)
     : sp_instr(ip, ctx), m_lex(NULL)
-  {}
+  {
+    m_query.str= 0;
+    m_query.length= 0;
+  }
 
   virtual ~sp_instr_stmt();
 
diff --git a/sql/sql_cache.cc b/sql/sql_cache.cc
index 8ab7106978c..35bf6f8701f 100644
--- a/sql/sql_cache.cc
+++ b/sql/sql_cache.cc
@@ -611,6 +611,7 @@ void query_cache_insert(NET *net, const char *packet, ulong length)
       DBUG_VOID_RETURN;
     }
     header->result(result);
+    header->last_pkt_nr= net->pkt_nr;
     BLOCK_UNLOCK_WR(query_block);
   }
   else
@@ -1085,6 +1086,7 @@ Query_cache::send_result_to_client(THD *thd, char *sql, uint query_length)
 		       ALIGN_SIZE(sizeof(Query_cache_result))))
       break;					// Client aborted
     result_block = result_block->next;
+    thd->net.pkt_nr= query->last_pkt_nr; // Keep packet number updated
   } while (result_block != first_result_block);
 #else
   {
diff --git a/sql/sql_cache.h b/sql/sql_cache.h
index 432c7659aa5..b54bc4f7b7b 100644
--- a/sql/sql_cache.h
+++ b/sql/sql_cache.h
@@ -116,6 +116,7 @@ struct Query_cache_query
   NET *wri;
   ulong len;
   uint8 tbls_type;
+  unsigned int last_pkt_nr;
 
   inline void init_n_lock();
   void unlock_n_destroy();
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 920f6fd4a82..79c5c094c58 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -1788,17 +1788,21 @@ sp_opt_default:
 
 sp_proc_stmt:
 	  {
-	    Lex->sphead->reset_lex(YYTHD);
+	    LEX *lex= Lex;
+
+	    lex->sphead->reset_lex(YYTHD);
+	    lex->sphead->m_tmp_query= lex->tok_start;
 	  }
 	  statement
 	  {
 	    LEX *lex= Lex;
+	    sp_head *sp= lex->sphead;
 
 	    if ((lex->sql_command == SQLCOM_SELECT && !lex->result) ||
 	        sp_multi_results_command(lex->sql_command))
 	    {
 	      /* We maybe have one or more SELECT without INTO */
-	      lex->sphead->m_multi_results= TRUE;
+	      sp->m_multi_results= TRUE;
 	    }
 	    if (lex->sql_command == SQLCOM_CHANGE_DB)
 	    { /* "USE db" doesn't work in a procedure */
@@ -1819,22 +1823,31 @@ sp_proc_stmt:
                 especially triggers a tremendously, but it's nothing we 
                 can do about this at the moment.
               */
-	      if (lex->sphead->m_type != TYPE_ENUM_PROCEDURE)
+	      if (sp->m_type != TYPE_ENUM_PROCEDURE)
 	      {
 		send_error(YYTHD, ER_SP_BADSTATEMENT);
 		YYABORT;
 	      }
 	      else
 	      {
-		sp_instr_stmt *i=new sp_instr_stmt(lex->sphead->instructions(),
+		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);
-		lex->sphead->add_instr(i);
+		sp->add_instr(i);
 		lex->sp_lex_in_use= TRUE;
 	      }
             }
-	    lex->sphead->restore_lex(YYTHD);
+	    sp->restore_lex(YYTHD);
           }
 	| RETURN_SYM expr
 	  {
@@ -6482,6 +6495,7 @@ simple_ident:
 	    }
 	    $$ = (Item*) new Item_splocal($1, spv->offset);
             lex->variables_used= 1;
+	    lex->safe_to_cache_query=0;
 	  }
 	  else
 	  {