From fe24add743df9dc2f8d72352e777c0f6f495d5fb Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 23 Aug 2005 18:08:04 +0300 Subject: WL#2486 - natural and using join according to SQL:2003 * Provide backwards compatibility extension to name resolution of coalesced columns. The patch allows such columns to be qualified with a table (and db) name, as it is in 4.1. Based on a patch from Monty. * Adjusted tests accordingly to test both backwards compatible name resolution of qualified columns, and ANSI-style resolution of non-qualified columns. For this, each affected test has two versions - one with qualified columns, and one without. mysql-test/include/ps_query.inc: Put back old tests to test that coalesced columns of natural joins can be qualified. mysql-test/r/bdb.result: Put back old tests to test that coalesced columns of natural joins can be qualified. mysql-test/r/innodb.result: Put back old tests to test that coalesced columns of natural joins can be qualified. mysql-test/r/join.result: Put back old tests to test that coalesced columns of natural joins can be qualified. mysql-test/r/join_nested.result: Put back old tests to test that coalesced columns of natural joins can be qualified. mysql-test/r/join_outer.result: Put back old tests to test that coalesced columns of natural joins can be qualified. mysql-test/r/null_key.result: Put back old tests to test that coalesced columns of natural joins can be qualified. mysql-test/r/order_by.result: Put back old tests to test that coalesced columns of natural joins can be qualified. mysql-test/r/ps_2myisam.result: Put back old tests to test that coalesced columns of natural joins can be qualified. mysql-test/r/ps_3innodb.result: Put back old tests to test that coalesced columns of natural joins can be qualified. mysql-test/r/ps_4heap.result: Put back old tests to test that coalesced columns of natural joins can be qualified. mysql-test/r/ps_5merge.result: Put back old tests to test that coalesced columns of natural joins can be qualified. mysql-test/r/ps_6bdb.result: Put back old tests to test that coalesced columns of natural joins can be qualified. mysql-test/r/ps_7ndb.result: Put back old tests to test that coalesced columns of natural joins can be qualified. mysql-test/r/select.result: Put back old tests to test that coalesced columns of natural joins can be qualified. mysql-test/r/subselect.result: Put back old tests to test that coalesced columns of natural joins can be qualified. mysql-test/r/type_ranges.result: Put back old tests to test that coalesced columns of natural joins can be qualified. mysql-test/t/bdb.test: Put back old tests to test that coalesced columns of natural joins can be qualified. mysql-test/t/innodb.test: Put back old tests to test that coalesced columns of natural joins can be qualified. mysql-test/t/join.test: Put back old tests to test that coalesced columns of natural joins can be qualified. mysql-test/t/join_nested.test: Put back old tests to test that coalesced columns of natural joins can be qualified. mysql-test/t/join_outer.test: Put back old tests to test that coalesced columns of natural joins can be qualified. mysql-test/t/null_key.test: Put back old tests to test that coalesced columns of natural joins can be qualified. mysql-test/t/order_by.test: Put back old tests to test that coalesced columns of natural joins can be qualified. mysql-test/t/select.test: Put back old tests to test that coalesced columns of natural joins can be qualified. mysql-test/t/subselect.test: Put back old tests to test that coalesced columns of natural joins can be qualified. mysql-test/t/type_ranges.test: Put back old tests to test that coalesced columns of natural joins can be qualified. sql/sql_base.cc: * Applied Monty's patch for backwards compatible name resolution of qualified columns. The idea is: - When a column is qualified, search for the column in all tables/views underlying each natural join. In this case natural joins are *not* considered leaves. - If a column is not qualified, then consider natural joins as leaves, thus directly search the result columns of natural joins. * Simplified 'find_field_in_tables()' - unified two similar loops into one. sql/table.cc: - Removed method & members not needed after Monty's patch. sql/table.h: - Removed method & members not needed after Monty's patch. tests/mysql_client_test.c: Put back old tests to test that coalesced columns of natural joins can be qualified. --- sql/sql_base.cc | 240 ++++++++++++++++++++++---------------------------------- sql/table.cc | 9 --- sql/table.h | 7 -- 3 files changed, 95 insertions(+), 161 deletions(-) (limited to 'sql') diff --git a/sql/sql_base.cc b/sql/sql_base.cc index b8748b89fbd..8017a82f1ed 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -2587,8 +2587,6 @@ find_field_in_view(THD *thd, TABLE_LIST *table_list, thd [in] thread handler table_ref [in] table reference to search name [in] name of field - table_name [in] optional table name that qualifies the field - db_name [in] optional database name that qualifies the field length [in] length of name ref [in/out] if 'name' is resolved to a view field, ref is set to point to the found view field @@ -2599,6 +2597,12 @@ find_field_in_view(THD *thd, TABLE_LIST *table_list, belongs - differs from 'table_list' only for NATURAL/USING joins + DESCRIPTION + Search for a field among the result fields of a NATURAL/USING join. + Notice that this procedure is called only for non-qualified field + names. In the case of qualified fields, we search directly the base + tables of a natural join. + RETURN NULL if the field was not found WRONG_GRANT if no access rights to the found field @@ -2607,7 +2611,6 @@ find_field_in_view(THD *thd, TABLE_LIST *table_list, static Field * find_field_in_natural_join(THD *thd, TABLE_LIST *table_ref, const char *name, - const char *table_name, const char *db_name, uint length, Item **ref, bool check_grants, bool register_tree_change, TABLE_LIST **actual_table) @@ -2629,27 +2632,6 @@ find_field_in_natural_join(THD *thd, TABLE_LIST *table_ref, const char *name, if (!(nj_col= field_it++)) DBUG_RETURN(NULL); - if (table_name) - { - /* - Coalesced columns cannot be qualified unless this is the execute phase - of prepared statements. The reason is that they do not belong to any - table, but for PS the prepare phase already resolves and stores - items, so during the execution phase we resolve fully qualified items. - */ - if (!thd->current_arena->is_stmt_execute() && nj_col->is_coalesced) - continue; - if (table_name[0] && - my_strcasecmp(table_alias_charset, nj_col->table_name(), table_name)) - continue; - if (db_name && db_name[0]) - { - const char *cur_db_name= nj_col->db_name(); - if (cur_db_name && strcmp(db_name, cur_db_name)) - continue; - } - } - if (!my_strcasecmp(system_charset_info, nj_col->name(), name)) break; } @@ -2842,11 +2824,35 @@ find_field_in_table_ref(THD *thd, TABLE_LIST *table_list, *actual_table= table_list; } else if (table_list->is_natural_join) - fld= find_field_in_natural_join(thd, table_list, name, table_name, - db_name, length, ref, + { + if (table_name && table_name[0]) + { + /* + Qualified field; Search for it in the tables used by the natural join. + */ + List_iterator it(table_list->nested_join->join_list); + TABLE_LIST *table; + while ((table= it++)) + { + if ((fld= find_field_in_table_ref(thd, table, name, item_name, + table_name, db_name, length, ref, + check_grants_table, + check_grants_view, + allow_rowid, cached_field_index_ptr, + register_tree_change, actual_table))) + DBUG_RETURN(fld); + } + DBUG_RETURN(0); + } + /* + Non-qualified field, search directly in the result columns of the + natural join. + */ + fld= find_field_in_natural_join(thd, table_list, name, length, ref, /* TIMOUR_TODO: check this with Sanja */ check_grants_table || check_grants_view, register_tree_change, actual_table); + } else { if ((fld= find_field_in_table(thd, table_list->table, name, length, @@ -2915,9 +2921,11 @@ find_field_in_tables(THD *thd, Item_ident *item, const char *name= item->field_name; uint length=(uint) strlen(name); char name_buff[NAME_LEN+1]; - bool allow_rowid; TABLE_LIST *cur_table= first_table; TABLE_LIST *actual_table; + bool is_qualified= table_name && table_name[0]; + bool allow_rowid= is_qualified ? + TRUE : (cur_table && !cur_table->next_local); if (item->cached_table) { @@ -2988,81 +2996,13 @@ find_field_in_tables(THD *thd, Item_ident *item, if (last_table) last_table= last_table->next_name_resolution_table; - /* The field we search for is qualified with a table name and optional db. */ - if (table_name && table_name[0]) - { - for (; cur_table != last_table ; - cur_table= cur_table->next_name_resolution_table) - { - Field *cur_field= find_field_in_table_ref(thd, cur_table, name, - item->name, table_name, - db, length, ref, - (cur_table->table && - test(cur_table->table->grant. - want_privilege) && - check_privileges), - (test(cur_table->grant. - want_privilege) && - check_privileges), - 1, &(item->cached_field_index), - register_tree_change, - &actual_table); - if (cur_field) - { - /* - Store the original table of the field, which may be different from - cur_table in the case of NATURAL/USING join. - */ - item->cached_table= (!actual_table->cacheable_table) ? 0 : actual_table; - - if (cur_field == WRONG_GRANT) - return (Field*) 0; - if (db || !thd->where) - return cur_field; - if (found) - { - if (report_error == REPORT_ALL_ERRORS || - report_error == IGNORE_EXCEPT_NON_UNIQUE) - my_error(ER_NON_UNIQ_ERROR, MYF(0), - item->full_name(),thd->where); - return (Field*) 0; - } - found= cur_field; - } - } - if (found) - return found; - /* - If there were no tables to search, we wouldn't go through the loop and - cur_table wouldn't be updated by the loop increment part. - */ - if (cur_table == first_table && (report_error == REPORT_ALL_ERRORS || - report_error == REPORT_EXCEPT_NON_UNIQUE)) - { - char buff[NAME_LEN*2+1]; - if (db && db[0]) - { - strxnmov(buff,sizeof(buff)-1,db,".",table_name,NullS); - table_name=buff; - } - my_error(ER_UNKNOWN_TABLE, MYF(0), table_name, thd->where); - } - else - if (report_error == REPORT_ALL_ERRORS || - report_error == REPORT_EXCEPT_NON_UNIQUE) - my_error(ER_BAD_FIELD_ERROR, MYF(0), item->full_name(),thd->where); - else - return (Field*) not_found_field; - return (Field*) 0; - } - - /* The field we search for is not qualified. */ - allow_rowid= cur_table && !cur_table->next_local; for (; cur_table != last_table ; cur_table= cur_table->next_name_resolution_table) { Field *cur_field= find_field_in_table_ref(thd, cur_table, name, item->name, - NULL, NULL, length, ref, + is_qualified ? table_name : NULL, + is_qualified ? db : NULL, + length, ref, (cur_table->table && test(cur_table->table->grant. want_privilege) && @@ -3086,26 +3026,57 @@ find_field_in_tables(THD *thd, Item_ident *item, item->cached_table= (!actual_table->cacheable_table || found) ? 0 : actual_table; + DBUG_ASSERT(thd->where); + /* + If we found a fully qualified field we return it directly as it can't + have duplicates. + */ + if (is_qualified && db) + return cur_field; + if (found) { - if (!thd->where) // Returns first found - break; if (report_error == REPORT_ALL_ERRORS || report_error == IGNORE_EXCEPT_NON_UNIQUE) - my_error(ER_NON_UNIQ_ERROR, MYF(0), name, thd->where); + my_error(ER_NON_UNIQ_ERROR, MYF(0), + is_qualified ? item->full_name() : name, thd->where); return (Field*) 0; } found= cur_field; } } + if (found) return found; - if (report_error == REPORT_ALL_ERRORS || - report_error == REPORT_EXCEPT_NON_UNIQUE) - my_error(ER_BAD_FIELD_ERROR, MYF(0), item->full_name(), thd->where); + + /* + If the field was qualified and there were no tables to search, issue + an error that an unknown table was given. The situation is detected + as follows: if there were no tables we wouldn't go through the loop + and cur_table wouldn't be updated by the loop increment part, so it + will be equal to the first table. + */ + if (is_qualified && (cur_table == first_table) && + (report_error == REPORT_ALL_ERRORS || + report_error == REPORT_EXCEPT_NON_UNIQUE)) + { + char buff[NAME_LEN*2+1]; + if (db && db[0]) + { + strxnmov(buff,sizeof(buff)-1,db,".",table_name,NullS); + table_name=buff; + } + my_error(ER_UNKNOWN_TABLE, MYF(0), table_name, thd->where); + } else - return (Field*) not_found_field; - return (Field*) 0; + if (report_error == REPORT_ALL_ERRORS || + report_error == REPORT_EXCEPT_NON_UNIQUE) + my_error(ER_BAD_FIELD_ERROR, MYF(0), item->full_name(), thd->where); + else + found= not_found_field; + + DBUG_ASSERT(!found || found == not_found_field); + return found; } @@ -3510,10 +3481,6 @@ mark_common_columns(THD *thd, TABLE_LIST *table_ref_1, TABLE_LIST *table_ref_2, Item_ident *item_ident_1, *item_ident_2; Item_func_eq *eq_cond; - DBUG_PRINT("info", ("new equi-join condition: %s.%s = %s.%s", - table_ref_1->alias, field_1->field_name, - table_ref_2->alias, field_2->field_name)); - if (!item_1 || !item_2) goto err; // out of memory @@ -3560,7 +3527,6 @@ mark_common_columns(THD *thd, TABLE_LIST *table_ref_1, TABLE_LIST *table_ref_2, eq_cond); nj_col_1->is_common= nj_col_2->is_common= TRUE; - nj_col_1->is_coalesced= nj_col_2->is_coalesced= TRUE; if (field_1) { @@ -4310,29 +4276,28 @@ insert_fields(THD *thd, Name_resolution_context *context, const char *db_name, } found= FALSE; - for (TABLE_LIST *tables= context->first_name_resolution_table; + + /* + If table names are qualified, then loop over all tables used in the query, + else treat natural joins as leaves and do not iterate over their underlying + tables. + */ + for (TABLE_LIST *tables= (table_name ? context->table_list : + context->first_name_resolution_table); tables; - tables= tables->next_name_resolution_table) + tables= (table_name ? tables->next_local : + tables->next_name_resolution_table) + ) { Field *field; TABLE *table= tables->table; DBUG_ASSERT(tables->is_leaf_for_name_resolution()); - /* - If optional table and db names do not match the ones used to qualify - the field being expanded, skip this table reference. However if this is - a NATURAL/USING join, we can't simply skip the whole table reference, - because its columns may come from different tables/views. For NATURAL/ - USING joins we perform this test for each column in the loop below. - */ - if (!tables->is_natural_join) - { - if (table_name && my_strcasecmp(table_alias_charset, table_name, - tables->alias) || - (db_name && strcmp(tables->db,db_name))) - continue; - } + if (table_name && my_strcasecmp(table_alias_charset, table_name, + tables->alias) || + (db_name && strcmp(tables->db,db_name))) + continue; #ifndef NO_EMBEDDED_ACCESS_CHECKS /* Ensure that we have access rights to all fields to be inserted. */ @@ -4369,21 +4334,6 @@ insert_fields(THD *thd, Name_resolution_context *context, const char *db_name, { Item *item; - /* - If this is a column of a NATURAL/USING join, and the star was - qualified with a table (and database) name, check if the - column is not a coalesced one, and if not, that is belongs to - the same table. - */ - if (tables->is_natural_join && table_name) - { - if (field_iterator.is_coalesced() || - my_strcasecmp(table_alias_charset, table_name, - field_iterator.table_name()) || - (db_name && strcmp(db_name, field_iterator.db_name()))) - continue; - } - if (!(item= field_iterator.create_item(thd))) DBUG_RETURN(TRUE); @@ -4410,18 +4360,18 @@ insert_fields(THD *thd, Name_resolution_context *context, const char *db_name, tables->is_natural_join); DBUG_ASSERT(item->type() == Item::FIELD_ITEM); Item_field *fld= (Item_field*) item; - const char *table_name= field_iterator.table_name(); + const char *field_table_name= field_iterator.table_name(); if (!tables->schema_table && !(fld->have_privileges= (get_column_grant(thd, field_iterator.grant(), field_iterator.db_name(), - table_name, fld->field_name) & + field_table_name, fld->field_name) & VIEW_ANY_ACL))) { my_error(ER_COLUMNACCESS_DENIED_ERROR, MYF(0), "ANY", thd->priv_user, thd->host_or_ip, - fld->field_name, table_name); + fld->field_name, field_table_name); DBUG_RETURN(TRUE); } } diff --git a/sql/table.cc b/sql/table.cc index beecd6442e8..5983b9afda7 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -2289,7 +2289,6 @@ Natural_join_column::Natural_join_column(Field_translator *field_param, table_field= NULL; table_ref= tab; is_common= FALSE; - is_coalesced= FALSE; } @@ -2301,7 +2300,6 @@ Natural_join_column::Natural_join_column(Field *field_param, view_field= NULL; table_ref= tab; is_common= FALSE; - is_coalesced= FALSE; } @@ -2641,13 +2639,6 @@ GRANT_INFO *Field_iterator_table_ref::grant() } -bool Field_iterator_table_ref::is_coalesced() -{ - if (table_ref->is_natural_join) - return natural_join_it.column_ref()->is_coalesced; - return FALSE; -} - /* Create new or return existing column reference to a column of a natural/using join. diff --git a/sql/table.h b/sql/table.h index c0e4ad4c150..b6616150610 100644 --- a/sql/table.h +++ b/sql/table.h @@ -388,12 +388,6 @@ public: we are looking at some column. */ bool is_common; - /* - A column is coalesced if it was common in some of several nested NATURAL/ - USING joins. We have to know this, because according to ANSI, coalesced - columns cannot be qualified. - */ - bool is_coalesced; public: Natural_join_column(Field_translator *field_param, st_table_list *tab); Natural_join_column(Field *field_param, st_table_list *tab); @@ -741,7 +735,6 @@ public: const char *table_name(); const char *db_name(); GRANT_INFO *grant(); - bool is_coalesced(); Item *create_item(THD *thd) { return field_it->create_item(thd); } Field *field() { return field_it->field(); } Natural_join_column *get_or_create_column_ref(THD *thd, bool *is_created); -- cgit v1.2.1