summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/multi_update.result53
-rw-r--r--mysql-test/t/multi_update.test53
-rw-r--r--sql/sql_base.cc3
-rw-r--r--sql/sql_class.h4
-rw-r--r--sql/sql_delete.cc15
-rw-r--r--sql/sql_derived.cc5
-rw-r--r--sql/sql_lex.cc21
-rw-r--r--sql/sql_lex.h2
-rw-r--r--sql/sql_update.cc41
9 files changed, 171 insertions, 26 deletions
diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result
index a5c60d6dfc2..1532ebb6420 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 b5328f74e70..dff3938d02f 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 f10846acdd7..3403f9e03ff 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 c47ea9c9020..ce4bf67e745 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -5600,7 +5600,8 @@ public:
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 @@ public:
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 e2d4cd47580..61a3b4e8e75 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 5a85b7ea7e3..5f90f2f9ab0 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 bfe773b2c00..57c6dfad4e5 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -4623,6 +4623,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 31751a16471..49265accc07 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -1147,6 +1147,8 @@ public:
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 01743a6751e..e8b973c03bd 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)
View Lorry Controller Status