From 55a8c28c27778d018a145ce25e61bdcda7a764a5 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 14 Sep 2004 19:28:29 +0300 Subject: fixed merged view fields names (BUG#5147) support of merged VIEW over several tables added (WL#1809) mysql-test/r/view.result: merge of VIEW with several tables mysql-test/t/view.test: merge of VIEW with several tables sql/item.cc: renaming Item and restoring item name on cleunup() sql/item.h: renaming Item and restoring item name on cleunup() debug output added sql/item_cmpfunc.h: setup_conds() changed to support two tables lists sql/item_subselect.cc: list of table leaves used instead of local table list for name resolving sql/mysql_priv.h: setup_conds() and setup_tables() changed to support two tables lists sql/opt_sum.cc: list of table leaves used instead of local table list for name resolving sql/sp.cc: setup_tables() changed to support two tables lists sql/sql_base.cc: skip temporary tables in table finding fixed merged view fields names (BUG#5147) sql/sql_delete.cc: setup_conds() and setup_tables() changed to support two tables lists sql/sql_help.cc: setup_tables() changed to support two tables lists sql/sql_insert.cc: setup_tables() changed to support two tables lists name handling support sql/sql_lex.cc: allow view with several tables for MERGE sql/sql_lex.h: new table list sql/sql_load.cc: setup_tables() changed to support two tables lists sql/sql_olap.cc: setup_tables() changed to support two tables lists sql/sql_parse.cc: new list support sql/sql_prepare.cc: new list support sql/sql_select.cc: list of table leaves used instead of local table list for name resolving sql/sql_update.cc: setup_conds() and setup_tables() changed to support two tables lists sql/sql_view.cc: support of MERGED VIEWS with several tables sql/sql_yacc.yy: removed blanks in new code sql/table.cc: fixed setup view code support of merged VIEW over several tables added sql/table.h: fixed merged view fields names (BUG#5147) --- sql/sql_update.cc | 35 +++++++++++++++++++++-------------- 1 file changed, 21 insertions(+), 14 deletions(-) (limited to 'sql/sql_update.cc') diff --git a/sql/sql_update.cc b/sql/sql_update.cc index dc867968262..79f3950e21f 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -497,8 +497,8 @@ int mysql_prepare_update(THD *thd, TABLE_LIST *table_list, tables.table= table; tables.alias= table_list->alias; - if (setup_tables(thd, table_list, conds) || - setup_conds(thd, table_list, conds) || + if (setup_tables(thd, table_list, conds, &select_lex->leaf_tables, 0) || + setup_conds(thd, table_list, select_lex->leaf_tables, conds) || select_lex->setup_ref_array(thd, order_num) || setup_order(thd, select_lex->ref_pointer_array, table_list, all_fields, all_fields, order) || @@ -542,25 +542,31 @@ int mysql_multi_update_prepare(THD *thd) TABLE_LIST *table_list= lex->query_tables; List *fields= &lex->select_lex.item_list; TABLE_LIST *tl; + TABLE_LIST *leaves; table_map tables_for_update= 0, readonly_tables= 0; int res; bool update_view= 0; DBUG_ENTER("mysql_multi_update_prepare"); + + if (setup_tables(thd, table_list, &lex->select_lex.where, + &lex->select_lex.leaf_tables, 0)) + DBUG_RETURN(-1); /* Ensure that we have update privilege for all tables and columns in the SET part */ - for (tl= table_list; tl; tl= tl->next_local) + for (tl= (leaves= lex->select_lex.leaf_tables); tl; tl= tl->next_leaf) { - TABLE *table= tl->table; /* Update of derived tables is checked later We don't check privileges here, becasue then we would get error "UPDATE command denided .. for column N" instead of "Target table ... is not updatable" */ - if (!tl->derived) - tl->grant.want_privilege= table->grant.want_privilege= + TABLE *table= tl->table; + TABLE_LIST *tlist; + if (!(tlist= tl->belong_to_view?tl->belong_to_view:tl)->derived) + tlist->grant.want_privilege= table->grant.want_privilege= (UPDATE_ACL & ~table->grant.privilege); } @@ -568,14 +574,13 @@ int mysql_multi_update_prepare(THD *thd) setup_tables() need for VIEWs. JOIN::prepare() will not do it second time. */ - if (setup_tables(thd, table_list, &lex->select_lex.where) || - (thd->lex->select_lex.no_wrap_view_item= 1, + if ((thd->lex->select_lex.no_wrap_view_item= 1, res= setup_fields(thd, 0, table_list, *fields, 1, 0, 0), thd->lex->select_lex.no_wrap_view_item= 0, res)) DBUG_RETURN(-1); - for (tl= table_list; tl ; tl= tl->next_local) + for (tl= table_list; tl; tl= tl->next_local) { if (tl->view) { @@ -602,25 +607,26 @@ int mysql_multi_update_prepare(THD *thd) /* Count tables and setup timestamp handling */ - for (tl= table_list; tl ; tl= tl->next_local) + for (tl= leaves; tl; tl= tl->next_leaf) { TABLE *table= tl->table; + TABLE_LIST *tlist= tl->belong_to_view?tl->belong_to_view:tl; /* We only need SELECT privilege for columns in the values list */ - tl->grant.want_privilege= table->grant.want_privilege= + tlist->grant.want_privilege= table->grant.want_privilege= (SELECT_ACL & ~table->grant.privilege); // Only set timestamp column if this is not modified if (table->timestamp_field && table->timestamp_field->query_id == thd->query_id) table->timestamp_on_update_now= 0; - if (!tl->updatable || check_key_in_view(thd, tl)) + if (!tlist->updatable || check_key_in_view(thd, tl)) readonly_tables|= table->map; } if (tables_for_update & readonly_tables) { // find readonly table/view which cause error - for (tl= table_list; tl ; tl= tl->next_local) + for (tl= leaves; tl; tl= tl->next_local) { if ((readonly_tables & tl->table->map) && (tables_for_update & tl->table->map)) @@ -726,6 +732,7 @@ int multi_update::prepare(List ¬_used_values, update.empty(); for (table_ref= all_tables; table_ref; table_ref= table_ref->next_local) { + /* TODO: add support of view of join support */ TABLE *table=table_ref->table; if (tables_to_update & table->map) { @@ -796,7 +803,7 @@ int multi_update::prepare(List ¬_used_values, for (table_ref= all_tables; table_ref; table_ref= table_ref->next_local) { TABLE *table=table_ref->table; - if (!(tables_to_update & table->map) && + if (!(tables_to_update & table->map) && find_table_in_local_list(update_tables, table_ref->db, table_ref->real_name)) table->no_cache= 1; // Disable row cache -- cgit v1.2.1 From 9aa459f0df7e000224e4ac54c5009eac42ef365a Mon Sep 17 00:00:00 2001 From: unknown Date: Wed, 15 Sep 2004 23:42:56 +0300 Subject: support of join view updateability (WL#1809) include/mysqld_error.h: new error mesaages mysql-test/r/view.result: tests of updatint/inserting join views mysql-test/t/view.test: tests of updatint/inserting join views sql/mysql_priv.h: support of "usual UPDATE" -> "multi UPDATE" conversion sql/share/czech/errmsg.txt: new error mesaages sql/share/danish/errmsg.txt: new error mesaages sql/share/dutch/errmsg.txt: new error mesaages sql/share/english/errmsg.txt: new error mesaages sql/share/estonian/errmsg.txt: new error mesaages sql/share/french/errmsg.txt: new error mesaages sql/share/german/errmsg.txt: new error mesaages sql/share/greek/errmsg.txt: new error mesaages sql/share/hungarian/errmsg.txt: new error mesaages sql/share/italian/errmsg.txt: new error mesaages sql/share/japanese/errmsg.txt: new error mesaages sql/share/korean/errmsg.txt: new error mesaages sql/share/norwegian-ny/errmsg.txt: new error mesaages sql/share/norwegian/errmsg.txt: new error mesaages sql/share/polish/errmsg.txt: new error mesaages sql/share/portuguese/errmsg.txt: new error mesaages sql/share/romanian/errmsg.txt: new error mesaages sql/share/russian/errmsg.txt: new error mesaages sql/share/serbian/errmsg.txt: new error mesaages sql/share/slovak/errmsg.txt: new error mesaages sql/share/spanish/errmsg.txt: new error mesaages sql/share/swedish/errmsg.txt: new error mesaages sql/share/ukrainian/errmsg.txt: new error mesaages sql/sql_base.cc: test to avoid join virew to be catched here sql/sql_class.h: support of join views add to update sql/sql_delete.cc: support of join views add to delete(error issue) sql/sql_insert.cc: support of join views add to insert (order of some check changed, to allow find table which will be inserted in, when we will know which fields will be inserted) mechanism of calling setup_tables() only once fixed for INSERT SELECT sql/sql_parse.cc: support of "usual UPDATE" -> "multi UPDATE" conversion mysql_insert_select_prepare now called in same environment for usual queries and PS preparing support of join views add to delete (error issue) sql/sql_prepare.cc: support of "usual UPDATE" -> "multi UPDATE" conversion support of join views add to delete (error issue) sql/sql_update.cc: support of join views add to update sql/sql_view.cc: join views made updatable sql/sql_view.h: insert_view_fields now can check some errors sql/table.cc: methods to support recursive walk by tables tree sql/table.h: methods to support recursive walk by tables tree --- sql/sql_update.cc | 80 ++++++++++++++++++++++++++++++++++++++++++++++--------- 1 file changed, 67 insertions(+), 13 deletions(-) (limited to 'sql/sql_update.cc') diff --git a/sql/sql_update.cc b/sql/sql_update.cc index 79f3950e21f..1e8cb73fa92 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -87,6 +87,28 @@ static bool check_fields(THD *thd, List &items) } +/* + Process usual UPDATE + + SYNOPSIS + mysql_update() + thd thread handler + fields fields for update + values values of fields for update + conds WHERE clause expression + order_num number of elemen in ORDER BY clause + order ORDER BY clause list + limit limit clause + handle_duplicates how to handle duplicates + + RETURN + 0 - OK + 2 - privilege check and openning table passed, but we need to convert to + multi-update because of view substitution + 1 - error and error sent to client + -1 - error and error is not sent to client +*/ + int mysql_update(THD *thd, TABLE_LIST *table_list, List &fields, @@ -96,7 +118,7 @@ int mysql_update(THD *thd, ha_rows limit, enum enum_duplicates handle_duplicates) { - bool using_limit=limit != HA_POS_ERROR; + bool using_limit=limit != HA_POS_ERROR; bool safe_update= thd->options & OPTION_SAFE_UPDATES; bool used_key_is_modified, transactional_table, log_delayed; int error=0; @@ -117,6 +139,15 @@ int mysql_update(THD *thd, if ((error= open_and_lock_tables(thd, table_list))) DBUG_RETURN(error); + + if (table_list->table == 0) + { + DBUG_ASSERT(table_list->view && + table_list->ancestor && table_list->ancestor->next_local); + DBUG_PRINT("info", ("Switch to multi-update")); + /* convert to multiupdate */ + return 2; + } thd->proc_info="init"; table= table_list->table; table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK); @@ -626,12 +657,30 @@ int mysql_multi_update_prepare(THD *thd) if (tables_for_update & readonly_tables) { // find readonly table/view which cause error - for (tl= leaves; tl; tl= tl->next_local) + for (tl= leaves; tl; tl= tl->next_leaf) { if ((readonly_tables & tl->table->map) && (tables_for_update & tl->table->map)) { - my_error(ER_NON_UPDATABLE_TABLE, MYF(0), tl->alias, "UPDATE"); + TABLE_LIST *table= tl->belong_to_view ? tl->belong_to_view : tl; + my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table->alias, "UPDATE"); + DBUG_RETURN(-1); + } + } + } + + /* check single table update for view compound from several tables */ + for (tl= table_list; tl; tl= tl->next_local) + { + if (tl->table == 0) + { + DBUG_ASSERT(tl->view && + tl->ancestor && tl->ancestor->next_local); + TABLE_LIST *for_update= 0; + if (tl->check_single_table(&for_update, tables_for_update)) + { + my_error(ER_VIEW_MULTIUPDATE, MYF(0), + tl->view_db.str, tl->view_name.str); DBUG_RETURN(-1); } } @@ -647,19 +696,22 @@ int mysql_multi_update(THD *thd, COND *conds, ulong options, enum enum_duplicates handle_duplicates, - SELECT_LEX_UNIT *unit, SELECT_LEX *select_lex) + SELECT_LEX_UNIT *unit, SELECT_LEX *select_lex, + bool converted) { - int res; + int res= 0; multi_update *result; DBUG_ENTER("mysql_multi_update"); - if ((res= open_and_lock_tables(thd, table_list))) + if (!converted && (res= open_and_lock_tables(thd, table_list))) DBUG_RETURN(res); if ((res= mysql_multi_update_prepare(thd))) DBUG_RETURN(res); - if (!(result= new multi_update(thd, table_list, fields, values, + if (!(result= new multi_update(thd, table_list, + thd->lex->select_lex.leaf_tables, + fields, values, handle_duplicates))) DBUG_RETURN(-1); @@ -677,12 +729,14 @@ int mysql_multi_update(THD *thd, multi_update::multi_update(THD *thd_arg, TABLE_LIST *table_list, + TABLE_LIST *leaves_list, List *field_list, List *value_list, enum enum_duplicates handle_duplicates_arg) - :all_tables(table_list), update_tables(0), thd(thd_arg), tmp_tables(0), - updated(0), found(0), fields(field_list), values(value_list), - table_count(0), copy_field(0), handle_duplicates(handle_duplicates_arg), - do_update(1), trans_safe(0), transactional_tables(1) + :all_tables(table_list), leaves(leaves_list), update_tables(0), + thd(thd_arg), tmp_tables(0), updated(0), found(0), fields(field_list), + values(value_list), table_count(0), copy_field(0), + handle_duplicates(handle_duplicates_arg), do_update(1), trans_safe(0), + transactional_tables(1) {} @@ -730,7 +784,7 @@ int multi_update::prepare(List ¬_used_values, */ update.empty(); - for (table_ref= all_tables; table_ref; table_ref= table_ref->next_local) + for (table_ref= leaves; table_ref; table_ref= table_ref->next_leaf) { /* TODO: add support of view of join support */ TABLE *table=table_ref->table; @@ -800,7 +854,7 @@ int multi_update::prepare(List ¬_used_values, which will cause an error when reading a row. (This issue is mostly relevent for MyISAM tables) */ - for (table_ref= all_tables; table_ref; table_ref= table_ref->next_local) + for (table_ref= leaves; table_ref; table_ref= table_ref->next_leaf) { TABLE *table=table_ref->table; if (!(tables_to_update & table->map) && -- cgit v1.2.1 From c9497e2365461fb2547a8cfceb33b68a99d7fe0b Mon Sep 17 00:00:00 2001 From: unknown Date: Sun, 21 Nov 2004 19:33:49 +0200 Subject: post merge --- sql/sql_update.cc | 68 ++++++++++++++++++++++++++++++++++++++++++++----------- 1 file changed, 55 insertions(+), 13 deletions(-) (limited to 'sql/sql_update.cc') diff --git a/sql/sql_update.cc b/sql/sql_update.cc index b60c0d95658..5f84c7435ad 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -127,6 +127,7 @@ int mysql_update(THD *thd, #ifndef NO_EMBEDDED_ACCESS_CHECKS uint want_privilege; #endif + uint table_count= 0; ulong query_id=thd->query_id, timestamp_query_id; ha_rows updated, found; key_map old_used_keys; @@ -138,17 +139,26 @@ int mysql_update(THD *thd, LINT_INIT(used_index); LINT_INIT(timestamp_query_id); - if ((error= open_and_lock_tables(thd, table_list))) - DBUG_RETURN(error); + if (open_tables(thd, table_list, &table_count)) + DBUG_RETURN(thd->net.report_error ? -1 : 1); if (table_list->table == 0) { DBUG_ASSERT(table_list->view && table_list->ancestor && table_list->ancestor->next_local); DBUG_PRINT("info", ("Switch to multi-update")); + /* pass counter value */ + thd->lex->table_count= table_count; /* convert to multiupdate */ return 2; } + + if (lock_tables(thd, table_list, table_count) || + mysql_handle_derived(thd->lex, &mysql_derived_prepare) || + (thd->fill_derived_tables() && + mysql_handle_derived(thd->lex, &mysql_derived_filling))) + DBUG_RETURN(thd->net.report_error ? -1 : 1); + thd->proc_info="init"; table= table_list->table; table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK); @@ -614,14 +624,25 @@ int mysql_multi_update_prepare(THD *thd) table_map tables_for_update; int res; bool update_view= 0; - uint table_count; + /* + if this multi-update was converted from usual update, here is table + counter else junk will be assigned here, but then replaced with real + count in open_tables() + */ + uint table_count= lex->table_count; const bool using_lock_tables= thd->locked_tables != 0; + bool original_multiupdate= (thd->lex->sql_command == SQLCOM_UPDATE_MULTI); DBUG_ENTER("mysql_multi_update_prepare"); /* open tables and create derived ones, but do not lock and fill them */ - if (open_tables(thd, table_list, & table_count) || + if ((original_multiupdate && open_tables(thd, table_list, & table_count)) || mysql_handle_derived(lex, &mysql_derived_prepare)) DBUG_RETURN(thd->net.report_error ? -1 : 1); + /* + setup_tables() need for VIEWs. JOIN::prepare() will call setup_tables() + second time, but this call will do nothing (there are check for second + call in setup_tables()). + */ if (setup_tables(thd, table_list, &lex->select_lex.where, &lex->select_lex.leaf_tables, 0)) DBUG_RETURN(-1); @@ -644,11 +665,6 @@ int mysql_multi_update_prepare(THD *thd) (UPDATE_ACL & ~table->grant.privilege); } - /* - setup_tables() need for VIEWs. JOIN::prepare() will call setup_tables() - second time, but this call will do nothing (there are check for second - call in setup_tables()). - */ if ((lex->select_lex.no_wrap_view_item= 1, res= setup_fields(thd, 0, table_list, *fields, 1, 0, 0), lex->select_lex.no_wrap_view_item= 0, @@ -675,7 +691,16 @@ int mysql_multi_update_prepare(THD *thd) Setup timestamp handling and locking mode */ for (tl= leaves; tl; tl= tl->next_leaf) - + { + TABLE *table= tl->table; + TABLE_LIST *tlist= tl->belong_to_view?tl->belong_to_view:tl; + + /* We only need SELECT privilege for columns in the values list */ + tlist->grant.want_privilege= table->grant.want_privilege= + (SELECT_ACL & ~table->grant.privilege); + /* Only set timestamp column if this is not modified */ + if (table->timestamp_field && + table->timestamp_field->query_id == thd->query_id) table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET; /* if table will be updated then check that it is unique */ @@ -711,6 +736,23 @@ int mysql_multi_update_prepare(THD *thd) tl->table->reginfo.lock_type= tl->lock_type; } + /* check single table update for view compound from several tables */ + for (tl= table_list; tl; tl= tl->next_local) + { + if (tl->table == 0) + { + DBUG_ASSERT(tl->view && + tl->ancestor && tl->ancestor->next_local); + TABLE_LIST *for_update= 0; + if (tl->check_single_table(&for_update, tables_for_update)) + { + my_error(ER_VIEW_MULTIUPDATE, MYF(0), + tl->view_db.str, tl->view_name.str); + DBUG_RETURN(-1); + } + } + } + opened_tables= thd->status_var.opened_tables; /* now lock and fill tables */ if (lock_tables(thd, table_list, table_count)) @@ -740,7 +782,8 @@ int mysql_multi_update_prepare(THD *thd) /* undone setup_tables() */ table_list->setup_is_done= 0; - if (setup_tables(thd, table_list, &lex->select_lex.where) || + if (setup_tables(thd, table_list, &lex->select_lex.where, + &lex->select_lex.leaf_tables, 0) || (lex->select_lex.no_wrap_view_item= 1, res= setup_fields(thd, 0, table_list, *fields, 1, 0, 0), lex->select_lex.no_wrap_view_item= 0, @@ -765,8 +808,7 @@ int mysql_multi_update(THD *thd, COND *conds, ulong options, enum enum_duplicates handle_duplicates, - SELECT_LEX_UNIT *unit, SELECT_LEX *select_lex, - bool converted) + SELECT_LEX_UNIT *unit, SELECT_LEX *select_lex) { int res= 0; multi_update *result; -- cgit v1.2.1