mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-04-05 23:05:34 +02:00
Code Issues Projects Releases Packages Wiki Activity Actions 1
mariadb/mysql-test/main/sp-sys_refcursor.test
Alexander Barkov e6961ea311 MDEV-20034 Add support for the pre-defined weak SYS_REFCURSOR 
This patch adds support for SYS_REFCURSOR (a weakly typed cursor)
for both sql_mode=ORACLE and sql_mode=DEFAULT.

Works as a regular stored routine variable, parameter and return value:

- can be passed as an IN parameter to stored functions and procedures
- can be passed as an INOUT and OUT parameter to stored procedures
- can be returned from a stored function

Note, strongly typed REF CURSOR will be added separately.

Note, to maintain dependencies easier, some parts of sql_class.h
and item.h were moved to new header files:

- select_results.h:
  class select_result_sink
  class select_result
  class select_result_interceptor

- sp_cursor.h:
  class sp_cursor_statistics
  class sp_cursor

- sp_rcontext_handler.h
  class Sp_rcontext_handler and its descendants

The implementation consists of the following parts:
- A new class sp_cursor_array deriving from Dynamic_array

- A new class Statement_rcontext which contains data shared
  between sub-statements of a compound statement.
  It has a member m_statement_cursors of the sp_cursor_array data type,
  as well as open cursor counter. THD inherits from Statement_rcontext.

- A new data type handler Type_handler_sys_refcursor in plugins/type_cursor/
  It is designed to store uint16 references -
  positions of the cursor in THD::m_statement_cursors.

- Type_handler_sys_refcursor suppresses some derived numeric features.
  When a SYS_REFCURSOR variable is used as an integer an error is raised.

- A new abstract class sp_instr_fetch_cursor. It's needed to share
  the common code between "OPEN cur" (for static cursors) and
  "OPER cur FOR stmt" (for SYS_REFCURSORs).

- New sp_instr classes:
  * sp_instr_copen_by_ref      - OPEN sys_ref_curor FOR stmt;
  * sp_instr_cfetch_by_ref     - FETCH sys_ref_cursor INTO targets;
  * sp_instr_cclose_by_ref     - CLOSE sys_ref_cursor;
  * sp_instr_destruct_variable - to destruct SYS_REFCURSOR variables when
                                 the execution goes out of the BEGIN..END block
                                 where SYS_REFCURSOR variables are declared.
- New methods in LEX:
  * sp_open_cursor_for_stmt   - handles "OPEN sys_ref_cursor FOR stmt".
  * sp_add_instr_fetch_cursor - "FETCH cur INTO targets" for both
                                static cursors and SYS_REFCURSORs.
  * sp_close - handles "CLOSE cur" both for static cursors and SYS_REFCURSORs.

- Changes in cursor functions to handle both static cursors and SYS_REFCURSORs:
  * Item_func_cursor_isopen
  * Item_func_cursor_found
  * Item_func_cursor_notfound
  * Item_func_cursor_rowcount

- A new system variable @@max_open_cursors - to limit the number
  of cursors (static and SYS_REFCURSORs) opened at the same time.
  Its allowed range is [0-65536], with 50 by default.

- A new virtual method Type_handler::can_return_bool() telling
  if calling item->val_bool() is allowed for Items of this data type,
  or if otherwise the "Illegal parameter for operation" error should be raised
  at fix_fields() time.

- New methods in Sp_rcontext_handler:
  * get_cursor()
  * get_cursor_by_ref()

- A new class Sp_rcontext_handler_statement to handle top level statement
  wide cursors which are shared by all substatements.

- A new virtual method expr_event_handler() in classes Item and Field.
  It's needed to close (and make available for a new OPEN)
  unused THD::m_statement_cursors elements which do not have any references
  any more. It can happen in various moments in time, e.g.
  * after evaluation parameters of an SQL routine
  * after assigning a cursor expression into a SYS_REFCURSOR variable
  * when leaving a BEGIN..END block with SYS_REFCURSOR variables
  * after setting OUT/INOUT routine actual parameters from formal
    parameters.
2025-03-18 18:31:28 +01:00

387 lines
8.2 KiB
Text
Raw Permalink Blame History

--echo #
--echo # MDEV-20034 Add support for the pre-defined weak SYS_REFCURSOR
--echo #
--echo #
--echo # p1() does not cause "Too many open cursors"
--echo # as on every iteration it closes an existng cursor and reopens it.
--echo #
SET @@max_open_cursors=3;
DELIMITER $$;
CREATE FUNCTION f1() RETURNS SYS_REFCURSOR
BEGIN
DECLARE c SYS_REFCURSOR;
OPEN c FOR SELECT 1 AS a FROM DUAL;
RETURN c;
END;
$$
CREATE PROCEDURE p1(count INT)
BEGIN
DECLARE c SYS_REFCURSOR;
DECLARE va INT;
FOR i IN 1..count
DO
SET c=f1();
FETCH c INTO va;
END FOR;
END;
$$
DELIMITER ;$$
CALL p1(30);
DROP PROCEDURE p1;
DROP FUNCTION f1;
SET @@max_open_cursors=DEFAULT;
--echo #
--echo # Error: too many open cursors
--echo #
SET @@max_open_cursors=3;
SET @@max_sp_recursion_depth=50;
DELIMITER $$;
CREATE FUNCTION f1() RETURNS SYS_REFCURSOR
BEGIN
DECLARE c SYS_REFCURSOR;
OPEN c FOR SELECT 1 AS a FROM DUAL;
RETURN c;
END;
$$
CREATE PROCEDURE p1(count INT)
BEGIN
DECLARE c SYS_REFCURSOR;
DECLARE va INT;
IF count > 0 THEN
SET c=f1();
CALL p1(count-1);
END IF;
END;
$$
DELIMITER ;$$
CALL p1(3);
--error ER_TOO_MANY_OPEN_CURSORS
CALL p1(4);
CALL p1(3);
--error ER_TOO_MANY_OPEN_CURSORS
CALL p1(4);
DROP FUNCTION f1;
DROP PROCEDURE p1;
SET max_open_cursors=DEFAULT;
SET @@max_sp_recursion_depth=DEFAULT;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2),(3),(4);
DELIMITER $$;
CREATE PROCEDURE p1()
BEGIN
DECLARE va INT;
DECLARE stage TEXT DEFAULT '';
DECLARE c1, c2, c3, c4 SYS_REFCURSOR;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN
GET DIAGNOSTICS CONDITION 1 @msg= MESSAGE_TEXT;
SELECT @@max_open_cursors, stage, @msg;
END;
SET max_open_cursors=3;
SET stage='OPEN1 c1'; OPEN c1 FOR SELECT a FROM t1; -- Ok
SET stage='OPEN1 c2'; OPEN c2 FOR SELECT a FROM t1; -- Ok
SET stage='OPEN1 c3'; OPEN c3 FOR SELECT a FROM t1; -- Ok
SET stage='OPEN1 c4'; OPEN c4 FOR SELECT a FROM t1; -- Error: too many open cursors
SET max_open_cursors= 1;
-- Cursors beyond the limit are still available for FETCH
SET stage='FETCH1 c1'; FETCH c1 INTO va; SELECT 'c1', va; -- Ok
SET stage='FETCH1 c2'; FETCH c2 INTO va; SELECT 'c2', va; -- Ok
SET stage='FETCH1 c3'; FETCH c3 INTO va; SELECT 'c3', va; -- Ok
SET stage='FETCH1 c4'; FETCH c4 INTO va; -- Error: not open
-- Open cursors beyond the limit are still available for reopen
-- Reasoning: CLOSE+OPEN do not increase the total amount of open cursors
SET stage='REOPEN1 c1'; OPEN c1 FOR SELECT a FROM t1; -- Ok
SET stage='REOPEN1 c2'; OPEN c2 FOR SELECT a FROM t1; -- Ok
SET stage='REOPEN1 c3'; OPEN c3 FOR SELECT a FROM t1; -- Ok
SET stage='REOPEN1 c4'; OPEN c4 FOR SELECT a FROM t1; -- Error: too many open cursors
-- Cursors beyond the limit are still available for FETCH after reopen
SET stage='FETCH2 c1'; FETCH c1 INTO va; SELECT 'c1', va; -- Ok
SET stage='FETCH2 c2'; FETCH c2 INTO va; SELECT 'c2', va; -- Ok
SET stage='FETCH2 c3'; FETCH c3 INTO va; SELECT 'c3', va; -- Ok
SET stage='FETCH2 c4'; FETCH c4 INTO va; -- Error: not open
-- Open cursors beyond the limit are available for CLOSE
SET stage='CLOSE1 c1'; CLOSE c1; -- Ok
SET stage='CLOSE1 c2'; CLOSE c2; -- Ok
SET stage='CLOSE1 c3'; CLOSE c3; -- Ok
SET stage='CLOSE1 c4'; CLOSE c4; -- Error: not open
-- Closed cursors beyond the limit are not available for a new OPEN
SET stage='OPEN2 c1'; OPEN c1 FOR SELECT a FROM t1; -- Ok: fits the limit
SET stage='OPEN2 c2'; OPEN c2 FOR SELECT a FROM t1; -- Error: beyond the limit
SET stage='OPEN2 c3'; OPEN c3 FOR SELECT a FROM t1; -- Error: beyond the limit
SET stage='OPEN2 c4'; OPEN c4 FOR SELECT a FROM t1; -- Error: beyond the limit
-- c1 is open. Close it, so we get all cursors c1..c4 closed.
SET stage= 'CLOSE2 c1'; CLOSE c1; -- Ok
-- All cursors are closed. Now open c3.
SET stage= 'OPEN3 c3'; OPEN c3 FOR SELECT a FROM t1; -- Ok
SET stage= 'FETCH3 c3'; FETCH c3 INTO va; -- Ok
SET stage= 'CLOSE3 c3'; CLOSE c3; -- Ok
END;
$$
DELIMITER ;$$
CALL p1;
SET max_open_cursors=DEFAULT;
DROP TABLE t1;
--echo #
--echo # Two consequent OPEN (without a CLOSE in beetween) are allowed
--echo #
DELIMITER $$;
BEGIN NOT ATOMIC
DECLARE a INT;
DECLARE c SYS_REFCURSOR;
OPEN c FOR SELECT 1;
OPEN c FOR SELECT 2;
FETCH c INTO a;
SELECT a;
END;
$$
DELIMITER ;$$
--echo #
--echo # Many consequent OPEN (without a CLOSE in between) are allowed
--echo # and do not cause ER_TOO_MANY_OPEN_CURSORS.
--echo #
DELIMITER $$;
BEGIN NOT ATOMIC
DECLARE c SYS_REFCURSOR;
FOR i IN 0..300
DO
OPEN c FOR SELECT 1 AS c FROM DUAL;
END FOR;
END;
$$
DELIMITER ;$$
--echo #
--echo # Simple use example (OPEN, FETCH, CLOSE)
--echo #
DELIMITER $$;
BEGIN NOT ATOMIC
DECLARE c SYS_REFCURSOR;
DECLARE a INT;
OPEN c FOR SELECT 1;
FETCH c INTO a;
CLOSE c;
END;
$$
DELIMITER ;$$
--echo #
--echo # Fetching from two parallel cursors
--echo #
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1);
DELIMITER $$;
CREATE OR REPLACE PROCEDURE p1()
BEGIN
DECLARE a0 INT;
DECLARE a1 INT;
DECLARE c0 SYS_REFCURSOR;
DECLARE c1 SYS_REFCURSOR;
OPEN c0 FOR SELECT a*10 FROM t1;
OPEN c1 FOR SELECT a*20 FROM t1;
FETCH c0 INTO a0;
FETCH c1 INTO a1;
SELECT a0, a1;
CLOSE c0;
CLOSE c1;
END;
$$
DELIMITER ;$$
CALL p1;
DROP PROCEDURE p1;
DROP TABLE t1;
--echo #
--echo # SYS_REFCURSOR alasing
--echo #
DELIMITER $$;
BEGIN NOT ATOMIC
DECLARE c0 SYS_REFCURSOR;
DECLARE c1 SYS_REFCURSOR;
DECLARE a INT;
OPEN c0 FOR SELECT 11 FROM DUAL UNION SELECT 12 FROM DUAL;
SET c1= c0;
FETCH c0 INTO a;
SELECT a;
OPEN c0 FOR SELECT 21 FROM DUAL UNION SELECT 22 FROM DUAL;
FETCH c1 INTO a; /* c1 now points to the new "OPEN c0" */
SELECT a;
END;
$$
DELIMITER ;$$
--echo #
--echo # Function returning SYS_REFCURSOR and mysql.proc
--echo #
DELIMITER $$;
CREATE FUNCTION f1() RETURNS SYS_REFCURSOR
BEGIN
DECLARE c0 SYS_REFCURSOR;
RETURN c0;
END;
$$
DELIMITER ;$$
SELECT returns FROM mysql.proc WHERE name='f1';
SHOW CREATE FUNCTION f1;
DROP FUNCTION f1;
--echo #
--echo # Procedure with a SYS_REFCURSOR parameter and mysql.proc
--echo #
DELIMITER $$;
CREATE PROCEDURE p1(OUT a0 SYS_REFCURSOR)
BEGIN
DECLARE c0 SYS_REFCURSOR;
SET a0= c0;
END;
$$
DELIMITER ;$$
SELECT param_list FROM mysql.proc WHERE name='p1';
SHOW CREATE PROCEDURE p1;
DROP PROCEDURE p1;
--echo #
--echo # Returning a open cursor from a function
--echo #
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10),(20);
DELIMITER $$;
CREATE FUNCTION f1() RETURNS SYS_REFCURSOR
BEGIN
DECLARE c SYS_REFCURSOR;
OPEN c FOR SELECT a FROM t1 ORDER BY a;
RETURN c;
END;
$$
DELIMITER ;$$
DELIMITER $$;
CREATE PROCEDURE p1()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE a INT;
DECLARE c SYS_REFCURSOR DEFAULT f1();
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
fetch_loop:
LOOP
FETCH c INTO a;
IF done THEN
LEAVE fetch_loop;
END IF;
SELECT a;
END LOOP;
CLOSE c;
END;
$$
DELIMITER ;$$
CALL p1;
DROP PROCEDURE p1;
DROP FUNCTION f1;
DROP TABLE t1;
--echo #
--echo # Returning an open cursor as an OUT param
--echo #
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10),(20);
DELIMITER $$;
CREATE PROCEDURE p1(OUT c SYS_REFCURSOR)
BEGIN
OPEN c FOR SELECT a FROM t1 ORDER BY a;
END;
$$
DELIMITER ;$$
DELIMITER $$;
CREATE PROCEDURE p2()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE a INT;
DECLARE c SYS_REFCURSOR;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
CALL p1(c);
fetch_loop:
LOOP
FETCH c INTO a;
IF done THEN
LEAVE fetch_loop;
END IF;
SELECT a;
END LOOP;
CLOSE c;
END;
$$
DELIMITER ;$$
CALL p2;
DROP PROCEDURE p1;
DROP PROCEDURE p2;
DROP TABLE t1;
--echo #
--echo # A prepared statement calls its own thd->cleanup_after_query()
--echo # Make sure it does not close SYS_REFCURSORs,
--echo # and does not assert that all static cursors are closed.
--echo #
DELIMITER $$;
CREATE PROCEDURE p1()
BEGIN
DECLARE v0, v1 VARCHAR(64);
DECLARE c0 SYS_REFCURSOR;
DECLARE c1 CURSOR FOR SELECT 'c1val';
OPEN c0 FOR SELECT 'c0val';
OPEN c1;
EXECUTE IMMEDIATE 'SELECT "ps1val"'; -- PS calls thd->cleanup_after_query()
FETCH c1 INTO v1; -- this still works, no asserts happened.
FETCH c0 INTO v0; -- this still works, c0 is still open.
SELECT v0, v1;
CLOSE c1;
CLOSE c0;
END
$$
DELIMITER ;$$
CALL p1;
DROP PROCEDURE p1;
Reference in a new issue View git blame Copy permalink