path: root/mysql-test/suite/compat/oracle/t/sp-cursor.test

SET sql_mode=ORACLE;

--echo #
--echo # MDEV-10582 sql_mode=ORACLE: explicit cursor attributes %ISOPEN, %ROWCOUNT, %FOUND, %NOTFOUND
--echo #


--echo #
--echo # Cursor attributes outside of an SP context
--echo #

--error ER_SP_CURSOR_MISMATCH
SELECT c%ISOPEN;
--error ER_SP_CURSOR_MISMATCH
SELECT c%FOUND;
--error ER_SP_CURSOR_MISMATCH
SELECT c%NOTFOUND;
--error ER_SP_CURSOR_MISMATCH
SELECT c%ROWCOUNT;


--echo #
--echo # Undefinite cursor attributes
--echo #

DELIMITER $$;
--error ER_SP_CURSOR_MISMATCH
CREATE PROCEDURE p1
AS
BEGIN
  SELECT c%ISOPEN;
END;
$$
--error ER_SP_CURSOR_MISMATCH
CREATE PROCEDURE p1
AS
BEGIN
  SELECT c%ROWCOUNT;
END;
$$
--error ER_SP_CURSOR_MISMATCH
CREATE PROCEDURE p1
AS
BEGIN
  SELECT c%FOUND;
END;
$$
--error ER_SP_CURSOR_MISMATCH
CREATE PROCEDURE p1
AS
BEGIN
  SELECT c%NOTFOUND;
END;
$$
DELIMITER ;$$


--echo #
--echo # Not opened cursor attributes %FOUND, %NOTFOUND, %ROWCOUNT
--echo #

DELIMITER $$;
CREATE PROCEDURE p1
AS
  CURSOR c IS SELECT 1 AS c FROM DUAL;
BEGIN
  SELECT c%ROWCOUNT;
END;
$$
DELIMITER ;$$
--error ER_SP_CURSOR_NOT_OPEN
CALL p1;
DROP PROCEDURE p1;

DELIMITER $$;
CREATE PROCEDURE p1
AS
  CURSOR c IS SELECT 1 AS c FROM DUAL;
BEGIN
  SELECT c%FOUND;
END;
$$
DELIMITER ;$$
--error ER_SP_CURSOR_NOT_OPEN
CALL p1;
DROP PROCEDURE p1;

DELIMITER $$;
CREATE PROCEDURE p1
AS
  CURSOR c IS SELECT 1 AS c FROM DUAL;
BEGIN
  SELECT c%NOTFOUND;
END;
$$
DELIMITER ;$$
--error ER_SP_CURSOR_NOT_OPEN
CALL p1;
DROP PROCEDURE p1;


--echo #
--echo # Not opened cursor attributes %FOUND, %NOTFOUND, %ROWCOUNT with INVALID_CURSOR exception
--echo #

DELIMITER $$;
CREATE PROCEDURE p1
AS
  CURSOR c IS SELECT 1 AS c FROM DUAL;
BEGIN
  SELECT c%ROWCOUNT;
EXCEPTION
  WHEN INVALID_CURSOR THEN SELECT 'INVALID_CURSOR caught' AS msg;
END;
$$
DELIMITER ;$$
CALL p1;
DROP PROCEDURE p1;

DELIMITER $$;
CREATE PROCEDURE p1
AS
  CURSOR c IS SELECT 1 AS c FROM DUAL;
BEGIN
  SELECT c%FOUND;
EXCEPTION
  WHEN INVALID_CURSOR THEN SELECT 'INVALID_CURSOR caught' AS msg;
END;
$$
DELIMITER ;$$
CALL p1;
DROP PROCEDURE p1;

DELIMITER $$;
CREATE PROCEDURE p1
AS
  CURSOR c IS SELECT 1 AS c FROM DUAL;
BEGIN
  SELECT c%NOTFOUND;
EXCEPTION
  WHEN INVALID_CURSOR THEN SELECT 'INVALID_CURSOR caught' AS msg;
END;
$$
DELIMITER ;$$
CALL p1;
DROP PROCEDURE p1;


--echo #
--echo # print()
--echo #

CREATE TABLE t1 (a INT);
DELIMITER $$;
CREATE PROCEDURE p1
AS
  CURSOR c IS SELECT * FROM t1 ORDER BY a;
BEGIN
  EXPLAIN EXTENDED SELECT c%ISOPEN, c%ROWCOUNT, c%FOUND, c%NOTFOUND;
END;
$$
DELIMITER ;$$
CALL p1();
DROP PROCEDURE p1;
DROP TABLE t1;


--echo #
--echo # Declared data type of the attributes
--echo #
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10);
DELIMITER $$;
CREATE PROCEDURE p1
AS
  CURSOR c IS SELECT * FROM t1 ORDER BY a;
BEGIN
  OPEN c;
  CREATE TABLE t2 AS SELECT c%ISOPEN, c%ROWCOUNT, c%FOUND, c%NOTFOUND;
  SHOW CREATE TABLE t2;
  DROP TABLE t2;
  CLOSE c;
END;
$$
DELIMITER ;$$
CALL p1();
DROP PROCEDURE p1;
DROP TABLE t1;


--echo #
--echo # Core functionality
--echo #

CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10);
INSERT INTO t1 VALUES (20);
INSERT INTO t1 VALUES (30);
DELIMITER $$;
CREATE PROCEDURE p1
AS
  a INT:=0;
  CURSOR c IS SELECT * FROM t1 ORDER BY a;
BEGIN
  SELECT a, c%ISOPEN;
  OPEN c;
  /*
    After OPEN and before FETCH:
    - %ROWCOUNT returns 0
    - %FOUND and %NOTFOUND return NULL
  */
  SELECT a, c%ISOPEN, c%ROWCOUNT, c%FOUND, c%NOTFOUND;
  FETCH c INTO a;
  SELECT a, c%ISOPEN, c%ROWCOUNT, c%FOUND, c%NOTFOUND;
  FETCH c INTO a;
  SELECT a, c%ISOPEN, c%ROWCOUNT, c%FOUND, c%NOTFOUND;
  FETCH c INTO a;
  SELECT a, c%ISOPEN, c%ROWCOUNT, c%FOUND, c%NOTFOUND;
  FETCH c INTO a;
  SELECT a, c%ISOPEN, c%ROWCOUNT, c%FOUND, c%NOTFOUND;
  CLOSE c;
  SELECT a, c%ISOPEN;
  /*
    After reopen and before FETCH:
    - %ROWCOUNT returns 0
    - %FOUND and %NOTFOUND return NULL
  */
  OPEN c;
  SELECT a, c%ISOPEN, c%ROWCOUNT, c%FOUND, c%NOTFOUND;
  FETCH c INTO a;
  SELECT a, c%ISOPEN, c%ROWCOUNT, c%FOUND, c%NOTFOUND;
  CLOSE c;
END;
$$
DELIMITER ;$$
CALL p1();
DROP PROCEDURE p1;
DROP TABLE t1;


--echo #
--echo # %NOTFOUND as a loop exit condition
--echo #

CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10);
INSERT INTO t1 VALUES (20);
INSERT INTO t1 VALUES (30);
DELIMITER $$;
CREATE PROCEDURE p1
AS
  a INT:=0;
  CURSOR c IS SELECT * FROM t1 ORDER BY a;
BEGIN
  OPEN c;
  LOOP
    FETCH c INTO a;
    EXIT WHEN c%NOTFOUND;
    SELECT a;
  END LOOP;
  CLOSE c;
END;
$$
DELIMITER ;$$
CALL p1();
DROP PROCEDURE p1;
DROP TABLE t1;


--echo #
--echo # %FOUND as a loop exit condition
--echo #

CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10);
INSERT INTO t1 VALUES (20);
INSERT INTO t1 VALUES (30);
DELIMITER $$;
CREATE PROCEDURE p1
AS
  a INT:=0;
  CURSOR c IS SELECT * FROM t1 ORDER BY a;
BEGIN
  OPEN c;
  LOOP
    FETCH c INTO a;
    EXIT WHEN NOT c%FOUND;
    SELECT a;
  END LOOP;
  CLOSE c;
END;
$$
DELIMITER ;$$
CALL p1();
DROP PROCEDURE p1;
DROP TABLE t1;

--echo #
--echo # End of MDEV-10582 sql_mode=ORACLE: explicit cursor attributes %ISOPEN, %ROWCOUNT, %FOUND, %NOTFOUND
--echo #

--echo #
--echo # MDEV-10597 Cursors with parameters
--echo #

--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)
AS
  v_a INT;
  v_b VARCHAR(10);
  CURSOR c (p_a INT, p_b VARCHAR) IS SELECT * FROM t1 WHERE a=p_a;
BEGIN
  OPEN c(a_a);
  LOOP
    FETCH c INTO v_a, v_b;
    EXIT WHEN c%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Fetched a record a='||TO_CHAR(v_a)||' b='||v_b);
  END LOOP;
  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)
AS
  v_a INT;
  CURSOR c (p_a INT) IS SELECT a FROM t1 WHERE a=p_a;
BEGIN
  OPEN c(a_a);
  p_a:=10;
END;
$$
DELIMITER ;$$

DELIMITER $$;
--error ER_UNKNOWN_SYSTEM_VARIABLE
CREATE PROCEDURE p1(a_a INT)
AS
  v_a INT;
  CURSOR c (p_a INT) IS SELECT a FROM t1 WHERE a=p_a;
BEGIN
  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)
AS
  v_a INT:=NULL;
  p_a INT:=NULL;
  CURSOR c (p_a VARCHAR2) IS SELECT a FROM t1 WHERE p_a IS NOT NULL;
BEGIN
  OPEN c(a);
  FETCH c INTO v_a;
  IF c%NOTFOUND THEN
    BEGIN
      SELECT 'No records found' AS msg;
      RETURN;
    END;
  END IF;
  CLOSE c;
  SELECT 'Fetched a record a='||v_a AS msg;
  INSERT INTO t1 VALUES (v_a);
END;
$$
DELIMITER ;$$
CALL p1(1);
SELECT * FROM t1;
CALL p1(NULL);
SELECT * FROM t1;
DROP PROCEDURE p1;
DROP TABLE t1;


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

DELIMITER $$;
CREATE PROCEDURE p1(a_a INT, a_b VARCHAR)
AS
  v_a INT;
  v_b VARCHAR(10);
  CURSOR c (p_a INT, p_b VARCHAR) IS SELECT p_a,p_b FROM DUAL;
BEGIN
  FOR i IN 0..1
  LOOP
    OPEN c(a_a + i,a_b);
    LOOP
      FETCH c INTO v_a, v_b;
      EXIT WHEN c%NOTFOUND;
      SELECT 'Fetched a record a=' || v_a || ' b=' || v_b AS msg;
    END LOOP;
    CLOSE c;
  END LOOP;
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)
AS
  v_a INT;
  v_b VARCHAR(10);
  CURSOR c (p_a INT, p_b VARCHAR) IS
    SELECT p_a,p_b FROM DUAL
    UNION ALL
    SELECT p_a+1,p_b||'b' FROM DUAL;
BEGIN
  OPEN c(a_a,a_b);
  LOOP
    FETCH c INTO v_a, v_b;
    EXIT WHEN c%NOTFOUND;
    SELECT 'Fetched a record a=' || v_a || ' b=' || v_b AS msg;
  END LOOP;
  CLOSE c;
END;
$$
DELIMITER ;$$
CALL p1(1,'b1');
DROP PROCEDURE p1;


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

DELIMITER $$;
CREATE PROCEDURE p1(a_a VARCHAR)
AS
  v_a INT;
  CURSOR c (p_a INT) IS SELECT p_a FROM DUAL;
BEGIN
  OPEN c(a_a);
  LOOP
    FETCH c INTO v_a;
    EXIT WHEN c%NOTFOUND;
    SELECT 'Fetched a record a=' || v_a AS msg;
  END LOOP;
  CLOSE c;
END;
$$
DELIMITER ;$$
CALL p1('1b');
CALL p1('b1');
DROP PROCEDURE p1;


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

DELIMITER $$;
CREATE PROCEDURE p1(a_a VARCHAR)
AS
  v_a VARCHAR(10);
  CURSOR c (p_a VARCHAR) IS
  SELECT p_a FROM DUAL UNION SELECT REVERSE(p_a) FROM DUAL;
BEGIN
  OPEN c((SELECT a_a));
  LOOP
    FETCH c INTO v_a;
    EXIT WHEN c%NOTFOUND;
    SELECT v_a;
  END LOOP;
  CLOSE c;
END;
$$
DELIMITER ;$$
CALL p1('ab');
DROP PROCEDURE p1;


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

SET sql_mode=ORACLE;
DELIMITER $$;
CREATE PROCEDURE p1()
AS
  v_a VARCHAR(10);
  v_b VARCHAR(20);
  CURSOR c (p_a VARCHAR, p_b VARCHAR) IS
    SELECT p_a, p_b FROM DUAL
      UNION
    SELECT p_b, p_a FROM DUAL;
BEGIN
  OPEN c((SELECT 'aaa'),(SELECT 'bbb'));
  LOOP
    FETCH c INTO v_a, v_b;
    EXIT WHEN c%NOTFOUND;
    SELECT v_a, v_b;
  END LOOP;
  CLOSE c;
END;
$$
DELIMITER ;$$
CALL p1();
DROP PROCEDURE p1;


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

SET sql_mode=ORACLE;
DELIMITER $$;
CREATE PROCEDURE p1(a_a VARCHAR, a_b VARCHAR)
AS
  v_a VARCHAR(10);
  v_b VARCHAR(20);
  CURSOR c (value_a VARCHAR, value_b VARCHAR,
            pattern_a VARCHAR, pattern_b VARCHAR) IS
    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;
BEGIN
  OPEN c((SELECT 'aaa'),(SELECT 'bbb'),(SELECT a_a),(SELECT a_b));
  LOOP
    FETCH c INTO v_a, v_b;
    EXIT WHEN c%NOTFOUND;
    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) RETURN VARCHAR
AS
BEGIN
  RETURN a || 'y';
END;
$$
CREATE PROCEDURE p1(a_a VARCHAR)
AS
  v_a VARCHAR(10);
  v_b VARCHAR(10);
  CURSOR c (p_sel_a VARCHAR, p_cmp_a VARCHAR) IS
    SELECT p_sel_a, p_cmp_a FROM DUAL;
BEGIN
  OPEN c(f1(a_a), f1(a_a));
  LOOP
    FETCH c INTO v_a, v_b;
    EXIT WHEN c%NOTFOUND;
    SELECT v_a;
  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)
AS
  v_a INT;
  v_b VARCHAR(10);
  CURSOR c (p_a INT) IS SELECT a,b FROM t1 WHERE a=p_a;
BEGIN
  OPEN c(a_a);
  LOOP
    FETCH c INTO v_a, v_b;
    EXIT WHEN c%NOTFOUND;
    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)
AS
  v_a INT;
  v_b VARCHAR(10);
  CURSOR c (p_a INT, p_b VARCHAR) IS SELECT a,b FROM t1 WHERE a=p_a AND b=p_b;
BEGIN
  OPEN c(a_a, a_b);
  LOOP
    FETCH c INTO v_a, v_b;
    EXIT WHEN c%NOTFOUND;
    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, arg_total_limit INT)
AS
  v_name VARCHAR(10);
  v_total INT;
-- +0 is needed to work around the bug MDEV-11081
  CURSOR c(p_v INT) IS
    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;
BEGIN
  FOR i IN 0..1
  LOOP
    OPEN c(i);
    LOOP
      FETCH c INTO v_name, v_total;
      EXIT WHEN c%NOTFOUND;
      SELECT v_name, v_total;
    END LOOP;
    CLOSE c;
  END LOOP;
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)
AS
  v_a INT;
  v_b VARCHAR(10);
  CURSOR c (p_a INT) IS SELECT a,b FROM t1 ORDER BY a LIMIT p_a;
BEGIN
  CREATE TABLE t2 (a INT, b VARCHAR(10));
  OPEN c(a_a);
  LOOP
    FETCH c INTO v_a, v_b;
    EXIT WHEN c%NOTFOUND;
    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-10597 Cursors with parameters
--echo #

--echo #
--echo # MDEV-12209 sql_mode=ORACLE: Syntax error in a OPEN cursor with parameters makes the server crash
--echo #
CREATE TABLE t1 (a INT, b VARCHAR(10));
INSERT INTO t1 VALUES (1,'A');
DELIMITER $$;
--error ER_PARSE_ERROR
CREATE PROCEDURE p1(a INT,b VARCHAR)
AS
  CURSOR c (p_a INT, p_b VARCHAR) IS SELECT * FROM t1 WHERE a=p_a;
BEGIN
  OPEN c(a+, b);
  LOOP
    FETCH c INTO a, b;
    EXIT WHEN c%NOTFOUND;
    SELECT a, b;
  END LOOP;
  CLOSE c;
END;
$$
DELIMITER ;$$
DROP TABLE t1;


--echo #
--echo # MDEV-10577 sql_mode=ORACLE: %TYPE in variable declarations
--echo #

CREATE TABLE t1 (a INT, b VARCHAR(10),c DATETIME(3));
INSERT INTO t1 VALUES (1,'b1','2001-01-01 10:20:30.123');
INSERT INTO t1 VALUES (2,'b2','2001-01-02 10:20:30.123');
CREATE TABLE t2 LIKE t1;
DELIMITER $$;
CREATE PROCEDURE p1()
AS
  v_a t1.a%TYPE;
  v_b t1.b%TYPE;
  v_c t1.c%TYPE;
  CURSOR c IS SELECT a,b,c FROM t1;
BEGIN
  OPEN c;
  LOOP
    FETCH c INTO v_a, v_b, v_c;
    EXIT WHEN c%NOTFOUND;
    INSERT INTO t2 (a,b,c) VALUES (v_a, v_b, v_c);
  END LOOP;
  CLOSE c;
END;
$$
DELIMITER ;$$
CALL p1();
SELECT * FROM t2;
DROP TABLE t2;
DROP PROCEDURE p1;
DROP TABLE t1;

--echo #
--echo # MDEV-12007 Allow ROW variables as a cursor FETCH target
--echo #

CREATE TABLE t1 (a INT, b VARCHAR(32));
INSERT INTO t1 VALUES (10,'b10');
INSERT INTO t1 VALUES (20,'b20');
INSERT INTO t1 VALUES (30,'b30');
DELIMITER $$;
CREATE PROCEDURE p1 AS
  rec ROW(a INT, b VARCHAR(32));
  CURSOR c IS SELECT a,b FROM t1;
BEGIN
  OPEN c;
  LOOP
    FETCH c INTO rec;
    EXIT WHEN c%NOTFOUND;
    SELECT ('rec=(' || rec.a ||','|| rec.b||')') AS c;
  END LOOP;
  CLOSE c;
END;
$$
DELIMITER ;$$
CALL p1();
DROP PROCEDURE p1;
DROP TABLE t1;


--echo #
--echo # MDEV-12441 Variables declared after cursors with parameters lose values
--echo #

DELIMITER $$;
CREATE PROCEDURE p1() AS
  x0 INT:=100;
  CURSOR cur(cp1 INT, cp2 INT) IS SELECT cp1+cp2;
  x1 INT:=101;
BEGIN
  OPEN cur(10,11);
  CLOSE cur;
  SELECT x0, x1;
END;
$$
DELIMITER ;$$
CALL p1();
DROP PROCEDURE p1;


CREATE TABLE t1 (a INT);
DELIMITER $$;
CREATE PROCEDURE p1() AS
  x0 INT:=100;
  CURSOR cur(cp1 INT, cp2 INT) IS SELECT cp1+cp2;
  x1 t1.a%TYPE:=101;
BEGIN
  OPEN cur(10,11);
  CLOSE cur;
  SELECT x0, x1;
END;
$$
DELIMITER ;$$
CALL p1();
DROP PROCEDURE p1;
DROP TABLE t1;


DELIMITER $$;
CREATE PROCEDURE p1() AS
  x0 INT:=100;
  CURSOR cur(cp1 INT, cp2 INT) IS SELECT cp1+cp2;
  x1 ROW(a INT,b INT):=ROW(101,102);
BEGIN
  OPEN cur(10,11);
  CLOSE cur;
  SELECT x0, x1.a, x1.b;
END;
$$
DELIMITER ;$$
CALL p1();
DROP PROCEDURE p1;


CREATE TABLE t1 (a INT, b VARCHAR(10));
INSERT INTO t1 VALUES (10,'Tbl-t1.b0');
DELIMITER $$;
CREATE PROCEDURE p1() AS
  x0 INT:=100;
  CURSOR cur(cp1 INT, cp2 INT) IS SELECT a,b FROM t1;
  x1 t1%ROWTYPE:=ROW(101,'Var-x1.b0');
BEGIN
  SELECT x0, x1.a, x1.b;
  OPEN cur(10,11);
  FETCH cur INTO x1;
  CLOSE cur;
  SELECT x0, x1.a, x1.b;
END;
$$
DELIMITER ;$$
CALL p1();
DROP PROCEDURE p1;
DROP TABLE t1;


CREATE TABLE t1 (a INT, b VARCHAR(10));
INSERT INTO t1 VALUES (10,'Tbl-t1.b0');
DELIMITER $$;
CREATE PROCEDURE p1() AS
  x0 INT:=100;
  CURSOR cur(cp1 INT, cp2 INT) IS SELECT a,b FROM t1;
  x1 cur%ROWTYPE:=ROW(101,'Var-x1.b0');
BEGIN
  SELECT x0, x1.a, x1.b;
  OPEN cur(10,11);
  FETCH cur INTO x1;
  CLOSE cur;
  SELECT x0, x1.a, x1.b;
END;
$$
DELIMITER ;$$
CALL p1();
DROP PROCEDURE p1;
DROP TABLE t1;

--echo #
--echo # MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions
--echo #

--enable_metadata
--disable_ps_protocol
DELIMITER $$;
DECLARE
  CURSOR c IS SELECT 1 AS c FROM DUAL;
BEGIN
  OPEN c;
  SELECT
    c%ISOPEN,
    c%NOTFOUND,
    c%FOUND,
    c%ROWCOUNT;
  CLOSE c;
END;
$$
DELIMITER ;$$
--enable_ps_protocol
--disable_metadata


--echo #
--echo # MDEV-17387 MariaDB Server giving wrong error while executing select query from procedure
--echo #

CREATE TABLE t1
(
  JOBN varchar(18) NOT NULL,
  pk int(11) NOT NULL,
  PRIMARY KEY (pk),
  KEY (JOBN)
);

DELIMITER $$;
CREATE PROCEDURE p1
AS
  lS NUMBER(10) :=0;
  CURSOR cBPD IS SELECT * FROM t1 WHERE JOBN='x';
BEGIN
  FOR lbpd IN cBPD LOOP
    lS:=lS+1;
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    BEGIN
      SELECT SQLERRM;
    END;
END;
$$
DELIMITER ;$$

CALL p1();
DROP PROCEDURE p1;
DROP TABLE t1;


--echo #
--echo # Start of 10.8 tests
--echo #

--echo #
--echo # MDEV-10654 IN, OUT, INOUT parameters in CREATE FUNCTION
--echo #

DELIMITER $$;
DECLARE
  va INT;
  CURSOR cur (a IN INT) IS SELECT a FROM dual;
BEGIN
  OPEN cur(1);
  FETCH cur INTO va;
  CLOSE cur;
  SELECT va;
END;
$$
DELIMITER ;$$

DELIMITER $$;
--error ER_NOT_SUPPORTED_YET
DECLARE
  va INT;
  CURSOR cur (a OUT INT) IS SELECT a FROM dual;
BEGIN
  OPEN cur(1);
  FETCH cur INTO va;
  CLOSE cur;
  SELECT va;
END;
$$
DELIMITER ;$$

DELIMITER $$;
--error ER_NOT_SUPPORTED_YET
DECLARE
  va INT;
  CURSOR cur (a INOUT INT) IS SELECT a FROM dual;
BEGIN
  OPEN cur(1);
  FETCH cur INTO va;
  CLOSE cur;
  SELECT va;
END;
$$
DELIMITER ;$$


--echo #
--echo # End of 10.8 tests
--echo #