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.

2 files changed, 183 insertions, 1 deletions

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;