revision-id: b99f29887530ecf0e9358928bc227bee5ec32f01 (mariadb-10.6.1-484-gb99f298) parent(s): 444fa763472bf2faa2e939866539f1a0816c29b3 author: Igor Babaev committer: Igor Babaev timestamp: 2022-07-28 17:52:47 -0700 message: 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> --- mysql-test/main/update.result | 38 ++++++++++++++++++++++++++++++++++++++ mysql-test/main/update.test | 38 ++++++++++++++++++++++++++++++++++++++ sql/sql_select.cc | 2 ++ 3 files changed, 78 insertions(+) diff --git a/mysql-test/main/update.result b/mysql-test/main/update.result index 15efd7e..c6c55da 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 8470910..0f133b2 100644 --- a/mysql-test/main/update.test +++ b/mysql-test/main/update.test @@ -673,3 +673,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 diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 60b14f9..0a42147 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -30677,6 +30677,8 @@ bool Sql_cmd_dml::execute(THD *thd) MYSQL_DML_DONE(thd, 1); THD_STAGE_INFO(thd, stage_end); (void)unit->cleanup(); + if (is_prepared()) + unprepare(thd); return thd->is_error(); }