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

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

4 files changed, 110 insertions, 0 deletions

diff --git a/mysql-test/main/delete.result b/mysql-test/main/delete.result
index ed3683d52f9..51257d2d65d 100644
--- a/mysql-test/main/delete.result
+++ b/mysql-test/main/delete.result
@@ -525,3 +525,51 @@ DELETE v2 FROM v2;
 ERROR HY000: Can not delete from join view 'test.v2'
 DROP VIEW v2, v1;
 DROP TABLE t1, t2;
+End of 10.10 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 10.11 tests
diff --git a/mysql-test/main/delete.test b/mysql-test/main/delete.test
index c82420640c2..1e3848f0a9f 100644
--- a/mysql-test/main/delete.test
+++ b/mysql-test/main/delete.test
@@ -582,3 +582,47 @@ DELETE v2 FROM v2;
 
 DROP VIEW v2, v1;
 DROP TABLE t1, t2;
+
+--echo End of 10.10 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 10.11 tests
diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc
index 5191baefc1d..7ab5913ee09 100644
--- a/sql/sql_delete.cc
+++ b/sql/sql_delete.cc
@@ -1448,6 +1448,19 @@ bool multi_delete::send_eof()
 
 
 /**
+  @brief Remove ORDER BY from DELETE if it's used without limit clause
+*/
+
+void  Sql_cmd_delete::remove_order_by_without_limit(THD *thd)
+{
+  SELECT_LEX *const select_lex = thd->lex->first_select_lex();
+  if (select_lex->order_list.elements &&
+      !select_lex->limit_params.select_limit)
+    select_lex->order_list.empty();
+}
+
+
+/**
   @brief Check whether processing to multi-table delete is prohibited
 
   @param thd  global context the processed statement
@@ -1588,7 +1601,10 @@ bool Sql_cmd_delete::prepare_inner(THD *thd)
         DBUG_ASSERT(update_source_table || table_list->view != 0);
         if (!table_list->is_multitable() &&
             !processing_as_multitable_delete_prohibited(thd))
+	{
           multitable= true;
+          remove_order_by_without_limit(thd);
+        }
       }
     }
 
diff --git a/sql/sql_delete.h b/sql/sql_delete.h
index 463cb198d03..ad018ed686c 100644
--- a/sql/sql_delete.h
+++ b/sql/sql_delete.h
@@ -64,6 +64,8 @@ public:
 
   void set_as_multitable() { multitable= true; }
 
+  void remove_order_by_without_limit(THD *thd);
+
 protected:
   /**
     @brief Perform precheck of table privileges for delete statements