diff options
author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 18:00:34 +0000 |
---|---|---|
committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 18:00:34 +0000 |
commit | 3f619478f796eddbba6e39502fe941b285dd97b1 (patch) | |
tree | e2c7b5777f728320e5b5542b6213fd3591ba51e2 /mysql-test/main/sp-for-loop.test | |
parent | Initial commit. (diff) | |
download | mariadb-3f619478f796eddbba6e39502fe941b285dd97b1.tar.xz mariadb-3f619478f796eddbba6e39502fe941b285dd97b1.zip |
Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'mysql-test/main/sp-for-loop.test')
-rw-r--r-- | mysql-test/main/sp-for-loop.test | 250 |
1 files changed, 250 insertions, 0 deletions
diff --git a/mysql-test/main/sp-for-loop.test b/mysql-test/main/sp-for-loop.test new file mode 100644 index 00000000..d6b30b0e --- /dev/null +++ b/mysql-test/main/sp-for-loop.test @@ -0,0 +1,250 @@ +--echo # +--echo # MDEV-14415 Add Oracle-style FOR loop to sql_mode=DEFAULT +--echo # + + +CREATE TABLE t1 (a INT); +DELIMITER /; +FOR i IN 1..3 +DO + INSERT INTO t1 VALUES (i); +END FOR; +/ +DELIMITER ;/ +SELECT * FROM t1; +DROP TABLE t1; + + +# Dots must have no delimiters in between + +DELIMITER /; +--error ER_PARSE_ERROR +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; +/ +DELIMITER ;/ + + +DELIMITER /; +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; +/ +DELIMITER ;/ +SELECT f1(1, 3, 100) FROM DUAL; +SELECT f1(1, 3, 2) FROM DUAL; +DROP FUNCTION f1; + + +DELIMITER /; +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; +/ +DELIMITER ;/ +SELECT f1() FROM DUAL; +DROP FUNCTION f1; + + +DELIMITER /; +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 +/ +DELIMITER ;/ +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) 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; +/ +DELIMITER ;/ +SELECT f1(1, 1, 1, 1) FROM DUAL; +SELECT f1(1, 2, 1, 2) FROM DUAL; +SELECT f1(2, 1, 2, 1) FROM DUAL; +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 statement + +DELIMITER /; +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; +/ +DELIMITER ;/ +SELECT f1(3,3,0), f1(3,3,1), f1(3,3,2), f1(3,3,3), f1(3,3,4) FROM DUAL; +DROP FUNCTION f1; + + +--echo # Testing INTERATE statement + +DELIMITER /; +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; +/ +DELIMITER ;/ +SELECT f1(3), f1(4), f1(5), f1(6) FROM DUAL; +DROP FUNCTION f1; + + +DELIMITER /; +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; +/ +DELIMITER ;/ +SELECT f1(3), f1(4), f1(5) FROM DUAL; +DROP FUNCTION f1; + +--echo # +--echo # End of 10.3 tests +--echo # + +--echo # +--echo # Start of 10.4 tests +--echo # + +--echo # +--echo # MDEV-32275 getting error 'Illegal parameter data types row and bigint for operation '+' ' when using ITERATE in a FOR..DO +--echo # + +DELIMITER $$; +CREATE OR REPLACE PROCEDURE forIterateBug() +BEGIN + DECLARE loopDone TINYINT DEFAULT FALSE; + FOR _unused IN (SELECT '') DO + innerLoop: LOOP + IF loopDone THEN + LEAVE innerLoop; + END IF; + SET loopDone = TRUE; + BEGIN + ITERATE innerLoop; + END; + END LOOP; + END FOR; +END; +$$ +DELIMITER ;$$ +CALL forIterateBug; +DROP PROCEDURE forIterateBug; + + +--echo # +--echo # End of 10.4 tests +--echo # |