revision-id: 4ab7e1e3e0d8d0114b83991d56591c7f5fc6cae9 (mariadb-10.3.26-136-g4ab7e1e) parent(s): 6f271302b649ee11e7987b46fe24824c2ca2be7c author: Igor Babaev committer: Igor Babaev timestamp: 2021-04-22 17:42:12 -0700 message: MDEV-24823 Crash with invalid multi-table update of view in 2nd execution of SP Before this patch mergeable derived tables / view used in a multi-table update / delete were merged before the preparation stage. When the merge of a derived table / view is performed the on expression attached to it is fixed and ANDed with the where condition of the select S containing this derived table / view. It happens after the specification of the derived table / view has been merged into S. If the ON expression refers to a non existing field an error is reported and some other mergeable derived tables / views remain unmerged. It's not a problem if the multi-table update / delete statement is standalone. Yet if it is used in a stored procedure the select with incompletely merged derived tables / views may cause a problem for the second call of the procedure. This does not happen for select queries using derived tables / views, because in this case their specifications are merged after the preparation stage at which all ON expressions are fixed. This patch makes sure that merging of the derived tables / views used in a multi-table update / delete statement is performed after the preparation stage. Approved by Oleksandr Byelkin <sanja@mariadb.com> --- mysql-test/main/multi_update.result | 53 +++++++++++++++++++++++++++++++++++++ mysql-test/main/multi_update.test | 53 +++++++++++++++++++++++++++++++++++++ sql/sql_base.cc | 3 ++- sql/sql_class.h | 4 ++- sql/sql_delete.cc | 15 +++++------ sql/sql_derived.cc | 5 +--- sql/sql_lex.cc | 21 +++++++++++++++ sql/sql_lex.h | 2 ++ sql/sql_update.cc | 43 +++++++++++++++++++++--------- 9 files changed, 172 insertions(+), 27 deletions(-) diff --git a/mysql-test/main/multi_update.result b/mysql-test/main/multi_update.result index b3edeb7..520199d 100644 --- a/mysql-test/main/multi_update.result +++ b/mysql-test/main/multi_update.result @@ -982,6 +982,59 @@ drop function f1; # # end of 5.5 tests # +# +# MDEV-24823: Invalid multi-table update of view within SP +# +create table t1 (id int) engine=myisam; +insert into t1 values (1),(2),(1); +create table t2 (pk int, c0 int) engine=myisam; +insert into t2 values (1,1), (2,3); +create view v2 as select * from t2; +create view v3 as select * from t2 where c0 < 3; +create procedure sp0() update t1, v2 set v2.pk = 1 where v2.c0 = t1.c1; +call sp0(); +ERROR 42S22: Unknown column 't1.c1' in 'where clause' +call sp0(); +ERROR 42S22: Unknown column 't1.c1' in 'where clause' +create procedure sp1() update (t1 join v2 on v2.c0 = t1.c1) set v2.pk = 1; +call sp1(); +ERROR 42S22: Unknown column 't1.c1' in 'on clause' +call sp1(); +ERROR 42S22: Unknown column 't1.c1' in 'on clause' +create procedure sp2() update (t1 join v3 on v3.c0 = t1.c1) set v3.pk = 1; +call sp2(); +ERROR 42S22: Unknown column 't1.c1' in 'on clause' +call sp2(); +ERROR 42S22: Unknown column 't1.c1' in 'on clause' +create procedure sp3() +update (t1 join v2 on v2.c0 = t1.id) set v2.c0 = v2.c0+1; +select * from t2; +pk c0 +1 1 +2 3 +call sp3(); +select * from t2; +pk c0 +1 2 +2 3 +call sp3(); +select * from t2; +pk c0 +1 3 +2 3 +create procedure sp4() delete t1 from t1 join v2 on v2.c0 = t1.c1; +call sp4(); +ERROR 42S22: Unknown column 't1.c1' in 'on clause' +call sp4(); +ERROR 42S22: Unknown column 't1.c1' in 'on clause' +drop procedure sp0; +drop procedure sp1; +drop procedure sp2; +drop procedure sp3; +drop procedure sp4; +drop view v2,v3; +drop table t1,t2; +# End of 10.2 tests create table t1 (c1 int, c3 int); insert t1(c3) values (1), (2), (3), (4), (5), (6), (7), (8); create table t2 select * from t1; diff --git a/mysql-test/main/multi_update.test b/mysql-test/main/multi_update.test index 8a32f62..84f06a7 100644 --- a/mysql-test/main/multi_update.test +++ b/mysql-test/main/multi_update.test @@ -958,6 +958,59 @@ drop function f1; --echo # end of 5.5 tests --echo # +--echo # +--echo # MDEV-24823: Invalid multi-table update of view within SP +--echo # + +create table t1 (id int) engine=myisam; +insert into t1 values (1),(2),(1); +create table t2 (pk int, c0 int) engine=myisam; +insert into t2 values (1,1), (2,3); +create view v2 as select * from t2; +create view v3 as select * from t2 where c0 < 3; + +create procedure sp0() update t1, v2 set v2.pk = 1 where v2.c0 = t1.c1; +--error ER_BAD_FIELD_ERROR +call sp0(); +--error ER_BAD_FIELD_ERROR +call sp0(); + +create procedure sp1() update (t1 join v2 on v2.c0 = t1.c1) set v2.pk = 1; +--error ER_BAD_FIELD_ERROR +call sp1(); +--error ER_BAD_FIELD_ERROR +call sp1(); + +create procedure sp2() update (t1 join v3 on v3.c0 = t1.c1) set v3.pk = 1; +--error ER_BAD_FIELD_ERROR +call sp2(); +--error ER_BAD_FIELD_ERROR +call sp2(); + +create procedure sp3() +update (t1 join v2 on v2.c0 = t1.id) set v2.c0 = v2.c0+1; +select * from t2; +call sp3(); +select * from t2; +call sp3(); +select * from t2; + +create procedure sp4() delete t1 from t1 join v2 on v2.c0 = t1.c1; +--error ER_BAD_FIELD_ERROR +call sp4(); +--error ER_BAD_FIELD_ERROR +call sp4(); + +drop procedure sp0; +drop procedure sp1; +drop procedure sp2; +drop procedure sp3; +drop procedure sp4; +drop view v2,v3; +drop table t1,t2; + +--echo # End of 10.2 tests + # # MDEV-13911 Support ORDER BY and LIMIT in multi-table update # diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 0b17032..74ac1a3 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -7780,7 +7780,8 @@ bool setup_tables(THD *thd, Name_resolution_context *context, if (table_list->jtbm_subselect) { Item *item= table_list->jtbm_subselect->optimizer; - if (table_list->jtbm_subselect->optimizer->fix_fields(thd, &item)) + if (!table_list->jtbm_subselect->optimizer->fixed && + table_list->jtbm_subselect->optimizer->fix_fields(thd, &item)) { my_error(ER_TOO_MANY_TABLES,MYF(0), static_cast<int>(MAX_TABLES)); /* psergey-todo: WHY ER_TOO_MANY_TABLES ???*/ DBUG_RETURN(1); diff --git a/sql/sql_class.h b/sql/sql_class.h index 5ab93de..4f0df74 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -6117,7 +6117,8 @@ class multi_delete :public select_result_interceptor class multi_update :public select_result_interceptor { TABLE_LIST *all_tables; /* query/update command tables */ - List<TABLE_LIST> *leaves; /* list of leves of join table tree */ + List<TABLE_LIST> *leaves; /* list of leaves of join table tree */ + List<TABLE_LIST> updated_leaves; /* list of of updated leaves */ TABLE_LIST *update_tables; TABLE **tmp_tables, *main_table, *table_to_update; TMP_TABLE_PARAM *tmp_table_param; @@ -6155,6 +6156,7 @@ class multi_update :public select_result_interceptor List<Item> *fields, List<Item> *values, enum_duplicates handle_duplicates, bool ignore); ~multi_update(); + bool init(THD *thd); int prepare(List<Item> &list, SELECT_LEX_UNIT *u); int send_data(List<Item> &items); bool initialize_tables (JOIN *join); diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc index 0857c4e..c78e8ed 100644 --- a/sql/sql_delete.cc +++ b/sql/sql_delete.cc @@ -1009,9 +1009,6 @@ int mysql_multi_delete_prepare(THD *thd) DELETE_ACL, SELECT_ACL, FALSE)) DBUG_RETURN(TRUE); - if (lex->select_lex.handle_derived(thd->lex, DT_MERGE)) - DBUG_RETURN(TRUE); - /* Multi-delete can't be constructed over-union => we always have single SELECT on top and have to check underlying SELECTs of it @@ -1039,6 +1036,12 @@ int mysql_multi_delete_prepare(THD *thd) target_tbl->table_name.str, "DELETE"); DBUG_RETURN(TRUE); } + } + + 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. @@ -1083,12 +1086,6 @@ multi_delete::prepare(List<Item> &values, SELECT_LEX_UNIT *u) unit= u; do_delete= 1; THD_STAGE_INFO(thd, stage_deleting_from_main_table); - SELECT_LEX *select_lex= u->first_select(); - if (select_lex->first_cond_optimization) - { - if (select_lex->handle_derived(thd->lex, DT_MERGE)) - DBUG_RETURN(TRUE); - } DBUG_RETURN(0); } diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index 50b0178..eef2ae7 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -355,10 +355,6 @@ bool mysql_derived_merge(THD *thd, LEX *lex, TABLE_LIST *derived) DBUG_RETURN(FALSE); } - if (thd->lex->sql_command == SQLCOM_UPDATE_MULTI || - thd->lex->sql_command == SQLCOM_DELETE_MULTI) - thd->save_prep_leaf_list= TRUE; - arena= thd->activate_stmt_arena_if_needed(&backup); // For easier test if (!derived->merged_for_insert || @@ -436,6 +432,7 @@ bool mysql_derived_merge(THD *thd, LEX *lex, TABLE_LIST *derived) derived->on_expr= expr; derived->prep_on_expr= expr->copy_andor_structure(thd); } + thd->where= "on clause"; if (derived->on_expr && derived->on_expr->fix_fields_if_needed_for_bool(thd, &derived->on_expr)) { diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index fe4d086..5604658 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -4927,6 +4927,27 @@ bool st_select_lex::save_prep_leaf_tables(THD *thd) } +/** + Set exclude_from_table_unique_test for selects of this select and all selects + belonging to the underlying units of derived tables or views +*/ + +void st_select_lex::set_unique_exclude() +{ + exclude_from_table_unique_test= TRUE; + for (SELECT_LEX_UNIT *unit= first_inner_unit(); + unit; + unit= unit->next_unit()) + { + if (unit->derived && unit->derived->is_view_or_derived()) + { + for (SELECT_LEX *sl= unit->first_select(); sl; sl= sl->next_select()) + sl->set_unique_exclude(); + } + } +} + + /* Return true if this select_lex has been converted into a semi-join nest within 'ancestor'. diff --git a/sql/sql_lex.h b/sql/sql_lex.h index b5b39fe..c3e48a7 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -1354,6 +1354,8 @@ class st_select_lex: public st_select_lex_node bool save_leaf_tables(THD *thd); bool save_prep_leaf_tables(THD *thd); + void set_unique_exclude(); + bool is_merged_child_of(st_select_lex *ancestor); /* diff --git a/sql/sql_update.cc b/sql/sql_update.cc index c66a474..01bf6e7 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -1571,15 +1571,8 @@ bool Multiupdate_prelocking_strategy::handle_end(THD *thd) call in setup_tables()). */ - if (setup_tables_and_check_access(thd, &select_lex->context, - &select_lex->top_join_list, table_list, select_lex->leaf_tables, - FALSE, UPDATE_ACL, SELECT_ACL, FALSE)) - DBUG_RETURN(1); - - if (select_lex->handle_derived(thd->lex, DT_MERGE)) - DBUG_RETURN(1); - - if (thd->lex->save_prep_leaf_tables()) + if (setup_tables(thd, &select_lex->context, &select_lex->top_join_list, + table_list, select_lex->leaf_tables, FALSE, TRUE)) DBUG_RETURN(1); List<Item> *fields= &lex->select_lex.item_list; @@ -1754,7 +1747,8 @@ int mysql_multi_update_prepare(THD *thd) Check that we are not using table that we are updating, but we should skip all tables of UPDATE SELECT itself */ - lex->select_lex.exclude_from_table_unique_test= TRUE; + lex->select_lex.set_unique_exclude(); + /* We only need SELECT privilege for columns in the values list */ List_iterator<TABLE_LIST> ti(lex->select_lex.leaf_tables); while ((tl= ti++)) @@ -1805,12 +1799,19 @@ bool mysql_multi_update(THD *thd, TABLE_LIST *table_list, List<Item> *fields, DBUG_RETURN(TRUE); } + if ((*result)->init(thd)) + DBUG_RETURN(1); + thd->abort_on_warning= !ignore && thd->is_strict_mode(); List<Item> total_list; - if (select_lex->vers_setup_conds(thd, table_list)) + if (setup_tables(thd, &select_lex->context, &select_lex->top_join_list, + table_list, select_lex->leaf_tables, FALSE, FALSE)) DBUG_RETURN(1); + if (select_lex->vers_setup_conds(thd, table_list)) + DBUG_RETURN(1); + res= mysql_select(thd, table_list, select_lex->with_wild, total_list, conds, select_lex->order_list.elements, @@ -1849,6 +1850,24 @@ multi_update::multi_update(THD *thd_arg, TABLE_LIST *table_list, } +bool multi_update::init(THD *thd) +{ + table_map tables_to_update= get_table_map(fields); + List_iterator_fast<TABLE_LIST> li(*leaves); + TABLE_LIST *tbl; + while ((tbl =li++)) + { + if (tbl->is_jtbm()) + continue; + if (!(tbl->table->map & tables_to_update)) + continue; + if (updated_leaves.push_back(tbl, thd->mem_root)) + return true; + } + return false; +} + + /* Connect fields with tables and create list of tables that are updated */ @@ -1865,7 +1884,7 @@ int multi_update::prepare(List<Item> ¬_used_values, List_iterator_fast<Item> value_it(*values); uint i, max_fields; uint leaf_table_count= 0; - List_iterator<TABLE_LIST> ti(*leaves); + List_iterator<TABLE_LIST> ti(updated_leaves); DBUG_ENTER("multi_update::prepare"); if (prepared)