MDEV-28965 Assertion failure when preparing UPDATE with derived table in WHERE

This patch fixes not only the assertion failure in the function
Field_iterator_table_ref::set_field_iterator() but also:
 - fixes the problem of forced materialization of derived tables used
   in subqueries contained in WHERE clauses of single-table and multi-table
   UPDATE and DELETE statements
 - fixes the problem of MDEV-17954 that prevented execution of multi-table
   DELETE statements if they use in their WHERE clauses references to
   the tables that are updated.

The patch must be considered a complement to the patch for MDEV-28883.

Approved by Oleksandr Byelkin <sanja@mariadb.com>

15 files changed, 864 insertions, 16 deletions

diff --git a/mysql-test/main/delete_use_source.result b/mysql-test/main/delete_use_source.result
index 0990a551db6..329203af613 100644
--- a/mysql-test/main/delete_use_source.result
+++ b/mysql-test/main/delete_use_source.result
@@ -49,7 +49,7 @@ rollback;
 start transaction;
 explain delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500 limit 1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	range	c1	c1	4	NULL	600	Using where
+1	PRIMARY	t1	range	c1	c1	4	NULL	600	Using index condition; Using where
 2	DEPENDENT SUBQUERY	b	ref	c1	c1	4	test.t1.c1	167	Using index
 delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500 limit 1;
 affected rows: 1
@@ -65,7 +65,7 @@ rollback;
 start transaction;
 explain delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	c1	NULL	NULL	NULL	#	Using where
+1	PRIMARY	t1	range	c1	c1	4	NULL	#	Using index condition; Using where
 2	DEPENDENT SUBQUERY	b	ref	c1	c1	4	test.t1.c1	#	Using index
 delete from v1 where (select count(*) from t1 b where b.c1=v1.c1) = 500 ;
 affected rows: 500
@@ -154,3 +154,183 @@ set session sort_buffer_size = 1024;
 delete from t1 where c1=0 and exists(select 'x' from t1 b where b.c1<10);
 affected rows: 128000
 drop table t1;
+#
+# MDEV-17954: multi-table DELETE with the same source and target
+#
+create table t1 (c1 int, c2 int, c3 int);
+insert into t1 values
+(1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5), (2,3,6), (2,4,7), (2,5,8);
+#
+# Single-table DELETE with the same source and target
+# handled as multi-table DELETE
+#
+explain delete from t1
+where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1 and a.c2 < 3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+1	PRIMARY	a	ALL	NULL	NULL	NULL	NULL	8	Using where; FirstMatch(t1)
+delete from t1
+where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1 and a.c2 < 3);
+select * from t1;
+c1	c2	c3
+1	3	3
+2	3	6
+2	4	7
+2	5	8
+delete from t1;
+insert into t1 values
+(1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5), (2,3,6), (2,4,7), (2,5,8);
+prepare stmt from "delete from t1
+where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1 and a.c2 < 3)";
+execute stmt;
+select * from t1;
+c1	c2	c3
+1	3	3
+2	3	6
+2	4	7
+2	5	8
+delete from t1;
+insert into t1 values
+(2,2,5), (2,3,6), (2,4,7), (2,5,8);
+execute stmt;
+select * from t1;
+c1	c2	c3
+2	3	6
+2	4	7
+2	5	8
+deallocate prepare stmt;
+delete from t1;
+insert into t1 values
+(1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5), (2,3,6), (2,4,7), (2,5,8);
+#
+# Multi-table DELETE with the same source and target
+#
+create table t2 (c1 int, c2 int, c3 int);
+insert into t2 values
+(1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5), (2,3,6), (2,5,8);
+explain delete from t1 using t1,t2
+where t1.c2 = t2.c2 and t1.c1 > 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	7	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+delete from t1 using t1,t2
+where t1.c2 = t2.c2 and t1.c1 > 1;
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+1	3	3
+2	4	7
+delete from t1;
+insert into t1 values
+(1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5), (2,3,6), (2,4,7), (2,5,8);
+prepare stmt from "delete from t1 using t1,t2
+where t1.c2 = t2.c2 and t1.c1 > 1";
+execute stmt;
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+1	3	3
+2	4	7
+delete from t1;
+insert into t1 values
+(2,2,5), (2,3,6), (2,4,7), (2,5,8);
+execute stmt;
+select * from t1;
+c1	c2	c3
+2	4	7
+deallocate prepare stmt;
+explain delete from t1 using t1,t2
+where t1.c2 = t2.c2 and t1.c1 > 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	7	Using where
+delete from t1 using t1,t2
+where t1.c2 = t2.c2 and t1.c1 > 1;
+select * from t1;
+c1	c2	c3
+2	4	7
+delete from t1;
+insert into t1 values
+(1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5), (2,3,6), (2,4,7), (2,5,8);
+prepare stmt from "delete from t1 using t1,t2
+where t1.c2 = t2.c2 and t1.c1 > 1";
+execute stmt;
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+1	3	3
+2	4	7
+delete from t1;
+insert into t1 values
+(2,2,5), (2,3,6), (2,4,7), (2,5,8);
+execute stmt;
+select * from t1;
+c1	c2	c3
+2	4	7
+deallocate prepare stmt;
+delete from t1;
+insert into t1 values
+(1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5), (2,3,6), (2,4,7), (2,5,8);
+explain delete from t1,t2 using t1,t2
+where t1.c2 = t2.c2 and t1.c1 > 1 and t2.c1 > 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	7	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+delete from t1,t2 using t1,t2
+where t1.c2 = t2.c2 and t1.c1 > 1 and t2.c1 > 1;
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+1	3	3
+2	4	7
+select * from t2;
+c1	c2	c3
+1	1	1
+1	2	2
+1	3	3
+delete from t1;
+insert into t1 values
+(1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5), (2,3,6), (2,4,7), (2,5,8);
+delete from t2;
+insert into t2 values
+(1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5), (2,3,6), (2,5,8);
+prepare stmt from "delete from t1,t2 using t1,t2
+where t1.c2 = t2.c2 and t1.c1 > 1 and t2.c1 > 1";
+execute stmt;
+select * from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+1	3	3
+2	4	7
+select * from t2;
+c1	c2	c3
+1	1	1
+1	2	2
+1	3	3
+delete from t1;
+insert into t1 values
+(1,2,2), (1,3,3), (2,2,5), (2,3,6), (2,4,7), (2,5,8);
+delete from t2;
+insert into t2 values
+(1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5);
+execute stmt;
+select * from t1;
+c1	c2	c3
+1	2	2
+1	3	3
+2	3	6
+2	4	7
+2	5	8
+select * from t2;
+c1	c2	c3
+1	1	1
+1	2	2
+1	3	3
+2	1	4
+deallocate prepare stmt;
+drop table t1,t2;
diff --git a/mysql-test/main/delete_use_source.test b/mysql-test/main/delete_use_source.test
index 4aed00da375..9625431c1a8 100644
--- a/mysql-test/main/delete_use_source.test
+++ b/mysql-test/main/delete_use_source.test
@@ -135,3 +135,123 @@ set session sort_buffer_size = 1024;
 delete from t1 where c1=0 and exists(select 'x' from t1 b where b.c1<10);
 
 drop table t1;
+
+--echo #
+--echo # MDEV-17954: multi-table DELETE with the same source and target
+--echo #
+
+create table t1 (c1 int, c2 int, c3 int);
+insert into t1 values
+  (1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5), (2,3,6), (2,4,7), (2,5,8);
+
+--echo #
+--echo # Single-table DELETE with the same source and target
+--echo # handled as multi-table DELETE
+--echo #
+
+let $q1=
+delete from t1
+  where c2 in (select distinct a.c2 from t1 a where t1.c1=a.c1 and a.c2 < 3);
+
+eval explain $q1;
+eval $q1;
+select * from t1;
+delete from t1;
+insert into t1 values
+  (1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5), (2,3,6), (2,4,7), (2,5,8);
+eval prepare stmt from "$q1";
+execute stmt;
+select * from t1;
+delete from t1;
+insert into t1 values
+  (2,2,5), (2,3,6), (2,4,7), (2,5,8);
+execute stmt;
+select * from t1;
+deallocate prepare stmt;
+
+delete from t1;
+insert into t1 values
+  (1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5), (2,3,6), (2,4,7), (2,5,8);
+
+--echo #
+--echo # Multi-table DELETE with the same source and target
+--echo #
+
+create table t2 (c1 int, c2 int, c3 int);
+insert into t2 values
+  (1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5), (2,3,6), (2,5,8);
+
+let $q2=
+delete from t1 using t1,t2
+  where t1.c2 = t2.c2 and t1.c1 > 1;
+
+eval explain $q2;
+eval $q2;
+select * from t1;
+delete from t1;
+insert into t1 values
+  (1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5), (2,3,6), (2,4,7), (2,5,8);
+eval prepare stmt from "$q2";
+execute stmt;
+select * from t1;
+delete from t1;
+insert into t1 values
+  (2,2,5), (2,3,6), (2,4,7), (2,5,8);
+execute stmt;
+select * from t1;
+deallocate prepare stmt;
+
+let $q2=
+delete from t1 using t1,t2
+  where t1.c2 = t2.c2 and t1.c1 > 1;
+
+eval explain $q2;
+eval $q2;
+select * from t1;
+delete from t1;
+insert into t1 values
+  (1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5), (2,3,6), (2,4,7), (2,5,8);
+eval prepare stmt from "$q2";
+execute stmt;
+select * from t1;
+delete from t1;
+insert into t1 values
+  (2,2,5), (2,3,6), (2,4,7), (2,5,8);
+execute stmt;
+select * from t1;
+deallocate prepare stmt;
+
+delete from t1;
+insert into t1 values
+  (1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5), (2,3,6), (2,4,7), (2,5,8);
+
+let $q3=
+delete from t1,t2 using t1,t2
+  where t1.c2 = t2.c2 and t1.c1 > 1 and t2.c1 > 1;
+
+eval explain $q3;
+eval $q3;
+select * from t1;
+select * from t2;
+delete from t1;
+insert into t1 values
+  (1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5), (2,3,6), (2,4,7), (2,5,8);
+delete from t2;
+insert into t2 values
+  (1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5), (2,3,6), (2,5,8);
+eval prepare stmt from "$q3";
+execute stmt;
+select * from t1;
+select * from t2;
+delete from t1;
+insert into t1 values
+  (1,2,2), (1,3,3), (2,2,5), (2,3,6), (2,4,7), (2,5,8);
+delete from t2;
+insert into t2 values
+  (1,1,1), (1,2,2), (1,3,3), (2,1,4), (2,2,5);
+execute stmt;
+select * from t1;
+select * from t2;
+deallocate prepare stmt;
+
+drop table t1,t2;
diff --git a/mysql-test/main/derived.result b/mysql-test/main/derived.result
index 112a72a2bf6..e8551957850 100644
--- a/mysql-test/main/derived.result
+++ b/mysql-test/main/derived.result
@@ -1478,3 +1478,338 @@ drop table t1;
 #
 # End of 11.0 tests
 #
+#
+# MDEV-28883: single/multi-table UPDATE/DELETE whose WHERE condition
+#             contains subquery from mergeable derived table
+#             that uses the updated/deleted table
+#
+create table t1 (pk int, a int);
+insert into t1 values (1,3), (2, 7), (3,1), (4,9);
+explain update t1 set a = 10
+where a = ( select * from (select a from t1) dt where dt.a > 7);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
+update t1 set a = 10
+where a = ( select * from (select a from t1) dt where dt.a > 7);
+select * from t1;
+pk	a
+1	3
+2	7
+3	1
+4	10
+delete from t1;
+insert into t1 values (1,3), (2, 7), (3,1), (4,9);
+prepare stmt from "update t1 set a = 10
+where a = ( select * from (select a from t1) dt where dt.a > 7)";
+execute stmt;
+select * from t1;
+pk	a
+1	3
+2	7
+3	1
+4	10
+delete from t1;
+insert into t1 value (4,9), (3,1), (1,3);
+execute stmt;
+select * from t1;
+pk	a
+4	10
+3	1
+1	3
+deallocate prepare stmt;
+delete from t1;
+insert into t1 value (4,9), (3,1), (1,3);
+explain update t1 set a = 10
+where a <> ( select * from (select a from t1) dt where dt.a > 7)
+order by a limit 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where; Using filesort
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+update t1 set a = 10
+where a <> ( select * from (select a from t1) dt where dt.a > 7)
+order by a limit 2;
+select * from t1;
+pk	a
+4	9
+3	10
+1	10
+delete from t1;
+insert into t1 value (4,9), (3,1), (1,3);
+prepare stmt from "update t1 set a = 10
+where a <> ( select * from (select a from t1) dt where dt.a > 7)
+order by a limit 2";
+execute stmt;
+select * from t1;
+pk	a
+4	9
+3	10
+1	10
+delete from t1;
+insert into t1 value (4,9), (3,1), (1,3);
+execute stmt;
+select * from t1;
+pk	a
+4	9
+3	10
+1	10
+deallocate prepare stmt;
+delete from t1;
+insert into t1 values (1,3), (2, 7), (3,1), (4,9);
+explain delete from t1
+where a = ( select * from (select a from t1) dt where dt.a > 7);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
+delete from t1
+where a = ( select * from (select a from t1) dt where dt.a > 7);
+select * from t1;
+pk	a
+1	3
+2	7
+3	1
+delete from t1;
+insert into t1 values (1,3), (2, 7), (3,1), (4,9);
+prepare stmt from "delete from t1
+where a = ( select * from (select a from t1) dt where dt.a > 7)";
+execute stmt;
+select * from t1;
+pk	a
+1	3
+2	7
+3	1
+delete from t1;
+insert into t1 value (4,9), (3,1), (1,3);
+execute stmt;
+select * from t1;
+pk	a
+3	1
+1	3
+deallocate prepare stmt;
+delete from t1;
+insert into t1 value (4,9), (3,1), (1,3);
+explain delete from t1
+where a = ( select * from (select a from t1) dt where dt.a > 5)
+returning pk, a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	SUBQUERY	<derived3>	ALL	NULL	NULL	NULL	NULL	3	Using where
+3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+delete from t1
+where a = ( select * from (select a from t1) dt where dt.a > 5)
+returning pk, a;
+pk	a
+4	9
+select * from t1;
+pk	a
+3	1
+1	3
+delete from t1;
+insert into t1 value (4,9), (3,1), (1,3);
+prepare stmt from "delete from t1
+where a = ( select * from (select a from t1) dt where dt.a > 5)
+returning pk, a";
+execute stmt;
+pk	a
+4	9
+select * from t1;
+pk	a
+3	1
+1	3
+delete from t1;
+insert into t1 value (4,9), (3,1), (1,3);
+execute stmt;
+pk	a
+4	9
+select * from t1;
+pk	a
+3	1
+1	3
+deallocate prepare stmt;
+delete from t1;
+insert into t1 values (1,3), (2, 7), (3,1), (4,9);
+explain delete from t1
+where a <> ( select * from (select a from t1) dt where dt.a > 7)
+order by a limit 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where; Using filesort
+2	SUBQUERY	<derived3>	ALL	NULL	NULL	NULL	NULL	4	Using where
+3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
+delete from t1
+where a <> ( select * from (select a from t1) dt where dt.a > 7)
+order by a limit 2;
+select * from t1;
+pk	a
+2	7
+4	9
+delete from t1;
+insert into t1 values (1,3), (2, 7), (3,1), (4,9);
+prepare stmt from "delete from t1
+where a <> ( select * from (select a from t1) dt where dt.a > 7)
+order by a limit 2";
+execute stmt;
+select * from t1;
+pk	a
+2	7
+4	9
+delete from t1;
+insert into t1 values (1,3), (2, 7), (3,1), (4,9);
+execute stmt;
+select * from t1;
+pk	a
+2	7
+4	9
+deallocate prepare stmt;
+create table t2 (pk int, a int);
+insert into t2 values (1,3), (2, 7), (3,1), (4,9);
+create table t3 (a int);
+insert into t3 VALUES (0),(1);
+explain update t1,t3 set t1.a = 1
+where t1.a=t3.a and
+t1.a = ( select * from (select a from t1) dt where dt.a > 7);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+update t1,t3 set t1.a = 1
+where t1.a=t3.a and
+t1.a = ( select * from (select a from t1) dt where dt.a > 7);
+select * from t1;
+pk	a
+2	7
+4	9
+delete from t1;
+insert into t1 values (1,3), (2, 7), (3,1), (4,9);
+prepare stmt from "update t1,t3 set t1.a = 1
+where t1.a=t3.a and
+t1.a = ( select * from (select a from t1) dt where dt.a > 7)";
+execute stmt;
+select * from t1;
+pk	a
+1	3
+2	7
+3	1
+4	9
+delete from t1;
+insert into t1 value (4,9), (3,1), (1,3);
+execute stmt;
+select * from t1;
+pk	a
+4	9
+3	1
+1	3
+deallocate prepare stmt;
+delete from t1;
+insert into t1 values (1,3), (2, 7), (3,1), (4,9);
+explain update t1,t3 set t1.a = 1
+where t1.a=t3.a and
+t1.a = ( select * from (select a from t2) dt where dt.a > 7);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	4	Using where
+update t1,t3 set t1.a = 1
+where t1.a=t3.a and
+t1.a = ( select * from (select a from t2) dt where dt.a > 7);
+select * from t1;
+pk	a
+1	3
+2	7
+3	1
+4	9
+delete from t1;
+insert into t1 values (1,3), (2, 7), (3,1), (4,9);
+prepare stmt from "update t1,t3 set t1.a = 1
+where t1.a=t3.a and
+t1.a = ( select * from (select a from t2) dt where dt.a > 7)";
+execute stmt;
+select * from t1;
+pk	a
+1	3
+2	7
+3	1
+4	9
+delete from t1;
+insert into t1 value (4,9), (3,1), (1,3);
+execute stmt;
+select * from t1;
+pk	a
+4	9
+3	1
+1	3
+deallocate prepare stmt;
+delete from t1;
+insert into t1 values (1,3), (2, 7), (3,1), (4,9);
+insert into t3 values (9), (10), (7);
+explain delete from t1 using t1,t3
+where t1.a=t3.a and
+t1.a = ( select * from (select a from t1) dt where dt.a > 7);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	5	Using where
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
+delete from t1 using t1,t3
+where t1.a=t3.a and
+t1.a = ( select * from (select a from t1) dt where dt.a > 7);
+select * from t1;
+pk	a
+1	3
+2	7
+3	1
+delete from t1;
+insert into t1 values (1,3), (2, 7), (3,1), (4,9);
+prepare stmt from "delete from t1 using t1,t3
+where t1.a=t3.a and
+t1.a = ( select * from (select a from t1) dt where dt.a > 7)";
+execute stmt;
+select * from t1;
+pk	a
+1	3
+2	7
+3	1
+delete from t1;
+insert into t1 value (4,9), (3,1), (1,3);
+execute stmt;
+select * from t1;
+pk	a
+3	1
+1	3
+deallocate prepare stmt;
+delete from t1;
+insert into t1 values (1,3), (2, 7), (3,1), (4,9);
+explain delete from t1 using t1,t3
+where t1.a=t3.a and
+t1.a = ( select * from (select a from t2) dt where dt.a > 7);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	5	Using where
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	4	Using where
+delete from t1 using t1,t3
+where t1.a=t3.a and
+t1.a = ( select * from (select a from t2) dt where dt.a > 7);
+select * from t1;
+pk	a
+1	3
+2	7
+3	1
+delete from t1;
+insert into t1 values (1,3), (2, 7), (3,1), (4,9);
+prepare stmt from "delete from t1 using t1,t3
+where t1.a=t3.a and
+t1.a = ( select * from (select a from t2) dt where dt.a > 7)";
+execute stmt;
+select * from t1;
+pk	a
+1	3
+2	7
+3	1
+delete from t1;
+insert into t1 value (4,9), (3,1), (1,3);
+execute stmt;
+select * from t1;
+pk	a
+3	1
+1	3
+deallocate prepare stmt;
+drop table t1,t2,t3;
+# End of MariaDB 11.1 tests
diff --git a/mysql-test/main/derived.test b/mysql-test/main/derived.test
index e5f01e15821..dceae7a8c8f 100644
--- a/mysql-test/main/derived.test
+++ b/mysql-test/main/derived.test
@@ -1246,3 +1246,211 @@ drop table t1;
 --echo #
 --echo # End of 11.0 tests
 --echo #
+
+--echo #
+--echo # MDEV-28883: single/multi-table UPDATE/DELETE whose WHERE condition
+--echo #             contains subquery from mergeable derived table
+--echo #             that uses the updated/deleted table
+--echo #
+
+create table t1 (pk int, a int);
+insert into t1 values (1,3), (2, 7), (3,1), (4,9);
+
+let $q1=
+update t1 set a = 10
+  where a = ( select * from (select a from t1) dt where dt.a > 7);
+
+eval explain $q1;
+eval $q1;
+select * from t1;
+delete from t1;
+insert into t1 values (1,3), (2, 7), (3,1), (4,9);
+eval prepare stmt from "$q1";
+execute stmt;
+select * from t1;
+delete from t1;
+insert into t1 value (4,9), (3,1), (1,3);
+execute stmt;
+select * from t1;
+deallocate prepare stmt;
+
+delete from t1;
+insert into t1 value (4,9), (3,1), (1,3);
+
+let $q2=
+update t1 set a = 10
+  where a <> ( select * from (select a from t1) dt where dt.a > 7)
+order by a limit 2;
+eval explain $q2;
+eval $q2;
+select * from t1;
+delete from t1;
+insert into t1 value (4,9), (3,1), (1,3);
+eval prepare stmt from "$q2";
+execute stmt;
+select * from t1;
+delete from t1;
+insert into t1 value (4,9), (3,1), (1,3);
+execute stmt;
+select * from t1;
+deallocate prepare stmt;
+
+delete from t1;
+insert into t1 values (1,3), (2, 7), (3,1), (4,9);
+
+let $q3=
+delete from t1
+  where a = ( select * from (select a from t1) dt where dt.a > 7);
+
+eval explain $q3;
+eval $q3;
+select * from t1;
+delete from t1;
+insert into t1 values (1,3), (2, 7), (3,1), (4,9);
+eval prepare stmt from "$q3";
+execute stmt;
+select * from t1;
+delete from t1;
+insert into t1 value (4,9), (3,1), (1,3);
+execute stmt;
+select * from t1;
+deallocate prepare stmt;
+
+delete from t1;
+insert into t1 value (4,9), (3,1), (1,3);
+
+let $q4=
+delete from t1
+  where a = ( select * from (select a from t1) dt where dt.a > 5)
+returning pk, a;
+
+eval explain $q4;
+eval $q4;
+select * from t1;
+delete from t1;
+insert into t1 value (4,9), (3,1), (1,3);
+eval prepare stmt from "$q4";
+execute stmt;
+select * from t1;
+delete from t1;
+insert into t1 value (4,9), (3,1), (1,3);
+execute stmt;
+select * from t1;
+deallocate prepare stmt;
+
+delete from t1;
+insert into t1 values (1,3), (2, 7), (3,1), (4,9);
+
+let $q5=
+delete from t1
+  where a <> ( select * from (select a from t1) dt where dt.a > 7)
+order by a limit 2;
+eval explain $q5;
+eval $q5;
+select * from t1;
+delete from t1;
+insert into t1 values (1,3), (2, 7), (3,1), (4,9);
+eval prepare stmt from "$q5";
+execute stmt;
+select * from t1;
+delete from t1;
+insert into t1 values (1,3), (2, 7), (3,1), (4,9);
+execute stmt;
+select * from t1;
+deallocate prepare stmt;
+
+create table t2 (pk int, a int);
+insert into t2 values (1,3), (2, 7), (3,1), (4,9);
+create table t3 (a int);
+insert into t3 VALUES (0),(1);
+
+let $q6=
+update t1,t3 set t1.a = 1
+  where t1.a=t3.a and
+        t1.a = ( select * from (select a from t1) dt where dt.a > 7);
+
+eval explain $q6;
+eval $q6;
+select * from t1;
+delete from t1;
+insert into t1 values (1,3), (2, 7), (3,1), (4,9);
+eval prepare stmt from "$q6";
+execute stmt;
+select * from t1;
+delete from t1;
+insert into t1 value (4,9), (3,1), (1,3);
+execute stmt;
+select * from t1;
+deallocate prepare stmt;
+
+delete from t1;
+insert into t1 values (1,3), (2, 7), (3,1), (4,9);
+
+let $q7=
+update t1,t3 set t1.a = 1
+  where t1.a=t3.a and
+        t1.a = ( select * from (select a from t2) dt where dt.a > 7);
+
+eval explain $q7;
+eval $q7;
+select * from t1;
+delete from t1;
+insert into t1 values (1,3), (2, 7), (3,1), (4,9);
+eval prepare stmt from "$q7";
+execute stmt;
+select * from t1;
+delete from t1;
+insert into t1 value (4,9), (3,1), (1,3);
+execute stmt;
+select * from t1;
+deallocate prepare stmt;
+
+delete from t1;
+insert into t1 values (1,3), (2, 7), (3,1), (4,9);
+insert into t3 values (9), (10), (7);
+
+let $q8=
+delete from t1 using t1,t3
+  where t1.a=t3.a and
+        t1.a = ( select * from (select a from t1) dt where dt.a > 7);
+
+eval explain $q8;
+eval $q8;
+select * from t1;
+delete from t1;
+insert into t1 values (1,3), (2, 7), (3,1), (4,9);
+eval prepare stmt from "$q8";
+execute stmt;
+select * from t1;
+delete from t1;
+insert into t1 value (4,9), (3,1), (1,3);
+execute stmt;
+select * from t1;
+deallocate prepare stmt;
+
+delete from t1;
+insert into t1 values (1,3), (2, 7), (3,1), (4,9);
+
+let $q9=
+delete from t1 using t1,t3
+  where t1.a=t3.a and
+        t1.a = ( select * from (select a from t2) dt where dt.a > 7);
+
+eval explain $q9;
+eval $q9;
+select * from t1;
+delete from t1;
+insert into t1 values (1,3), (2, 7), (3,1), (4,9);
+eval prepare stmt from "$q9";
+execute stmt;
+select * from t1;
+delete from t1;
+insert into t1 value (4,9), (3,1), (1,3);
+execute stmt;
+select * from t1;
+deallocate prepare stmt;
+
+
+drop table t1,t2,t3;
+
+--echo # End of MariaDB 11.1 tests
diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result
index ddc6505a178..7523e2883f7 100644
--- a/mysql-test/main/derived_cond_pushdown.result
+++ b/mysql-test/main/derived_cond_pushdown.result
@@ -13098,7 +13098,7 @@ DROP TABLE t1;
 #
 CREATE TABLE t1 (f1 text, f2 int);
 INSERT INTO t1 VALUES ('x',1), ('y',2);
-CREATE VIEW v1 AS SELECT f2 FROM ( SELECT f2 FROM t1 ) AS t;
+CREATE VIEW v1 AS SELECT f2 FROM ( SELECT f2 FROM t1 GROUP BY f2) AS t;
 UPDATE v1, t1 SET t1.f1 = 'z' WHERE v1.f2 < 2 AND t1.f2 = v1.f2;
 EXPLAIN FORMAT=JSON UPDATE v1, t1 SET t1.f1 = 'z' WHERE v1.f2 < 2 AND t1.f2 = v1.f2;
 EXPLAIN
@@ -13134,6 +13134,7 @@ EXPLAIN
           "materialized": {
             "query_block": {
               "select_id": 3,
+<<<<<<< 2ad65c4dbcb291867725d50d1f53c8da8549afb3
               "cost": "COST_REPLACED",
               "nested_loop": [
                 {
@@ -13146,8 +13147,23 @@ EXPLAIN
                     "filtered": 100,
                     "attached_condition": "t1.f2 < 2"
                   }
+=======
+              "filesort": {
+                "sort_key": "t1.f2",
+                "temporary_table": {
+                  "nested_loop": [
+                    {
+                      "table": {
+                        "table_name": "t1",
+                        "access_type": "ALL",
+                        "rows": 2,
+                        "filtered": 100,
+                        "attached_condition": "t1.f2 < 2"
+                      }
+                    }
+                  ]
                 }
-              ]
+              }
             }
           }
         }
diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test
index 26b44488e1d..47c1f9c20f2 100644
--- a/mysql-test/main/derived_cond_pushdown.test
+++ b/mysql-test/main/derived_cond_pushdown.test
@@ -2219,7 +2219,7 @@ DROP TABLE t1;
 CREATE TABLE t1 (f1 text, f2 int);
 INSERT INTO t1 VALUES ('x',1), ('y',2);
 
-CREATE VIEW v1 AS SELECT f2 FROM ( SELECT f2 FROM t1 ) AS t;
+CREATE VIEW v1 AS SELECT f2 FROM ( SELECT f2 FROM t1 GROUP BY f2) AS t;
 let $q1 =
 UPDATE v1, t1 SET t1.f1 = 'z' WHERE v1.f2 < 2 AND t1.f2 = v1.f2;
 eval $q1;
diff --git a/mysql-test/main/multi_update.result b/mysql-test/main/multi_update.result
index bf011f66be0..06e0d1442a4 100644
--- a/mysql-test/main/multi_update.result
+++ b/mysql-test/main/multi_update.result
@@ -441,12 +441,10 @@ create table t1 (col1 int);
 create table t2 (col1 int);
 update t1,t2 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1;
 delete t1 from t1,t2 where t1.col1 < (select max(col1) from t1) and t1.col1 = t2.col1;
-ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data
 drop table t1,t2;
 create table t1(a int);
 create table t2(a int);
 delete from t1,t2 using t1,t2 where t1.a=(select a from t1);
-ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data
 drop table t1, t2;
 create table t1 (a int, b int);
 insert into t1 values (1, 2), (2, 3), (3, 4);
diff --git a/mysql-test/main/multi_update.test b/mysql-test/main/multi_update.test
index b9ceb458db6..9f42765b18c 100644
--- a/mysql-test/main/multi_update.test
+++ b/mysql-test/main/multi_update.test
@@ -391,7 +391,6 @@ drop table t1, t2, t3;
 create table t1 (col1 int);
 create table t2 (col1 int);
 update t1,t2 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1;
--- error ER_UPDATE_TABLE_USED
 delete t1 from t1,t2 where t1.col1 < (select max(col1) from t1) and t1.col1 = t2.col1;
 drop table t1,t2;
 
@@ -400,7 +399,6 @@ drop table t1,t2;
 #
 create table t1(a int);
 create table t2(a int);
---error ER_UPDATE_TABLE_USED
 delete from t1,t2 using t1,t2 where t1.a=(select a from t1);
 drop table t1, t2;
 # End of 4.1 tests
diff --git a/mysql-test/main/subselect.result b/mysql-test/main/subselect.result
index 2b3050c3ca2..9e486179719 100644
--- a/mysql-test/main/subselect.result
+++ b/mysql-test/main/subselect.result
@@ -634,7 +634,6 @@ a	b
 22	11
 2	12
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
-ERROR HY000: Table 't12' is specified twice, both as a target for 'DELETE' and as a separate source for data
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2);
 ERROR 21000: Subquery returns more than 1 row
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a);
diff --git a/mysql-test/main/subselect.test b/mysql-test/main/subselect.test
index 48ad7402755..8fefa39fb3b 100644
--- a/mysql-test/main/subselect.test
+++ b/mysql-test/main/subselect.test
@@ -386,7 +386,6 @@ insert into t12 values (33, 10),(22, 11),(2, 12);
 insert into t2 values (1, 21),(2, 12),(3, 23);
 select * from t11;
 select * from t12;
--- error ER_UPDATE_TABLE_USED
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
 -- error ER_SUBQUERY_NO_1_ROW
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2);
diff --git a/mysql-test/main/subselect_no_exists_to_in.result b/mysql-test/main/subselect_no_exists_to_in.result
index 3a144e70c75..37a503dff6b 100644
--- a/mysql-test/main/subselect_no_exists_to_in.result
+++ b/mysql-test/main/subselect_no_exists_to_in.result
@@ -638,7 +638,6 @@ a	b
 22	11
 2	12
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
-ERROR HY000: Table 't12' is specified twice, both as a target for 'DELETE' and as a separate source for data
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2);
 ERROR 21000: Subquery returns more than 1 row
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a);
diff --git a/mysql-test/main/subselect_no_mat.result b/mysql-test/main/subselect_no_mat.result
index ca68e4d7e03..3832365ed86 100644
--- a/mysql-test/main/subselect_no_mat.result
+++ b/mysql-test/main/subselect_no_mat.result
@@ -641,7 +641,6 @@ a	b
 22	11
 2	12
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
-ERROR HY000: Table 't12' is specified twice, both as a target for 'DELETE' and as a separate source for data
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2);
 ERROR 21000: Subquery returns more than 1 row
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a);
diff --git a/mysql-test/main/subselect_no_opts.result b/mysql-test/main/subselect_no_opts.result
index f9561178ca5..d75c421e3aa 100644
--- a/mysql-test/main/subselect_no_opts.result
+++ b/mysql-test/main/subselect_no_opts.result
@@ -637,7 +637,6 @@ a	b
 22	11
 2	12
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
-ERROR HY000: Table 't12' is specified twice, both as a target for 'DELETE' and as a separate source for data
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2);
 ERROR 21000: Subquery returns more than 1 row
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a);
diff --git a/mysql-test/main/subselect_no_scache.result b/mysql-test/main/subselect_no_scache.result
index 813ca78703a..3bce10e803c 100644
--- a/mysql-test/main/subselect_no_scache.result
+++ b/mysql-test/main/subselect_no_scache.result
@@ -640,7 +640,6 @@ a	b
 22	11
 2	12
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
-ERROR HY000: Table 't12' is specified twice, both as a target for 'DELETE' and as a separate source for data
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2);
 ERROR 21000: Subquery returns more than 1 row
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a);
diff --git a/mysql-test/main/subselect_no_semijoin.result b/mysql-test/main/subselect_no_semijoin.result
index 19dae33c5c1..c34dfe69ea9 100644
--- a/mysql-test/main/subselect_no_semijoin.result
+++ b/mysql-test/main/subselect_no_semijoin.result
@@ -637,7 +637,6 @@ a	b
 22	11
 2	12
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t12 where t11.a = t12.a);
-ERROR HY000: Table 't12' is specified twice, both as a target for 'DELETE' and as a separate source for data
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2);
 ERROR 21000: Subquery returns more than 1 row
 delete t11.*, t12.* from t11,t12 where t11.a = t12.a and t11.b = (select b from t2 where t11.a = t2.a);