From 7916d9e965fb8ddf62fde4ed1db849d3b49c37cc Mon Sep 17 00:00:00 2001 From: "evgen@moonbone.local" <> Date: Fri, 16 Feb 2007 19:39:28 +0300 Subject: Bug#16630: The update fields of the INSERT .. SELECT .. ON DUPLICATE KEY UPDATE contains wrong data if the SELECT employs a temporary table. If the UPDATE values of the INSERT .. SELECT .. ON DUPLICATE KEY UPDATE statement contains fields from the SELECT part and the select employs a temporary table then those fields will contain wrong values because they aren't corrected to get data from the temporary table. The solution is to add these fields to the selects all_fields list, to store pointers to those fields in the selects ref_pointer_array and to access them via Item_ref objects. The substitution for Item_ref objects is done in the new function called Item_field::update_value_transformer(). It is called through the item->transform() mechanism at the end of the select_insert::prepare() function. --- sql/item.cc | 45 +++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 45 insertions(+) (limited to 'sql/item.cc') diff --git a/sql/item.cc b/sql/item.cc index 95001809e9a..23193e0990c 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -4809,6 +4809,51 @@ void Item_field::update_null_value() } +/* + Add the field to the select list and substitute it for the reference to + the field. + + SYNOPSIS + Item_field::update_value_transformer() + select_arg current select + + DESCRIPTION + If the field doesn't belong to the table being inserted into then it is + added to the select list, pointer to it is stored in the ref_pointer_array + of the select and the field itself is substituted for the Item_ref object. + This is done in order to get correct values from update fields that + belongs to the SELECT part in the INSERT .. SELECT .. ON DUPLICATE KEY + UPDATE statement. + + RETURN + 0 if error occured + ref if all conditions are met + this field otherwise +*/ + +Item *Item_field::update_value_transformer(byte *select_arg) +{ + SELECT_LEX *select= (SELECT_LEX*)select_arg; + DBUG_ASSERT(fixed); + + if (field->table != select->context.table_list->table && + type() != Item::TRIGGER_FIELD_ITEM) + { + List *all_fields= &select->join->all_fields; + Item **ref_pointer_array= select->ref_pointer_array; + int el= all_fields->elements; + Item_ref *ref; + + ref_pointer_array[el]= (Item*)this; + all_fields->push_front((Item*)this); + ref= new Item_ref(&select->context, ref_pointer_array + el, + table_name, field_name); + return ref; + } + return this; +} + + Item_ref::Item_ref(Name_resolution_context *context_arg, Item **item, const char *table_name_arg, const char *field_name_arg) -- cgit v1.2.1 From 9a233742b871924059c81e988b28a87f2ff42f67 Mon Sep 17 00:00:00 2001 From: "evgen@moonbone.local" <> Date: Wed, 21 Feb 2007 23:00:32 +0300 Subject: Bug#23800: Outer fields in correlated subqueries is used in a temporary table created for sorting. Any outer reference in a subquery was represented by an Item_field object. If the outer select employs a temporary table all such fields should be replaced with fields from that temporary table in order to point to the actual data. This replacement wasn't done and that resulted in a wrong subquery evaluation and a wrong result of the whole query. Now any outer field is represented by two objects - Item_field placed in the outer select and Item_outer_ref in the subquery. Item_field object is processed as a normal field and the reference to it is saved in the ref_pointer_array. Thus the Item_outer_ref is always references the correct field. The original field is substituted for a reference in the Item_field::fix_outer_field() function. New function called fix_inner_refs() is added to fix fields referenced from inner selects and to fix references (Item_ref objects) to these fields. The new Item_outer_ref class is a descendant of the Item_direct_ref class. It additionally stores a reference to the original field and designed to behave more like a field. --- sql/item.cc | 80 ++++++++++++++++++++++++++++++++++++++++++++++++++++++------- 1 file changed, 71 insertions(+), 9 deletions(-) (limited to 'sql/item.cc') diff --git a/sql/item.cc b/sql/item.cc index 95001809e9a..54aaf573525 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -1609,7 +1609,7 @@ void Item_ident_for_show::make_field(Send_field *tmp_field) Item_field::Item_field(Field *f) :Item_ident(0, NullS, *f->table_name, f->field_name), item_equal(0), no_const_subst(0), - have_privileges(0), any_privileges(0) + have_privileges(0), any_privileges(0), fixed_as_field(0) { set_field(f); /* @@ -1623,7 +1623,7 @@ Item_field::Item_field(THD *thd, Name_resolution_context *context_arg, Field *f) :Item_ident(context_arg, f->table->s->db, *f->table_name, f->field_name), item_equal(0), no_const_subst(0), - have_privileges(0), any_privileges(0) + have_privileges(0), any_privileges(0), fixed_as_field(0) { /* We always need to provide Item_field with a fully qualified field @@ -1662,7 +1662,7 @@ Item_field::Item_field(Name_resolution_context *context_arg, const char *field_name_arg) :Item_ident(context_arg, db_arg,table_name_arg,field_name_arg), field(0), result_field(0), item_equal(0), no_const_subst(0), - have_privileges(0), any_privileges(0) + have_privileges(0), any_privileges(0), fixed_as_field(0) { collation.set(DERIVATION_IMPLICIT); } @@ -1675,7 +1675,8 @@ Item_field::Item_field(THD *thd, Item_field *item) item_equal(item->item_equal), no_const_subst(item->no_const_subst), have_privileges(item->have_privileges), - any_privileges(item->any_privileges) + any_privileges(item->any_privileges), + fixed_as_field(item->fixed_as_field) { collation.set(DERIVATION_IMPLICIT); } @@ -3484,8 +3485,46 @@ Item_field::fix_outer_field(THD *thd, Field **from_field, Item **reference) } if (*from_field != view_ref_found) { + prev_subselect_item->used_tables_cache|= (*from_field)->table->map; prev_subselect_item->const_item_cache= 0; + if (!last_checked_context->select_lex->having_fix_field && + !fixed_as_field) + { + Item_outer_ref *rf; + Query_arena *arena= 0, backup; + /* + Each outer field is replaced for an Item_outer_ref object. + This is done in order to get correct results when the outer + select employs a temporary table. + The original fields are saved in the inner_fields_list of the + outer select. This list is created by the following reasons: + 1. We can't add field items to the outer select list directly + because the outer select hasn't been fully fixed yet. + 2. We need a location to refer to in the Item_ref object + so the inner_fields_list is used as such temporary + reference storage. + The new Item_outer_ref object replaces the original field and is + also saved in the inner_refs_list of the outer select. Here + it is only created. It can be fixed only after the original + field has been fixed and this is done in the fix_inner_refs() + function. + */ + set_field(*from_field); + arena= thd->activate_stmt_arena_if_needed(&backup); + rf= new Item_outer_ref(context, this); + if (!rf) + { + if (arena) + thd->restore_active_arena(arena, &backup); + return -1; + } + *reference= rf; + select->inner_refs_list.push_back(rf); + if (arena) + thd->restore_active_arena(arena, &backup); + fixed_as_field= 1; + } if (thd->lex->in_sum_func && thd->lex->in_sum_func->nest_level == thd->lex->current_select->nest_level) @@ -3612,7 +3651,7 @@ Item_field::fix_outer_field(THD *thd, Field **from_field, Item **reference) { mark_as_dependent(thd, last_checked_context->select_lex, context->select_lex, - this, this); + this, (Item_ident*)*reference); if (last_checked_context->select_lex->having_fix_field) { Item_ref *rf; @@ -4818,8 +4857,7 @@ Item_ref::Item_ref(Name_resolution_context *context_arg, /* This constructor used to create some internals references over fixed items */ - DBUG_ASSERT(ref != 0); - if (*ref && (*ref)->fixed) + if (ref && *ref && (*ref)->fixed) set_properties(); } @@ -5119,7 +5157,7 @@ void Item_ref::print(String *str) if (ref) { if ((*ref)->type() != Item::CACHE_ITEM && ref_type() != VIEW_REF && - name && alias_name_used) + ref_type() != OUTER_REF && name && alias_name_used) { THD *thd= current_thd; append_identifier(thd, str, name, (uint) strlen(name)); @@ -5367,7 +5405,7 @@ bool Item_direct_ref::get_date(TIME *ltime,uint fuzzydate) /* - Prepare referenced view viewld then call usual Item_direct_ref::fix_fields + Prepare referenced field then call usual Item_direct_ref::fix_fields SYNOPSIS Item_direct_view_ref::fix_fields() @@ -5390,6 +5428,30 @@ bool Item_direct_view_ref::fix_fields(THD *thd, Item **reference) return Item_direct_ref::fix_fields(thd, reference); } +/* + Prepare referenced outer field then call usual Item_direct_ref::fix_fields + + SYNOPSIS + Item_outer_ref::fix_fields() + thd thread handler + reference reference on reference where this item stored + + RETURN + FALSE OK + TRUE Error +*/ + +bool Item_outer_ref::fix_fields(THD *thd, Item **reference) +{ + DBUG_ASSERT(*ref); + /* outer_field->check_cols() will be made in Item_direct_ref::fix_fields */ + outer_field->fixed_as_field= 1; + if (!outer_field->fixed && + (outer_field->fix_fields(thd, reference))) + return TRUE; + return Item_direct_ref::fix_fields(thd, reference); +} + /* Compare two view column references for equality. -- cgit v1.2.1 From 47ffb61fb0ae8cd1cb04938830f6f95ef60c0837 Mon Sep 17 00:00:00 2001 From: "evgen@moonbone.local" <> Date: Sat, 24 Feb 2007 23:04:15 +0300 Subject: item.cc: Post fix for bug#23800. The Item_field constructor now increases the select_n_where_fields counter. sql_yacc.yy: Post fix for bug#23800. Take into account fields that might be added by subselects. sql_lex.h: Post fix for bug#23800. Added the select_n_where_fields variable to the st_select_lex class. sql_lex.cc: Post fix for bug#23800. Initialization of the select_n_where_fields variable. --- sql/item.cc | 3 +++ 1 file changed, 3 insertions(+) (limited to 'sql/item.cc') diff --git a/sql/item.cc b/sql/item.cc index a57f3c0fa96..9149a1240ea 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -1664,7 +1664,10 @@ Item_field::Item_field(Name_resolution_context *context_arg, field(0), result_field(0), item_equal(0), no_const_subst(0), have_privileges(0), any_privileges(0), fixed_as_field(0) { + SELECT_LEX *select= current_thd->lex->current_select; collation.set(DERIVATION_IMPLICIT); + if (select && select->parsing_place != IN_HAVING) + select->select_n_where_fields++; } // Constructor need to process subselect with temporary tables (see Item) -- cgit v1.2.1 From d9831e91a19c46aa5e373b682e26ca23bc048c40 Mon Sep 17 00:00:00 2001 From: "evgen@sunlight.local" <> Date: Mon, 26 Feb 2007 09:01:43 +0300 Subject: item.cc, item.h: Post fix for bug#23800. --- sql/item.cc | 1 + 1 file changed, 1 insertion(+) (limited to 'sql/item.cc') diff --git a/sql/item.cc b/sql/item.cc index 9149a1240ea..cc41b83f8d9 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -5497,6 +5497,7 @@ bool Item_outer_ref::fix_fields(THD *thd, Item **reference) if (!outer_field->fixed && (outer_field->fix_fields(thd, reference))) return TRUE; + table_name= outer_field->table_name; return Item_direct_ref::fix_fields(thd, reference); } -- cgit v1.2.1