mysql-test/main/sp-cursor.test

--echo #
--echo # MDEV-12457 Cursors with parameters
--echo #

CREATE TABLE t1 (a INT, b VARCHAR(10)) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1,'old'),(2,'old'),(3,'old'),(4,'old'),(5,'old');
DELIMITER $$;
CREATE PROCEDURE p1(min INT,max INT)
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE va INT;
  DECLARE cur CURSOR(pmin INT, pmax INT) FOR SELECT a FROM t1 WHERE a BETWEEN pmin AND pmax;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
  OPEN cur(min,max);
  read_loop: LOOP
    FETCH cur INTO va;
    IF done THEN
      LEAVE read_loop;
    END IF;
    INSERT INTO t1 VALUES (va,'new');
  END LOOP;
  CLOSE cur;
END;
$$
DELIMITER ;$$
CALL p1(2,4);
SELECT * FROM t1 ORDER BY b DESC,a;
DROP PROCEDURE p1;
DROP TABLE t1;


--echo #
--echo # OPEN with a wrong number of parameters
--echo #

CREATE TABLE t1 (a INT, b VARCHAR(10));
DELIMITER $$;
--error ER_WRONG_PARAMCOUNT_TO_CURSOR
CREATE PROCEDURE p1(a_a INT,a_b VARCHAR(32))
BEGIN
  DECLARE v_a INT;
  DECLARE v_b VARCHAR(10);
  DECLARE c CURSOR (p_a INT, p_b VARCHAR(32)) FOR SELECT * FROM t1 WHERE a=p_a;
  OPEN c(a_a);
  CLOSE c;
END;
$$
DELIMITER ;$$
DROP TABLE t1;


--echo #
--echo # Cursor parameters are not visible outside of the cursor
--echo #

DELIMITER $$;
--error ER_UNKNOWN_SYSTEM_VARIABLE
CREATE PROCEDURE p1(a_a INT)
BEGIN
  DECLARE v_a INT;
  DECLARE c CURSOR (p_a INT) FOR SELECT a FROM t1 WHERE a=p_a;
  OPEN c(a_a);
  SET p_a=10;
END;
$$
DELIMITER ;$$

DELIMITER $$;
--error ER_UNKNOWN_SYSTEM_VARIABLE
CREATE PROCEDURE p1(a_a INT)
BEGIN
  DECLARE v_a INT;
  DECLARE c CURSOR (p_a INT) FOR SELECT a FROM t1 WHERE a=p_a;
  SET p_a= 10;
  OPEN c(a_a);
END;
$$
DELIMITER ;$$


--echo #
--echo # Cursor parameter shadowing a local variable
--echo #

CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1);
DELIMITER $$;
CREATE PROCEDURE p1(a INT)
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE v_a INT DEFAULT NULL;
  DECLARE p_a INT DEFAULT NULL;
  DECLARE c CURSOR (p_a VARCHAR(32)) FOR SELECT p_a FROM t1;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
  OPEN c(a);
  read_loop: LOOP
    FETCH c INTO v_a;
    IF done THEN
      LEAVE read_loop;
    END IF;
    SELECT v_a;
  END LOOP;
  CLOSE c;
END;
$$
DELIMITER ;$$
CALL p1(1);
CALL p1(NULL);
DROP PROCEDURE p1;
DROP TABLE t1;


--echo #
--echo # Parameters in SELECT list
--echo #

DELIMITER $$;
CREATE PROCEDURE p1(a_a INT, a_b VARCHAR(32))
BEGIN
  DECLARE v_a INT;
  DECLARE v_b VARCHAR(10);
  DECLARE c CURSOR (p_a INT, p_b VARCHAR(32)) FOR SELECT p_a,p_b FROM DUAL;
  OPEN c(a_a + 0,a_b);
  FETCH c INTO v_a, v_b;
  SELECT v_a, v_b;
  CLOSE c;
  OPEN c(a_a + 1,a_b);
  FETCH c INTO v_a, v_b;
  SELECT v_a, v_b;
  CLOSE c;
END;
$$
DELIMITER ;$$
CALL p1(1,'b1');
DROP PROCEDURE p1;


--echo #
--echo # Parameters in SELECT list + UNION
--echo #

DELIMITER $$;
CREATE PROCEDURE p1(a_a INT, a_b VARCHAR(32))
BEGIN
  DECLARE v_a INT;
  DECLARE v_b VARCHAR(10);
  DECLARE c CURSOR(p_a INT, p_b VARCHAR(32)) FOR
    SELECT p_a,p_b FROM DUAL
    UNION ALL
    SELECT p_a+1,CONCAT(p_b,'b') FROM DUAL;
  OPEN c(a_a,a_b);
  FETCH c INTO v_a, v_b;
  SELECT v_a, v_b;
  FETCH c INTO v_a, v_b;
  SELECT v_a, v_b;
  CLOSE c;
END;
$$
DELIMITER ;$$
CALL p1(1,'b1');
DROP PROCEDURE p1;


--echo #
--echo # Parameters in SELECT list + type conversion + warnings
--echo #

SET sql_mode='';
DELIMITER $$;
CREATE PROCEDURE p1(a_a VARCHAR(32))
BEGIN
  DECLARE v_a INT;
  DECLARE c CURSOR (p_a INT) FOR SELECT p_a FROM DUAL;
  OPEN c(a_a);
  FETCH c INTO v_a;
  SELECT v_a;
  CLOSE c;
END;
$$
DELIMITER ;$$
CALL p1('1b');
CALL p1('b1');
DROP PROCEDURE p1;
SET sql_mode=DEFAULT;


--echo #
--echo # One parameter in SELECT list + subselect
--echo #

DELIMITER $$;
CREATE PROCEDURE p1(a_a VARCHAR(32))
BEGIN
  DECLARE v_a VARCHAR(10);
  DECLARE c CURSOR (p_a VARCHAR(32)) FOR
  SELECT p_a FROM DUAL UNION SELECT REVERSE(p_a) FROM DUAL;
  OPEN c((SELECT a_a));
  FETCH c INTO v_a;
  SELECT v_a;
  FETCH c INTO v_a;
  SELECT v_a;
  CLOSE c;
END;
$$
DELIMITER ;$$
CALL p1('ab');
DROP PROCEDURE p1;


--echo #
--echo # Two parameters in SELECT list + subselect
--echo #

DELIMITER $$;
CREATE PROCEDURE p1()
BEGIN
  DECLARE v_a VARCHAR(32);
  DECLARE v_b VARCHAR(32);
  DECLARE c CURSOR (p_a VARCHAR(32), p_b VARCHAR(32)) FOR
    SELECT p_a, p_b FROM DUAL
      UNION
    SELECT p_b, p_a FROM DUAL;
  OPEN c((SELECT 'aaa'),(SELECT 'bbb'));
  FETCH c INTO v_a, v_b;
  SELECT v_a, v_b;
  FETCH c INTO v_a, v_b;
  SELECT v_a, v_b;
  CLOSE c;
END;
$$
DELIMITER ;$$
CALL p1();
DROP PROCEDURE p1;


--echo #
--echo # Two parameters in SELECT list + two parameters in WHERE + subselects
--echo #

DELIMITER $$;
CREATE PROCEDURE p1(a_a VARCHAR(32), a_b VARCHAR(32))
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE v_a VARCHAR(32);
  DECLARE v_b VARCHAR(32);
  DECLARE c CURSOR (value_a VARCHAR(32), value_b VARCHAR(32),
                    pattern_a VARCHAR(32), pattern_b VARCHAR(32)) FOR
    SELECT value_a, value_b FROM DUAL WHERE value_a LIKE pattern_a
      UNION
    SELECT value_b, value_a FROM DUAL WHERE value_b LIKE pattern_b;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
  OPEN c((SELECT 'aaa'),(SELECT 'bbb'),(SELECT a_a),(SELECT a_b));
  read_loop: LOOP
    FETCH c INTO v_a, v_b;
    IF done THEN
      LEAVE read_loop;
    END IF;
    SELECT v_a, v_b;
  END LOOP;
  CLOSE c;
END;
$$
DELIMITER ;$$
CALL p1('%','%');
CALL p1('aaa','xxx');
CALL p1('xxx','bbb');
CALL p1('xxx','xxx');
DROP PROCEDURE p1;


--echo #
--echo # Parameters in SELECT list + stored function
--echo #

DELIMITER $$;
CREATE FUNCTION f1 (a VARCHAR(32)) RETURNS VARCHAR(32)
BEGIN
  RETURN CONCAT(a,'y');
END;
$$
CREATE PROCEDURE p1(a_a VARCHAR(32))
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE v_a VARCHAR(10);
  DECLARE v_b VARCHAR(10);
  DECLARE c CURSOR (p_sel_a VARCHAR(32), p_cmp_a VARCHAR(32)) FOR
    SELECT p_sel_a, p_cmp_a FROM DUAL;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
  OPEN c(f1(a_a), f1(a_a));
  read_loop: LOOP
    FETCH c INTO v_a, v_b;
    IF done THEN
      LEAVE read_loop;
    END IF;
    SELECT v_a, v_b;
  END LOOP;
  CLOSE c;
END;
$$
DELIMITER ;$$
CALL p1('x');
# A complex expression
CALL p1(f1(COALESCE(NULL, f1('x'))));
DROP PROCEDURE p1;
DROP FUNCTION f1;


--echo #
--echo # One parameter in WHERE clause
--echo #

CREATE TABLE t1 (a INT, b VARCHAR(10));
CREATE TABLE t2 (a INT, b VARCHAR(10));
INSERT INTO t1 VALUES (1,'11');
INSERT INTO t1 VALUES (1,'12');
INSERT INTO t1 VALUES (2,'21');
INSERT INTO t1 VALUES (2,'22');
INSERT INTO t1 VALUES (3,'31');
INSERT INTO t1 VALUES (3,'32');
DELIMITER $$;
CREATE PROCEDURE p1(a_a INT)
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE v_a INT;
  DECLARE v_b VARCHAR(10);
  DECLARE c CURSOR (p_a INT) FOR SELECT a,b FROM t1 WHERE a=p_a;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
  OPEN c(a_a);
  read_loop: LOOP
    FETCH c INTO v_a, v_b;
    IF done THEN
      LEAVE read_loop;
    END IF;
    INSERT INTO t2 VALUES (v_a,v_b);
  END LOOP;
  CLOSE c;
END;
$$
DELIMITER ;$$
CALL p1(1);
SELECT * FROM t2;
DROP TABLE t1;
DROP TABLE t2;
DROP PROCEDURE p1;


--echo #
--echo # Two parameters in WHERE clause
--echo #

CREATE TABLE t1 (a INT, b VARCHAR(10));
CREATE TABLE t2 (a INT, b VARCHAR(10));
INSERT INTO t1 VALUES (1,'11');
INSERT INTO t1 VALUES (1,'12');
INSERT INTO t1 VALUES (2,'21');
INSERT INTO t1 VALUES (2,'22');
INSERT INTO t1 VALUES (3,'31');
INSERT INTO t1 VALUES (3,'32');
DELIMITER $$;
CREATE PROCEDURE p1(a_a INT, a_b VARCHAR(32))
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE v_a INT;
  DECLARE v_b VARCHAR(10);
  DECLARE c CURSOR (p_a INT, p_b VARCHAR(32)) FOR SELECT a,b FROM t1 WHERE a=p_a AND b=p_b;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
  OPEN c(a_a, a_b);
  read_loop: LOOP
    FETCH c INTO v_a, v_b;
    IF done THEN
      LEAVE read_loop;
    END IF;
    INSERT INTO t2 VALUES (v_a,v_b);
  END LOOP;
  CLOSE c;
END;
$$
DELIMITER ;$$
CALL p1(1,'11');
SELECT * FROM t2;
DROP TABLE t1;
DROP TABLE t2;
DROP PROCEDURE p1;

--echo #
--echo # Parameters in WHERE and HAVING clauses
--echo #
CREATE TABLE t1 (name VARCHAR(10), value INT);
INSERT INTO t1 VALUES ('but',1);
INSERT INTO t1 VALUES ('but',1);
INSERT INTO t1 VALUES ('but',1);
INSERT INTO t1 VALUES ('bin',1);
INSERT INTO t1 VALUES ('bin',1);
INSERT INTO t1 VALUES ('bot',1);
DELIMITER $$;
CREATE PROCEDURE p1 (arg_name_limit VARCHAR(32), arg_total_limit INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE v_name VARCHAR(10);
  DECLARE v_total INT;
-- +0 is needed to work around the bug MDEV-11081
  DECLARE c CURSOR(p_v INT) FOR
    SELECT name, SUM(value + p_v) + 0 AS total FROM t1
    WHERE name LIKE arg_name_limit
    GROUP BY name HAVING total>=arg_total_limit;
  WHILE i < 2 DO
    BEGIN
      DECLARE done INT DEFAULT 0;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
      OPEN c(i);
      read_loop: LOOP
        FETCH c INTO v_name, v_total;
        IF done THEN
          LEAVE read_loop;
        END IF;
        SELECT v_name, v_total;
      END LOOP;
      CLOSE c;
      SET i= i + 1;
    END;
  END WHILE;
END;
$$
DELIMITER ;$$
CALL p1('%', 2);
CALL p1('b_t', 0);
DROP PROCEDURE p1;
DROP TABLE t1;


--echo #
--echo # One parameter in LIMIT clause
--echo #

CREATE TABLE t1 (a INT, b VARCHAR(10));
INSERT INTO t1 VALUES (1,'b1');
INSERT INTO t1 VALUES (2,'b2');
INSERT INTO t1 VALUES (3,'b3');
INSERT INTO t1 VALUES (4,'b4');
INSERT INTO t1 VALUES (5,'b5');
INSERT INTO t1 VALUES (6,'b6');
DELIMITER $$;
CREATE PROCEDURE p1(a_a INT)
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE v_a INT;
  DECLARE v_b VARCHAR(10);
  DECLARE c CURSOR (p_a INT) FOR SELECT a,b FROM t1 ORDER BY a LIMIT p_a;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
  CREATE TABLE t2 (a INT, b VARCHAR(10));
  OPEN c(a_a);
  read_loop: LOOP
    FETCH c INTO v_a, v_b;
    IF done THEN
      LEAVE read_loop;
    END IF;
    INSERT INTO t2 VALUES (v_a,v_b);
  END LOOP;
  CLOSE c;
  SELECT * FROM t2;
  DROP TABLE t2;
END;
$$
DELIMITER ;$$
CALL p1(1);
CALL p1(3);
CALL p1(6);
DROP TABLE t1;
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;


--echo #
--echo # MDEV-15941 Explicit cursor FOR loop does not close the cursor
--echo #

DELIMITER $$;
--error ER_SP_CURSOR_NOT_OPEN
BEGIN NOT ATOMIC
  DECLARE v INT;
  DECLARE cur CURSOR FOR SELECT 1 AS a FROM DUAL;
  FOR rec IN cur
  DO
    SELECT rec.a;
  END FOR;
  FETCH cur INTO v;
END;
$$
DELIMITER ;$$


DELIMITER $$;
--error ER_SP_CURSOR_NOT_OPEN
BEGIN NOT ATOMIC
  DECLARE v INT;
  DECLARE cur CURSOR FOR SELECT 1 AS a FROM DUAL;
label:
  FOR rec IN cur
  DO
    SELECT rec.a;
  END FOR;
  FETCH cur INTO v;
END;
$$
DELIMITER ;$$


DELIMITER $$;
--error ER_SP_CURSOR_ALREADY_OPEN
BEGIN NOT ATOMIC
  DECLARE cur CURSOR FOR SELECT 1 AS a FROM DUAL;
  OPEN cur;
  FOR rec IN cur DO
    SELECT rec.a;
  END FOR;
END;
$$
DELIMITER ;$$


DELIMITER $$;
BEGIN NOT ATOMIC
  DECLARE cur CURSOR FOR SELECT 1 AS a FROM DUAL;
  FOR rec IN cur
  DO
    SELECT rec.a;
  END FOR;
  FOR rec IN cur
  DO
    SELECT rec.a;
  END FOR;
END;
$$
DELIMITER ;$$


DELIMITER $$;
BEGIN NOT ATOMIC
  DECLARE cur CURSOR FOR SELECT 1 AS a FROM DUAL;
label1:
  FOR rec IN cur
  DO
    SELECT rec.a;
  END FOR;
label2:
  FOR rec IN cur
  DO
    SELECT rec.a;
  END FOR;
END;
$$
DELIMITER ;$$


--echo #
--echo # MDEV-16584 SP with a cursor inside a loop wastes THD memory aggressively
--echo #

DELIMITER $$;
CREATE PROCEDURE p1()
BEGIN
  DECLARE mem_used_old BIGINT UNSIGNED DEFAULT
    (SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.SESSION_STATUS
     WHERE VARIABLE_NAME='MEMORY_USED');
  DECLARE i INT DEFAULT 1;
  WHILE i <= 5000
  DO
    BEGIN
      DECLARE msg TEXT;
      DECLARE mem_used_cur BIGINT UNSIGNED DEFAULT
        (SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.SESSION_STATUS
         WHERE VARIABLE_NAME='MEMORY_USED');
      DECLARE cur CURSOR FOR SELECT 1 FROM DUAL;
      IF (mem_used_cur >= mem_used_old * 2) THEN
        SHOW STATUS LIKE 'Memory_used';
        SET msg=CONCAT('Memory leak detected: i=', i, ' mem_used_old=',mem_used_old,' mem_used_cur=', mem_used_cur);
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT=msg;
      END IF;
    END;
    SET i=i+1;
  END WHILE;
END;
$$
DELIMITER ;$$
CALL p1;
DROP PROCEDURE p1;


--echo #
--echo # MDEV-17278 CURSOR FOR LOOP - ERROR: unexpected end of stream, read 0 bytes (SERVER CRASH)
--echo #

CREATE TABLE t1 (id2 int, id int, en1 enum('aaa','a','b','c'));
INSERT INTO t1 VALUES(1,1,'aaa'),(2,2,'a'),(3,3,'b'),(4,4,'c');
DELIMITER $$;
CREATE PROCEDURE p1()
BEGIN
  FOR rec IN (SELECT en1 FROM t1)
  DO
    SELECT rec.en1;
  END FOR;
END;
$$
DELIMITER ;$$
CALL p1();
DROP PROCEDURE p1;
DROP TABLE t1;