Allow prepared statements in stored functions in assignment right hand

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.

Note, we will allow prepared statements only if a stored function has the CONTAINS SQL clause. Functions without this clause will still raise the error on attempts to use prepared statements on the parser level.

After this change the following example will work:

DELIMITER $$

CREATE OR REPLACE FUNCTION f1() RETURNS INT DETERMINISTIC CONTAINS SQL

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 |

+------+