MDEV-18813 PROCEDURE and anonymous blocks silently ignore FETCH GROUP NEXT ROW

Part#2 (final): rewritting the code to pass the correct enum_sp_aggregate_type
to the sp_head constructor, so sp_head never changes its aggregation type
later on. The grammar has been simplified and defragmented.
This allowed to check aggregate specific instructions right after
a routine body has been scanned, by calling new LEX methods:
  sp_body_finalize_{procedure|function|trigger|event}()

Moving some C++ code from *.yy to a few new helper methods in LEX.

12 files changed, 567 insertions, 275 deletions

diff --git a/mysql-test/main/custom_aggregate_functions.result b/mysql-test/main/custom_aggregate_functions.result
index 0a27334f58e..b98954b920e 100644
--- a/mysql-test/main/custom_aggregate_functions.result
+++ b/mysql-test/main/custom_aggregate_functions.result
@@ -37,7 +37,7 @@ set x=5;
 fetch group next row;
 return x+1;
 end |
-ERROR HY000: Non-aggregate function contains aggregate specific instructions: (FETCH GROUP NEXT ROW)
+ERROR HY000: Aggregate specific instruction (FETCH GROUP NEXT ROW) used in a wrong context
 create aggregate function f1(x INT) returns INT
 begin
 declare continue handler for not found return x;
@@ -1153,3 +1153,36 @@ i	sum(i)
 NULL	8
 drop function agg_sum;
 drop table t1;
+#
+# MDEV-18813 PROCEDURE and anonymous blocks silently ignore FETCH GROUP NEXT ROW
+#
+CREATE PROCEDURE p1()
+BEGIN
+FETCH GROUP NEXT ROW;
+END;
+$$
+ERROR HY000: Aggregate specific instruction (FETCH GROUP NEXT ROW) used in a wrong context
+BEGIN NOT ATOMIC
+FETCH GROUP NEXT ROW;
+END;
+$$
+ERROR HY000: Aggregate specific instruction (FETCH GROUP NEXT ROW) used in a wrong context
+CREATE DEFINER=root@localhost  FUNCTION f1() RETURNS INT
+BEGIN
+FETCH GROUP NEXT ROW;
+RETURN 0;
+END;
+$$
+ERROR HY000: Aggregate specific instruction (FETCH GROUP NEXT ROW) used in a wrong context
+CREATE TABLE t1 (a INT);
+CREATE TRIGGER tr1
+AFTER INSERT ON t1 FOR EACH ROW 
+FETCH GROUP NEXT ROW;
+ERROR HY000: Aggregate specific instruction (FETCH GROUP NEXT ROW) used in a wrong context
+DROP TABLE t1;
+CREATE EVENT ev1
+ON SCHEDULE EVERY 1 HOUR
+STARTS CURRENT_TIMESTAMP + INTERVAL 1 MONTH
+ENDS CURRENT_TIMESTAMP + INTERVAL 1 MONTH + INTERVAL 1 WEEK
+DO FETCH GROUP NEXT ROW;
+ERROR HY000: Aggregate specific instruction (FETCH GROUP NEXT ROW) used in a wrong context
diff --git a/mysql-test/main/custom_aggregate_functions.test b/mysql-test/main/custom_aggregate_functions.test
index ab799b48bdb..a10ea44af60 100644
--- a/mysql-test/main/custom_aggregate_functions.test
+++ b/mysql-test/main/custom_aggregate_functions.test
@@ -965,3 +965,54 @@ select i, sum(i) from t1 group by i with rollup;
 # Cleanup
 drop function agg_sum;
 drop table t1;
+
+
+--echo #
+--echo # MDEV-18813 PROCEDURE and anonymous blocks silently ignore FETCH GROUP NEXT ROW
+--echo #
+
+
+DELIMITER $$;
+--error ER_NOT_AGGREGATE_FUNCTION
+CREATE PROCEDURE p1()
+BEGIN
+  FETCH GROUP NEXT ROW;
+END;
+$$
+DELIMITER ;$$
+
+
+DELIMITER $$;
+--error ER_NOT_AGGREGATE_FUNCTION
+BEGIN NOT ATOMIC
+  FETCH GROUP NEXT ROW;
+END;
+$$
+DELIMITER ;$$
+
+
+DELIMITER $$;
+--error ER_NOT_AGGREGATE_FUNCTION
+CREATE DEFINER=root@localhost  FUNCTION f1() RETURNS INT
+BEGIN
+  FETCH GROUP NEXT ROW;
+  RETURN 0;
+END;
+$$
+DELIMITER ;$$
+
+
+CREATE TABLE t1 (a INT);
+--error ER_NOT_AGGREGATE_FUNCTION
+CREATE TRIGGER tr1
+  AFTER INSERT ON t1 FOR EACH ROW 
+   FETCH GROUP NEXT ROW;
+DROP TABLE t1;
+
+
+--error ER_NOT_AGGREGATE_FUNCTION
+CREATE EVENT ev1
+  ON SCHEDULE EVERY 1 HOUR
+  STARTS CURRENT_TIMESTAMP + INTERVAL 1 MONTH
+  ENDS CURRENT_TIMESTAMP + INTERVAL 1 MONTH + INTERVAL 1 WEEK
+DO FETCH GROUP NEXT ROW;
diff --git a/mysql-test/main/mysqldump.result b/mysql-test/main/mysqldump.result
index c40aa271465..41a719c6a94 100644
--- a/mysql-test/main/mysqldump.result
+++ b/mysql-test/main/mysqldump.result
@@ -5170,7 +5170,7 @@ RETURN CONCAT(']]>, ', c1, '!');
 	<routines>
 		<routine Function="straße" sql_mode="" character_set_client="utf8" collation_connection="utf8_general_ci" Database_Collation="latin1_swedish_ci">
 <![CDATA[
-CREATE DEFINER=`root`@`localhost` FUNCTION `straße`( c1 CHAR(20)) RETURNS char(50) CHARSET latin1
+CREATE DEFINER=`root`@`localhost` FUNCTION `straße`(c1 CHAR(20)) RETURNS char(50) CHARSET latin1
     DETERMINISTIC
 RETURN CONCAT(']]]]><![CDATA[>, ', c1, '!')
 ]]>
diff --git a/mysql-test/suite/compat/oracle/r/custom_aggregate_functions.result b/mysql-test/suite/compat/oracle/r/custom_aggregate_functions.result
index 413eca1b02f..21fac1939bc 100644
--- a/mysql-test/suite/compat/oracle/r/custom_aggregate_functions.result
+++ b/mysql-test/suite/compat/oracle/r/custom_aggregate_functions.result
@@ -11,7 +11,7 @@ set x=5;
 fetch group next row;
 return x+1;
 end |
-ERROR HY000: Non-aggregate function contains aggregate specific instructions: (FETCH GROUP NEXT ROW)
+ERROR HY000: Aggregate specific instruction (FETCH GROUP NEXT ROW) used in a wrong context
 CREATE TABLE marks(stud_id INT, grade_count INT);
 INSERT INTO marks VALUES (1,6), (2,4), (3,7), (4,5), (5,8);
 SELECT * FROM marks;
@@ -56,3 +56,81 @@ aggregate_count(stud_id)
 5
 DROP FUNCTION IF EXISTS aggregate_count;
 DROP TABLE marks;
+#
+# MDEV-18813 PROCEDURE and anonymous blocks silently ignore FETCH GROUP NEXT ROW
+#
+CREATE PROCEDURE p1 AS
+BEGIN
+FETCH GROUP NEXT ROW;
+END;
+$$
+ERROR HY000: Aggregate specific instruction (FETCH GROUP NEXT ROW) used in a wrong context
+BEGIN NOT ATOMIC
+FETCH GROUP NEXT ROW;
+END;
+$$
+ERROR HY000: Aggregate specific instruction (FETCH GROUP NEXT ROW) used in a wrong context
+CREATE DEFINER=root@localhost  FUNCTION f1 RETURN INT AS
+BEGIN
+FETCH GROUP NEXT ROW;
+RETURN 0;
+END;
+$$
+ERROR HY000: Aggregate specific instruction (FETCH GROUP NEXT ROW) used in a wrong context
+CREATE TABLE t1 (a INT);
+CREATE TRIGGER tr1
+AFTER INSERT ON t1 FOR EACH ROW 
+FETCH GROUP NEXT ROW;
+ERROR HY000: Aggregate specific instruction (FETCH GROUP NEXT ROW) used in a wrong context
+DROP TABLE t1;
+CREATE EVENT ev1
+ON SCHEDULE EVERY 1 HOUR
+STARTS CURRENT_TIMESTAMP + INTERVAL 1 MONTH
+ENDS CURRENT_TIMESTAMP + INTERVAL 1 MONTH + INTERVAL 1 WEEK
+DO FETCH GROUP NEXT ROW;
+ERROR HY000: Aggregate specific instruction (FETCH GROUP NEXT ROW) used in a wrong context
+CREATE PACKAGE pkg1 AS
+PROCEDURE p1;
+FUNCTION f1 RETURN INT;
+END;
+$$
+CREATE PACKAGE BODY pkg1 AS
+PROCEDURE p1 AS
+BEGIN
+FETCH GROUP NEXT ROW;  -- In a package procedure
+END;
+FUNCTION f1 RETURN INT AS
+BEGIN
+RETURN 0;
+END;
+END;
+$$
+ERROR HY000: Aggregate specific instruction (FETCH GROUP NEXT ROW) used in a wrong context
+CREATE PACKAGE BODY pkg1 AS
+PROCEDURE p1 AS
+BEGIN
+NULL;
+END;
+FUNCTION f1 RETURN INT AS
+BEGIN
+FETCH GROUP NEXT ROW;  -- In a package function
+RETURN 0;
+END;
+END;
+$$
+ERROR HY000: Aggregate specific instruction (FETCH GROUP NEXT ROW) used in a wrong context
+CREATE PACKAGE BODY pkg1 AS
+PROCEDURE p1 AS
+BEGIN
+NULL;
+END;
+FUNCTION f1 RETURN INT AS
+BEGIN  
+RETURN 0;
+END;
+BEGIN
+FETCH GROUP NEXT ROW;  -- In a package executable section
+END;
+$$
+ERROR HY000: Aggregate specific instruction (FETCH GROUP NEXT ROW) used in a wrong context
+DROP PACKAGE pkg1;
diff --git a/mysql-test/suite/compat/oracle/t/custom_aggregate_functions.test b/mysql-test/suite/compat/oracle/t/custom_aggregate_functions.test
index 2d42722a1d9..0affc4efa29 100644
--- a/mysql-test/suite/compat/oracle/t/custom_aggregate_functions.test
+++ b/mysql-test/suite/compat/oracle/t/custom_aggregate_functions.test
@@ -64,3 +64,107 @@ DROP FUNCTION IF EXISTS aggregate_count;
 
 
 DROP TABLE marks;
+
+
+--echo #
+--echo # MDEV-18813 PROCEDURE and anonymous blocks silently ignore FETCH GROUP NEXT ROW
+--echo #
+
+
+DELIMITER $$;
+--error ER_NOT_AGGREGATE_FUNCTION
+CREATE PROCEDURE p1 AS
+BEGIN
+  FETCH GROUP NEXT ROW;
+END;
+$$
+DELIMITER ;$$
+
+
+DELIMITER $$;
+--error ER_NOT_AGGREGATE_FUNCTION
+BEGIN NOT ATOMIC
+  FETCH GROUP NEXT ROW;
+END;
+$$
+DELIMITER ;$$
+
+
+DELIMITER $$;
+--error ER_NOT_AGGREGATE_FUNCTION
+CREATE DEFINER=root@localhost  FUNCTION f1 RETURN INT AS
+BEGIN
+  FETCH GROUP NEXT ROW;
+  RETURN 0;
+END;
+$$
+DELIMITER ;$$
+
+
+CREATE TABLE t1 (a INT);
+--error ER_NOT_AGGREGATE_FUNCTION
+CREATE TRIGGER tr1
+  AFTER INSERT ON t1 FOR EACH ROW 
+   FETCH GROUP NEXT ROW;
+DROP TABLE t1;
+
+
+--error ER_NOT_AGGREGATE_FUNCTION
+CREATE EVENT ev1
+  ON SCHEDULE EVERY 1 HOUR
+  STARTS CURRENT_TIMESTAMP + INTERVAL 1 MONTH
+  ENDS CURRENT_TIMESTAMP + INTERVAL 1 MONTH + INTERVAL 1 WEEK
+DO FETCH GROUP NEXT ROW;
+
+
+DELIMITER $$;
+CREATE PACKAGE pkg1 AS
+  PROCEDURE p1;
+  FUNCTION f1 RETURN INT;
+END;
+$$
+
+--error ER_NOT_AGGREGATE_FUNCTION
+CREATE PACKAGE BODY pkg1 AS
+  PROCEDURE p1 AS
+  BEGIN
+    FETCH GROUP NEXT ROW;  -- In a package procedure
+  END;
+  FUNCTION f1 RETURN INT AS
+  BEGIN
+    RETURN 0;
+  END;
+END;
+$$
+
+--error ER_NOT_AGGREGATE_FUNCTION
+CREATE PACKAGE BODY pkg1 AS
+  PROCEDURE p1 AS
+  BEGIN
+    NULL;
+  END;
+  FUNCTION f1 RETURN INT AS
+  BEGIN
+    FETCH GROUP NEXT ROW;  -- In a package function
+    RETURN 0;
+  END;
+END;
+$$
+
+--error ER_NOT_AGGREGATE_FUNCTION
+CREATE PACKAGE BODY pkg1 AS
+  PROCEDURE p1 AS
+  BEGIN
+    NULL;
+  END;
+  FUNCTION f1 RETURN INT AS
+  BEGIN  
+    RETURN 0;
+  END;
+BEGIN
+  FETCH GROUP NEXT ROW;  -- In a package executable section
+END;
+$$
+
+DELIMITER ;$$
+DROP PACKAGE pkg1;
diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt
index 9f50aea1e22..cc2ac272458 100644
--- a/sql/share/errmsg-utf8.txt
+++ b/sql/share/errmsg-utf8.txt
@@ -7811,7 +7811,7 @@ ER_ARGUMENT_OUT_OF_RANGE
 ER_WRONG_TYPE_OF_ARGUMENT
         eng "%s function only accepts arguments that can be converted to numerical types"
 ER_NOT_AGGREGATE_FUNCTION
-        eng "Non-aggregate function contains aggregate specific instructions: (FETCH GROUP NEXT ROW)"
+        eng "Aggregate specific instruction (FETCH GROUP NEXT ROW) used in a wrong context"
 ER_INVALID_AGGREGATE_FUNCTION
         eng "Aggregate specific instruction(FETCH GROUP NEXT ROW) missing from the aggregate function"
 ER_INVALID_VALUE_TO_LIMIT
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index 8ea0b19c378..f2d3a01b953 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -489,7 +489,8 @@ sp_head::operator delete(void *ptr, size_t size) throw()
 }
 
 
-sp_head::sp_head(sp_package *parent, const Sp_handler *sph)
+sp_head::sp_head(sp_package *parent, const Sp_handler *sph,
+                 enum_sp_aggregate_type agg_type)
   :Query_arena(&main_mem_root, STMT_INITIALIZED_FOR_SP),
    Database_qualified_name(&null_clex_str, &null_clex_str),
    m_parent(parent),
@@ -522,6 +523,7 @@ sp_head::sp_head(sp_package *parent, const Sp_handler *sph)
    m_pcont(new (&main_mem_root) sp_pcontext()),
    m_cont_level(0)
 {
+  set_chistics_agg_type(agg_type);
   m_first_instance= this;
   m_first_free_instance= this;
   m_last_cached_sp= this;
@@ -547,7 +549,7 @@ sp_head::sp_head(sp_package *parent, const Sp_handler *sph)
 sp_package::sp_package(LEX *top_level_lex,
                        const sp_name *name,
                        const Sp_handler *sph)
- :sp_head(NULL, sph),
+ :sp_head(NULL, sph, DEFAULT_AGGREGATE),
   m_current_routine(NULL),
   m_top_level_lex(top_level_lex),
   m_rcontext(NULL),
@@ -2681,6 +2683,17 @@ sp_head::set_chistics(const st_sp_chistics &chistics)
                                          m_chistics.comment.length);
 }
 
+
+void
+sp_head::set_c_chistics(const st_sp_chistics &chistics)
+{
+  // Set all chistics but preserve agg_type.
+  enum_sp_aggregate_type save_agg_type= agg_type();
+  set_chistics(chistics);
+  set_chistics_agg_type(save_agg_type);
+}
+
+
 void
 sp_head::set_info(longlong created, longlong modified,
                   const st_sp_chistics &chistics, sql_mode_t sql_mode)
@@ -5134,6 +5147,36 @@ bool sp_head::spvar_fill_table_rowtype_reference(THD *thd,
 }
 
 
+bool sp_head::check_group_aggregate_instructions_forbid() const
+{
+  if (unlikely(m_flags & sp_head::HAS_AGGREGATE_INSTR))
+  {
+    my_error(ER_NOT_AGGREGATE_FUNCTION, MYF(0));
+    return true;
+  }
+  return false;
+}
+
+
+bool sp_head::check_group_aggregate_instructions_require() const
+{
+  if (unlikely(!(m_flags & HAS_AGGREGATE_INSTR)))
+  {
+    my_error(ER_INVALID_AGGREGATE_FUNCTION, MYF(0));
+    return true;
+  }
+  return false;
+}
+
+
+bool sp_head::check_group_aggregate_instructions_function() const
+{
+  return agg_type() == GROUP_AGGREGATE ?
+         check_group_aggregate_instructions_require() :
+         check_group_aggregate_instructions_forbid();
+}
+
+
 /*
   In Oracle mode stored routines have an optional name
   at the end of a declaration:
diff --git a/sql/sp_head.h b/sql/sp_head.h
index cec9347d6f1..3365bf4883f 100644
--- a/sql/sp_head.h
+++ b/sql/sp_head.h
@@ -183,6 +183,11 @@ private:
     set_chistics() makes sure this.
   */
   Sp_chistics m_chistics;
+  void set_chistics(const st_sp_chistics &chistics);
+  inline void set_chistics_agg_type(enum enum_sp_aggregate_type type)
+  {
+    m_chistics.agg_type= type;
+  }
 public:
   sql_mode_t m_sql_mode;		///< For SHOW CREATE and execution
   bool       m_explicit_name;                   /**< Prepend the db name? */
@@ -319,7 +324,8 @@ public:
   static void
   operator delete(void *ptr, size_t size) throw ();
 
-  sp_head(sp_package *parent, const Sp_handler *handler);
+  sp_head(sp_package *parent, const Sp_handler *handler,
+          enum_sp_aggregate_type);
 
   /// Initialize after we have reset mem_root
   void
@@ -413,6 +419,9 @@ public:
                                             Item *val, LEX *lex);
   bool check_package_routine_end_name(const LEX_CSTRING &end_name) const;
   bool check_standalone_routine_end_name(const sp_name *end_name) const;
+  bool check_group_aggregate_instructions_function() const;
+  bool check_group_aggregate_instructions_forbid() const;
+  bool check_group_aggregate_instructions_require() const;
 private:
   /**
     Generate a code to set a single cursor parameter variable.
@@ -730,11 +739,7 @@ public:
                                           const LEX_CSTRING &db,
                                           const LEX_CSTRING &table);
 
-  void set_chistics(const st_sp_chistics &chistics);
-  inline void set_chistics_agg_type(enum enum_sp_aggregate_type type)
-  {
-    m_chistics.agg_type= type;
-  }
+  void set_c_chistics(const st_sp_chistics &chistics);
   void set_info(longlong created, longlong modified,
 		const st_sp_chistics &chistics, sql_mode_t sql_mode);
 
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index aaa34d29c70..c204f96f303 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -35,6 +35,7 @@
 #include "sql_admin.h"                         // Sql_cmd_analyze/Check..._table
 #include "sql_partition.h"
 #include "sql_partition_admin.h"               // Sql_cmd_alter_table_*_part
+#include "event_parse_data.h"
 
 void LEX::parse_error(uint err_number)
 {
@@ -6469,13 +6470,14 @@ sp_name *LEX::make_sp_name(THD *thd, const LEX_CSTRING *name1,
 
 
 sp_head *LEX::make_sp_head(THD *thd, const sp_name *name,
-                           const Sp_handler *sph)
+                           const Sp_handler *sph,
+                           enum_sp_aggregate_type agg_type)
 {
   sp_package *package= get_sp_package();
   sp_head *sp;
 
   /* Order is important here: new - reset - init */
-  if (likely((sp= new sp_head(package, sph))))
+  if (likely((sp= new sp_head(package, sph, agg_type))))
   {
     sp->reset_thd_mem_root(thd);
     sp->init(this);
@@ -6498,7 +6500,8 @@ sp_head *LEX::make_sp_head(THD *thd, const sp_name *name,
 
 
 sp_head *LEX::make_sp_head_no_recursive(THD *thd, const sp_name *name,
-                                        const Sp_handler *sph)
+                                        const Sp_handler *sph,
+                                        enum_sp_aggregate_type agg_type)
 {
   sp_package *package= thd->lex->get_sp_package();
   /*
@@ -6516,13 +6519,13 @@ sp_head *LEX::make_sp_head_no_recursive(THD *thd, const sp_name *name,
       (package &&
        (sph == &sp_handler_package_procedure ||
         sph == &sp_handler_package_function)))
-    return make_sp_head(thd, name, sph);
+    return make_sp_head(thd, name, sph, agg_type);
   my_error(ER_SP_NO_RECURSIVE_CREATE, MYF(0), sph->type_str());
   return NULL;
 }
 
 
-bool LEX::sp_body_finalize_procedure(THD *thd)
+bool LEX::sp_body_finalize_routine(THD *thd)
 {
   if (sphead->check_unresolved_goto())
     return true;
@@ -6532,6 +6535,13 @@ bool LEX::sp_body_finalize_procedure(THD *thd)
 }
 
 
+bool LEX::sp_body_finalize_procedure(THD *thd)
+{
+  return sphead->check_group_aggregate_instructions_forbid() ||
+         sp_body_finalize_routine(thd);
+}
+
+
 bool LEX::sp_body_finalize_procedure_standalone(THD *thd,
                                                 const sp_name *end_name)
 {
@@ -6542,25 +6552,41 @@ bool LEX::sp_body_finalize_procedure_standalone(THD *thd,
 
 bool LEX::sp_body_finalize_function(THD *thd)
 {
-  if (sphead->is_not_allowed_in_function("function"))
+  if (sphead->is_not_allowed_in_function("function") ||
+      sphead->check_group_aggregate_instructions_function())
     return true;
   if (!(sphead->m_flags & sp_head::HAS_RETURN))
   {
     my_error(ER_SP_NORETURN, MYF(0), ErrConvDQName(sphead).ptr());
     return true;
   }
-  if (sp_body_finalize_procedure(thd))
+  if (sp_body_finalize_routine(thd))
     return true;
   (void) is_native_function_with_warn(thd, &sphead->m_name);
   return false;
 }
 
 
-bool LEX::sp_body_finalize_function_standalone(THD *thd,
-                                               const sp_name *end_name)
+bool LEX::sp_body_finalize_trigger(THD *thd)
 {
-  return sp_body_finalize_function(thd) ||
-         sphead->check_standalone_routine_end_name(end_name);
+  return sphead->is_not_allowed_in_function("trigger") ||
+         sp_body_finalize_procedure(thd);
+}
+
+
+bool LEX::sp_body_finalize_event(THD *thd)
+{
+  event_parse_data->body_changed= true;
+  return sp_body_finalize_procedure(thd);
+}
+
+
+bool LEX::stmt_create_stored_function_finalize_standalone(const sp_name *end_name)
+{
+  if (sphead->check_standalone_routine_end_name(end_name))
+    return true;
+  stmt_create_routine_finalize();
+  return false;
 }
 
 
@@ -6855,7 +6881,7 @@ bool LEX::maybe_start_compound_statement(THD *thd)
 {
   if (!sphead)
   {
-    if (!make_sp_head(thd, NULL, &sp_handler_procedure))
+    if (!make_sp_head(thd, NULL, &sp_handler_procedure, DEFAULT_AGGREGATE))
       return true;
     sphead->set_suid(SP_IS_NOT_SUID);
     sphead->set_body_start(thd, thd->m_parser_state->m_lip.get_cpp_ptr());
@@ -8376,6 +8402,7 @@ bool LEX::create_package_finalize(THD *thd,
              exp ? ErrConvDQName(name).ptr() : name->m_name.str);
     return true;
   }
+  // TODO: reuse code in LEX::create_package_finalize and sp_head::set_stmt_end
   sphead->m_body.length= body_end - body_start;
   if (unlikely(!(sphead->m_body.str= thd->strmake(body_start,
                                                   sphead->m_body.length))))
@@ -8390,7 +8417,8 @@ bool LEX::create_package_finalize(THD *thd,
   sphead->restore_thd_mem_root(thd);
   sp_package *pkg= sphead->get_package();
   DBUG_ASSERT(pkg);
-  return pkg->validate_after_parser(thd);
+  return sphead->check_group_aggregate_instructions_forbid() ||
+         pkg->validate_after_parser(thd);
 }
 
 
@@ -10326,3 +10354,40 @@ bool LEX::stmt_purge_before(Item *item)
   value_list.push_front(item, thd->mem_root);
   return check_main_unit_semantics();
 }
+
+
+bool LEX::stmt_create_udf_function(const DDL_options_st &options,
+                                   enum_sp_aggregate_type agg_type,
+                                   const Lex_ident_sys_st &name,
+                                   Item_result return_type,
+                                   const LEX_CSTRING &soname)
+{
+  if (stmt_create_function_start(options))
+    return true;
+
+   if (unlikely(is_native_function(thd, &name)))
+   {
+     my_error(ER_NATIVE_FCT_NAME_COLLISION, MYF(0), name.str);
+     return true;
+   }
+   sql_command= SQLCOM_CREATE_FUNCTION;
+   udf.name= name;
+   udf.returns= return_type;
+   udf.dl= soname.str;
+   udf.type= agg_type == GROUP_AGGREGATE ? UDFTYPE_AGGREGATE :
+                                           UDFTYPE_FUNCTION;
+   stmt_create_routine_finalize();
+   return false;
+}
+
+
+bool LEX::stmt_create_stored_function_start(const DDL_options_st &options,
+                                            enum_sp_aggregate_type agg_type,
+                                            const sp_name *spname)
+{
+  if (stmt_create_function_start(options) ||
+      unlikely(!make_sp_head_no_recursive(thd, spname,
+                                          &sp_handler_function, agg_type)))
+    return true;
+  return false;
+}
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 70272e83e08..b78a010d4b7 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -241,6 +241,14 @@ enum enum_sp_suid_behaviour
 };
 
 
+enum enum_sp_aggregate_type
+{
+  DEFAULT_AGGREGATE= 0,
+  NOT_AGGREGATE,
+  GROUP_AGGREGATE
+};
+
+
 /* These may not be declared yet */
 class Table_ident;
 class sql_exchange;
@@ -371,13 +379,6 @@ enum enum_sp_data_access
   SP_MODIFIES_SQL_DATA
 };
 
-enum enum_sp_aggregate_type
-{
-  DEFAULT_AGGREGATE= 0,
-  NOT_AGGREGATE,
-  GROUP_AGGREGATE
-};
-
 const LEX_CSTRING sp_data_access_name[]=
 {
   { STRING_WITH_LEN("") },
@@ -3734,12 +3735,16 @@ public:
   sp_name *make_sp_name(THD *thd, const LEX_CSTRING *name1,
                                   const LEX_CSTRING *name2);
   sp_name *make_sp_name_package_routine(THD *thd, const LEX_CSTRING *name);
-  sp_head *make_sp_head(THD *thd, const sp_name *name, const Sp_handler *sph);
+  sp_head *make_sp_head(THD *thd, const sp_name *name, const Sp_handler *sph,
+                        enum_sp_aggregate_type agg_type);
   sp_head *make_sp_head_no_recursive(THD *thd, const sp_name *name,
-                                     const Sp_handler *sph);
+                                     const Sp_handler *sph,
+                                     enum_sp_aggregate_type agg_type);
+  bool sp_body_finalize_routine(THD *);
+  bool sp_body_finalize_trigger(THD *);
+  bool sp_body_finalize_event(THD *);
   bool sp_body_finalize_function(THD *);
   bool sp_body_finalize_procedure(THD *);
-  bool sp_body_finalize_function_standalone(THD *, const sp_name *end_name);
   bool sp_body_finalize_procedure_standalone(THD *, const sp_name *end_name);
   sp_package *create_package_start(THD *thd,
                                    enum_sql_command command,
@@ -4502,6 +4507,17 @@ public:
   {
     pop_select(); // main select
   }
+
+  bool stmt_create_stored_function_start(const DDL_options_st &options,
+                                         enum_sp_aggregate_type,
+                                         const sp_name *name);
+  bool stmt_create_stored_function_finalize_standalone(const sp_name *end_name);
+
+  bool stmt_create_udf_function(const DDL_options_st &options,
+                                enum_sp_aggregate_type agg_type,
+                                const Lex_ident_sys_st &name,
+                                Item_result return_type,
+                                const LEX_CSTRING &soname);
 };
 
 
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index d17e92b6c22..970f5d50334 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -758,6 +758,7 @@ Virtual_column_info *add_virtual_expression(THD *thd, Item *expr)
 
   /* enums */
   enum enum_sp_suid_behaviour sp_suid;
+  enum enum_sp_aggregate_type sp_aggregate_type;
   enum enum_view_suid view_suid;
   enum Condition_information_item::Name cond_info_item_name;
   enum enum_diag_condition_item_name diag_condition_item_name;
@@ -2061,10 +2062,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize);
         sp_c_chistics sp_a_chistics sp_chistic sp_c_chistic xa
         opt_field_or_var_spec fields_or_vars opt_load_data_set_spec
         view_list_opt view_list view_select
-        trigger_tail sp_tail sf_tail event_tail
-        udf_tail
-        create_function_tail
-        create_aggregate_function_tail
+        trigger_tail sp_tail event_tail
         install uninstall partition_entry binlog_base64_event
         normal_key_options normal_key_opts all_key_opt 
         spatial_key_options fulltext_key_options normal_key_opt 
@@ -2107,6 +2105,7 @@ END_OF_INPUT
 
 %type <plsql_cursor_attr> plsql_cursor_attr
 %type <sp_suid> sp_suid
+%type <sp_aggregate_type> opt_aggregate
 
 %type <num> sp_decl_idents sp_decl_idents_init_vars
 %type <num> sp_handler_type sp_hcond_list
@@ -2860,42 +2859,37 @@ create:
           {
             Lex->pop_select(); //main select
           }
-        | create_or_replace definer FUNCTION_SYM opt_if_not_exists
+        | create_or_replace definer opt_aggregate FUNCTION_SYM opt_if_not_exists
+          sp_name '('
           {
-            if (Lex->stmt_create_function_start($1 | $4))
+            if (Lex->stmt_create_stored_function_start($1 | $5, $3, $6))
               MYSQL_YYABORT;
           }
-          sf_tail
-          {
-            Lex->stmt_create_routine_finalize();
-          }
-        | create_or_replace definer AGGREGATE_SYM FUNCTION_SYM opt_if_not_exists
-          {
-            if (Lex->stmt_create_function_start($1 | $5))
-              MYSQL_YYABORT;
-          }
-          sf_tail_aggregate
+          sp_fdparam_list ')'
+          sf_return_type
+          sf_c_chistics_and_body
           {
             Lex->stmt_create_routine_finalize();
           }
-        | create_or_replace no_definer FUNCTION_SYM opt_if_not_exists
+        | create_or_replace no_definer opt_aggregate FUNCTION_SYM opt_if_not_exists
+          sp_name '('
           {
-            if (Lex->stmt_create_function_start($1 | $4))
+            if (Lex->stmt_create_stored_function_start($1 | $5, $3, $6))
               MYSQL_YYABORT;
           }
-          create_function_tail
+          sp_fdparam_list ')'
+          sf_return_type
+          sf_c_chistics_and_body
           {
             Lex->stmt_create_routine_finalize();
           }
-        | create_or_replace no_definer AGGREGATE_SYM FUNCTION_SYM opt_if_not_exists
+        | create_or_replace no_definer opt_aggregate FUNCTION_SYM opt_if_not_exists
+          ident RETURNS_SYM udf_type SONAME_SYM TEXT_STRING_sys
           {
-            if (Lex->stmt_create_function_start($1 | $5))
+            if (Lex->stmt_create_udf_function($1 | $5, $3, $6,
+                                              (Item_result) $8, $10))
               MYSQL_YYABORT;
           }
-          create_aggregate_function_tail
-          {
-            Lex->stmt_create_routine_finalize();
-          }
         | create_or_replace USER_SYM opt_if_not_exists clear_privileges
           grant_list opt_require_clause opt_resource_options opt_account_locking opt_password_expiration
           {
@@ -2923,38 +2917,6 @@ create:
           { }
         ;
 
-sf_tail_not_aggregate:
-        sf_tail
-        {
-          if (unlikely(Lex->sphead->m_flags & sp_head::HAS_AGGREGATE_INSTR))
-          {
-            my_yyabort_error((ER_NOT_AGGREGATE_FUNCTION, MYF(0)));
-          }
-          Lex->sphead->set_chistics_agg_type(NOT_AGGREGATE);
-        }
-        ;
-
-sf_tail_aggregate:
-        sf_tail
-        {
-          if (unlikely(!(Lex->sphead->m_flags & sp_head::HAS_AGGREGATE_INSTR)))
-          {
-            my_yyabort_error((ER_INVALID_AGGREGATE_FUNCTION, MYF(0)));
-          }
-          Lex->sphead->set_chistics_agg_type(GROUP_AGGREGATE);
-        }
-        ;
-
-create_function_tail:
-          sf_tail_not_aggregate { }
-        | udf_tail { Lex->udf.type= UDFTYPE_FUNCTION; }
-        ;
-
-create_aggregate_function_tail:
-          sf_tail_aggregate { }
-        | udf_tail { Lex->udf.type= UDFTYPE_AGGREGATE; }
-        ;
-
 opt_sequence:
          /* empty */ { }
         | sequence_defs
@@ -3275,20 +3237,17 @@ ev_sql_stmt:
               
             if (unlikely(!lex->make_sp_head(thd,
                                             lex->event_parse_data->identifier,
-                                            &sp_handler_procedure)))
+                                            &sp_handler_procedure,
+                                            DEFAULT_AGGREGATE)))
               MYSQL_YYABORT;
 
             lex->sphead->set_body_start(thd, lip->get_cpp_ptr());
           }
           sp_proc_stmt
           {
-            LEX *lex= thd->lex;
-
             /* return back to the original memory root ASAP */
-            lex->sphead->set_stmt_end(thd);
-            lex->sphead->restore_thd_mem_root(thd);
-
-            lex->event_parse_data->body_changed= TRUE;
+            if (Lex->sp_body_finalize_event(thd))
+              MYSQL_YYABORT;
           }
         ;
 
@@ -3305,6 +3264,11 @@ clear_privileges:
          }
         ;
 
+opt_aggregate:
+          /* Empty */   { $$= NOT_AGGREGATE; }
+        | AGGREGATE_SYM { $$= GROUP_AGGREGATE; }
+        ;
+
 sp_name:
           ident '.' ident
           {
@@ -3391,7 +3355,18 @@ sp_cparams:
 /* Stored FUNCTION parameter declaration list */
 sp_fdparam_list:
           /* Empty */
-        | sp_fdparams
+          {
+            Lex->sphead->m_param_begin= YYLIP->get_cpp_tok_start();
+            Lex->sphead->m_param_end= Lex->sphead->m_param_begin;
+          }
+        |
+          {
+            Lex->sphead->m_param_begin= YYLIP->get_cpp_tok_start();
+          }
+          sp_fdparams
+          {
+            Lex->sphead->m_param_end= YYLIP->get_cpp_tok_start();
+          }
         ;
 
 sp_fdparams:
@@ -3465,18 +3440,6 @@ sp_opt_inout:
         | INOUT_SYM   { $$= sp_variable::MODE_INOUT; }
         ;
 
-sp_parenthesized_fdparam_list:
-          '('
-          {
-            Lex->sphead->m_param_begin= YYLIP->get_cpp_tok_start() + 1;
-          }
-          sp_fdparam_list
-          ')'
-          {
-            Lex->sphead->m_param_end= YYLIP->get_cpp_tok_start();
-          }
-        ;
-
 sp_parenthesized_pdparam_list:
           '('
           {
@@ -17415,8 +17378,8 @@ compound_statement:
           sp_proc_stmt_compound_ok
           {
             Lex->sql_command= SQLCOM_COMPOUND;
-            Lex->sphead->set_stmt_end(thd);
-            Lex->sphead->restore_thd_mem_root(thd);
+            if (Lex->sp_body_finalize_procedure(thd))
+              MYSQL_YYABORT;
           }
         ;
 
@@ -17702,7 +17665,8 @@ trigger_tail:
             (*static_cast<st_trg_execution_order*>(&lex->trg_chistics))= ($17);
             lex->trg_chistics.ordering_clause_end= lip->get_cpp_ptr();
 
-            if (unlikely(!lex->make_sp_head(thd, $4, &sp_handler_trigger)))
+            if (unlikely(!lex->make_sp_head(thd, $4, &sp_handler_trigger,
+                                            DEFAULT_AGGREGATE)))
               MYSQL_YYABORT;
 
             lex->sphead->set_body_start(thd, lip->get_cpp_tok_start());
@@ -17710,13 +17674,9 @@ trigger_tail:
           sp_proc_stmt /* $19 */
           { /* $20 */
             LEX *lex= Lex;
-            sp_head *sp= lex->sphead;
 
             lex->sql_command= SQLCOM_CREATE_TRIGGER;
-            sp->set_stmt_end(thd);
-            sp->restore_thd_mem_root(thd);
-
-            if (unlikely(sp->is_not_allowed_in_function("trigger")))
+            if (lex->sp_body_finalize_trigger(thd))
               MYSQL_YYABORT;
 
             /*
@@ -17738,19 +17698,6 @@ trigger_tail:
 
 **************************************************************************/
 
-udf_tail:
-          ident RETURNS_SYM udf_type SONAME_SYM TEXT_STRING_sys
-          {
-            LEX *lex= thd->lex;
-            if (unlikely(is_native_function(thd, & $1)))
-              my_yyabort_error((ER_NATIVE_FCT_NAME_COLLISION, MYF(0), $1.str));
-            lex->sql_command= SQLCOM_CREATE_FUNCTION;
-            lex->udf.name= $1;
-            lex->udf.returns= (Item_result) $3;
-            lex->udf.dl= $5.str;
-          }
-        ;
-
 
 sf_return_type:
           RETURNS_SYM
@@ -17768,22 +17715,12 @@ sf_return_type:
           }
         ;
 
-sf_tail:
-          sp_name
-          {
-            if (unlikely(!Lex->make_sp_head_no_recursive(thd, $1,
-                                                         &sp_handler_function)))
-              MYSQL_YYABORT;
-          }
-          sp_parenthesized_fdparam_list
-          sf_return_type
+sf_c_chistics_and_body:
           sp_c_chistics
           {
             LEX *lex= thd->lex;
-            Lex_input_stream *lip= YYLIP;
-
-            lex->sphead->set_chistics(lex->sp_chistics);
-            lex->sphead->set_body_start(thd, lip->get_cpp_tok_start());
+            lex->sphead->set_c_chistics(lex->sp_chistics);
+            lex->sphead->set_body_start(thd, YYLIP->get_cpp_tok_start());
           }
           sp_proc_stmt_in_returns_clause
           {
@@ -17792,17 +17729,19 @@ sf_tail:
           }
         ;
 
+
 sp_tail:
           sp_name
           {
             if (unlikely(!Lex->make_sp_head_no_recursive(thd, $1,
-                                                         &sp_handler_procedure)))
+                                                         &sp_handler_procedure,
+                                                         DEFAULT_AGGREGATE)))
               MYSQL_YYABORT;
           }
           sp_parenthesized_pdparam_list
           sp_c_chistics
           {
-            Lex->sphead->set_chistics(Lex->sp_chistics);
+            Lex->sphead->set_c_chistics(Lex->sp_chistics);
             Lex->sphead->set_body_start(thd, YYLIP->get_cpp_tok_start());
           }
           sp_proc_stmt
diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy
index 0f7bbdcff5e..e709e7e3afa 100644
--- a/sql/sql_yacc_ora.yy
+++ b/sql/sql_yacc_ora.yy
@@ -254,6 +254,7 @@ void ORAerror(THD *thd, const char *s)
 
   /* enums */
   enum enum_sp_suid_behaviour sp_suid;
+  enum enum_sp_aggregate_type sp_aggregate_type;
   enum enum_view_suid view_suid;
   enum Condition_information_item::Name cond_info_item_name;
   enum enum_diag_condition_item_name diag_condition_item_name;
@@ -1565,9 +1566,6 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize);
         opt_field_or_var_spec fields_or_vars opt_load_data_set_spec
         view_list_opt view_list view_select
         trigger_tail event_tail
-        udf_tail
-        create_function_tail_standalone
-        create_aggregate_function_tail_standalone
         install uninstall partition_entry binlog_base64_event
         normal_key_options normal_key_opts all_key_opt 
         spatial_key_options fulltext_key_options normal_key_opt 
@@ -1587,7 +1585,6 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize);
         opt_delete_gtid_domain
         asrow_attribute
         set_assign
-        sf_tail_standalone
         sp_tail_standalone
         opt_constraint_no_id
 END_OF_INPUT
@@ -1613,6 +1610,7 @@ END_OF_INPUT
 
 %type <plsql_cursor_attr> plsql_cursor_attr
 %type <sp_suid> sp_suid
+%type <sp_aggregate_type> opt_aggregate
 
 %type <num> sp_decl_idents sp_decl_idents_init_vars
 %type <num> sp_handler_type sp_hcond_list
@@ -2381,41 +2379,66 @@ create:
           {
             Lex->pop_select(); //main select
           }
-        | create_or_replace definer FUNCTION_SYM opt_if_not_exists
+        | create_or_replace definer opt_aggregate FUNCTION_SYM opt_if_not_exists
+          sp_name RETURN_ORACLE_SYM
           {
-            if (Lex->stmt_create_function_start($1 | $4))
+            if (Lex->stmt_create_stored_function_start($1 | $5, $3, $6))
               MYSQL_YYABORT;
           }
-          sf_tail_standalone
+          sf_return_type
+          sf_c_chistics_and_body_standalone
+          opt_sp_name
           {
-            Lex->stmt_create_routine_finalize();
+            if (Lex->stmt_create_stored_function_finalize_standalone($11))
+              MYSQL_YYABORT;
           }
-        | create_or_replace definer AGGREGATE_SYM FUNCTION_SYM opt_if_not_exists
+        | create_or_replace definer opt_aggregate FUNCTION_SYM opt_if_not_exists
+          sp_name '('
           {
-            if (Lex->stmt_create_function_start($1 | $5))
+            if (Lex->stmt_create_stored_function_start($1 | $5, $3, $6))
               MYSQL_YYABORT;
           }
-          sf_tail_aggregate_standalone
+          sp_fdparam_list ')'
+          RETURN_ORACLE_SYM sf_return_type
+          sf_c_chistics_and_body_standalone
+          opt_sp_name
           {
-            Lex->stmt_create_routine_finalize();
+            if (Lex->stmt_create_stored_function_finalize_standalone($14))
+              MYSQL_YYABORT;
           }
-        | create_or_replace no_definer FUNCTION_SYM opt_if_not_exists
+        | create_or_replace no_definer opt_aggregate FUNCTION_SYM opt_if_not_exists
+          sp_name RETURN_ORACLE_SYM
           {
-            if (Lex->stmt_create_function_start($1 | $4))
+            if (Lex->stmt_create_stored_function_start($1 | $5, $3, $6))
               MYSQL_YYABORT;
           }
-          create_function_tail_standalone
+          sf_return_type
+          sf_c_chistics_and_body_standalone
+          opt_sp_name
           {
-            Lex->stmt_create_routine_finalize();
+            if (Lex->stmt_create_stored_function_finalize_standalone($11))
+              MYSQL_YYABORT;
           }
-        | create_or_replace no_definer AGGREGATE_SYM FUNCTION_SYM opt_if_not_exists
+        | create_or_replace no_definer opt_aggregate FUNCTION_SYM opt_if_not_exists
+          sp_name '('
           {
-            if (Lex->stmt_create_function_start($1 | $5))
+            if (Lex->stmt_create_stored_function_start($1 | $5, $3, $6))
               MYSQL_YYABORT;
           }
-          create_aggregate_function_tail_standalone
+          sp_fdparam_list ')'
+          RETURN_ORACLE_SYM sf_return_type
+          sf_c_chistics_and_body_standalone
+          opt_sp_name
           {
-            Lex->stmt_create_routine_finalize();
+            if (Lex->stmt_create_stored_function_finalize_standalone($14))
+              MYSQL_YYABORT;
+          }
+        | create_or_replace no_definer opt_aggregate FUNCTION_SYM opt_if_not_exists
+          ident RETURNS_SYM udf_type SONAME_SYM TEXT_STRING_sys
+          {
+            if (Lex->stmt_create_udf_function($1 | $5, $3, $6,
+                                              (Item_result) $8, $10))
+              MYSQL_YYABORT;
           }
         | create_or_replace USER_SYM opt_if_not_exists clear_privileges
           grant_list opt_require_clause opt_resource_options opt_account_locking opt_password_expiration
@@ -2454,7 +2477,7 @@ create:
                                                 &sp_handler_package_spec,
                                                 $5, $1 | $4))))
               MYSQL_YYABORT;
-            pkg->set_chistics(Lex->sp_chistics);
+            pkg->set_c_chistics(Lex->sp_chistics);
           }
           opt_package_specification_element_list END
           remember_end_opt opt_sp_name
@@ -2474,7 +2497,7 @@ create:
                                                 &sp_handler_package_body,
                                                 $6, $1 | $5))))
               MYSQL_YYABORT;
-            pkg->set_chistics(Lex->sp_chistics);
+            pkg->set_c_chistics(Lex->sp_chistics);
             Lex->sp_block_init(thd);
           }
           package_implementation_declare_section
@@ -2495,39 +2518,6 @@ create:
           }
         ;
 
-sf_tail_not_aggregate_standalone:
-        sf_tail_standalone
-        {
-          if (unlikely(Lex->sphead->m_flags & sp_head::HAS_AGGREGATE_INSTR))
-          {
-            my_yyabort_error((ER_NOT_AGGREGATE_FUNCTION, MYF(0)));
-          }
-          Lex->sphead->set_chistics_agg_type(NOT_AGGREGATE);
-        }
-        ;
-
-sf_tail_aggregate_standalone:
-        sf_tail_standalone
-        {
-          if (unlikely(!(Lex->sphead->m_flags & sp_head::HAS_AGGREGATE_INSTR)))
-          {
-            my_yyabort_error((ER_INVALID_AGGREGATE_FUNCTION, MYF(0)));
-          }
-          Lex->sphead->set_chistics_agg_type(GROUP_AGGREGATE);
-        }
-        ;
-
-create_function_tail_standalone:
-          sf_tail_not_aggregate_standalone { }
-        | udf_tail { Lex->udf.type= UDFTYPE_FUNCTION; }
-        ;
-
-
-create_aggregate_function_tail_standalone:
-          sf_tail_aggregate_standalone { }
-        | udf_tail { Lex->udf.type= UDFTYPE_AGGREGATE; }
-        ;
-
 package_implementation_executable_section:
           END
           {
@@ -2584,13 +2574,14 @@ package_specification_function:
               MYSQL_YYABORT;
             thd->lex= $2;
             if (unlikely(!$2->make_sp_head_no_recursive(thd, spname,
-                                                        &sp_handler_package_function)))
+                                                        &sp_handler_package_function,
+                                                        NOT_AGGREGATE)))
               MYSQL_YYABORT;
             $1->sphead->get_package()->m_current_routine= $2;
             (void) is_native_function_with_warn(thd, &$3);
           }
           opt_sp_parenthesized_fdparam_list
-          sf_return_type
+          RETURN_ORACLE_SYM sf_return_type
           sp_c_chistics
           {
             sp_head *sp= thd->lex->sphead;
@@ -2610,7 +2601,8 @@ package_specification_procedure:
               MYSQL_YYABORT;
             thd->lex= $2;
             if (unlikely(!$2->make_sp_head_no_recursive(thd, spname,
-                                                        &sp_handler_package_procedure)))
+                                                        &sp_handler_package_procedure,
+                                                        DEFAULT_AGGREGATE)))
               MYSQL_YYABORT;
             $1->sphead->get_package()->m_current_routine= $2;
           }
@@ -2660,11 +2652,6 @@ package_implementation_function_body:
           }
           sp_body opt_package_routine_end_name
           {
-            if (unlikely(Lex->sphead->m_flags & sp_head::HAS_AGGREGATE_INSTR))
-            {
-              my_yyabort_error((ER_NOT_AGGREGATE_FUNCTION, MYF(0)));
-            }
-            Lex->sphead->set_chistics_agg_type(NOT_AGGREGATE);
             if (unlikely(thd->lex->sp_body_finalize_function(thd) ||
                          thd->lex->sphead->check_package_routine_end_name($5)))
               MYSQL_YYABORT;
@@ -2684,7 +2671,7 @@ package_implementation_procedure_body:
           sp_body opt_package_routine_end_name
           {
             if (unlikely(thd->lex->sp_body_finalize_procedure(thd) ||
-                        thd->lex->sphead->check_package_routine_end_name($5)))
+                         thd->lex->sphead->check_package_routine_end_name($5)))
               MYSQL_YYABORT;
             thd->lex= $2;
           }
@@ -3043,20 +3030,17 @@ ev_sql_stmt:
               
             if (unlikely(!lex->make_sp_head(thd,
                                             lex->event_parse_data->identifier,
-                                            &sp_handler_procedure)))
+                                            &sp_handler_procedure,
+                                            DEFAULT_AGGREGATE)))
               MYSQL_YYABORT;
 
             lex->sphead->set_body_start(thd, lip->get_cpp_ptr());
           }
           sp_proc_stmt
           {
-            LEX *lex= thd->lex;
-
             /* return back to the original memory root ASAP */
-            lex->sphead->set_stmt_end(thd);
-            lex->sphead->restore_thd_mem_root(thd);
-
-            lex->event_parse_data->body_changed= TRUE;
+            if (Lex->sp_body_finalize_event(thd))
+              MYSQL_YYABORT;
           }
         ;
 
@@ -3073,6 +3057,11 @@ clear_privileges:
          }
         ;
 
+opt_aggregate:
+          /* Empty */   { $$= NOT_AGGREGATE; }
+        | AGGREGATE_SYM { $$= GROUP_AGGREGATE; }
+        ;
+
 sp_name:
           ident '.' ident
           {
@@ -3186,7 +3175,18 @@ sp_cparams:
 /* Stored FUNCTION parameter declaration list */
 sp_fdparam_list:
           /* Empty */
-        | sp_fdparams
+          {
+            Lex->sphead->m_param_begin= YYLIP->get_cpp_tok_start();
+            Lex->sphead->m_param_end= Lex->sphead->m_param_begin;
+          }
+        |
+          {
+            Lex->sphead->m_param_begin= YYLIP->get_cpp_tok_start();
+          }
+          sp_fdparams
+          {
+            Lex->sphead->m_param_end= YYLIP->get_cpp_tok_start();
+          }
         ;
 
 sp_fdparams:
@@ -3293,18 +3293,6 @@ sp_opt_inout:
         | IN_SYM OUT_SYM { $$= sp_variable::MODE_INOUT; }
         ;
 
-sp_parenthesized_fdparam_list:
-          '('
-          {
-            Lex->sphead->m_param_begin= YYLIP->get_cpp_tok_start() + 1;
-          }
-          sp_fdparam_list
-          ')'
-          {
-            Lex->sphead->m_param_end= YYLIP->get_cpp_tok_start();
-          }
-        ;
-
 sp_parenthesized_pdparam_list:
           '('
           {
@@ -3327,7 +3315,7 @@ sp_no_param:
 
 opt_sp_parenthesized_fdparam_list:
           sp_no_param
-        | sp_parenthesized_fdparam_list
+        | '(' sp_fdparam_list ')'
         ;
 
 opt_sp_parenthesized_pdparam_list:
@@ -17620,8 +17608,8 @@ compound_statement:
           sp_proc_stmt_compound_ok
           {
             Lex->sql_command= SQLCOM_COMPOUND;
-            Lex->sphead->set_stmt_end(thd);
-            Lex->sphead->restore_thd_mem_root(thd);
+            if (Lex->sp_body_finalize_procedure(thd))
+              MYSQL_YYABORT;
           }
         ;
 
@@ -17908,7 +17896,8 @@ trigger_tail:
             (*static_cast<st_trg_execution_order*>(&lex->trg_chistics))= ($17);
             lex->trg_chistics.ordering_clause_end= lip->get_cpp_ptr();
 
-            if (unlikely(!lex->make_sp_head(thd, $4, &sp_handler_trigger)))
+            if (unlikely(!lex->make_sp_head(thd, $4, &sp_handler_trigger,
+                                            DEFAULT_AGGREGATE)))
               MYSQL_YYABORT;
 
             lex->sphead->set_body_start(thd, lip->get_cpp_tok_start());
@@ -17916,15 +17905,9 @@ trigger_tail:
           sp_proc_stmt /* $19 */
           { /* $20 */
             LEX *lex= Lex;
-            sp_head *sp= lex->sphead;
-            if (unlikely(sp->check_unresolved_goto()))
-              MYSQL_YYABORT;
 
             lex->sql_command= SQLCOM_CREATE_TRIGGER;
-            sp->set_stmt_end(thd);
-            sp->restore_thd_mem_root(thd);
-
-            if (unlikely(sp->is_not_allowed_in_function("trigger")))
+            if (lex->sp_body_finalize_trigger(thd))
               MYSQL_YYABORT;
 
             /*
@@ -17946,22 +17929,7 @@ trigger_tail:
 
 **************************************************************************/
 
-udf_tail:
-          ident RETURNS_SYM udf_type SONAME_SYM TEXT_STRING_sys
-          {
-            LEX *lex= thd->lex;
-            if (unlikely(is_native_function(thd, & $1)))
-              my_yyabort_error((ER_NATIVE_FCT_NAME_COLLISION, MYF(0), $1.str));
-            lex->sql_command= SQLCOM_CREATE_FUNCTION;
-            lex->udf.name= $1;
-            lex->udf.returns= (Item_result) $3;
-            lex->udf.dl= $5.str;
-          }
-        ;
-
-
 sf_return_type:
-          RETURN_ORACLE_SYM
           {
             LEX *lex= Lex;
             lex->init_last_field(&lex->sphead->m_return_field_def,
@@ -17976,28 +17944,17 @@ sf_return_type:
           }
         ;
 
-sf_tail_standalone:
-          sp_name
-          {
-            if (unlikely(!Lex->make_sp_head_no_recursive(thd, $1,
-                                                         &sp_handler_function)))
-              MYSQL_YYABORT;
-          }
-          opt_sp_parenthesized_fdparam_list
-          sf_return_type
+sf_c_chistics_and_body_standalone:
           sp_c_chistics
           {
             LEX *lex= thd->lex;
-            Lex_input_stream *lip= YYLIP;
-
-            lex->sphead->set_chistics(lex->sp_chistics);
-            lex->sphead->set_body_start(thd, lip->get_cpp_tok_start());
+            lex->sphead->set_c_chistics(lex->sp_chistics);
+            lex->sphead->set_body_start(thd, YYLIP->get_cpp_tok_start());
           }
           sp_tail_is
           sp_body
-          opt_sp_name
           {
-            if (unlikely(Lex->sp_body_finalize_function_standalone(thd, $9)))
+            if (unlikely(Lex->sp_body_finalize_function(thd)))
               MYSQL_YYABORT;
           }
         ;
@@ -18006,13 +17963,14 @@ sp_tail_standalone:
           sp_name
           {
             if (unlikely(!Lex->make_sp_head_no_recursive(thd, $1,
-                                                         &sp_handler_procedure)))
+                                                         &sp_handler_procedure,
+                                                         DEFAULT_AGGREGATE)))
               MYSQL_YYABORT;
           }
           opt_sp_parenthesized_pdparam_list
           sp_c_chistics
           {
-            Lex->sphead->set_chistics(Lex->sp_chistics);
+            Lex->sphead->set_c_chistics(Lex->sp_chistics);
             Lex->sphead->set_body_start(thd, YYLIP->get_cpp_tok_start());
           }
           sp_tail_is