#
# 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 1..a
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;