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>

27 files changed, 1106 insertions, 79 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 62bcfaed57b..97616d39812 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 77d80405b3b..ce156f1a859 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 12c72545f73..32a63585c1c 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 8083057eddf..336936ddb35 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);
diff --git a/mysql-test/suite/engines/iuds/r/update_delete_number.result b/mysql-test/suite/engines/iuds/r/update_delete_number.result
index 1cd2a62cb56..1534f93b436 100644
--- a/mysql-test/suite/engines/iuds/r/update_delete_number.result
+++ b/mysql-test/suite/engines/iuds/r/update_delete_number.result
@@ -739,8 +739,21 @@ c1	c2	c3	c1	c2	c3
 DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2;
 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
 c1	c2	c3	c1	c2	c3
-DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1);
-ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data
+TRUNCATE TABLE t1;
+TRUNCATE TABLE t2;
+INSERT INTO t1 VALUES(254,127,1),(0,-128,2),(1,127,3),(3,NULL,5);
+INSERT INTO t2 VALUES(127,255,1),(127,1,2),(-128,0,3),(-1,NULL,5);
+DELETE FROM t1,t2 using t1,t2
+where t1.c1=(select c1 from t1 where c2 < 10) and t2.c2 < 10;
+SELECT * FROM t1;
+c1	c2	c3
+1	127	3
+254	127	1
+3	NULL	5
+SELECT * FROM t2;
+c1	c2	c3
+-1	NULL	5
+127	255	1
 CREATE TABLE t3(c1 INT UNSIGNED NOT NULL PRIMARY KEY, c2 INT SIGNED NULL, c3 INT);
 CREATE TABLE t4(c1 INT UNSIGNED, c2 INT);
 INSERT INTO t3 VALUES(200,126,1),(250,-127,2);
@@ -981,7 +994,6 @@ create table mt1 (col1 int);
 create table mt2 (col1 int);
 update mt1,mt2 set mt1.col1 = (select max(col1) from mt1) where mt1.col1 = mt2.col1;
 delete mt1 from mt1,mt2 where mt1.col1 < (select max(col1) from mt1) and mt1.col1 = mt2.col1;
-ERROR HY000: Table 'mt1' is specified twice, both as a target for 'DELETE' and as a separate source for data
 drop table mt1,mt2;
 CREATE TABLE IF NOT EXISTS `mt1` (`id` int(11) NOT NULL auto_increment, `tst` text, `tsmt1` text, PRIMARY KEY  (`id`));
 CREATE TABLE IF NOT EXISTS `mt2` (`ID` int(11) NOT NULL auto_increment, `ParId` int(11) default NULL, `tst` text, `tsmt1` text, PRIMARY KEY  (`ID`), KEY `IX_ParId_mt2` (`ParId`),  FOREIGN KEY (`ParId`) REFERENCES `mt1` (`id`));
@@ -1852,7 +1864,6 @@ DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2;
 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
 c1	c2	c3	c1	c2	c3
 DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1);
-ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data
 CREATE TABLE t3(c1 TINYINT UNSIGNED NOT NULL PRIMARY KEY, c2 TINYINT SIGNED NULL, c3 INT);
 CREATE TABLE t4(c1 TINYINT UNSIGNED, c2 INT);
 INSERT INTO t3 VALUES(200,126,1),(250,-127,2);
@@ -2599,7 +2610,6 @@ DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2;
 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
 c1	c2	c3	c1	c2	c3
 DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1);
-ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data
 CREATE TABLE t3(c1 SMALLINT UNSIGNED NOT NULL PRIMARY KEY, c2 SMALLINT SIGNED NULL, c3 INT);
 CREATE TABLE t4(c1 SMALLINT UNSIGNED, c2 INT);
 INSERT INTO t3 VALUES(200,126,1),(250,-127,2);
@@ -3346,7 +3356,6 @@ DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2;
 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
 c1	c2	c3	c1	c2	c3
 DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1);
-ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data
 CREATE TABLE t3(c1 MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY, c2 MEDIUMINT SIGNED NULL, c3 INT);
 CREATE TABLE t4(c1 MEDIUMINT UNSIGNED, c2 INT);
 INSERT INTO t3 VALUES(200,126,1),(250,-127,2);
@@ -4093,7 +4102,6 @@ DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2;
 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
 c1	c2	c3	c1	c2	c3
 DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1);
-ERROR HY000: Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data
 CREATE TABLE t3(c1 BIGINT UNSIGNED NOT NULL PRIMARY KEY, c2 BIGINT SIGNED NULL, c3 INT);
 CREATE TABLE t4(c1 BIGINT UNSIGNED, c2 INT);
 INSERT INTO t3 VALUES(200,126,1),(250,-127,2);
diff --git a/mysql-test/suite/engines/iuds/t/update_delete_number.test b/mysql-test/suite/engines/iuds/t/update_delete_number.test
index ce3f90140b2..4347d0606ae 100644
--- a/mysql-test/suite/engines/iuds/t/update_delete_number.test
+++ b/mysql-test/suite/engines/iuds/t/update_delete_number.test
@@ -285,8 +285,18 @@ SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
 DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2;
 --sorted_result
 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
---error ER_UPDATE_TABLE_USED
-DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1);
+TRUNCATE TABLE t1;
+TRUNCATE TABLE t2;
+INSERT INTO t1 VALUES(254,127,1),(0,-128,2),(1,127,3),(3,NULL,5);
+INSERT INTO t2 VALUES(127,255,1),(127,1,2),(-128,0,3),(-1,NULL,5);
+# After the patch for MDEV-28883 this should not report
+# ER_UPDATE_TABLE_USED anymore
+DELETE FROM t1,t2 using t1,t2
+  where t1.c1=(select c1 from t1 where c2 < 10) and t2.c2 < 10;
+--sorted_result
+SELECT * FROM t1;
+--sorted_result
+SELECT * FROM t2;
 
 # eq-ref join
 CREATE TABLE t3(c1 INT UNSIGNED NOT NULL PRIMARY KEY, c2 INT SIGNED NULL, c3 INT); 
@@ -496,7 +506,7 @@ drop table mt1, mt2, mt3;
 create table mt1 (col1 int);
 create table mt2 (col1 int);
 update mt1,mt2 set mt1.col1 = (select max(col1) from mt1) where mt1.col1 = mt2.col1;
--- error ER_UPDATE_TABLE_USED
+# -- error ER_UPDATE_TABLE_USED
 delete mt1 from mt1,mt2 where mt1.col1 < (select max(col1) from mt1) and mt1.col1 = mt2.col1;
 drop table mt1,mt2;
 
@@ -865,7 +875,6 @@ SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
 DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2;
 --sorted_result
 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
---error ER_UPDATE_TABLE_USED
 DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1);
 
 # eq-ref join
@@ -1166,7 +1175,6 @@ SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
 DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2;
 --sorted_result
 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
---error ER_UPDATE_TABLE_USED
 DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1);
 
 # eq-ref join
@@ -1467,7 +1475,6 @@ SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
 DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2;
 --sorted_result
 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
---error ER_UPDATE_TABLE_USED
 DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1);
 
 # eq-ref join
@@ -1768,7 +1775,6 @@ SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
 DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a2.c1=a1.c2;
 --sorted_result
 SELECT * FROM t1,t2 WHERE t2.c1=t1.c2;
---error ER_UPDATE_TABLE_USED
 DELETE FROM t1,t2 using t1,t2 where t1.c1=(select c1 from t1);
 
 # eq-ref join
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index 27d53474292..926af6b45cb 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -30,6 +30,8 @@
 #include "sql_base.h"
 #include "sql_const.h"
 #include "sql_select.h"
+#include "sql_update.h"  // class Sql_cmd_update
+#include "sql_delete.h"  // class Sql_cmd_delete
 #include "filesort.h"
 #include "opt_subselect.h"
 #include "sql_test.h"
@@ -534,6 +536,48 @@ bool is_materialization_applicable(THD *thd, Item_in_subselect *in_subs,
   return FALSE;
 }
 
+/**
+  @brief Check whether an IN subquery must be excluded from conversion to SJ
+
+  @param thd  global context the processed statement
+  @returns true if the IN subquery must be excluded from conversion to SJ
+
+  @note
+  Currently a top level IN subquery of an delete statement is not converted
+  to SJ if the statement contains ORDER BY ... LIMIT  or contains RETURNING.
+
+  @todo
+    The disjunctive members
+      !((Sql_cmd_update *) cmd)->is_multitable()
+      !((Sql_cmd_delete *) cmd)->is_multitable()
+    will be removed when conversions of IN predicands to semi-joins are
+    fully supported for single-table UPDATE/DELETE statements.
+*/
+
+bool SELECT_LEX::is_sj_conversion_prohibited(THD *thd)
+{
+  DBUG_ASSERT(master_unit()->item->substype() == Item_subselect::IN_SUBS);
+
+  SELECT_LEX *outer_sl= outer_select();
+  if (outer_sl->outer_select())
+    return false;
+
+  Sql_cmd *cmd= thd->lex->m_sql_cmd;
+
+  switch (thd->lex->sql_command) {
+  case SQLCOM_UPDATE:
+    return
+      !((Sql_cmd_update *) cmd)->is_multitable() ||
+      ((Sql_cmd_update *) cmd)->processing_as_multitable_update_prohibited(thd);
+  case SQLCOM_DELETE:
+    return
+      !((Sql_cmd_delete *) cmd)->is_multitable() ||
+      ((Sql_cmd_delete *) cmd)->processing_as_multitable_delete_prohibited(thd);
+  default:
+    return false;
+  }
+}
+
 
 /*
   Check if we need JOIN::prepare()-phase subquery rewrites and if yes, do them
@@ -677,9 +721,8 @@ int check_and_do_in_subquery_rewrites(JOIN *join)
         3. Subquery does not have GROUP BY or ORDER BY
         4. Subquery does not use aggregate functions or HAVING
         5. Subquery predicate is at the AND-top-level of ON/WHERE clause
-        6. We are not in a subquery of a single table UPDATE/DELETE that 
-             doesn't have a JOIN (TODO: We should handle this at some
-             point by switching to multi-table UPDATE/DELETE)
+        6. We are not in a subquery of a single-table UPDATE/DELETE that
+           does not allow conversion to multi-table UPDATE/DELETE
         7. We're not in a table-less subquery like "SELECT 1"
         8. No execution method was already chosen (by a prepared statement)
         9. Parent select is not a table-less select
@@ -698,9 +741,7 @@ int check_and_do_in_subquery_rewrites(JOIN *join)
         !select_lex->group_list.elements && !join->order &&           // 3
         !join->having && !select_lex->with_sum_func &&                // 4
         in_subs->emb_on_expr_nest &&                                  // 5
-        select_lex->outer_select()->join &&                           // 6
-        (!thd->lex->m_sql_cmd ||
-	 thd->lex->m_sql_cmd->sql_command_code() == SQLCOM_UPDATE_MULTI) &&
+        !select_lex->is_sj_conversion_prohibited(thd) &&              // 6
         parent_unit->first_select()->leaf_tables.elements &&          // 7
         !in_subs->has_strategy() &&                                   // 8
         select_lex->outer_select()->table_list.first &&               // 9
@@ -760,7 +801,8 @@ int check_and_do_in_subquery_rewrites(JOIN *join)
       */
       if (in_subs && !in_subs->has_strategy())
       {
-        if (is_materialization_applicable(thd, in_subs, select_lex))
+        if (!select_lex->is_sj_conversion_prohibited(thd) &&
+            is_materialization_applicable(thd, in_subs, select_lex))
         {
           in_subs->add_strategy(SUBS_MATERIALIZATION);
 
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 4b253386a90..4019de6d36d 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -47,6 +47,8 @@
 #include "sql_prepare.h"
 #include "sql_statistics.h"
 #include "sql_cte.h"
+#include "sql_update.h"  // class Sql_cmd_update
+#include "sql_delete.h"  // class Sql_cmd_delete
 #include <m_ctype.h>
 #include <my_dir.h>
 #include <hash.h>
@@ -1194,16 +1196,42 @@ retry:
       We come here for queries of type:
       INSERT INTO t1 (SELECT tmp.a FROM (select * FROM t1) as tmp);
 
-      Try to fix by materializing the derived table
+      Try to fix by materializing the derived table if one can't do without it.
     */
     TABLE_LIST *derived=  res->belong_to_derived;
     if (derived->is_merged_derived() && !derived->derived->is_excluded())
     {
-      DBUG_PRINT("info",
+      bool materialize= true;
+      if (thd->lex->sql_command == SQLCOM_UPDATE)
+      {
+        Sql_cmd_update *cmd= (Sql_cmd_update *) (thd->lex->m_sql_cmd);
+        if (cmd->is_multitable())
+          materialize= false;
+        else if (!cmd->processing_as_multitable_update_prohibited(thd))
+	{
+          cmd->set_as_multitable();
+          materialize= false;
+        }
+      }
+      else if (thd->lex->sql_command == SQLCOM_DELETE)
+      {
+        Sql_cmd_delete *cmd= (Sql_cmd_delete *) (thd->lex->m_sql_cmd);
+        if (cmd->is_multitable())
+          materialize= false;
+        if (!cmd->processing_as_multitable_delete_prohibited(thd))
+	{
+          cmd->set_as_multitable();
+          materialize= false;
+        }
+      }
+      if (materialize)
+      {
+        DBUG_PRINT("info",
                  ("convert merged to materialization to resolve the conflict"));
-      derived->change_refs_to_fields();
-      derived->set_materialized_derived();
-      goto retry;
+        derived->change_refs_to_fields();
+        derived->set_materialized_derived();
+        goto retry;
+      }
     }
   }
   DBUG_RETURN(res);
diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc
index 0f926ab65b9..aad463b6a53 100644
--- a/sql/sql_delete.cc
+++ b/sql/sql_delete.cc
@@ -1117,6 +1117,8 @@ multi_delete::~multi_delete()
        table_being_deleted= table_being_deleted->next_local)
   {
     TABLE *table= table_being_deleted->table;
+    if (!table)
+      continue;
     table->no_keyread=0;
     table->no_cache= 0;
   }
@@ -1448,6 +1450,34 @@ bool multi_delete::send_eof()
 }
 
 
+/**
+  @brief Check whether processing to multi-table delete is prohibited
+
+  @param thd  global context the processed statement
+  @returns true if processing as multitable is prohibited, false otherwise
+
+  @todo
+  Introduce handler level flag for storage engines that would prohibit
+  such conversion for any single-table delete.
+*/
+
+bool Sql_cmd_delete::processing_as_multitable_delete_prohibited(THD *thd)
+{
+  SELECT_LEX *const select_lex = thd->lex->first_select_lex();
+  return
+    ((select_lex->order_list.elements &&
+      select_lex->limit_params.select_limit) ||
+     thd->lex->has_returning());
+}
+
+
+/**
+  @brief Perform precheck of table privileges for delete statements
+
+  @param thd  global context the processed statement
+  @returns false on success, true on error
+*/
+
 bool Sql_cmd_delete::precheck(THD *thd)
 {
   if (!multitable)
@@ -1551,6 +1581,20 @@ bool Sql_cmd_delete::prepare_inner(THD *thd)
       DBUG_RETURN(true);
     }
 
+    if (!multitable)
+    {
+      TABLE_LIST *update_source_table= 0;
+      if (((update_source_table=unique_table(thd, table_list,
+                                             table_list->next_global, 0)) ||
+          table_list->is_multitable()))
+      {
+        DBUG_ASSERT(update_source_table || table_list->view != 0);
+        if (!table_list->is_multitable() &&
+            !processing_as_multitable_delete_prohibited(thd))
+          multitable= true;
+      }
+    }
+
     if (table_list->has_period())
     {
       if (table_list->is_view_or_derived())
@@ -1635,25 +1679,6 @@ bool Sql_cmd_delete::prepare_inner(THD *thd)
       }
     }
 
-    for (target_tbl= (TABLE_LIST*) aux_tables;
-         target_tbl;
-         target_tbl= target_tbl->next_local)
-    {
-      /*
-        Check that table from which we delete is not used somewhere
-        inside subqueries/view.
-      */
-      {
-        TABLE_LIST *duplicate;
-        if ((duplicate= unique_table(thd, target_tbl->correspondent_table,
-                                     lex->query_tables, 0)))
-        {
-          update_non_unique_table_error(target_tbl->correspondent_table,
-                                        "DELETE", duplicate);
-          DBUG_RETURN(TRUE);
-        }
-      }
-    }
     /*
       Reset the exclude flag to false so it doesn't interfare
       with further calls to unique_table
diff --git a/sql/sql_delete.h b/sql/sql_delete.h
index e1d5044ef43..463cb198d03 100644
--- a/sql/sql_delete.h
+++ b/sql/sql_delete.h
@@ -44,11 +44,13 @@ class Sql_cmd_delete final : public Sql_cmd_dml
 {
 public:
   Sql_cmd_delete(bool multitable_arg)
-    :  multitable(multitable_arg), save_protocol(NULL) {}
+    : orig_multitable(multitable_arg), multitable(multitable_arg),
+      save_protocol(NULL)
+  {}
 
   enum_sql_command sql_command_code() const override
   {
-    return multitable ? SQLCOM_DELETE_MULTI : SQLCOM_DELETE;
+    return orig_multitable ? SQLCOM_DELETE_MULTI : SQLCOM_DELETE;
   }
 
   DML_prelocking_strategy *get_dml_prelocking_strategy()
@@ -56,6 +58,12 @@ public:
     return &dml_prelocking_strategy;
   }
 
+  bool processing_as_multitable_delete_prohibited(THD *thd);
+
+  bool is_multitable() { return multitable; }
+
+  void set_as_multitable() { multitable= true; }
+
 protected:
   /**
     @brief Perform precheck of table privileges for delete statements
@@ -78,6 +86,9 @@ protected:
   */
   bool delete_from_single_table(THD *thd);
 
+  /* Original value of the 'multitable' flag set by constructor */
+  const bool orig_multitable;
+
   /*
     True if the statement is a multitable delete or converted to such.
     For a single-table delete this flag is set to true if the statement
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 60268e938f4..fc8e4bbd867 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -40,6 +40,8 @@
 #ifdef WITH_WSREP
 #include "mysql/service_wsrep.h"
 #endif
+#include "sql_update.h"                        // class Sql_cmd_update
+#include "sql_delete.h"                        // class Sql_cmd_delete
 
 void LEX::parse_error(uint err_number)
 {
@@ -4026,9 +4028,8 @@ bool LEX::can_use_merged()
   SYNOPSIS
     LEX::can_not_use_merged()
 
-  @param no_update_or_delete Set to 1 if we can't use merge with multiple-table
-                             updates, like when used from
-                             TALE_LIST::init_derived()
+  @param forced_no_merge_for_update_delete Set to 1 if we can't use merge with
+                                           multiple-table updates/deletes
 
   DESCRIPTION
     Temporary table algorithm will be used on all SELECT levels for queries
@@ -4039,7 +4040,7 @@ bool LEX::can_use_merged()
     TRUE  - VIEWs with MERGE algorithms can be used
 */
 
-bool LEX::can_not_use_merged(bool no_update_or_delete)
+bool LEX::can_not_use_merged(bool forced_no_merge_for_update_delete)
 {
   switch (sql_command) {
   case SQLCOM_CREATE_VIEW:
@@ -4053,18 +4054,29 @@ bool LEX::can_not_use_merged(bool no_update_or_delete)
     return TRUE;
 
   case SQLCOM_UPDATE_MULTI:
-  case SQLCOM_DELETE_MULTI:
-    if (no_update_or_delete)
+    if (forced_no_merge_for_update_delete)
       return TRUE;
     /* Fall through */
 
   case SQLCOM_UPDATE:
-    if (no_update_or_delete && m_sql_cmd &&
-        (m_sql_cmd->sql_command_code() == SQLCOM_UPDATE_MULTI ||
+    if (forced_no_merge_for_update_delete &&
+        (((Sql_cmd_update *) m_sql_cmd)->is_multitable() ||
          query_tables->is_multitable()))
       return TRUE;
+    return FALSE;
+
+  case SQLCOM_DELETE_MULTI:
+    if (forced_no_merge_for_update_delete)
+      return TRUE;
     /* Fall through */
 
+  case SQLCOM_DELETE:
+    if (forced_no_merge_for_update_delete &&
+        (((Sql_cmd_delete *) m_sql_cmd)->is_multitable() ||
+         query_tables->is_multitable()))
+      return TRUE;
+    return FALSE;
+
   default:
     return FALSE;
   }
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 941fedd3580..5cfdfa12e7c 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -1652,6 +1652,7 @@ public:
   void lex_start(LEX *plex);
   bool is_unit_nest() { return (nest_flags & UNIT_NEST_FL); }
   void mark_as_unit_nest() { nest_flags= UNIT_NEST_FL; }
+  bool is_sj_conversion_prohibited(THD *thd);
 };
 typedef class st_select_lex SELECT_LEX;
 
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index 7781e0101d6..90f78a298c8 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -2812,6 +2812,23 @@ bool multi_update::send_eof()
 
 
 /**
+  @brief Check whether conversion to multi-table update is prohibited
+
+  @param thd  global context the processed statement
+  @returns true if conversion is prohibited, false otherwise
+
+  @todo
+  Introduce handler level flag for storage engines that would prohibit
+  such conversion for any single-table update.
+*/
+
+bool Sql_cmd_update::processing_as_multitable_update_prohibited(THD *thd)
+{
+  return false;
+}
+
+
+/**
   @brief Perform precheck of table privileges for update statements
 
   @param thd  global context the processed statement
@@ -2894,7 +2911,9 @@ bool Sql_cmd_update::prepare_inner(THD *thd)
                  "updating and querying the same temporal periods table");
         DBUG_RETURN(TRUE);
       }
-      multitable= true;
+      if (!table_list->is_multitable() &&
+          !processing_as_multitable_update_prohibited(thd))
+        multitable= true;
     }
   }
 
diff --git a/sql/sql_update.h b/sql/sql_update.h
index d0fc7cb01e1..bd7d58cf97d 100644
--- a/sql/sql_update.h
+++ b/sql/sql_update.h
@@ -46,12 +46,12 @@ class Sql_cmd_update final : public Sql_cmd_dml
 {
 public:
   Sql_cmd_update(bool multitable_arg)
-      : multitable(multitable_arg)
-  { }
+    : orig_multitable(multitable_arg), multitable(multitable_arg)
+  {}
 
   enum_sql_command sql_command_code() const override
   {
-    return multitable ? SQLCOM_UPDATE_MULTI : SQLCOM_UPDATE;
+    return orig_multitable ? SQLCOM_UPDATE_MULTI : SQLCOM_UPDATE;
   }
 
   DML_prelocking_strategy *get_dml_prelocking_strategy()
@@ -59,6 +59,12 @@ public:
     return &multiupdate_prelocking_strategy;
   }
 
+  bool processing_as_multitable_update_prohibited(THD *thd);
+
+  bool is_multitable() { return multitable; }
+
+  void set_as_multitable() { multitable= true; }
+
 protected:
   /**
     @brief Perform precheck of table privileges for update statements
@@ -82,6 +88,9 @@ private:
   */
   bool update_single_table(THD *thd);
 
+  /* Original value of the 'multitable' flag set by constructor */
+  const bool orig_multitable;
+
   /*
     True if the statement is a multi-table update or converted to such.
     For a single-table update this flag is set to true if the statement
@@ -95,7 +104,6 @@ private:
  public:
   /* The list of the updating expressions used in the set clause */
   List<Item> *update_value_list;
-
 };
 
 #endif /* SQL_UPDATE_INCLUDED */
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index f70e5d895cf..403377e1f71 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -13392,8 +13392,23 @@ delete_single_table:
                                            YYPS->m_lock_type,
                                            YYPS->m_mdl_type,
                                            NULL,
+                                           0)))
+              MYSQL_YYABORT;
+            Select->table_list.save_and_clear(&Lex->auxiliary_table_list);
+            /* Save the number of auxiliary tables */
+            Lex->table_count= 1;
+
+            Lex->query_tables= 0;
+            Lex->query_tables_last= &Lex->query_tables;
+            if (unlikely(!Select->
+                         add_table_to_list(thd, $2, NULL, TL_OPTION_UPDATING,
+                                           YYPS->m_lock_type,
+                                           YYPS->m_mdl_type,
+                                           NULL,
                                            $3)))
               MYSQL_YYABORT;
+            Lex->auxiliary_table_list.first->correspondent_table=
+              Lex->query_tables;
             YYPS->m_lock_type= TL_READ_DEFAULT;
             YYPS->m_mdl_type= MDL_SHARED_READ;
           }
diff --git a/sql/table.cc b/sql/table.cc
index 9ba9817d587..a8eeeb53d72 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -7213,7 +7213,8 @@ void Field_iterator_table_ref::set_field_iterator()
                        table_ref->alias.str));
   }
   /* This is a merge view, so use field_translation. */
-  else if (table_ref->field_translation)
+  else if (table_ref->field_translation &&
+           !table_ref->is_materialized_derived())
   {
     DBUG_ASSERT(table_ref->is_merged_derived());
     field_it= &view_field_it;
@@ -7223,7 +7224,7 @@ void Field_iterator_table_ref::set_field_iterator()
   /* This is a base table or stored view. */
   else
   {
-    DBUG_ASSERT(table_ref->table || table_ref->view);
+    DBUG_ASSERT(table_ref->table || table_ref->is_materialized_derived());
     field_it= &table_field_it;
     DBUG_PRINT("info", ("field_it for '%s' is Field_iterator_table",
                         table_ref->alias.str));
@@ -9788,13 +9789,16 @@ bool TABLE_LIST::init_derived(THD *thd, bool init_view)
       !derived_table_optimization_done(this))
   {
     /* A subquery might be forced to be materialized due to a side-effect. */
+    bool forced_no_merge_for_update_delete=
+           belong_to_view ? belong_to_view->updating :
+                           !unit->outer_select()->outer_select();
     if (!is_materialized_derived() && first_select->is_mergeable() &&
         (unit->outer_select() && !unit->outer_select()->with_rownum) &&
         (!thd->lex->with_rownum ||
          (!first_select->group_list.elements &&
           !first_select->order_list.elements)) &&
         optimizer_flag(thd, OPTIMIZER_SWITCH_DERIVED_MERGE) &&
-        !thd->lex->can_not_use_merged(1) &&
+        !thd->lex->can_not_use_merged(forced_no_merge_for_update_delete) &&
         !is_recursive_with_table())
       set_merged_derived();
     else