revision-id: 845af351eea0e0d5cb1b61e1d4fbccec5eb610e0 (mariadb-10.10.1-15-g845af35) parent(s): 5de8fea53b9a4d18c146f96c0b6f13e96a322260 author: Igor Babaev committer: Igor Babaev timestamp: 2022-09-13 17:16:31 -0700 message: MDEV-29428 Incorrect result for delete with "order by" clause ORDER BY clause without LIMIT clause can be removed from DELETE statements. --- mysql-test/main/delete.result | 48 +++++++++++++++++++++++++++++++++++++++++++ mysql-test/main/delete.test | 44 +++++++++++++++++++++++++++++++++++++++ sql/sql_delete.cc | 16 +++++++++++++++ sql/sql_delete.h | 2 ++ 4 files changed, 110 insertions(+) diff --git a/mysql-test/main/delete.result b/mysql-test/main/delete.result index ed3683d..51257d2 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 c824206..1e3848f 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 5191bae..7ab5913 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 463cb19..ad018ed 100644 --- a/sql/sql_delete.h +++ b/sql/sql_delete.h @@ -64,6 +64,8 @@ class Sql_cmd_delete final : public Sql_cmd_dml void set_as_multitable() { multitable= true; } + void remove_order_by_without_limit(THD *thd); + protected: /** @brief Perform precheck of table privileges for delete statements