MDEV-29189 Crash of the second execution of SF using DELETE/UPDATE

This bug caused a crash of the server at the second execution of a stored
function that used DELETE or UPDATE statement if the first execution
of this function reported an error encountered after the prepare phase.
This happened because in such cases the executed DELETE/UPDATE statement
remained marked as prepared. As a result the second execution of SF missed
the prepare phase for the statement altogether and the statement could not
be executed properly.

Approved by Oleksandr Byelkin <sanja@mariadb.com>

2 files changed, 76 insertions, 0 deletions

diff --git a/mysql-test/main/update.result b/mysql-test/main/update.result
index 15efd7e4362..c6c55da229e 100644
--- a/mysql-test/main/update.result
+++ b/mysql-test/main/update.result
@@ -734,3 +734,41 @@ UPDATE t1,t2 SET t1.i1 = -39 WHERE t2.d1 <> t1.i1 AND t2.d1 = t1.d2;
 ERROR 22007: Incorrect datetime value: '19' for column `test`.`t1`.`i1` at row 1
 DROP TABLE t1,t2;
 # End of MariaDB 10.2 tests
+#
+# MDEV-29189: Second execution of SF using UPDATE?DELETE
+#             after reported error by the first execution
+#
+CREATE TABLE t1 (c int);
+CREATE FUNCTION f1() RETURNS int
+BEGIN
+UPDATE t1 SET c=c+1;
+RETURN 1;
+END;//
+CREATE FUNCTION f2() RETURNS int
+BEGIN
+DELETE FROM t1 WHERE c < 7;
+RETURN 1;
+END;//
+INSERT INTO t1 VALUES (3), (7), (1);
+SELECT * FROM t1 WHERE f1() = 1;
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger
+SELECT f1();
+f1()
+1
+SELECT * FROM t1;
+c
+4
+8
+2
+SELECT * FROM t1 WHERE f2() = 1;
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger
+SELECT f2();
+f2()
+1
+SELECT * FROM t1;
+c
+8
+DROP FUNCTION f1;
+DROP FUNCTION f2;
+DROP TABLE t1;
+# End of MariaDB 10.10 tests
diff --git a/mysql-test/main/update.test b/mysql-test/main/update.test
index 8a6949447ee..88fea88f120 100644
--- a/mysql-test/main/update.test
+++ b/mysql-test/main/update.test
@@ -676,3 +676,41 @@ UPDATE t1,t2 SET t1.i1 = -39 WHERE t2.d1 <> t1.i1 AND t2.d1 = t1.d2;
 DROP TABLE t1,t2;
 
 --echo # End of MariaDB 10.2 tests
+
+--echo #
+--echo # MDEV-29189: Second execution of SF using UPDATE?DELETE
+--echo #             after reported error by the first execution
+--echo #
+
+CREATE TABLE t1 (c int);
+
+DELIMITER //;
+CREATE FUNCTION f1() RETURNS int
+BEGIN
+  UPDATE t1 SET c=c+1;
+  RETURN 1;
+END;//
+CREATE FUNCTION f2() RETURNS int
+BEGIN
+  DELETE FROM t1 WHERE c < 7;
+  RETURN 1;
+END;//
+DELIMITER ;//
+
+INSERT INTO t1 VALUES (3), (7), (1);
+
+--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
+SELECT * FROM t1 WHERE f1() = 1;
+SELECT f1();
+SELECT * FROM t1;
+
+--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
+SELECT * FROM t1 WHERE f2() = 1;
+SELECT f2();
+SELECT * FROM t1;
+
+DROP FUNCTION f1;
+DROP FUNCTION f2;
+DROP TABLE t1;
+
+--echo # End of MariaDB 10.10 tests