revision-id: 90016d4de943f9557ea3905bd0c165421f658ddb (mariadb-10.6.1-477-g90016d4) parent(s): 2db18fdb3d68d906fbd188ec570a64502ba55849 author: Igor Babaev committer: Igor Babaev timestamp: 2022-07-01 16:25:24 -0700 message: MDEV-28965 Assertion failure when preparing UPDATE with derived table in WHERE This is a preliminary patch to test the fix. --- mysql-test/main/derived_cond_pushdown.result | 27 ++-- mysql-test/main/derived_cond_pushdown.test | 2 +- mysql-test/main/multi_update.result | 2 - mysql-test/main/multi_update.test | 2 - mysql-test/main/subselect.result | 1 - mysql-test/main/subselect.test | 1 - mysql-test/main/subselect_no_exists_to_in.result | 1 - mysql-test/main/subselect_no_mat.result | 1 - mysql-test/main/subselect_no_opts.result | 1 - mysql-test/main/subselect_no_scache.result | 1 - mysql-test/main/subselect_no_semijoin.result | 1 - mysql-test/main/update.result | 180 +++++++++++++++++++++++ mysql-test/main/update.test | 112 ++++++++++++++ sql/opt_subselect.cc | 56 ++++++- sql/sql_base.cc | 41 +++++- sql/sql_delete.cc | 49 +++--- sql/sql_delete.h | 14 +- sql/sql_lex.cc | 28 +++- sql/sql_lex.h | 1 + sql/sql_update.cc | 21 ++- sql/sql_update.h | 16 +- sql/sql_yacc.yy | 13 ++ sql/table.cc | 10 +- 23 files changed, 508 insertions(+), 73 deletions(-) diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index 9bbd32a..a7d7e87 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -11908,7 +11908,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 @@ -11939,17 +11939,22 @@ EXPLAIN "materialized": { "query_block": { "select_id": 3, - "nested_loop": [ - { - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 2, - "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 39e8221..e88fae7 100644 --- a/mysql-test/main/derived_cond_pushdown.test +++ b/mysql-test/main/derived_cond_pushdown.test @@ -2089,7 +2089,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 674dc79..ae661fa 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 5f4b5fc..839cebf 100644 --- a/mysql-test/main/multi_update.test +++ b/mysql-test/main/multi_update.test @@ -390,7 +390,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; @@ -399,7 +398,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 22a814c..5e8265c 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 19c30bd..7e29660 100644 --- a/mysql-test/main/subselect.test +++ b/mysql-test/main/subselect.test @@ -366,7 +366,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 def116c..3c18950 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 7eb3734..c1b0c67 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 f2981c0..13af2f4 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 17bec03..38d5665 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 cb3620f..cab4e6f 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/main/update.result b/mysql-test/main/update.result index 15efd7e..bcbfd8e 100644 --- a/mysql-test/main/update.result +++ b/mysql-test/main/update.result @@ -734,3 +734,183 @@ UPDATE t1,t2 SET t1.i1 = -39 WHERE t2.d1 <> t1.i1 AND t2.d1 = t1.d2; ERROR 22007: Incorrect datetime value: '19' for column `test`.`t1`.`i1` at row 1 DROP TABLE t1,t2; # End of MariaDB 10.2 tests +# +# MDEV-28965: single-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 +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 +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; +drop table t1; +# End of MariaDB 10.10 tests diff --git a/mysql-test/main/update.test b/mysql-test/main/update.test index 8470910..1df917a 100644 --- a/mysql-test/main/update.test +++ b/mysql-test/main/update.test @@ -673,3 +673,115 @@ UPDATE t1,t2 SET t1.i1 = -39 WHERE t2.d1 <> t1.i1 AND t2.d1 = t1.d2; DROP TABLE t1,t2; --echo # End of MariaDB 10.2 tests + +--echo # +--echo # MDEV-28965: single-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; +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 $q1= +delete from t1 + where a = ( select * from (select a from t1) dt where dt.a > 7); + +eval explain $q1; +eval $q1; +select * from t1; +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= +delete from t1 + where a = ( select * from (select a from t1) dt where dt.a > 5) +returning pk, a; + +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) +order by a limit 2; +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 values (1,3), (2, 7), (3,1), (4,9); +execute stmt; +select * from t1; +deallocate prepare stmt; + +drop table t1; + +--echo # End of MariaDB 10.10 tests diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index fa338f0..ac47566 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" +#include "sql_delete.h" #include "filesort.h" #include "opt_subselect.h" #include "sql_test.h" @@ -532,6 +534,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 @@ -675,9 +719,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 @@ -692,9 +735,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 @@ -754,7 +795,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 2b41b78..447573b 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" +#include "sql_delete.h" #include <m_ctype.h> #include <my_dir.h> #include <hash.h> @@ -1164,7 +1166,7 @@ TABLE_LIST* find_dup_table(THD *thd, TABLE_LIST *table, TABLE_LIST *table_list, /* If we found entry of this table or table of SELECT which already processed in derived table or top select of multi-update/multi-delete - (exclude_from_table_unique_test) or prelocking placeholder. + (exclude_from_table_unique_test) or prelocking placeholder. */ DBUG_PRINT("info", ("found same copy of table or table which we should skip")); @@ -1175,16 +1177,43 @@ TABLE_LIST* find_dup_table(THD *thd, TABLE_LIST *table, TABLE_LIST *table_list, 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(); + // derived->field_translation= 0; + goto retry; + } } } DBUG_RETURN(res); diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc index 2ff4331..22b76da 100644 --- a/sql/sql_delete.cc +++ b/sql/sql_delete.cc @@ -1109,6 +1109,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; } @@ -1440,6 +1442,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) @@ -1627,25 +1657,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 e1d5044..ffb8173 100644 --- a/sql/sql_delete.h +++ b/sql/sql_delete.h @@ -44,11 +44,12 @@ 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), save_protocol(NULL) + { multitable= orig_multitable; } 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 +57,12 @@ class Sql_cmd_delete final : public Sql_cmd_dml 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 @@ -85,6 +92,9 @@ class Sql_cmd_delete final : public Sql_cmd_dml */ bool multitable; + /* Original value of the 'multitable' flag set by constructor */ + const bool orig_multitable; + /* The prelocking strategy used when opening the used tables */ DML_prelocking_strategy dml_prelocking_strategy; diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 78c067a..4059c0e 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -37,6 +37,8 @@ #include "sql_partition.h" #include "sql_partition_admin.h" // Sql_cmd_alter_table_*_part #include "event_parse_data.h" +#include "sql_update.h" +#include "sql_delete.h" void LEX::parse_error(uint err_number) { @@ -4037,9 +4039,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 @@ -4050,7 +4051,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: @@ -4064,18 +4065,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 e733b4f..402e3bd 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -1642,6 +1642,7 @@ class st_select_lex: public st_select_lex_node 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 6b14c4f..7769777 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -2807,6 +2807,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 @@ -2889,7 +2906,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 d0fc7cb..4aff77a 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= orig_multitable; } 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 @@ class Sql_cmd_update final : public Sql_cmd_dml 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 @@ -89,13 +95,15 @@ class Sql_cmd_update final : public Sql_cmd_dml */ bool multitable; + /* Original value of the 'multitable' flag set by constructor */ + const bool orig_multitable; + /* The prelocking strategy used when opening the used tables */ Multiupdate_prelocking_strategy multiupdate_prelocking_strategy; 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 a587a37..13dc602 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -13370,8 +13370,21 @@ 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); + 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 f0d5149..44eb9f6 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -7059,7 +7059,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; @@ -7069,7 +7070,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)); @@ -9518,13 +9519,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