- commits - lists.mariadb.org

[Commits] 2504263: MDEV-27159 Re-design the upper level of handling DML commands
by IgorBabaev 25 Mar '22

25 Mar '22

revision-id: 2504263fb920964b8ccaa2b19d8f48b8bddd0188 (mariadb-10.6.1-284-g2504263) parent(s): fc0842269bf1119aaee1cb49da9fb4b3167a61e2 author: Igor Babaev committer: Igor Babaev timestamp: 2022-03-25 10:05:00 -0700 message: MDEV-27159 Re-design the upper level of handling DML commands This is the second commit for the task. This patch allows to execute only single-table and multi-table DELETE statements using the method Sql_cmd_dml::execute(). The code that handles INSERT statements has not been touched. This patch still does not have the final changes to handle UPDATE/DELETE statements. All tests from the main suite passed. With --ps-protocol one test from opt_trace_security returns not the same result. This will be fixed soon. --- extra/wolfssl/wolfssl | 2 +- mysql-test/main/opt_trace.result | 14 +- sql/opt_range.cc | 2 +- sql/sql_base.cc | 7 +- sql/sql_cmd.h | 9 +- sql/sql_delete.cc | 577 ++++++++++++++++++++------------------- sql/sql_delete.h | 9 + sql/sql_lex.cc | 30 +- sql/sql_parse.cc | 133 +-------- sql/sql_parse.h | 1 + sql/sql_prepare.cc | 102 +------ sql/sql_select.cc | 1 - sql/sql_update.cc | 6 +- sql/sql_yacc.yy | 63 ++--- sql/table.h | 1 + 15 files changed, 374 insertions(+), 583 deletions(-) diff --git a/extra/wolfssl/wolfssl b/extra/wolfssl/wolfssl index c3513bf..9c87f97 160000 --- a/extra/wolfssl/wolfssl +++ b/extra/wolfssl/wolfssl @@ -1 +1 @@ -Subproject commit c3513bf2573c30f6d2df815de216120e92142020 +Subproject commit 9c87f979a7f1d3a6d786b260653d566c1d31a1c4 diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index 044db82..1444320 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -3742,6 +3742,16 @@ QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain delete from t0 where t0.a<3 { "steps": [ { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "expanded_query": "select from dual where t0.a < 3" + } + ] + } + }, + { "table": "t0", "range_analysis": { "table_scan": { @@ -3773,7 +3783,7 @@ explain delete from t0 where t0.a<3 { }, "group_index_range": { "chosen": false, - "cause": "no join" + "cause": "no group by or distinct" }, "chosen_range_access_summary": { "range_access_plan": { @@ -3816,7 +3826,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "select_id": 1, "steps": [ { - "expanded_query": "select NULL AS `NULL` from t0 join t1 where t0.a = t1.a and t1.a < 3" + "expanded_query": "select from t0 join t1 where t0.a = t1.a and t1.a < 3" } ] } diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 7909f5b..13457d7 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -11589,7 +11589,7 @@ ha_rows check_quick_select(PARAM *param, uint idx, bool index_only, Skip materialized derived table/view result table from MRR check as they aren't contain any data yet. */ - if (param->table->pos_in_table_list->is_non_derived()) + if (!param->table->pos_in_table_list->is_materialized_derived()) rows= file->multi_range_read_info_const(keynr, &seq_if, (void*)&seq, 0, bufsize, mrr_flags, cost); param->quick_rows[keynr]= rows; diff --git a/sql/sql_base.cc b/sql/sql_base.cc index a170aa0..3feeed5 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -1069,7 +1069,9 @@ TABLE_LIST* find_dup_table(THD *thd, TABLE_LIST *table, TABLE_LIST *table_list, */ if (table->table && thd->lex->sql_command != SQLCOM_UPDATE && - thd->lex->sql_command != SQLCOM_UPDATE_MULTI) + thd->lex->sql_command != SQLCOM_UPDATE_MULTI && + thd->lex->sql_command != SQLCOM_DELETE && + thd->lex->sql_command != SQLCOM_DELETE_MULTI) { /* All MyISAMMRG children are plain MyISAM tables. */ DBUG_ASSERT(table->table->file->ht->db_type != DB_TYPE_MRG_MYISAM); @@ -7609,6 +7611,9 @@ int setup_wild(THD *thd, TABLE_LIST *tables, List<Item> &fields, if (!select_lex->with_wild) DBUG_RETURN(0); + if (!fields.elements) + DBUG_RETURN(0); + /* Don't use arena if we are not in prepared statements or stored procedures For PS/SP we have to use arena to remember the changes diff --git a/sql/sql_cmd.h b/sql/sql_cmd.h index c62fe83..2c069cf 100644 --- a/sql/sql_cmd.h +++ b/sql/sql_cmd.h @@ -262,6 +262,7 @@ class LEX; class select_result; class Prelocking_strategy; class DML_prelocking_strategy; +class Protocol; class Sql_cmd_dml : public Sql_cmd { @@ -289,7 +290,9 @@ class Sql_cmd_dml : public Sql_cmd protected: Sql_cmd_dml() - : Sql_cmd(), lex(nullptr), result(nullptr), m_empty_query(false) {} + : Sql_cmd(), lex(nullptr), result(nullptr), + m_empty_query(false), save_protocol(NULL) + {} /// @return true if query is guaranteed to return no data /** @@ -347,12 +350,14 @@ class Sql_cmd_dml : public Sql_cmd virtual DML_prelocking_strategy *get_dml_prelocking_strategy() = 0; - uint table_count; + uint table_count; protected: LEX *lex; ///< Pointer to LEX for this statement select_result *result; ///< Pointer to object for handling of the result bool m_empty_query; ///< True if query will produce no rows + List<Item> empty_list; + Protocol *save_protocol; }; diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc index 95adf17..7ac4797 100644 --- a/sql/sql_delete.cc +++ b/sql/sql_delete.cc @@ -103,7 +103,7 @@ bool Update_plan::save_explain_data_intern(MEM_ROOT *mem_root, bool is_analyze) { explain->select_type= "SIMPLE"; - explain->table_name.append(&table->pos_in_table_list->alias); + explain->table_name.append(table->alias); explain->impossible_where= false; explain->no_partitions= false; @@ -294,124 +294,79 @@ int TABLE::delete_row() } -/** - Implement DELETE SQL word. - - @note Like implementations of other DDL/DML in MySQL, this function - relies on the caller to close the thread tables. This is done in the - end of dispatch_command(). -*/ - -bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds, - SQL_I_List<ORDER> *order_list, ha_rows limit, - ulonglong options, select_result *result) +bool Sql_cmd_delete::delete_from_single_table(THD *thd) { - bool will_batch= FALSE; - int error, loc_error; - TABLE *table; - SQL_SELECT *select=0; - SORT_INFO *file_sort= 0; - READ_RECORD info; - bool using_limit=limit != HA_POS_ERROR; - bool transactional_table, safe_update, const_cond; - bool const_cond_result; - bool return_error= 0; - ha_rows deleted= 0; - bool reverse= FALSE; - bool has_triggers= false; - ORDER *order= (ORDER *) ((order_list && order_list->elements) ? - order_list->first : NULL); - SELECT_LEX *select_lex= thd->lex->first_select_lex(); - SELECT_LEX *returning= thd->lex->has_returning() ? thd->lex->returning() : 0; + int error; + int loc_error; + bool transactional_table; + bool const_cond; + bool safe_update; + bool const_cond_result; + bool return_error= 0; + TABLE *table; + SQL_SELECT *select= 0; + SORT_INFO *file_sort= 0; + READ_RECORD info; + ha_rows deleted= 0; + bool reverse= FALSE; + bool binlog_is_row; killed_state killed_status= NOT_KILLED; THD::enum_binlog_query_type query_type= THD::ROW_QUERY_TYPE; - bool binlog_is_row; - Explain_delete *explain; + bool will_batch= FALSE; + + bool has_triggers= false; + SELECT_LEX_UNIT *unit = &lex->unit; + SELECT_LEX *select_lex= unit->first_select(); + SELECT_LEX *returning= thd->lex->has_returning() ? thd->lex->returning() : 0; + TABLE_LIST *const table_list = select_lex->get_table_list(); + ulonglong options= select_lex->options; + ORDER *order= select_lex->order_list.first; + COND *conds= select_lex->join->conds; + ha_rows limit= unit->lim.get_select_limit(); + bool using_limit= limit != HA_POS_ERROR; + Delete_plan query_plan(thd->mem_root); + Explain_delete *explain; Unique * deltempfile= NULL; bool delete_record= false; - bool delete_while_scanning; + bool delete_while_scanning= table_list->delete_while_scanning; bool portion_of_time_through_update; - DBUG_ENTER("mysql_delete"); + + DBUG_ENTER("Sql_cmd_delete::delete_single_table"); query_plan.index= MAX_KEY; query_plan.using_filesort= FALSE; - create_explain_query(thd->lex, thd->mem_root); - if (open_and_lock_tables(thd, table_list, TRUE, 0)) - DBUG_RETURN(TRUE); - THD_STAGE_INFO(thd, stage_init_update); + create_explain_query(thd->lex, thd->mem_root); const bool delete_history= table_list->vers_conditions.delete_history; DBUG_ASSERT(!(delete_history && table_list->period_conditions.is_set())); - if (thd->lex->handle_list_of_derived(table_list, DT_MERGE_FOR_INSERT)) - DBUG_RETURN(TRUE); - if (thd->lex->handle_list_of_derived(table_list, DT_PREPARE)) - DBUG_RETURN(TRUE); + if (table_list->handle_derived(thd->lex, DT_MERGE_FOR_INSERT)) + DBUG_RETURN(1); + if (table_list->handle_derived(thd->lex, DT_PREPARE)) + DBUG_RETURN(1); + + table= table_list->table; if (!table_list->single_table_updatable()) { my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias.str, "DELETE"); DBUG_RETURN(TRUE); } - if (!(table= table_list->table) || !table->is_created()) + + if (!table || !table->is_created()) { my_error(ER_VIEW_DELETE_MERGE_VIEW, MYF(0), table_list->view_db.str, table_list->view_name.str); DBUG_RETURN(TRUE); } - table->map=1; + query_plan.select_lex= thd->lex->first_select_lex(); query_plan.table= table; - thd->lex->promote_select_describe_flag_if_needed(); - if (mysql_prepare_delete(thd, table_list, &conds, &delete_while_scanning)) - DBUG_RETURN(TRUE); - - if (table_list->has_period()) - { - if (!table_list->period_conditions.start.item->const_item() - || !table_list->period_conditions.end.item->const_item()) - { - my_error(ER_NOT_CONSTANT_EXPRESSION, MYF(0), "FOR PORTION OF"); - DBUG_RETURN(true); - } - } - - if (delete_history) - table->vers_write= false; - - if (returning) - (void) result->prepare(returning->item_list, NULL); - - if (thd->lex->current_select->first_cond_optimization) - { - thd->lex->current_select->save_leaf_tables(thd); - thd->lex->current_select->first_cond_optimization= 0; - } - /* check ORDER BY even if it can be ignored */ - if (order) - { - TABLE_LIST tables; - List<Item> fields; - List<Item> all_fields; - - bzero((char*) &tables,sizeof(tables)); - tables.table = table; - tables.alias = table_list->alias; - - if (select_lex->setup_ref_array(thd, order_list->elements) || - setup_order(thd, select_lex->ref_pointer_array, &tables, - fields, all_fields, order)) - { - free_underlaid_joins(thd, thd->lex->first_select_lex()); - DBUG_RETURN(TRUE); - } - } - /* Apply the IN=>EXISTS transformation to all subqueries and optimize them. */ if (select_lex->optimize_unflattened_subqueries(false)) DBUG_RETURN(TRUE); @@ -519,7 +474,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds, table->covering_keys.clear_all(); table->opt_range_keys.clear_all(); - select=make_select(table, 0, 0, conds, (SORT_INFO*) 0, 0, &error); + select= make_select(table, 0, 0, conds, (SORT_INFO*) 0, 0, &error); if (unlikely(error)) DBUG_RETURN(TRUE); if ((select && select->check_quick(thd, safe_update, limit)) || !limit) @@ -953,7 +908,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds, } } DBUG_ASSERT(transactional_table || !deleted || thd->transaction->stmt.modified_non_trans_table); - + if (likely(error < 0) || (thd->lex->ignore && !thd->is_error() && !thd->is_fatal_error)) { @@ -1003,90 +958,6 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds, } -/* - Prepare items in DELETE statement - - SYNOPSIS - mysql_prepare_delete() - thd - thread handler - table_list - global/local table list - conds - conditions - - RETURN VALUE - FALSE OK - TRUE error -*/ -int mysql_prepare_delete(THD *thd, TABLE_LIST *table_list, Item **conds, - bool *delete_while_scanning) -{ - Item *fake_conds= 0; - SELECT_LEX *select_lex= thd->lex->first_select_lex(); - DBUG_ENTER("mysql_prepare_delete"); - List<Item> all_fields; - - *delete_while_scanning= true; - thd->lex->allow_sum_func.clear_all(); - if (setup_tables_and_check_access(thd, &select_lex->context, - &select_lex->top_join_list, table_list, - select_lex->leaf_tables, FALSE, - DELETE_ACL, SELECT_ACL, TRUE)) - DBUG_RETURN(TRUE); - - if (table_list->vers_conditions.is_set() && table_list->is_view_or_derived()) - { - my_error(ER_IT_IS_A_VIEW, MYF(0), table_list->table_name.str); - DBUG_RETURN(true); - } - - if (table_list->has_period()) - { - if (table_list->is_view_or_derived()) - { - my_error(ER_IT_IS_A_VIEW, MYF(0), table_list->table_name.str); - DBUG_RETURN(true); - } - - if (select_lex->period_setup_conds(thd, table_list)) - DBUG_RETURN(true); - } - - DBUG_ASSERT(table_list->table); - // conds could be cached from previous SP call - DBUG_ASSERT(!table_list->vers_conditions.need_setup() || - !*conds || thd->stmt_arena->is_stmt_execute()); - if (select_lex->vers_setup_conds(thd, table_list)) - DBUG_RETURN(TRUE); - - *conds= select_lex->where; - - if (setup_returning_fields(thd, table_list) || - setup_conds(thd, table_list, select_lex->leaf_tables, conds) || - setup_ftfuncs(select_lex)) - DBUG_RETURN(TRUE); - if (!table_list->single_table_updatable() || - check_key_in_view(thd, table_list)) - { - my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias.str, "DELETE"); - DBUG_RETURN(TRUE); - } - - /* - Application-time periods: if FOR PORTION OF ... syntax used, DELETE - statement could issue delete_row's mixed with write_row's. This causes - problems for myisam and corrupts table, if deleting while scanning. - */ - if (table_list->has_period() - || unique_table(thd, table_list, table_list->next_global, 0)) - *delete_while_scanning= false; - - if (select_lex->inner_refs_list.elements && - fix_inner_refs(thd, all_fields, select_lex, select_lex->ref_pointer_array)) - DBUG_RETURN(TRUE); - - select_lex->fix_prepare_information(thd, conds, &fake_conds); - DBUG_RETURN(FALSE); -} - /*************************************************************************** Delete multiple tables from join @@ -1099,106 +970,6 @@ extern "C" int refpos_order_cmp(void* arg, const void *a,const void *b) return file->cmp_ref((const uchar*)a, (const uchar*)b); } -/* - make delete specific preparation and checks after opening tables - - SYNOPSIS - mysql_multi_delete_prepare() - thd thread handler - - RETURN - FALSE OK - TRUE Error -*/ - -int mysql_multi_delete_prepare(THD *thd) -{ - LEX *lex= thd->lex; - TABLE_LIST *aux_tables= lex->auxiliary_table_list.first; - TABLE_LIST *target_tbl; - DBUG_ENTER("mysql_multi_delete_prepare"); - - if (mysql_handle_derived(lex, DT_INIT)) - DBUG_RETURN(TRUE); - if (mysql_handle_derived(lex, DT_MERGE_FOR_INSERT)) - DBUG_RETURN(TRUE); - if (mysql_handle_derived(lex, DT_PREPARE)) - DBUG_RETURN(TRUE); - /* - setup_tables() need for VIEWs. JOIN::prepare() will not do it second - time. - - lex->query_tables also point on local list of DELETE SELECT_LEX - */ - if (setup_tables_and_check_access(thd, - &thd->lex->first_select_lex()->context, - &thd->lex->first_select_lex()-> - top_join_list, - lex->query_tables, - lex->first_select_lex()->leaf_tables, - FALSE, DELETE_ACL, SELECT_ACL, FALSE)) - DBUG_RETURN(TRUE); - - /* - Multi-delete can't be constructed over-union => we always have - single SELECT on top and have to check underlying SELECTs of it - */ - lex->first_select_lex()->set_unique_exclude(); - /* Fix tables-to-be-deleted-from list to point at opened tables */ - for (target_tbl= (TABLE_LIST*) aux_tables; - target_tbl; - target_tbl= target_tbl->next_local) - { - - target_tbl->table= target_tbl->correspondent_table->table; - if (target_tbl->correspondent_table->is_multitable()) - { - my_error(ER_VIEW_DELETE_MERGE_VIEW, MYF(0), - target_tbl->correspondent_table->view_db.str, - target_tbl->correspondent_table->view_name.str); - DBUG_RETURN(TRUE); - } - - if (!target_tbl->correspondent_table->single_table_updatable() || - check_key_in_view(thd, target_tbl->correspondent_table)) - { - my_error(ER_NON_UPDATABLE_TABLE, MYF(0), - target_tbl->table_name.str, "DELETE"); - DBUG_RETURN(TRUE); - } - } - - for (target_tbl= (TABLE_LIST*) aux_tables; - target_tbl; - target_tbl= target_tbl->next_local) - { - /* - Check that table from which we delete is not used somewhere - inside subqueries/view. - */ - { - TABLE_LIST *duplicate; - if ((duplicate= unique_table(thd, target_tbl->correspondent_table, - lex->query_tables, 0))) - { - update_non_unique_table_error(target_tbl->correspondent_table, - "DELETE", duplicate); - DBUG_RETURN(TRUE); - } - } - } - /* - Reset the exclude flag to false so it doesn't interfare - with further calls to unique_table - */ - lex->first_select_lex()->exclude_from_table_unique_test= FALSE; - - if (lex->save_prep_leaf_tables()) - DBUG_RETURN(TRUE); - - DBUG_RETURN(FALSE); -} - multi_delete::multi_delete(THD *thd_arg, TABLE_LIST *dt, uint num_of_tables_arg): select_result_interceptor(thd_arg), delete_tables(dt), deleted(0), found(0), @@ -1647,3 +1418,261 @@ bool multi_delete::send_eof() } return 0; } + + +bool Sql_cmd_delete::precheck(THD *thd) +{ + if (!multitable) + { + if (delete_precheck(thd, lex->query_tables)) + return true; + } + else + { + if (multi_delete_precheck(thd, lex->query_tables)) + return true; + } + return false; +} + + +bool Sql_cmd_delete::prepare_inner(THD *thd) +{ + int err= 0; + TABLE_LIST *target_tbl; + JOIN *join; + SELECT_LEX *const select_lex = thd->lex->first_select_lex(); + TABLE_LIST *const table_list = select_lex->get_table_list(); + TABLE_LIST *aux_tables= thd->lex->auxiliary_table_list.first; + ulonglong select_options= select_lex->options; + bool free_join= 1; + SELECT_LEX *returning= thd->lex->has_returning() ? thd->lex->returning() : 0; + + DBUG_ENTER("Sql_cmd_delete::prepare_inner"); + + (void) read_statistics_for_tables_if_needed(thd, table_list); + + { + if (mysql_handle_derived(lex, DT_INIT)) + DBUG_RETURN(TRUE); + if (mysql_handle_derived(lex, DT_MERGE_FOR_INSERT)) + DBUG_RETURN(TRUE); + if (mysql_handle_derived(lex, DT_PREPARE)) + DBUG_RETURN(TRUE); + } + + if (!(result= new (thd->mem_root) multi_delete(thd, aux_tables, + lex->table_count))) + { + DBUG_RETURN(TRUE); + } + + table_list->delete_while_scanning= true; + + if (setup_tables_and_check_access(thd, &select_lex->context, + &select_lex->top_join_list, + table_list, select_lex->leaf_tables, + false, DELETE_ACL, SELECT_ACL, true)) + DBUG_RETURN(TRUE); + + if (setup_tables(thd, &select_lex->context, &select_lex->top_join_list, + table_list, select_lex->leaf_tables, false, false)) + DBUG_RETURN(TRUE); + + if (!multitable) + { + if (table_list->vers_conditions.is_set() && table_list->is_view_or_derived()) + { + my_error(ER_IT_IS_A_VIEW, MYF(0), table_list->table_name.str); + DBUG_RETURN(true); + } + + if (table_list->has_period()) + { + if (table_list->is_view_or_derived()) + { + my_error(ER_IT_IS_A_VIEW, MYF(0), table_list->table_name.str); + DBUG_RETURN(true); + } + + if (select_lex->period_setup_conds(thd, table_list)) + DBUG_RETURN(true); + } + + if (select_lex->vers_setup_conds(thd, table_list)) + DBUG_RETURN(TRUE); + /* + Application-time periods: if FOR PORTION OF ... syntax used, DELETE + statement could issue delete_row's mixed with write_row's. This causes + problems for myisam and corrupts table, if deleting while scanning. + */ + if (table_list->has_period() + || unique_table(thd, table_list, table_list->next_global, 0)) + table_list->delete_while_scanning= false; + } + + if (multitable) + { + /* + Multi-delete can't be constructed over-union => we always have + single SELECT on top and have to check underlying SELECTs of it + */ + lex->first_select_lex()->set_unique_exclude(); + /* Fix tables-to-be-deleted-from list to point at opened tables */ + for (target_tbl= (TABLE_LIST*) aux_tables; + target_tbl; + target_tbl= target_tbl->next_local) + { + target_tbl->table= target_tbl->correspondent_table->table; + if (target_tbl->correspondent_table->is_multitable()) + { + my_error(ER_VIEW_DELETE_MERGE_VIEW, MYF(0), + target_tbl->correspondent_table->view_db.str, + target_tbl->correspondent_table->view_name.str); + DBUG_RETURN(TRUE); + } + + if (!target_tbl->correspondent_table->single_table_updatable() || + check_key_in_view(thd, target_tbl->correspondent_table)) + { + my_error(ER_NON_UPDATABLE_TABLE, MYF(0), + target_tbl->table_name.str, "DELETE"); + DBUG_RETURN(TRUE); + } + } + + for (target_tbl= (TABLE_LIST*) aux_tables; + target_tbl; + target_tbl= target_tbl->next_local) + { + /* + Check that table from which we delete is not used somewhere + inside subqueries/view. + */ + { + TABLE_LIST *duplicate; + if ((duplicate= unique_table(thd, target_tbl->correspondent_table, + lex->query_tables, 0))) + { + update_non_unique_table_error(target_tbl->correspondent_table, + "DELETE", duplicate); + DBUG_RETURN(TRUE); + } + } + } + /* + Reset the exclude flag to false so it doesn't interfare + with further calls to unique_table + */ + lex->first_select_lex()->exclude_from_table_unique_test= FALSE; + } + + { + if (thd->lex->describe) + select_options|= SELECT_DESCRIBE; + + /* + When in EXPLAIN, delay deleting the joins so that they are still + available when we're producing EXPLAIN EXTENDED warning text. + */ + if (select_options & SELECT_DESCRIBE) + free_join= 0; + select_options|= + SELECT_NO_JOIN_CACHE | SELECT_NO_UNLOCK | OPTION_SETUP_TABLES_DONE; + + if (!(join= new (thd->mem_root) JOIN(thd, empty_list, + select_options, result))) + DBUG_RETURN(TRUE); + THD_STAGE_INFO(thd, stage_init); + select_lex->join= join; + thd->lex->used_tables=0; + if ((err= join->prepare(table_list, select_lex->where, + select_lex->order_list.elements, + select_lex->order_list.first, + false, NULL, NULL, NULL, + select_lex, &lex->unit))) + + { + goto err; + } + + } + + + if (setup_returning_fields(thd, table_list) || + setup_ftfuncs(select_lex)) + goto err; + + free_join= false; + + if (returning) + (void) result->prepare(returning->item_list, NULL); + +err: + + if (free_join) + { + THD_STAGE_INFO(thd, stage_end); + err|= (int)(select_lex->cleanup()); + DBUG_RETURN(err || thd->is_error()); + } + DBUG_RETURN(err); + +} + +bool Sql_cmd_delete::execute_inner(THD *thd) +{ + if (!multitable) + { + if (lex->has_returning()) + { + select_result *sel_result= NULL; + delete result; + /* This is DELETE ... RETURNING. It will return output to the client */ + if (thd->lex->analyze_stmt) + { + /* + Actually, it is ANALYZE .. DELETE .. RETURNING. We need to produce + output and then discard it. + */ + sel_result= new (thd->mem_root) select_send_analyze(thd); + save_protocol= thd->protocol; + thd->protocol= new Protocol_discard(thd); + } + else + { + if (!lex->result && !(sel_result= new (thd->mem_root) select_send(thd))) + return true; + } + result= lex->result ? lex->result : sel_result; + } + } + + bool res= multitable ? Sql_cmd_dml::execute_inner(thd) + : delete_from_single_table(thd); + + res|= thd->is_error(); + + if (save_protocol) + { + delete thd->protocol; + thd->protocol= save_protocol; + } + { + if (unlikely(res)) + result->abort_result_set(); + else + { + if (thd->lex->describe || thd->lex->analyze_stmt) + res= thd->lex->explain->send_explain(thd); + } + } + + if (result) + { + res= false; + delete result; + } + + return res; +} diff --git a/sql/sql_delete.h b/sql/sql_delete.h index dabcafb..4aee510 100644 --- a/sql/sql_delete.h +++ b/sql/sql_delete.h @@ -17,6 +17,9 @@ #define SQL_DELETE_INCLUDED #include "my_base.h" /* ha_rows */ +#include "sql_class.h" /* enum_duplicates */ +#include "sql_cmd.h" // Sql_cmd_dml +#include "sql_base.h" class THD; struct TABLE_LIST; @@ -43,6 +46,11 @@ class Sql_cmd_delete final : public Sql_cmd_dml return multitable ? SQLCOM_DELETE_MULTI : SQLCOM_DELETE; } + DML_prelocking_strategy *get_dml_prelocking_strategy() + { + return &dml_prelocking_strategy; + } + protected: bool precheck(THD *thd) override; @@ -55,5 +63,6 @@ class Sql_cmd_delete final : public Sql_cmd_dml bool multitable; + DML_prelocking_strategy dml_prelocking_strategy; }; #endif /* SQL_DELETE_INCLUDED */ diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 5efc4a1..accefa8 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -3328,34 +3328,6 @@ void st_select_lex_unit::exclude_level() } -#if 0 -/* - Exclude subtree of current unit from tree of SELECTs - - SYNOPSYS - st_select_lex_unit::exclude_tree() -*/ -void st_select_lex_unit::exclude_tree() -{ - for (SELECT_LEX *sl= first_select(); sl; sl= sl->next_select()) - { - // unlink current level from global SELECTs list - if (sl->link_prev && (*sl->link_prev= sl->link_next)) - sl->link_next->link_prev= sl->link_prev; - - // unlink underlay levels - for (SELECT_LEX_UNIT *u= sl->first_inner_unit(); u; u= u->next_unit()) - { - u->exclude_level(); - } - } - // exclude currect unit from list of nodes - (*prev)= next; - if (next) - next->prev= prev; -} -#endif - /* st_select_lex_node::mark_as_dependent mark all st_select_lex struct from @@ -3577,7 +3549,7 @@ bool st_select_lex::setup_ref_array(THD *thd, uint order_group_num) select_n_where_fields + order_group_num + hidden_bit_fields + - fields_in_window_functions) * (size_t) 5; + fields_in_window_functions + 1) * (size_t) 5; DBUG_ASSERT(n_elems % 5 == 0); if (!ref_pointer_array.is_null()) { diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index fd33455..028c6a8 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -4379,6 +4379,8 @@ mysql_execute_command(THD *thd, bool is_called_from_prepared_stmt) } case SQLCOM_UPDATE: case SQLCOM_UPDATE_MULTI: + case SQLCOM_DELETE: + case SQLCOM_DELETE_MULTI: { DBUG_ASSERT(first_table == all_tables && first_table != 0); DBUG_ASSERT(lex->m_sql_cmd != NULL); @@ -4646,129 +4648,6 @@ mysql_execute_command(THD *thd, bool is_called_from_prepared_stmt) break; } - case SQLCOM_DELETE: - { - WSREP_SYNC_WAIT(thd, WSREP_SYNC_WAIT_BEFORE_UPDATE_DELETE); - select_result *sel_result= NULL; - DBUG_ASSERT(first_table == all_tables && first_table != 0); - WSREP_SYNC_WAIT(thd, WSREP_SYNC_WAIT_BEFORE_UPDATE_DELETE); - - if ((res= delete_precheck(thd, all_tables))) - break; - DBUG_ASSERT(select_lex->limit_params.offset_limit == 0); - unit->set_limit(select_lex); - - MYSQL_DELETE_START(thd->query()); - Protocol *save_protocol= NULL; - - if (lex->has_returning()) - { - /* This is DELETE ... RETURNING. It will return output to the client */ - if (thd->lex->analyze_stmt) - { - /* - Actually, it is ANALYZE .. DELETE .. RETURNING. We need to produce - output and then discard it. - */ - sel_result= new (thd->mem_root) select_send_analyze(thd); - save_protocol= thd->protocol; - thd->protocol= new Protocol_discard(thd); - } - else - { - if (!lex->result && !(sel_result= new (thd->mem_root) select_send(thd))) - goto error; - } - } - - res = mysql_delete(thd, all_tables, - select_lex->where, &select_lex->order_list, - unit->lim.get_select_limit(), select_lex->options, - lex->result ? lex->result : sel_result); - - if (save_protocol) - { - delete thd->protocol; - thd->protocol= save_protocol; - } - - if (thd->lex->analyze_stmt || thd->lex->describe) - { - if (!res) - res= thd->lex->explain->send_explain(thd); - } - - delete sel_result; - MYSQL_DELETE_DONE(res, (ulong) thd->get_row_count_func()); - break; - } - case SQLCOM_DELETE_MULTI: - { - WSREP_SYNC_WAIT(thd, WSREP_SYNC_WAIT_BEFORE_UPDATE_DELETE); - DBUG_ASSERT(first_table == all_tables && first_table != 0); - TABLE_LIST *aux_tables= thd->lex->auxiliary_table_list.first; - multi_delete *result; - WSREP_SYNC_WAIT(thd, WSREP_SYNC_WAIT_BEFORE_UPDATE_DELETE); - - if ((res= multi_delete_precheck(thd, all_tables))) - break; - - /* condition will be TRUE on SP re-excuting */ - if (select_lex->item_list.elements != 0) - select_lex->item_list.empty(); - if (add_item_to_list(thd, new (thd->mem_root) Item_null(thd))) - goto error; - - THD_STAGE_INFO(thd, stage_init); - if ((res= open_and_lock_tables(thd, all_tables, TRUE, 0))) - break; - - MYSQL_MULTI_DELETE_START(thd->query()); - if (unlikely(res= mysql_multi_delete_prepare(thd))) - { - MYSQL_MULTI_DELETE_DONE(1, 0); - goto error; - } - - if (likely(!thd->is_fatal_error)) - { - result= new (thd->mem_root) multi_delete(thd, aux_tables, - lex->table_count); - if (likely(result)) - { - if (unlikely(select_lex->vers_setup_conds(thd, aux_tables))) - goto multi_delete_error; - res= mysql_select(thd, - select_lex->get_table_list(), - select_lex->item_list, - select_lex->where, - 0, (ORDER *)NULL, (ORDER *)NULL, (Item *)NULL, - (ORDER *)NULL, - (select_lex->options | thd->variables.option_bits | - SELECT_NO_JOIN_CACHE | SELECT_NO_UNLOCK | - OPTION_SETUP_TABLES_DONE) & ~OPTION_BUFFER_RESULT, - result, unit, select_lex); - res|= (int)(thd->is_error()); - - MYSQL_MULTI_DELETE_DONE(res, result->num_deleted()); - if (res) - result->abort_result_set(); /* for both DELETE and EXPLAIN DELETE */ - else - { - if (lex->describe || lex->analyze_stmt) - res= thd->lex->explain->send_explain(thd); - } - multi_delete_error: - delete result; - } - } - else - { - res= TRUE; // Error - MYSQL_MULTI_DELETE_DONE(1, 0); - } - break; - } case SQLCOM_DROP_SEQUENCE: case SQLCOM_DROP_TABLE: { @@ -7643,12 +7522,16 @@ void create_select_for_variable(THD *thd, LEX_CSTRING *var_name) } -void mysql_init_multi_delete(LEX *lex) +void mysql_init_delete(LEX *lex) { - lex->sql_command= SQLCOM_DELETE_MULTI; mysql_init_select(lex); lex->first_select_lex()->limit_params.clear(); lex->unit.lim.clear(); +} + +void mysql_init_multi_delete(LEX *lex) +{ + lex->sql_command= SQLCOM_DELETE_MULTI; lex->first_select_lex()->table_list. save_and_clear(&lex->auxiliary_table_list); lex->query_tables= 0; diff --git a/sql/sql_parse.h b/sql/sql_parse.h index ebe3fe9..45cd15c 100644 --- a/sql/sql_parse.h +++ b/sql/sql_parse.h @@ -95,6 +95,7 @@ void mysql_parse(THD *thd, char *rawbuf, uint length, bool mysql_new_select(LEX *lex, bool move_down, SELECT_LEX *sel); void create_select_for_variable(THD *thd, LEX_CSTRING *var_name); void create_table_set_open_action_and_adjust_tables(LEX *lex); +void mysql_init_delete(LEX *lex); void mysql_init_multi_delete(LEX *lex); bool multi_delete_set_locks_and_link_aux_tables(LEX *lex); void create_table_set_open_action_and_adjust_tables(LEX *lex); diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index 1d26add..72550b1 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -95,7 +95,6 @@ When one supplies long data for a placeholder: #include "sql_base.h" // open_normal_and_derived_tables #include "sql_cache.h" // query_cache_* #include "sql_view.h" // create_view_precheck -#include "sql_delete.h" // mysql_prepare_delete #include "sql_select.h" // for JOIN #include "sql_insert.h" // upgrade_lock_type_for_insert, mysql_prepare_insert #include "sql_db.h" // mysql_opt_change_db, mysql_change_db @@ -1398,56 +1397,6 @@ static bool mysql_test_insert(Prepared_statement *stmt, } -/** - Validate DELETE statement. - - @param stmt prepared statement - @param tables list of tables used in this query - - @retval - FALSE success - @retval - TRUE error, error message is set in THD -*/ - -static bool mysql_test_delete(Prepared_statement *stmt, - TABLE_LIST *table_list) -{ - uint table_count= 0; - THD *thd= stmt->thd; - LEX *lex= stmt->lex; - bool delete_while_scanning; - DBUG_ENTER("mysql_test_delete"); - - if (delete_precheck(thd, table_list) || - open_tables(thd, &table_list, &table_count, MYSQL_OPEN_FORCE_SHARED_MDL)) - goto error; - - if (mysql_handle_derived(thd->lex, DT_INIT)) - goto error; - if (mysql_handle_derived(thd->lex, DT_MERGE_FOR_INSERT)) - goto error; - if (mysql_handle_derived(thd->lex, DT_PREPARE)) - goto error; - - if (!table_list->single_table_updatable()) - { - my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias.str, "DELETE"); - goto error; - } - if (!table_list->table || !table_list->table->is_created()) - { - my_error(ER_VIEW_DELETE_MERGE_VIEW, MYF(0), - table_list->view_db.str, table_list->view_name.str); - goto error; - } - - DBUG_RETURN(mysql_prepare_delete(thd, table_list, - &lex->first_select_lex()->where, - &delete_while_scanning)); -error: - DBUG_RETURN(TRUE); -} /** @@ -2031,48 +1980,6 @@ static bool mysql_test_create_view(Prepared_statement *stmt) /** - Validate and prepare for execution a multi delete statement. - - @param stmt prepared statement - @param tables list of tables used in this query - - @retval - FALSE success - @retval - TRUE error, error message in THD is set. -*/ - -static bool mysql_test_multidelete(Prepared_statement *stmt, - TABLE_LIST *tables) -{ - THD *thd= stmt->thd; - - thd->lex->current_select= thd->lex->first_select_lex(); - if (add_item_to_list(thd, new (thd->mem_root) - Item_null(thd))) - { - my_error(ER_OUTOFMEMORY, MYF(ME_FATAL), 0); - goto error; - } - - if (multi_delete_precheck(thd, tables) || - select_like_stmt_test_with_open(stmt, tables, - &mysql_multi_delete_prepare, - OPTION_SETUP_TABLES_DONE)) - goto error; - if (!tables->table) - { - my_error(ER_VIEW_DELETE_MERGE_VIEW, MYF(0), - tables->view_db.str, tables->view_name.str); - goto error; - } - return FALSE; -error: - return TRUE; -} - - -/** Wrapper for mysql_insert_select_prepare, to make change of local tables after open_normal_and_derived_tables() call. @@ -2350,14 +2257,13 @@ static bool check_prepared_statement(Prepared_statement *stmt) case SQLCOM_UPDATE: case SQLCOM_UPDATE_MULTI: + case SQLCOM_DELETE: + case SQLCOM_DELETE_MULTI: res = lex->m_sql_cmd->prepare(thd); if (!res) lex->m_sql_cmd->unprepare(thd); break; - case SQLCOM_DELETE: - res= mysql_test_delete(stmt, tables); - break; /* The following allow WHERE clause, so they must be tested like SELECT */ case SQLCOM_SHOW_DATABASES: case SQLCOM_SHOW_TABLES: @@ -2494,10 +2400,6 @@ static bool check_prepared_statement(Prepared_statement *stmt) res= mysql_test_set_fields(stmt, tables, &lex->var_list); break; - case SQLCOM_DELETE_MULTI: - res= mysql_test_multidelete(stmt, tables); - break; - case SQLCOM_INSERT_SELECT: case SQLCOM_REPLACE_SELECT: res= mysql_test_insert_select(stmt, tables); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index e46028c..50d8ccd 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -30336,7 +30336,6 @@ bool Sql_cmd_dml::execute(THD *thd) THD_STAGE_INFO(thd, stage_init); - DBUG_ASSERT(!lex->is_query_tables_locked()); /* Locking of tables is done after preparation but before optimization. This allows to do better partition pruning and avoid locking unused diff --git a/sql/sql_update.cc b/sql/sql_update.cc index bc66612..2d2a1d4 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -3024,12 +3024,10 @@ bool Sql_cmd_update::prepare_inner(THD *thd) { JOIN *join; int err= 0; - // uint table_cnt= 0; SELECT_LEX *const select_lex = thd->lex->first_select_lex(); TABLE_LIST *const table_list = select_lex->get_table_list(); ulonglong select_options= select_lex->options; bool free_join= 1; - // bool orig_multitable= multitable; DBUG_ENTER("Sql_cmd_update::prepare_inner"); if (!multitable) @@ -3083,8 +3081,8 @@ bool Sql_cmd_update::prepare_inner(THD *thd) DBUG_RETURN(TRUE); } - if (((multi_update *)result)->init(thd)) - DBUG_RETURN(TRUE); + if (((multi_update *)result)->init(thd)) + DBUG_RETURN(TRUE); if (setup_tables(thd, &select_lex->context, &select_lex->top_join_list, table_list, select_lex->leaf_tables, false, false)) diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 036cab9..c02ac8a 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -70,6 +70,7 @@ #include "sql_type_json.h" #include "json_table.h" #include "sql_update.h" +#include "sql_delete.h" /* this is to get the bison compilation windows warnings out */ #ifdef _MSC_VER @@ -1675,7 +1676,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); opt_mi_check_type opt_to mi_check_types table_to_table_list table_to_table opt_table_list opt_as handler_rkey_function handler_read_or_scan - single_multi table_wild_list table_wild_one opt_wild + single_multi opt_wild opt_and select_var_list select_var_list_init help opt_extended_describe shutdown @@ -13273,12 +13274,11 @@ delete: DELETE_SYM { LEX *lex= Lex; - lex->sql_command= SQLCOM_DELETE; YYPS->m_lock_type= TL_WRITE_DEFAULT; YYPS->m_mdl_type= MDL_SHARED_WRITE; if (Lex->main_select_push()) MYSQL_YYABORT; - mysql_init_select(lex); + mysql_init_delete(lex); lex->ignore= 0; lex->first_select_lex()->order_list.empty(); } @@ -13339,12 +13339,22 @@ single_multi: delete_limit_clause opt_returning { + LEX *lex= Lex; if ($3) Select->order_list= *($3); - Lex->pop_select(); //main select + lex->pop_select(); //main select + lex->sql_command= SQLCOM_DELETE; + if (!(lex->m_sql_cmd= + new (thd->mem_root) Sql_cmd_delete(false))) + MYSQL_YYABORT; } - | table_wild_list + | table_alias_ref_list { + LEX *lex= Lex; + lex->sql_command= SQLCOM_DELETE_MULTI; + if (!(lex->m_sql_cmd= + new (thd->mem_root) Sql_cmd_delete(true))) + MYSQL_YYABORT; mysql_init_multi_delete(Lex); YYPS->m_lock_type= TL_READ_DEFAULT; YYPS->m_mdl_type= MDL_SHARED_READ; @@ -13356,6 +13366,11 @@ single_multi: } stmt_end {} | FROM table_alias_ref_list { + LEX *lex= Lex; + lex->sql_command= SQLCOM_DELETE_MULTI; + if (!(lex->m_sql_cmd= + new (thd->mem_root) Sql_cmd_delete(true))) + MYSQL_YYABORT; mysql_init_multi_delete(Lex); YYPS->m_lock_type= TL_READ_DEFAULT; YYPS->m_mdl_type= MDL_SHARED_READ; @@ -13391,44 +13406,6 @@ opt_returning: } ; -table_wild_list: - table_wild_one - | table_wild_list ',' table_wild_one - ; - -table_wild_one: - ident opt_wild - { - Table_ident *ti= new (thd->mem_root) Table_ident(&$1); - if (unlikely(ti == NULL)) - MYSQL_YYABORT; - if (unlikely(!Select-> - add_table_to_list(thd, - ti, - NULL, - (TL_OPTION_UPDATING | - TL_OPTION_ALIAS), - YYPS->m_lock_type, - YYPS->m_mdl_type))) - MYSQL_YYABORT; - } - | ident '.' ident opt_wild - { - Table_ident *ti= new (thd->mem_root) Table_ident(thd, &$1, &$3, 0); - if (unlikely(ti == NULL)) - MYSQL_YYABORT; - if (unlikely(!Select-> - add_table_to_list(thd, - ti, - NULL, - (TL_OPTION_UPDATING | - TL_OPTION_ALIAS), - YYPS->m_lock_type, - YYPS->m_mdl_type))) - MYSQL_YYABORT; - } - ; - opt_wild: /* empty */ {} | '.' '*' {} diff --git a/sql/table.h b/sql/table.h index 88216c7..fc7cc14 100644 --- a/sql/table.h +++ b/sql/table.h @@ -2314,6 +2314,7 @@ struct TABLE_LIST */ select_unit *derived_result; /* Stub used for materialized derived tables. */ + bool delete_while_scanning; table_map map; /* ID bit of table (1,2,4,8,16...) */ table_map get_map() {

1 0

[Commits] bd48128: MDEV-27159 Re-design the upper level of handling DML commands
by IgorBabaev 25 Mar '22

25 Mar '22

revision-id: bd48128deab009fa8351bee3b0e55ddb60a33b8a (mariadb-10.6.1-50-gbd48128) parent(s): 086a212d96b7693d1bacf67e3ad14627fb802269 author: Igor Babaev committer: Igor Babaev timestamp: 2022-03-25 08:45:10 -0700 message: MDEV-27159 Re-design the upper level of handling DML commands This is the second commit for the task. This patch allows to execute only single-table and multi-table DELETE statements using the method Sql_cmd_dml::execute(). The code that handles INSERT statements has not been touched. This patch still does not have the final changes to handle UPDATE/DELETE statements. All tests from the main suite passed. With --ps-protocol one test from opt_trace_security returns not the same result. This will be fixed soon. --- mysql-test/main/opt_trace.result | 14 +- sql/opt_range.cc | 2 +- sql/sql_base.cc | 7 +- sql/sql_cmd.h | 9 +- sql/sql_delete.cc | 577 ++++++++++++++++++++------------------- sql/sql_delete.h | 9 + sql/sql_lex.cc | 30 +- sql/sql_parse.cc | 133 +-------- sql/sql_parse.h | 1 + sql/sql_prepare.cc | 102 +------ sql/sql_select.cc | 1 - sql/sql_update.cc | 6 +- sql/sql_yacc.yy | 63 ++--- sql/table.h | 1 + 14 files changed, 373 insertions(+), 582 deletions(-) diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index fef7b4d..11bb18c 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -3740,6 +3740,16 @@ QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES explain delete from t0 where t0.a<3 { "steps": [ { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "expanded_query": "select from dual where t0.a < 3" + } + ] + } + }, + { "table": "t0", "range_analysis": { "table_scan": { @@ -3771,7 +3781,7 @@ explain delete from t0 where t0.a<3 { }, "group_index_range": { "chosen": false, - "cause": "no join" + "cause": "no group by or distinct" }, "chosen_range_access_summary": { "range_access_plan": { @@ -3814,7 +3824,7 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { "select_id": 1, "steps": [ { - "expanded_query": "select NULL AS `NULL` from t0 join t1 where t0.a = t1.a and t1.a < 3" + "expanded_query": "select from t0 join t1 where t0.a = t1.a and t1.a < 3" } ] } diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 698c709..31bba1e 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -11554,7 +11554,7 @@ ha_rows check_quick_select(PARAM *param, uint idx, bool index_only, Skip materialized derived table/view result table from MRR check as they aren't contain any data yet. */ - if (param->table->pos_in_table_list->is_non_derived()) + if (!param->table->pos_in_table_list->is_materialized_derived()) rows= file->multi_range_read_info_const(keynr, &seq_if, (void*)&seq, 0, bufsize, mrr_flags, cost); param->quick_rows[keynr]= rows; diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 65b1b92..38d1d60 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -1069,7 +1069,9 @@ TABLE_LIST* find_dup_table(THD *thd, TABLE_LIST *table, TABLE_LIST *table_list, */ if (table->table && thd->lex->sql_command != SQLCOM_UPDATE && - thd->lex->sql_command != SQLCOM_UPDATE_MULTI) + thd->lex->sql_command != SQLCOM_UPDATE_MULTI && + thd->lex->sql_command != SQLCOM_DELETE && + thd->lex->sql_command != SQLCOM_DELETE_MULTI) { /* All MyISAMMRG children are plain MyISAM tables. */ DBUG_ASSERT(table->table->file->ht->db_type != DB_TYPE_MRG_MYISAM); @@ -7592,6 +7594,9 @@ int setup_wild(THD *thd, TABLE_LIST *tables, List<Item> &fields, if (!select_lex->with_wild) DBUG_RETURN(0); + if (!fields.elements) + DBUG_RETURN(0); + /* Don't use arena if we are not in prepared statements or stored procedures For PS/SP we have to use arena to remember the changes diff --git a/sql/sql_cmd.h b/sql/sql_cmd.h index a5557c6..64299d1 100644 --- a/sql/sql_cmd.h +++ b/sql/sql_cmd.h @@ -263,6 +263,7 @@ class LEX; class select_result; class Prelocking_strategy; class DML_prelocking_strategy; +class Protocol; class Sql_cmd_dml : public Sql_cmd { @@ -290,7 +291,9 @@ class Sql_cmd_dml : public Sql_cmd protected: Sql_cmd_dml() - : Sql_cmd(), lex(nullptr), result(nullptr), m_empty_query(false) {} + : Sql_cmd(), lex(nullptr), result(nullptr), + m_empty_query(false), save_protocol(NULL) + {} /// @return true if query is guaranteed to return no data /** @@ -348,12 +351,14 @@ class Sql_cmd_dml : public Sql_cmd virtual DML_prelocking_strategy *get_dml_prelocking_strategy() = 0; - uint table_count; + uint table_count; protected: LEX *lex; ///< Pointer to LEX for this statement select_result *result; ///< Pointer to object for handling of the result bool m_empty_query; ///< True if query will produce no rows + List<Item> empty_list; + Protocol *save_protocol; }; diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc index 0a2db2e..01ee19b 100644 --- a/sql/sql_delete.cc +++ b/sql/sql_delete.cc @@ -103,7 +103,7 @@ bool Update_plan::save_explain_data_intern(MEM_ROOT *mem_root, bool is_analyze) { explain->select_type= "SIMPLE"; - explain->table_name.append(&table->pos_in_table_list->alias); + explain->table_name.append(table->alias); explain->impossible_where= false; explain->no_partitions= false; @@ -294,124 +294,79 @@ int TABLE::delete_row() } -/** - Implement DELETE SQL word. - - @note Like implementations of other DDL/DML in MySQL, this function - relies on the caller to close the thread tables. This is done in the - end of dispatch_command(). -*/ - -bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds, - SQL_I_List<ORDER> *order_list, ha_rows limit, - ulonglong options, select_result *result) +bool Sql_cmd_delete::delete_from_single_table(THD *thd) { - bool will_batch= FALSE; - int error, loc_error; - TABLE *table; - SQL_SELECT *select=0; - SORT_INFO *file_sort= 0; - READ_RECORD info; - bool using_limit=limit != HA_POS_ERROR; - bool transactional_table, safe_update, const_cond; - bool const_cond_result; - bool return_error= 0; - ha_rows deleted= 0; - bool reverse= FALSE; - bool has_triggers= false; - ORDER *order= (ORDER *) ((order_list && order_list->elements) ? - order_list->first : NULL); - SELECT_LEX *select_lex= thd->lex->first_select_lex(); - SELECT_LEX *returning= thd->lex->has_returning() ? thd->lex->returning() : 0; + int error; + int loc_error; + bool transactional_table; + bool const_cond; + bool safe_update; + bool const_cond_result; + bool return_error= 0; + TABLE *table; + SQL_SELECT *select= 0; + SORT_INFO *file_sort= 0; + READ_RECORD info; + ha_rows deleted= 0; + bool reverse= FALSE; + bool binlog_is_row; killed_state killed_status= NOT_KILLED; THD::enum_binlog_query_type query_type= THD::ROW_QUERY_TYPE; - bool binlog_is_row; - Explain_delete *explain; + bool will_batch= FALSE; + + bool has_triggers= false; + SELECT_LEX_UNIT *unit = &lex->unit; + SELECT_LEX *select_lex= unit->first_select(); + SELECT_LEX *returning= thd->lex->has_returning() ? thd->lex->returning() : 0; + TABLE_LIST *const table_list = select_lex->get_table_list(); + ulonglong options= select_lex->options; + ORDER *order= select_lex->order_list.first; + COND *conds= select_lex->join->conds; + ha_rows limit= unit->lim.get_select_limit(); + bool using_limit= limit != HA_POS_ERROR; + Delete_plan query_plan(thd->mem_root); + Explain_delete *explain; Unique * deltempfile= NULL; bool delete_record= false; - bool delete_while_scanning; + bool delete_while_scanning= table_list->delete_while_scanning; bool portion_of_time_through_update; - DBUG_ENTER("mysql_delete"); + + DBUG_ENTER("Sql_cmd_delete::delete_single_table"); query_plan.index= MAX_KEY; query_plan.using_filesort= FALSE; - create_explain_query(thd->lex, thd->mem_root); - if (open_and_lock_tables(thd, table_list, TRUE, 0)) - DBUG_RETURN(TRUE); - THD_STAGE_INFO(thd, stage_init_update); + create_explain_query(thd->lex, thd->mem_root); const bool delete_history= table_list->vers_conditions.delete_history; DBUG_ASSERT(!(delete_history && table_list->period_conditions.is_set())); - if (thd->lex->handle_list_of_derived(table_list, DT_MERGE_FOR_INSERT)) - DBUG_RETURN(TRUE); - if (thd->lex->handle_list_of_derived(table_list, DT_PREPARE)) - DBUG_RETURN(TRUE); + if (table_list->handle_derived(thd->lex, DT_MERGE_FOR_INSERT)) + DBUG_RETURN(1); + if (table_list->handle_derived(thd->lex, DT_PREPARE)) + DBUG_RETURN(1); + + table= table_list->table; if (!table_list->single_table_updatable()) { my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias.str, "DELETE"); DBUG_RETURN(TRUE); } - if (!(table= table_list->table) || !table->is_created()) + + if (!table || !table->is_created()) { my_error(ER_VIEW_DELETE_MERGE_VIEW, MYF(0), table_list->view_db.str, table_list->view_name.str); DBUG_RETURN(TRUE); } - table->map=1; + query_plan.select_lex= thd->lex->first_select_lex(); query_plan.table= table; - thd->lex->promote_select_describe_flag_if_needed(); - if (mysql_prepare_delete(thd, table_list, &conds, &delete_while_scanning)) - DBUG_RETURN(TRUE); - - if (table_list->has_period()) - { - if (!table_list->period_conditions.start.item->const_item() - || !table_list->period_conditions.end.item->const_item()) - { - my_error(ER_NOT_CONSTANT_EXPRESSION, MYF(0), "FOR PORTION OF"); - DBUG_RETURN(true); - } - } - - if (delete_history) - table->vers_write= false; - - if (returning) - (void) result->prepare(returning->item_list, NULL); - - if (thd->lex->current_select->first_cond_optimization) - { - thd->lex->current_select->save_leaf_tables(thd); - thd->lex->current_select->first_cond_optimization= 0; - } - /* check ORDER BY even if it can be ignored */ - if (order) - { - TABLE_LIST tables; - List<Item> fields; - List<Item> all_fields; - - bzero((char*) &tables,sizeof(tables)); - tables.table = table; - tables.alias = table_list->alias; - - if (select_lex->setup_ref_array(thd, order_list->elements) || - setup_order(thd, select_lex->ref_pointer_array, &tables, - fields, all_fields, order)) - { - free_underlaid_joins(thd, thd->lex->first_select_lex()); - DBUG_RETURN(TRUE); - } - } - /* Apply the IN=>EXISTS transformation to all subqueries and optimize them. */ if (select_lex->optimize_unflattened_subqueries(false)) DBUG_RETURN(TRUE); @@ -518,7 +473,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds, table->covering_keys.clear_all(); table->opt_range_keys.clear_all(); - select=make_select(table, 0, 0, conds, (SORT_INFO*) 0, 0, &error); + select= make_select(table, 0, 0, conds, (SORT_INFO*) 0, 0, &error); if (unlikely(error)) DBUG_RETURN(TRUE); if ((select && select->check_quick(thd, safe_update, limit)) || !limit) @@ -943,7 +898,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds, } } DBUG_ASSERT(transactional_table || !deleted || thd->transaction->stmt.modified_non_trans_table); - + if (likely(error < 0) || (thd->lex->ignore && !thd->is_error() && !thd->is_fatal_error)) { @@ -993,90 +948,6 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds, } -/* - Prepare items in DELETE statement - - SYNOPSIS - mysql_prepare_delete() - thd - thread handler - table_list - global/local table list - conds - conditions - - RETURN VALUE - FALSE OK - TRUE error -*/ -int mysql_prepare_delete(THD *thd, TABLE_LIST *table_list, Item **conds, - bool *delete_while_scanning) -{ - Item *fake_conds= 0; - SELECT_LEX *select_lex= thd->lex->first_select_lex(); - DBUG_ENTER("mysql_prepare_delete"); - List<Item> all_fields; - - *delete_while_scanning= true; - thd->lex->allow_sum_func.clear_all(); - if (setup_tables_and_check_access(thd, &select_lex->context, - &select_lex->top_join_list, table_list, - select_lex->leaf_tables, FALSE, - DELETE_ACL, SELECT_ACL, TRUE)) - DBUG_RETURN(TRUE); - - if (table_list->vers_conditions.is_set() && table_list->is_view_or_derived()) - { - my_error(ER_IT_IS_A_VIEW, MYF(0), table_list->table_name.str); - DBUG_RETURN(true); - } - - if (table_list->has_period()) - { - if (table_list->is_view_or_derived()) - { - my_error(ER_IT_IS_A_VIEW, MYF(0), table_list->table_name.str); - DBUG_RETURN(true); - } - - if (select_lex->period_setup_conds(thd, table_list)) - DBUG_RETURN(true); - } - - DBUG_ASSERT(table_list->table); - // conds could be cached from previous SP call - DBUG_ASSERT(!table_list->vers_conditions.need_setup() || - !*conds || thd->stmt_arena->is_stmt_execute()); - if (select_lex->vers_setup_conds(thd, table_list)) - DBUG_RETURN(TRUE); - - *conds= select_lex->where; - - if (setup_returning_fields(thd, table_list) || - setup_conds(thd, table_list, select_lex->leaf_tables, conds) || - setup_ftfuncs(select_lex)) - DBUG_RETURN(TRUE); - if (!table_list->single_table_updatable() || - check_key_in_view(thd, table_list)) - { - my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias.str, "DELETE"); - DBUG_RETURN(TRUE); - } - - /* - Application-time periods: if FOR PORTION OF ... syntax used, DELETE - statement could issue delete_row's mixed with write_row's. This causes - problems for myisam and corrupts table, if deleting while scanning. - */ - if (table_list->has_period() - || unique_table(thd, table_list, table_list->next_global, 0)) - *delete_while_scanning= false; - - if (select_lex->inner_refs_list.elements && - fix_inner_refs(thd, all_fields, select_lex, select_lex->ref_pointer_array)) - DBUG_RETURN(TRUE); - - select_lex->fix_prepare_information(thd, conds, &fake_conds); - DBUG_RETURN(FALSE); -} - /*************************************************************************** Delete multiple tables from join @@ -1089,106 +960,6 @@ extern "C" int refpos_order_cmp(void* arg, const void *a,const void *b) return file->cmp_ref((const uchar*)a, (const uchar*)b); } -/* - make delete specific preparation and checks after opening tables - - SYNOPSIS - mysql_multi_delete_prepare() - thd thread handler - - RETURN - FALSE OK - TRUE Error -*/ - -int mysql_multi_delete_prepare(THD *thd) -{ - LEX *lex= thd->lex; - TABLE_LIST *aux_tables= lex->auxiliary_table_list.first; - TABLE_LIST *target_tbl; - DBUG_ENTER("mysql_multi_delete_prepare"); - - if (mysql_handle_derived(lex, DT_INIT)) - DBUG_RETURN(TRUE); - if (mysql_handle_derived(lex, DT_MERGE_FOR_INSERT)) - DBUG_RETURN(TRUE); - if (mysql_handle_derived(lex, DT_PREPARE)) - DBUG_RETURN(TRUE); - /* - setup_tables() need for VIEWs. JOIN::prepare() will not do it second - time. - - lex->query_tables also point on local list of DELETE SELECT_LEX - */ - if (setup_tables_and_check_access(thd, - &thd->lex->first_select_lex()->context, - &thd->lex->first_select_lex()-> - top_join_list, - lex->query_tables, - lex->first_select_lex()->leaf_tables, - FALSE, DELETE_ACL, SELECT_ACL, FALSE)) - DBUG_RETURN(TRUE); - - /* - Multi-delete can't be constructed over-union => we always have - single SELECT on top and have to check underlying SELECTs of it - */ - lex->first_select_lex()->set_unique_exclude(); - /* Fix tables-to-be-deleted-from list to point at opened tables */ - for (target_tbl= (TABLE_LIST*) aux_tables; - target_tbl; - target_tbl= target_tbl->next_local) - { - - target_tbl->table= target_tbl->correspondent_table->table; - if (target_tbl->correspondent_table->is_multitable()) - { - my_error(ER_VIEW_DELETE_MERGE_VIEW, MYF(0), - target_tbl->correspondent_table->view_db.str, - target_tbl->correspondent_table->view_name.str); - DBUG_RETURN(TRUE); - } - - if (!target_tbl->correspondent_table->single_table_updatable() || - check_key_in_view(thd, target_tbl->correspondent_table)) - { - my_error(ER_NON_UPDATABLE_TABLE, MYF(0), - target_tbl->table_name.str, "DELETE"); - DBUG_RETURN(TRUE); - } - } - - for (target_tbl= (TABLE_LIST*) aux_tables; - target_tbl; - target_tbl= target_tbl->next_local) - { - /* - Check that table from which we delete is not used somewhere - inside subqueries/view. - */ - { - TABLE_LIST *duplicate; - if ((duplicate= unique_table(thd, target_tbl->correspondent_table, - lex->query_tables, 0))) - { - update_non_unique_table_error(target_tbl->correspondent_table, - "DELETE", duplicate); - DBUG_RETURN(TRUE); - } - } - } - /* - Reset the exclude flag to false so it doesn't interfare - with further calls to unique_table - */ - lex->first_select_lex()->exclude_from_table_unique_test= FALSE; - - if (lex->save_prep_leaf_tables()) - DBUG_RETURN(TRUE); - - DBUG_RETURN(FALSE); -} - multi_delete::multi_delete(THD *thd_arg, TABLE_LIST *dt, uint num_of_tables_arg): select_result_interceptor(thd_arg), delete_tables(dt), deleted(0), found(0), @@ -1637,3 +1408,261 @@ bool multi_delete::send_eof() } return 0; } + + +bool Sql_cmd_delete::precheck(THD *thd) +{ + if (!multitable) + { + if (delete_precheck(thd, lex->query_tables)) + return true; + } + else + { + if (multi_delete_precheck(thd, lex->query_tables)) + return true; + } + return false; +} + + +bool Sql_cmd_delete::prepare_inner(THD *thd) +{ + int err= 0; + TABLE_LIST *target_tbl; + JOIN *join; + SELECT_LEX *const select_lex = thd->lex->first_select_lex(); + TABLE_LIST *const table_list = select_lex->get_table_list(); + TABLE_LIST *aux_tables= thd->lex->auxiliary_table_list.first; + ulonglong select_options= select_lex->options; + bool free_join= 1; + SELECT_LEX *returning= thd->lex->has_returning() ? thd->lex->returning() : 0; + + DBUG_ENTER("Sql_cmd_delete::prepare_inner"); + + (void) read_statistics_for_tables_if_needed(thd, table_list); + + { + if (mysql_handle_derived(lex, DT_INIT)) + DBUG_RETURN(TRUE); + if (mysql_handle_derived(lex, DT_MERGE_FOR_INSERT)) + DBUG_RETURN(TRUE); + if (mysql_handle_derived(lex, DT_PREPARE)) + DBUG_RETURN(TRUE); + } + + if (!(result= new (thd->mem_root) multi_delete(thd, aux_tables, + lex->table_count))) + { + DBUG_RETURN(TRUE); + } + + table_list->delete_while_scanning= true; + + if (setup_tables_and_check_access(thd, &select_lex->context, + &select_lex->top_join_list, + table_list, select_lex->leaf_tables, + false, DELETE_ACL, SELECT_ACL, true)) + DBUG_RETURN(TRUE); + + if (setup_tables(thd, &select_lex->context, &select_lex->top_join_list, + table_list, select_lex->leaf_tables, false, false)) + DBUG_RETURN(TRUE); + + if (!multitable) + { + if (table_list->vers_conditions.is_set() && table_list->is_view_or_derived()) + { + my_error(ER_IT_IS_A_VIEW, MYF(0), table_list->table_name.str); + DBUG_RETURN(true); + } + + if (table_list->has_period()) + { + if (table_list->is_view_or_derived()) + { + my_error(ER_IT_IS_A_VIEW, MYF(0), table_list->table_name.str); + DBUG_RETURN(true); + } + + if (select_lex->period_setup_conds(thd, table_list)) + DBUG_RETURN(true); + } + + if (select_lex->vers_setup_conds(thd, table_list)) + DBUG_RETURN(TRUE); + /* + Application-time periods: if FOR PORTION OF ... syntax used, DELETE + statement could issue delete_row's mixed with write_row's. This causes + problems for myisam and corrupts table, if deleting while scanning. + */ + if (table_list->has_period() + || unique_table(thd, table_list, table_list->next_global, 0)) + table_list->delete_while_scanning= false; + } + + if (multitable) + { + /* + Multi-delete can't be constructed over-union => we always have + single SELECT on top and have to check underlying SELECTs of it + */ + lex->first_select_lex()->set_unique_exclude(); + /* Fix tables-to-be-deleted-from list to point at opened tables */ + for (target_tbl= (TABLE_LIST*) aux_tables; + target_tbl; + target_tbl= target_tbl->next_local) + { + target_tbl->table= target_tbl->correspondent_table->table; + if (target_tbl->correspondent_table->is_multitable()) + { + my_error(ER_VIEW_DELETE_MERGE_VIEW, MYF(0), + target_tbl->correspondent_table->view_db.str, + target_tbl->correspondent_table->view_name.str); + DBUG_RETURN(TRUE); + } + + if (!target_tbl->correspondent_table->single_table_updatable() || + check_key_in_view(thd, target_tbl->correspondent_table)) + { + my_error(ER_NON_UPDATABLE_TABLE, MYF(0), + target_tbl->table_name.str, "DELETE"); + DBUG_RETURN(TRUE); + } + } + + for (target_tbl= (TABLE_LIST*) aux_tables; + target_tbl; + target_tbl= target_tbl->next_local) + { + /* + Check that table from which we delete is not used somewhere + inside subqueries/view. + */ + { + TABLE_LIST *duplicate; + if ((duplicate= unique_table(thd, target_tbl->correspondent_table, + lex->query_tables, 0))) + { + update_non_unique_table_error(target_tbl->correspondent_table, + "DELETE", duplicate); + DBUG_RETURN(TRUE); + } + } + } + /* + Reset the exclude flag to false so it doesn't interfare + with further calls to unique_table + */ + lex->first_select_lex()->exclude_from_table_unique_test= FALSE; + } + + { + if (thd->lex->describe) + select_options|= SELECT_DESCRIBE; + + /* + When in EXPLAIN, delay deleting the joins so that they are still + available when we're producing EXPLAIN EXTENDED warning text. + */ + if (select_options & SELECT_DESCRIBE) + free_join= 0; + select_options|= + SELECT_NO_JOIN_CACHE | SELECT_NO_UNLOCK | OPTION_SETUP_TABLES_DONE; + + if (!(join= new (thd->mem_root) JOIN(thd, empty_list, + select_options, result))) + DBUG_RETURN(TRUE); + THD_STAGE_INFO(thd, stage_init); + select_lex->join= join; + thd->lex->used_tables=0; + if ((err= join->prepare(table_list, select_lex->where, + select_lex->order_list.elements, + select_lex->order_list.first, + false, NULL, NULL, NULL, + select_lex, &lex->unit))) + + { + goto err; + } + + } + + + if (setup_returning_fields(thd, table_list) || + setup_ftfuncs(select_lex)) + goto err; + + free_join= false; + + if (returning) + (void) result->prepare(returning->item_list, NULL); + +err: + + if (free_join) + { + THD_STAGE_INFO(thd, stage_end); + err|= (int)(select_lex->cleanup()); + DBUG_RETURN(err || thd->is_error()); + } + DBUG_RETURN(err); + +} + +bool Sql_cmd_delete::execute_inner(THD *thd) +{ + if (!multitable) + { + if (lex->has_returning()) + { + select_result *sel_result= NULL; + delete result; + /* This is DELETE ... RETURNING. It will return output to the client */ + if (thd->lex->analyze_stmt) + { + /* + Actually, it is ANALYZE .. DELETE .. RETURNING. We need to produce + output and then discard it. + */ + sel_result= new (thd->mem_root) select_send_analyze(thd); + save_protocol= thd->protocol; + thd->protocol= new Protocol_discard(thd); + } + else + { + if (!lex->result && !(sel_result= new (thd->mem_root) select_send(thd))) + return true; + } + result= lex->result ? lex->result : sel_result; + } + } + + bool res= multitable ? Sql_cmd_dml::execute_inner(thd) + : delete_from_single_table(thd); + + res|= thd->is_error(); + + if (save_protocol) + { + delete thd->protocol; + thd->protocol= save_protocol; + } + { + if (unlikely(res)) + result->abort_result_set(); + else + { + if (thd->lex->describe || thd->lex->analyze_stmt) + res= thd->lex->explain->send_explain(thd); + } + } + + if (result) + { + res= false; + delete result; + } + + return res; +} diff --git a/sql/sql_delete.h b/sql/sql_delete.h index dabcafb..4aee510 100644 --- a/sql/sql_delete.h +++ b/sql/sql_delete.h @@ -17,6 +17,9 @@ #define SQL_DELETE_INCLUDED #include "my_base.h" /* ha_rows */ +#include "sql_class.h" /* enum_duplicates */ +#include "sql_cmd.h" // Sql_cmd_dml +#include "sql_base.h" class THD; struct TABLE_LIST; @@ -43,6 +46,11 @@ class Sql_cmd_delete final : public Sql_cmd_dml return multitable ? SQLCOM_DELETE_MULTI : SQLCOM_DELETE; } + DML_prelocking_strategy *get_dml_prelocking_strategy() + { + return &dml_prelocking_strategy; + } + protected: bool precheck(THD *thd) override; @@ -55,5 +63,6 @@ class Sql_cmd_delete final : public Sql_cmd_dml bool multitable; + DML_prelocking_strategy dml_prelocking_strategy; }; #endif /* SQL_DELETE_INCLUDED */ diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 47d9479..032e094 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -3324,34 +3324,6 @@ void st_select_lex_unit::exclude_level() } -#if 0 -/* - Exclude subtree of current unit from tree of SELECTs - - SYNOPSYS - st_select_lex_unit::exclude_tree() -*/ -void st_select_lex_unit::exclude_tree() -{ - for (SELECT_LEX *sl= first_select(); sl; sl= sl->next_select()) - { - // unlink current level from global SELECTs list - if (sl->link_prev && (*sl->link_prev= sl->link_next)) - sl->link_next->link_prev= sl->link_prev; - - // unlink underlay levels - for (SELECT_LEX_UNIT *u= sl->first_inner_unit(); u; u= u->next_unit()) - { - u->exclude_level(); - } - } - // exclude currect unit from list of nodes - (*prev)= next; - if (next) - next->prev= prev; -} -#endif - /* st_select_lex_node::mark_as_dependent mark all st_select_lex struct from @@ -3573,7 +3545,7 @@ bool st_select_lex::setup_ref_array(THD *thd, uint order_group_num) select_n_where_fields + order_group_num + hidden_bit_fields + - fields_in_window_functions) * 5; + fields_in_window_functions + 1) * 5; if (!ref_pointer_array.is_null()) { /* diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 47d42f9..f23b554 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -4383,6 +4383,8 @@ mysql_execute_command(THD *thd, bool is_called_from_prepared_stmt) } case SQLCOM_UPDATE: case SQLCOM_UPDATE_MULTI: + case SQLCOM_DELETE: + case SQLCOM_DELETE_MULTI: { DBUG_ASSERT(first_table == all_tables && first_table != 0); DBUG_ASSERT(lex->m_sql_cmd != NULL); @@ -4650,129 +4652,6 @@ mysql_execute_command(THD *thd, bool is_called_from_prepared_stmt) break; } - case SQLCOM_DELETE: - { - WSREP_SYNC_WAIT(thd, WSREP_SYNC_WAIT_BEFORE_UPDATE_DELETE); - select_result *sel_result= NULL; - DBUG_ASSERT(first_table == all_tables && first_table != 0); - WSREP_SYNC_WAIT(thd, WSREP_SYNC_WAIT_BEFORE_UPDATE_DELETE); - - if ((res= delete_precheck(thd, all_tables))) - break; - DBUG_ASSERT(select_lex->limit_params.offset_limit == 0); - unit->set_limit(select_lex); - - MYSQL_DELETE_START(thd->query()); - Protocol *save_protocol= NULL; - - if (lex->has_returning()) - { - /* This is DELETE ... RETURNING. It will return output to the client */ - if (thd->lex->analyze_stmt) - { - /* - Actually, it is ANALYZE .. DELETE .. RETURNING. We need to produce - output and then discard it. - */ - sel_result= new (thd->mem_root) select_send_analyze(thd); - save_protocol= thd->protocol; - thd->protocol= new Protocol_discard(thd); - } - else - { - if (!lex->result && !(sel_result= new (thd->mem_root) select_send(thd))) - goto error; - } - } - - res = mysql_delete(thd, all_tables, - select_lex->where, &select_lex->order_list, - unit->lim.get_select_limit(), select_lex->options, - lex->result ? lex->result : sel_result); - - if (save_protocol) - { - delete thd->protocol; - thd->protocol= save_protocol; - } - - if (thd->lex->analyze_stmt || thd->lex->describe) - { - if (!res) - res= thd->lex->explain->send_explain(thd); - } - - delete sel_result; - MYSQL_DELETE_DONE(res, (ulong) thd->get_row_count_func()); - break; - } - case SQLCOM_DELETE_MULTI: - { - WSREP_SYNC_WAIT(thd, WSREP_SYNC_WAIT_BEFORE_UPDATE_DELETE); - DBUG_ASSERT(first_table == all_tables && first_table != 0); - TABLE_LIST *aux_tables= thd->lex->auxiliary_table_list.first; - multi_delete *result; - WSREP_SYNC_WAIT(thd, WSREP_SYNC_WAIT_BEFORE_UPDATE_DELETE); - - if ((res= multi_delete_precheck(thd, all_tables))) - break; - - /* condition will be TRUE on SP re-excuting */ - if (select_lex->item_list.elements != 0) - select_lex->item_list.empty(); - if (add_item_to_list(thd, new (thd->mem_root) Item_null(thd))) - goto error; - - THD_STAGE_INFO(thd, stage_init); - if ((res= open_and_lock_tables(thd, all_tables, TRUE, 0))) - break; - - MYSQL_MULTI_DELETE_START(thd->query()); - if (unlikely(res= mysql_multi_delete_prepare(thd))) - { - MYSQL_MULTI_DELETE_DONE(1, 0); - goto error; - } - - if (likely(!thd->is_fatal_error)) - { - result= new (thd->mem_root) multi_delete(thd, aux_tables, - lex->table_count); - if (likely(result)) - { - if (unlikely(select_lex->vers_setup_conds(thd, aux_tables))) - goto multi_delete_error; - res= mysql_select(thd, - select_lex->get_table_list(), - select_lex->item_list, - select_lex->where, - 0, (ORDER *)NULL, (ORDER *)NULL, (Item *)NULL, - (ORDER *)NULL, - (select_lex->options | thd->variables.option_bits | - SELECT_NO_JOIN_CACHE | SELECT_NO_UNLOCK | - OPTION_SETUP_TABLES_DONE) & ~OPTION_BUFFER_RESULT, - result, unit, select_lex); - res|= (int)(thd->is_error()); - - MYSQL_MULTI_DELETE_DONE(res, result->num_deleted()); - if (res) - result->abort_result_set(); /* for both DELETE and EXPLAIN DELETE */ - else - { - if (lex->describe || lex->analyze_stmt) - res= thd->lex->explain->send_explain(thd); - } - multi_delete_error: - delete result; - } - } - else - { - res= TRUE; // Error - MYSQL_MULTI_DELETE_DONE(1, 0); - } - break; - } case SQLCOM_DROP_SEQUENCE: case SQLCOM_DROP_TABLE: { @@ -7643,12 +7522,16 @@ void create_select_for_variable(THD *thd, LEX_CSTRING *var_name) } -void mysql_init_multi_delete(LEX *lex) +void mysql_init_delete(LEX *lex) { - lex->sql_command= SQLCOM_DELETE_MULTI; mysql_init_select(lex); lex->first_select_lex()->limit_params.clear(); lex->unit.lim.clear(); +} + +void mysql_init_multi_delete(LEX *lex) +{ + lex->sql_command= SQLCOM_DELETE_MULTI; lex->first_select_lex()->table_list. save_and_clear(&lex->auxiliary_table_list); lex->query_tables= 0; diff --git a/sql/sql_parse.h b/sql/sql_parse.h index ebe3fe9..45cd15c 100644 --- a/sql/sql_parse.h +++ b/sql/sql_parse.h @@ -95,6 +95,7 @@ void mysql_parse(THD *thd, char *rawbuf, uint length, bool mysql_new_select(LEX *lex, bool move_down, SELECT_LEX *sel); void create_select_for_variable(THD *thd, LEX_CSTRING *var_name); void create_table_set_open_action_and_adjust_tables(LEX *lex); +void mysql_init_delete(LEX *lex); void mysql_init_multi_delete(LEX *lex); bool multi_delete_set_locks_and_link_aux_tables(LEX *lex); void create_table_set_open_action_and_adjust_tables(LEX *lex); diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index 87ee207..ad51fe2 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -95,7 +95,6 @@ When one supplies long data for a placeholder: #include "sql_base.h" // open_normal_and_derived_tables #include "sql_cache.h" // query_cache_* #include "sql_view.h" // create_view_precheck -#include "sql_delete.h" // mysql_prepare_delete #include "sql_select.h" // for JOIN #include "sql_insert.h" // upgrade_lock_type_for_insert, mysql_prepare_insert #include "sql_db.h" // mysql_opt_change_db, mysql_change_db @@ -1396,56 +1395,6 @@ static bool mysql_test_insert(Prepared_statement *stmt, } -/** - Validate DELETE statement. - - @param stmt prepared statement - @param tables list of tables used in this query - - @retval - FALSE success - @retval - TRUE error, error message is set in THD -*/ - -static bool mysql_test_delete(Prepared_statement *stmt, - TABLE_LIST *table_list) -{ - uint table_count= 0; - THD *thd= stmt->thd; - LEX *lex= stmt->lex; - bool delete_while_scanning; - DBUG_ENTER("mysql_test_delete"); - - if (delete_precheck(thd, table_list) || - open_tables(thd, &table_list, &table_count, MYSQL_OPEN_FORCE_SHARED_MDL)) - goto error; - - if (mysql_handle_derived(thd->lex, DT_INIT)) - goto error; - if (mysql_handle_derived(thd->lex, DT_MERGE_FOR_INSERT)) - goto error; - if (mysql_handle_derived(thd->lex, DT_PREPARE)) - goto error; - - if (!table_list->single_table_updatable()) - { - my_error(ER_NON_UPDATABLE_TABLE, MYF(0), table_list->alias.str, "DELETE"); - goto error; - } - if (!table_list->table || !table_list->table->is_created()) - { - my_error(ER_VIEW_DELETE_MERGE_VIEW, MYF(0), - table_list->view_db.str, table_list->view_name.str); - goto error; - } - - DBUG_RETURN(mysql_prepare_delete(thd, table_list, - &lex->first_select_lex()->where, - &delete_while_scanning)); -error: - DBUG_RETURN(TRUE); -} /** @@ -2024,48 +1973,6 @@ static bool mysql_test_create_view(Prepared_statement *stmt) /** - Validate and prepare for execution a multi delete statement. - - @param stmt prepared statement - @param tables list of tables used in this query - - @retval - FALSE success - @retval - TRUE error, error message in THD is set. -*/ - -static bool mysql_test_multidelete(Prepared_statement *stmt, - TABLE_LIST *tables) -{ - THD *thd= stmt->thd; - - thd->lex->current_select= thd->lex->first_select_lex(); - if (add_item_to_list(thd, new (thd->mem_root) - Item_null(thd))) - { - my_error(ER_OUTOFMEMORY, MYF(ME_FATAL), 0); - goto error; - } - - if (multi_delete_precheck(thd, tables) || - select_like_stmt_test_with_open(stmt, tables, - &mysql_multi_delete_prepare, - OPTION_SETUP_TABLES_DONE)) - goto error; - if (!tables->table) - { - my_error(ER_VIEW_DELETE_MERGE_VIEW, MYF(0), - tables->view_db.str, tables->view_name.str); - goto error; - } - return FALSE; -error: - return TRUE; -} - - -/** Wrapper for mysql_insert_select_prepare, to make change of local tables after open_normal_and_derived_tables() call. @@ -2343,14 +2250,13 @@ static bool check_prepared_statement(Prepared_statement *stmt) case SQLCOM_UPDATE: case SQLCOM_UPDATE_MULTI: + case SQLCOM_DELETE: + case SQLCOM_DELETE_MULTI: res = lex->m_sql_cmd->prepare(thd); if (!res) lex->m_sql_cmd->unprepare(thd); break; - case SQLCOM_DELETE: - res= mysql_test_delete(stmt, tables); - break; /* The following allow WHERE clause, so they must be tested like SELECT */ case SQLCOM_SHOW_DATABASES: case SQLCOM_SHOW_TABLES: @@ -2487,10 +2393,6 @@ static bool check_prepared_statement(Prepared_statement *stmt) res= mysql_test_set_fields(stmt, tables, &lex->var_list); break; - case SQLCOM_DELETE_MULTI: - res= mysql_test_multidelete(stmt, tables); - break; - case SQLCOM_INSERT_SELECT: case SQLCOM_REPLACE_SELECT: res= mysql_test_insert_select(stmt, tables); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 47ac7c8..3d7ec69 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -30219,7 +30219,6 @@ bool Sql_cmd_dml::execute(THD *thd) THD_STAGE_INFO(thd, stage_init); - DBUG_ASSERT(!lex->is_query_tables_locked()); /* Locking of tables is done after preparation but before optimization. This allows to do better partition pruning and avoid locking unused diff --git a/sql/sql_update.cc b/sql/sql_update.cc index 1877194..b20b704 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -3017,12 +3017,10 @@ bool Sql_cmd_update::prepare_inner(THD *thd) { JOIN *join; int err= 0; - // uint table_cnt= 0; SELECT_LEX *const select_lex = thd->lex->first_select_lex(); TABLE_LIST *const table_list = select_lex->get_table_list(); ulonglong select_options= select_lex->options; bool free_join= 1; - // bool orig_multitable= multitable; DBUG_ENTER("Sql_cmd_update::prepare_inner"); if (!multitable) @@ -3076,8 +3074,8 @@ bool Sql_cmd_update::prepare_inner(THD *thd) DBUG_RETURN(TRUE); } - if (((multi_update *)result)->init(thd)) - DBUG_RETURN(TRUE); + if (((multi_update *)result)->init(thd)) + DBUG_RETURN(TRUE); if (setup_tables(thd, &select_lex->context, &select_lex->top_join_list, table_list, select_lex->leaf_tables, false, false)) diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 9d112c9..61d4ed9 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -70,6 +70,7 @@ #include "sql_type_json.h" #include "json_table.h" #include "sql_update.h" +#include "sql_delete.h" /* this is to get the bison compilation windows warnings out */ #ifdef _MSC_VER @@ -1680,7 +1681,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); opt_mi_check_type opt_to mi_check_types table_to_table_list table_to_table opt_table_list opt_as handler_rkey_function handler_read_or_scan - single_multi table_wild_list table_wild_one opt_wild + single_multi opt_wild opt_and select_var_list select_var_list_init help opt_extended_describe shutdown @@ -13554,12 +13555,11 @@ delete: DELETE_SYM { LEX *lex= Lex; - lex->sql_command= SQLCOM_DELETE; YYPS->m_lock_type= TL_WRITE_DEFAULT; YYPS->m_mdl_type= MDL_SHARED_WRITE; if (Lex->main_select_push()) MYSQL_YYABORT; - mysql_init_select(lex); + mysql_init_delete(lex); lex->ignore= 0; lex->first_select_lex()->order_list.empty(); } @@ -13617,12 +13617,22 @@ single_multi: delete_limit_clause opt_returning { + LEX *lex= Lex; if ($3) Select->order_list= *($3); - Lex->pop_select(); //main select + lex->pop_select(); //main select + lex->sql_command= SQLCOM_DELETE; + if (!(lex->m_sql_cmd= + new (thd->mem_root) Sql_cmd_delete(false))) + MYSQL_YYABORT; } - | table_wild_list + | table_alias_ref_list { + LEX *lex= Lex; + lex->sql_command= SQLCOM_DELETE_MULTI; + if (!(lex->m_sql_cmd= + new (thd->mem_root) Sql_cmd_delete(true))) + MYSQL_YYABORT; mysql_init_multi_delete(Lex); YYPS->m_lock_type= TL_READ_DEFAULT; YYPS->m_mdl_type= MDL_SHARED_READ; @@ -13634,6 +13644,11 @@ single_multi: } stmt_end {} | FROM table_alias_ref_list { + LEX *lex= Lex; + lex->sql_command= SQLCOM_DELETE_MULTI; + if (!(lex->m_sql_cmd= + new (thd->mem_root) Sql_cmd_delete(true))) + MYSQL_YYABORT; mysql_init_multi_delete(Lex); YYPS->m_lock_type= TL_READ_DEFAULT; YYPS->m_mdl_type= MDL_SHARED_READ; @@ -13669,44 +13684,6 @@ opt_returning: } ; -table_wild_list: - table_wild_one - | table_wild_list ',' table_wild_one - ; - -table_wild_one: - ident opt_wild - { - Table_ident *ti= new (thd->mem_root) Table_ident(&$1); - if (unlikely(ti == NULL)) - MYSQL_YYABORT; - if (unlikely(!Select-> - add_table_to_list(thd, - ti, - NULL, - (TL_OPTION_UPDATING | - TL_OPTION_ALIAS), - YYPS->m_lock_type, - YYPS->m_mdl_type))) - MYSQL_YYABORT; - } - | ident '.' ident opt_wild - { - Table_ident *ti= new (thd->mem_root) Table_ident(thd, &$1, &$3, 0); - if (unlikely(ti == NULL)) - MYSQL_YYABORT; - if (unlikely(!Select-> - add_table_to_list(thd, - ti, - NULL, - (TL_OPTION_UPDATING | - TL_OPTION_ALIAS), - YYPS->m_lock_type, - YYPS->m_mdl_type))) - MYSQL_YYABORT; - } - ; - opt_wild: /* empty */ {} | '.' '*' {} diff --git a/sql/table.h b/sql/table.h index 2e074ab..1bf8ee3 100644 --- a/sql/table.h +++ b/sql/table.h @@ -2311,6 +2311,7 @@ struct TABLE_LIST */ select_unit *derived_result; /* Stub used for materialized derived tables. */ + bool delete_while_scanning; table_map map; /* ID bit of table (1,2,4,8,16...) */ table_map get_map() {

1 0

[Commits] b9cd8aa: MDEV-27937 Assertion failure when executing prepared statement with ? in IN list
by IgorBabaev 24 Mar '22

24 Mar '22

revision-id: b9cd8aa1de578b72e2875f7a6b730d43c1f3fc13 (mariadb-10.3.26-366-gb9cd8aa) parent(s): bbf02c85ba2e850da546199421cb75c224747475 author: Igor Babaev committer: Igor Babaev timestamp: 2022-03-24 12:36:22 -0700 message: MDEV-27937 Assertion failure when executing prepared statement with ? in IN list This bug affected queries with IN predicates that contain parameter markers in the value list. Such queries are executed via prepared statements. The problem appeared only if the number of elements in the value list was greater than the set value of the system variable in_predicate_conversion_threshold. The patch unconditionally prohibits conversion of an IN predicate to the equivalent IN predicand if the value list of the IN predicate contains parameters markers. --- mysql-test/main/opt_tvc.result | 27 +++++++++++++++++++++++++++ mysql-test/main/opt_tvc.test | 26 ++++++++++++++++++++++++++ sql/item_cmpfunc.cc | 7 ++++--- sql/item_cmpfunc.h | 2 ++ sql/sql_tvc.cc | 27 ++++++++++++++++++++++++--- 5 files changed, 83 insertions(+), 6 deletions(-) diff --git a/mysql-test/main/opt_tvc.result b/mysql-test/main/opt_tvc.result index a68e70e..02d9096 100644 --- a/mysql-test/main/opt_tvc.result +++ b/mysql-test/main/opt_tvc.result @@ -732,3 +732,30 @@ a b 4 4 drop table t1; SET @@in_predicate_conversion_threshold= default; +# +# MDEV-27937: Prepared statement with ? in the list if IN predicate +# +set in_predicate_conversion_threshold=2; +create table t1 (id int, a int, b int); +insert into t1 values (1,3,30), (2,7,70), (3,1,10); +prepare stmt from " +select * from t1 where a in (7, ?, 5, 1); +"; +execute stmt using 3; +id a b +1 3 30 +2 7 70 +3 1 10 +deallocate prepare stmt; +prepare stmt from " +select * from t1 where (a,b) in ((7,70), (3,?), (5,50), (1,10)); +"; +execute stmt using 30; +id a b +1 3 30 +2 7 70 +3 1 10 +deallocate prepare stmt; +drop table t1; +set in_predicate_conversion_threshold=default; +# End of 10.3 tests diff --git a/mysql-test/main/opt_tvc.test b/mysql-test/main/opt_tvc.test index e4e8c6d..f8469f2 100644 --- a/mysql-test/main/opt_tvc.test +++ b/mysql-test/main/opt_tvc.test @@ -428,3 +428,29 @@ eval $query; drop table t1; SET @@in_predicate_conversion_threshold= default; +--echo # +--echo # MDEV-27937: Prepared statement with ? in the list if IN predicate +--echo # + +set in_predicate_conversion_threshold=2; + +create table t1 (id int, a int, b int); +insert into t1 values (1,3,30), (2,7,70), (3,1,10); + +prepare stmt from " +select * from t1 where a in (7, ?, 5, 1); +"; +execute stmt using 3; +deallocate prepare stmt; + +prepare stmt from " +select * from t1 where (a,b) in ((7,70), (3,?), (5,50), (1,10)); +"; +execute stmt using 30; +deallocate prepare stmt; + +drop table t1; + +set in_predicate_conversion_threshold=default; + +--echo # End of 10.3 tests diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 38f0a28..f41414f 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -4472,10 +4472,11 @@ void Item_func_in::mark_as_condition_AND_part(TABLE_LIST *embedding) Query_arena *arena, backup; arena= thd->activate_stmt_arena_if_needed(&backup); - if (to_be_transformed_into_in_subq(thd)) + if (!transform_into_subq_checked) { - transform_into_subq= true; - thd->lex->current_select->in_funcs.push_back(this, thd->mem_root); + if ((transform_into_subq= to_be_transformed_into_in_subq(thd))) + thd->lex->current_select->in_funcs.push_back(this, thd->mem_root); + transform_into_subq_checked= true; } if (arena) diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 4c88f5b..f3d3be4 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -2299,6 +2299,7 @@ class Item_func_in :public Item_func_opt_neg, SEL_TREE *get_func_mm_tree(RANGE_OPT_PARAM *param, Field *field, Item *value); bool transform_into_subq; + bool transform_into_subq_checked; public: /// An array of values, created when the bisection lookup method is used in_vector *array; @@ -2321,6 +2322,7 @@ class Item_func_in :public Item_func_opt_neg, Item_func_opt_neg(thd, list), Predicant_to_list_comparator(thd, arg_count - 1), transform_into_subq(false), + transform_into_subq_checked(false), array(0), have_null(0), arg_types_compatible(FALSE), emb_on_expr_nest(0) { } diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc index 3866b7c..13efd97 100644 --- a/sql/sql_tvc.cc +++ b/sql/sql_tvc.cc @@ -900,8 +900,6 @@ Item *Item_func_in::in_predicate_to_in_subs_transformer(THD *thd, if (!transform_into_subq) return this; - transform_into_subq= false; - List<List_item> values; LEX *lex= thd->lex; @@ -1058,15 +1056,38 @@ uint32 Item_func_in::max_length_of_left_expr() bool Item_func_in::to_be_transformed_into_in_subq(THD *thd) { + bool is_row_list= args[1]->type() == Item::ROW_ITEM; uint values_count= arg_count-1; - if (args[1]->type() == Item::ROW_ITEM) + if (is_row_list) values_count*= ((Item_row *)(args[1]))->cols(); if (thd->variables.in_subquery_conversion_threshold == 0 || thd->variables.in_subquery_conversion_threshold > values_count) return false; + if (!(thd->lex->context_analysis_only & CONTEXT_ANALYSIS_ONLY_PREPARE)) + return true; + + /* Occurence of '?' in IN list is checked only for PREPARE <stmt> commands */ + for (uint i=1; i < arg_count; i++) + { + if (!is_row_list) + { + if (args[i]->type() == Item::PARAM_ITEM) + return false; + } + else + { + Item_row *row_list= (Item_row *)(args[i]); + for (uint j=0; j < row_list->cols(); j++) + { + if (row_list->element_index(j)->type() == Item::PARAM_ITEM) + return false; + } + } + } + return true; }

1 0

[Commits] 1902fc4: MDEV-27937 Assertion failure when executing prepared statement with ? in IN list
by IgorBabaev 24 Mar '22

24 Mar '22

revision-id: 1902fc43b74c9dabedbde0da6b567fa184eaf6d1 (mariadb-10.3.26-366-g1902fc4) parent(s): bbf02c85ba2e850da546199421cb75c224747475 author: Igor Babaev committer: Igor Babaev timestamp: 2022-03-24 11:43:17 -0700 message: MDEV-27937 Assertion failure when executing prepared statement with ? in IN list This bug affected queries with IN predicates that contain parameter markers in the value list. Such queries are executed via prepared statements. The problem appeared only if the number of elements in the value list was greater than the set value of the system variable in_predicate_conversion_threshold. The patch unconditionally prohibits conversion of an IN predicate to the equivalent IN predicand if the value list of the IN predicate contains parameters markers. --- mysql-test/main/opt_tvc.result | 27 +++++++++++++++++++++++++++ mysql-test/main/opt_tvc.test | 26 ++++++++++++++++++++++++++ sql/item_cmpfunc.cc | 7 ++++--- sql/item_cmpfunc.h | 2 ++ sql/sql_tvc.cc | 27 ++++++++++++++++++++++++--- 5 files changed, 83 insertions(+), 6 deletions(-) diff --git a/mysql-test/main/opt_tvc.result b/mysql-test/main/opt_tvc.result index a68e70e..02d9096 100644 --- a/mysql-test/main/opt_tvc.result +++ b/mysql-test/main/opt_tvc.result @@ -732,3 +732,30 @@ a b 4 4 drop table t1; SET @@in_predicate_conversion_threshold= default; +# +# MDEV-27937: Prepared statement with ? in the list if IN predicate +# +set in_predicate_conversion_threshold=2; +create table t1 (id int, a int, b int); +insert into t1 values (1,3,30), (2,7,70), (3,1,10); +prepare stmt from " +select * from t1 where a in (7, ?, 5, 1); +"; +execute stmt using 3; +id a b +1 3 30 +2 7 70 +3 1 10 +deallocate prepare stmt; +prepare stmt from " +select * from t1 where (a,b) in ((7,70), (3,?), (5,50), (1,10)); +"; +execute stmt using 30; +id a b +1 3 30 +2 7 70 +3 1 10 +deallocate prepare stmt; +drop table t1; +set in_predicate_conversion_threshold=default; +# End of 10.3 tests diff --git a/mysql-test/main/opt_tvc.test b/mysql-test/main/opt_tvc.test index e4e8c6d..f8469f2 100644 --- a/mysql-test/main/opt_tvc.test +++ b/mysql-test/main/opt_tvc.test @@ -428,3 +428,29 @@ eval $query; drop table t1; SET @@in_predicate_conversion_threshold= default; +--echo # +--echo # MDEV-27937: Prepared statement with ? in the list if IN predicate +--echo # + +set in_predicate_conversion_threshold=2; + +create table t1 (id int, a int, b int); +insert into t1 values (1,3,30), (2,7,70), (3,1,10); + +prepare stmt from " +select * from t1 where a in (7, ?, 5, 1); +"; +execute stmt using 3; +deallocate prepare stmt; + +prepare stmt from " +select * from t1 where (a,b) in ((7,70), (3,?), (5,50), (1,10)); +"; +execute stmt using 30; +deallocate prepare stmt; + +drop table t1; + +set in_predicate_conversion_threshold=default; + +--echo # End of 10.3 tests diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 38f0a28..f41414f 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -4472,10 +4472,11 @@ void Item_func_in::mark_as_condition_AND_part(TABLE_LIST *embedding) Query_arena *arena, backup; arena= thd->activate_stmt_arena_if_needed(&backup); - if (to_be_transformed_into_in_subq(thd)) + if (!transform_into_subq_checked) { - transform_into_subq= true; - thd->lex->current_select->in_funcs.push_back(this, thd->mem_root); + if ((transform_into_subq= to_be_transformed_into_in_subq(thd))) + thd->lex->current_select->in_funcs.push_back(this, thd->mem_root); + transform_into_subq_checked= true; } if (arena) diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 4c88f5b..f3d3be4 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -2299,6 +2299,7 @@ class Item_func_in :public Item_func_opt_neg, SEL_TREE *get_func_mm_tree(RANGE_OPT_PARAM *param, Field *field, Item *value); bool transform_into_subq; + bool transform_into_subq_checked; public: /// An array of values, created when the bisection lookup method is used in_vector *array; @@ -2321,6 +2322,7 @@ class Item_func_in :public Item_func_opt_neg, Item_func_opt_neg(thd, list), Predicant_to_list_comparator(thd, arg_count - 1), transform_into_subq(false), + transform_into_subq_checked(false), array(0), have_null(0), arg_types_compatible(FALSE), emb_on_expr_nest(0) { } diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc index 3866b7c..13efd97 100644 --- a/sql/sql_tvc.cc +++ b/sql/sql_tvc.cc @@ -900,8 +900,6 @@ Item *Item_func_in::in_predicate_to_in_subs_transformer(THD *thd, if (!transform_into_subq) return this; - transform_into_subq= false; - List<List_item> values; LEX *lex= thd->lex; @@ -1058,15 +1056,38 @@ uint32 Item_func_in::max_length_of_left_expr() bool Item_func_in::to_be_transformed_into_in_subq(THD *thd) { + bool is_row_list= args[1]->type() == Item::ROW_ITEM; uint values_count= arg_count-1; - if (args[1]->type() == Item::ROW_ITEM) + if (is_row_list) values_count*= ((Item_row *)(args[1]))->cols(); if (thd->variables.in_subquery_conversion_threshold == 0 || thd->variables.in_subquery_conversion_threshold > values_count) return false; + if (!(thd->lex->context_analysis_only & CONTEXT_ANALYSIS_ONLY_PREPARE)) + return true; + + /* Occurence of '?' in IN list is checked only for PREPARE <stmt> commands */ + for (uint i=1; i < arg_count; i++) + { + if (!is_row_list) + { + if (args[i]->type() == Item::PARAM_ITEM) + return false; + } + else + { + Item_row *row_list= (Item_row *)(args[i]); + for (uint j=0; j < row_list->cols(); j++) + { + if (row_list->element_index(j)->type() == Item::PARAM_ITEM) + return false; + } + } + } + return true; }

1 0

[Commits] 8fff088: MDEV-24281 Reading from freed memory when running main.view with --ps-protocol
by IgorBabaev 23 Mar '22

23 Mar '22

revision-id: 8fff0886ddc95eb5f8dba60b8b859183f42bd494 (mariadb-10.3.26-356-g8fff088) parent(s): bfed2c7d57a7ca34936d6ef0688af7357592dc40 author: Igor Babaev committer: Igor Babaev timestamp: 2022-03-23 12:45:56 -0700 message: MDEV-24281 Reading from freed memory when running main.view with --ps-protocol This bug could affect prepared statements for the command CREATE VIEW with specification that contained unnamed basic constant in select list. If generation of a valid name for the corresponding view column required resolution of conflicts with names of other columns that were explicitly defined then execution of such prepared statement and following deallocation of this statement led to reading from freed memory. Approved by Oleksandr Byelkin <sanja(a)mariadb.com> --- mysql-test/main/view.result | 28 ++++++++++++++++++++++++++++ mysql-test/main/view.test | 26 ++++++++++++++++++++++++++ sql/sql_view.cc | 3 ++- 3 files changed, 56 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result index a410ab7..6483d76 100644 --- a/mysql-test/main/view.result +++ b/mysql-test/main/view.result @@ -6839,5 +6839,33 @@ id bar Drop View v1; Drop table t1; # +# MDEV-24281: Execution of PREPARE from CREATE VIEW statement +# +create table t1 (s1 int); +insert into t1 values (3), (7), (1); +prepare stmt from " +create view v1 as select 's1', s1, 1 as My_exp_s1 from t1; +"; +execute stmt; +deallocate prepare stmt; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 's1' AS `My_exp_1_s1`,`t1`.`s1` AS `s1`,1 AS `My_exp_s1` from `t1` latin1 latin1_swedish_ci +select * from v1; +My_exp_1_s1 s1 My_exp_s1 +s1 3 1 +s1 7 1 +s1 1 1 +drop view v1; +prepare stmt from " +create view v1 as select 's1', s1, 1 as My_exp_s1 from t1; +"; +execute stmt; +execute stmt; +ERROR 42S01: Table 'v1' already exists +deallocate prepare stmt; +drop view v1; +drop table t1; +# # End of 10.3 tests # diff --git a/mysql-test/main/view.test b/mysql-test/main/view.test index 431dfdb..46232b1 100644 --- a/mysql-test/main/view.test +++ b/mysql-test/main/view.test @@ -6577,5 +6577,31 @@ Drop View v1; Drop table t1; --echo # +--echo # MDEV-24281: Execution of PREPARE from CREATE VIEW statement +--echo # + +create table t1 (s1 int); +insert into t1 values (3), (7), (1); + +prepare stmt from " +create view v1 as select 's1', s1, 1 as My_exp_s1 from t1; +"; +execute stmt; +deallocate prepare stmt; +show create view v1; +select * from v1; +drop view v1; + +prepare stmt from " +create view v1 as select 's1', s1, 1 as My_exp_s1 from t1; +"; +execute stmt; +--error ER_TABLE_EXISTS_ERROR +execute stmt; +deallocate prepare stmt; +drop view v1; +drop table t1; + +--echo # --echo # End of 10.3 tests --echo # diff --git a/sql/sql_view.cc b/sql/sql_view.cc index 024bd36..b6787a1 100644 --- a/sql/sql_view.cc +++ b/sql/sql_view.cc @@ -96,7 +96,8 @@ static void make_unique_view_field_name(THD *thd, Item *target, itc.rewind(); } - target->orig_name= target->name.str; + if (!target->orig_name) + target->orig_name= target->name.str; target->set_name(thd, buff, name_len, system_charset_info); }

1 0

[Commits] 260d13f0fb4: Update test results (8)
by psergey 22 Mar '22

22 Mar '22

revision-id: 260d13f0fb40822d259fab76c6f9127d0c3e0167 (mariadb-10.6.1-373-g260d13f0fb4) parent(s): 304a2a6ca6a906afaf60c2650b21c2de7a06df60 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-03-22 10:07:39 +0300 message: Update test results (8) --- mysql-test/suite/maria/mrr.result | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/mysql-test/suite/maria/mrr.result b/mysql-test/suite/maria/mrr.result index 61450c948bf..2047febb92a 100644 --- a/mysql-test/suite/maria/mrr.result +++ b/mysql-test/suite/maria/mrr.result @@ -187,7 +187,7 @@ explain select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1' or c='no-such-row2'); id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t4 NULL range idx1 idx1 29 NULL 16 2.86 Using index condition; Rowid-ordered scan +1 SIMPLE t4 NULL range idx1 idx1 29 NULL 16 FLTRD Using index condition; Rowid-ordered scan select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1' or c='no-such-row2'); a b c filler

1 0

[Commits] 304a2a6ca6a: Update test results (7)
by psergey 22 Mar '22

22 Mar '22

revision-id: 304a2a6ca6a906afaf60c2650b21c2de7a06df60 (mariadb-10.6.1-372-g304a2a6ca6a) parent(s): 7ba842d1e8d81ca99887e500187ec6dc6964c858 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-03-22 07:56:44 +0300 message: Update test results (7) --- mysql-test/include/mrr_tests.inc | 1 + mysql-test/main/myisam_mrr.result | 2 +- mysql-test/main/xtradb_mrr.result | 2 +- mysql-test/suite/innodb/r/innodb_mysql.result | 2 +- mysql-test/suite/innodb/t/innodb_mysql.test | 1 + 5 files changed, 5 insertions(+), 3 deletions(-) diff --git a/mysql-test/include/mrr_tests.inc b/mysql-test/include/mrr_tests.inc index ad7dff61477..a50cc8c16f0 100644 --- a/mysql-test/include/mrr_tests.inc +++ b/mysql-test/include/mrr_tests.inc @@ -89,6 +89,7 @@ insert into t4 (a,b,c,filler) insert into t4 (a,b,c,filler) select 'zz-1',NULL,'cc-2', 'filler-data' from t2 order by a limit 500; +--replace_column 11 FLTRD explain select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1' or c='no-such-row2'); diff --git a/mysql-test/main/myisam_mrr.result b/mysql-test/main/myisam_mrr.result index 57e6a443940..37ad7565a1b 100644 --- a/mysql-test/main/myisam_mrr.result +++ b/mysql-test/main/myisam_mrr.result @@ -189,7 +189,7 @@ explain select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1' or c='no-such-row2'); id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t4 NULL range idx1 idx1 29 NULL 10 1.79 Using index condition; Rowid-ordered scan +1 SIMPLE t4 NULL range idx1 idx1 29 NULL 10 FLTRD Using index condition; Rowid-ordered scan select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1' or c='no-such-row2'); a b c filler diff --git a/mysql-test/main/xtradb_mrr.result b/mysql-test/main/xtradb_mrr.result index ceb98f2c7d5..015d1e61753 100644 --- a/mysql-test/main/xtradb_mrr.result +++ b/mysql-test/main/xtradb_mrr.result @@ -186,7 +186,7 @@ explain select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1' or c='no-such-row2'); id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t4 NULL range idx1 idx1 29 NULL 16 2.86 Using index condition; Rowid-ordered scan +1 SIMPLE t4 NULL range idx1 idx1 29 NULL 16 FLTRD Using index condition; Rowid-ordered scan select * from t4 where a IS NULL and b IS NULL and (c IS NULL or c='no-such-row1' or c='no-such-row2'); a b c filler diff --git a/mysql-test/suite/innodb/r/innodb_mysql.result b/mysql-test/suite/innodb/r/innodb_mysql.result index 7a2dfa7eec8..0bdf7965b0e 100644 --- a/mysql-test/suite/innodb/r/innodb_mysql.result +++ b/mysql-test/suite/innodb/r/innodb_mysql.result @@ -3373,7 +3373,7 @@ SELECT v2 FROM t1 WHERE v1 IN ('f', 'd', 'h', 'u' ) AND i = 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 NULL ref i,v i 5 const 2 100.00 Using where +1 SIMPLE t1 NULL ref i,v i 5 const 2 FLTRD Using where DROP TABLE t1; # # Bug#54606 innodb fast alter table + pack_keys=0 diff --git a/mysql-test/suite/innodb/t/innodb_mysql.test b/mysql-test/suite/innodb/t/innodb_mysql.test index d495186db25..ae681fc8631 100644 --- a/mysql-test/suite/innodb/t/innodb_mysql.test +++ b/mysql-test/suite/innodb/t/innodb_mysql.test @@ -1015,6 +1015,7 @@ WHERE v1 IN ('f', 'd', 'h', 'u' ) AND i = 2; --echo --echo # Should not use index_merge +--replace_column 11 FLTRD EXPLAIN SELECT v2 FROM t1

1 0

[Commits] 390051a: MDEV-24281 Reading from freed memory when running main.view with --ps-protocol
by IgorBabaev 22 Mar '22

22 Mar '22

revision-id: 390051add0fd3fba3aae9e2f1795ec73833f792a (mariadb-10.3.26-356-g390051a) parent(s): bfed2c7d57a7ca34936d6ef0688af7357592dc40 author: Igor Babaev committer: Igor Babaev timestamp: 2022-03-21 20:00:24 -0700 message: MDEV-24281 Reading from freed memory when running main.view with --ps-protocol This bug could affect prepared statements for the command CREATE VIEW with specification that contained unnamed basic constant in select list. If generation of a valid name for the corresponding view column required resolution of conflicts with names of other columns that were explicitly defined then execution of such prepared statement and following deallocation of this statement led to reading from freed memory. --- mysql-test/main/view.result | 28 ++++++++++++++++++++++++++++ mysql-test/main/view.test | 26 ++++++++++++++++++++++++++ sql/sql_view.cc | 3 ++- 3 files changed, 56 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result index a410ab7..6483d76 100644 --- a/mysql-test/main/view.result +++ b/mysql-test/main/view.result @@ -6839,5 +6839,33 @@ id bar Drop View v1; Drop table t1; # +# MDEV-24281: Execution of PREPARE from CREATE VIEW statement +# +create table t1 (s1 int); +insert into t1 values (3), (7), (1); +prepare stmt from " +create view v1 as select 's1', s1, 1 as My_exp_s1 from t1; +"; +execute stmt; +deallocate prepare stmt; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select 's1' AS `My_exp_1_s1`,`t1`.`s1` AS `s1`,1 AS `My_exp_s1` from `t1` latin1 latin1_swedish_ci +select * from v1; +My_exp_1_s1 s1 My_exp_s1 +s1 3 1 +s1 7 1 +s1 1 1 +drop view v1; +prepare stmt from " +create view v1 as select 's1', s1, 1 as My_exp_s1 from t1; +"; +execute stmt; +execute stmt; +ERROR 42S01: Table 'v1' already exists +deallocate prepare stmt; +drop view v1; +drop table t1; +# # End of 10.3 tests # diff --git a/mysql-test/main/view.test b/mysql-test/main/view.test index 431dfdb..46232b1 100644 --- a/mysql-test/main/view.test +++ b/mysql-test/main/view.test @@ -6577,5 +6577,31 @@ Drop View v1; Drop table t1; --echo # +--echo # MDEV-24281: Execution of PREPARE from CREATE VIEW statement +--echo # + +create table t1 (s1 int); +insert into t1 values (3), (7), (1); + +prepare stmt from " +create view v1 as select 's1', s1, 1 as My_exp_s1 from t1; +"; +execute stmt; +deallocate prepare stmt; +show create view v1; +select * from v1; +drop view v1; + +prepare stmt from " +create view v1 as select 's1', s1, 1 as My_exp_s1 from t1; +"; +execute stmt; +--error ER_TABLE_EXISTS_ERROR +execute stmt; +deallocate prepare stmt; +drop view v1; +drop table t1; + +--echo # --echo # End of 10.3 tests --echo # diff --git a/sql/sql_view.cc b/sql/sql_view.cc index 024bd36..b6787a1 100644 --- a/sql/sql_view.cc +++ b/sql/sql_view.cc @@ -96,7 +96,8 @@ static void make_unique_view_field_name(THD *thd, Item *target, itc.rewind(); } - target->orig_name= target->name.str; + if (!target->orig_name) + target->orig_name= target->name.str; target->set_name(thd, buff, name_len, system_charset_info); }

1 0

[Commits] 7ba842d1e8d: Update test results (6)
by psergey 21 Mar '22

21 Mar '22

revision-id: 7ba842d1e8d81ca99887e500187ec6dc6964c858 (mariadb-10.6.1-371-g7ba842d1e8d) parent(s): b6ff2b590519ab141fca0cd597abf0ffbde0ac14 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-03-21 19:41:00 +0300 message: Update test results (6) --- mysql-test/main/named_pipe.result | 110 +++++++++++++++++++------------------- 1 file changed, 55 insertions(+), 55 deletions(-) diff --git a/mysql-test/main/named_pipe.result b/mysql-test/main/named_pipe.result index e512e2a0f5f..903afc4053a 100644 --- a/mysql-test/main/named_pipe.result +++ b/mysql-test/main/named_pipe.result @@ -1360,82 +1360,82 @@ select count(*) from t2 left join t4 using (companynr) where t4.companynr is not count(*) 1199 explain select t2.companynr,companyname from t2 left join t4 using (companynr) where t4.companynr is null; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1200 -1 SIMPLE t4 NULL eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 Using where; Not exists +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1200 100.00 +1 SIMPLE t4 NULL eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 100.00 Using where; Not exists explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr is null; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t4 NULL ALL NULL NULL NULL NULL 12 -1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1200 Using where; Not exists +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t4 NULL ALL NULL NULL NULL NULL 12 100.00 +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1200 100.00 Using where; Not exists select companynr,companyname from t2 left join t4 using (companynr) where companynr is null; companynr companyname select count(*) from t2 left join t4 using (companynr) where companynr is not null; count(*) 1200 explain select companynr,companyname from t2 left join t4 using (companynr) where companynr is null; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE explain select companynr,companyname from t4 left join t2 using (companynr) where companynr is null; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE delete from t2 where fld1=999999; explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 Using where -1 SIMPLE t4 NULL eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 100.00 Using where +1 SIMPLE t4 NULL eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 100.00 explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 Using where -1 SIMPLE t4 NULL eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 100.00 Using where +1 SIMPLE t4 NULL eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 100.00 explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 and t4.companynr > 0; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 Using where -1 SIMPLE t4 NULL eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 100.00 Using where +1 SIMPLE t4 NULL eq_ref PRIMARY PRIMARY 1 test.t2.companynr 1 100.00 explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t4 NULL ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t4 NULL ALL PRIMARY NULL NULL NULL 12 91.67 Using where +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 100.00 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t4 NULL ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t4 NULL ALL PRIMARY NULL NULL NULL 12 100.00 Using where +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 100.00 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 and companynr > 0; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t4 NULL ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t4 NULL ALL PRIMARY NULL NULL NULL 12 91.67 Using where +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 100.00 Using where explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr is null; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t4 NULL ALL NULL NULL NULL NULL 12 -1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t4 NULL ALL NULL NULL NULL NULL 12 100.00 +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 100.00 Using where explain select t2.companynr,companyname from t4 left join t2 using (companynr) where t2.companynr > 0 or t2.companynr < 0 or t4.companynr > 0; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t4 NULL ALL PRIMARY NULL NULL NULL 12 -1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t4 NULL ALL PRIMARY NULL NULL NULL 12 100.00 +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 100.00 Using where explain select t2.companynr,companyname from t4 left join t2 using (companynr) where ifnull(t2.companynr,1)>0; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t4 NULL ALL NULL NULL NULL NULL 12 -1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t4 NULL ALL NULL NULL NULL NULL 12 100.00 +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 100.00 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr is null; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t4 NULL ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t4 NULL ALL PRIMARY NULL NULL NULL 12 91.67 Using where +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 100.00 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where companynr > 0 or companynr < 0 or companynr > 0; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t4 NULL ALL PRIMARY NULL NULL NULL 12 Using where -1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t4 NULL ALL PRIMARY NULL NULL NULL 12 100.00 Using where +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 100.00 Using where explain select companynr,companyname from t4 left join t2 using (companynr) where ifnull(companynr,1)>0; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t4 NULL ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 Using where +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t4 NULL ALL NULL NULL NULL NULL 12 100.00 Using where +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 100.00 Using where SET @@optimizer_switch=@local_optimizer_switch; select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; companynr companynr 37 36 41 40 explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t4 NULL index NULL PRIMARY 1 NULL 12 Using index; Using temporary -1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 Using where; Using join buffer (flat, BNL join) +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t4 NULL index NULL PRIMARY 1 NULL 12 100.00 Using index; Using temporary +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 100.00 Using where; Using join buffer (flat, BNL join) SET @@join_cache_level=@local_join_cache_level; select t2.fld1,t2.companynr,fld3,period from t3,t2 where t2.fld1 = 38208 and t2.fld1=t3.t2nr and period = 1008 or t2.fld1 = 38008 and t2.fld1 =t3.t2nr and period = 1008; fld1 companynr fld3 period @@ -1950,11 +1950,11 @@ select t2.fld1,sum(price) from t3,t2 where t2.fld1 = t3.t2nr and t3.companynr = fld1 sum(price) 038008 234298 explain select fld3 from t2 where 1>2 or 2>3; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE explain select fld3 from t2 where fld1=fld1; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 1199 100.00 select companynr,fld1 from t2 HAVING fld1=250501 or fld1=250502; companynr fld1 34 250501 @@ -2005,8 +2005,8 @@ select count(*) from t3 where companynr=512 and price2=76234234; count(*) 4181 explain select min(fld1),max(fld1),count(*) from t2; -id select_type table partitions type possible_keys key key_len ref rows Extra -1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away select min(fld1),max(fld1),count(*) from t2; min(fld1) max(fld1) count(*) 0 1232609 1199

1 0

[Commits] b6ff2b59051: Update test results (5)
by psergey 21 Mar '22

21 Mar '22

revision-id: b6ff2b590519ab141fca0cd597abf0ffbde0ac14 (mariadb-10.6.1-370-gb6ff2b59051) parent(s): f00c21ce9785f2ebffd7e500773d29dfe7d45768 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-03-21 18:07:48 +0300 message: Update test results (5) --- mysql-test/include/index_merge_ror_cpk.inc | 1 + mysql-test/main/index_merge_innodb.result | 2 +- mysql-test/main/index_merge_myisam.result | 2 +- mysql-test/main/join_cache.result | 286 ++++++++++++++--------------- mysql-test/main/join_cache.test | 64 ++++++- mysql-test/main/myisam_icp.result | 2 +- mysql-test/suite/maria/icp.result | 2 +- 7 files changed, 210 insertions(+), 149 deletions(-) diff --git a/mysql-test/include/index_merge_ror_cpk.inc b/mysql-test/include/index_merge_ror_cpk.inc index c2da93cf383..59c7a6194a3 100644 --- a/mysql-test/include/index_merge_ror_cpk.inc +++ b/mysql-test/include/index_merge_ror_cpk.inc @@ -60,6 +60,7 @@ commit; # Verify that range scan on CPK is ROR # (use index_intersection because it is impossible to check that for index union) +--replace_column 11 FLTRD explain select * from t1 where pk1 = 1 and pk2 < 80 and key1=0; # CPK scan + 1 ROR range scan is a special case select * from t1 where pk1 = 1 and pk2 < 80 and key1=0; diff --git a/mysql-test/main/index_merge_innodb.result b/mysql-test/main/index_merge_innodb.result index 0d1e20b6860..4eac7afa1fd 100644 --- a/mysql-test/main/index_merge_innodb.result +++ b/mysql-test/main/index_merge_innodb.result @@ -547,7 +547,7 @@ primary key (pk1, pk2) ); explain select * from t1 where pk1 = 1 and pk2 < 80 and key1=0; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 NULL range PRIMARY,key1 PRIMARY 8 NULL 10 0.10 Using where +1 SIMPLE t1 NULL range PRIMARY,key1 PRIMARY 8 NULL 10 FLTRD Using where select * from t1 where pk1 = 1 and pk2 < 80 and key1=0; pk1 pk2 key1 key2 pktail1ok pktail2ok pktail3bad pktail4bad pktail5bad pk2copy badkey filler1 filler2 1 10 0 0 0 0 0 0 0 10 0 filler-data-10 filler2 diff --git a/mysql-test/main/index_merge_myisam.result b/mysql-test/main/index_merge_myisam.result index f9234230b82..1eec57a9834 100644 --- a/mysql-test/main/index_merge_myisam.result +++ b/mysql-test/main/index_merge_myisam.result @@ -1379,7 +1379,7 @@ primary key (pk1, pk2) ); explain select * from t1 where pk1 = 1 and pk2 < 80 and key1=0; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 NULL range PRIMARY,key1 PRIMARY 8 NULL 7 0.30 Using index condition; Using where +1 SIMPLE t1 NULL range PRIMARY,key1 PRIMARY 8 NULL 7 FLTRD Using index condition; Using where select * from t1 where pk1 = 1 and pk2 < 80 and key1=0; pk1 pk2 key1 key2 pktail1ok pktail2ok pktail3bad pktail4bad pktail5bad pk2copy badkey filler1 filler2 1 10 0 0 0 0 0 0 0 10 0 filler-data-10 filler2 diff --git a/mysql-test/main/join_cache.result b/mysql-test/main/join_cache.result index 02c87eb8195..3e62b54bf5d 100644 --- a/mysql-test/main/join_cache.result +++ b/mysql-test/main/join_cache.result @@ -58,8 +58,8 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 3.77 Using where -1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 87.23 Using where; Using join buffer (flat, BNL join) +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 FLTRD Using where; Using join buffer (flat, BNL join) SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -88,9 +88,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 55.23 Using where -1 SIMPLE CountryLanguage NULL ALL NULL NULL NULL NULL 984 17.09 Using where; Using join buffer (flat, BNL join) -1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 4.17 Using where; Using join buffer (flat, BNL join) +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE CountryLanguage NULL ALL NULL NULL NULL NULL 984 FLTRD Using where; Using join buffer (flat, BNL join) +1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 FLTRD Using where; Using join buffer (flat, BNL join) SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -136,8 +136,8 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 3.77 Using where -1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 87.23 Using where; Using join buffer (flat, BNL join) +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 FLTRD Using where; Using join buffer (flat, BNL join) SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -166,9 +166,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 55.23 Using where -1 SIMPLE CountryLanguage NULL ALL NULL NULL NULL NULL 984 17.09 Using where; Using join buffer (flat, BNL join) -1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 4.17 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE CountryLanguage NULL ALL NULL NULL NULL NULL 984 FLTRD Using where; Using join buffer (flat, BNL join) +1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 FLTRD Using where; Using join buffer (incremental, BNL join) SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -292,8 +292,8 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 3.77 Using where -1 SIMPLE City NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 87.23 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE City NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 FLTRD Using where; Using join buffer (flat, BNLH join) SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -322,9 +322,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 55.23 Using where -1 SIMPLE CountryLanguage NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 984 17.09 Using where; Using join buffer (flat, BNLH join) -1 SIMPLE City NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 4.17 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE CountryLanguage NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 984 FLTRD Using where; Using join buffer (flat, BNLH join) +1 SIMPLE City NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 FLTRD Using where; Using join buffer (incremental, BNLH join) SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -410,8 +410,8 @@ FROM Country LEFT JOIN City ON City.Country=Country.Code AND City.Population > 5000000 WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 3.00 Using where -1 SIMPLE City NULL hash_range City_Population #hash#$hj:City_Population 3:4 world.Country.Code 24 0.59 Using where; Rowid-ordered scan; Using join buffer (flat, BNLH join) +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE City NULL hash_range City_Population #hash#$hj:City_Population 3:4 world.Country.Code 24 FLTRD Using where; Rowid-ordered scan; Using join buffer (flat, BNLH join) SELECT Country.Name, Country.Population, City.Name, City.Population FROM Country LEFT JOIN City ON City.Country=Country.Code AND City.Population > 5000000 @@ -437,8 +437,8 @@ ON City.Country=Country.Code AND (City.Population > 5000000 OR City.Name LIKE 'Za%') WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 3.00 Using where -1 SIMPLE City NULL hash_index_merge City_Population,City_Name #hash#$hj:City_Population,City_Name 3:4,35 world.Country.Code 96 2.35 Using sort_union(City_Population,City_Name); Using where; Using join buffer (flat, BNLH join) +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE City NULL hash_index_merge City_Population,City_Name #hash#$hj:City_Population,City_Name 3:4,35 world.Country.Code 96 FLTRD Using sort_union(City_Population,City_Name); Using where; Using join buffer (flat, BNLH join) SELECT Country.Name, Country.Population, City.Name, City.Population FROM Country LEFT JOIN City ON City.Country=Country.Code AND @@ -476,8 +476,8 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 3.77 Using where -1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 87.23 Using where; Using join buffer (flat, BNL join) +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 FLTRD Using where; Using join buffer (flat, BNL join) SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -506,9 +506,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 55.23 Using where -1 SIMPLE CountryLanguage NULL ALL NULL NULL NULL NULL 984 17.09 Using where; Using join buffer (flat, BNL join) -1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 4.17 Using where; Using join buffer (flat, BNL join) +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE CountryLanguage NULL ALL NULL NULL NULL NULL 984 FLTRD Using where; Using join buffer (flat, BNL join) +1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 FLTRD Using where; Using join buffer (flat, BNL join) SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -554,8 +554,8 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 3.77 Using where -1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 87.23 Using where; Using join buffer (flat, BNL join) +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 FLTRD Using where; Using join buffer (flat, BNL join) SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -584,9 +584,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 55.23 Using where -1 SIMPLE CountryLanguage NULL ALL NULL NULL NULL NULL 984 17.09 Using where; Using join buffer (flat, BNL join) -1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 4.17 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE CountryLanguage NULL ALL NULL NULL NULL NULL 984 FLTRD Using where; Using join buffer (flat, BNL join) +1 SIMPLE City NULL ALL NULL NULL NULL NULL 4079 FLTRD Using where; Using join buffer (incremental, BNL join) SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -632,8 +632,8 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 3.77 Using where -1 SIMPLE City NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 87.23 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE City NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 FLTRD Using where; Using join buffer (flat, BNLH join) SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -662,9 +662,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 55.23 Using where -1 SIMPLE CountryLanguage NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 984 17.09 Using where; Using join buffer (flat, BNLH join) -1 SIMPLE City NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 4.17 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE CountryLanguage NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 984 FLTRD Using where; Using join buffer (flat, BNLH join) +1 SIMPLE City NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 FLTRD Using where; Using join buffer (flat, BNLH join) SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -710,8 +710,8 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 3.77 Using where -1 SIMPLE City NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 87.23 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE City NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 FLTRD Using where; Using join buffer (flat, BNLH join) SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -740,9 +740,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 55.23 Using where -1 SIMPLE CountryLanguage NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 984 17.09 Using where; Using join buffer (flat, BNLH join) -1 SIMPLE City NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 4.17 Using where; Using join buffer (incremental, BNLH join) +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE CountryLanguage NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 984 FLTRD Using where; Using join buffer (flat, BNLH join) +1 SIMPLE City NULL hash_ALL NULL #hash#$hj 3 world.Country.Code 4079 FLTRD Using where; Using join buffer (incremental, BNLH join) SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -821,8 +821,8 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 SIMPLE City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 87.60 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 SIMPLE City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 FLTRD Using where; Using join buffer (flat, BNLH join) SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -851,9 +851,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 4.17 Using where -1 SIMPLE Country NULL hash_ALL PRIMARY #hash#PRIMARY 3 world.City.Country 239 55.23 Using where; Using join buffer (flat, BNLH join) -1 SIMPLE CountryLanguage NULL hash_ALL|filter PRIMARY,Percentage #hash#PRIMARY|Percentage 3|4 world.City.Country 984 (19%) 18.80 Using where; Using join buffer (flat, BNLH join); Using rowid filter +1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 FLTRD Using where +1 SIMPLE Country NULL hash_ALL PRIMARY #hash#PRIMARY 3 world.City.Country 239 FLTRD Using where; Using join buffer (flat, BNLH join) +1 SIMPLE CountryLanguage NULL hash_ALL|filter PRIMARY,Percentage #hash#PRIMARY|Percentage 3|4 world.City.Country 984 (19%) FLTRD Using where; Using join buffer (flat, BNLH join); Using rowid filter SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -923,8 +923,8 @@ FROM Country LEFT JOIN CountryLanguage ON WHERE Country.Population > 10000000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 32.64 Using where -1 SIMPLE CountryLanguage NULL hash_ALL PRIMARY #hash#PRIMARY 33 world.Country.Code,const 984 0.10 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE CountryLanguage NULL hash_ALL PRIMARY #hash#PRIMARY 33 world.Country.Code,const 984 FLTRD Using where; Using join buffer (flat, BNLH join) SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) FROM Country LEFT JOIN CountryLanguage ON (CountryLanguage.Country=Country.Code AND Language='English') @@ -1021,8 +1021,8 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 SIMPLE City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 87.60 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 SIMPLE City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 FLTRD Using where; Using join buffer (flat, BNLH join) SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -1051,9 +1051,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 4.17 Using where -1 SIMPLE Country NULL hash_ALL PRIMARY #hash#PRIMARY 3 world.City.Country 239 55.23 Using where; Using join buffer (flat, BNLH join) -1 SIMPLE CountryLanguage NULL hash_ALL|filter PRIMARY,Percentage #hash#PRIMARY|Percentage 3|4 world.City.Country 984 (19%) 18.80 Using where; Using join buffer (incremental, BNLH join); Using rowid filter +1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 FLTRD Using where +1 SIMPLE Country NULL hash_ALL PRIMARY #hash#PRIMARY 3 world.City.Country 239 FLTRD Using where; Using join buffer (flat, BNLH join) +1 SIMPLE CountryLanguage NULL hash_ALL|filter PRIMARY,Percentage #hash#PRIMARY|Percentage 3|4 world.City.Country 984 (19%) FLTRD Using where; Using join buffer (incremental, BNLH join); Using rowid filter SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -1095,8 +1095,8 @@ SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 PRIMARY City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 87.60 Using where; Using join buffer (flat, BNLH join) +1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 PRIMARY City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 FLTRD Using where; Using join buffer (flat, BNLH join) SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; @@ -1123,8 +1123,8 @@ FROM Country LEFT JOIN CountryLanguage ON WHERE Country.Population > 10000000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 32.64 Using where -1 SIMPLE CountryLanguage NULL hash_ALL PRIMARY #hash#PRIMARY 33 world.Country.Code,const 984 0.10 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE CountryLanguage NULL hash_ALL PRIMARY #hash#PRIMARY 33 world.Country.Code,const 984 FLTRD Using where; Using join buffer (flat, BNLH join) SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) FROM Country LEFT JOIN CountryLanguage ON (CountryLanguage.Country=Country.Code AND Language='English') @@ -1215,8 +1215,8 @@ FROM Country LEFT JOIN City ON City.Country=Country.Code AND City.Population > 5000000 WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL range Name Name 52 NULL # 32.64 Using index condition; Using where; Rowid-ordered scan -1 SIMPLE City NULL hash_range Population,Country #hash#Country:Population 3:4 world.Country.Code # 0.74 Using where; Rowid-ordered scan; Using join buffer (flat, BNLH join) +1 SIMPLE Country NULL range Name Name 52 NULL ROWS FLTRD Using index condition; Using where; Rowid-ordered scan +1 SIMPLE City NULL hash_range Population,Country #hash#Country:Population 3:4 world.Country.Code ROWS FLTRD Using where; Rowid-ordered scan; Using join buffer (flat, BNLH join) SELECT Country.Name, Country.Population, City.Name, City.Population FROM Country LEFT JOIN City ON City.Country=Country.Code AND City.Population > 5000000 @@ -1243,8 +1243,8 @@ ON City.Country=Country.Code AND (City.Population > 5000000 OR City.Name LIKE 'Za%') WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL range Name Name 52 NULL 17 32.64 Using index condition; Using where; Rowid-ordered scan -1 SIMPLE City NULL hash_index_merge Population,Country,City_Name #hash#Country:Population,City_Name 3:4,35 world.Country.Code 96 2.35 Using sort_union(Population,City_Name); Using where; Using join buffer (flat, BNLH join) +1 SIMPLE Country NULL range Name Name 52 NULL 17 FLTRD Using index condition; Using where; Rowid-ordered scan +1 SIMPLE City NULL hash_index_merge Population,Country,City_Name #hash#Country:Population,City_Name 3:4,35 world.Country.Code 96 FLTRD Using sort_union(Population,City_Name); Using where; Using join buffer (flat, BNLH join) SELECT Country.Name, Country.Population, City.Name, City.Population FROM Country LEFT JOIN City ON City.Country=Country.Code AND @@ -1280,8 +1280,8 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -1310,9 +1310,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 4.17 Using where -1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 55.23 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan -1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) 18.80 Using index condition(BKA); Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter +1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 FLTRD Using where +1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 FLTRD Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) FLTRD Using index condition(BKA); Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -1354,8 +1354,8 @@ SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; @@ -1382,8 +1382,8 @@ FROM Country LEFT JOIN CountryLanguage ON WHERE Country.Population > 10000000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 32.64 Using where -1 SIMPLE CountryLanguage NULL eq_ref PRIMARY PRIMARY 33 world.Country.Code,const 1 100.00 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE CountryLanguage NULL eq_ref PRIMARY PRIMARY 33 world.Country.Code,const 1 FLTRD Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) FROM Country LEFT JOIN CountryLanguage ON (CountryLanguage.Country=Country.Code AND Language='English') @@ -1477,8 +1477,8 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -1507,9 +1507,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 4.17 Using where -1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 55.23 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan -1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) 18.80 Using index condition(BKA); Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter +1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 FLTRD Using where +1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 FLTRD Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) FLTRD Using index condition(BKA); Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -1551,8 +1551,8 @@ SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; @@ -1579,8 +1579,8 @@ FROM Country LEFT JOIN CountryLanguage ON WHERE Country.Population > 10000000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 32.64 Using where -1 SIMPLE CountryLanguage NULL eq_ref PRIMARY PRIMARY 33 world.Country.Code,const 1 100.00 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE CountryLanguage NULL eq_ref PRIMARY PRIMARY 33 world.Country.Code,const 1 FLTRD Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) FROM Country LEFT JOIN CountryLanguage ON (CountryLanguage.Country=Country.Code AND Language='English') @@ -1674,8 +1674,8 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan +1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -1704,9 +1704,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 4.17 Using where -1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 55.23 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan -1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) 18.80 Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan; Using rowid filter +1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 FLTRD Using where +1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 FLTRD Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan +1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) FLTRD Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan; Using rowid filter SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -1748,8 +1748,8 @@ SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan +1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; @@ -1776,8 +1776,8 @@ FROM Country LEFT JOIN CountryLanguage ON WHERE Country.Population > 10000000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 32.64 Using where -1 SIMPLE CountryLanguage NULL eq_ref PRIMARY PRIMARY 33 world.Country.Code,const 1 100.00 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE CountryLanguage NULL eq_ref PRIMARY PRIMARY 33 world.Country.Code,const 1 FLTRD Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) FROM Country LEFT JOIN CountryLanguage ON (CountryLanguage.Country=Country.Code AND Language='English') @@ -1871,8 +1871,8 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan +1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -1901,9 +1901,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 4.17 Using where -1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 55.23 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan -1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) 18.80 Using index condition(BKA); Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan; Using rowid filter +1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 FLTRD Using where +1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 FLTRD Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan +1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) FLTRD Using index condition(BKA); Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan; Using rowid filter SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -1945,8 +1945,8 @@ SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan +1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; @@ -1973,8 +1973,8 @@ FROM Country LEFT JOIN CountryLanguage ON WHERE Country.Population > 10000000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 32.64 Using where -1 SIMPLE CountryLanguage NULL eq_ref PRIMARY PRIMARY 33 world.Country.Code,const 1 100.00 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan +1 SIMPLE Country NULL ALL NULL NULL NULL NULL 239 FLTRD Using where +1 SIMPLE CountryLanguage NULL eq_ref PRIMARY PRIMARY 33 world.Country.Code,const 1 FLTRD Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) FROM Country LEFT JOIN CountryLanguage ON (CountryLanguage.Country=Country.Code AND Language='English') @@ -2072,8 +2072,8 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 SIMPLE City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 87.23 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 SIMPLE City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 FLTRD Using where; Using join buffer (flat, BNLH join) SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -2102,9 +2102,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 4.17 Using where -1 SIMPLE Country NULL hash_ALL PRIMARY #hash#PRIMARY 3 world.City.Country 239 55.23 Using where; Using join buffer (flat, BNLH join) -1 SIMPLE CountryLanguage NULL hash_ALL|filter PRIMARY,Percentage #hash#PRIMARY|Percentage 3|4 world.City.Country 984 (19%) 18.80 Using where; Using join buffer (flat, BNLH join); Using rowid filter +1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 FLTRD Using where +1 SIMPLE Country NULL hash_ALL PRIMARY #hash#PRIMARY 3 world.City.Country 239 FLTRD Using where; Using join buffer (flat, BNLH join) +1 SIMPLE CountryLanguage NULL hash_ALL|filter PRIMARY,Percentage #hash#PRIMARY|Percentage 3|4 world.City.Country 984 (19%) FLTRD Using where; Using join buffer (flat, BNLH join); Using rowid filter SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -2146,8 +2146,8 @@ SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 PRIMARY City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 87.23 Using where; Using join buffer (flat, BNLH join) +1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 PRIMARY City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 FLTRD Using where; Using join buffer (flat, BNLH join) SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; @@ -2176,8 +2176,8 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 SIMPLE City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 87.23 Using where; Using join buffer (flat, BNLH join) +1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 SIMPLE City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 FLTRD Using where; Using join buffer (flat, BNLH join) SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -2206,9 +2206,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 4.17 Using where -1 SIMPLE Country NULL hash_ALL PRIMARY #hash#PRIMARY 3 world.City.Country 239 55.23 Using where; Using join buffer (flat, BNLH join) -1 SIMPLE CountryLanguage NULL hash_ALL|filter PRIMARY,Percentage #hash#PRIMARY|Percentage 3|4 world.City.Country 984 (19%) 18.80 Using where; Using join buffer (incremental, BNLH join); Using rowid filter +1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 FLTRD Using where +1 SIMPLE Country NULL hash_ALL PRIMARY #hash#PRIMARY 3 world.City.Country 239 FLTRD Using where; Using join buffer (flat, BNLH join) +1 SIMPLE CountryLanguage NULL hash_ALL|filter PRIMARY,Percentage #hash#PRIMARY|Percentage 3|4 world.City.Country 984 (19%) FLTRD Using where; Using join buffer (incremental, BNLH join); Using rowid filter SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -2250,8 +2250,8 @@ SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 PRIMARY City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 87.23 Using where; Using join buffer (flat, BNLH join) +1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 PRIMARY City NULL hash_ALL Population,Country #hash#Country 3 world.Country.Code 4079 FLTRD Using where; Using join buffer (flat, BNLH join) SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; @@ -2280,8 +2280,8 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -2310,9 +2310,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 4.17 Using where -1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 55.23 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan -1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) 18.80 Using index condition(BKA); Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter +1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 FLTRD Using where +1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 FLTRD Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) FLTRD Using index condition(BKA); Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -2354,8 +2354,8 @@ SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; @@ -2384,8 +2384,8 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -2414,9 +2414,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 4.17 Using where -1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 55.23 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan -1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) 18.80 Using index condition(BKA); Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter +1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 FLTRD Using where +1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 FLTRD Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) FLTRD Using index condition(BKA); Using where; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan; Using rowid filter SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -2458,8 +2458,8 @@ SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan +1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; @@ -2488,8 +2488,8 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan +1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -2518,9 +2518,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 4.17 Using where -1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 55.23 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan -1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) 18.80 Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan; Using rowid filter +1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 FLTRD Using where +1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 FLTRD Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan +1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) FLTRD Using index condition(BKA); Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan; Using rowid filter SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -2562,8 +2562,8 @@ SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan +1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; @@ -2592,8 +2592,8 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan +1 SIMPLE Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 SIMPLE City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; @@ -2622,9 +2622,9 @@ City.Name LIKE 'L%' AND Country.Population > 3000000 AND CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 4.17 Using where -1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 55.23 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan -1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) 18.80 Using index condition(BKA); Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan; Using rowid filter +1 SIMPLE City NULL ALL Country NULL NULL NULL 4079 FLTRD Using where +1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country 1 FLTRD Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan +1 SIMPLE CountryLanguage NULL ref|filter PRIMARY,Percentage PRIMARY|Percentage 3|4 world.City.Country 4 (19%) FLTRD Using index condition(BKA); Using where; Using join buffer (incremental, BKAH join); Key-ordered Rowid-ordered scan; Using rowid filter SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage WHERE City.Country=Country.Code AND @@ -2666,8 +2666,8 @@ SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 100.00 Using index condition; Rowid-ordered scan -1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 87.23 Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan +1 PRIMARY Country NULL range PRIMARY,Name Name 52 NULL 10 FLTRD Using index condition; Rowid-ordered scan +1 PRIMARY City NULL ref Population,Country Country 3 world.Country.Code 17 FLTRD Using where; Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; @@ -2745,8 +2745,8 @@ EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND City.Population > 3000000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE City NULL range Population,Country Population 4 NULL # 100.00 Using index condition; Rowid-ordered scan -1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country # 100.00 Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan +1 SIMPLE City NULL range Population,Country Population 4 NULL ROWS FLTRD Using index condition; Rowid-ordered scan +1 SIMPLE Country NULL eq_ref PRIMARY PRIMARY 3 world.City.Country ROWS FLTRD Using join buffer (flat, BKAH join); Key-ordered Rowid-ordered scan SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND City.Population > 3000000; Name Name @@ -2899,8 +2899,8 @@ FROM Country LEFT JOIN City ON City.Country=Country.Code AND City.Population > 5000000 WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL range Name Name 302 NULL 15 32.64 Using index condition; Using where; Rowid-ordered scan -1 SIMPLE City NULL hash_range Population,Country #hash#Country:Population 3:4 world.Country.Code 24 0.59 Using where; Rowid-ordered scan; Using join buffer (flat, BNLH join) +1 SIMPLE Country NULL range Name Name 302 NULL 15 FLTRD Using index condition; Using where; Rowid-ordered scan +1 SIMPLE City NULL hash_range Population,Country #hash#Country:Population 3:4 world.Country.Code 24 FLTRD Using where; Rowid-ordered scan; Using join buffer (flat, BNLH join) EXPLAIN SELECT Country.Name, Country.Population, City.Name, City.Population FROM Country LEFT JOIN City @@ -2908,8 +2908,8 @@ ON City.Country=Country.Code AND (City.Population > 5000000 OR City.Name LIKE 'Za%') WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE Country NULL range Name Name 302 NULL 15 32.64 Using index condition; Using where; Rowid-ordered scan -1 SIMPLE City NULL hash_index_merge Population,Country,City_Name #hash#Country:Population,City_Name 3:4,35 world.Country.Code 96 2.35 Using sort_union(Population,City_Name); Using where; Using join buffer (flat, BNLH join) +1 SIMPLE Country NULL range Name Name 302 NULL 15 FLTRD Using index condition; Using where; Rowid-ordered scan +1 SIMPLE City NULL hash_index_merge Population,Country,City_Name #hash#Country:Population,City_Name 3:4,35 world.Country.Code 96 FLTRD Using sort_union(Population,City_Name); Using where; Using join buffer (flat, BNLH join) set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; set @@use_stat_tables=@save_use_stat_tables; set @@join_cache_level=@save_join_cache_level; diff --git a/mysql-test/main/join_cache.test b/mysql-test/main/join_cache.test index 1bea4cfcbf8..4564b63cd5c 100644 --- a/mysql-test/main/join_cache.test +++ b/mysql-test/main/join_cache.test @@ -51,6 +51,7 @@ set join_cache_level=1; show variables like 'join_cache_level'; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND @@ -61,6 +62,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage @@ -82,6 +84,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language set join_cache_level=2; show variables like 'join_cache_level'; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND @@ -92,6 +95,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage @@ -148,6 +152,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language set join_cache_level=4; show variables like 'join_cache_level'; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND @@ -158,6 +163,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage @@ -195,6 +201,7 @@ CREATE INDEX City_Name ON City(Name); ANALYZE TABLE City; --enable_result_log +--replace_column 11 FLTRD EXPLAIN SELECT Country.Name, Country.Population, City.Name, City.Population FROM Country LEFT JOIN City @@ -206,6 +213,7 @@ SELECT Country.Name, Country.Population, City.Name, City.Population ON City.Country=Country.Code AND City.Population > 5000000 WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000; +--replace_column 11 FLTRD EXPLAIN SELECT Country.Name, Country.Population, City.Name, City.Population FROM Country LEFT JOIN City @@ -229,6 +237,7 @@ show variables like 'join_buffer_size'; show variables like 'join_cache_level'; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND @@ -239,6 +248,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage @@ -260,6 +270,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language set join_cache_level=2; show variables like 'join_cache_level'; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND @@ -270,6 +281,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage @@ -291,6 +303,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language set join_cache_level=3; show variables like 'join_cache_level'; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND @@ -301,6 +314,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage @@ -322,6 +336,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language set join_cache_level=4; show variables like 'join_cache_level'; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND @@ -332,6 +347,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage @@ -374,6 +390,7 @@ show variables like 'join_buffer_size'; set join_cache_level=3; show variables like 'join_cache_level'; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND @@ -384,6 +401,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage @@ -412,6 +430,7 @@ SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) FROM Country LEFT JOIN CountryLanguage ON @@ -429,6 +448,7 @@ show variables like 'join_buffer_size'; set join_cache_level=4; show variables like 'join_cache_level'; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND @@ -439,6 +459,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage @@ -457,6 +478,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; +--replace_column 11 FLTRD EXPLAIN SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND @@ -466,6 +488,7 @@ SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) FROM Country LEFT JOIN CountryLanguage ON @@ -480,7 +503,7 @@ SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.P Country.Population > 10000000; ---replace_column 10 # +--replace_column 10 ROWS 11 FLTRD EXPLAIN SELECT Country.Name, Country.Population, City.Name, City.Population FROM Country LEFT JOIN City @@ -494,6 +517,7 @@ SELECT Country.Name, Country.Population, City.Name, City.Population CREATE INDEX City_Name ON City(Name); +--replace_column 11 FLTRD EXPLAIN SELECT Country.Name, Country.Population, City.Name, City.Population FROM Country LEFT JOIN City @@ -513,6 +537,7 @@ show variables like 'join_buffer_size'; set join_cache_level=5; show variables like 'join_cache_level'; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND @@ -523,6 +548,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage @@ -541,6 +567,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; +--replace_column 11 FLTRD EXPLAIN SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND @@ -550,6 +577,7 @@ SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) FROM Country LEFT JOIN CountryLanguage ON @@ -566,6 +594,7 @@ SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.P set join_cache_level=6; show variables like 'join_cache_level'; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND @@ -576,6 +605,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage @@ -594,6 +624,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; +--replace_column 11 FLTRD EXPLAIN SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND @@ -603,6 +634,7 @@ SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) FROM Country LEFT JOIN CountryLanguage ON @@ -619,6 +651,7 @@ SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.P set join_cache_level=7; show variables like 'join_cache_level'; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND @@ -629,6 +662,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage @@ -647,6 +681,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; +--replace_column 11 FLTRD EXPLAIN SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND @@ -656,6 +691,7 @@ SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) FROM Country LEFT JOIN CountryLanguage ON @@ -672,6 +708,7 @@ SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.P set join_cache_level=8; show variables like 'join_cache_level'; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND @@ -682,6 +719,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage @@ -700,6 +738,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; +--replace_column 11 FLTRD EXPLAIN SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND @@ -709,6 +748,7 @@ SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT Country.Name, IF(ISNULL(CountryLanguage.Country), NULL, CountryLanguage.Percentage) FROM Country LEFT JOIN CountryLanguage ON @@ -728,6 +768,7 @@ show variables like 'join_buffer_size'; set join_cache_level=3; show variables like 'join_cache_level'; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND @@ -738,6 +779,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage @@ -756,6 +798,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; +--replace_column 11 FLTRD EXPLAIN SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND @@ -768,6 +811,7 @@ SELECT Name FROM City set join_cache_level=4; show variables like 'join_cache_level'; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND @@ -778,6 +822,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage @@ -796,6 +841,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; +--replace_column 11 FLTRD EXPLAIN SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND @@ -808,6 +854,7 @@ SELECT Name FROM City set join_cache_level=5; show variables like 'join_cache_level'; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND @@ -818,6 +865,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage @@ -836,6 +884,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; +--replace_column 11 FLTRD EXPLAIN SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND @@ -848,6 +897,7 @@ SELECT Name FROM City set join_cache_level=6; show variables like 'join_cache_level'; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND @@ -858,6 +908,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage @@ -876,6 +927,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; +--replace_column 11 FLTRD EXPLAIN SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND @@ -888,6 +940,7 @@ SELECT Name FROM City set join_cache_level=7; show variables like 'join_cache_level'; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND @@ -898,6 +951,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage @@ -916,6 +970,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; +--replace_column 11 FLTRD EXPLAIN SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND @@ -928,6 +983,7 @@ SELECT Name FROM City set join_cache_level=8; show variables like 'join_cache_level'; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND @@ -938,6 +994,7 @@ SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND Country.Name LIKE 'L%' AND City.Population > 100000; +--replace_column 11 FLTRD EXPLAIN SELECT City.Name, Country.Name, CountryLanguage.Language FROM City,Country,CountryLanguage @@ -956,6 +1013,7 @@ SELECT City.Name, Country.Name, CountryLanguage.Language CountryLanguage.Percentage > 50 AND LENGTH(Language) < LENGTH(City.Name) - 2; +--replace_column 11 FLTRD EXPLAIN SELECT Name FROM City WHERE City.Country IN (SELECT Code FROM Country WHERE Country.Name LIKE 'L%') AND @@ -977,7 +1035,7 @@ SELECT City.Name, Country.Name FROM City,Country set join_cache_level=8; set join_buffer_size=384; ---replace_column 10 # +--replace_column 10 ROWS 11 FLTRD EXPLAIN SELECT City.Name, Country.Name FROM City,Country WHERE City.Country=Country.Code AND City.Population > 3000000; @@ -1041,12 +1099,14 @@ CREATE INDEX City_Name ON City(Name); ANALYZE TABLE City, Country; --enable_result_log +--replace_column 11 FLTRD EXPLAIN SELECT Country.Name, Country.Population, City.Name, City.Population FROM Country LEFT JOIN City ON City.Country=Country.Code AND City.Population > 5000000 WHERE Country.Name LIKE 'C%' AND Country.Population > 10000000; +--replace_column 11 FLTRD EXPLAIN SELECT Country.Name, Country.Population, City.Name, City.Population FROM Country LEFT JOIN City diff --git a/mysql-test/main/myisam_icp.result b/mysql-test/main/myisam_icp.result index dba40aa6369..211773621c1 100644 --- a/mysql-test/main/myisam_icp.result +++ b/mysql-test/main/myisam_icp.result @@ -407,7 +407,7 @@ WHERE (pk BETWEEN 4 AND 5 OR pk < 2) AND c1 < 240 ORDER BY c1 LIMIT 1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 NULL range|filter PRIMARY,k1 PRIMARY|k1 4|5 NULL 3 (50%) 50.00 Using index condition; Using where; Rowid-ordered scan; Using filesort; Using rowid filter +1 SIMPLE t1 NULL range|filter PRIMARY,k1 PRIMARY|k1 4|5 NULL ROWS FLTRD Using index condition; Using where; Rowid-ordered scan; Using filesort; Using rowid filter DROP TABLE t1; # # diff --git a/mysql-test/suite/maria/icp.result b/mysql-test/suite/maria/icp.result index 7c0b9c701e0..8117293cd80 100644 --- a/mysql-test/suite/maria/icp.result +++ b/mysql-test/suite/maria/icp.result @@ -409,7 +409,7 @@ WHERE (pk BETWEEN 4 AND 5 OR pk < 2) AND c1 < 240 ORDER BY c1 LIMIT 1; id select_type table partitions type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 NULL range PRIMARY,k1 PRIMARY 4 NULL 3 50.00 Using index condition; Using where; Rowid-ordered scan; Using filesort +1 SIMPLE t1 NULL range PRIMARY,k1 PRIMARY 4 NULL ROWS FLTRD Using index condition; Using where; Rowid-ordered scan; Using filesort DROP TABLE t1; # #

1 0