MDEV-24823 Crash with invalid multi-table update of view in 2nd execution of SP

Before this patch mergeable derived tables / view used in a multi-table
update / delete were merged before the preparation stage.
When the merge of a derived table / view is performed the on expression
attached to it is fixed and ANDed with the where condition of the select S
containing this derived table / view. It happens after the specification of
the derived table / view has been merged into S. If the ON expression refers
to a non existing field an error is reported and some other mergeable derived
tables / views remain unmerged. It's not a problem if the multi-table
update / delete statement is standalone. Yet if it is used in a stored
procedure the select with incompletely merged derived tables / views may
cause a problem for the second call of the procedure. This does not happen
for select queries using derived tables / views, because in this case their
specifications are merged after the preparation stage at which all ON
expressions are fixed.
This patch makes sure that merging of the derived tables / views used in a
multi-table update / delete statement is performed after the preparation
stage.

Approved by Oleksandr Byelkin <sanja@mariadb.com>

1 files changed, 53 insertions, 0 deletions

diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test
index b5328f74e70..dff3938d02f 100644
--- a/mysql-test/t/multi_update.test
+++ b/mysql-test/t/multi_update.test
@@ -1041,3 +1041,56 @@ drop function f1;
 --echo #
 --echo # end of 5.5 tests
 --echo #
+
+--echo #
+--echo # MDEV-24823: Invalid multi-table update of view within SP
+--echo #
+
+create table t1 (id int) engine=myisam;
+insert into t1 values (1),(2),(1);
+create table t2 (pk int, c0 int) engine=myisam;
+insert into t2 values (1,1), (2,3);
+create view v2 as select * from t2;
+create view v3 as select * from t2 where c0 < 3;
+
+create  procedure sp0() update t1, v2 set v2.pk = 1 where v2.c0 = t1.c1;
+--error ER_BAD_FIELD_ERROR
+call sp0();
+--error ER_BAD_FIELD_ERROR
+call sp0();
+
+create  procedure sp1() update (t1 join v2 on v2.c0 = t1.c1) set v2.pk = 1;
+--error ER_BAD_FIELD_ERROR
+call sp1();
+--error ER_BAD_FIELD_ERROR
+call sp1();
+
+create  procedure sp2() update (t1 join v3 on v3.c0 = t1.c1) set v3.pk = 1;
+--error ER_BAD_FIELD_ERROR
+call sp2();
+--error ER_BAD_FIELD_ERROR
+call sp2();
+
+create  procedure sp3()
+update (t1 join v2 on v2.c0 = t1.id) set v2.c0 = v2.c0+1;
+select * from t2;
+call sp3();
+select * from t2;
+call sp3();
+select * from t2;
+
+create  procedure sp4() delete t1 from t1 join v2 on v2.c0 = t1.c1;
+--error ER_BAD_FIELD_ERROR
+call sp4();
+--error ER_BAD_FIELD_ERROR
+call sp4();
+
+drop procedure sp0;
+drop procedure sp1;
+drop procedure sp2;
+drop procedure sp3;
+drop procedure sp4;
+drop view v2,v3;
+drop table t1,t2;
+
+--echo # End of 10.2 tests