| List: | Commits | « Previous MessageNext Message » | |
| From: | igor | Date: | March 5 2007 4:54am |
| Subject: | bk commit into 5.0 tree (igor:1.2431) BUG#26560 | ||
| View as plain text | |||
Below is the list of changes that have just been committed into a local 5.0 repository of igor. When igor does a push these changes will be propagated to the main repository and, within 24 hours after the push, to the public repository. For information on how to access the public repository see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html ChangeSet@stripped, 2007-03-04 19:54:35-08:00, igor@stripped +7 -0 Fixed bug #26560. The flag alias_name_used was not set on for the outer references in subqueries. It resulted in replacement of any outer reference resolved against an alias for a full field name when the frm representation of a view with a subquery was generated. If the subquery and the outer query referenced the same table in their from lists this replacement effectively changed the meaning of the view and led to wrong results for selects from this view. Modified several functions to ensure setting the right value of the alias_name_used flag for outer references resolved against aliases. mysql-test/r/view.result@stripped, 2007-03-04 19:54:30-08:00, igor@stripped +37 -0 Added a test case for bug #26560. mysql-test/t/view.test@stripped, 2007-03-04 19:54:30-08:00, igor@stripped +40 -0 Added a test case for bug #26560. sql/item.cc@stripped, 2007-03-04 19:54:30-08:00, igor@stripped +17 -9 Fixed bug #26560. Made the function resolve_ref_in_select_and_group analyze the return value of the last parameter with the type of the name resolution for the submitted reference. If the reference has been resolved against an alias name from select list then its flag alias_name_used is set on. Now this value is used in Item_field::fix_outer_field to initialize the flag when the item_ref object is created for an outer reference. Added a parameter for the second Item_ref::Item_ref constructor to initialize properly the flag alias_name_used. The default value of the parameter is FALSE. If this flag is set on at the creation of an object by this constructor it will never be changed. Corrected appropriately the Item_ref::set_properties function. The function Item_ref::print now prints alias name for an outer reference if the flag alias_name_used is set on. sql/item.h@stripped, 2007-03-04 19:54:30-08:00, igor@stripped +7 -3 Fixed bug #26560. Added a parameter for the second Item_ref::Item_ref constructor to initialize properly the flag alias_name_used. The default value of the parameter is FALSE. A similar change has been applied to the first Item_direct_ref::Item_direct_ref constructor. sql/mysql_priv.h@stripped, 2007-03-04 19:54:30-08:00, igor@stripped +21 -1 Fixed bug #26560. Added an an enumeration type enum_resolution_type to return info on how the function find_item_in_list has resolved the submitted item. The type is used only for this function. sql/sql_base.cc@stripped, 2007-03-04 19:54:30-08:00, igor@stripped +69 -52 Fixed bug #26560. Made the last parameter of the function find_field_in_tables return more detailed information on how the submitted item has been resolved. Now it says whether the item has been resolved against an alias name, or as a field name without alias, or as a field name hidden by alias, or was resolved ignoring alias. sql/sql_select.cc@stripped, 2007-03-04 19:54:30-08:00, igor@stripped +4 -4 Fixed bug #26560. Took into account the new type of the last parameter of the function find_item_in_list. # This is a BitKeeper patch. What follows are the unified diffs for the # set of deltas contained in the patch. The rest of the patch, the part # that BitKeeper cares about, is below these diffs. # User: igor # Host: olga.mysql.com # Root: /home/igor/dev-opt/mysql-5.0-opt-bug26560 --- 1.251/sql/item.cc 2007-03-04 19:54:41 -08:00 +++ 1.252/sql/item.cc 2007-03-04 19:54:41 -08:00 @@ -3320,7 +3320,7 @@ ORDER *group_list= (ORDER*) select->group_list.first; bool ambiguous_fields= FALSE; uint counter; - bool not_used; + enum_resolution_type resolution; /* Search for a column or derived column named as 'ref' in the SELECT @@ -3328,8 +3328,10 @@ */ if (!(select_ref= find_item_in_list(ref, *(select->get_item_list()), &counter, REPORT_EXCEPT_NOT_FOUND, - ¬_used))) + &resolution))) return NULL; /* Some error occurred. */ + if (resolution == RESOLVED_AGAINST_ALIAS) + ref->alias_name_used= TRUE; /* If this is a non-aggregated field inside HAVING, search in GROUP BY. */ if (select->having_fix_field && !ref->with_sum_func && group_list) @@ -3630,9 +3632,9 @@ *ref= NULL; // Don't call set_properties() rf= (place == IN_HAVING ? new Item_ref(context, ref, (char*) table_name, - (char*) field_name) : + (char*) field_name, alias_name_used) : new Item_direct_ref(context, ref, (char*) table_name, - (char*) field_name)); + (char*) field_name, alias_name_used)); *ref= save; if (!rf) return -1; @@ -3750,12 +3752,14 @@ if (thd->lex->current_select->is_item_list_lookup) { uint counter; - bool not_used; + enum_resolution_type resolution; Item** res= find_item_in_list(this, thd->lex->current_select->item_list, &counter, REPORT_EXCEPT_NOT_FOUND, - ¬_used); + &resolution); if (!res) return 1; + if (resolution == RESOLVED_AGAINST_ALIAS) + alias_name_used= TRUE; if (res != (Item **)not_found_item) { if ((*res)->type() == Item::FIELD_ITEM) @@ -4898,10 +4902,12 @@ Item_ref::Item_ref(Name_resolution_context *context_arg, Item **item, const char *table_name_arg, - const char *field_name_arg) + const char *field_name_arg, + bool alias_name_used_arg) :Item_ident(context_arg, NullS, table_name_arg, field_name_arg), result_field(0), ref(item) { + alias_name_used= alias_name_used_arg; /* This constructor used to create some internals references over fixed items */ @@ -5184,11 +5190,13 @@ */ with_sum_func= (*ref)->with_sum_func; unsigned_flag= (*ref)->unsigned_flag; + fixed= 1; + if (alias_name_used) + return; if ((*ref)->type() == FIELD_ITEM) alias_name_used= ((Item_ident *) (*ref))->alias_name_used; else alias_name_used= TRUE; // it is not field, so it is was resolved by alias - fixed= 1; } @@ -5206,7 +5214,7 @@ if (ref) { if ((*ref)->type() != Item::CACHE_ITEM && ref_type() != VIEW_REF && - ref_type() != OUTER_REF && name && alias_name_used) + !table_name && name && alias_name_used) { THD *thd= current_thd; append_identifier(thd, str, name, (uint) strlen(name)); --- 1.219/sql/item.h 2007-03-04 19:54:41 -08:00 +++ 1.220/sql/item.h 2007-03-04 19:54:41 -08:00 @@ -1851,7 +1851,8 @@ with Bar, and if we have a more broader set of problems like this. */ Item_ref(Name_resolution_context *context_arg, Item **item, - const char *table_name_arg, const char *field_name_arg); + const char *table_name_arg, const char *field_name_arg, + bool alias_name_used_arg= FALSE); /* Constructor need to process subselect with temporary tables (see Item) */ Item_ref(THD *thd, Item_ref *item) @@ -1926,8 +1927,11 @@ public: Item_direct_ref(Name_resolution_context *context_arg, Item **item, const char *table_name_arg, - const char *field_name_arg) - :Item_ref(context_arg, item, table_name_arg, field_name_arg) {} + const char *field_name_arg, + bool alias_name_used_arg= FALSE) + :Item_ref(context_arg, item, table_name_arg, + field_name_arg, alias_name_used_arg) + {} /* Constructor need to process subselect with temporary tables (see Item) */ Item_direct_ref(THD *thd, Item_direct_ref *item) : Item_ref(thd, item) {} --- 1.434/sql/mysql_priv.h 2007-03-04 19:54:41 -08:00 +++ 1.435/sql/mysql_priv.h 2007-03-04 19:54:41 -08:00 @@ -1008,9 +1008,29 @@ table_map read_tables, COND *conds, bool allow_null_cond, int *error); extern Item **not_found_item; + +/* + This enumeration type is used only by the function find_item_in_list + to return the info on how an item has been resolved against a list + of possibly aliased items. + The item can be resolved: + - against an alias name of the list's element (RESOLVED_AGAINST_ALIAS) + - against non-aliased field name of the list (RESOLVED_WITH_NO_ALIAS) + - against an aliased field name of the list (RESOLVED_BEHIND_ALIAS) + - ignoring the alias name in cases when SQL requires to ignore aliases + (e.g. when the resolved field reference contains a table name or + when the resolved item is an expression) (RESOLVED_IGNORING_ALIAS) +*/ +enum enum_resolution_type { + NOT_RESOLVED=0, + RESOLVED_IGNORING_ALIAS, + RESOLVED_BEHIND_ALIAS, + RESOLVED_WITH_NO_ALIAS, + RESOLVED_AGAINST_ALIAS +}; Item ** find_item_in_list(Item *item, List<Item> &items, uint *counter, find_item_error_report_type report_error, - bool *unaliased); + enum_resolution_type *resolution); bool get_key_map_from_key_list(key_map *map, TABLE *table, List<String> *index_list); bool insert_fields(THD *thd, Name_resolution_context *context, --- 1.369/sql/sql_base.cc 2007-03-04 19:54:41 -08:00 +++ 1.370/sql/sql_base.cc 2007-03-04 19:54:41 -08:00 @@ -3468,10 +3468,13 @@ return not_found_item, report other errors, return 0 IGNORE_ERRORS Do not report errors, return 0 if error - unaliased Set to true if item is field which was found - by original field name and not by its alias - in item list. Set to false otherwise. - + resolution Set to the resolution type if the item is found + (it says whether the item is resolved + against an alias name, + or as a field name without alias, + or as a field hidden by alias, + or ignoring alias) + RETURN VALUES 0 Item is not found or item is not unique, error message is reported @@ -3487,7 +3490,8 @@ Item ** find_item_in_list(Item *find, List<Item> &items, uint *counter, - find_item_error_report_type report_error, bool *unaliased) + find_item_error_report_type report_error, + enum_resolution_type *resolution) { List_iterator<Item> li(items); Item **found=0, **found_unaliased= 0, *item; @@ -3501,10 +3505,9 @@ */ bool is_ref_by_name= 0; uint unaliased_counter; - LINT_INIT(unaliased_counter); // Dependent on found_unaliased - *unaliased= FALSE; + *resolution= NOT_RESOLVED; is_ref_by_name= (find->type() == Item::FIELD_ITEM || find->type() == Item::REF_ITEM); @@ -3571,63 +3574,77 @@ } found_unaliased= li.ref(); unaliased_counter= i; + *resolution= RESOLVED_IGNORING_ALIAS; if (db_name) break; // Perfect match } } - else if (!my_strcasecmp(system_charset_info, item_field->name, - field_name)) - { - /* - If table name was not given we should scan through aliases - (or non-aliased fields) first. We are also checking unaliased - name of the field in then next else-if, to be able to find - instantly field (hidden by alias) if no suitable alias (or - non-aliased field) was found. - */ - if (found) - { - if ((*found)->eq(item, 0)) - continue; // Same field twice - if (report_error != IGNORE_ERRORS) - my_error(ER_NON_UNIQ_ERROR, MYF(0), - find->full_name(), current_thd->where); - return (Item**) 0; - } - found= li.ref(); - *counter= i; - } - else if (!my_strcasecmp(system_charset_info, item_field->field_name, - field_name)) + else { - /* - We will use un-aliased field or react on such ambiguities only if - we won't be able to find aliased field. - Again if we have ambiguity with field outside of select list - we should prefer fields from select list. - */ - if (found_unaliased) + int fname_cmp= my_strcasecmp(system_charset_info, + item_field->field_name, + field_name); + if (!my_strcasecmp(system_charset_info, + item_field->name,field_name)) { - if ((*found_unaliased)->eq(item, 0)) - continue; // Same field twice - found_unaliased_non_uniq= 1; + /* + If table name was not given we should scan through aliases + and non-aliased fields first. We are also checking unaliased + name of the field in then next else-if, to be able to find + instantly field (hidden by alias) if no suitable alias or + non-aliased field was found. + */ + if (found) + { + if ((*found)->eq(item, 0)) + continue; // Same field twice + if (report_error != IGNORE_ERRORS) + my_error(ER_NON_UNIQ_ERROR, MYF(0), + find->full_name(), current_thd->where); + return (Item**) 0; + } + found= li.ref(); + *counter= i; + *resolution= fname_cmp ? RESOLVED_AGAINST_ALIAS: + RESOLVED_WITH_NO_ALIAS; } - else + else if (!fname_cmp) { + /* + We will use non-aliased field or react on such ambiguities only if + we won't be able to find aliased field. + Again if we have ambiguity with field outside of select list + we should prefer fields from select list. + */ + if (found_unaliased) + { + if ((*found_unaliased)->eq(item, 0)) + continue; // Same field twice + found_unaliased_non_uniq= 1; + } found_unaliased= li.ref(); unaliased_counter= i; } } } - else if (!table_name && (find->eq(item,0) || - is_ref_by_name && find->name && item->name && - !my_strcasecmp(system_charset_info, - item->name,find->name))) - { - found= li.ref(); - *counter= i; - break; - } + else if (!table_name) + { + if (is_ref_by_name && find->name && item->name && + !my_strcasecmp(system_charset_info,item->name,find->name)) + { + found= li.ref(); + *counter= i; + *resolution= RESOLVED_AGAINST_ALIAS; + break; + } + else if (find->eq(item,0)) + { + found= li.ref(); + *counter= i; + *resolution= RESOLVED_IGNORING_ALIAS; + break; + } + } } if (!found) { @@ -3642,7 +3659,7 @@ { found= found_unaliased; *counter= unaliased_counter; - *unaliased= TRUE; + *resolution= RESOLVED_BEHIND_ALIAS; } } if (found) --- 1.495/sql/sql_select.cc 2007-03-04 19:54:41 -08:00 +++ 1.496/sql/sql_select.cc 2007-03-04 19:54:41 -08:00 @@ -13158,7 +13158,7 @@ Item **select_item; /* The corresponding item from the SELECT clause. */ Field *from_field; /* The corresponding field from the FROM clause. */ uint counter; - bool unaliased; + enum_resolution_type resolution; /* Local SP variables may be int but are expressions, not positions. @@ -13181,7 +13181,7 @@ } /* Lookup the current GROUP/ORDER field in the SELECT clause. */ select_item= find_item_in_list(order_item, fields, &counter, - REPORT_EXCEPT_NOT_FOUND, &unaliased); + REPORT_EXCEPT_NOT_FOUND, &resolution); if (!select_item) return TRUE; /* The item is not unique, or some other error occured. */ @@ -13195,7 +13195,7 @@ original field name, we should additionaly check if we have conflict for this name (in case if we would perform lookup in all tables). */ - if (unaliased && !order_item->fixed && + if (resolution == RESOLVED_BEHIND_ALIAS && !order_item->fixed && order_item->fix_fields(thd, order->item)) return TRUE; @@ -13429,7 +13429,7 @@ thd->set_query_id=1; // Not really needed, but... uint counter; - bool not_used; + enum_resolution_type not_used; for (; new_field ; new_field= new_field->next) { if ((item= find_item_in_list(*new_field->item, fields, &counter, --- 1.191/mysql-test/r/view.result 2007-03-04 19:54:41 -08:00 +++ 1.192/mysql-test/r/view.result 2007-03-04 19:54:41 -08:00 @@ -3135,4 +3135,41 @@ 100 2 DROP VIEW v1; DROP TABLE t1; +CREATE TABLE t1 ( +lid int NOT NULL PRIMARY KEY, +name char(10) NOT NULL +); +INSERT INTO t1 (lid, name) VALUES +(1, 'YES'), (2, 'NO'); +CREATE TABLE t2 ( +id int NOT NULL PRIMARY KEY, +gid int NOT NULL, +lid int NOT NULL, +dt date +); +INSERT INTO t2 (id, gid, lid, dt) VALUES +(1, 1, 1, '2007-01-01'),(2, 1, 2, '2007-01-02'), +(3, 2, 2, '2007-02-01'),(4, 2, 1, '2007-02-02'); +SELECT DISTINCT t2.gid AS lgid, +(SELECT t1.name FROM t1, t2 +WHERE t1.lid = t2.lid AND t2.gid = lgid +ORDER BY t2.dt DESC LIMIT 1 +) as clid +FROM t2; +lgid clid +1 NO +2 YES +CREATE VIEW v1 AS +SELECT DISTINCT t2.gid AS lgid, +(SELECT t1.name FROM t1, t2 +WHERE t1.lid = t2.lid AND t2.gid = lgid +ORDER BY t2.dt DESC LIMIT 1 +) as clid +FROM t2; +SELECT * FROM v1; +lgid clid +1 NO +2 YES +DROP VIEW v1; +DROP table t1,t2; End of 5.0 tests. --- 1.176/mysql-test/t/view.test 2007-03-04 19:54:41 -08:00 +++ 1.177/mysql-test/t/view.test 2007-03-04 19:54:41 -08:00 @@ -3058,4 +3058,44 @@ DROP VIEW v1; DROP TABLE t1; +# +# Bug#26560: view using subquery with a reference to an outer alias +# + +CREATE TABLE t1 ( + lid int NOT NULL PRIMARY KEY, + name char(10) NOT NULL +); +INSERT INTO t1 (lid, name) VALUES + (1, 'YES'), (2, 'NO'); + +CREATE TABLE t2 ( + id int NOT NULL PRIMARY KEY, + gid int NOT NULL, + lid int NOT NULL, + dt date +); +INSERT INTO t2 (id, gid, lid, dt) VALUES + (1, 1, 1, '2007-01-01'),(2, 1, 2, '2007-01-02'), + (3, 2, 2, '2007-02-01'),(4, 2, 1, '2007-02-02'); + +SELECT DISTINCT t2.gid AS lgid, + (SELECT t1.name FROM t1, t2 + WHERE t1.lid = t2.lid AND t2.gid = lgid + ORDER BY t2.dt DESC LIMIT 1 + ) as clid + FROM t2; + +CREATE VIEW v1 AS +SELECT DISTINCT t2.gid AS lgid, + (SELECT t1.name FROM t1, t2 + WHERE t1.lid = t2.lid AND t2.gid = lgid + ORDER BY t2.dt DESC LIMIT 1 + ) as clid + FROM t2; +SELECT * FROM v1; + +DROP VIEW v1; +DROP table t1,t2; + --echo End of 5.0 tests.
| Thread | ||
|---|---|---|
| • bk commit into 5.0 tree (igor:1.2431) BUG#26560 | igor | 5 Mar |
© 1995-2008 MySQL AB. All rights reserved.
Page generated in 0.471 sec. using MySQL 5.1.14-beta-log