summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/sp-code.result303
-rw-r--r--mysql-test/r/sp-cursor.result131
-rw-r--r--mysql-test/r/sp-for-loop.result208
-rw-r--r--mysql-test/t/sp-code.test169
-rw-r--r--mysql-test/t/sp-cursor.test133
-rw-r--r--mysql-test/t/sp-for-loop.test212
-rw-r--r--sql/sp_head.cc4
-rw-r--r--sql/sp_head.h5
-rw-r--r--sql/sp_rcontext.cc4
-rw-r--r--sql/sp_rcontext.h2
-rw-r--r--sql/sql_lex.cc34
-rw-r--r--sql/sql_lex.h3
-rw-r--r--sql/sql_yacc.yy131
-rw-r--r--sql/sql_yacc_ora.yy27
14 files changed, 1330 insertions, 36 deletions
diff --git a/mysql-test/r/sp-code.result b/mysql-test/r/sp-code.result
index ddb2901b8a4..3a4dc9db6f8 100644
--- a/mysql-test/r/sp-code.result
+++ b/mysql-test/r/sp-code.result
@@ -998,3 +998,306 @@ Pos Instruction
7 set b.a@1["a"] a.a@0["a"]
DROP PROCEDURE p1;
DROP TABLE t1;
+#
+# MDEV-14415 Add Oracle-style FOR loop to sql_mode=DEFAULT
+#
+# Integer range FOR loop
+CREATE PROCEDURE p1()
+BEGIN
+FOR i IN 1..3
+DO
+SELECT i;
+END FOR;
+END;
+$$
+CALL p1;
+i
+1
+i
+2
+i
+3
+SHOW PROCEDURE CODE p1;
+Pos Instruction
+0 set i@0 1
+1 set [upper_bound]@1 3
+2 jump_if_not 6(6) i@0 <= [upper_bound]@1
+3 stmt 0 "SELECT i"
+4 set i@0 i@0 + 1
+5 jump 2
+DROP PROCEDURE p1;
+# Nested integer range FOR loops
+CREATE PROCEDURE p1()
+BEGIN
+fori:
+FOR i IN 1..3
+DO
+forj:
+FOR j IN 1..3
+DO
+IF i = 3 THEN
+LEAVE fori;
+END IF;
+IF j = 3 THEN
+LEAVE forj;
+END IF;
+SELECT i,j;
+END FOR;
+END FOR;
+END;
+$$
+CALL p1;
+i j
+1 1
+i j
+1 2
+i j
+2 1
+i j
+2 2
+SHOW PROCEDURE CODE p1;
+Pos Instruction
+0 set i@0 1
+1 set [upper_bound]@1 3
+2 jump_if_not 17(17) i@0 <= [upper_bound]@1
+3 set j@2 1
+4 set [upper_bound]@3 3
+5 jump_if_not 13(13) j@2 <= [upper_bound]@3
+6 jump_if_not 8(8) i@0 = 3
+7 jump 17
+8 jump_if_not 10(10) j@2 = 3
+9 jump 13
+10 stmt 0 "SELECT i,j"
+11 set j@2 j@2 + 1
+12 jump 5
+13 set i@0 i@0 + 1
+14 jump 2
+DROP PROCEDURE p1;
+# Explicit cursor FOR loops
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE cur0 CURSOR FOR SELECT 10 AS a, 'b0' AS b;
+DECLARE cur1 CURSOR FOR SELECT 10 AS a, 'b0' AS b;
+DECLARE cur2 CURSOR FOR SELECT 10 AS a, 'b0' AS b;
+FOR rec1 IN cur1
+DO
+SELECT rec1.a, rec1.b;
+SET rec1.a= 11;
+SET rec1.b= 'b1';
+SELECT rec1.a, rec1.b;
+END FOR;
+FOR rec0 IN cur0
+DO
+SET rec0.a= 10;
+SET rec0.b='b0';
+END FOR;
+FOR rec2 IN cur2
+DO
+SET rec2.a= 10;
+SET rec2.b='b0';
+END FOR;
+END;
+$$
+SHOW PROCEDURE CODE p1;
+Pos Instruction
+0 cpush cur0@0
+1 cpush cur1@1
+2 cpush cur2@2
+3 cursor_copy_struct cur1 rec1@0
+4 copen cur1@1
+5 cfetch cur1@1 rec1@0
+6 jump_if_not 13(13) `cur1`%FOUND
+7 stmt 0 "SELECT rec1.a, rec1.b"
+8 set rec1.a@0["a"] 11
+9 set rec1.b@0["b"] 'b1'
+10 stmt 0 "SELECT rec1.a, rec1.b"
+11 cfetch cur1@1 rec1@0
+12 jump 6
+13 cursor_copy_struct cur0 rec0@1
+14 copen cur0@0
+15 cfetch cur0@0 rec0@1
+16 jump_if_not 21(21) `cur0`%FOUND
+17 set rec0.a@1["a"] 10
+18 set rec0.b@1["b"] 'b0'
+19 cfetch cur0@0 rec0@1
+20 jump 16
+21 cursor_copy_struct cur2 rec2@2
+22 copen cur2@2
+23 cfetch cur2@2 rec2@2
+24 jump_if_not 29(29) `cur2`%FOUND
+25 set rec2.a@2["a"] 10
+26 set rec2.b@2["b"] 'b0'
+27 cfetch cur2@2 rec2@2
+28 jump 24
+29 cpop 3
+DROP PROCEDURE p1;
+# Nested explicit cursor FOR loops
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE cur0 CURSOR FOR SELECT 10 AS a, 'b0' AS b;
+FOR rec0 IN cur0
+DO
+BEGIN
+DECLARE cur1 CURSOR FOR SELECT 11 AS a, 'b1' AS b;
+SET rec0.a= 11;
+SET rec0.b= 'b0';
+FOR rec1 IN cur1
+DO
+SET rec1.a= 11;
+SET rec1.b= 'b1';
+BEGIN
+DECLARE cur2 CURSOR FOR SELECT 12 AS a, 'b2' AS b;
+FOR rec2 IN cur2
+DO
+SET rec2.a=12;
+SET rec2.b='b2';
+END FOR;
+END;
+END FOR;
+END;
+END FOR;
+END;
+$$
+SHOW PROCEDURE CODE p1;
+Pos Instruction
+0 cpush cur0@0
+1 cursor_copy_struct cur0 rec0@0
+2 copen cur0@0
+3 cfetch cur0@0 rec0@0
+4 jump_if_not 29(29) `cur0`%FOUND
+5 cpush cur1@1
+6 set rec0.a@0["a"] 11
+7 set rec0.b@0["b"] 'b0'
+8 cursor_copy_struct cur1 rec1@1
+9 copen cur1@1
+10 cfetch cur1@1 rec1@1
+11 jump_if_not 26(26) `cur1`%FOUND
+12 set rec1.a@1["a"] 11
+13 set rec1.b@1["b"] 'b1'
+14 cpush cur2@2
+15 cursor_copy_struct cur2 rec2@2
+16 copen cur2@2
+17 cfetch cur2@2 rec2@2
+18 jump_if_not 23(23) `cur2`%FOUND
+19 set rec2.a@2["a"] 12
+20 set rec2.b@2["b"] 'b2'
+21 cfetch cur2@2 rec2@2
+22 jump 18
+23 cpop 1
+24 cfetch cur1@1 rec1@1
+25 jump 11
+26 cpop 1
+27 cfetch cur0@0 rec0@0
+28 jump 4
+29 cpop 1
+DROP PROCEDURE p1;
+# Implicit cursor FOR loops
+CREATE PROCEDURE p1()
+BEGIN
+FOR rec1 IN (SELECT 11 AS a, 'b1' AS b)
+DO
+SELECT rec1.a, rec1.b;
+SET rec1.a= 11;
+SET rec1.b= 'b1';
+SELECT rec1.a, rec1.b;
+END FOR;
+FOR rec0 IN (SELECT 10 AS a, 'b0' AS b)
+DO
+SET rec0.a= 10;
+SET rec0.b='b0';
+END FOR;
+FOR rec2 IN (SELECT 12 AS a, 'b2' AS b)
+DO
+SET rec2.a= 10;
+SET rec2.b='b0';
+END FOR;
+END;
+$$
+SHOW PROCEDURE CODE p1;
+Pos Instruction
+0 cpush [implicit_cursor]@0
+1 cursor_copy_struct [implicit_cursor] rec1@0
+2 copen [implicit_cursor]@0
+3 cfetch [implicit_cursor]@0 rec1@0
+4 jump_if_not 11(11) `[implicit_cursor]`%FOUND
+5 stmt 0 "SELECT rec1.a, rec1.b"
+6 set rec1.a@0["a"] 11
+7 set rec1.b@0["b"] 'b1'
+8 stmt 0 "SELECT rec1.a, rec1.b"
+9 cfetch [implicit_cursor]@0 rec1@0
+10 jump 4
+11 cpop 1
+12 cpush [implicit_cursor]@0
+13 cursor_copy_struct [implicit_cursor] rec0@1
+14 copen [implicit_cursor]@0
+15 cfetch [implicit_cursor]@0 rec0@1
+16 jump_if_not 21(21) `[implicit_cursor]`%FOUND
+17 set rec0.a@1["a"] 10
+18 set rec0.b@1["b"] 'b0'
+19 cfetch [implicit_cursor]@0 rec0@1
+20 jump 16
+21 cpop 1
+22 cpush [implicit_cursor]@0
+23 cursor_copy_struct [implicit_cursor] rec2@2
+24 copen [implicit_cursor]@0
+25 cfetch [implicit_cursor]@0 rec2@2
+26 jump_if_not 31(31) `[implicit_cursor]`%FOUND
+27 set rec2.a@2["a"] 10
+28 set rec2.b@2["b"] 'b0'
+29 cfetch [implicit_cursor]@0 rec2@2
+30 jump 26
+31 cpop 1
+DROP PROCEDURE p1;
+# Nested implicit cursor FOR loops
+CREATE PROCEDURE p1()
+BEGIN
+FOR rec0 IN (SELECT 10 AS a, 'b0' AS b)
+DO
+SET rec0.a= 11;
+SET rec0.b= 'b0';
+FOR rec1 IN (SELECT 11 AS a, 'b1' AS b)
+DO
+SET rec1.a= 11;
+SET rec1.b= 'b1';
+FOR rec2 IN (SELECT 12 AS a, 'b2' AS b)
+DO
+SET rec2.a=12;
+SET rec2.b='b2';
+END FOR;
+END FOR;
+END FOR;
+END;
+$$
+SHOW PROCEDURE CODE p1;
+Pos Instruction
+0 cpush [implicit_cursor]@0
+1 cursor_copy_struct [implicit_cursor] rec0@0
+2 copen [implicit_cursor]@0
+3 cfetch [implicit_cursor]@0 rec0@0
+4 jump_if_not 29(29) `[implicit_cursor]`%FOUND
+5 set rec0.a@0["a"] 11
+6 set rec0.b@0["b"] 'b0'
+7 cpush [implicit_cursor]@1
+8 cursor_copy_struct [implicit_cursor] rec1@1
+9 copen [implicit_cursor]@1
+10 cfetch [implicit_cursor]@1 rec1@1
+11 jump_if_not 26(26) `[implicit_cursor]`%FOUND
+12 set rec1.a@1["a"] 11
+13 set rec1.b@1["b"] 'b1'
+14 cpush [implicit_cursor]@2
+15 cursor_copy_struct [implicit_cursor] rec2@2
+16 copen [implicit_cursor]@2
+17 cfetch [implicit_cursor]@2 rec2@2
+18 jump_if_not 23(23) `[implicit_cursor]`%FOUND
+19 set rec2.a@2["a"] 12
+20 set rec2.b@2["b"] 'b2'
+21 cfetch [implicit_cursor]@2 rec2@2
+22 jump 18
+23 cpop 1
+24 cfetch [implicit_cursor]@1 rec1@1
+25 jump 11
+26 cpop 1
+27 cfetch [implicit_cursor]@0 rec0@0
+28 jump 4
+29 cpop 1
+DROP PROCEDURE p1;
diff --git a/mysql-test/r/sp-cursor.result b/mysql-test/r/sp-cursor.result
index d068c19b22b..1f8cb7f0635 100644
--- a/mysql-test/r/sp-cursor.result
+++ b/mysql-test/r/sp-cursor.result
@@ -480,3 +480,134 @@ DROP PROCEDURE p1;
#
# End of MDEV-12457 Cursors with parameters
#
+#
+# MDEV-14415 Add Oracle-style FOR loop to sql_mode=DEFAULT
+#
+# Explicit cursor
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+INSERT INTO t1 VALUES (1,'b1'), (2,'b2'), (3,'b3');
+BEGIN NOT ATOMIC
+DECLARE cur CURSOR FOR SELECT * FROM t1;
+FOR rec IN cur
+DO
+SELECT rec.a AS a, rec.b AS b;
+END FOR;
+END;
+$$
+a b
+1 b1
+a b
+2 b2
+a b
+3 b3
+DROP TABLE t1;
+# Explicit cursor with parameters
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+INSERT INTO t1 VALUES (1,'b1'), (2,'b2'), (3,'b3');
+BEGIN NOT ATOMIC
+DECLARE cur CURSOR(pa INT) FOR SELECT * FROM t1 WHERE a>=pa;
+FOR rec IN cur(2)
+DO
+SELECT rec.a AS a, rec.b AS b;
+END FOR;
+END;
+$$
+a b
+2 b2
+a b
+3 b3
+DROP TABLE t1;
+# Explicit cursor + label
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+INSERT INTO t1 VALUES ('1','b1'), ('2','b2');
+BEGIN NOT ATOMIC
+DECLARE cur CURSOR FOR SELECT * FROM t1;
+forrec:
+FOR rec IN cur
+DO
+SELECT rec.a AS a, rec.b AS b;
+IF rec.a = 2 THEN
+LEAVE forrec;
+END IF;
+END FOR forrec;
+END;
+$$
+a b
+1 b1
+a b
+2 b2
+DROP TABLE t1;
+# Explicit cursor + FETCH inside the loop body produce an error on "NOT FOUND"
+BEGIN NOT ATOMIC
+DECLARE x INT;
+DECLARE cur CURSOR FOR SELECT 1 AS x;
+FOR rec IN cur
+DO
+FETCH cur INTO x;
+END FOR;
+END;
+$$
+ERROR 02000: No data - zero rows fetched, selected, or processed
+# Explicit cursor + FETCH inside the loop body are normally handled by "HANDLER FOR NOT FOUND"
+BEGIN NOT ATOMIC
+DECLARE done INT DEFAULT 0;
+DECLARE cur CURSOR FOR SELECT 1 AS x, 'y1' AS y UNION
+SELECT 2,'y2' UNION
+SELECT 3,'y3';
+DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
+forrec:
+FOR rec IN cur
+DO
+SELECT CONCAT(rec.x, ' ', rec.y) AS 'Implicit FETCH';
+FETCH cur INTO rec;
+IF done THEN
+SELECT 'NO DATA' AS `Explicit FETCH`;
+LEAVE forrec;
+ELSE
+SELECT CONCAT(rec.x, ' ', rec.y) AS 'Explicit FETCH';
+END IF;
+END FOR;
+END;
+$$
+Implicit FETCH
+1 y1
+Explicit FETCH
+2 y2
+Implicit FETCH
+3 y3
+Explicit FETCH
+NO DATA
+# Implicit cursor
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+INSERT INTO t1 VALUES ('1','b1'), ('2','b2');
+BEGIN NOT ATOMIC
+FOR rec IN (SELECT * FROM t1)
+DO
+SELECT rec.a AS a, rec.b AS b;
+END FOR;
+END;
+$$
+a b
+1 b1
+a b
+2 b2
+DROP TABLE t1;
+# Implicit cursor + label
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+INSERT INTO t1 VALUES ('1','b1'), ('2','b2');
+BEGIN NOT ATOMIC
+forrec:
+FOR rec IN (SELECT * FROM t1)
+DO
+SELECT rec.a AS a, rec.b AS b;
+IF rec.a = 2 THEN
+LEAVE forrec;
+END IF;
+END FOR;
+END;
+$$
+a b
+1 b1
+a b
+2 b2
+DROP TABLE t1;
diff --git a/mysql-test/r/sp-for-loop.result b/mysql-test/r/sp-for-loop.result
new file mode 100644
index 00000000000..0da09586df5
--- /dev/null
+++ b/mysql-test/r/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;
diff --git a/mysql-test/t/sp-code.test b/mysql-test/t/sp-code.test
index 29b6764ebc6..1f2f5191f0a 100644
--- a/mysql-test/t/sp-code.test
+++ b/mysql-test/t/sp-code.test
@@ -758,3 +758,172 @@ DELIMITER ;$$
SHOW PROCEDURE CODE p1;
DROP PROCEDURE p1;
DROP TABLE t1;
+
+--echo #
+--echo # MDEV-14415 Add Oracle-style FOR loop to sql_mode=DEFAULT
+--echo #
+
+--echo # Integer range FOR loop
+
+DELIMITER $$;
+CREATE PROCEDURE p1()
+BEGIN
+ FOR i IN 1..3
+ DO
+ SELECT i;
+ END FOR;
+END;
+$$
+DELIMITER ;$$
+CALL p1;
+SHOW PROCEDURE CODE p1;
+DROP PROCEDURE p1;
+
+
+--echo # Nested integer range FOR loops
+
+DELIMITER $$;
+CREATE PROCEDURE p1()
+BEGIN
+ fori:
+ FOR i IN 1..3
+ DO
+ forj:
+ FOR j IN 1..3
+ DO
+ IF i = 3 THEN
+ LEAVE fori;
+ END IF;
+ IF j = 3 THEN
+ LEAVE forj;
+ END IF;
+ SELECT i,j;
+ END FOR;
+ END FOR;
+END;
+$$
+DELIMITER ;$$
+CALL p1;
+SHOW PROCEDURE CODE p1;
+DROP PROCEDURE p1;
+
+
+--echo # Explicit cursor FOR loops
+
+DELIMITER $$;
+CREATE PROCEDURE p1()
+BEGIN
+ DECLARE cur0 CURSOR FOR SELECT 10 AS a, 'b0' AS b;
+ DECLARE cur1 CURSOR FOR SELECT 10 AS a, 'b0' AS b;
+ DECLARE cur2 CURSOR FOR SELECT 10 AS a, 'b0' AS b;
+ FOR rec1 IN cur1
+ DO
+ SELECT rec1.a, rec1.b;
+ SET rec1.a= 11;
+ SET rec1.b= 'b1';
+ SELECT rec1.a, rec1.b;
+ END FOR;
+ FOR rec0 IN cur0
+ DO
+ SET rec0.a= 10;
+ SET rec0.b='b0';
+ END FOR;
+ FOR rec2 IN cur2
+ DO
+ SET rec2.a= 10;
+ SET rec2.b='b0';
+ END FOR;
+END;
+$$
+DELIMITER ;$$
+SHOW PROCEDURE CODE p1;
+DROP PROCEDURE p1;
+
+
+--echo # Nested explicit cursor FOR loops
+
+DELIMITER $$;
+CREATE PROCEDURE p1()
+BEGIN
+ DECLARE cur0 CURSOR FOR SELECT 10 AS a, 'b0' AS b;
+ FOR rec0 IN cur0
+ DO
+ BEGIN
+ DECLARE cur1 CURSOR FOR SELECT 11 AS a, 'b1' AS b;
+ SET rec0.a= 11;
+ SET rec0.b= 'b0';
+ FOR rec1 IN cur1
+ DO
+ SET rec1.a= 11;
+ SET rec1.b= 'b1';
+ BEGIN
+ DECLARE cur2 CURSOR FOR SELECT 12 AS a, 'b2' AS b;
+ FOR rec2 IN cur2
+ DO
+ SET rec2.a=12;
+ SET rec2.b='b2';
+ END FOR;
+ END;
+ END FOR;
+ END;
+ END FOR;
+END;
+$$
+DELIMITER ;$$
+SHOW PROCEDURE CODE p1;
+DROP PROCEDURE p1;
+
+
+--echo # Implicit cursor FOR loops
+
+DELIMITER $$;
+CREATE PROCEDURE p1()
+BEGIN
+ FOR rec1 IN (SELECT 11 AS a, 'b1' AS b)
+ DO
+ SELECT rec1.a, rec1.b;
+ SET rec1.a= 11;
+ SET rec1.b= 'b1';
+ SELECT rec1.a, rec1.b;
+ END FOR;
+ FOR rec0 IN (SELECT 10 AS a, 'b0' AS b)
+ DO
+ SET rec0.a= 10;
+ SET rec0.b='b0';
+ END FOR;
+ FOR rec2 IN (SELECT 12 AS a, 'b2' AS b)
+ DO
+ SET rec2.a= 10;
+ SET rec2.b='b0';
+ END FOR;
+END;
+$$
+DELIMITER ;$$
+SHOW PROCEDURE CODE p1;
+DROP PROCEDURE p1;
+
+--echo # Nested implicit cursor FOR loops
+
+DELIMITER $$;
+CREATE PROCEDURE p1()
+BEGIN
+ FOR rec0 IN (SELECT 10 AS a, 'b0' AS b)
+ DO
+ SET rec0.a= 11;
+ SET rec0.b= 'b0';
+ FOR rec1 IN (SELECT 11 AS a, 'b1' AS b)
+ DO
+ SET rec1.a= 11;
+ SET rec1.b= 'b1';
+ FOR rec2 IN (SELECT 12 AS a, 'b2' AS b)
+ DO
+ SET rec2.a=12;
+ SET rec2.b='b2';
+ END FOR;
+ END FOR;
+ END FOR;
+END;
+$$
+DELIMITER ;$$
+SHOW PROCEDURE CODE p1;
+DROP PROCEDURE p1;
diff --git a/mysql-test/t/sp-cursor.test b/mysql-test/t/sp-cursor.test
index 394dc56556a..2e7a72cf8d0 100644
--- a/mysql-test/t/sp-cursor.test
+++ b/mysql-test/t/sp-cursor.test
@@ -474,3 +474,136 @@ DROP PROCEDURE p1;
--echo #
--echo # End of MDEV-12457 Cursors with parameters
--echo #
+
+
+--echo #
+--echo # MDEV-14415 Add Oracle-style FOR loop to sql_mode=DEFAULT
+--echo #
+
+--echo # Explicit cursor
+
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+INSERT INTO t1 VALUES (1,'b1'), (2,'b2'), (3,'b3');
+DELIMITER $$;
+BEGIN NOT ATOMIC
+ DECLARE cur CURSOR FOR SELECT * FROM t1;
+ FOR rec IN cur
+ DO
+ SELECT rec.a AS a, rec.b AS b;
+ END FOR;
+END;
+$$
+DELIMITER ;$$
+DROP TABLE t1;
+
+--echo # Explicit cursor with parameters
+
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+INSERT INTO t1 VALUES (1,'b1'), (2,'b2'), (3,'b3');
+DELIMITER $$;
+BEGIN NOT ATOMIC
+ DECLARE cur CURSOR(pa INT) FOR SELECT * FROM t1 WHERE a>=pa;
+ FOR rec IN cur(2)
+ DO
+ SELECT rec.a AS a, rec.b AS b;
+ END FOR;
+END;
+$$
+DELIMITER ;$$
+DROP TABLE t1;
+
+--echo # Explicit cursor + label
+
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+INSERT INTO t1 VALUES ('1','b1'), ('2','b2');
+DELIMITER $$;
+BEGIN NOT ATOMIC
+ DECLARE cur CURSOR FOR SELECT * FROM t1;
+ forrec:
+ FOR rec IN cur
+ DO
+ SELECT rec.a AS a, rec.b AS b;
+ IF rec.a = 2 THEN
+ LEAVE forrec;
+ END IF;
+ END FOR forrec;
+END;
+$$
+DELIMITER ;$$
+DROP TABLE t1;
+
+--echo # Explicit cursor + FETCH inside the loop body produce an error on "NOT FOUND"
+
+DELIMITER $$;
+--error ER_SP_FETCH_NO_DATA
+BEGIN NOT ATOMIC
+ DECLARE x INT;
+ DECLARE cur CURSOR FOR SELECT 1 AS x;
+ FOR rec IN cur
+ DO
+ FETCH cur INTO x;
+ END FOR;
+END;
+$$
+DELIMITER ;$$
+
+
+--echo # Explicit cursor + FETCH inside the loop body are normally handled by "HANDLER FOR NOT FOUND"
+
+DELIMITER $$;
+BEGIN NOT ATOMIC
+ DECLARE done INT DEFAULT 0;
+ DECLARE cur CURSOR FOR SELECT 1 AS x, 'y1' AS y UNION
+ SELECT 2,'y2' UNION
+ SELECT 3,'y3';
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
+ forrec:
+ FOR rec IN cur
+ DO
+ SELECT CONCAT(rec.x, ' ', rec.y) AS 'Implicit FETCH';
+ FETCH cur INTO rec;
+ IF done THEN
+ SELECT 'NO DATA' AS `Explicit FETCH`;
+ LEAVE forrec;
+ ELSE
+ SELECT CONCAT(rec.x, ' ', rec.y) AS 'Explicit FETCH';
+ END IF;
+ END FOR;
+END;
+$$
+DELIMITER ;$$
+
+
+--echo # Implicit cursor
+
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+INSERT INTO t1 VALUES ('1','b1'), ('2','b2');
+DELIMITER $$;
+BEGIN NOT ATOMIC
+ FOR rec IN (SELECT * FROM t1)
+ DO
+ SELECT rec.a AS a, rec.b AS b;
+ END FOR;
+END;
+$$
+DELIMITER ;$$
+DROP TABLE t1;
+
+--echo # Implicit cursor + label
+
+CREATE TABLE t1 (a INT, b VARCHAR(10));
+INSERT INTO t1 VALUES ('1','b1'), ('2','b2');
+DELIMITER $$;
+BEGIN NOT ATOMIC
+ forrec:
+ FOR rec IN (SELECT * FROM t1)
+ DO
+ SELECT rec.a AS a, rec.b AS b;
+ IF rec.a = 2 THEN
+ LEAVE forrec;
+ END IF;
+ END FOR;
+END;
+$$
+DELIMITER ;$$
+DROP TABLE t1;
diff --git a/mysql-test/t/sp-for-loop.test b/mysql-test/t/sp-for-loop.test
new file mode 100644
index 00000000000..6350e9fb9d3
--- /dev/null
+++ b/mysql-test/t/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;
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index 0649ea2a4a3..7e7ad300e7c 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -4130,7 +4130,7 @@ sp_instr_cfetch::execute(THD *thd, uint *nextp)
Query_arena backup_arena;
DBUG_ENTER("sp_instr_cfetch::execute");
- res= c ? c->fetch(thd, &m_varlist) : -1;
+ res= c ? c->fetch(thd, &m_varlist, m_error_on_no_data) : -1;
*nextp= m_ip+1;
DBUG_RETURN(res);
@@ -4730,7 +4730,7 @@ bool sp_head::add_for_loop_open_cursor(THD *thd, sp_pcontext *spcont,
sp_instr_cfetch *instr_cfetch=
new (thd->mem_root) sp_instr_cfetch(instructions(),
- spcont, coffset);
+ spcont, coffset, false);
if (instr_cfetch == NULL || add_instr(instr_cfetch))
return true;
instr_cfetch->add_to_varlist(index);
diff --git a/sql/sp_head.h b/sql/sp_head.h
index ac0f406f854..f8a819bbe94 100644
--- a/sql/sp_head.h
+++ b/sql/sp_head.h
@@ -1796,8 +1796,8 @@ class sp_instr_cfetch : public sp_instr
public:
- sp_instr_cfetch(uint ip, sp_pcontext *ctx, uint c)
- : sp_instr(ip, ctx), m_cursor(c)
+ sp_instr_cfetch(uint ip, sp_pcontext *ctx, uint c, bool error_on_no_data)
+ : sp_instr(ip, ctx), m_cursor(c), m_error_on_no_data(error_on_no_data)
{
m_varlist.empty();
}
@@ -1818,6 +1818,7 @@ private:
uint m_cursor;
List<sp_variable> m_varlist;
+ bool m_error_on_no_data;
}; // class sp_instr_cfetch : public sp_instr
diff --git a/sql/sp_rcontext.cc b/sql/sp_rcontext.cc
index b5aacb1ac0f..d82fffc69b0 100644
--- a/sql/sp_rcontext.cc
+++ b/sql/sp_rcontext.cc
@@ -837,7 +837,7 @@ void sp_cursor::destroy()
}
-int sp_cursor::fetch(THD *thd, List<sp_variable> *vars)
+int sp_cursor::fetch(THD *thd, List<sp_variable> *vars, bool error_on_no_data)
{
if (! server_side_cursor)
{
@@ -874,7 +874,7 @@ int sp_cursor::fetch(THD *thd, List<sp_variable> *vars)
if (! server_side_cursor->is_open())
{
m_found= false;
- if (thd->variables.sql_mode & MODE_ORACLE)
+ if (!error_on_no_data)
return 0;
my_message(ER_SP_FETCH_NO_DATA, ER_THD(thd, ER_SP_FETCH_NO_DATA), MYF(0));
return -1;
diff --git a/sql/sp_rcontext.h b/sql/sp_rcontext.h
index 5e5bca62982..26c06512417 100644
--- a/sql/sp_rcontext.h
+++ b/sql/sp_rcontext.h
@@ -456,7 +456,7 @@ public:
ulonglong fetch_count() const
{ return m_fetch_count; }
- int fetch(THD *, List<sp_variable> *vars);
+ int fetch(THD *, List<sp_variable> *vars, bool error_on_no_data);
bool export_structure(THD *thd, Row_definition_list *list);
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 049004e0e16..fa65589135d 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -5580,6 +5580,35 @@ sp_variable *LEX::sp_add_for_loop_variable(THD *thd, const LEX_CSTRING *name,
}
+bool LEX::sp_for_loop_implicit_cursor_statement(THD *thd,
+ Lex_for_loop_bounds_st *bounds,
+ sp_lex_cursor *cur)
+{
+ Item *item;
+ DBUG_ASSERT(sphead);
+ LEX_CSTRING name= {STRING_WITH_LEN("[implicit_cursor]") };
+ if (sp_declare_cursor(thd, &name, cur, NULL, true))
+ return true;
+ DBUG_ASSERT(thd->lex == this);
+ if (!(bounds->m_index= new (thd->mem_root) sp_assignment_lex(thd, this)))
+ return true;
+ bounds->m_index->sp_lex_in_use= true;
+ sphead->reset_lex(thd, bounds->m_index);
+ DBUG_ASSERT(thd->lex != this);
+ if (!(item= new (thd->mem_root) Item_field(thd,
+ thd->lex->current_context(),
+ NullS, NullS, &name)))
+ return true;
+ bounds->m_index->set_item_and_free_list(item, NULL);
+ if (thd->lex->sphead->restore_lex(thd))
+ return true;
+ DBUG_ASSERT(thd->lex == this);
+ bounds->m_direction= 1;
+ bounds->m_upper_bound= NULL;
+ bounds->m_implicit_cursor= true;
+ return false;
+}
+
sp_variable *
LEX::sp_add_for_loop_cursor_variable(THD *thd,
const LEX_CSTRING *name,
@@ -5791,7 +5820,7 @@ bool LEX::sp_for_loop_cursor_finalize(THD *thd, const Lex_for_loop_st &loop)
{
sp_instr_cfetch *instr=
new (thd->mem_root) sp_instr_cfetch(sphead->instructions(),
- spcont, loop.m_cursor_offset);
+ spcont, loop.m_cursor_offset, false);
if (instr == NULL || sphead->add_instr(instr))
return true;
instr->add_to_varlist(loop.m_index);
@@ -7239,7 +7268,8 @@ bool LEX::sp_add_cfetch(THD *thd, const LEX_CSTRING *name)
return true;
}
i= new (thd->mem_root)
- sp_instr_cfetch(sphead->instructions(), spcont, offset);
+ sp_instr_cfetch(sphead->instructions(), spcont, offset,
+ !(thd->variables.sql_mode & MODE_ORACLE));
if (i == NULL || sphead->add_instr(i))
return true;
return false;
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 9caf1b905ca..c360fe4e3b1 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -3472,6 +3472,9 @@ public:
uint coffset,
sp_assignment_lex *param_lex,
Item_args *parameters);
+ bool sp_for_loop_implicit_cursor_statement(THD *thd,
+ Lex_for_loop_bounds_st *bounds,
+ sp_lex_cursor *cur);
bool sp_for_loop_cursor_condition_test(THD *thd, const Lex_for_loop_st &loop);
bool sp_for_loop_cursor_finalize(THD *thd, const Lex_for_loop_st &);
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index bf9e1ba660e..6f94afae3de 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -866,10 +866,10 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
%parse-param { THD *thd }
%lex-param { THD *thd }
/*
- Currently there are 102 shift/reduce conflicts.
+ Currently there are 104 shift/reduce conflicts.
We should not introduce new conflicts any more.
*/
-%expect 102
+%expect 104
/*
Comments for TOKENS.
@@ -1614,11 +1614,15 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
LEX_HOSTNAME ULONGLONG_NUM field_ident select_alias ident_or_text
IDENT_sys TEXT_STRING_sys TEXT_STRING_literal
opt_component key_cache_name
- sp_opt_label BIN_NUM label_ident TEXT_STRING_filesystem ident_or_empty
+ sp_opt_label BIN_NUM TEXT_STRING_filesystem ident_or_empty
opt_constraint constraint opt_ident
sp_decl_ident
sp_block_label opt_place opt_db
+%type <lex_str>
+ label_ident
+ sp_label
+
%type <lex_string_with_metadata>
TEXT_STRING
NCHAR_STRING
@@ -1755,6 +1759,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
%type <assignment_lex>
assignment_source_lex
assignment_source_expr
+ for_loop_bound_expr
%type <sp_assignment_lex_list>
cursor_actual_parameters
@@ -1927,6 +1932,9 @@ END_OF_INPUT
%type <spblock> sp_decls sp_decl sp_decl_body sp_decl_variable_list
%type <spname> sp_name
%type <spvar> sp_param_name sp_param_name_and_type
+%type <for_loop> sp_for_loop_index_and_bounds
+%type <for_loop_bounds> sp_for_loop_bounds
+%type <num> opt_sp_for_loop_direction
%type <spvar_mode> sp_opt_inout
%type <index_hint> index_hint_type
%type <num> index_hint_clause normal_join inner_join
@@ -3923,6 +3931,22 @@ assignment_source_expr:
}
;
+for_loop_bound_expr:
+ assignment_source_lex
+ {
+ Lex->sphead->reset_lex(thd, $1);
+ }
+ expr
+ {
+ DBUG_ASSERT($1 == thd->lex);
+ $$= $1;
+ $$->sp_lex_in_use= true;
+ $$->set_item_and_free_list($3, NULL);
+ if ($$->sphead->restore_lex(thd))
+ MYSQL_YYABORT;
+ }
+ ;
+
cursor_actual_parameters:
assignment_source_expr
{
@@ -4222,13 +4246,17 @@ else_clause_opt:
| ELSE sp_proc_stmts1
;
+sp_label:
+ label_ident ':' { $$= $1; }
+ ;
+
sp_opt_label:
/* Empty */ { $$= null_clex_str; }
| label_ident { $$= $1; }
;
sp_block_label:
- label_ident ':'
+ sp_label
{
if (Lex->spcont->block_label_declare(&$1))
MYSQL_YYABORT;
@@ -4282,6 +4310,43 @@ sp_unlabeled_block_not_atomic:
}
;
+/* This adds one shift/reduce conflict */
+opt_sp_for_loop_direction:
+ /* Empty */ { $$= 1; }
+ | REVERSE_SYM { $$= -1; }
+ ;
+
+sp_for_loop_index_and_bounds:
+ ident sp_for_loop_bounds
+ {
+ if (Lex->sp_for_loop_declarations(thd, &$$, &$1, $2))
+ MYSQL_YYABORT;
+ }
+ ;
+
+sp_for_loop_bounds:
+ IN_SYM opt_sp_for_loop_direction for_loop_bound_expr
+ DOT_DOT_SYM for_loop_bound_expr
+ {
+ $$.m_direction= $2;
+ $$.m_index= $3;
+ $$.m_upper_bound= $5;
+ $$.m_implicit_cursor= false;
+ }
+ | IN_SYM opt_sp_for_loop_direction for_loop_bound_expr
+ {
+ $$.m_direction= $2;
+ $$.m_index= $3;
+ $$.m_upper_bound= NULL;
+ $$.m_implicit_cursor= false;
+ }
+ | IN_SYM opt_sp_for_loop_direction '(' sp_cursor_stmt ')'
+ {
+ if (Lex->sp_for_loop_implicit_cursor_statement(thd, &$$, $4))
+ MYSQL_YYABORT;
+ }
+ ;
+
loop_body:
sp_proc_stmts1 END LOOP_SYM
{
@@ -4341,14 +4406,14 @@ pop_sp_loop_label:
;
sp_labeled_control:
- label_ident ':' LOOP_SYM
+ sp_label LOOP_SYM
{
if (Lex->sp_push_loop_label(thd, &$1))
MYSQL_YYABORT;
}
loop_body pop_sp_loop_label
{ }
- | label_ident ':' WHILE_SYM
+ | sp_label WHILE_SYM
{
if (Lex->sp_push_loop_label(thd, &$1))
MYSQL_YYABORT;
@@ -4356,7 +4421,33 @@ sp_labeled_control:
}
while_body pop_sp_loop_label
{ }
- | label_ident ':' REPEAT_SYM
+ | sp_label FOR_SYM
+ {
+ // See "The FOR LOOP statement" comments in sql_lex.cc
+ Lex->sp_block_init(thd); // The outer DECLARE..BEGIN..END block
+ }
+ sp_for_loop_index_and_bounds
+ {
+ if (Lex->sp_push_loop_label(thd, &$1)) // The inner WHILE block
+ MYSQL_YYABORT;
+ if (Lex->sp_for_loop_condition_test(thd, $4))
+ MYSQL_YYABORT;
+ }
+ DO_SYM
+ sp_proc_stmts1
+ END FOR_SYM
+ {
+ if (Lex->sp_for_loop_finalize(thd, $4))
+ MYSQL_YYABORT;
+ }
+ pop_sp_loop_label // The inner WHILE block
+ {
+ Lex_spblock tmp;
+ tmp.curs= MY_TEST($4.m_implicit_cursor);
+ if (Lex->sp_block_finalize(thd, tmp)) // The outer DECLARE..BEGIN..END
+ MYSQL_YYABORT;
+ }
+ | sp_label REPEAT_SYM
{
if (Lex->sp_push_loop_label(thd, &$1))
MYSQL_YYABORT;
@@ -4385,6 +4476,32 @@ sp_unlabeled_control:
{
Lex->sp_pop_loop_empty_label(thd);
}
+ | FOR_SYM
+ {
+ // See "The FOR LOOP statement" comments in sql_lex.cc
+ if (Lex->maybe_start_compound_statement(thd))
+ MYSQL_YYABORT;
+ Lex->sp_block_init(thd); // The outer DECLARE..BEGIN..END block
+ }
+ sp_for_loop_index_and_bounds
+ {
+ if (Lex->sp_push_loop_empty_label(thd)) // The inner WHILE block
+ MYSQL_YYABORT;
+ if (Lex->sp_for_loop_condition_test(thd, $3))
+ MYSQL_YYABORT;
+ }
+ DO_SYM
+ sp_proc_stmts1
+ END FOR_SYM
+ {
+ Lex_spblock tmp;
+ tmp.curs= MY_TEST($3.m_implicit_cursor);
+ if (Lex->sp_for_loop_finalize(thd, $3))
+ MYSQL_YYABORT;
+ Lex->sp_pop_loop_empty_label(thd); // The inner WHILE block
+ if (Lex->sp_block_finalize(thd, tmp)) // The outer DECLARE..BEGIN..END
+ MYSQL_YYABORT;
+ }
| REPEAT_SYM
{
if (Lex->sp_push_loop_empty_label(thd))
diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy
index 30c4b6a0f7c..9382ac60709 100644
--- a/sql/sql_yacc_ora.yy
+++ b/sql/sql_yacc_ora.yy
@@ -1023,13 +1023,17 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
LEX_HOSTNAME ULONGLONG_NUM field_ident select_alias ident_or_text
IDENT_sys TEXT_STRING_sys TEXT_STRING_literal
opt_component key_cache_name
- sp_opt_label BIN_NUM label_ident TEXT_STRING_filesystem ident_or_empty
+ sp_opt_label BIN_NUM TEXT_STRING_filesystem ident_or_empty
opt_constraint constraint opt_ident
- label_declaration_oracle labels_declaration_oracle
ident_directly_assignable
sp_decl_ident
sp_block_label opt_place opt_db
+%type <lex_str>
+ label_ident
+ label_declaration_oracle
+ labels_declaration_oracle
+
%type <lex_string_with_metadata>
TEXT_STRING
NCHAR_STRING
@@ -4080,25 +4084,8 @@ sp_for_loop_bounds:
}
| IN_SYM opt_sp_for_loop_direction '(' sp_cursor_stmt ')'
{
- Item *item;
- DBUG_ASSERT(Lex->sphead);
- LEX_CSTRING name= {STRING_WITH_LEN("[implicit_cursor]") };
- if (Lex->sp_declare_cursor(thd, &name, $4, NULL, true))
- MYSQL_YYABORT;
- if (!($$.m_index= new (thd->mem_root) sp_assignment_lex(thd, thd->lex)))
+ if (Lex->sp_for_loop_implicit_cursor_statement(thd, &$$, $4))
MYSQL_YYABORT;
- $$.m_index->sp_lex_in_use= true;
- Lex->sphead->reset_lex(thd, $$.m_index);
- if (!(item= new (thd->mem_root) Item_field(thd,
- Lex->current_context(),
- NullS, NullS, &name)))
- MYSQL_YYABORT;
- $$.m_index->set_item_and_free_list(item, NULL);
- if (Lex->sphead->restore_lex(thd))
- MYSQL_YYABORT;
- $$.m_direction= 1;
- $$.m_upper_bound= NULL;
- $$.m_implicit_cursor= true;
}
;
View Lorry Controller Status