- commits - lists.mariadb.org

[Commits] e3a2579: MDEV-24823 Crash with invalid multi-table update of view in 2nd execution of SP
by IgorBabaev 23 Apr '21

23 Apr '21

revision-id: e3a25793be936d9682a711a00d6b4bf708b6fb8d (mariadb-10.3.26-136-ge3a2579) parent(s): 6f271302b649ee11e7987b46fe24824c2ca2be7c author: Igor Babaev committer: Igor Babaev timestamp: 2021-04-22 20:02:08 -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(a)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_update.cc | 39 ++++++++++++++++++++------- 7 files changed, 147 insertions(+), 25 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_update.cc b/sql/sql_update.cc index c66a474..c58eb4e 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; @@ -1755,6 +1748,7 @@ int mysql_multi_update_prepare(THD *thd) skip all tables of UPDATE SELECT itself */ lex->select_lex.exclude_from_table_unique_test= TRUE; + /* 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,9 +1799,16 @@ 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 (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); @@ -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> &not_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)

1 0

[Commits] 4ab7e1e: MDEV-24823 Crash with invalid multi-table update of view in 2nd execution of SP
by IgorBabaev 23 Apr '21

23 Apr '21

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(a)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> &not_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)

1 0

[Commits] dd6ad380685: Code cleanup: merge walk_items_for_table_list with walk_table_functions_for_list
by psergey 21 Apr '21

21 Apr '21

revision-id: dd6ad3806856221f1af302e61ebd985905a00060 (mariadb-10.5.2-640-gdd6ad380685) parent(s): 4930f9c94bb610f4d76b73a8b6b351d9fa9c5d68 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-04-21 12:32:58 +0300 message: Code cleanup: merge walk_items_for_table_list with walk_table_functions_for_list --- sql/item_subselect.cc | 18 ++++++++++++++---- 1 file changed, 14 insertions(+), 4 deletions(-) diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index c9afc9c1bcd..3f60203387a 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -685,6 +685,12 @@ bool Item_subselect::is_expensive() } +/* + @brief + Apply item processor for all scalar (i.e. Item*) expressions that + occur in the nested join. +*/ + static int walk_items_for_table_list(Item_processor processor, bool walk_subquery, void *argument, @@ -699,6 +705,14 @@ int walk_items_for_table_list(Item_processor processor, if ((res= table->on_expr->walk(processor, walk_subquery, argument))) return res; } + if (Table_function_json_table *tf= table->table_function) + { + if ((res= tf->walk_items(processor, walk_subquery, argument))) + { + return res; + } + } + if (table->nested_join) { if ((res= walk_items_for_table_list(processor, walk_subquery, argument, @@ -807,10 +821,6 @@ bool Item_subselect::walk(Item_processor processor, bool walk_subquery, *lex->join_list)) return 1; - if (walk_table_functions_for_list(processor, walk_subquery, argument, - *lex->join_list)) - return 1; - while (Item *item= li++) { if (item->walk(processor, walk_subquery, argument))

1 0

[Commits] 03a162bc51a: MDEV-25154: JSON_TABLE: Queries involving ordinality columns are unsafe...
by psergey 20 Apr '21

20 Apr '21

revision-id: 03a162bc51aadb8fd71a60bb24806ffea3b828cf (mariadb-10.5.2-589-g03a162bc51a) parent(s): 36c3be336616cb95443c5164a0e082862c6270d0 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-04-20 14:29:36 +0300 message: MDEV-25154: JSON_TABLE: Queries involving ordinality columns are unsafe... Actually the functions are safe for the current version. Still, we will mark the function as SBR-unsafe to be more future-proof. --- mysql-test/suite/json/r/json_table_binlog.result | 26 ++++++++++++++++++++++++ mysql-test/suite/json/t/json_table_binlog.test | 25 +++++++++++++++++++++++ sql/json_table.cc | 16 +++++++++++++++ sql/json_table.h | 7 +------ sql/sql_yacc.yy | 2 +- 5 files changed, 69 insertions(+), 7 deletions(-) diff --git a/mysql-test/suite/json/r/json_table_binlog.result b/mysql-test/suite/json/r/json_table_binlog.result new file mode 100644 index 00000000000..472f7395648 --- /dev/null +++ b/mysql-test/suite/json/r/json_table_binlog.result @@ -0,0 +1,26 @@ +# +# MDEV-25154: JSON_TABLE: Queries involving ordinality columns are unsafe for statement binlog and should be marked as such +# +create table t1 (a int); +call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); +set binlog_format='statement'; +insert into t1 +select * +from json_table('[1,2,3]', '$[*]' columns (a for ordinality)) as T ; +Warnings: +Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave +set binlog_format='mixed'; +insert into t1 +select * +from json_table('[1,2,3]', '$[*]' columns (a for ordinality)) as T ; +# This must show Annotate_rows, Write_rows_v1 events. Not the statement event +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # BEGIN GTID #-#-# +master-bin.000001 # Annotate_rows # # insert into t1 +select * +from json_table('[1,2,3]', '$[*]' columns (a for ordinality)) as T +master-bin.000001 # Table_map # # table_id: # (test.t1) +master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F +master-bin.000001 # Query # # COMMIT +drop table t1; diff --git a/mysql-test/suite/json/t/json_table_binlog.test b/mysql-test/suite/json/t/json_table_binlog.test new file mode 100644 index 00000000000..dcc05fb855d --- /dev/null +++ b/mysql-test/suite/json/t/json_table_binlog.test @@ -0,0 +1,25 @@ +--source include/have_binlog_format_mixed.inc + +--echo # +--echo # MDEV-25154: JSON_TABLE: Queries involving ordinality columns are unsafe for statement binlog and should be marked as such +--echo # + +create table t1 (a int); + +call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); +set binlog_format='statement'; +insert into t1 +select * +from json_table('[1,2,3]', '$[*]' columns (a for ordinality)) as T ; + +set binlog_format='mixed'; +let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1); +let $binlog_file= LAST; + +insert into t1 +select * +from json_table('[1,2,3]', '$[*]' columns (a for ordinality)) as T ; + +--echo # This must show Annotate_rows, Write_rows_v1 events. Not the statement event +--source include/show_binlog_events.inc +drop table t1; diff --git a/sql/json_table.cc b/sql/json_table.cc index a16adc25f08..c07c96e1b7a 100644 --- a/sql/json_table.cc +++ b/sql/json_table.cc @@ -978,6 +978,22 @@ int Json_table_column::On_response::print(const char *name, String *str) const str->append(name)); } +Table_function_json_table::Table_function_json_table(THD *thd, Item *json) : + m_json(json) +{ + cur_parent= &m_nested_path; + last_sibling_hook= &m_nested_path.m_nested; + /* + Currently, the evaluation of JSON_TABLE is deterministic (passing the same + input string will produce the same set of rows in the same order). + In order to be future-proof and to be able to make changes like + - sort array arguments by name (like MySQL does) + - change the way duplicate object members are handled + we mark the function as SBR-unsafe. (If we re-consider, making the function + SBR-safe is easier than making it unsafe) + */ + thd->lex->set_stmt_unsafe(LEX::BINLOG_STMT_UNSAFE_SYSTEM_FUNCTION); +} void Table_function_json_table::start_nested_path(Json_table_nested_path *np) { diff --git a/sql/json_table.h b/sql/json_table.h index 09e4295d80c..f1ae5d5c577 100644 --- a/sql/json_table.h +++ b/sql/json_table.h @@ -225,12 +225,7 @@ class Table_function_json_table : public Sql_alloc String *str, enum_query_type query_type); /*** Construction interface to be used from the parser ***/ - Table_function_json_table(Item *json): - m_json(json) - { - cur_parent= &m_nested_path; - last_sibling_hook= &m_nested_path.m_nested; - } + Table_function_json_table(THD *thd, Item *json); void start_nested_path(Json_table_nested_path *np); void end_nested_path(); diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index b92a83665ea..fab17ff7a25 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -11685,7 +11685,7 @@ table_function: expr ',' { Table_function_json_table *jt= - new (thd->mem_root) Table_function_json_table($4); + new (thd->mem_root) Table_function_json_table(thd, $4); if (unlikely(!jt)) MYSQL_YYABORT; Lex->json_table= jt;

1 0

[Commits] 75cc808: MDEV-24823 Crash with invalid multi-table update of view in 2nd execution of SP
by IgorBabaev 20 Apr '21

20 Apr '21

revision-id: 75cc8083605e8ce92d45574a98121b7043e29e5a (mariadb-10.2.31-812-g75cc808) parent(s): 8f7a6cde580298116b50b144984f996dc6af60ae author: Igor Babaev committer: Igor Babaev timestamp: 2021-04-19 19:52:06 -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(a)mariadb.com> --- mysql-test/r/multi_update.result | 53 ++++++++++++++++++++++++++++++++++++++++ mysql-test/t/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 | 41 ++++++++++++++++++++++--------- 9 files changed, 171 insertions(+), 26 deletions(-) diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result index a5c60d6..1532ebb 100644 --- a/mysql-test/r/multi_update.result +++ b/mysql-test/r/multi_update.result @@ -1043,3 +1043,56 @@ 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 diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test index b5328f7..dff3938 100644 --- a/mysql-test/t/multi_update.test +++ b/mysql-test/t/multi_update.test @@ -1041,3 +1041,56 @@ drop function f1; --echo # --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 diff --git a/sql/sql_base.cc b/sql/sql_base.cc index b8d18ab..2086bea 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -7501,7 +7501,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 c47ea9c..ce4bf67 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -5600,7 +5600,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_being_updated; TABLE **tmp_tables, *main_table, *table_to_update; TMP_TABLE_PARAM *tmp_table_param; @@ -5632,6 +5633,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 e2d4cd4..61a3b4e 100644 --- a/sql/sql_delete.cc +++ b/sql/sql_delete.cc @@ -841,9 +841,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 @@ -871,6 +868,12 @@ int mysql_multi_delete_prepare(THD *thd) target_tbl->table_name, "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. @@ -915,12 +918,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 be5905d..404fb03 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -354,10 +354,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 || @@ -435,6 +431,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->fixed && derived->on_expr->fix_fields(thd, &derived->on_expr)) || diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 5059e4f..97c4185 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -4619,6 +4619,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 f733f78..531c5c9 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -1146,6 +1146,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 01743a6..e8b973c 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -1390,15 +1390,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; @@ -1574,7 +1567,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++)) @@ -1635,9 +1629,16 @@ 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 (setup_tables(thd, &select_lex->context, &select_lex->top_join_list, + table_list, select_lex->leaf_tables, FALSE, FALSE)) + DBUG_RETURN(1); + res= mysql_select(thd, table_list, select_lex->with_wild, total_list, conds, 0, NULL, NULL, NULL, NULL, @@ -1673,6 +1674,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 */ @@ -1689,7 +1708,7 @@ int multi_update::prepare(List<Item> &not_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)

1 0

[Commits] a7928cc: MDEV-25362 Incorrect name resolution for subqueries in ON expressions
by IgorBabaev 19 Apr '21

19 Apr '21

revision-id: a7928cc7e7848e0a282d60c09cfe9dd82c0382fe (mariadb-10.4.11-599-ga7928cc) parent(s): c3b016efde4b1e0c2b85ca26c814ad43f5611ab2 author: Igor Babaev committer: Igor Babaev timestamp: 2021-04-18 23:29:33 -0700 message: MDEV-25362 Incorrect name resolution for subqueries in ON expressions This patch sets the proper name resolution context for outer references used in a subquery from an ON clause. Usually this context is more narrow than the name resolution context of the parent select that were used before this fix. This fix revealed another problem that concerned ON expressions used in from clauses of specifications of derived tables / views / CTEs. The name resolution outer context for such ON expression must be set to NULL to prevent name resolution beyond the derived table where it is used. The solution to resolve this problem applied in sql_derived.cc was provided by Sergei Petrunia <sergey(a)mariadb.com>. The change in sql_parse.cc is not good for 10.4+. A corresponding diff for 10.4+ will be provided in JIRA entry for this bug. Approved by Oleksandr Byelkin <sanja(a)mariadb.com> --- mysql-test/main/join_outer.result | 40 +++++++++++++++++++++++++++ mysql-test/main/join_outer.test | 50 ++++++++++++++++++++++++++++++++++ mysql-test/main/join_outer_jcl6.result | 40 +++++++++++++++++++++++++++ sql/sql_derived.cc | 31 +++++++++++++++++++++ sql/sql_lex.cc | 2 +- 5 files changed, 162 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/join_outer.result b/mysql-test/main/join_outer.result index c92b8e6..362335a 100644 --- a/mysql-test/main/join_outer.result +++ b/mysql-test/main/join_outer.result @@ -2687,6 +2687,46 @@ id timestamp modifiedBy id REV REVTYPE profile_id id REV person_id id REV DROP TABLE t1,t2,t3,t4; # end of 10.1 tests # +# MDEV-25362: name resolution for subqueries in ON expressions +# +create table t1 (a int, b int); +create table t2 (c int, d int); +create table t3 (e int, f int); +create table t4 (g int, h int); +explain +select * +from +t1 left join +(t2 +join +t3 on +(t3.f=t1.a) +) on (t2.c=t1.a ); +ERROR 42S22: Unknown column 't1.a' in 'on clause' +explain +select * +from +t1 left join +(t2 +join +t3 on +(t3.f=(select max(g) from t4 where t4.h=t1.a)) +) on (t2.c=t1.a ); +ERROR 42S22: Unknown column 't1.a' in 'where clause' +drop table t1,t2,t3,t4; +create table t1 (a int); +insert into t1 values (1),(2); +create table t2 (b int); +insert into t2 values (1),(2); +create table t3 (c int); +insert into t3 values (1),(2); +select * from ( select * from t1 left join t2 +on b in (select x from t3 as sq1) +) as sq2; +ERROR 42S22: Unknown column 'x' in 'field list' +drop table t1,t2,t3; +# end of 10.2 tests +# # MDEV-17518: Range optimization doesn't use ON expressions from nested outer joins # create table t1(a int); diff --git a/mysql-test/main/join_outer.test b/mysql-test/main/join_outer.test index f835d8a..bf75a52 100644 --- a/mysql-test/main/join_outer.test +++ b/mysql-test/main/join_outer.test @@ -2192,6 +2192,56 @@ DROP TABLE t1,t2,t3,t4; --echo # end of 10.1 tests --echo # +--echo # MDEV-25362: name resolution for subqueries in ON expressions +--echo # + +create table t1 (a int, b int); +create table t2 (c int, d int); +create table t3 (e int, f int); +create table t4 (g int, h int); + +--error ER_BAD_FIELD_ERROR +explain +select * +from + t1 left join + (t2 + join + t3 on + (t3.f=t1.a) + ) on (t2.c=t1.a ); + +# This must produce an error: +--error ER_BAD_FIELD_ERROR +explain +select * +from + t1 left join + (t2 + join + t3 on + (t3.f=(select max(g) from t4 where t4.h=t1.a)) + ) on (t2.c=t1.a ); + +drop table t1,t2,t3,t4; + +create table t1 (a int); +insert into t1 values (1),(2); +create table t2 (b int); +insert into t2 values (1),(2); +create table t3 (c int); +insert into t3 values (1),(2); + +--error ER_BAD_FIELD_ERROR +select * from ( select * from t1 left join t2 + on b in (select x from t3 as sq1) + ) as sq2; + +drop table t1,t2,t3; + +--echo # end of 10.2 tests + +--echo # --echo # MDEV-17518: Range optimization doesn't use ON expressions from nested outer joins --echo # create table t1(a int); diff --git a/mysql-test/main/join_outer_jcl6.result b/mysql-test/main/join_outer_jcl6.result index 2c7dc7b..2810ffa 100644 --- a/mysql-test/main/join_outer_jcl6.result +++ b/mysql-test/main/join_outer_jcl6.result @@ -2694,6 +2694,46 @@ id timestamp modifiedBy id REV REVTYPE profile_id id REV person_id id REV DROP TABLE t1,t2,t3,t4; # end of 10.1 tests # +# MDEV-25362: name resolution for subqueries in ON expressions +# +create table t1 (a int, b int); +create table t2 (c int, d int); +create table t3 (e int, f int); +create table t4 (g int, h int); +explain +select * +from +t1 left join +(t2 +join +t3 on +(t3.f=t1.a) +) on (t2.c=t1.a ); +ERROR 42S22: Unknown column 't1.a' in 'on clause' +explain +select * +from +t1 left join +(t2 +join +t3 on +(t3.f=(select max(g) from t4 where t4.h=t1.a)) +) on (t2.c=t1.a ); +ERROR 42S22: Unknown column 't1.a' in 'where clause' +drop table t1,t2,t3,t4; +create table t1 (a int); +insert into t1 values (1),(2); +create table t2 (b int); +insert into t2 values (1),(2); +create table t3 (c int); +insert into t3 values (1),(2); +select * from ( select * from t1 left join t2 +on b in (select x from t3 as sq1) +) as sq2; +ERROR 42S22: Unknown column 'x' in 'field list' +drop table t1,t2,t3; +# end of 10.2 tests +# # MDEV-17518: Range optimization doesn't use ON expressions from nested outer joins # create table t1(a int); diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index 500cb43..2ad31ae 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -596,6 +596,32 @@ bool mysql_derived_init(THD *thd, LEX *lex, TABLE_LIST *derived) } + /* + @brief + Prevent name resolution out of context of ON expressions in derived tables + + @param + join_list list of tables used in from list of a derived + + @details + The function sets the Name_resolution_context::outer_context to NULL + for all ON expressions contexts in the given join list. It does this + recursively for all nested joins the list contains. +*/ + +static void nullify_outer_context_for_on_clauses(List<TABLE_LIST>& join_list) +{ + List_iterator<TABLE_LIST> li(join_list); + while (TABLE_LIST *table= li++) + { + if (table->on_context) + table->on_context->outer_context= NULL; + if (table->nested_join) + nullify_outer_context_for_on_clauses(table->nested_join->join_list); + } +} + + /* Create temporary table structure (but do not fill it) @@ -760,7 +786,12 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived) /* prevent name resolving out of derived table */ for (SELECT_LEX *sl= first_select; sl; sl= sl->next_select()) { + // Prevent it for the WHERE clause sl->context.outer_context= 0; + + // And for ON clauses, if there are any + nullify_outer_context_for_on_clauses(*sl->join_list); + if (!derived->is_with_table_recursive_reference() || (!derived->with->with_anchor && !derived->with->is_with_prepared_anchor())) diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index ecde337..5338fad 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -9391,7 +9391,7 @@ SELECT_LEX *LEX::parsed_subselect(SELECT_LEX_UNIT *unit) (curr_sel == NULL && current_select == &builtin_select)); if (curr_sel) { - curr_sel->register_unit(unit, &curr_sel->context); + curr_sel->register_unit(unit, context_stack.head()); curr_sel->add_statistics(unit); }

1 0

[Commits] 48645d7f73d: MDEV-25202: JSON_TABLE: Early table reference leads to unexpected result set
by psergey 17 Apr '21

17 Apr '21

revision-id: 48645d7f73ddab1752468eae10f22df43001d231 (mariadb-10.5.2-592-g48645d7f73d) parent(s): d3004d1b88072c170a0764a329bfdcec5406328e author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-04-17 18:23:15 +0300 message: MDEV-25202: JSON_TABLE: Early table reference leads to unexpected result set Followup part#2: allocate the List object on the right mem-root, too. --- mysql-test/suite/json/r/json_table.result | 38 +++++++++++++++++++++++++++++- mysql-test/suite/json/t/json_table.test | 39 ++++++++++++++++++++++++++++++- sql/json_table.cc | 2 +- 3 files changed, 76 insertions(+), 3 deletions(-) diff --git a/mysql-test/suite/json/r/json_table.result b/mysql-test/suite/json/r/json_table.result index 1ebb90918f9..3ef87b3e24b 100644 --- a/mysql-test/suite/json/r/json_table.result +++ b/mysql-test/suite/json/r/json_table.result @@ -722,7 +722,43 @@ insert into t1 values ('Jeans', '{"color": ["black"], "price": 60}'); insert into t1 values ('Jeans', '{"color": ["gray"], "price": 60}'); insert into t1 values ('Laptop', '{"color": ["black"], "price": 1000}'); insert into t1 values ('Shirt', '{"color": ["black"], "price": 20}'); -select t.item_name, jt.* from (select t1.item_name, concat(concat(concat("{\"color\": ",concat(concat("[\"",group_concat(jt.color separator "\", \"")),"\"]")),','),concat(concat("\"price\": ",jt.price),'}')) as item_props from t1, json_table(t1.item_props, '$' columns (nested path '$.color[*]' columns (color varchar(32) path '$'), price int path '$.price')) as jt group by t1.item_name, jt.price) as t, json_table(t.item_props, '$' columns (nested path '$.color[*]' columns (color varchar(32) path '$'), price int path '$.price')) as jt order by t.item_name, jt.price, jt.color; +select +t.item_name, +jt.* +from +(select +t1.item_name, +concat( +concat( +concat( +"{\"color\": ", +concat( +concat("[\"", + group_concat( jt.color separator "\", \"") + ), + "\"]") +),',' + ), +concat(concat("\"price\": ",jt.price),'}') +) as item_props +from +t1, +json_table( +t1.item_props, +'$' columns ( +nested path '$.color[*]' columns (color varchar(32) path '$'), +price int path '$.price') +) as jt +group by +t1.item_name, jt.price +) as t, +json_table(t.item_props, +'$' columns ( +nested path '$.color[*]' columns (color varchar(32) path '$'), +price int path '$.price') +) as jt +order by +t.item_name, jt.price, jt.color; item_name color price Jeans brown 50 Jeans green 50 diff --git a/mysql-test/suite/json/t/json_table.test b/mysql-test/suite/json/t/json_table.test index 39c97f73496..b8b16a750d1 100644 --- a/mysql-test/suite/json/t/json_table.test +++ b/mysql-test/suite/json/t/json_table.test @@ -623,7 +623,44 @@ insert into t1 values ('Jeans', '{"color": ["gray"], "price": 60}'); insert into t1 values ('Laptop', '{"color": ["black"], "price": 1000}'); insert into t1 values ('Shirt', '{"color": ["black"], "price": 20}'); -select t.item_name, jt.* from (select t1.item_name, concat(concat(concat("{\"color\": ",concat(concat("[\"",group_concat(jt.color separator "\", \"")),"\"]")),','),concat(concat("\"price\": ",jt.price),'}')) as item_props from t1, json_table(t1.item_props, '$' columns (nested path '$.color[*]' columns (color varchar(32) path '$'), price int path '$.price')) as jt group by t1.item_name, jt.price) as t, json_table(t.item_props, '$' columns (nested path '$.color[*]' columns (color varchar(32) path '$'), price int path '$.price')) as jt order by t.item_name, jt.price, jt.color; +select + t.item_name, + jt.* +from + (select + t1.item_name, + concat( + concat( + concat( + "{\"color\": ", + concat( + concat("[\"", + group_concat( jt.color separator "\", \"") + ), + "\"]") + ),',' + ), + concat(concat("\"price\": ",jt.price),'}') + ) as item_props + from + t1, + json_table( + t1.item_props, + '$' columns ( + nested path '$.color[*]' columns (color varchar(32) path '$'), + price int path '$.price') + ) as jt + group by + t1.item_name, jt.price + ) as t, + + json_table(t.item_props, + '$' columns ( + nested path '$.color[*]' columns (color varchar(32) path '$'), + price int path '$.price') + ) as jt +order by + t.item_name, jt.price, jt.color; drop table t1; diff --git a/sql/json_table.cc b/sql/json_table.cc index 8ad246f39f1..39eb415aaed 100644 --- a/sql/json_table.cc +++ b/sql/json_table.cc @@ -148,7 +148,7 @@ List<TABLE_LIST>* get_disallowed_table_deps(MEM_ROOT *mem_root, { List<TABLE_LIST> *disallowed_tables; - if (!(disallowed_tables = new List<TABLE_LIST>)) + if (!(disallowed_tables = new (mem_root) List<TABLE_LIST>)) return NULL; int res= get_disallowed_table_deps_for_list(mem_root, table_func,

1 0

[Commits] d3004d1b880: MDEV-25202: JSON_TABLE: Early table reference leads to unexpected result set
by psergey 17 Apr '21

17 Apr '21

revision-id: d3004d1b88072c170a0764a329bfdcec5406328e (mariadb-10.5.2-591-gd3004d1b880) parent(s): 0aa8e0eabdf3e157159d9dd6e0338633e4b49baa author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-04-17 10:55:35 +0300 message: MDEV-25202: JSON_TABLE: Early table reference leads to unexpected result set Followup to review input address commit: pass the new parameter type to find_field_in_table_ref(). --- sql/sql_acl.cc | 5 +++-- sql/sql_help.cc | 2 +- sql/sql_select.cc | 4 ++-- 3 files changed, 6 insertions(+), 5 deletions(-) diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc index 8f096f4074d..24e730dcb6b 100644 --- a/sql/sql_acl.cc +++ b/sql/sql_acl.cc @@ -6966,8 +6966,9 @@ int mysql_table_grant(THD *thd, TABLE_LIST *table_list, Field *f=find_field_in_table_ref(thd, table_list, column->column.ptr(), column->column.length(), column->column.ptr(), NULL, NULL, - table_map(0), NULL, TRUE, FALSE, - &unused_field_idx, FALSE, &dummy); + ignored_tables_list_t(NULL), NULL, + TRUE, FALSE, &unused_field_idx, FALSE, + &dummy); if (unlikely(f == (Field*)0)) { my_error(ER_BAD_FIELD_ERROR, MYF(0), diff --git a/sql/sql_help.cc b/sql/sql_help.cc index 916315cf67e..01c47bc2b21 100644 --- a/sql/sql_help.cc +++ b/sql/sql_help.cc @@ -99,7 +99,7 @@ static bool init_fields(THD *thd, TABLE_LIST *tables, Lex_cstring_strlen(find_fields->table_name), Lex_cstring_strlen(find_fields->field_name))); if (!(find_fields->field= find_field_in_tables(thd, field, tables, NULL, - table_map(0), + ignored_tables_list_t(NULL), 0, REPORT_ALL_ERRORS, 1, TRUE))) DBUG_RETURN(1); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 82af960078b..d96a6b10cae 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -24521,8 +24521,8 @@ find_order_in_list(THD *thd, Ref_ptr_array ref_pointer_array, order_item_type == Item::REF_ITEM) { from_field= find_field_in_tables(thd, (Item_ident*) order_item, tables, - NULL, table_map(0), &view_ref, - IGNORE_ERRORS, FALSE, FALSE); + NULL, ignored_tables_list_t(NULL), + &view_ref, IGNORE_ERRORS, FALSE, FALSE); if (!from_field) from_field= (Field*) not_found_field; }

1 0

[Commits] a4b84122438: MDEV-25202: JSON_TABLE: Early table reference leads to unexpected result set
by psergey 16 Apr '21

16 Apr '21

revision-id: a4b84122438579e96f7aa7aabf19eca7afa9e88b (mariadb-10.5.2-589-ga4b84122438) parent(s): 36c3be336616cb95443c5164a0e082862c6270d0 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-04-16 23:55:17 +0300 message: MDEV-25202: JSON_TABLE: Early table reference leads to unexpected result set Address review input: switch Name_resolution_context::ignored_tables from table_map to a list of TABLE_LIST objects. The rationale is that table bits may be changed due to query rewrites, etc, which may potentially require updating ignored_tables. --- mysql-test/suite/json/r/json_table.result | 17 ++++ mysql-test/suite/json/t/json_table.test | 13 ++++ sql/item.cc | 15 ++++ sql/item.h | 7 +- sql/json_table.cc | 125 ++++++++++++++++++------------ sql/json_table.h | 7 +- sql/sql_base.cc | 19 +++-- sql/sql_base.h | 5 +- 8 files changed, 147 insertions(+), 61 deletions(-) diff --git a/mysql-test/suite/json/r/json_table.result b/mysql-test/suite/json/r/json_table.result index 6cb197890c1..a996401496f 100644 --- a/mysql-test/suite/json/r/json_table.result +++ b/mysql-test/suite/json/r/json_table.result @@ -790,6 +790,23 @@ ON t1.a = tt.b; a b c o 1 1 {} 1 2 2 [] NULL +prepare s from +"SELECT * +FROM + t1 RIGHT JOIN + v2 AS tt + LEFT JOIN + JSON_TABLE(CONCAT(tt.c,''), '$' COLUMNS(o FOR ORDINALITY)) AS jt + ON tt.b = jt.o + ON t1.a = tt.b"; +execute s; +a b c o +1 1 {} 1 +2 2 [] NULL +execute s; +a b c o +1 1 {} 1 +2 2 [] NULL DROP VIEW v2; DROP TABLE t1, t2; # diff --git a/mysql-test/suite/json/t/json_table.test b/mysql-test/suite/json/t/json_table.test index 41233c8a8ea..39c97f73496 100644 --- a/mysql-test/suite/json/t/json_table.test +++ b/mysql-test/suite/json/t/json_table.test @@ -682,6 +682,19 @@ FROM ON tt.b = jt.o ON t1.a = tt.b; +prepare s from +"SELECT * +FROM + t1 RIGHT JOIN + v2 AS tt + LEFT JOIN + JSON_TABLE(CONCAT(tt.c,''), '$' COLUMNS(o FOR ORDINALITY)) AS jt + ON tt.b = jt.o + ON t1.a = tt.b"; +execute s; +execute s; + + DROP VIEW v2; DROP TABLE t1, t2; diff --git a/sql/item.cc b/sql/item.cc index 18bb26e18eb..6b593925369 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -10704,3 +10704,18 @@ bool Item::cleanup_excluding_immutables_processor (void *arg) return false; } } + + +bool ignored_list_includes_table(ignored_tables_list_t list, TABLE_LIST *tbl) +{ + if (!list) + return false; + List_iterator<TABLE_LIST> it(*list); + TABLE_LIST *list_tbl; + while ((list_tbl = it++)) + { + if (list_tbl == tbl) + return true; + } + return false; +} diff --git a/sql/item.h b/sql/item.h index 747f366e33d..4fdac92f357 100644 --- a/sql/item.h +++ b/sql/item.h @@ -162,6 +162,9 @@ void dummy_error_processor(THD *thd, void *data); void view_error_processor(THD *thd, void *data); +typedef List<TABLE_LIST>* ignored_tables_list_t; +bool ignored_list_includes_table(ignored_tables_list_t list, TABLE_LIST *tbl); + /* Instances of Name_resolution_context store the information necessary for name resolution of Items and other context analysis of a query made in @@ -236,7 +239,7 @@ struct Name_resolution_context: Sql_alloc Bitmap of tables that should be ignored when doing name resolution. Normally it is {0}. Non-zero values are used by table functions. */ - table_map ignored_tables; + ignored_tables_list_t ignored_tables; /* Security context of this name resolution context. It's used for views @@ -247,7 +250,7 @@ struct Name_resolution_context: Sql_alloc Name_resolution_context() :outer_context(0), table_list(0), select_lex(0), error_processor_data(0), - ignored_tables(0), + ignored_tables(NULL), security_ctx(0) {} diff --git a/sql/json_table.cc b/sql/json_table.cc index a16adc25f08..c79abf942fa 100644 --- a/sql/json_table.cc +++ b/sql/json_table.cc @@ -44,9 +44,14 @@ static table_function_handlerton table_function_hton; /* @brief - Collect a bitmap of tables that a given table function cannot have + Collect a set of tables that a given table function cannot have references to. + @param + table_func The table function we are connecting info for + join_list The nested join to be processed + disallowed_tables Collect the tables here. + @detail According to the SQL standard, a table function can refer to any table that's "preceding" it in the FROM clause. @@ -80,16 +85,16 @@ static table_function_handlerton table_function_hton; we are ok with operating on the tables "in the left join order". @return - TRUE - enumeration has found the Table Function instance. The bitmap is - ready. - FALSE - Otherwise - + 0 - Continue + 1 - Finish the process, success + -1 - Finish the process, failure */ static -bool get_disallowed_table_deps_for_list(table_map table_func_bit, - List<TABLE_LIST> *join_list, - table_map *disallowed_tables) +int get_disallowed_table_deps_for_list(MEM_ROOT *mem_root, + TABLE_LIST *table_func, + List<TABLE_LIST> *join_list, + List<TABLE_LIST> *disallowed_tables) { TABLE_LIST *table; NESTED_JOIN *nested_join; @@ -99,23 +104,25 @@ bool get_disallowed_table_deps_for_list(table_map table_func_bit, { if ((nested_join= table->nested_join)) { - if (get_disallowed_table_deps_for_list(table_func_bit, - &nested_join->join_list, - disallowed_tables)) - return true; + int res; + if ((res= get_disallowed_table_deps_for_list(mem_root, table_func, + &nested_join->join_list, + disallowed_tables))) + return res; } else { - *disallowed_tables |= table->table->map; - if (table_func_bit == table->table->map) + if (disallowed_tables->push_back(table, mem_root)) + return -1; + if (table == table_func) { // This is the JSON_TABLE(...) that are we're computing dependencies // for. - return true; + return 1; // Finish the processing } } } - return false; + return 0; // Continue } @@ -129,19 +136,31 @@ bool get_disallowed_table_deps_for_list(table_map table_func_bit, See get_disallowed_table_deps_for_list @return - Bitmap of tables that table function can NOT have references to. + NULL - Out of memory + Other - A list of tables that the function cannot have references to. May + be empty. */ static -table_map get_disallowed_table_deps(JOIN *join, table_map table_func_bit) +List<TABLE_LIST>* get_disallowed_table_deps(MEM_ROOT *mem_root, + SELECT_LEX *select, + TABLE_LIST *table_func) { - table_map disallowed_tables= 0; - if (!get_disallowed_table_deps_for_list(table_func_bit, join->join_list, - &disallowed_tables)) - { - // We haven't found the table with table_func_bit in all tables? - DBUG_ASSERT(0); - } + List<TABLE_LIST> *disallowed_tables; + + if (!(disallowed_tables = new List<TABLE_LIST>)) + return NULL; + + int res= get_disallowed_table_deps_for_list(mem_root, table_func, + select->join_list, + disallowed_tables); + + // The collection process must have finished + DBUG_ASSERT(res != 0); + + if (res == -1) + return NULL; // Out of memory + return disallowed_tables; } @@ -1034,48 +1053,56 @@ bool push_table_function_arg_context(LEX *lex, MEM_ROOT *alloc) Perform name-resolution phase tasks @detail - - The only argument that needs resolution is the JSON text - - Then, we need to set dependencies: if JSON_TABLE refers to table's - column, e.g. + The only argument that needs name resolution is the first parameter which + has the JSON text: + + JSON_TABLE(json_doc, ... ) - JSON_TABLE (t1.col ... ) AS t2 + The argument may refer to other tables and uses special name resolution + rules (see get_disallowed_table_deps_for_list for details). This function + sets up Name_resolution_context object appropriately before calling + fix_fields for the argument. - then it can be computed only after table t1. - - The dependencies must not form a loop. + @return + false OK + true Fatal error */ -int Table_function_json_table::setup(THD *thd, TABLE_LIST *sql_table, +bool Table_function_json_table::setup(THD *thd, TABLE_LIST *sql_table, SELECT_LEX *s_lex) { - TABLE *t= sql_table->table; - thd->where= "JSON_TABLE argument"; - { - bool save_is_item_list_lookup; - bool res; - save_is_item_list_lookup= s_lex->is_item_list_lookup; - s_lex->is_item_list_lookup= 0; + if (!m_context_setup_done) + { + m_context_setup_done= true; // Prepare the name resolution context. First, copy the context that is // used for name resolution of the WHERE clause *m_context= s_lex->context; // Then, restrict it to only allow to refer to tables that come before the // table function reference - m_context->ignored_tables= get_disallowed_table_deps(s_lex->join, t->map); + if (!(m_context->ignored_tables= + get_disallowed_table_deps(thd->stmt_arena->mem_root, s_lex, + sql_table))) + return TRUE; // Error + } - // Do the same what setup_without_group() does: do not count the referred - // fields in non_agg_field_used: - const bool saved_non_agg_field_used= s_lex->non_agg_field_used(); + bool save_is_item_list_lookup; + save_is_item_list_lookup= s_lex->is_item_list_lookup; + s_lex->is_item_list_lookup= 0; - res= m_json->fix_fields_if_needed(thd, &m_json); + // Do the same what setup_without_group() does: do not count the referred + // fields in non_agg_field_used: + const bool saved_non_agg_field_used= s_lex->non_agg_field_used(); - s_lex->is_item_list_lookup= save_is_item_list_lookup; - s_lex->set_non_agg_field_used(saved_non_agg_field_used); + bool res= m_json->fix_fields_if_needed(thd, &m_json); - if (res) - return TRUE; - } + s_lex->is_item_list_lookup= save_is_item_list_lookup; + s_lex->set_non_agg_field_used(saved_non_agg_field_used); + + if (res) + return TRUE; // Error return FALSE; } diff --git a/sql/json_table.h b/sql/json_table.h index 09e4295d80c..beae5405d25 100644 --- a/sql/json_table.h +++ b/sql/json_table.h @@ -198,7 +198,7 @@ class Table_function_json_table : public Sql_alloc List<Json_table_column> m_columns; /*** Name resolution functions ***/ - int setup(THD *thd, TABLE_LIST *sql_table, SELECT_LEX *s_lex); + bool setup(THD *thd, TABLE_LIST *sql_table, SELECT_LEX *s_lex); int walk_items(Item_processor processor, bool walk_subquery, void *argument); @@ -226,7 +226,8 @@ class Table_function_json_table : public Sql_alloc /*** Construction interface to be used from the parser ***/ Table_function_json_table(Item *json): - m_json(json) + m_json(json), + m_context_setup_done(false) { cur_parent= &m_nested_path; last_sibling_hook= &m_nested_path.m_nested; @@ -250,6 +251,8 @@ class Table_function_json_table : public Sql_alloc /* Context to be used for resolving the first argument. */ Name_resolution_context *m_context; + bool m_context_setup_done; + /* Current NESTED PATH level being parsed */ Json_table_nested_path *cur_parent; diff --git a/sql/sql_base.cc b/sql/sql_base.cc index a859f83c5f7..0fc5159f7b4 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -6097,7 +6097,8 @@ Field * find_field_in_table_ref(THD *thd, TABLE_LIST *table_list, const char *name, size_t length, const char *item_name, const char *db_name, - const char *table_name, table_map ignored_tables, + const char *table_name, + ignored_tables_list_t ignored_tables, Item **ref, bool check_privileges, bool allow_rowid, uint *cached_field_index_ptr, @@ -6190,8 +6191,13 @@ find_field_in_table_ref(THD *thd, TABLE_LIST *table_list, TABLE_LIST *table; while ((table= it++)) { - if (table->table && (table->table->map & ignored_tables)) + /* + Check if the table is in the ignore list. Only base tables can be in + the ignore list. + */ + if (table->table && ignored_list_includes_table(ignored_tables, table)) continue; + if ((fld= find_field_in_table_ref(thd, table, name, length, item_name, db_name, table_name, ignored_tables, ref, check_privileges, allow_rowid, @@ -6318,8 +6324,8 @@ Field *find_field_in_table_sef(TABLE *table, const char *name) first_table list of tables to be searched for item last_table end of the list of tables to search for item. If NULL then search to the end of the list 'first_table'. - ignored_tables Bitmap of tables that should be ignored. Do not try - to find the field in those. + ignored_tables Set of tables that should be ignored. Do not try to + find the field in those. ref if 'item' is resolved to a view field, ref is set to point to the found view field report_error Degree of error reporting: @@ -6347,7 +6353,7 @@ Field *find_field_in_table_sef(TABLE *table, const char *name) Field * find_field_in_tables(THD *thd, Item_ident *item, TABLE_LIST *first_table, TABLE_LIST *last_table, - table_map ignored_tables, + ignored_tables_list_t ignored_tables, Item **ref, find_item_error_report_type report_error, bool check_privileges, bool register_tree_change) { @@ -6471,7 +6477,8 @@ find_field_in_tables(THD *thd, Item_ident *item, for (; cur_table != last_table ; cur_table= cur_table->next_name_resolution_table) { - if (cur_table->table && (cur_table->table->map & ignored_tables)) + if (cur_table->table && + ignored_list_includes_table(ignored_tables, cur_table)) continue; Field *cur_field= find_field_in_table_ref(thd, cur_table, name, length, diff --git a/sql/sql_base.h b/sql/sql_base.h index 1836c07497a..922c61ca123 100644 --- a/sql/sql_base.h +++ b/sql/sql_base.h @@ -195,14 +195,15 @@ bool fill_record(THD *thd, TABLE *table, Field **field, List<Item> &values, Field * find_field_in_tables(THD *thd, Item_ident *item, TABLE_LIST *first_table, TABLE_LIST *last_table, - table_map ignored_tables, + ignored_tables_list_t ignored_tables, Item **ref, find_item_error_report_type report_error, bool check_privileges, bool register_tree_change); Field * find_field_in_table_ref(THD *thd, TABLE_LIST *table_list, const char *name, size_t length, const char *item_name, const char *db_name, - const char *table_name, table_map ignored_tables, + const char *table_name, + ignored_tables_list_t ignored_tables, Item **ref, bool check_privileges, bool allow_rowid, uint *cached_field_index_ptr, bool register_tree_change, TABLE_LIST **actual_table);

1 0

[Commits] dcfb34f: MDEV-25362 Incorrect name resolution for subqueries in ON expressions
by IgorBabaev 16 Apr '21

16 Apr '21

revision-id: dcfb34f38c4b14820754443b4565e3c7b590dc85 (mariadb-10.2.31-867-gdcfb34f) parent(s): 55a7682a30963c1ee5e367d9f3b9b2e50e12d663 author: Igor Babaev committer: Igor Babaev timestamp: 2021-04-16 09:53:16 -0700 message: MDEV-25362 Incorrect name resolution for subqueries in ON expressions This patch sets the proper name resolution context for outer references used in a subquery from an ON clause. Usually this context is more narrow than the name resolution context of the parent select that were used before this fix. This fix revealed another problem that concerned ON expressions used in from clauses of specifications of derived tables / views / CTEs. The name resolution outer context for such ON expression must be set to NULL to prevent name resolution beyond the derived table where it is used. The solution to resolve this problem applied in sql_derived.cc was provided by Sergei Petrunia <sergey(a)mariadb.com>. The change in sql_parse.cc is not good for 10.4+. A corresponding diff for 10.4+ will be provided in JIRA entry for this bug. Approved by Oleksandr Byelkin <sanja(a)mariadb.com> --- mysql-test/r/join_outer.result | 40 +++++++++++++++++++++++++++++ mysql-test/r/join_outer_jcl6.result | 40 +++++++++++++++++++++++++++++ mysql-test/t/join_outer.test | 50 +++++++++++++++++++++++++++++++++++++ sql/sql_derived.cc | 31 +++++++++++++++++++++++ sql/sql_parse.cc | 4 ++- 5 files changed, 164 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index fdc36b7..1995640 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -2682,4 +2682,44 @@ id timestamp modifiedBy id REV REVTYPE profile_id id REV person_id id REV 12 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 117548 300006 300006 97697 DROP TABLE t1,t2,t3,t4; # end of 10.1 tests +# +# MDEV-25362: name resolution for subqueries in ON expressions +# +create table t1 (a int, b int); +create table t2 (c int, d int); +create table t3 (e int, f int); +create table t4 (g int, h int); +explain +select * +from +t1 left join +(t2 +join +t3 on +(t3.f=t1.a) +) on (t2.c=t1.a ); +ERROR 42S22: Unknown column 't1.a' in 'on clause' +explain +select * +from +t1 left join +(t2 +join +t3 on +(t3.f=(select max(g) from t4 where t4.h=t1.a)) +) on (t2.c=t1.a ); +ERROR 42S22: Unknown column 't1.a' in 'where clause' +drop table t1,t2,t3,t4; +create table t1 (a int); +insert into t1 values (1),(2); +create table t2 (b int); +insert into t2 values (1),(2); +create table t3 (c int); +insert into t3 values (1),(2); +select * from ( select * from t1 left join t2 +on b in (select x from t3 as sq1) +) as sq2; +ERROR 42S22: Unknown column 'x' in 'field list' +drop table t1,t2,t3; +# end of 10.2 tests SET optimizer_switch=@org_optimizer_switch; diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result index 6a6b100..58df420 100644 --- a/mysql-test/r/join_outer_jcl6.result +++ b/mysql-test/r/join_outer_jcl6.result @@ -2690,6 +2690,46 @@ id timestamp modifiedBy id REV REVTYPE profile_id id REV person_id id REV 12 1294650860266 u62C^Kzx3wH8 8 12 2 2 2 117548 300006 300006 97697 DROP TABLE t1,t2,t3,t4; # end of 10.1 tests +# +# MDEV-25362: name resolution for subqueries in ON expressions +# +create table t1 (a int, b int); +create table t2 (c int, d int); +create table t3 (e int, f int); +create table t4 (g int, h int); +explain +select * +from +t1 left join +(t2 +join +t3 on +(t3.f=t1.a) +) on (t2.c=t1.a ); +ERROR 42S22: Unknown column 't1.a' in 'on clause' +explain +select * +from +t1 left join +(t2 +join +t3 on +(t3.f=(select max(g) from t4 where t4.h=t1.a)) +) on (t2.c=t1.a ); +ERROR 42S22: Unknown column 't1.a' in 'where clause' +drop table t1,t2,t3,t4; +create table t1 (a int); +insert into t1 values (1),(2); +create table t2 (b int); +insert into t2 values (1),(2); +create table t3 (c int); +insert into t3 values (1),(2); +select * from ( select * from t1 left join t2 +on b in (select x from t3 as sq1) +) as sq2; +ERROR 42S22: Unknown column 'x' in 'field list' +drop table t1,t2,t3; +# end of 10.2 tests SET optimizer_switch=@org_optimizer_switch; set join_cache_level=default; set @@optimizer_switch=@save_optimizer_switch_jcl6; diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index c5b2c98..9ad2c48 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -2190,4 +2190,54 @@ DROP TABLE t1,t2,t3,t4; --echo # end of 10.1 tests +--echo # +--echo # MDEV-25362: name resolution for subqueries in ON expressions +--echo # + +create table t1 (a int, b int); +create table t2 (c int, d int); +create table t3 (e int, f int); +create table t4 (g int, h int); + +--error ER_BAD_FIELD_ERROR +explain +select * +from + t1 left join + (t2 + join + t3 on + (t3.f=t1.a) + ) on (t2.c=t1.a ); + +# This must produce an error: +--error ER_BAD_FIELD_ERROR +explain +select * +from + t1 left join + (t2 + join + t3 on + (t3.f=(select max(g) from t4 where t4.h=t1.a)) + ) on (t2.c=t1.a ); + +drop table t1,t2,t3,t4; + +create table t1 (a int); +insert into t1 values (1),(2); +create table t2 (b int); +insert into t2 values (1),(2); +create table t3 (c int); +insert into t3 values (1),(2); + +--error ER_BAD_FIELD_ERROR +select * from ( select * from t1 left join t2 + on b in (select x from t3 as sq1) + ) as sq2; + +drop table t1,t2,t3; + +--echo # end of 10.2 tests + SET optimizer_switch=@org_optimizer_switch; diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc index be5905d..5a85b7e 100644 --- a/sql/sql_derived.cc +++ b/sql/sql_derived.cc @@ -563,6 +563,32 @@ bool mysql_derived_init(THD *thd, LEX *lex, TABLE_LIST *derived) /* + @brief + Prevent name resolution out of context of ON expressions in derived tables + + @param + join_list list of tables used in from list of a derived + + @details + The function sets the Name_resolution_context::outer_context to NULL + for all ON expressions contexts in the given join list. It does this + recursively for all nested joins the list contains. +*/ + +static void nullify_outer_context_for_on_clauses(List<TABLE_LIST>& join_list) +{ + List_iterator<TABLE_LIST> li(join_list); + while (TABLE_LIST *table= li++) + { + if (table->on_context) + table->on_context->outer_context= NULL; + if (table->nested_join) + nullify_outer_context_for_on_clauses(table->nested_join->join_list); + } +} + + +/* Create temporary table structure (but do not fill it) @param thd Thread handle @@ -695,7 +721,12 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived) /* prevent name resolving out of derived table */ for (SELECT_LEX *sl= first_select; sl; sl= sl->next_select()) { + // Prevent it for the WHERE clause sl->context.outer_context= 0; + + // And for ON clauses, if there are any + nullify_outer_context_for_on_clauses(*sl->join_list); + if (!derived->is_with_table_recursive_reference() || (!derived->with->with_anchor && !derived->with->is_with_prepared_anchor())) diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 573df24..9436e11 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -7418,6 +7418,7 @@ mysql_new_select(LEX *lex, bool move_down) DBUG_RETURN(1); select_lex->select_number= ++thd->lex->stmt_lex->current_select_number; select_lex->parent_lex= lex; /* Used in init_query. */ + Name_resolution_context *curr_context= lex->context_stack.head(); select_lex->init_query(); select_lex->init_select(); lex->nest_level++; @@ -7448,7 +7449,8 @@ mysql_new_select(LEX *lex, bool move_down) By default we assume that it is usual subselect and we have outer name resolution context, if no we will assign it to 0 later */ - select_lex->context.outer_context= &select_lex->outer_select()->context; + + select_lex->context.outer_context= curr_context; } else {

1 0