#At lp:maria/5.2 based on revid:igor@askmonty.org-20100518174632-e2xaeunykfmtyafm 2792 Igor Babaev 2010-05-26 MWL#106: creation of keys for materialized derived tables/views. Also fixed several bugs in the backported code. modified: mysql-test/r/derived_view.result sql/item_cmpfunc.cc sql/item_subselect.cc sql/sql_base.cc sql/sql_delete.cc sql/sql_insert.cc sql/sql_join_cache.cc sql/sql_lex.h sql/sql_parse.cc sql/sql_prepare.cc sql/sql_select.cc sql/sql_select.h sql/table.cc sql/table.h === modified file 'mysql-test/r/derived_view.result' --- a/mysql-test/r/derived_view.result 2010-05-18 17:46:32 +0000 +++ b/mysql-test/r/derived_view.result 2010-05-26 20:04:58 +0000 @@ -556,7 +556,7 @@ test two keys explain select * from t1 join (select * from t2 group by f2) tt on t1.f1=tt.f2 join t1 xx on tt.f22=xx.f1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 11 -1 PRIMARY <derived2> ALL key0 NULL NULL NULL 11 Using where; Using join buffer +1 PRIMARY <derived2> ref key0 key0 5 test.t1.f1 2 Using where 1 PRIMARY xx ALL NULL NULL NULL NULL 11 Using where; Using join buffer 2 DERIVED t2 ALL NULL NULL NULL NULL 11 Using temporary; Using filesort select * from t1 join (select * from t2 group by f2) tt on t1.f1=tt.f2 join t1 xx on tt.f22=xx.f1; === modified file 'sql/item_cmpfunc.cc' --- a/sql/item_cmpfunc.cc 2010-05-18 17:46:32 +0000 +++ b/sql/item_cmpfunc.cc 2010-05-26 20:04:58 +0000 @@ -4232,10 +4232,6 @@ Item_cond::fix_fields(THD *thd, Item **r (item= *li.ref())->check_cols(1)) return TRUE; /* purecov: inspected */ used_tables_cache|= item->used_tables(); -#if 0 - if (!item->const_item()) - const_item_cache= FALSE; -#else if (item->const_item()) and_tables_cache= (table_map) 0; else @@ -4245,7 +4241,6 @@ Item_cond::fix_fields(THD *thd, Item **r and_tables_cache&= tmp_table_map; const_item_cache= FALSE; } -#endif with_sum_func= with_sum_func || item->with_sum_func; with_subselect|= item->with_subselect; === modified file 'sql/item_subselect.cc' --- a/sql/item_subselect.cc 2010-04-29 21:10:39 +0000 +++ b/sql/item_subselect.cc 2010-05-26 20:04:58 +0000 @@ -2894,6 +2894,9 @@ int subselect_uniquesubquery_engine::exe DBUG_RETURN(0); } + if (!tab->preread_init_done && tab->preread_init()) + DBUG_RETURN(1); + if (null_keypart) DBUG_RETURN(scan_table()); @@ -3026,7 +3029,7 @@ subselect_uniquesubquery_engine::~subsel int subselect_indexsubquery_engine::exec() { - DBUG_ENTER("subselect_indexsubquery_engine::exec"); + DBUG_ENTER("subselect_indexsubquery_engine"); int error; bool null_finding= 0; TABLE *table= tab->table; @@ -3057,6 +3060,9 @@ int subselect_indexsubquery_engine::exec DBUG_RETURN(0); } + if (!tab->preread_init_done && tab->preread_init()) + DBUG_RETURN(1); + if (null_keypart) DBUG_RETURN(scan_table()); === modified file 'sql/sql_base.cc' --- a/sql/sql_base.cc 2010-05-12 04:09:58 +0000 +++ b/sql/sql_base.cc 2010-05-26 20:04:58 +0000 @@ -6288,7 +6288,9 @@ find_field_in_tables(THD *thd, Item_iden find_field_in_table even in the case of information schema tables when table_ref->field_translation != NULL. */ - if (table_ref->table && !table_ref->is_merged_derived()) + if (table_ref->table && + (!table_ref->is_merged_derived() || + (!table_ref->is_multitable() && table_ref->merged_for_insert))) found= find_field_in_table(thd, table_ref->table, name, length, TRUE, &(item->cached_field_index)); else === modified file 'sql/sql_delete.cc' --- a/sql/sql_delete.cc 2010-05-12 04:09:58 +0000 +++ b/sql/sql_delete.cc 2010-05-26 20:04:58 +0000 @@ -559,6 +559,11 @@ int mysql_multi_delete_prepare(THD *thd) TABLE_LIST *target_tbl; DBUG_ENTER("mysql_multi_delete_prepare"); + TABLE_LIST *tables= lex->query_tables; + if (mysql_handle_derived(lex, DT_INIT) || + mysql_handle_list_of_derived(lex, tables, DT_MERGE_FOR_INSERT) || + mysql_handle_list_of_derived(lex, tables, DT_PREPARE)) + DBUG_RETURN(TRUE); /* setup_tables() need for VIEWs. JOIN::prepare() will not do it second time. === modified file 'sql/sql_insert.cc' --- a/sql/sql_insert.cc 2010-04-29 21:10:39 +0000 +++ b/sql/sql_insert.cc 2010-05-26 20:04:58 +0000 @@ -1184,8 +1184,8 @@ static bool mysql_prepare_insert_check_t if (insert_into_view && !fields.elements) { thd->lex->empty_field_list_on_rset= 1; - if (table_list->is_multitable() && !table_list->table || - !table_list->table->created) + if (!thd->lex->select_lex.leaf_tables.head()->table || + table_list->is_multitable()) { my_error(ER_VIEW_NO_INSERT_FIELD_LIST, MYF(0), table_list->view_db.str, table_list->view_name.str); @@ -1276,8 +1276,10 @@ bool mysql_prepare_insert(THD *thd, TABL /* INSERT should have a SELECT or VALUES clause */ DBUG_ASSERT (!select_insert || !values); + if (mysql_handle_derived(thd->lex, DT_INIT)) + DBUG_RETURN(TRUE); if (table_list->handle_derived(thd->lex, DT_MERGE_FOR_INSERT)) - DBUG_RETURN(TRUE); + DBUG_RETURN(TRUE); if (mysql_handle_list_of_derived(thd->lex, table_list, DT_PREPARE)) DBUG_RETURN(TRUE); /* === modified file 'sql/sql_join_cache.cc' --- a/sql/sql_join_cache.cc 2010-03-07 15:41:45 +0000 +++ b/sql/sql_join_cache.cc 2010-05-26 20:04:58 +0000 @@ -2370,6 +2370,8 @@ JOIN_CACHE_BKA::init_join_matching_recor init_mrr_buff(); + if (!join_tab->preread_init_done && join_tab->preread_init()) + return NESTED_LOOP_ERROR; /* Prepare to iterate over keys from the join buffer and to get matching candidates obtained with MMR handler functions. === modified file 'sql/sql_lex.h' --- a/sql/sql_lex.h 2010-04-29 21:10:39 +0000 +++ b/sql/sql_lex.h 2010-05-26 20:04:58 +0000 @@ -1873,6 +1873,8 @@ typedef struct st_lex : public Query_tab switch (sql_command) { case SQLCOM_UPDATE: case SQLCOM_UPDATE_MULTI: + case SQLCOM_DELETE: + case SQLCOM_DELETE_MULTI: case SQLCOM_INSERT: case SQLCOM_INSERT_SELECT: case SQLCOM_REPLACE: === modified file 'sql/sql_parse.cc' --- a/sql/sql_parse.cc 2010-04-29 21:10:39 +0000 +++ b/sql/sql_parse.cc 2010-05-26 20:04:58 +0000 @@ -3425,9 +3425,6 @@ end_with_restore_list: thd_proc_info(thd, "init"); if ((res= open_and_lock_tables(thd, all_tables))) break; - if (mysql_handle_list_of_derived(lex, all_tables, DT_MERGE_FOR_INSERT) || - mysql_handle_list_of_derived(lex, all_tables, DT_PREPARE)) - DBUG_RETURN(1); if ((res= mysql_multi_delete_prepare(thd))) goto error; === modified file 'sql/sql_prepare.cc' --- a/sql/sql_prepare.cc 2010-04-29 21:10:39 +0000 +++ b/sql/sql_prepare.cc 2010-05-26 20:04:58 +0000 @@ -1133,9 +1133,7 @@ static bool mysql_test_insert(Prepared_s If we would use locks, then we have to ensure we are not using TL_WRITE_DELAYED as having two such locks can cause table corruption. */ - if (open_normal_and_derived_tables(thd, table_list, 0, - DT_INIT | DT_PREPARE | DT_CREATE) || - mysql_handle_single_derived(thd->lex, table_list, DT_MERGE_FOR_INSERT)) + if (open_normal_and_derived_tables(thd, table_list, 0, DT_INIT)) goto error; if ((values= its++)) @@ -1236,9 +1234,16 @@ static int mysql_test_update(Prepared_st thd->fill_derived_tables() is false here for sure (because it is preparation of PS, so we even do not check it). */ - if (mysql_handle_derived(thd->lex, DT_PREPARE)) + if (table_list->handle_derived(thd->lex, DT_MERGE_FOR_INSERT) || + table_list->handle_derived(thd->lex, DT_PREPARE)) goto error; + if (!table_list->updatable) + { + my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias, "UPDATE"); + goto error; + } + #ifndef NO_EMBEDDED_ACCESS_CHECKS /* Force privilege re-checking for views after they have been opened. */ want_privilege= (table_list->view ? UPDATE_ACL : @@ -1291,13 +1296,18 @@ error: static bool mysql_test_delete(Prepared_statement *stmt, TABLE_LIST *table_list) { + uint table_count= 0; THD *thd= stmt->thd; LEX *lex= stmt->lex; DBUG_ENTER("mysql_test_delete"); if (delete_precheck(thd, table_list) || - open_normal_and_derived_tables(thd, table_list, 0, - DT_PREPARE | DT_CREATE)) + open_tables(thd, &table_list, &table_count, 0)) + goto error; + + if (mysql_handle_derived(thd->lex, DT_INIT) || + mysql_handle_list_of_derived(thd->lex, table_list, DT_MERGE_FOR_INSERT) || + mysql_handle_list_of_derived(thd->lex, table_list, DT_PREPARE)) goto error; if (!table_list->table) @@ -1561,6 +1571,7 @@ select_like_stmt_test_with_open(Prepared int (*specific_prepare)(THD *thd), ulong setup_tables_done_option) { + uint table_count= 0; DBUG_ENTER("select_like_stmt_test_with_open"); /* @@ -1569,8 +1580,8 @@ select_like_stmt_test_with_open(Prepared prepared EXPLAIN yet so derived tables will clean up after themself. */ - if (open_normal_and_derived_tables(stmt->thd, tables, 0, - DT_PREPARE | DT_CREATE)) + THD *thd= stmt->thd; + if (open_tables(thd, &tables, &table_count, 0)) DBUG_RETURN(TRUE); DBUG_RETURN(select_like_stmt_test(stmt, specific_prepare, === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-05-18 17:46:32 +0000 +++ b/sql/sql_select.cc 2010-05-26 20:04:58 +0000 @@ -238,7 +238,7 @@ static void add_group_and_distinct_keys( void get_partial_join_cost(JOIN *join, uint idx, double *read_time_arg, double *record_count_arg); static uint make_join_orderinfo(JOIN *join); -static bool generate_derived_keys(List<TABLE_LIST> &tables); +static bool generate_derived_keys(DYNAMIC_ARRAY *keyuse_array); static int join_read_record_no_init(JOIN_TAB *tab); @@ -744,18 +744,6 @@ JOIN::optimize() tables= select_lex->leaf_tables.elements; -#if 0 - if (thd->lex->describe) - { - /* - Force join->join_tmp creation, because we will use this JOIN - twice for EXPLAIN and we have to have unchanged join for EXPLAINing - */ - select_lex->uncacheable|= UNCACHEABLE_EXPLAIN; - select_lex->master_unit()->uncacheable|= UNCACHEABLE_EXPLAIN; - } -#else -#endif if (setup_ftfuncs(select_lex)) /* should be after having->fix_fields */ DBUG_RETURN(-1); @@ -3311,18 +3299,20 @@ add_key_field(KEY_FIELD **key_fields,uin Field *field, bool eq_func, Item **value, uint num_values, table_map usable_tables, SARGABLE_PARAM **sargables) { - uint exists_optimize= 0; - if (field->table->pos_in_table_list->is_materialized_derived() && + uint optimize= 0; + if (eq_func && + field->table->pos_in_table_list->is_materialized_derived() && !field->table->created) - field->table->pos_in_table_list->update_derived_keys(field, value, - num_values); - if (!(field->flags & PART_KEY_FLAG)) + { + optimize= KEY_OPTIMIZE_EQ; + } + else if (!(field->flags & PART_KEY_FLAG)) { // Don't remove column IS NULL on a LEFT JOIN table if (!eq_func || (*value)->type() != Item::NULL_ITEM || !field->table->maybe_null || field->null_ptr) return; // Not a key. Skip it - exists_optimize= KEY_OPTIMIZE_EXISTS; + optimize= KEY_OPTIMIZE_EXISTS; DBUG_ASSERT(num_values == 1); } else @@ -3342,7 +3332,7 @@ add_key_field(KEY_FIELD **key_fields,uin if (!eq_func || (*value)->type() != Item::NULL_ITEM || !field->table->maybe_null || field->null_ptr) return; // Can't use left join optimize - exists_optimize= KEY_OPTIMIZE_EXISTS; + optimize= KEY_OPTIMIZE_EXISTS; } else { @@ -3441,7 +3431,7 @@ add_key_field(KEY_FIELD **key_fields,uin (*key_fields)->eq_func= eq_func; (*key_fields)->val= *value; (*key_fields)->level= and_level; - (*key_fields)->optimize= exists_optimize; + (*key_fields)->optimize= optimize; /* If the condition has form "tbl.keypart = othertbl.field" and othertbl.field can be NULL, there will be no matches if othertbl.field @@ -3760,6 +3750,34 @@ max_part_bit(key_part_map bits) return found; } +static bool +add_keyuse(DYNAMIC_ARRAY *keyuse_array, KEY_FIELD *key_field, + uint key, uint part) +{ + KEYUSE keyuse; + Field *field= key_field->field; + + keyuse.table= field->table; + keyuse.val= key_field->val; + keyuse.key= key; + if (key != MAX_KEY) + { + keyuse.keypart=part; + keyuse.keypart_map= (key_part_map) 1 << part; + } + else + { + keyuse.keypart= field->field_index; + keyuse.keypart_map= (key_part_map) 0; + } + keyuse.used_tables= key_field->val->used_tables(); + keyuse.optimize= key_field->optimize & KEY_OPTIMIZE_REF_OR_NULL; + keyuse.null_rejecting= key_field->null_rejecting; + keyuse.cond_guard= key_field->cond_guard; + keyuse.sj_pred_no= key_field->sj_pred_no; + return (insert_dynamic(keyuse_array,(uchar*) &keyuse)); +} + /* Add all keys with uses 'field' for some keypart If field->and_level != and_level then only mark key_part as const_part @@ -3774,10 +3792,13 @@ add_key_part(DYNAMIC_ARRAY *keyuse_array { Field *field=key_field->field; TABLE *form= field->table; - KEYUSE keyuse; if (key_field->eq_func && !(key_field->optimize & KEY_OPTIMIZE_EXISTS)) { + if (key_field->eq_func && (key_field->optimize & KEY_OPTIMIZE_EQ)) + { + return add_keyuse(keyuse_array, key_field, MAX_KEY, 0); + } for (uint key=0 ; key < form->s->keys ; key++) { if (!(form->keys_in_use_for_query.is_set(key))) @@ -3790,17 +3811,7 @@ add_key_part(DYNAMIC_ARRAY *keyuse_array { if (field->eq(form->key_info[key].key_part[part].field)) { - keyuse.table= field->table; - keyuse.val = key_field->val; - keyuse.key = key; - keyuse.keypart=part; - keyuse.keypart_map= (key_part_map) 1 << part; - keyuse.used_tables=key_field->val->used_tables(); - keyuse.optimize= key_field->optimize & KEY_OPTIMIZE_REF_OR_NULL; - keyuse.null_rejecting= key_field->null_rejecting; - keyuse.cond_guard= key_field->cond_guard; - keyuse.sj_pred_no= key_field->sj_pred_no; - if (insert_dynamic(keyuse_array,(uchar*) &keyuse)) + if (add_keyuse(keyuse_array, key_field, key, part)) return TRUE; } } @@ -3885,6 +3896,9 @@ sort_keyuse(KEYUSE *a,KEYUSE *b) return (int) (a->table->tablenr - b->table->tablenr); if (a->key != b->key) return (int) (a->key - b->key); + if (a->key == MAX_KEY && b->key == MAX_KEY && + a->used_tables != b->used_tables) + return (int) ((ulong) a->used_tables - (ulong) b->used_tables); if (a->keypart != b->keypart) return (int) (a->keypart - b->keypart); // Place const values before other ones @@ -4080,9 +4094,6 @@ update_ref_and_keys(THD *thd, DYNAMIC_AR } } - /* Generate keys descriptions for derived tables */ - generate_derived_keys(select_lex->leaf_tables); - /* fill keyuse with found key parts */ for ( ; field != end ; field++) { @@ -4117,6 +4128,8 @@ update_ref_and_keys(THD *thd, DYNAMIC_AR if (insert_dynamic(keyuse,(uchar*) &key_end)) return TRUE; + generate_derived_keys(keyuse); + use=save_pos=dynamic_element(keyuse,0,KEYUSE*); prev= &key_end; found_eq_constant=0; @@ -6979,33 +6992,93 @@ make_join_select(JOIN *join,SQL_SELECT * } -/** - @brief - Add keys to derived tables'/views' result tables in a list - - @param tables list of tables to generate keys for - - @details - This function generates keys for all derived tables/views in the 'tables' - list with help of the TABLE_LIST:generate_keys function. +static +uint get_next_field_for_derived_key(uchar *arg) +{ + KEYUSE *keyuse= *(KEYUSE **) arg; + if (!keyuse) + return (uint) (-1); + uint key= keyuse->key; + uint fldno= keyuse->keypart; + uint keypart= keyuse->keypart_map == (key_part_map) 1 ? + 0 : (keyuse-1)->keypart+1; + for ( ; keyuse->key == key && keyuse->keypart == fldno; keyuse++) + keyuse->keypart= keypart; + if (keyuse->key != key) + keyuse= 0; + return fldno; +} - @note currently this function can't fail because errors from the - TABLE_LIST:generate_keys function is ignored as they aren't critical to the - query execution. - @return FALSE all keys were successfully added. -*/ +static +bool generate_derived_keys_for_table(KEYUSE *keyuse, uint count, uint keys) +{ + TABLE *table= keyuse->table; + if (table->alloc_keys(keys)) + return TRUE; + uint keyno= 0; + KEYUSE *first_keyuse= keyuse; + uint prev_part= (uint) (-1); + uint parts= 0; + uint i= 0; + do + { + keyuse->key= keyno; + keyuse->keypart_map= (key_part_map) (1 << parts); + keyuse++; + if (++i == count || keyuse->used_tables != first_keyuse->used_tables) + { + if (table->add_tmp_key(keyno, ++parts, + get_next_field_for_derived_key, + (uchar *) &first_keyuse)) + return TRUE; + first_keyuse= keyuse; + keyno++; + parts= 0; + } + else if (keyuse->keypart != prev_part) + { + parts++; + prev_part= keyuse->keypart; + } + } while (keyno < keys); + return FALSE; +} + static -bool generate_derived_keys(List<TABLE_LIST> &tables) +bool generate_derived_keys(DYNAMIC_ARRAY *keyuse_array) { - TABLE_LIST *table; - List_iterator<TABLE_LIST> ti(tables); - while ((table= ti++)) + KEYUSE *keyuse= dynamic_element(keyuse_array, 0, KEYUSE*); + uint elements= keyuse_array->elements; + TABLE *prev_table= 0; + for (uint i= 0; i < elements; i++, keyuse++) { - /* Process tables that aren't materialized yet. */ - if (table->is_materialized_derived() && !table->table->created) - table->generate_keys(); + KEYUSE *first_table_keyuse; + table_map last_used_tables; + uint count; + uint keys; + while (keyuse->key == MAX_KEY) + { + if (keyuse->table != prev_table) + { + prev_table= keyuse->table; + first_table_keyuse= keyuse; + last_used_tables= keyuse->used_tables; + count= 0; + keys= 0; + } + else if (keyuse->used_tables != last_used_tables) + { + keys++; + last_used_tables= keyuse->used_tables; + } + count++; + keyuse++; + if (keyuse->table != prev_table && + generate_derived_keys_for_table(first_table_keyuse, count, ++keys)) + return TRUE; + } } return FALSE; } === modified file 'sql/sql_select.h' --- a/sql/sql_select.h 2010-05-18 17:46:32 +0000 +++ b/sql/sql_select.h 2010-05-26 20:04:58 +0000 @@ -37,6 +37,7 @@ /* Values in optimize */ #define KEY_OPTIMIZE_EXISTS 1 #define KEY_OPTIMIZE_REF_OR_NULL 2 +#define KEY_OPTIMIZE_EQ 4 typedef struct keyuse_t { TABLE *table; === modified file 'sql/table.cc' --- a/sql/table.cc 2010-05-12 04:09:58 +0000 +++ b/sql/table.cc 2010-05-26 20:04:58 +0000 @@ -5143,12 +5143,11 @@ void st_table::mark_virtual_columns_for_ bool TABLE::alloc_keys(uint key_count) { DBUG_ASSERT(!s->keys); - key_info= s->key_info= (KEY*) my_malloc(sizeof(KEY)*key_count, MYF(0)); + key_info= s->key_info= (KEY*) alloc_root(&mem_root, sizeof(KEY)*key_count); max_keys= key_count; return !(key_info); } - /** @brief Adds one key to a temporary table. @@ -5164,40 +5163,41 @@ bool TABLE::alloc_keys(uint key_count) @return >=0 number of newly added key. */ -int TABLE::add_tmp_key(ulonglong key_parts, char *key_name) +bool TABLE::add_tmp_key(uint key, uint key_parts, + uint (*next_field_no) (uchar *), uchar *arg) { - DBUG_ASSERT(!created && s->keys< max_keys); + DBUG_ASSERT(!created && key < max_keys); + char buf[NAME_CHAR_LEN]; KEY* keyinfo; Field **reg_field; uint i; bool key_start= TRUE; - uint key_part_count= my_count_bits(key_parts); KEY_PART_INFO* key_part_info= - (KEY_PART_INFO*) my_malloc(sizeof(KEY_PART_INFO)* key_part_count, MYF(0)); + (KEY_PART_INFO*) alloc_root(&mem_root, sizeof(KEY_PART_INFO)*key_parts); if (!key_part_info) - return -1; - keyinfo= key_info + s->keys; - keyinfo->key_part=key_part_info; - keyinfo->usable_key_parts=keyinfo->key_parts= key_part_count; + return TRUE; + keyinfo= key_info + key; + keyinfo->key_part= key_part_info; + keyinfo->usable_key_parts= keyinfo->key_parts = key_parts; keyinfo->key_length=0; keyinfo->algorithm= HA_KEY_ALG_UNDEF; - keyinfo->name= key_name; keyinfo->flags= HA_GENERATED_KEY; - keyinfo->rec_per_key= (ulong*)my_malloc(sizeof(ulong)*key_part_count, MYF(0)); + sprintf(buf, "key%i", key); + if (!(keyinfo->name= strdup_root(&mem_root, buf))) + return TRUE; + keyinfo->rec_per_key= (ulong*) alloc_root(&mem_root, + sizeof(ulong)*key_parts); if (!keyinfo->rec_per_key) - return -1; - bzero(keyinfo->rec_per_key, sizeof(ulong)*key_part_count); - for (i= 0, reg_field=field ; - *reg_field; - i++, reg_field++) + return TRUE; + bzero(keyinfo->rec_per_key, sizeof(ulong)*key_parts); + for (i= 0; i < key_parts; i++) { - if (!(key_parts & (1 << i))) - continue; + reg_field= field + next_field_no(arg); if (key_start) - (*reg_field)->key_start.set_bit(s->keys); + (*reg_field)->key_start.set_bit(key); key_start= FALSE; - (*reg_field)->part_of_key.set_bit(s->keys); + (*reg_field)->part_of_key.set_bit(key); (*reg_field)->flags|= PART_KEY_FLAG; key_part_info->null_bit= (*reg_field)->null_bit; key_part_info->null_offset= (uint) ((*reg_field)->null_ptr - @@ -5231,10 +5231,10 @@ int TABLE::add_tmp_key(ulonglong key_par key_part_info++; } set_if_bigger(s->max_key_length, keyinfo->key_length); - return ++s->keys - 1; + s->keys++; + return FALSE; } - /* @brief Drop all indexes except specified one. @@ -5310,6 +5310,7 @@ void TABLE_LIST::reinit_before_use(THD * parent_embedding->nested_join->join_list.head() == embedded); } + /* Return subselect that contains the FROM list this table is taken from @@ -5819,143 +5820,8 @@ int TABLE_LIST::fetch_number_of_rows() t1 ON tt.f1=a_function(t1.f3); In this case for the derived table tt one key will be generated. It will consist of one field - f1. - - Implementation is split in two steps: - gathering information on all used fields of derived tables/view and - store it in lists of possible keys, one per a derived table/view. - add keys to result tables of derived tables/view using info from above - lists. - - The above procedure is implemented in 4 functions: - TABLE_LIST::update_derived_keys - Create/extend list of possible keys for one derived - table/view based on given field/used tables info. - (Step one) - generate_derived_keys This function is called at the moment when all - possible info on keys is gathered and it's safe to - add keys. Walk over list of derived tables/views and - calls to TABLE_LIST::generate_keys to actually - generate keys. (Step two) - TABLE_LIST::generate_keys - Walks over list of possible keys for this derived - table/view to add keys to the result table. - Calls to TABLE::add_tmp_index to actually add - keys. (Step two) - TABLE::add_tmp_index Creates one index description according to given - bitmap of used fields. (Step two) - There is also the fifth function called TABLE::use_index. It saves used - key and frees others. It is called when the optimizer has chosen which key - it will use, thus we don't need other keys anymore. -*/ - - -/* - @brief - Update derived table's list of possible keys - - @param field derived table's field to take part in a key - @param values array of values - @param num_values number of elements in the array values - - @details - This function creates/extends a list of possible keys for this derived - table/view. For each table used by a value from the 'values' array the - corresponding possible key is extended to include the 'field'. - If there is no such possible key then it is created. field's - key_start/part_of_key bitmaps are updated accordingly. - - @return TRUE new possible key can't be allocated. - @return FALSE list of possible keys successfully updated. -*/ - -bool TABLE_LIST::update_derived_keys(Field *field, Item **values, - uint num_values) -{ - DERIVED_KEY_MAP *entry= 0; - List_iterator<DERIVED_KEY_MAP> ki(derived_keymap_list); - uint i; - - /* Allow all keys to be used. */ - if (!derived_keymap_list.elements) - { - table->keys_in_use_for_query.set_all(); - table->s->uniques= 0; - derived_keymap_list.empty(); - } - - for (i= 0; i < num_values; i++) - { - uint tbl; - table_map tables= values[i]->used_tables() & ~OUTER_REF_TABLE_BIT; - for (tbl= 1; tables >= tbl; tbl<<= 1) - { - uint key= 0; - if (! (tables & tbl)) - continue; - ki.rewind(); - while ((entry= ki++)) - { - key++; - if (entry->referenced_by & tbl) - break; - } - if (!entry) - { - key++; - entry= (DERIVED_KEY_MAP*)my_malloc(sizeof(DERIVED_KEY_MAP), MYF(0)); - if (!entry) - return TRUE; - entry->referenced_by|= tbl; - entry->used_fields.clear_all(); - derived_keymap_list.push_back(entry); - field->key_start.set_bit(key); - table->max_keys++; - } - field->part_of_key.set_bit(key - 1); - field->flags|= PART_KEY_FLAG; - entry->used_fields.set_bit(field->field_index); - } - } - return FALSE; -} - - -/** - @brief - Generate keys for a materialized derived table/view - - @details - This function adds keys to the result table by walking over the list of - possible keys for this derived table/view and calling to the - TABLE::add_tmp_index to actually add keys. A name "key" with a sequential - number is given to each key to ease debugging. - - @return TRUE an error occur. - @return FALSE all keys were successfully added. */ -bool TABLE_LIST::generate_keys() -{ - List_iterator<DERIVED_KEY_MAP> it(derived_keymap_list); - DERIVED_KEY_MAP *entry; - uint key= 0; - char buf[NAME_CHAR_LEN]; - DBUG_ASSERT(is_materialized_derived()); - - if (!derived_keymap_list.elements) - return FALSE; - - table->alloc_keys(table->max_keys); - while ((entry= it++)) - { - table->s->key_parts+= entry->used_fields.bits_set(); - sprintf(buf, "key%i", key++); - if (table->add_tmp_key(entry->used_fields.to_ulonglong(), - table->in_use->strdup(buf)) < 0) - return TRUE; - } - return FALSE; -} /* === modified file 'sql/table.h' --- a/sql/table.h 2010-04-29 21:10:39 +0000 +++ b/sql/table.h 2010-05-26 20:04:58 +0000 @@ -916,7 +916,8 @@ struct st_table { inline bool needs_reopen_or_name_lock() { return s->version != refresh_version; } bool alloc_keys(uint key_count); - int add_tmp_key(ulonglong key_parts, char *key_name); + bool add_tmp_key(uint key, uint key_parts, + uint (*next_field_no) (uchar *), uchar *arg); void use_index(int key_to_save); void set_table_map(table_map map_arg, uint tablenr_arg) { @@ -1202,20 +1203,6 @@ class Item_in_subselect; (TABLE_LIST::join_using_fields != NULL) */ -/* - This structure is used to keep info about possible key for the result table - of a derived table/view. - The 'referenced_by' is the table map of the table to which this possible - key corresponds. - The 'used_field' is a map of fields of which this key consists of. - See also the comment for the TABLE_LIST::update_derived_keys function. -*/ -struct st_derived_table_key_map { - table_map referenced_by; - key_map used_fields; -}; -typedef st_derived_table_key_map DERIVED_KEY_MAP; - class Index_hint; struct st_lex; struct TABLE_LIST @@ -1531,8 +1518,6 @@ struct TABLE_LIST uint table_open_method; enum enum_schema_table_state schema_table_state; - List<DERIVED_KEY_MAP> derived_keymap_list; - void calc_md5(char *buffer); int view_check_option(THD *thd, bool ignore_failure); bool create_field_translation(THD *thd); @@ -1709,8 +1694,6 @@ struct TABLE_LIST void wrap_into_nested_join(List<TABLE_LIST> &join_list); bool init_derived(THD *thd, bool init_view); int fetch_number_of_rows(); - bool update_derived_keys(Field *field, Item **values, uint num_values); - bool generate_keys(); bool change_refs_to_fields(); private: