revision-id: 2c233f3dca8d4a12bd96b072aa358784fda5f28c (mariadb-10.3.5-153-g2c233f3dca8) parent(s): 95811f01f34705ea026e4b7bbebe2e00f7061f88 author: Sergei Golubchik committer: Sergei Golubchik timestamp: 2018-04-13 19:13:48 +0200 message: MDEV-14551 Can't find record in table on multi-table update with ORDER BY preserve positions if the multi-update join is using tmp table: * introduce Item_temptable_rowid() that is used to store table->file->position() in the temporary table record * store positions in the tmp table if needed JOIN::add_fields_for_current_rowid() * take positions from the tmp table, not from file->position(): multi_update::prepare2() --- mysql-test/main/multi_update.result | 72 +++++++++++++++++++++++ mysql-test/main/multi_update.test | 46 +++++++++++++++ sql/item.cc | 5 ++ sql/item.h | 3 +- sql/item_func.h | 2 +- sql/item_strfunc.cc | 20 +++++++ sql/item_strfunc.h | 22 +++++++- sql/sql_class.h | 7 ++- sql/sql_insert.cc | 2 +- sql/sql_select.cc | 45 +++++++++++---- sql/sql_select.h | 6 ++ sql/sql_union.cc | 4 +- sql/sql_update.cc | 110 ++++++++++++++++++++++-------------- 13 files changed, 283 insertions(+), 61 deletions(-) diff --git a/mysql-test/main/multi_update.result b/mysql-test/main/multi_update.result index 45239f6e090..c40de47668a 100644 --- a/mysql-test/main/multi_update.result +++ b/mysql-test/main/multi_update.result @@ -968,3 +968,75 @@ NULL 6 7 7 8 8 drop table t1, t2; +create table t1 (i int) engine=memory; +insert t1 values (1),(2); +create table t2 (f int) engine=myisam; +insert t2 values (1),(2); +explain update t1, t2 set f = 126 order by f limit 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 +update t1, t2 set f = 126 order by f limit 2; +select * from t2; +f +126 +2 +drop table t1, t2; +create table t0(a int); +insert t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 (a int, b int, c int, key(a)); +insert t1 select a,a,a from t0; +create table t2 as select * from t1; +create table t3 as select * from t1; +select * from t1, t2 where t1.a=t2.a and t1.b in (select b from t3 where t3.c<=t2.c) order by t2.c, t1.c limit 5; +a b c a b c +0 0 0 0 0 0 +1 1 1 1 1 1 +2 2 2 2 2 2 +3 3 3 3 3 3 +4 4 4 4 4 4 +set optimizer_switch='firstmatch=off'; +explain update t1, t2 set t2.c=1 where t1.a=t2.a and t1.b in (select b from t3 where t3.c< t2.c) order by t2.c, t1.c limit 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using temporary; Using filesort +1 PRIMARY t1 ALL a NULL NULL NULL 10 Using where +1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; Start temporary; End temporary +update t1, t2 set t2.c=1 where t1.a=t2.a and t1.b in (select b from t3 where t3.c<=t2.c) order by t2.c, t1.c limit 5; +select * from t2; +a b c +0 0 1 +1 1 1 +2 2 1 +3 3 1 +4 4 1 +5 5 5 +6 6 6 +7 7 7 +8 8 8 +9 9 9 +set optimizer_switch=default; +drop table t0,t1,t2,t3; +create table t0 (x int); +create table t1 (a int); +create table t2 (b int, c int default 0); +insert t0 (x) values (0),(10); +insert t1 (a) values (1), (2); +insert t2 (b) values (1), (2); +create view v1 as select t2.b,t2.c from t1, t2 +where t1.a=t2.b and t2.b < 3 with check option; +select * from t0 join v1 on (x=c); +x b c +0 1 0 +0 2 0 +explain update v1,t0 set c=1 where b=1 and x=c order by x,b limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where; Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE t0 ALL NULL NULL NULL NULL 2 Using where +update v1,t0 set c=1 where b<3 and x=c order by x,b limit 1; +select * from v1; +b c +1 1 +2 0 +drop view v1; +drop table t0, t1,t2; diff --git a/mysql-test/main/multi_update.test b/mysql-test/main/multi_update.test index 5feebe87a5a..42e34d1e4a1 100644 --- a/mysql-test/main/multi_update.test +++ b/mysql-test/main/multi_update.test @@ -914,3 +914,49 @@ update t1 set c1=NULL; update t1, t2 set t1.c1=t2.c3 where t1.c3=t2.c3 order by t1.c3 desc limit 2; select * from t1; drop table t1, t2; + +# +# MDEV-14551 Can't find record in table on multi-table update with ORDER BY +# + +# simple test with multi-update and Using temporary: +create table t1 (i int) engine=memory; +insert t1 values (1),(2); +create table t2 (f int) engine=myisam; +insert t2 values (1),(2); +explain update t1, t2 set f = 126 order by f limit 2; +update t1, t2 set f = 126 order by f limit 2; +select * from t2; +drop table t1, t2; + +# test with DuplicateElimination +# (so that keep_current_rowid is set for DuplicateElimination too) +create table t0(a int); +insert t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 (a int, b int, c int, key(a)); +insert t1 select a,a,a from t0; +create table t2 as select * from t1; +create table t3 as select * from t1; +select * from t1, t2 where t1.a=t2.a and t1.b in (select b from t3 where t3.c<=t2.c) order by t2.c, t1.c limit 5; +set optimizer_switch='firstmatch=off'; +explain update t1, t2 set t2.c=1 where t1.a=t2.a and t1.b in (select b from t3 where t3.c< t2.c) order by t2.c, t1.c limit 10; +update t1, t2 set t2.c=1 where t1.a=t2.a and t1.b in (select b from t3 where t3.c<=t2.c) order by t2.c, t1.c limit 5; +select * from t2; +set optimizer_switch=default; +drop table t0,t1,t2,t3; + +# test WITH CHECK OPTION +create table t0 (x int); +create table t1 (a int); +create table t2 (b int, c int default 0); +insert t0 (x) values (0),(10); +insert t1 (a) values (1), (2); +insert t2 (b) values (1), (2); +create view v1 as select t2.b,t2.c from t1, t2 + where t1.a=t2.b and t2.b < 3 with check option; +select * from t0 join v1 on (x=c); +explain update v1,t0 set c=1 where b=1 and x=c order by x,b limit 1; +update v1,t0 set c=1 where b<3 and x=c order by x,b limit 1; +select * from v1; +drop view v1; +drop table t0, t1,t2; diff --git a/sql/item.cc b/sql/item.cc index 56af69be427..f1695b67ca1 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -6846,6 +6846,11 @@ fast_field_copier Item_field::setup_fast_field_copier(Field *to) return to->get_fast_field_copier(field); } +void Item_field::save_in_result_field(bool no_conversions) +{ + bool unused; + save_field_in_field(field, &unused, result_field, no_conversions); +} /** Set a field's value from a item. diff --git a/sql/item.h b/sql/item.h index 9574bdc63bf..30e7eb1f1cb 100644 --- a/sql/item.h +++ b/sql/item.h @@ -2996,8 +2996,7 @@ class Item_field :public Item_ident, cond_equal_ref); } bool is_result_field() { return false; } - void set_result_field(Field *field_arg) {} - void save_in_result_field(bool no_conversions) { } + void save_in_result_field(bool no_conversions); Item *get_tmp_table_item(THD *thd); bool collect_item_field_processor(void * arg); bool add_field_to_set_processor(void * arg); diff --git a/sql/item_func.h b/sql/item_func.h index 4b4f94df329..8ed911c725c 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -70,7 +70,7 @@ class Item_func :public Item_func_or_sum SP_CONTAINS_FUNC,SP_OVERLAPS_FUNC, SP_STARTPOINT,SP_ENDPOINT,SP_EXTERIORRING, SP_POINTN,SP_GEOMETRYN,SP_INTERIORRINGN, SP_RELATE_FUNC, - NOT_FUNC, NOT_ALL_FUNC, + NOT_FUNC, NOT_ALL_FUNC, TEMPTABLE_ROWID, NOW_FUNC, NOW_UTC_FUNC, SYSDATE_FUNC, TRIG_COND_FUNC, SUSERVAR_FUNC, GUSERVAR_FUNC, COLLATE_FUNC, EXTRACT_FUNC, CHAR_TYPECAST_FUNC, FUNC_SP, UDF_FUNC, diff --git a/sql/item_strfunc.cc b/sql/item_strfunc.cc index 85e578f4595..12081a0cd9e 100644 --- a/sql/item_strfunc.cc +++ b/sql/item_strfunc.cc @@ -5204,3 +5204,23 @@ String *Item_func_dyncol_list::val_str(String *str) my_free(names); return NULL; } + +Item_temptable_rowid::Item_temptable_rowid(TABLE *table_arg) + : Item_str_func(table_arg->in_use), table(table_arg) +{ + max_length= table->file->ref_length; +} + +void Item_temptable_rowid::fix_length_and_dec() +{ + used_tables_cache= table->map; + const_item_cache= false; +} + +String *Item_temptable_rowid::val_str(String *str) +{ + if (!((null_value= table->null_row))) + table->file->position(table->record[0]); + str_value.set((char*)(table->file->ref), max_length, &my_charset_bin); + return &str_value; +} diff --git a/sql/item_strfunc.h b/sql/item_strfunc.h index cff9fdee072..c368d9336c4 100644 --- a/sql/item_strfunc.h +++ b/sql/item_strfunc.h @@ -1750,5 +1750,25 @@ class Item_func_dyncol_list: public Item_str_func { return get_item_copy<Item_func_dyncol_list>(thd, this); } }; -#endif /* ITEM_STRFUNC_INCLUDED */ +/* + this is used by JOIN_TAB::keep_current_rowid + and stores handler::position(). + It has nothing to do with _rowid pseudo-column, that the parser supports. +*/ +class Item_temptable_rowid :public Item_str_func +{ +public: + TABLE *table; + Item_temptable_rowid(TABLE *table_arg); + const Type_handler *type_handler() const { return &type_handler_string; } + Field *create_tmp_field(bool group, TABLE *table) + { return create_table_field_from_handler(table); } + String *val_str(String *str); + enum Functype functype() const { return TEMPTABLE_ROWID; } + const char *func_name() const { return "<rowid>"; } + void fix_length_and_dec(); + Item *get_copy(THD *thd) + { return get_item_copy<Item_temptable_rowid>(thd, this); } +}; +#endif /* ITEM_STRFUNC_INCLUDED */ diff --git a/sql/sql_class.h b/sql/sql_class.h index a7c33cbc504..ddde591793d 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -4907,7 +4907,7 @@ class select_result :public select_result_sink unit= u; return 0; } - virtual int prepare2(void) { return 0; } + virtual int prepare2(JOIN *join) { return 0; } /* Because of peculiarities of prepared statements protocol we need to know number of columns in the result set (if @@ -5151,7 +5151,7 @@ class select_insert :public select_result_interceptor { enum_duplicates duplic, bool ignore); ~select_insert(); int prepare(List<Item> &list, SELECT_LEX_UNIT *u); - virtual int prepare2(void); + virtual int prepare2(JOIN *join); virtual int send_data(List<Item> &items); virtual void store_values(List<Item> &values); virtual bool can_rollback_data() { return 0; } @@ -5203,7 +5203,7 @@ class select_create: public select_insert { // Needed for access from local class MY_HOOKS in prepare(), since thd is proteted. const THD *get_thd(void) { return thd; } const HA_CREATE_INFO *get_create_info() { return create_info; }; - int prepare2(void) { return 0; } + int prepare2(JOIN *join) { return 0; } private: TABLE *create_table_from_items(THD *thd, @@ -5887,6 +5887,7 @@ class multi_update :public select_result_interceptor int prepare(List<Item> &list, SELECT_LEX_UNIT *u); int send_data(List<Item> &items); bool initialize_tables (JOIN *join); + int prepare2(JOIN *join); int do_updates(); bool send_eof(); inline ha_rows num_found() const { return found; } diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index 392aa52825e..f1b438e8305 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -3781,7 +3781,7 @@ select_insert::prepare(List<Item> &values, SELECT_LEX_UNIT *u) 0 OK */ -int select_insert::prepare2(void) +int select_insert::prepare2(JOIN *) { DBUG_ENTER("select_insert::prepare2"); if (thd->lex->current_select->options & OPTION_BUFFER_RESULT && diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 796ea569e64..2511cff82a5 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -2650,6 +2650,25 @@ bool JOIN::add_having_as_table_cond(JOIN_TAB *tab) } +bool JOIN::add_fields_for_current_rowid(JOIN_TAB *cur, List<Item> *table_fields) +{ + /* + this will not walk into semi-join materialization nests but this is ok + because we will never need to save current rowids for those. + */ + for (JOIN_TAB *tab=join_tab; tab < cur; tab++) + { + if (!tab->keep_current_rowid) + continue; + Item *item= new (thd->mem_root) Item_temptable_rowid(tab->table); + item->fix_fields(thd, 0); + table_fields->push_back(item, thd->mem_root); + cur->tmp_table_param->func_count++; + } + return 0; +} + + /** Set info for aggregation tables @@ -3258,6 +3277,8 @@ JOIN::create_postjoin_aggr_table(JOIN_TAB *tab, List<Item> *table_fields, if (!(tab->tmp_table_param= new TMP_TABLE_PARAM(tmp_table_param))) DBUG_RETURN(true); + if (tmp_table_keep_current_rowid) + add_fields_for_current_rowid(tab, table_fields); tab->tmp_table_param->skip_create_table= true; TABLE* table= create_tmp_table(thd, tab->tmp_table_param, *table_fields, table_group, distinct, @@ -3652,7 +3673,7 @@ bool JOIN::prepare_result(List<Item> **columns_list) select_lex->handle_derived(thd->lex, DT_CREATE)) goto err; - if (result->prepare2()) + if (result->prepare2(this)) goto err; if ((select_lex->options & OPTION_SCHEMA_TABLE) && @@ -3789,7 +3810,7 @@ void JOIN::exec_inner() } columns_list= &procedure_fields_list; } - if (result->prepare2()) + if (result->prepare2(this)) DBUG_VOID_RETURN; if (!tables_list && (table_count || !select_lex->with_sum_func) && @@ -23365,13 +23386,10 @@ get_sort_by_table(ORDER *a,ORDER *b, List<TABLE_LIST> &tables, calc how big buffer we need for comparing group entries. */ -static void -calc_group_buffer(JOIN *join,ORDER *group) +void calc_group_buffer(TMP_TABLE_PARAM *param, ORDER *group) { uint key_length=0, parts=0, null_parts=0; - if (group) - join->group= 1; for (; group ; group=group->next) { Item *group_item= *group->item; @@ -23441,9 +23459,16 @@ calc_group_buffer(JOIN *join,ORDER *group) if (group_item->maybe_null) null_parts++; } - join->tmp_table_param.group_length=key_length+null_parts; - join->tmp_table_param.group_parts=parts; - join->tmp_table_param.group_null_parts=null_parts; + param->group_length= key_length + null_parts; + param->group_parts= parts; + param->group_null_parts= null_parts; +} + +static void calc_group_buffer(JOIN *join, ORDER *group) +{ + if (group) + join->group= 1; + calc_group_buffer(&join->tmp_table_param, group); } @@ -26184,7 +26209,7 @@ bool JOIN::change_result(select_result *new_result, select_result *old_result) { result= new_result; if (result->prepare(fields_list, select_lex->master_unit()) || - result->prepare2()) + result->prepare2(this)) DBUG_RETURN(true); /* purecov: inspected */ DBUG_RETURN(false); } diff --git a/sql/sql_select.h b/sql/sql_select.h index f8911fbba01..1da87bb9d50 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -1438,6 +1438,9 @@ class JOIN :public Sql_alloc enum { QEP_NOT_PRESENT_YET, QEP_AVAILABLE, QEP_DELETED} have_query_plan; + // if keep_current_rowid=true, whether they should be saved in temporary table + bool tmp_table_keep_current_rowid; + /* Additional WHERE and HAVING predicates to be considered for IN=>EXISTS subquery transformation of a JOIN object. @@ -1543,6 +1546,7 @@ class JOIN :public Sql_alloc pushdown_query= 0; original_join_tab= 0; explain= NULL; + tmp_table_keep_current_rowid= 0; all_fields= fields_arg; if (&fields_list != &fields_arg) /* Avoid valgrind-warning */ @@ -1767,6 +1771,7 @@ class JOIN :public Sql_alloc void cleanup_item_list(List<Item> &items) const; bool add_having_as_table_cond(JOIN_TAB *tab); bool make_aggr_tables_info(); + bool add_fields_for_current_rowid(JOIN_TAB *cur, List<Item> *fields); }; enum enum_with_bush_roots { WITH_BUSH_ROOTS, WITHOUT_BUSH_ROOTS}; @@ -2373,6 +2378,7 @@ int append_possible_keys(MEM_ROOT *alloc, String_list &list, TABLE *table, #define RATIO_TO_PACK_ROWS 2 #define MIN_STRING_LENGTH_TO_PACK_ROWS 10 +void calc_group_buffer(TMP_TABLE_PARAM *param, ORDER *group); TABLE *create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields, ORDER *group, bool distinct, bool save_sum_fields, ulonglong select_options, ha_rows rows_limit, diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 0149c2848c2..f9e4414f13a 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -507,14 +507,14 @@ void select_union_recursive::cleanup() bool select_union_direct::change_result(select_result *new_result) { result= new_result; - return (result->prepare(unit->types, unit) || result->prepare2()); + return (result->prepare(unit->types, unit) || result->prepare2(NULL)); } bool select_union_direct::postponed_prepare(List<Item> &types) { if (result != NULL) - return (result->prepare(types, unit) || result->prepare2()); + return (result->prepare(types, unit) || result->prepare2(NULL)); else return false; } diff --git a/sql/sql_update.cc b/sql/sql_update.cc index 38638d3aa1d..82fef617815 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -2164,22 +2164,12 @@ multi_update::initialize_tables(JOIN *join) tbl->prepare_for_position(); join->map2table[tbl->tablenr]->keep_current_rowid= true; - Field_string *field= new Field_string(tbl->file->ref_length, 0, - &field_name, - &my_charset_bin); - if (!field) - DBUG_RETURN(1); - field->init(tbl); - /* - The field will be converted to varstring when creating tmp table if - table to be updated was created by mysql 4.1. Deny this. - */ - field->can_alter_field_type= 0; - Item_field *ifield= new (thd->mem_root) Item_field(join->thd, (Field *) field); - if (!ifield) + Item_temptable_rowid *item= + new (thd->mem_root) Item_temptable_rowid(tbl); + if (!item) DBUG_RETURN(1); - ifield->maybe_null= 0; - if (temp_fields.push_back(ifield, thd->mem_root)) + item->fix_fields(thd, 0); + if (temp_fields.push_back(item, thd->mem_root)) DBUG_RETURN(1); } while ((tbl= tbl_it++)); @@ -2190,10 +2180,10 @@ multi_update::initialize_tables(JOIN *join) group.direction= ORDER::ORDER_ASC; group.item= (Item**) temp_fields.head_ref(); - tmp_param->quick_group=1; - tmp_param->field_count=temp_fields.elements; - tmp_param->group_parts=1; - tmp_param->group_length= table->file->ref_length; + tmp_param->quick_group= 1; + tmp_param->field_count= temp_fields.elements; + tmp_param->func_count= temp_fields.elements - 1; + calc_group_buffer(tmp_param, &group); /* small table, ignore SQL_BIG_TABLES */ my_bool save_big_tables= thd->variables.big_tables; thd->variables.big_tables= FALSE; @@ -2205,10 +2195,66 @@ multi_update::initialize_tables(JOIN *join) DBUG_RETURN(1); tmp_tables[cnt]->file->extra(HA_EXTRA_WRITE_CACHE); } + join->tmp_table_keep_current_rowid= TRUE; DBUG_RETURN(0); } +static TABLE *item_rowid_table(Item *item) +{ + if (item->type() != Item::FUNC_ITEM) + return NULL; + Item_func *func= (Item_func *)item; + if (func->functype() != Item_func::TEMPTABLE_ROWID) + return NULL; + Item_temptable_rowid *itr= (Item_temptable_rowid *)func; + return itr->table; +} + + +/* + multi_update stores a rowid and new field values for every updated row in a + temporary table (one temporary table per updated table). These rowids are + obtained via Item_temptable_rowid's by calling handler::position(). But if + the join is resolved via a temp table, rowids cannot be obtained from + handler::position() in the multi_update::send_data(). So, they're stored in + the join's temp table (JOIN::add_fields_for_current_rowid()) and here we + replace Item_temptable_rowid's (that would've done handler::position()) with + Item_field's (that will simply take the corresponding field value from the + temp table). +*/ +int multi_update::prepare2(JOIN *join) +{ + if (!join->need_tmp || !join->tmp_table_keep_current_rowid) + return 0; + + // there cannot be many tmp tables in multi-update + JOIN_TAB *tmptab= join->join_tab + join->exec_join_tab_cnt(); + + for (Item **it= tmptab->tmp_table_param->items_to_copy; *it ; it++) + { + TABLE *tbl= item_rowid_table(*it); + if (!tbl) + continue; + for (uint i= 0; i < table_count; i++) + { + for (Item **it2= tmp_table_param[i].items_to_copy; *it2; it2++) + { + if (item_rowid_table(*it2) != tbl) + continue; + Item *fld= new (thd->mem_root) + Item_field(thd, (*it)->get_tmp_table_field()); + if (!fld) + return 1; + fld->set_result_field((*it2)->get_tmp_table_field()); + *it2= fld; + } + } + } + return 0; +} + + multi_update::~multi_update() { TABLE_LIST *table; @@ -2380,29 +2426,11 @@ int multi_update::send_data(List<Item> ¬_used_values) { int error; TABLE *tmp_table= tmp_tables[offset]; - /* - For updatable VIEW store rowid of the updated table and - rowids of tables used in the CHECK OPTION condition. - */ - uint field_num= 0; - List_iterator_fast<TABLE> tbl_it(unupdated_check_opt_tables); - /* Set first tbl = table and then tbl to tables from tbl_it */ - TABLE *tbl= table; - do - { - tbl->file->position(tbl->record[0]); - memcpy((char*) tmp_table->field[field_num]->ptr, - (char*) tbl->file->ref, tbl->file->ref_length); - /* - For outer joins a rowid field may have no NOT_NULL_FLAG, - so we have to reset NULL bit for this field. - (set_notnull() resets NULL bit only if available). - */ - tmp_table->field[field_num]->set_notnull(); - field_num++; - } while ((tbl= tbl_it++)); - + if (copy_funcs(tmp_table_param[offset].items_to_copy, thd)) + DBUG_RETURN(1); /* Store regular updated fields in the row. */ + DBUG_ASSERT(1 + unupdated_check_opt_tables.elements == + tmp_table_param[offset].func_count); fill_record(thd, tmp_table, tmp_table->field + 1 + unupdated_check_opt_tables.elements, *values_for_table[offset], TRUE, FALSE);