revision-id: a74e3ef17e7a8693d68001290a8d91b5b206804d (mariadb-10.3.5-138-ga74e3ef17e7) parent(s): 9bd3af97dffa411657879bbdfd4dfef38c99f7cc author: Sergei Golubchik committer: Sergei Golubchik timestamp: 2018-04-10 14:09:17 +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 | 10 +++++ mysql-test/main/multi_update.test | 11 +++++ sql/item.h | 1 + sql/item_strfunc.cc | 20 +++++++++ sql/item_strfunc.h | 17 +++++++- sql/sql_class.h | 7 +-- sql/sql_insert.cc | 2 +- sql/sql_list.h | 11 +++-- sql/sql_select.cc | 41 +++++++++++++----- sql/sql_select.h | 6 +++ sql/sql_union.cc | 4 +- sql/sql_update.cc | 85 +++++++++++++++++++------------------ 12 files changed, 154 insertions(+), 61 deletions(-) diff --git a/mysql-test/main/multi_update.result b/mysql-test/main/multi_update.result index 45239f6e090..992936d0fdd 100644 --- a/mysql-test/main/multi_update.result +++ b/mysql-test/main/multi_update.result @@ -968,3 +968,13 @@ 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); +UPDATE t1, t2 SET f = 126 ORDER BY f LIMIT 2; +SELECT * FROM t2; +f +126 +2 +DROP TABLE t1, t2; diff --git a/mysql-test/main/multi_update.test b/mysql-test/main/multi_update.test index 5feebe87a5a..96e78dab82b 100644 --- a/mysql-test/main/multi_update.test +++ b/mysql-test/main/multi_update.test @@ -914,3 +914,14 @@ 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 +# +CREATE TABLE t1 (i INT) ENGINE=MEMORY; +INSERT t1 VALUES (1),(2); +CREATE TABLE t2 (f INT) ENGINE=MyISAM; +INSERT t2 VALUES (1),(2); +UPDATE t1, t2 SET f = 126 ORDER BY f LIMIT 2; +SELECT * FROM t2; +DROP TABLE t1, t2; diff --git a/sql/item.h b/sql/item.h index 9574bdc63bf..e391e7810c4 100644 --- a/sql/item.h +++ b/sql/item.h @@ -2023,6 +2023,7 @@ class Item: public Value_source, { marker &= ~EXTRACTION_MASK; } + virtual TABLE *rowid_table() const { return 0; } }; MEM_ROOT *get_thd_memroot(THD *thd); diff --git a/sql/item_strfunc.cc b/sql/item_strfunc.cc index c6b0ae7fba8..9097ffca867 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 18cda491efd..49de5568696 100644 --- a/sql/item_strfunc.h +++ b/sql/item_strfunc.h @@ -1748,5 +1748,20 @@ class Item_func_dyncol_list: public Item_str_func { return get_item_copy<Item_func_dyncol_list>(thd, this); } }; -#endif /* ITEM_STRFUNC_INCLUDED */ +class Item_temptable_rowid :public Item_str_func +{ + TABLE *table; +public: + 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); + 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); } + TABLE *rowid_table() const { return table; } +}; +#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_list.h b/sql/sql_list.h index 311e601490b..4b36ffa24b5 100644 --- a/sql/sql_list.h +++ b/sql/sql_list.h @@ -138,6 +138,13 @@ class base_list :public Sql_alloc first == rhs.first && last == rhs.last; } + base_list& operator=(const base_list &rhs) + { + elements= rhs.elements; + first= rhs.first; + last= elements ? rhs.last : &first; + return *this; + } inline void empty() { elements=0; first= &end_of_list; last=&first;} inline base_list() { empty(); } @@ -152,9 +159,7 @@ class base_list :public Sql_alloc */ inline base_list(const base_list &tmp) :Sql_alloc() { - elements= tmp.elements; - first= tmp.first; - last= elements ? tmp.last : &first; + *this= tmp; } /** Construct a deep copy of the argument in memory root mem_root. diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 796ea569e64..22301319680 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -2650,6 +2650,21 @@ bool JOIN::add_having_as_table_cond(JOIN_TAB *tab) } +bool JOIN::add_fields_for_current_rowid(JOIN_TAB *cur, List<Item> *table_fields) +{ + 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 +3273,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 +3669,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 +3806,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 +23382,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 +23455,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 +26205,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..0e29f817e56 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(0)); } 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(0)); else return false; } diff --git a/sql/sql_update.cc b/sql/sql_update.cc index 38638d3aa1d..4ae7e33b4e3 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,41 @@ 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); } + +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= (*it)->rowid_table(); + if (!tbl) + continue; + for (uint i= 0; i < table_count; i++) + { + for (Item **it2= tmp_table_param[i].items_to_copy; *it2; it2++) + { + if ((*it2)->rowid_table() != tbl) + continue; + *it2= new (thd->mem_root) Item_field(thd, (*it)->get_tmp_table_field()); + if (!*it2) + return 1; + } + } + } + return 0; +} + + multi_update::~multi_update() { TABLE_LIST *table; @@ -2380,29 +2401,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);