Adding upstream version 1:10.11.6.upstream/1%10.11.6

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 1088 insertions, 0 deletions

diff --git a/mysql-test/suite/compat/oracle/t/sp-code.test b/mysql-test/suite/compat/oracle/t/sp-code.test
new file mode 100644
index 00000000..1ffd3b94
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/t/sp-code.test
@@ -0,0 +1,1088 @@
+-- source include/have_debug.inc
+
+SET sql_mode=ORACLE;
+
+--echo #
+--echo # Testing exceptions in the top-level blocks
+--echo #
+
+--echo # No HANDLER declarations, no exceptions
+DELIMITER /;
+CREATE FUNCTION f1 RETURN INT
+AS
+BEGIN
+  RETURN 10;
+END;
+/
+DELIMITER ;/
+SHOW FUNCTION CODE f1;
+SELECT f1();
+DROP FUNCTION f1;
+
+--echo # No HANDLER declarations, no code, no exceptions
+DELIMITER /;
+CREATE PROCEDURE p1 ()
+IS
+BEGIN
+END;
+/
+DELIMITER ;/
+SHOW PROCEDURE CODE p1;
+CALL p1;
+DROP PROCEDURE p1;
+
+
+--echo # No HANDLER declarations, no code, some exceptions
+DELIMITER /;
+CREATE PROCEDURE p1 (v IN OUT INT)
+IS
+BEGIN
+EXCEPTION
+  WHEN 1002 THEN v:=225;
+END;
+/
+DELIMITER ;/
+SHOW PROCEDURE CODE p1;
+set @v= 10;
+CALL p1(@v);
+SELECT @v;
+DROP PROCEDURE p1;
+
+
+--echo # No HANDLER declarations, some code, some exceptions
+DELIMITER /;
+CREATE PROCEDURE p1 (v IN OUT INT)
+IS
+BEGIN
+  v:=224;
+EXCEPTION
+  WHEN 1002 THEN v:=225;
+END;
+/
+DELIMITER ;/
+SHOW PROCEDURE CODE p1;
+set @v= 10;
+CALL p1(@v);
+SELECT @v;
+DROP PROCEDURE p1;
+
+
+--echo # Some HANDLER declarations, no code, no exceptions
+DELIMITER /;
+CREATE PROCEDURE p1 (v IN OUT INT)
+IS
+  EXIT HANDLER FOR 1000
+  BEGIN
+   v:=123;
+  END;
+BEGIN
+END;
+/
+DELIMITER ;/
+SHOW PROCEDURE CODE p1;
+set @v= 10;
+CALL p1(@v);
+SELECT @v;
+DROP PROCEDURE p1;
+
+
+--echo # Some HANDLER declarations, no code, some exceptions
+DELIMITER /;
+CREATE PROCEDURE p1 (v IN OUT INT)
+IS
+  EXIT HANDLER FOR 1000
+  BEGIN
+   v:=123;
+  END;
+BEGIN
+EXCEPTION
+  WHEN 1002 THEN v:=225;
+END;
+/
+DELIMITER ;/
+SHOW PROCEDURE CODE p1;
+set @v= 10;
+CALL p1(@v);
+SELECT @v;
+DROP PROCEDURE p1;
+
+
+--echo # Some HANDLER declarations, some code, no exceptions
+DELIMITER /;
+CREATE PROCEDURE p1 (v IN OUT INT)
+IS
+  EXIT HANDLER FOR 1000
+  BEGIN
+   v:=123;
+  END;
+BEGIN
+  v:=223;
+END;
+/
+DELIMITER ;/
+SHOW PROCEDURE CODE p1;
+set @v= 10;
+CALL p1(@v);
+SELECT @v;
+DROP PROCEDURE p1;
+
+--echo # Some HANDLER declarations, some code, some exceptions
+DELIMITER /;
+CREATE PROCEDURE p1 (v IN OUT VARCHAR2(20))
+IS
+  EXIT HANDLER FOR 1000
+  BEGIN
+    v:=123;
+  END;
+  CONTINUE HANDLER FOR 1001
+  BEGIN
+    SET v=223;
+  END;
+BEGIN
+  v:= 1;
+EXCEPTION
+  WHEN 1002 THEN SET v=225;
+END;
+/
+DELIMITER ;/
+SHOW PROCEDURE CODE p1;
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # Testing EXCEPTIONS in internal blocks
+--echo #
+
+--echo # No HANDLER declarations, no code, no exceptions
+DELIMITER /;
+CREATE PROCEDURE p1 (v IN OUT INT)
+IS
+BEGIN
+  v:=123;
+  BEGIN
+  END;
+END;
+/
+DELIMITER ;/
+SHOW PROCEDURE CODE p1;
+SET @v=10;
+CALL p1(@v);
+SELECT @v;
+DROP PROCEDURE p1;
+
+--echo # No HANDLER declarations, no code, some exceptions
+DELIMITER /;
+CREATE PROCEDURE p1 (v IN OUT INT)
+IS
+BEGIN
+  v:=123;
+  BEGIN
+  EXCEPTION
+    WHEN 20002 THEN v:=335;
+  END;
+END;
+/
+DELIMITER ;/
+SHOW PROCEDURE CODE p1;
+SET @v=10;
+CALL p1(@v);
+SELECT @v;
+DROP PROCEDURE p1;
+
+--echo # No HANDLER declarations, some code, no exceptions
+DELIMITER /;
+CREATE PROCEDURE p1 (v IN OUT INT)
+IS
+BEGIN
+  v:=123;
+  BEGIN
+    v:=223;
+  END;
+END;
+/
+DELIMITER ;/
+SHOW PROCEDURE CODE p1;
+SET @v=10;
+CALL p1(@v);
+SELECT @v;
+DROP PROCEDURE p1;
+
+--echo # No HANDLER declarations, some code, some exceptions
+DELIMITER /;
+CREATE PROCEDURE p1 (v IN OUT INT)
+IS
+BEGIN
+  v:=123;
+  BEGIN
+    v:=223;
+  EXCEPTION
+    WHEN 20002 THEN v:=335;
+  END;
+END;
+/
+DELIMITER ;/
+SHOW PROCEDURE CODE p1;
+SET @v=10;
+CALL p1(@v);
+SELECT @v;
+DROP PROCEDURE p1;
+
+--echo # Some HANDLER declarations, no code, no exceptions
+DELIMITER /;
+CREATE PROCEDURE p1 (v IN OUT INT)
+IS
+BEGIN
+  v:=123;
+  DECLARE
+    EXIT HANDLER FOR 1000
+    BEGIN
+      v:=323;
+    END;
+  BEGIN
+  END;
+END;
+/
+DELIMITER ;/
+SHOW PROCEDURE CODE p1;
+SET @v=10;
+CALL p1(@v);
+SELECT @v;
+DROP PROCEDURE p1;
+
+--echo # Some HANDLER declarations, no code, some exceptions
+DELIMITER /;
+CREATE PROCEDURE p1 (v IN OUT INT)
+IS
+BEGIN
+  v:=123;
+  DECLARE
+    EXIT HANDLER FOR 1000
+    BEGIN
+      v:=323;
+    END;
+  BEGIN
+  EXCEPTION
+    WHEN 20002 THEN v:=335;
+  END;
+END;
+/
+DELIMITER ;/
+SHOW PROCEDURE CODE p1;
+SET @v=10;
+CALL p1(@v);
+SELECT @v;
+DROP PROCEDURE p1;
+
+--echo # Some HANDLER declarations, some code, no exceptions
+DELIMITER /;
+CREATE PROCEDURE p1 (v IN OUT INT)
+IS
+BEGIN
+  v:=123;
+  DECLARE
+    EXIT HANDLER FOR 1000
+    BEGIN
+      v:=323;
+    END;
+  BEGIN
+   v:= 324;
+  END;
+END;
+/
+DELIMITER ;/
+SHOW PROCEDURE CODE p1;
+SET @v=10;
+CALL p1(@v);
+SELECT @v;
+DROP PROCEDURE p1;
+
+--echo # Some HANDLER declarations, some code, some exceptions
+DELIMITER /;
+CREATE PROCEDURE p1 (v IN OUT INT)
+IS
+BEGIN
+  v:=123;
+  DECLARE
+    EXIT HANDLER FOR 1000
+    BEGIN
+      v:=323;
+    END;
+  BEGIN
+    v:= 324;
+  EXCEPTION WHEN 2002 THEN v:= 325;
+  END;
+END;
+/
+DELIMITER ;/
+SHOW PROCEDURE CODE p1;
+SET @v=10;
+CALL p1(@v);
+SELECT @v;
+DROP PROCEDURE p1;
+
+--echo #
+--echo # Testing EXIT statement
+--echo #
+
+DELIMITER /;
+CREATE FUNCTION f1 RETURN INT
+IS
+  i INT := 0;
+BEGIN
+  LOOP
+    i:= i + 1;
+    IF i >= 5 THEN
+      EXIT;
+    END IF;
+  END LOOP;
+  RETURN i;
+END;
+/
+DELIMITER ;/
+SHOW FUNCTION CODE f1;
+SELECT f1() FROM DUAL;
+DROP FUNCTION f1;
+
+
+DELIMITER /;
+CREATE FUNCTION f1 RETURN INT
+IS
+  i INT := 0;
+BEGIN
+  LOOP
+    i:= i + 1;
+    EXIT WHEN i >=5;
+  END LOOP;
+  RETURN i;
+END;
+/
+DELIMITER ;/
+SHOW FUNCTION CODE f1;
+SELECT f1() FROM DUAL;
+DROP FUNCTION f1;
+
+
+DELIMITER /;
+CREATE FUNCTION f1 RETURN INT
+IS
+  i INT := 0;
+BEGIN
+  LOOP
+    BEGIN
+      i:= i + 1;
+      IF i >= 5 THEN
+        EXIT;
+      END IF;
+    EXCEPTION
+      WHEN OTHERS THEN i:= 1000;
+    END;
+  END LOOP;
+  RETURN i;
+END;
+/
+DELIMITER ;/
+SHOW FUNCTION CODE f1;
+SELECT f1() FROM DUAL;
+DROP FUNCTION f1;
+
+
+DELIMITER /;
+CREATE PROCEDURE p1(a IN OUT INT)
+IS
+  i INT := 0;
+BEGIN
+  LOOP
+    LOOP
+      BEGIN
+        i:= i + 1;
+        IF i >=5 THEN
+          EXIT;
+        END IF;
+      EXCEPTION
+        WHEN OTHERS THEN a:=1000;
+      END;
+    END LOOP;
+    i:= i + 100;
+    EXIT;
+  END LOOP;
+  a:= i;
+EXCEPTION
+  WHEN OTHERS THEN a:=11;
+END;
+/
+DELIMITER ;/
+SHOW PROCEDURE CODE p1;
+set @v= 10;
+CALL p1(@v);
+SELECT @v;
+DROP PROCEDURE p1;
+
+
+--echo # Testing RETURN in procedures
+DELIMITER /;
+CREATE PROCEDURE p1 (a IN OUT INT)
+AS
+BEGIN
+  IF a < 10 THEN
+    BEGIN
+      a:= a + 1;
+      RETURN;
+    END;
+  END IF;
+  a:= 200;
+EXCEPTION
+  WHEN OTHERS THEN
+  BEGIN
+    a:= 100;
+    RETURN;
+  END;
+END;
+/
+DELIMITER ;/
+SHOW PROCEDURE CODE p1;
+DROP PROCEDURE p1;
+
+
+--echo # Testing FOR loop statement
+DELIMITER /;
+CREATE FUNCTION f1 (a INT, b INT) RETURN INT
+AS
+  total INT := 0;
+BEGIN
+  FOR i IN 1 .. a
+  LOOP
+    total:= total + i;
+    IF i = b THEN
+      EXIT;
+    END IF;
+  END LOOP;
+  RETURN total;
+END
+/
+DELIMITER ;/
+SHOW FUNCTION CODE f1;
+SELECT f1(3, 100) FROM DUAL;
+SELECT f1(3, 2) FROM DUAL;
+DROP FUNCTION f1;
+
+DELIMITER /;
+CREATE FUNCTION f1 (a INT, b INT) RETURN INT
+AS
+  total INT := 0;
+BEGIN
+  FOR i IN REVERSE 1..a
+  LOOP
+    total:= total + i;
+    IF i = b THEN
+      EXIT;
+    END IF;
+  END LOOP;
+  RETURN total;
+END
+/
+DELIMITER ;/
+SHOW FUNCTION CODE f1;
+SELECT f1(3, 100) FROM DUAL;
+SELECT f1(3, 2) FROM DUAL;
+DROP FUNCTION f1;
+
+
+--echo # Testing labeled FOR LOOP statement
+
+DELIMITER /;
+CREATE FUNCTION f1 (a INT, limita INT, b INT, limitb INT) RETURN INT
+AS
+  total INT := 0;
+BEGIN
+  <<la>>
+  FOR ia IN 1 .. a
+  LOOP
+    total:= total + 1000;
+    <<lb>>
+    FOR ib IN 1 .. b
+    LOOP
+      total:= total + 1;
+      EXIT lb WHEN ib = limitb;
+      EXIT la WHEN ia = limita;
+    END LOOP lb;
+  END LOOP la;
+  RETURN total;
+END;
+/
+DELIMITER ;/
+SHOW FUNCTION CODE f1;
+SELECT f1(2, 1, 2, 2) FROM DUAL;
+SELECT f1(2, 2, 2, 2) FROM DUAL;
+SELECT f1(2, 3, 2, 3) FROM DUAL;
+DROP FUNCTION f1;
+
+
+--echo # Testing labeled ITERATE in a labeled FOR LOOP
+
+DELIMITER /;
+CREATE FUNCTION f1(a INT) RETURN INT
+AS
+  total INT:= 0;
+BEGIN
+  <<li>>
+  FOR i IN 1 .. a
+  LOOP
+    total:= total + 1000;
+    IF i = 5 THEN
+      ITERATE li;
+    END IF;
+    total:= total + 1;
+  END LOOP;
+  RETURN total;
+END;
+/
+DELIMITER ;/
+SHOW FUNCTION CODE f1;
+SELECT f1(3), f1(4), f1(5), f1(6) FROM DUAL;
+DROP FUNCTION f1;
+
+
+DELIMITER /;
+CREATE FUNCTION f1(a INT) RETURN INT
+AS
+  total INT:= 0;
+BEGIN
+  <<li>>
+  FOR i IN 1 .. a
+  LOOP
+    FOR j IN 1 .. 2
+    LOOP
+      total:= total + 1000;
+      IF i = 5 THEN
+        ITERATE li;
+      END IF;
+      total:= total + 1;
+    END LOOP;
+  END LOOP;
+  RETURN total;
+END;
+/
+DELIMITER ;/
+SHOW FUNCTION CODE f1;
+SELECT f1(3), f1(4), f1(5), f1(6) FROM DUAL;
+DROP FUNCTION f1;
+
+
+DELIMITER /;
+CREATE FUNCTION f1(a INT) RETURN INT
+AS
+  total INT:= 0;
+BEGIN
+  <<lj>>
+  FOR j IN 1 .. 2
+  LOOP
+    <<li>>
+    FOR i IN 1 .. a
+    LOOP
+      total:= total + 1000;
+      IF i = 5 THEN
+        ITERATE li;
+      END IF;
+      total:= total + 1;
+    END LOOP;
+  END LOOP;
+  RETURN total;
+END;
+/
+DELIMITER ;/
+SHOW FUNCTION CODE f1;
+SELECT f1(3), f1(4), f1(5), f1(6) FROM DUAL;
+DROP FUNCTION f1;
+
+
+--echo # Testing CONTINUE statement
+
+DELIMITER /;
+CREATE FUNCTION f1(a INT) RETURN INT
+AS
+  total INT:= 0;
+BEGIN
+  FOR i IN 1 .. a
+  LOOP
+    CONTINUE WHEN i=5;
+    total:= total + 1;
+  END LOOP;
+  RETURN total;
+END;
+/
+DELIMITER ;/
+SHOW FUNCTION CODE f1;
+SELECT f1(3), f1(4), f1(5), f1(6) FROM DUAL;
+DROP FUNCTION f1;
+
+--echo #
+--echo # Start of MDEV-10597 Cursors with parameters
+--echo #
+
+DELIMITER $$;
+CREATE PROCEDURE p1(arg_value_a VARCHAR, arg_value_b VARCHAR,
+                    arg_pattern_a VARCHAR, arg_pattern_b VARCHAR)
+AS
+  v_a VARCHAR(10);
+  v_b VARCHAR(20);
+  CURSOR c (p_value_a VARCHAR,
+            p_value_b VARCHAR,
+            p_pattern_a VARCHAR,
+            p_pattern_b VARCHAR,
+            p_limit_a INT,
+            p_limit_b INT,
+            p_unused TEXT) IS
+    (SELECT p_value_a, p_value_b FROM DUAL
+       WHERE p_value_a LIKE p_pattern_a LIMIT p_limit_a)
+    UNION
+    (SELECT p_value_b, p_value_a FROM DUAL
+       WHERE p_value_b LIKE p_pattern_b LIMIT p_limit_b);
+BEGIN
+  OPEN c(arg_value_a, (SELECT arg_value_b),
+         arg_pattern_a, arg_pattern_b, 100, 101, 'x');
+  LOOP
+    FETCH c INTO v_a, v_b;
+    EXIT WHEN c%NOTFOUND;
+    SELECT v_a, v_b;
+  END LOOP;
+  CLOSE c;
+END;
+$$
+DELIMITER ;$$
+CALL p1('aaa','bbb','aaa','bbb');
+SHOW PROCEDURE CODE p1;
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # End of MDEV-10597 Cursors with parameters
+--echo #
+
+
+--echo #
+--echo # MDEV-10914 ROW data type for stored routine variables
+--echo #
+DELIMITER $$;
+CREATE FUNCTION f1() RETURN INT
+AS
+  a ROW(a INT, b INT);
+BEGIN
+  a.b:= 200;
+  RETURN a.b;
+END;
+$$
+DELIMITER ;$$
+SHOW FUNCTION CODE f1;
+SELECT f1();
+DROP FUNCTION f1;
+
+
+DELIMITER $$;
+CREATE PROCEDURE p1
+AS
+  rec ROW(a INT,b DOUBLE,c DECIMAL(10,3),d VARCHAR(10));
+BEGIN
+  rec:= ROW(10,20.123456,30.123,'test');
+  SELECT rec.a, rec.b, rec.c, rec.d;
+END;
+$$
+DELIMITER ;$$
+SHOW PROCEDURE CODE p1;
+CALL p1;
+DROP PROCEDURE p1;
+
+
+DELIMITER $$;
+CREATE PROCEDURE p1
+AS
+  rec ROW(a INT,b DOUBLE,c DECIMAL(10,3),d VARCHAR(10)) :=
+      ROW(10,20.123456,30.123,'test');
+BEGIN
+  SELECT rec.a, rec.b, rec.c, rec.d;
+END;
+$$
+DELIMITER ;$$
+SHOW PROCEDURE CODE p1;
+CALL p1;
+DROP PROCEDURE p1;
+
+
+DELIMITER $$;
+CREATE PROCEDURE p1
+AS
+  rec1 ROW(a INT,b DOUBLE,c DECIMAL(10,3),d VARCHAR(10));
+  rec2 ROW(a INT,b DOUBLE,c DECIMAL(10,3),d VARCHAR(10));
+BEGIN
+  rec1:= ROW(10,20.123456,30.123,'test');
+  rec2:= rec1;
+  SELECT rec2.a, rec2.b, rec2.c, rec2.d;
+END;
+$$
+DELIMITER ;$$
+SHOW PROCEDURE CODE p1;
+CALL p1;
+DROP PROCEDURE p1;
+
+
+DELIMITER $$;
+CREATE PROCEDURE p1
+AS
+  rec1 ROW(a INT,b DOUBLE,c DECIMAL(10,3),d VARCHAR(10)) :=
+       ROW(10,20.123456,30.123,'test');
+  rec2 ROW(a INT,b DOUBLE,c DECIMAL(10,3),d VARCHAR(10)) := rec1;
+BEGIN
+  SELECT rec2.a, rec2.b, rec2.c, rec2.d;
+END;
+$$
+DELIMITER ;$$
+SHOW PROCEDURE CODE p1;
+CALL p1;
+DROP PROCEDURE p1;
+
+--echo #
+--echo # End of MDEV-10914 ROW data type for stored routine variables
+--echo #
+
+--echo #
+--echo # MDEV-12133 sql_mode=ORACLE: table%ROWTYPE in variable declarations
+--echo #
+
+CREATE TABLE t1 (a INT, b VARCHAR(10), c DOUBLE, d DECIMAL(10,2));
+DELIMITER $$;
+CREATE PROCEDURE p1()
+AS
+  rec1 t1%ROWTYPE;
+BEGIN
+  rec1.a:= 10;
+  rec1.b:= 'bbb';
+  rec1.c:= 10e2;
+  rec1.d:= 10.12;
+  rec1.c:= rec1.d;
+END;
+$$
+DELIMITER ;$$
+SHOW PROCEDURE CODE p1;
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # MDEV-12011 sql_mode=ORACLE: cursor%ROWTYPE in variable declarations
+--echo #
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+DELIMITER $$;
+CREATE PROCEDURE p1()
+AS
+  CURSOR cur1 IS SELECT * FROM t1;
+  CURSOR cur2 IS SELECT * FROM t1;
+BEGIN
+  DECLARE
+    rec1,rec2 cur1%ROWTYPE;
+    rec3 cur2%ROWTYPE;
+  BEGIN
+    rec1.a:= 10;
+    rec1.b:= 'bbb';
+  END;
+END;
+$$
+DELIMITER ;$$
+SHOW PROCEDURE CODE p1;
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # MDEV-10581 sql_mode=ORACLE: Explicit cursor FOR LOOP
+--echo #
+
+DELIMITER $$;
+CREATE PROCEDURE p1
+AS
+  CURSOR cur0 IS SELECT 10 AS a, 'b0' AS b;
+  CURSOR cur1 IS SELECT 10 AS a, 'b0' AS b;
+  CURSOR cur2 IS SELECT 10 AS a, 'b0' AS b;
+BEGIN
+  FOR rec1 IN cur1
+  LOOP
+    SELECT rec1.a, rec1.b;
+    rec1.a:= 11;
+    rec1.b:= 'b1';
+    SELECT rec1.a, rec1.b;
+  END LOOP;
+  FOR rec0 IN cur0
+  LOOP
+    rec0.a:= 10;
+    rec0.b:='b0';
+  END LOOP;
+  FOR rec2 IN cur2
+  LOOP
+    rec2.a:= 10;
+    rec2.b:='b0';
+  END LOOP;
+END;
+$$
+DELIMITER ;$$
+SHOW PROCEDURE CODE p1;
+DROP PROCEDURE p1;
+
+
+DELIMITER $$;
+CREATE PROCEDURE p1
+AS
+  CURSOR cur0 IS SELECT 10 AS a, 'b0' AS b;
+BEGIN
+  FOR rec0 IN cur0
+  LOOP
+    DECLARE
+      CURSOR cur1 IS SELECT 11 AS a, 'b1' AS b;
+    BEGIN
+      rec0.a:= 11;
+      rec0.b:= 'b0';
+      FOR rec1 IN cur1
+      LOOP
+        rec1.a:= 11;
+        rec1.b:= 'b1';
+        DECLARE
+          CURSOR cur2 IS SELECT 12 AS a, 'b2' AS b;
+        BEGIN
+          FOR rec2 IN cur2
+          LOOP
+            rec2.a:=12;
+            rec2.b:='b2';
+          END LOOP;
+        END;
+      END LOOP;
+    END;
+  END LOOP;
+END;
+$$
+DELIMITER ;$$
+SHOW PROCEDURE CODE p1;
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # MDEV-12098 sql_mode=ORACLE: Implicit cursor FOR loop
+--echo #
+
+DELIMITER $$;
+CREATE PROCEDURE p1
+AS
+BEGIN
+  FOR rec1 IN (SELECT 11 AS a, 'b1' AS b)
+  LOOP
+    SELECT rec1.a, rec1.b;
+    rec1.a:= 11;
+    rec1.b:= 'b1';
+    SELECT rec1.a, rec1.b;
+  END LOOP;
+  FOR rec0 IN (SELECT 10 AS a, 'b0' AS b)
+  LOOP
+    rec0.a:= 10;
+    rec0.b:='b0';
+  END LOOP;
+  FOR rec2 IN (SELECT 12 AS a, 'b2' AS b)
+  LOOP
+    rec2.a:= 10;
+    rec2.b:='b0';
+  END LOOP;
+END;
+$$
+DELIMITER ;$$
+SHOW PROCEDURE CODE p1;
+DROP PROCEDURE p1;
+
+
+DELIMITER $$;
+CREATE PROCEDURE p1
+AS
+BEGIN
+  FOR rec0 IN (SELECT 10 AS a, 'b0' AS b)
+  LOOP
+    rec0.a:= 11;
+    rec0.b:= 'b0';
+    FOR rec1 IN (SELECT 11 AS a, 'b1' AS b)
+    LOOP
+      rec1.a:= 11;
+      rec1.b:= 'b1';
+      FOR rec2 IN (SELECT 12 AS a, 'b2' AS b)
+      LOOP
+        rec2.a:=12;
+        rec2.b:='b2';
+      END LOOP;
+    END LOOP;
+  END LOOP;
+END;
+$$
+DELIMITER ;$$
+SHOW PROCEDURE CODE p1;
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # MDEV-10598 sql_mode=ORACLE: Variable declarations can go after cursor declarations
+--echo #
+
+--echo #
+--echo # Cursor declaration and cursor%ROWTYPE declaration in the same block
+--echo #
+
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+INSERT INTO t1 VALUES (1,'a');
+DELIMITER $$;
+CREATE PROCEDURE p1()
+AS
+  CURSOR cur1 IS SELECT a FROM t1;
+  rec1 cur1%ROWTYPE;
+BEGIN
+  rec1.a:= 10;
+END;
+$$
+DELIMITER ;$$
+SHOW PROCEDURE CODE p1;
+CALL p1;
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
+--echo #
+--echo # Recursive cursor and cursor%ROWTYPE declarations in the same block
+--echo #
+
+DELIMITER $$;
+CREATE PROCEDURE p1
+AS
+  a INT:=10;
+  CURSOR cur1 IS SELECT a;
+  rec1 cur1%ROWTYPE;
+  CURSOR cur2 IS SELECT rec1.a + 1  "a";
+  rec2 cur2%ROWTYPE;
+BEGIN
+  OPEN cur1;
+  FETCH cur1 INTO rec1;
+  CLOSE cur1;
+  SELECT rec1.a;
+  open cur2;
+  FETCH cur2 INTO rec2;
+  CLOSE cur2;
+  SELECT rec2.a;
+END;
+$$
+DELIMITER ;$$
+SHOW PROCEDURE CODE p1;
+CALL p1();
+DROP PROCEDURE p1;
+
+
+--echo #
+--echo # MDEV-12441 Variables declared after cursors with parameters lose values
+--echo #
+
+DELIMITER $$;
+CREATE PROCEDURE p1() AS
+  x0 INT:=100;
+  CURSOR cur(cp1 INT, cp2 INT) IS SELECT cp1+cp2;
+  x1 INT:=101;
+BEGIN
+  OPEN cur(10,11);
+  CLOSE cur;
+  SELECT x0, x1;
+END;
+$$
+DELIMITER ;$$
+SHOW PROCEDURE CODE p1;
+CALL p1();
+DROP PROCEDURE p1;
+
+
+DELIMITER $$;
+CREATE PROCEDURE p1() AS
+  x0 INT:=100;
+  CURSOR cur0(cp1 INT, cp2 INT) IS SELECT cp1+cp2;
+  x1 INT:=101;
+  CURSOR cur1(cp1 INT, cp2 INT) IS SELECT cp1+cp2;
+  x2 INT:=102;
+  CURSOR cur2(cp1 INT, cp2 INT) IS SELECT cp1+cp2;
+  x3 INT:=103;
+BEGIN
+  OPEN cur0(0,1);
+  CLOSE cur0;
+  SELECT x0, x1, x2, x3;
+  OPEN cur1(10,11);
+  CLOSE cur1;
+  SELECT x0, x1, x2, x3;
+  OPEN cur2(20,21);
+  CLOSE cur2;
+  SELECT x0, x1, x2, x3;
+END;
+$$
+DELIMITER ;$$
+SHOW PROCEDURE CODE p1;
+CALL p1();
+DROP PROCEDURE p1;
+
+
+CREATE TABLE t1 (a INT);
+DELIMITER $$;
+CREATE PROCEDURE p1() AS
+  x0 INT:=100;
+  CURSOR cur(cp1 INT, cp2 INT) IS SELECT cp1+cp2;
+  x1 t1.a%TYPE:=101;
+BEGIN
+  OPEN cur(10,11);
+  CLOSE cur;
+  SELECT x0, x1;
+END;
+$$
+DELIMITER ;$$
+SHOW PROCEDURE CODE p1;
+CALL p1();
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
+
+DELIMITER $$;
+CREATE PROCEDURE p1() AS
+  x0 INT:=100;
+  CURSOR cur(cp1 INT, cp2 INT) IS SELECT cp1+cp2;
+  x1 ROW(a INT,b INT):=ROW(101,102);
+BEGIN
+  OPEN cur(10,11);
+  CLOSE cur;
+  SELECT x0, x1.a, x1.b;
+END;
+$$
+DELIMITER ;$$
+SHOW PROCEDURE CODE p1;
+CALL p1();
+DROP PROCEDURE p1;
+
+--echo #
+--echo # MDEV-19640 Wrong SHOW PROCEDURE output for SET GLOBAL sysvar1=expr, sysvar2=expr
+--echo #
+
+DELIMITER $$;
+CREATE OR REPLACE PROCEDURE p1() AS
+BEGIN
+  SET GLOBAL max_allowed_packet=16000000, max_error_count=60;
+  SELECT @@GLOBAL.max_allowed_packet, @@GLOBAL.max_error_count;
+END;
+$$
+DELIMITER ;$$
+SHOW PROCEDURE CODE p1;
+DROP PROCEDURE p1;
+
+--echo #
+--echo # MDEV-19639 sql_mode=ORACLE: Wrong SHOW PROCEDURE output for sysvar:=expr
+--echo #
+
+DELIMITER $$;
+CREATE OR REPLACE PROCEDURE p1() AS
+BEGIN
+  max_error_count:=10;
+END;
+$$
+DELIMITER ;$$
+SHOW PROCEDURE CODE p1;
+DROP PROCEDURE p1;
+
+