MDEV-29428 Incorrect result for delete with "order by" clause

ORDER BY clause without LIMIT clause can be removed from DELETE statements.

2 files changed, 89 insertions, 0 deletions

diff --git a/mysql-test/main/delete.result b/mysql-test/main/delete.result
index 900c14c5578..d67c84d8ea2 100644
--- a/mysql-test/main/delete.result
+++ b/mysql-test/main/delete.result
@@ -564,3 +564,50 @@ having t3.a > any (select t2.b from t2
 where t2.b*10 < sum(t3.b)));
 drop table t1,t2,t3;
 End of 10.4 tests
+#
+# MDEV-29428: DELETE with ORDER BY without LIMIT clause
+#
+create table t1 (c1 integer, c2 integer, c3 integer);
+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);
+create temporary table t select * from t1;
+explain delete from t1 order by c2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	8	Deleting all rows
+delete from t1 order by c2;
+select *from t1;
+c1	c2	c3
+delete from t1;
+insert into t1 select * from t;
+explain delete from t1
+where exists (select 'X' from t1 a where a.c2 = t1.c2) and 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
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+delete from t1
+where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+select *from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+delete from t1;
+insert into t1 select * from t;
+explain delete from t1
+where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3
+order by c2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+2	DEPENDENT SUBQUERY	a	ALL	NULL	NULL	NULL	NULL	8	Using where
+delete from t1
+where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3
+order by c2;
+select *from t1;
+c1	c2	c3
+1	1	1
+1	2	2
+2	1	4
+2	2	5
+drop table t1;
+End of 11.1 tests
diff --git a/mysql-test/main/delete.test b/mysql-test/main/delete.test
index 733384193e5..a4057666818 100644
--- a/mysql-test/main/delete.test
+++ b/mysql-test/main/delete.test
@@ -624,3 +624,45 @@ update t1 set t1.a=t1.a+10
 drop table t1,t2,t3;
 
 --echo End of 10.4 tests
+
+--echo #
+--echo # MDEV-29428: DELETE with ORDER BY without LIMIT clause
+--echo #
+
+create table t1 (c1 integer, c2 integer, c3 integer);
+
+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);
+
+create temporary table t select * from t1;
+
+let $q1=
+delete from t1 order by c2;
+eval explain $q1;
+eval $q1;
+select *from t1;
+
+delete from t1;
+insert into t1 select * from t;
+
+let $q2=
+delete from t1
+  where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3;
+eval explain $q2;
+eval $q2;
+select *from t1;
+
+delete from t1;
+insert into t1 select * from t;
+
+let $q3=
+delete from t1
+  where exists (select 'X' from t1 a where a.c2 = t1.c2) and c2 >= 3
+    order by c2;
+eval explain $q3;
+eval $q3;
+select *from t1;
+
+drop table t1;
+
+--echo End of 11.1 tests