From c258ca2463947fcc3d69bb50a8b5cf6906778508 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Wed, 7 Jun 2017 12:45:09 -0700 Subject: Fixed the bug mdev-12838. If the optimizer chose an execution plan where a semi-join nest were materialized and the result of materialization was scanned to access other tables by ref access it could build a key over columns of the tables from the nest that were actually inaccessible. The patch performs a proper check whether a key that uses columns of the tables from a materialized semi-join nest can be employed to access outer tables. --- sql/sql_select.cc | 71 +++++++++++++++++++++++++++++++++++++++++++++++++++---- 1 file changed, 66 insertions(+), 5 deletions(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index bad57aeac87..86ba0346366 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -7361,6 +7361,63 @@ bool JOIN_TAB::hash_join_is_possible() } +/** + @brief + Check whether a KEYUSE can be really used for access this join table + + @param join Join structure with the best join order + for which the check is performed + @param keyuse Evaluated KEYUSE structure + + @details + This function is supposed to be used after the best execution plan have been + already chosen and the JOIN_TAB array for the best join order been already set. + For a given KEYUSE to access this JOIN_TAB in the best execution plan the + function checks whether it really can be used. The function first performs + the check with access_from_tables_is_allowed(). If it succeeds it checks + whether the keyuse->val does not use some fields of a materialized semijoin + nest that cannot be used to build keys to access outer tables. + Such KEYUSEs exists for the query like this: + select * from ot + where ot.c in (select it1.c from it1, it2 where it1.c=f(it2.c)) + Here we have two KEYUSEs to access table ot: with val=it1.c and val=f(it2.c). + However if the subquery was materialized the second KEYUSE cannot be employed + to access ot. + + @retval true the given keyuse can be used for ref access of this JOIN_TAB + @retval false otherwise +*/ + +bool JOIN_TAB::keyuse_is_valid_for_access_in_chosen_plan(JOIN *join, + KEYUSE *keyuse) +{ + if (!access_from_tables_is_allowed(keyuse->used_tables, + join->sjm_lookup_tables)) + return false; + if (join->sjm_scan_tables & table->map) + return true; + table_map keyuse_sjm_scan_tables= keyuse->used_tables & + join->sjm_scan_tables; + if (!keyuse_sjm_scan_tables) + return true; + uint sjm_tab_nr= 0; + while (!(keyuse_sjm_scan_tables & table_map(1) << sjm_tab_nr)) + sjm_tab_nr++; + JOIN_TAB *sjm_tab= join->map2table[sjm_tab_nr]; + TABLE_LIST *emb_sj_nest= sjm_tab->emb_sj_nest; + if (!(emb_sj_nest->sj_mat_info && emb_sj_nest->sj_mat_info->is_used && + emb_sj_nest->sj_mat_info->is_sj_scan)) + return true; + st_select_lex *sjm_sel= emb_sj_nest->sj_subq_pred->unit->first_select(); + for (uint i= 0; i < sjm_sel->item_list.elements; i++) + { + if (sjm_sel->ref_pointer_array[i] == keyuse->val) + return true; + } + return false; +} + + static uint cache_record_length(JOIN *join,uint idx) { @@ -7904,6 +7961,7 @@ static bool create_hj_key_for_table(JOIN *join, JOIN_TAB *join_tab, do { if (!(~used_tables & keyuse->used_tables) && + join_tab->keyuse_is_valid_for_access_in_chosen_plan(join, keyuse) && are_tables_local(join_tab, keyuse->used_tables)) { if (first_keyuse) @@ -7918,6 +7976,8 @@ static bool create_hj_key_for_table(JOIN *join, JOIN_TAB *join_tab, { if (curr->keypart == keyuse->keypart && !(~used_tables & curr->used_tables) && + join_tab->keyuse_is_valid_for_access_in_chosen_plan(join, + keyuse) && are_tables_local(join_tab, curr->used_tables)) break; } @@ -7951,6 +8011,7 @@ static bool create_hj_key_for_table(JOIN *join, JOIN_TAB *join_tab, do { if (!(~used_tables & keyuse->used_tables) && + join_tab->keyuse_is_valid_for_access_in_chosen_plan(join, keyuse) && are_tables_local(join_tab, keyuse->used_tables)) { bool add_key_part= TRUE; @@ -7960,7 +8021,9 @@ static bool create_hj_key_for_table(JOIN *join, JOIN_TAB *join_tab, { if (curr->keypart == keyuse->keypart && !(~used_tables & curr->used_tables) && - are_tables_local(join_tab, curr->used_tables)) + join_tab->keyuse_is_valid_for_access_in_chosen_plan(join, + curr) && + are_tables_local(join_tab, curr->used_tables)) { keyuse->keypart= NO_KEYPART; add_key_part= FALSE; @@ -8062,8 +8125,7 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, do { if (!(~used_tables & keyuse->used_tables) && - j->access_from_tables_is_allowed(keyuse->used_tables, - join->sjm_lookup_tables)) + j->keyuse_is_valid_for_access_in_chosen_plan(join, keyuse)) { if (are_tables_local(j, keyuse->val->used_tables())) { @@ -8132,8 +8194,7 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, for (i=0 ; i < keyparts ; keyuse++,i++) { while (((~used_tables) & keyuse->used_tables) || - !j->access_from_tables_is_allowed(keyuse->used_tables, - join->sjm_lookup_tables) || + !j->keyuse_is_valid_for_access_in_chosen_plan(join, keyuse) || keyuse->keypart == NO_KEYPART || (keyuse->keypart != (is_hash_join_key_no(key) ? -- cgit v1.2.1 From 151f4e9b4adea020fbe19b640016845dfa65d820 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Wed, 7 Jun 2017 16:29:55 -0700 Subject: Fixed the bug mdev-12963. This patch corrects the fix for bug mdev-7599. When the min/max optimization of the function opt_sum_query() optimizes away all tables of a subquery it should not ever be rolled back. --- sql/sql_select.cc | 1 + 1 file changed, 1 insertion(+) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 86ba0346366..f2be17f71d6 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1195,6 +1195,7 @@ JOIN::optimize() DBUG_PRINT("info",("Select tables optimized away")); zero_result_cause= "Select tables optimized away"; tables_list= 0; // All tables resolved + select_lex->min_max_opt_list.empty(); const_tables= top_join_tab_count= table_count; /* Extract all table-independent conditions and replace the WHERE -- cgit v1.2.1 From b850fc66ca72ed9b63cb8b899b64f8c555a525c7 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Wed, 7 Jun 2017 22:54:57 -0700 Subject: Fixed the bug mdev-12855. This is actually a legacy bug: SQL_SELECT::test_quick_select() was called with SQL_SELECT::head not set. It looks like that this problem can be reproduced only on queries with ORDER BY that use IN predicates converted to semi-joins. --- sql/sql_select.cc | 5 ++++- 1 file changed, 4 insertions(+), 1 deletion(-) (limited to 'sql/sql_select.cc') diff --git a/sql/sql_select.cc b/sql/sql_select.cc index f2be17f71d6..720c0a22681 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -2759,8 +2759,11 @@ JOIN::exec() if (sort_table_cond) { if (!curr_table->select) + { if (!(curr_table->select= new SQL_SELECT)) DBUG_VOID_RETURN; + curr_table->select->head= curr_table->table; + } if (!curr_table->select->cond) curr_table->select->cond= sort_table_cond; else @@ -2846,7 +2849,7 @@ JOIN::exec() curr_join->select_limit, (select_options & OPTION_FOUND_ROWS ? HA_POS_ERROR : unit->select_limit_cnt), - curr_join->group_list ? TRUE : FALSE)) + curr_join->group_list ? FALSE : TRUE)) DBUG_VOID_RETURN; sortorder= curr_join->sortorder; if (curr_join->const_tables != curr_join->table_count && -- cgit v1.2.1