Create 'main' test directory and move 't' and 'r' there

1 files changed, 212 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 00000000000..6350e9fb9d3
--- /dev/null
+++ b/mysql-test/main/sp-for-loop.test
@@ -0,0 +1,212 @@
+--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 a..1
+  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;