mirror of
https://github.com/MariaDB/server.git
synced 2025-11-03 20:36:16 +01:00
534a2bf1c6
An "ITERATE innerLoop" did not work properly inside
a WHILE loop, which itself is inside an outer FOR loop:
outerLoop:
FOR
...
innerLoop:
WHILE
...
ITERATE innerLoop;
...
END WHILE;
...
END FOR;
It erroneously generated an integer increment code for the outer FOR loop.
There were two problems:
1. "ITERATE innerLoop" worked like "ITERATE outerLoop"
2. It was always integer increment, even in case of FOR cursor loops.
Background:
- A FOR loop automatically creates a dedicated sp_pcontext stack entry,
to put the iteration and bound variables on it.
- Other loop types (LOOP, WHILE, REPEAT), do not generate a dedicated
slack entry.
The old code erroneously assumed that sp_pcontext::m_for_loop
either describes the most inner loop (in case the inner loop is FOR),
or is empty (in case the inner loop is not FOR).
But in fact, sp_pcontext::m_for_loop is never empty inside a FOR loop:
it describes the closest FOR loop, even if this FOR loop has nested
non-FOR loops inside.
So when we're near the ITERATE statement in the above script,
sp_pcontext::m_for_loop is not empty - it stores information about
the FOR loop labeled as "outrLoop:".
Fix:
- Adding a new member sp_pcontext::Lex_for_loop::m_start_label,
to remember the explicit or the auto-generated label correspoding
to the start of the FOR body. It's used during generation
of "ITERATE loop_label" code to check if "loop_label" belongs
to the current FOR loop pointed by sp_pcontext::m_for_loop,
or belongs to a non-FOR nested loop.
- Adding LEX methods sp_for_loop_intrange_iterate() and
sp_for_loop_cursor_iterate() to reuse the code between
methods handling:
* ITERATE
* END FOR
- Adding a test for Lex_for_loop::is_for_loop_cursor()
and generate a code either a cursor fetch, or for an integer increment.
Before this change, it always erroneously generated an integer increment
version.
- Cleanup: Initialize Lex_for_loop_st::m_cursor_offset inside
Lex_for_loop_st::init(), to avoid not initialized members.
- Cleanup: Removing a redundant method:
Lex_for_loop_st::init(const Lex_for_loop_st &other)
Using Lex_for_loop_st::operator(const Lex_for_loop_st &other) instead.
238 lines
3.9 KiB
Text
238 lines
3.9 KiB
Text
#
|
|
# 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;
|
|
#
|
|
# End of 10.3 tests
|
|
#
|
|
#
|
|
# Start of 10.4 tests
|
|
#
|
|
#
|
|
# MDEV-32275 getting error 'Illegal parameter data types row and bigint for operation '+' ' when using ITERATE in a FOR..DO
|
|
#
|
|
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;
|
|
$$
|
|
CALL forIterateBug;
|
|
DROP PROCEDURE forIterateBug;
|
|
#
|
|
# End of 10.4 tests
|
|
#
|