[MDEV-39518] Allow prepared statements in stored functions in assignment right hand - Jira

XML

Word

Printable

Currently prepared statements are not allowed in stored functions. Prepared statements are caught on the parser level and an error is raised:

ERROR 1336 (0A000): Dynamic SQL is not allowed in stored function or trigger

SP variable assignment is a special case where prepared statements can be safely allowed.

SET spvar= stored_function_with_prepared_statements();

The above call is equivalent to a stored procedure call with an OUT parameter:

CALL stored_procedure_with_prepared_statements(spvar);

So allowing prepared statements in functions which are used on the right hand of the assignment operator should be safe.

After this change the following example will work:

DELIMITER $$

CREATE OR REPLACE FUNCTION f1() RETURNS INT DETERMINISTIC

BEGIN

  DECLARE c0 SYS_REFCURSOR;

  DECLARE v0 INT;

  PREPARE stmt FROM 'SELECT 10';

  OPEN c0 FOR PREPARE stmt;

  FETCH c0 INTO v0;

  CLOSE c0;

  DEALLOCATE PREPARE stmt;

  RETURN v0;

END;

$$

DELIMITER ;

DELIMITER $$

CREATE OR REPLACE PROCEDURE p1()

BEGIN

  DECLARE n INT;

  SET n= f1();

  SELECT n;

END;

$$

DELIMITER ;

CALL p1;

+------+

| n    |

+------+

|   10 |

+------+

Limitations

Queries which cause explicit or implicit commit (e.g. DDL) in the query won't work in functions

is blocked by

MDEV-39022 Add `LOCAL spvar` syntax for prepared statements and SYS_REFCURSORs

MDEV-39748 Package function calls are not replicated well in statement and mixed modes

relates to

MDEV-12034 Dynamic SQL in stored functions

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.