Bugfixes in SHOW CREATE PROCEDURE/FUNCTION and SHOW PROCEDURE/FUNCTION STATUS;

- dropped routines should not show up in status
- error handling for non-existing routines

+ some cleanup.


mysql-test/r/sp-error.result:
  Test SHOW CREATE PROCEDURE for non-existing procedure.
mysql-test/r/sp.result:
  Additional SHOW FUNCTION/PROCEDURE STATUS calls (make sure they don't show
  after being dropped).
mysql-test/t/sp-error.test:
  Test SHOW CREATE PROCEDURE for non-existing procedure.
mysql-test/t/sp.test:
  Additional SHOW FUNCTION/PROCEDURE STATUS calls (make sure they don't show
  after being dropped).
sql/sp.cc:
  Fixed bug in SHOW ... STATUS after a routine has been dropped,
  and fixed the error return codes (for correct error handling).
  Also some general cleanup.
sql/sp.h:
  Fixed prefix for external functions (should be sp_, not db_).
sql/sql_parse.cc:
  Fixed error handling in SHOW CREATE PROCEDURE/FUNCTION.

3 files changed, 68 insertions, 49 deletions

diff --git a/sql/sp.cc b/sql/sp.cc
index bf8cf37f293..b725614dfb8 100644
--- a/sql/sp.cc
+++ b/sql/sp.cc
@@ -322,9 +322,10 @@ static struct st_used_field init_fields[]=
   { 0,                 0, MYSQL_TYPE_STRING,    0}
 };
 
-int print_field_values(THD *thd, TABLE *table,
-		       struct st_used_field *used_fields,
-		       int type, const char *wild)
+static int
+print_field_values(THD *thd, TABLE *table,
+		   struct st_used_field *used_fields,
+		   int type, const char *wild)
 {
   Protocol *protocol= thd->protocol;
 
@@ -332,9 +333,8 @@ int print_field_values(THD *thd, TABLE *table,
   {
     String *tmp_string= new String();
     struct st_used_field *used_field= used_fields;
-    get_field(&thd->mem_root,
-	      used_field->field,
-	      tmp_string);
+
+    get_field(&thd->mem_root, used_field->field, tmp_string);
     if (!wild || !wild[0] || !wild_compare(tmp_string->ptr(), wild, 0))
     {
       protocol->prepare_for_resend();
@@ -345,35 +345,36 @@ int print_field_values(THD *thd, TABLE *table,
       {
 	switch (used_field->field_type) {
 	case MYSQL_TYPE_TIMESTAMP:
-	{
-	  TIME tmp_time;
-	  ((Field_timestamp *) used_field->field)->get_time(&tmp_time);
-	  protocol->store(&tmp_time);
-	}
-	break;
+	  {
+	    TIME tmp_time;
+	    ((Field_timestamp *) used_field->field)->get_time(&tmp_time);
+	    protocol->store(&tmp_time);
+	  }
+	  break;
 	default:
-	{
-	  String *tmp_string1= new String();
-	  get_field(&thd->mem_root, used_field->field, tmp_string1);
-	  protocol->store(tmp_string1);
-	}
-	break;
+	  {
+	    String *tmp_string1= new String();
+	    get_field(&thd->mem_root, used_field->field, tmp_string1);
+	    protocol->store(tmp_string1);
+	  }
+	  break;
 	}
       }
       if (protocol->write())
-	return 1;
+	return SP_INTERNAL_ERROR;
     }
   }
-  return 0;
+  return SP_OK;
 }
 
-int
+static int
 db_show_routine_status(THD *thd, int type, const char *wild)
 {
   DBUG_ENTER("db_show_routine_status");
 
   TABLE *table;
   TABLE_LIST tables;
+  int res;
 
   memset(&tables, 0, sizeof(tables));
   tables.db= (char*)"mysql";
@@ -381,7 +382,8 @@ db_show_routine_status(THD *thd, int type, const char *wild)
 
   if (! (table= open_ltable(thd, &tables, TL_READ)))
   {
-    DBUG_RETURN(1);
+    res= SP_OPEN_TABLE_FAILED;
+    goto done;
   }
   else
   {
@@ -389,6 +391,7 @@ db_show_routine_status(THD *thd, int type, const char *wild)
     List<Item> field_list;
     struct st_used_field *used_field;
     st_used_field used_fields[array_elements(init_fields)];
+
     memcpy((char*) used_fields, (char*) init_fields, sizeof(used_fields));
     /* Init header */
     for (used_field= &used_fields[0];
@@ -408,7 +411,10 @@ db_show_routine_status(THD *thd, int type, const char *wild)
     }
     /* Print header */
     if (thd->protocol->send_fields(&field_list,1))
+    {
+      res= SP_INTERNAL_ERROR;
       goto err_case;
+    }
 
     /* Init fields */
     setup_tables(&tables);
@@ -421,27 +427,37 @@ db_show_routine_status(THD *thd, int type, const char *wild)
 					used_field->field_name);
       if (!(used_field->field= find_field_in_tables(thd, field, &tables, 
 						    &not_used, TRUE)))
+      {
+	res= SP_INTERNAL_ERROR;
 	goto err_case1;
+      }
     }
 
     table->file->index_init(0);
-    table->file->index_first(table->record[0]);
-    if (print_field_values(thd, table, used_fields, type, wild))
+    if ((res= table->file->index_first(table->record[0])))
+    {
+      if (res == HA_ERR_END_OF_FILE)
+	res= 0;
+      else
+	res= SP_INTERNAL_ERROR;
+      goto err_case1;
+    }
+    if ((res= print_field_values(thd, table, used_fields, type, wild)))
       goto err_case1;
     while (!table->file->index_next(table->record[0]))
     {
-      if (print_field_values(thd, table, used_fields, type, wild))
+      if ((res= print_field_values(thd, table, used_fields, type, wild)))
 	goto err_case1;
     }
-    send_eof(thd);
-    close_thread_tables(thd);
-    DBUG_RETURN(0);
+    res= SP_OK;
   }
-err_case1:
+
+ err_case1:
   send_eof(thd);
-err_case:
+ err_case:
   close_thread_tables(thd);
-  DBUG_RETURN(1);
+ done:
+  DBUG_RETURN(res);
 }
 
 
@@ -530,16 +546,16 @@ sp_show_create_procedure(THD *thd, LEX_STRING *name)
   sp_head *sp;
 
   sp= sp_find_procedure(thd, name);
-  if (sp)
-    DBUG_RETURN(sp->show_create_procedure(thd));
+  if (sp) 
+   DBUG_RETURN(sp->show_create_procedure(thd));
 
-  DBUG_RETURN(1);
+  DBUG_RETURN(SP_KEY_NOT_FOUND);
 }
 
 int
-db_show_status_procedure(THD *thd, const char *wild)
+sp_show_status_procedure(THD *thd, const char *wild)
 {
-  DBUG_ENTER("db_show_status_procedure");
+  DBUG_ENTER("sp_show_status_procedure");
   DBUG_RETURN(db_show_routine_status(thd, TYPE_ENUM_PROCEDURE, wild));
 }
 
@@ -628,13 +644,13 @@ sp_show_create_function(THD *thd, LEX_STRING *name)
   if (sp)
     DBUG_RETURN(sp->show_create_function(thd));
 
-  DBUG_RETURN(1);
+  DBUG_RETURN(SP_KEY_NOT_FOUND);
 }
 
 int
-db_show_status_function(THD *thd, const char *wild)
+sp_show_status_function(THD *thd, const char *wild)
 {
-  DBUG_ENTER("db_show_status_function");
+  DBUG_ENTER("sp_show_status_function");
   DBUG_RETURN(db_show_routine_status(thd, TYPE_ENUM_FUNCTION, wild));
 }
 
diff --git a/sql/sp.h b/sql/sp.h
index e7c2fba3bba..f957dd3c692 100644
--- a/sql/sp.h
+++ b/sql/sp.h
@@ -18,7 +18,7 @@
 #ifndef _SP_H_
 #define _SP_H_
 
-// Return codes from sp_create_* and sp_drop_*:
+// Return codes from sp_create_*, sp_drop_*, and sp_show_*:
 #define SP_OK                 0
 #define SP_KEY_NOT_FOUND     -1
 #define SP_OPEN_TABLE_FAILED -2
@@ -26,6 +26,7 @@
 #define SP_DELETE_ROW_FAILED -4
 #define SP_GET_FIELD_FAILED  -5
 #define SP_PARSE_ERROR       -6
+#define SP_INTERNAL_ERROR    -7
 
 sp_head *
 sp_find_procedure(THD *thd, LEX_STRING *name);
@@ -47,7 +48,7 @@ int
 sp_show_create_procedure(THD *thd, LEX_STRING *name);
 
 int
-db_show_status_procedure(THD *thd, const char *wild);
+sp_show_status_procedure(THD *thd, const char *wild);
 
 sp_head *
 sp_find_function(THD *thd, LEX_STRING *name);
@@ -68,7 +69,7 @@ int
 sp_show_create_function(THD *thd, LEX_STRING *name);
 
 int
-db_show_status_function(THD *thd, const char *wild);
+sp_show_status_function(THD *thd, const char *wild);
 
 // QQ Temporary until the function call detection in sql_lex has been reworked.
 bool
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index bb9f48231d7..c4a1251cd3a 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -3592,10 +3592,11 @@ mysql_execute_command(THD *thd)
 	goto error;
       }
       res= sp_show_create_procedure(thd, &lex->udf.name);
-      if (res == SP_KEY_NOT_FOUND)
-      {
-	net_printf(thd, ER_SP_DOES_NOT_EXIST, 
+      if (res != SP_OK)
+      {			/* We don't distinguish between errors for now */
+	net_printf(thd, ER_SP_DOES_NOT_EXIST,
 		   SP_COM_STRING(lex), lex->udf.name.str);
+	res= 0;
 	goto error;
       }
       break;
@@ -3608,23 +3609,24 @@ mysql_execute_command(THD *thd)
 	goto error;
       }
       res= sp_show_create_function(thd, &lex->udf.name);
-      if (res == SP_KEY_NOT_FOUND)
-      {
+      if (res != SP_OK)
+      {			/* We don't distinguish between errors for now */
 	net_printf(thd, ER_SP_DOES_NOT_EXIST,
 		   SP_COM_STRING(lex), lex->udf.name.str);
+	res= 0;
 	goto error;
       }
       break;
     }
   case SQLCOM_SHOW_STATUS_PROC:
     {
-      res= db_show_status_procedure(thd, (lex->wild ?
+      res= sp_show_status_procedure(thd, (lex->wild ?
 					  lex->wild->ptr() : NullS));
       break;
     }
   case SQLCOM_SHOW_STATUS_FUNC:
     {
-      res= db_show_status_function(thd, (lex->wild ? 
+      res= sp_show_status_function(thd, (lex->wild ? 
 					 lex->wild->ptr() : NullS));
       break;
     }