1 files changed, 208 insertions, 0 deletions
diff --git a/mysql-test/main/sp-for-loop.result b/mysql-test/main/sp-for-loop.result
new file mode 100644
index 00000000000..0da09586df5
--- /dev/null
+++ b/mysql-test/main/sp-for-loop.result
@@ -0,0 +1,208 @@
+#
+# MDEV-14415 Add Oracle-style FOR loop to sql_mode=DEFAULT
+#
+CREATE TABLE t1 (a INT);
+FOR i IN 1..3
+DO
+INSERT INTO t1 VALUES (i);
+END FOR;
+/
+SELECT * FROM t1;
+a
+1
+2
+3
+DROP TABLE t1;
+CREATE FUNCTION f1 (lower_bound INT, upper_bound INT, lim INT) RETURNS INT
+BEGIN
+DECLARE total INT DEFAULT 0;
+FOR i IN lower_bound . . upper_bound
+DO
+NULL
+END FOR;
+RETURN total;
+END;
+/
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '. upper_bound
+DO
+NULL
+END FOR;
+RETURN total;
+END' at line 4
+CREATE FUNCTION f1 (lower_bound INT, upper_bound INT, lim INT) RETURNS INT
+BEGIN
+DECLARE total INT DEFAULT 0;
+lab:
+FOR i IN lower_bound .. upper_bound
+DO
+SET total= total + i;
+IF i = lim THEN
+LEAVE lab;
+END IF;
+-- Bounds are calculated only once.
+-- The below assignments have no effect on the loop condition
+SET lower_bound= 900;
+SET upper_bound= 1000;
+END FOR;
+RETURN total;
+END;
+/
+SELECT f1(1, 3, 100) FROM DUAL;
+f1(1, 3, 100)
+6
+SELECT f1(1, 3, 2) FROM DUAL;
+f1(1, 3, 2)
+3
+DROP FUNCTION f1;
+CREATE FUNCTION f1() RETURNS INT
+BEGIN
+DECLARE total INT DEFAULT 0;
+FOR i IN 1 .. 5
+DO
+SET total= total + 1000;
+forj:
+FOR j IN 1 .. 5
+DO
+SET total= total + 1;
+IF j = 3 THEN
+LEAVE forj; -- End the internal loop
+END IF;
+END FOR;
+END FOR;
+RETURN total;
+END;
+/
+SELECT f1() FROM DUAL;
+f1()
+5015
+DROP FUNCTION f1;
+CREATE FUNCTION f1 (a INT, b INT) RETURNS INT
+BEGIN
+DECLARE total INT DEFAULT 0;
+fori:
+FOR i IN REVERSE a..1
+DO
+SET total= total + i;
+IF i = b THEN
+LEAVE fori;
+END IF;
+END FOR;
+RETURN total;
+END
+/
+SELECT f1(3, 100) FROM DUAL;
+f1(3, 100)
+6
+SELECT f1(3, 2) FROM DUAL;
+f1(3, 2)
+5
+DROP FUNCTION f1;
+# Testing labeled FOR LOOP statement
+CREATE FUNCTION f1 (a INT, limita INT, b INT, limitb INT) RETURNS INT
+BEGIN
+DECLARE total INT DEFAULT 0;
+la:
+FOR ia IN 1 .. a
+DO
+SET total= total + 1000;
+lb:
+FOR ib IN 1 .. b
+DO
+SET total= total + 1;
+IF ib = limitb THEN
+LEAVE lb;
+END IF;
+IF ia = limita THEN
+LEAVE la;
+END IF;
+END FOR lb;
+END FOR la;
+RETURN total;
+END;
+/
+SELECT f1(1, 1, 1, 1) FROM DUAL;
+f1(1, 1, 1, 1)
+1001
+SELECT f1(1, 2, 1, 2) FROM DUAL;
+f1(1, 2, 1, 2)
+1001
+SELECT f1(2, 1, 2, 1) FROM DUAL;
+f1(2, 1, 2, 1)
+2002
+SELECT f1(2, 1, 2, 2) FROM DUAL;
+f1(2, 1, 2, 2)
+1001
+SELECT f1(2, 2, 2, 2) FROM DUAL;
+f1(2, 2, 2, 2)
+2003
+SELECT f1(2, 3, 2, 3) FROM DUAL;
+f1(2, 3, 2, 3)
+2004
+DROP FUNCTION f1;
+# Testing labeled ITERATE in a labeled FOR LOOP statement
+CREATE FUNCTION f1 (a INT, b INT, blim INT) RETURNS INT
+BEGIN
+DECLARE total INT DEFAULT 0;
+la:
+FOR ia IN 1 .. a
+DO
+SET total= total + 1000;
+BEGIN
+DECLARE ib INT DEFAULT 1;
+WHILE ib <= b
+DO
+IF ib > blim THEN
+ITERATE la;
+END IF;
+SET ib= ib + 1;
+SET total= total + 1;
+END WHILE;
+END;
+END FOR la;
+RETURN total;
+END;
+/
+SELECT f1(3,3,0), f1(3,3,1), f1(3,3,2), f1(3,3,3), f1(3,3,4) FROM DUAL;
+f1(3,3,0)	f1(3,3,1)	f1(3,3,2)	f1(3,3,3)	f1(3,3,4)
+3000	3003	3006	3009	3009
+DROP FUNCTION f1;
+# Testing INTERATE statement
+CREATE FUNCTION f1(a INT) RETURNS INT
+BEGIN
+DECLARE total INT DEFAULT 0;
+fori:
+FOR i IN 1 .. a
+DO
+IF i=5 THEN
+ITERATE fori;
+END IF;
+SET total= total + 1;
+END FOR;
+RETURN total;
+END;
+/
+SELECT f1(3), f1(4), f1(5), f1(6) FROM DUAL;
+f1(3)	f1(4)	f1(5)	f1(6)
+3	4	4	5
+DROP FUNCTION f1;
+CREATE FUNCTION f1(a INT) RETURNS INT
+BEGIN
+DECLARE total INT DEFAULT 0;
+lj:
+FOR j IN 1 .. 2
+DO
+FOR i IN 1 .. a
+DO
+IF i=5 THEN
+ITERATE lj;
+END IF;
+SET total= total + 1;
+END FOR;
+END FOR;
+RETURN total;
+END;
+/
+SELECT f1(3), f1(4), f1(5) FROM DUAL;
+f1(3)	f1(4)	f1(5)
+6	8	8
+DROP FUNCTION f1;