From 7e66213444a5af73879b57ad0b5bd7476b5c6f4d Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Tue, 23 Aug 2011 19:28:32 +0400 Subject: MWL#182: Explain running statements First code - "Asynchronous procedure call" system - new THD::check_killed() that serves APC request is called from within most important loops - EXPLAIN code is now able to generate EXPLAIN output on-the-fly [incomplete] Parts that are still missing: - put THD::check_killed() call into every loop where we could spend significant amount of time - Make sure EXPLAIN code works for group-by queries that replace JOIN::join_tab with make_simple_join() and other such cases. - User interface: what error code to use, where to get timeout settings from, etc. --- libmysqld/Makefile.am | 2 +- sql/Makefile.am | 7 +- sql/my_apc.cc | 355 ++++++++++++++++++++++++++++++++++++++++++++++++++ sql/my_apc.h | 98 ++++++++++++++ sql/mysql_priv.h | 2 + sql/mysqld.cc | 1 + sql/protocol.h | 19 +++ sql/sp_head.cc | 1 + sql/sql_class.cc | 119 ++++++++++++++++- sql/sql_class.h | 94 ++++++++++++- sql/sql_lex.cc | 34 +++++ sql/sql_lex.h | 6 + sql/sql_parse.cc | 27 ++++ sql/sql_prepare.cc | 1 + sql/sql_select.cc | 81 ++++++++---- sql/sql_select.h | 7 +- sql/sql_show.cc | 91 +++++++++++++ sql/sql_yacc.yy | 6 + 18 files changed, 915 insertions(+), 36 deletions(-) create mode 100644 sql/my_apc.cc create mode 100644 sql/my_apc.h diff --git a/libmysqld/Makefile.am b/libmysqld/Makefile.am index bf2231f47a1..980de9a08ba 100644 --- a/libmysqld/Makefile.am +++ b/libmysqld/Makefile.am @@ -81,7 +81,7 @@ sqlsources = derror.cc field.cc field_conv.cc strfunc.cc filesort.cc \ rpl_injector.cc my_user.c partition_info.cc \ sql_servers.cc event_parse_data.cc opt_table_elimination.cc \ multi_range_read.cc opt_index_cond_pushdown.cc \ - sql_expression_cache.cc + sql_expression_cache.cc my_apc.cc # automake misses these sql_yacc.cc sql_yacc.h: $(top_srcdir)/sql/sql_yacc.yy diff --git a/sql/Makefile.am b/sql/Makefile.am index cde8962b8d0..8ef828dd243 100644 --- a/sql/Makefile.am +++ b/sql/Makefile.am @@ -84,7 +84,8 @@ noinst_HEADERS = item.h item_func.h item_sum.h item_cmpfunc.h \ multi_range_read.h sql_handler.h \ sql_join_cache.h \ create_options.h \ - sql_expression_cache.h + sql_expression_cache.h \ + my_apc.h mysqld_SOURCES = sql_lex.cc sql_handler.cc sql_partition.cc \ item.cc item_sum.cc item_buff.cc item_func.cc \ @@ -134,7 +135,9 @@ mysqld_SOURCES = sql_lex.cc sql_handler.cc sql_partition.cc \ sql_servers.cc event_parse_data.cc \ opt_table_elimination.cc create_options.cc \ multi_range_read.cc \ - opt_index_cond_pushdown.cc sql_expression_cache.cc + opt_index_cond_pushdown.cc sql_expression_cache.cc \ + my_apc.cc + nodist_mysqld_SOURCES = mini_client_errors.c pack.c client.c my_time.c my_user.c client_plugin.c diff --git a/sql/my_apc.cc b/sql/my_apc.cc new file mode 100644 index 00000000000..3842947f3bb --- /dev/null +++ b/sql/my_apc.cc @@ -0,0 +1,355 @@ +/* + TODO: MP AB Copyright +*/ + + +#ifdef MY_APC_STANDALONE + +#include +#include +#include + +#else + +#include "mysql_priv.h" + +#endif + +//#include "my_apc.h" + +/* + Standalone testing: + g++ -c -DMY_APC_STANDALONE -g -I.. -I../include -o my_apc.o my_apc.cc + g++ -L../mysys -L../dbug -L../strings my_apc.o -lmysys -ldbug -lmystrings -lpthread -lrt +*/ + + +void Apc_target::init() +{ + // todo: should use my_pthread_... functions instead? + DBUG_ASSERT(!enabled); + (void)pthread_mutex_init(&LOCK_apc_queue, MY_MUTEX_INIT_SLOW); +} + + +void Apc_target::destroy() +{ + DBUG_ASSERT(!enabled); + pthread_mutex_destroy(&LOCK_apc_queue); +} + + +void Apc_target::enable() +{ + pthread_mutex_lock(&LOCK_apc_queue); + enabled++; + pthread_mutex_unlock(&LOCK_apc_queue); +} + + +void Apc_target::disable() +{ + bool process= FALSE; + pthread_mutex_lock(&LOCK_apc_queue); + if (!(--enabled)) + process= TRUE; + pthread_mutex_unlock(&LOCK_apc_queue); + if (process) + process_apc_requests(); +} + +void Apc_target::enqueue_request(Call_request *qe) +{ + //call_queue_size++; + if (apc_calls) + { + Call_request *after= apc_calls->prev; + qe->next= apc_calls; + apc_calls->prev= qe; + + qe->prev= after; + after->next= qe; + } + else + { + apc_calls= qe; + qe->next= qe->prev= qe; + } +} + +void Apc_target::dequeue_request(Call_request *qe) +{ + //call_queue_size--; + if (apc_calls == qe) + { + if ((apc_calls= apc_calls->next) == qe) + { + //DBUG_ASSERT(!call_queue_size); + apc_calls= NULL; + } + } + + qe->prev->next= qe->next; + qe->next->prev= qe->prev; +} + + +/* + Make an apc call in another thread. The caller is responsible so + that we're not calling to ourselves. + +*/ + +bool Apc_target::make_apc_call(apc_func_t func, void *func_arg, + int timeout_sec, bool *timed_out) +{ + bool res= TRUE; + *timed_out= FALSE; + + pthread_mutex_lock(&LOCK_apc_queue); + if (enabled) + { + /* Create and post the request */ + Call_request apc_request; + apc_request.func= func; + apc_request.func_arg= func_arg; + apc_request.done= FALSE; + (void)pthread_cond_init(&apc_request.COND_request, NULL); + (void)pthread_mutex_init(&apc_request.LOCK_request, MY_MUTEX_INIT_SLOW); + pthread_mutex_lock(&apc_request.LOCK_request); + enqueue_request(&apc_request); + apc_request.what="enqueued by make_apc_call"; + pthread_mutex_unlock(&LOCK_apc_queue); + + struct timespec abstime; + const int timeout= timeout_sec; + set_timespec(abstime, timeout); + + int wait_res= 0; + /* todo: how about processing other errors here? */ + while (!apc_request.done && (wait_res != ETIMEDOUT)) + { + wait_res= pthread_cond_timedwait(&apc_request.COND_request, + &apc_request.LOCK_request, &abstime); + } + + if (!apc_request.done) + { + /* We timed out */ + apc_request.done= TRUE; + *timed_out= TRUE; + pthread_mutex_unlock(&apc_request.LOCK_request); + + pthread_mutex_lock(&LOCK_apc_queue); + dequeue_request(&apc_request); + pthread_mutex_unlock(&LOCK_apc_queue); + res= TRUE; + } + else + { + /* Request was successfully executed and dequeued by the target thread */ + pthread_mutex_unlock(&apc_request.LOCK_request); + res= FALSE; + } + + /* Destroy all APC request data */ + pthread_mutex_destroy(&apc_request.LOCK_request); + pthread_cond_destroy(&apc_request.COND_request); + } + else + { + pthread_mutex_unlock(&LOCK_apc_queue); + } + return res; +} + + +/* + Process all APC requests +*/ + +void Apc_target::process_apc_requests() +{ + while (1) + { + Call_request *request; + + pthread_mutex_lock(&LOCK_apc_queue); + if (!(request= get_first_in_queue())) + { + pthread_mutex_unlock(&LOCK_apc_queue); + break; + } + + request->what="seen by process_apc_requests"; + pthread_mutex_lock(&request->LOCK_request); + + if (request->done) + { + /* + We can get here when + - the requestor thread has been waiting for this request + - the wait has timed out + - it has set request->done=TRUE + - it has released LOCK_request, because its next action + will be to remove the request from the queue, however, + it could not attempt to lock the queue while holding the lock on + request, because that would deadlock with this function + (we here first lock the queue and then lock the request) + */ + pthread_mutex_unlock(&request->LOCK_request); + pthread_mutex_unlock(&LOCK_apc_queue); + fprintf(stderr, "Whoa rare event #1!\n"); + continue; + } + /* + Remove the request from the queue (we're holding its lock so we can be + sure that request owner won't try to remove it) + */ + request->what="dequeued by process_apc_requests"; + dequeue_request(request); + request->done= TRUE; + + pthread_mutex_unlock(&LOCK_apc_queue); + + request->func(request->func_arg); + request->what="func called by process_apc_requests"; + + pthread_cond_signal(&request->COND_request); + + pthread_mutex_unlock(&request->LOCK_request); + } +} + +/***************************************************************************** + * Testing + *****************************************************************************/ +#ifdef MY_APC_STANDALONE + +volatile bool started= FALSE; +volatile bool service_should_exit= FALSE; +volatile bool requestors_should_exit=FALSE; + +volatile int apcs_served= 0; +volatile int apcs_missed=0; +volatile int apcs_timed_out=0; + +Apc_target apc_target; + +int int_rand(int size) +{ + return round (((double)rand() / RAND_MAX) * size); +} + +/* An APC-serving thread */ +void *test_apc_service_thread(void *ptr) +{ + my_thread_init(); + apc_target.init(); + apc_target.enable(); + started= TRUE; + fprintf(stderr, "# test_apc_service_thread started\n"); + while (!service_should_exit) + { + //apc_target.disable(); + usleep(10000); + //apc_target.enable(); + for (int i = 0; i < 10 && !service_should_exit; i++) + { + apc_target.process_apc_requests(); + usleep(int_rand(30)); + } + } + apc_target.disable(); + apc_target.destroy(); + my_thread_end(); + pthread_exit(0); +} + +class Apc_order +{ +public: + int value; // The value + int *where_to; // Where to write it + Apc_order(int a, int *b) : value(a), where_to(b) {} +}; + +void test_apc_func(void *arg) +{ + Apc_order *order=(Apc_order*)arg; + usleep(int_rand(1000)); + *(order->where_to) = order->value; + __sync_fetch_and_add(&apcs_served, 1); +} + +void *test_apc_requestor_thread(void *ptr) +{ + my_thread_init(); + fprintf(stderr, "# test_apc_requestor_thread started\n"); + while (!requestors_should_exit) + { + int dst_value= 0; + int src_value= int_rand(4*1000*100); + /* Create APC to do dst_value= src_value */ + Apc_order apc_order(src_value, &dst_value); + bool timed_out; + + bool res= apc_target.make_apc_call(test_apc_func, (void*)&apc_order, 60, &timed_out); + if (res) + { + if (timed_out) + __sync_fetch_and_add(&apcs_timed_out, 1); + else + __sync_fetch_and_add(&apcs_missed, 1); + + if (dst_value != 0) + fprintf(stderr, "APC was done even though return value says it wasnt!\n"); + } + else + { + if (dst_value != src_value) + fprintf(stderr, "APC was not done even though return value says it was!\n"); + } + //usleep(300); + } + fprintf(stderr, "# test_apc_requestor_thread exiting\n"); + my_thread_end(); +} + +const int N_THREADS=23; +int main(int args, char **argv) +{ + pthread_t service_thr; + pthread_t request_thr[N_THREADS]; + int i, j; + my_thread_global_init(); + + pthread_create(&service_thr, NULL, test_apc_service_thread, (void*)NULL); + while (!started) + usleep(1000); + for (i = 0; i < N_THREADS; i++) + pthread_create(&request_thr[i], NULL, test_apc_requestor_thread, (void*)NULL); + + for (i = 0; i < 15; i++) + { + usleep(500*1000); + fprintf(stderr, "# %d APCs served %d missed\n", apcs_served, apcs_missed); + } + fprintf(stderr, "# Shutting down requestors\n"); + requestors_should_exit= TRUE; + for (i = 0; i < N_THREADS; i++) + pthread_join(request_thr[i], NULL); + + fprintf(stderr, "# Shutting down service\n"); + service_should_exit= TRUE; + pthread_join(service_thr, NULL); + fprintf(stderr, "# Done.\n"); + my_thread_end(); + my_thread_global_end(); + return 0; +} + +#endif // MY_APC_STANDALONE + + + diff --git a/sql/my_apc.h b/sql/my_apc.h new file mode 100644 index 00000000000..3783bd28f54 --- /dev/null +++ b/sql/my_apc.h @@ -0,0 +1,98 @@ +/* + TODO: MP AB Copyright +*/ + +/* + Design + - Mutex-guarded request queue (it belongs to the target), which can be enabled/ + disabled (when empty). + + - After the request has been put into queue, the requestor waits for request + to be satisfied. The worker satisifes the request and signals the + requestor. +*/ + +/* + Target for asynchronous calls. +*/ +class Apc_target +{ +public: + Apc_target() : enabled(0), apc_calls(NULL) /*, call_queue_size(0)*/ {} + ~Apc_target() { DBUG_ASSERT(!enabled && !apc_calls);} + + /* + Initialize the target. This must be called before anything else. Right + after initialization, the target is disabled. + */ + void init(); + + /* + Destroy the target. The target must be disabled when this call is made. + */ + void destroy(); + + /* + Enter into state where this target will be serving APC requests + */ + void enable(); + + /* + Leave the state where we could serve APC requests (will serve all already + enqueued requests) + */ + void disable(); + + /* + This should be called periodically to serve observation requests. + */ + void process_apc_requests(); + + typedef void (*apc_func_t)(void *arg); + + /* + Make an APC call: schedule it for execution and wait until the target + thread has executed it. This function must not be called from a thread + that's different from the target thread. + + @retval FALSE - Ok, the call has been made + @retval TRUE - Call wasnt made (either the target is in disabled state or + timeout occured) + */ + bool make_apc_call(apc_func_t func, void *func_arg, + int timeout_sec, bool *timed_out); + +private: + class Call_request; + int enabled; + + Call_request *apc_calls; + pthread_mutex_t LOCK_apc_queue; + //int call_queue_size; + + class Call_request + { + public: + apc_func_t func; + void *func_arg; + bool done; + + pthread_mutex_t LOCK_request; + pthread_cond_t COND_request; + + Call_request *next; + Call_request *prev; + + const char *what; + }; + + void enqueue_request(Call_request *qe); + void dequeue_request(Call_request *qe); + Call_request *get_first_in_queue() + { + return apc_calls; + } +}; + +/////////////////////////////////////////////////////////////////////// + diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h index 24e637df497..a19cca31d1c 100644 --- a/sql/mysql_priv.h +++ b/sql/mysql_priv.h @@ -803,6 +803,7 @@ typedef my_bool (*qc_engine_callback)(THD *thd, char *table_key, ulonglong *engine_data); #include "sql_string.h" #include "my_decimal.h" +#include "my_apc.h" /* to unify the code that differs only in the argument passed to the @@ -1544,6 +1545,7 @@ bool mysqld_show_create(THD *thd, TABLE_LIST *table_list); bool mysqld_show_create_db(THD *thd, char *dbname, HA_CREATE_INFO *create); void mysqld_list_processes(THD *thd,const char *user,bool verbose); +void mysqld_show_explain(THD *thd, ulong thread_id); int mysqld_show_status(THD *thd); int mysqld_show_variables(THD *thd,const char *wild); bool mysqld_show_storage_engines(THD *thd); diff --git a/sql/mysqld.cc b/sql/mysqld.cc index 1c9b6a19d38..04439b470ce 100644 --- a/sql/mysqld.cc +++ b/sql/mysqld.cc @@ -3430,6 +3430,7 @@ SHOW_VAR com_status_vars[]= { {"show_engine_status", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_ENGINE_STATUS]), SHOW_LONG_STATUS}, {"show_events", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_EVENTS]), SHOW_LONG_STATUS}, {"show_errors", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_ERRORS]), SHOW_LONG_STATUS}, + {"show_explain", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_EXPLAIN]), SHOW_LONG_STATUS}, {"show_fields", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_FIELDS]), SHOW_LONG_STATUS}, #ifndef DBUG_OFF {"show_function_code", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_FUNC_CODE]), SHOW_LONG_STATUS}, diff --git a/sql/protocol.h b/sql/protocol.h index e07af5208db..7a882e8a10d 100644 --- a/sql/protocol.h +++ b/sql/protocol.h @@ -28,6 +28,7 @@ class Protocol protected: THD *thd; String *packet; + /* Used by net_store_data() for charset conversions */ String *convert; uint field_pos; #ifndef DBUG_OFF @@ -42,6 +43,10 @@ protected: MYSQL_FIELD *next_mysql_field; MEM_ROOT *alloc; #endif + /* + The following two are low-level functions that are invoked from + higher-level store_xxx() funcs. The data is stored into this->packet. + */ bool net_store_data(const uchar *from, size_t length, CHARSET_INFO *fromcs, CHARSET_INFO *tocs); bool store_string_aux(const char *from, size_t length, @@ -55,6 +60,20 @@ public: enum { SEND_NUM_ROWS= 1, SEND_DEFAULTS= 2, SEND_EOF= 4 }; virtual bool send_fields(List *list, uint flags); + void get_packet(const char **start, size_t *length) + { + *start= packet->ptr(); + *length= packet->length(); + } + void set_packet(const char *start, size_t len) + { + packet->length(0); + packet->append(start, len); +#ifndef DBUG_OFF + field_pos= field_count - 1; +#endif + } + bool store(I_List *str_list); bool store(const char *from, CHARSET_INFO *cs); String *storage_packet() { return packet; } diff --git a/sql/sp_head.cc b/sql/sp_head.cc index bd2dcfd8653..93743c2985b 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -204,6 +204,7 @@ sp_get_flags_for_command(LEX *lex) case SQLCOM_SHOW_CREATE_TRIGGER: case SQLCOM_SHOW_DATABASES: case SQLCOM_SHOW_ERRORS: + case SQLCOM_SHOW_EXPLAIN: case SQLCOM_SHOW_FIELDS: case SQLCOM_SHOW_FUNC_CODE: case SQLCOM_SHOW_GRANTS: diff --git a/sql/sql_class.cc b/sql/sql_class.cc index 9bb1f83b06d..12709e23a34 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -964,6 +964,7 @@ void THD::init(void) /* Initialize the Debug Sync Facility. See debug_sync.cc. */ debug_sync_init_thread(this); #endif /* defined(ENABLED_DEBUG_SYNC) */ + apc_target.init(); } @@ -1122,7 +1123,8 @@ void THD::cleanup(void) pthread_mutex_unlock(&LOCK_user_locks); ull= NULL; } - + + apc_target.destroy(); cleanup_done=1; DBUG_VOID_RETURN; } @@ -1666,6 +1668,14 @@ CHANGED_TABLE_LIST* THD::changed_table_dup(const char *key, long key_length) int THD::send_explain_fields(select_result *result) { List field_list; + make_explain_field_list(field_list); + return (result->send_fields(field_list, + Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF)); +} + + +void THD::make_explain_field_list(List &field_list) +{ Item *item; CHARSET_INFO *cs= system_charset_info; field_list.push_back(new Item_return_int("id",3, MYSQL_TYPE_LONGLONG)); @@ -1703,10 +1713,9 @@ int THD::send_explain_fields(select_result *result) } item->maybe_null= 1; field_list.push_back(new Item_empty_string("Extra", 255, cs)); - return (result->send_fields(field_list, - Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF)); } + #ifdef SIGNAL_WITH_VIO_CLOSE void THD::close_active_vio() { @@ -1810,6 +1819,21 @@ void THD::rollback_item_tree_changes() } +/* + Check if the thread has been killed, and also process "APC requests" + + @retval true The thread is killed, execution should be interrupted + @retval false Not killed, continue execution +*/ + +bool THD::check_killed() +{ + if (killed) + return TRUE; + apc_target.process_apc_requests(); + return FALSE; +} + /***************************************************************************** ** Functions to provide a interface to select results *****************************************************************************/ @@ -1950,6 +1974,68 @@ int select_send::send_data(List &items) DBUG_RETURN(0); } + +////////////////////////////////////////////////////////////////////////////// +int select_result_explain_buffer::send_data(List &items) +{ + List_iterator_fast li(items); + char buff[MAX_FIELD_WIDTH]; + String buffer(buff, sizeof(buff), &my_charset_bin); + DBUG_ENTER("select_send::send_data"); + + protocol->prepare_for_resend(); + Item *item; + while ((item=li++)) + { + if (item->send(protocol, &buffer)) + { + protocol->free(); // Free used buffer + my_message(ER_OUT_OF_RESOURCES, ER(ER_OUT_OF_RESOURCES), MYF(0)); + break; + } + /* + Reset buffer to its original state, as it may have been altered in + Item::send(). + */ + buffer.set(buff, sizeof(buff), &my_charset_bin); + } + //TODO: do we need the following: + if (thd->is_error()) + { + protocol->remove_last_row(); + DBUG_RETURN(1); + } + /* psergey-TODO: instead of protocol->write(), steal the packet here */ + const char *packet_data; + size_t len; + protocol->get_packet(&packet_data, &len); + + String *s= new (thd->mem_root) String; + s->append(packet_data, len); + data_rows.push_back(s); + protocol->remove_last_row(); // <-- this does nothing. Do we need it? + // prepare_for_resend() will wipe out the packet + DBUG_RETURN(0); +} + + +void select_result_explain_buffer::flush_data() +{ + List_iterator it(data_rows); + String *str; + while ((str= it++)) + { + /* TODO: write out the lines. */ + protocol->set_packet(str->ptr(), str->length()); + protocol->write(); + delete str; + } + data_rows.empty(); +} + +////////////////////////////////////////////////////////////////////////////// + + bool select_send::send_eof() { /* @@ -2810,6 +2896,10 @@ void THD::end_statement() } +/* + Start using arena specified by @set. Current arena data will be saved to + *backup. +*/ void THD::set_n_backup_active_arena(Query_arena *set, Query_arena *backup) { DBUG_ENTER("THD::set_n_backup_active_arena"); @@ -2824,6 +2914,12 @@ void THD::set_n_backup_active_arena(Query_arena *set, Query_arena *backup) } +/* + Stop using the temporary arena, and start again using the arena that is + specified in *backup. + The temporary arena is returned back into *set. +*/ + void THD::restore_active_arena(Query_arena *set, Query_arena *backup) { DBUG_ENTER("THD::restore_active_arena"); @@ -2836,6 +2932,23 @@ void THD::restore_active_arena(Query_arena *set, Query_arena *backup) DBUG_VOID_RETURN; } +// psergey +void Show_explain_request::get_explain_data(void *arg) +{ + Show_explain_request *req= (Show_explain_request*)arg; + //TODO: change mem_root to point to request_thd->mem_root. + // Actually, change the ARENA, because we're going to allocate items! + Query_arena backup_arena; + req->target_thd->set_n_backup_active_arena((Query_arena*)req->request_thd, + &backup_arena); + + req->target_thd->lex->unit.print_explain(req->explain_buf); + + req->target_thd->restore_active_arena((Query_arena*)req->request_thd, + &backup_arena); +} + + Statement::~Statement() { } diff --git a/sql/sql_class.h b/sql/sql_class.h index c97cc34e166..5d55d7182fc 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -1420,6 +1420,19 @@ struct Ha_data }; +class select_result_explain_buffer; + +class Show_explain_request +{ +public: + THD *target_thd; + THD *request_thd; + + select_result_explain_buffer *explain_buf; + + static void get_explain_data(void *arg); +}; + /** @class THD For each client connection we create a separate thread with THD serving as @@ -1990,6 +2003,8 @@ public: }; killed_state volatile killed; + bool check_killed(); + /* scramble - random string sent to client on handshake */ char scramble[SCRAMBLE_LENGTH+1]; @@ -2171,6 +2186,16 @@ public: void close_active_vio(); #endif void awake(THD::killed_state state_to_set); + + + /* + This is what allows this thread to serve as a target for others to + schedule Async Procedure Calls on. + + It's possible to schedule arbitrary C function call but currently this + facility is used only by SHOW EXPLAIN code (See Show_explain_request) + */ + Apc_target apc_target; #ifndef MYSQL_CLIENT enum enum_binlog_query_type { @@ -2302,6 +2327,7 @@ public: void add_changed_table(const char *key, long key_length); CHANGED_TABLE_LIST * changed_table_dup(const char *key, long key_length); int send_explain_fields(select_result *result); + void make_explain_field_list(List &field_list); #ifndef EMBEDDED_LIBRARY /** Clear the current error, if any. @@ -2750,10 +2776,42 @@ public: class JOIN; -class select_result :public Sql_alloc { +/* Pure interface for sending tabular data */ +class select_result_sink: public Sql_alloc +{ +public: + /* + send_data returns 0 on ok, 1 on error and -1 if data was ignored, for + example for a duplicate row entry written to a temp table. + */ + virtual int send_data(List &items)=0; + virtual ~select_result_sink() {}; +}; + + +/* + Interface for sending tabular data, together with some other stuff: + + - Primary purpose seems to be seding typed tabular data: + = the DDL is sent with send_fields() + = the rows are sent with send_data() + Besides that, + - there seems to be an assumption that the sent data is a result of + SELECT_LEX_UNIT *unit, + - nest_level is used by SQL parser +*/ + +class select_result :public select_result_sink +{ protected: THD *thd; + /* + All descendant classes have their send_data() skip the first + unit->offset_limit_cnt rows sent. Select_materialize + also uses unit->get_unit_column_types(). + */ SELECT_LEX_UNIT *unit; + /* Something used only by the parser: */ int nest_level; public: select_result(); @@ -2772,11 +2830,6 @@ public: virtual uint field_count(List &fields) const { return fields.elements; } virtual bool send_fields(List &list, uint flags)=0; - /* - send_data returns 0 on ok, 1 on error and -1 if data was ignored, for - example for a duplicate row entry written to a temp table. - */ - virtual int send_data(List &items)=0; virtual bool initialize_tables (JOIN *join=0) { return 0; } virtual void send_error(uint errcode,const char *err); virtual bool send_eof()=0; @@ -2809,6 +2862,35 @@ public: }; +/* + A select result sink that collects the sent data and then can flush it to + network when requested. + + This class is targeted at collecting EXPLAIN output: + - Unoptimized data storage (can't handle big datasets) + - Unlike select_result class, we don't assume that the sent data is an + output of a SELECT_LEX_UNIT (and so we dont apply "LIMIT x,y" from the + unit) +*/ + +class select_result_explain_buffer : public select_result_sink +{ +public: + THD *thd; + Protocol *protocol; + select_result_explain_buffer(){}; + + /* The following is called in the child thread: */ + int send_data(List &items); + + /* this will be called in the parent thread: */ + void flush_data(); + + List data_rows; +}; + + + /* Base class for select_result descendands which intercept and transform result set rows. As the rows are not sent to the client, diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 021e7a3b5e8..ae9c11c47e2 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -3623,6 +3623,40 @@ bool st_select_lex::save_prep_leaf_tables(THD *thd) } +int st_select_lex::print_explain(select_result_sink *output) +{ + if (join && join->optimized == 2) + { + //psergey-TODO: any? + return join->print_explain(output, TRUE, + FALSE, // need_tmp_table, + FALSE, // bool need_order, + FALSE, // bool distinct, + NULL); //const char *message + } + else + { + DBUG_ASSERT(0); + /* produce "not yet optimized" line */ + } + return 0; +} + + +int st_select_lex_unit::print_explain(select_result_sink *output) +{ + int res= 0; + SELECT_LEX *first= first_select(); + + for (SELECT_LEX *sl= first; sl; sl= sl->next_select()) + { + if ((res= sl->print_explain(output))) + break; + } + return res; +} + + /** A routine used by the parser to decide whether we are specifying a full partitioning or if only partitions to add or to split. diff --git a/sql/sql_lex.h b/sql/sql_lex.h index fe79e6e2908..d5dc4dc3a1f 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -120,6 +120,7 @@ enum enum_sql_command { SQLCOM_SHOW_PROFILE, SQLCOM_SHOW_PROFILES, SQLCOM_SHOW_USER_STATS, SQLCOM_SHOW_TABLE_STATS, SQLCOM_SHOW_INDEX_STATS, SQLCOM_SHOW_CLIENT_STATS, + SQLCOM_SHOW_EXPLAIN, /* When a command is added here, be sure it's also added in mysqld.cc @@ -253,6 +254,8 @@ typedef uchar index_clause_map; #define INDEX_HINT_MASK_ALL (INDEX_HINT_MASK_JOIN | INDEX_HINT_MASK_GROUP | \ INDEX_HINT_MASK_ORDER) +class select_result_sink; + /* Single element of an USE/FORCE/IGNORE INDEX list specified as a SQL hint */ class Index_hint : public Sql_alloc { @@ -591,6 +594,7 @@ public: friend int subselect_union_engine::exec(); List *get_unit_column_types(); + int print_explain(select_result_sink *output); }; typedef class st_select_lex_unit SELECT_LEX_UNIT; @@ -908,6 +912,8 @@ public: bool save_leaf_tables(THD *thd); bool save_prep_leaf_tables(THD *thd); + int print_explain(select_result_sink *output); + private: /* current index hint kind. used in filling up index_hints */ enum index_hint_type current_index_hint_type; diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 18dad6cfff0..8e36da5f285 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -328,6 +328,7 @@ void init_update_queries(void) sql_command_flags[SQLCOM_SHOW_ENGINE_STATUS]= CF_STATUS_COMMAND; sql_command_flags[SQLCOM_SHOW_ENGINE_MUTEX]= CF_STATUS_COMMAND; sql_command_flags[SQLCOM_SHOW_ENGINE_LOGS]= CF_STATUS_COMMAND; + sql_command_flags[SQLCOM_SHOW_EXPLAIN]= CF_STATUS_COMMAND; sql_command_flags[SQLCOM_SHOW_PROCESSLIST]= CF_STATUS_COMMAND; sql_command_flags[SQLCOM_SHOW_GRANTS]= CF_STATUS_COMMAND; sql_command_flags[SQLCOM_SHOW_CREATE_DB]= CF_STATUS_COMMAND; @@ -3402,6 +3403,32 @@ end_with_restore_list: thd->security_ctx->priv_user), lex->verbose); break; + case SQLCOM_SHOW_EXPLAIN: + { + /* Same security as SHOW PROCESSLIST (TODO check this) */ + if (!thd->security_ctx->priv_user[0] && + check_global_access(thd,PROCESS_ACL)) + break; + + Item *it= (Item *)lex->value_list.head(); + + if (lex->table_or_sp_used()) + { + my_error(ER_NOT_SUPPORTED_YET, MYF(0), "Usage of subqueries or stored " + "function calls as part of this statement"); + break; + } + + if ((!it->fixed && it->fix_fields(lex->thd, &it)) || it->check_cols(1)) + { + my_message(ER_SET_CONSTANTS_ONLY, ER(ER_SET_CONSTANTS_ONLY), + MYF(0)); + goto error; + } + + mysqld_show_explain(thd, (ulong)it->val_int()); + break; + } case SQLCOM_SHOW_AUTHORS: res= mysqld_show_authors(thd); break; diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc index 5b5ed004006..465a026ad6e 100644 --- a/sql/sql_prepare.cc +++ b/sql/sql_prepare.cc @@ -2049,6 +2049,7 @@ static bool check_prepared_statement(Prepared_statement *stmt) case SQLCOM_SHOW_ENGINE_LOGS: case SQLCOM_SHOW_ENGINE_STATUS: case SQLCOM_SHOW_ENGINE_MUTEX: + case SQLCOM_SHOW_EXPLAIN: case SQLCOM_SHOW_CREATE_DB: case SQLCOM_SHOW_GRANTS: case SQLCOM_SHOW_BINLOG_EVENTS: diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 5cc8078b9f0..74d2bc7c356 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -846,6 +846,12 @@ inject_jtbm_conds(JOIN *join, List *join_list, Item **join_where) DBUG_VOID_RETURN; } +int JOIN::optimize() +{ + int res= optimize_inner(); + optimized= 2; + return res; +} /** global select optimisation. @@ -859,7 +865,7 @@ inject_jtbm_conds(JOIN *join, List *join_list, Item **join_where) */ int -JOIN::optimize() +JOIN::optimize_inner() { ulonglong select_opts_for_readinfo; uint no_jbuf_after; @@ -2888,7 +2894,9 @@ mysql_select(THD *thd, Item ***rref_pointer_array, if (thd->is_error()) goto err; + thd->apc_target.enable(); join->exec(); + thd->apc_target.disable(); if (thd->cursor && thd->cursor->is_open()) { @@ -3529,7 +3537,7 @@ make_join_statistics(JOIN *join, List &tables_list, goto error; /* Generate an execution plan from the found optimal join order. */ - DBUG_RETURN(join->thd->killed || get_best_combination(join)); + DBUG_RETURN(join->thd->check_killed() || get_best_combination(join)); error: /* @@ -6276,7 +6284,7 @@ best_extension_by_limited_search(JOIN *join, DBUG_ENTER("best_extension_by_limited_search"); THD *thd= join->thd; - if (thd->killed) // Abort + if (thd->check_killed()) // Abort DBUG_RETURN(TRUE); DBUG_EXECUTE("opt", print_plan(join, idx, read_time, record_count, idx, @@ -6436,7 +6444,7 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count, { DBUG_ENTER("find_best"); THD *thd= join->thd; - if (thd->killed) + if (thd->check_killed()) DBUG_RETURN(TRUE); if (!rest_tables) { @@ -14452,7 +14460,7 @@ create_internal_tmp_table_from_heap2(THD *thd, TABLE *table, DBUG_EXECUTE_IF("raise_error", write_err= HA_ERR_FOUND_DUPP_KEY ;); if (write_err) goto err; - if (thd->killed) + if (thd->check_killed()) { thd->send_kill_message(); goto err_killed; @@ -14822,7 +14830,7 @@ sub_select_cache(JOIN *join, JOIN_TAB *join_tab, bool end_of_records) rc= sub_select(join, join_tab, end_of_records); DBUG_RETURN(rc); } - if (join->thd->killed) + if (join->thd->check_killed()) { /* The user has aborted the execution of the query */ join->thd->send_kill_message(); @@ -15121,7 +15129,7 @@ evaluate_join_record(JOIN *join, JOIN_TAB *join_tab, DBUG_RETURN(NESTED_LOOP_ERROR); if (error < 0) DBUG_RETURN(NESTED_LOOP_NO_MORE_ROWS); - if (join->thd->killed) // Aborted by user + if (join->thd->check_killed()) // Aborted by user { join->thd->send_kill_message(); DBUG_RETURN(NESTED_LOOP_KILLED); /* purecov: inspected */ @@ -16250,7 +16258,7 @@ end_write(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), TABLE *table=join->tmp_table; DBUG_ENTER("end_write"); - if (join->thd->killed) // Aborted by user + if (join->thd->check_killed()) // Aborted by user { join->thd->send_kill_message(); DBUG_RETURN(NESTED_LOOP_KILLED); /* purecov: inspected */ @@ -16321,7 +16329,7 @@ end_update(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), if (end_of_records) DBUG_RETURN(NESTED_LOOP_OK); - if (join->thd->killed) // Aborted by user + if (join->thd->check_killed()) // Aborted by user { join->thd->send_kill_message(); DBUG_RETURN(NESTED_LOOP_KILLED); /* purecov: inspected */ @@ -16402,7 +16410,7 @@ end_unique_update(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), if (end_of_records) DBUG_RETURN(NESTED_LOOP_OK); - if (join->thd->killed) // Aborted by user + if (join->thd->check_killed()) // Aborted by user { join->thd->send_kill_message(); DBUG_RETURN(NESTED_LOOP_KILLED); /* purecov: inspected */ @@ -16449,7 +16457,7 @@ end_write_group(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), int idx= -1; DBUG_ENTER("end_write_group"); - if (join->thd->killed) + if (join->thd->check_killed()) { // Aborted by user join->thd->send_kill_message(); DBUG_RETURN(NESTED_LOOP_KILLED); /* purecov: inspected */ @@ -18226,7 +18234,7 @@ static int remove_dup_with_compare(THD *thd, TABLE *table, Field **first_field, error= file->ha_rnd_next(record); for (;;) { - if (thd->killed) + if (thd->check_killed()) { thd->send_kill_message(); error=0; @@ -18358,7 +18366,7 @@ static int remove_dup_with_hash_index(THD *thd, TABLE *table, for (;;) { uchar *org_key_pos; - if (thd->killed) + if (thd->check_killed()) { thd->send_kill_message(); error=0; @@ -20355,29 +20363,40 @@ void JOIN::clear() } } + /** EXPLAIN handling. Send a description about what how the select will be done to stdout. + + @param on_the_fly TRUE <=> we're being executed on-the-fly, so don't make + modifications to any select's data structures */ -static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, - bool distinct,const char *message) +int JOIN::print_explain(select_result_sink *result, bool on_the_fly, + bool need_tmp_table, bool need_order, + bool distinct, const char *message) { List field_list; List item_list; + JOIN *join= this; /* Legacy: this code used to be a non-member function */ THD *thd=join->thd; - select_result *result=join->result; Item *item_null= new Item_null(); CHARSET_INFO *cs= system_charset_info; int quick_type; - DBUG_ENTER("select_describe"); + int error= 0; + DBUG_ENTER("JOIN::print_explain"); DBUG_PRINT("info", ("Select 0x%lx, type %s, message %s", (ulong)join->select_lex, join->select_lex->type, message ? message : "NULL")); + DBUG_ASSERT(this->optimized == 2); /* Don't log this into the slow query log */ - thd->server_status&= ~(SERVER_QUERY_NO_INDEX_USED | SERVER_QUERY_NO_GOOD_INDEX_USED); - join->unit->offset_limit_cnt= 0; + + if (!on_the_fly) + { + thd->server_status&= ~(SERVER_QUERY_NO_INDEX_USED | SERVER_QUERY_NO_GOOD_INDEX_USED); + join->unit->offset_limit_cnt= 0; + } /* NOTE: the number/types of items pushed into item_list must be in sync with @@ -20398,10 +20417,11 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, item_list.push_back(new Item_string(message,strlen(message),cs)); if (result->send_data(item_list)) - join->error= 1; + error= 1; } else if (join->select_lex == join->unit->fake_select_lex) { + join->select_lex->set_explain_type(); //psergey /* here we assume that the query will return at least two rows, so we show "filesort" in EXPLAIN. Of course, sometimes we'll be wrong @@ -20468,12 +20488,13 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, item_list.push_back(new Item_string("", 0, cs)); if (result->send_data(item_list)) - join->error= 1; + error= 1; } else if (!join->select_lex->master_unit()->derived || join->select_lex->master_unit()->derived->is_materialized_derived()) { table_map used_tables=0; + join->select_lex->set_explain_type(); //psergey bool printing_materialize_nest= FALSE; uint select_id= join->select_lex->select_number; @@ -20965,9 +20986,23 @@ static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, // For next iteration used_tables|=table->map; if (result->send_data(item_list)) - join->error= 1; + error= 1; } } + DBUG_RETURN(error); +} + + +static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, + bool distinct,const char *message) +{ + THD *thd=join->thd; + select_result *result=join->result; + DBUG_ENTER("select_describe"); + join->error= join->print_explain(result, FALSE, /* Not on-the-fly */ + need_tmp_table, need_order, distinct, + message); + for (SELECT_LEX_UNIT *unit= join->select_lex->first_inner_unit(); unit; unit= unit->next_unit()) @@ -21010,7 +21045,7 @@ bool mysql_explain_union(THD *thd, SELECT_LEX_UNIT *unit, select_result *result) for (SELECT_LEX *sl= first; sl; sl= sl->next_select()) { - sl->set_explain_type(); + sl->set_explain_type(); //psergey-todo: maybe remove this from here? sl->options|= SELECT_DESCRIBE; } diff --git a/sql/sql_select.h b/sql/sql_select.h index bbf390aaf7e..e3b6b1f6cac 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -963,7 +963,7 @@ public: const char *zero_result_cause; ///< not 0 if exec must return zero result bool union_part; ///< this subselect is part of union - bool optimized; ///< flag to avoid double optimization in EXPLAIN + int optimized; ///< flag to avoid double optimization in EXPLAIN bool initialized; ///< flag to avoid double init_execution calls /* @@ -1074,6 +1074,7 @@ public: SELECT_LEX_UNIT *unit); bool prepare_stage2(); int optimize(); + int optimize_inner(); int reinit(); int init_execution(); void exec(); @@ -1167,6 +1168,10 @@ public: { return (unit->item && unit->item->is_in_predicate()); } + + int print_explain(select_result_sink *result, bool on_the_fly, + bool need_tmp_table, bool need_order, + bool distinct,const char *message); private: /** TRUE if the query contains an aggregate function but has no GROUP diff --git a/sql/sql_show.cc b/sql/sql_show.cc index e4981701025..770e4610fd8 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -2024,6 +2024,97 @@ void mysqld_list_processes(THD *thd,const char *user, bool verbose) DBUG_VOID_RETURN; } + +/* + SHOW EXPLAIN FOR command handler + + @param thd Current thread's thd + @param thread_id Thread whose explain we need + + @notes + - Attempt to do "SHOW EXPLAIN FOR " will properly produce "target not + running EXPLAINable command". + - todo: check how all this can/will work when using thread pools +*/ + +void mysqld_show_explain(THD *thd, ulong thread_id) +{ + THD *tmp; + Protocol *protocol= thd->protocol; + List field_list; + DBUG_ENTER("mysqld_show_explain"); + + thd->make_explain_field_list(field_list); + if (protocol->send_fields(&field_list, Protocol::SEND_NUM_ROWS | + Protocol::SEND_EOF)) + DBUG_VOID_RETURN; + + /* + Find the thread we need EXPLAIN for. Thread search code was copied from + kill_one_thread() + */ + VOID(pthread_mutex_lock(&LOCK_thread_count)); // For unlink from list + I_List_iterator it(threads); + while ((tmp=it++)) + { + if (tmp->command == COM_DAEMON) + continue; + if (tmp->thread_id == thread_id) + { + pthread_mutex_lock(&tmp->LOCK_thd_data); // Lock from delete + break; + } + } + VOID(pthread_mutex_unlock(&LOCK_thread_count)); + + if (tmp) + { + bool bres; + /* + Ok we've found the thread of interest and it won't go away because + we're holding its LOCK_thd data. + Post it an EXPLAIN request. + todo: where to get timeout from? + */ + bool timed_out; + int timeout_sec= 30; + Show_explain_request explain_req; + select_result_explain_buffer *explain_buf; + + explain_buf= new select_result_explain_buffer; + explain_buf->thd=thd; + explain_buf->protocol= thd->protocol; + + explain_req.explain_buf= explain_buf; + explain_req.target_thd= tmp; + explain_req.request_thd= thd; + + bres= tmp->apc_target.make_apc_call(Show_explain_request::get_explain_data, + (void*)&explain_req, + timeout_sec, &timed_out); + if (bres) + { + /* TODO not enabled or time out */ + my_error(ER_ERROR_WHEN_EXECUTING_COMMAND, MYF(0), + "SHOW EXPLAIN", + "Target is not running EXPLAINable command"); + } + pthread_mutex_unlock(&tmp->LOCK_thd_data); + if (!bres) + { + explain_buf->flush_data(); + my_eof(thd); + } + } + else + { + my_error(ER_NO_SUCH_THREAD, MYF(0), thread_id); + } + + DBUG_VOID_RETURN; +} + + int fill_schema_processlist(THD* thd, TABLE_LIST* tables, COND* cond) { TABLE *table= tables->table; diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index c2fea985aa1..879c502a25f 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -10852,6 +10852,12 @@ show_param: Lex->spname= $3; Lex->sql_command = SQLCOM_SHOW_CREATE_EVENT; } + | describe_command FOR_SYM expr + { + Lex->sql_command= SQLCOM_SHOW_EXPLAIN; + Lex->value_list.empty(); + Lex->value_list.push_front($3); + } ; show_engine_param: -- cgit v1.2.1 From 84cb5de047e83ced6ac9490b9da630fe7dff3c4a Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Wed, 24 Aug 2011 14:41:13 +0400 Subject: MWL#182: Explain running statements - Further progress with the code - Testcases. --- mysql-test/r/show_explain.result | 26 +++++++++++++++++++++++ mysql-test/t/show_explain.test | 46 ++++++++++++++++++++++++++++++++++++++++ sql/sql_class.cc | 9 +++++++- sql/sql_lex.cc | 21 +++++++++++------- sql/sql_select.cc | 17 +++++++++++---- sql/sql_select.h | 1 + 6 files changed, 107 insertions(+), 13 deletions(-) create mode 100644 mysql-test/r/show_explain.result create mode 100644 mysql-test/t/show_explain.test diff --git a/mysql-test/r/show_explain.result b/mysql-test/r/show_explain.result new file mode 100644 index 00000000000..5aee9221d50 --- /dev/null +++ b/mysql-test/r/show_explain.result @@ -0,0 +1,26 @@ +drop table if exists t0, t1; +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 (a int); +insert into t1 select A.a + 10*B.a + 100*C.a from t0 A, t0 B, t0 C; +show explain for 2*1000*1000*1000; +ERROR HY000: Unknown thread id: 2000000000 +show explain for 3; +ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command +show explain for 2; +ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command +select get_lock('optimizer_done', 10); +get_lock('optimizer_done', 10) +1 +select count(*) from t1 where a < 100000 and sleep(a*0 + release_lock('optimizer_done') +1); +select get_lock('optimizer_done', 100); +get_lock('optimizer_done', 100) +1 +show explain for 3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 Using where +select release_lock('optimizer_done'); +release_lock('optimizer_done') +1 +kill query 3; +drop table t0,t1; diff --git a/mysql-test/t/show_explain.test b/mysql-test/t/show_explain.test new file mode 100644 index 00000000000..fda31392e46 --- /dev/null +++ b/mysql-test/t/show_explain.test @@ -0,0 +1,46 @@ +# +# Tests for SHOW EXPLAIN FOR functionality +# +--disable_warnings +drop table if exists t0, t1; +--enable_warnings + +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 (a int); +insert into t1 select A.a + 10*B.a + 100*C.a from t0 A, t0 B, t0 C; + +# +# Try killing a non-existent thread +# +--error ER_NO_SUCH_THREAD +show explain for 2*1000*1000*1000; + +# Setup two threads and their ids +let $thr1=`select connection_id()`; +connect (con1, localhost, root,,); +connection con1; +let $thr2=`select connection_id()`; +connection default; + +# SHOW EXPLAIN FOR +--error ER_ERROR_WHEN_EXECUTING_COMMAND +eval show explain for $thr2; + +# SHOW EXPLAIN FOR +--error ER_ERROR_WHEN_EXECUTING_COMMAND +eval show explain for $thr1; + +# SHOW EXPLAIN FOR +connection con1; +select get_lock('optimizer_done', 10); +send select count(*) from t1 where a < 100000 and sleep(a*0 + release_lock('optimizer_done') +1); +connection default; +select get_lock('optimizer_done', 100); +eval show explain for $thr2; +select release_lock('optimizer_done'); +eval kill query $thr2; + +#insert into t1 values ('one'),('two'),('three'); + +drop table t0,t1; diff --git a/sql/sql_class.cc b/sql/sql_class.cc index 12709e23a34..c9b99b5fd1f 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -2932,7 +2932,14 @@ void THD::restore_active_arena(Query_arena *set, Query_arena *backup) DBUG_VOID_RETURN; } -// psergey + +/* + Produce EXPLAIN data. + + This function is APC-scheduled to be run in the context of the thread that + we're producing EXPLAIN for. +*/ + void Show_explain_request::get_explain_data(void *arg) { Show_explain_request *req= (Show_explain_request*)arg; diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index ae9c11c47e2..454331a0859 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -3625,19 +3625,24 @@ bool st_select_lex::save_prep_leaf_tables(THD *thd) int st_select_lex::print_explain(select_result_sink *output) { + int res; if (join && join->optimized == 2) { - //psergey-TODO: any? - return join->print_explain(output, TRUE, - FALSE, // need_tmp_table, - FALSE, // bool need_order, - FALSE, // bool distinct, - NULL); //const char *message + res= join->print_explain(output, TRUE, + FALSE, // need_tmp_table, + FALSE, // bool need_order, + FALSE, // bool distinct, + NULL); //const char *message } else { - DBUG_ASSERT(0); - /* produce "not yet optimized" line */ + /* Produce "not yet optimized" line */ + const char *msg="Not yet optimized"; + res= join->print_explain(output, TRUE, + FALSE, // need_tmp_table, + FALSE, // bool need_order, + FALSE, // bool distinct, + msg); //const char *message } return 0; } diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 74d2bc7c356..c3d1543b363 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -2019,6 +2019,17 @@ JOIN::save_join_tab() } +void JOIN::exec() +{ + /* + Enable SHOW EXPLAIN only if we're in the top-level query. + */ + thd->apc_target.enable(); + exec_inner(); + thd->apc_target.disable(); +} + + /** Exec select. @@ -2030,8 +2041,8 @@ JOIN::save_join_tab() @todo When can we have here thd->net.report_error not zero? */ -void -JOIN::exec() + +void JOIN::exec_inner() { List *columns_list= &fields_list; int tmp_error; @@ -2894,9 +2905,7 @@ mysql_select(THD *thd, Item ***rref_pointer_array, if (thd->is_error()) goto err; - thd->apc_target.enable(); join->exec(); - thd->apc_target.disable(); if (thd->cursor && thd->cursor->is_open()) { diff --git a/sql/sql_select.h b/sql/sql_select.h index e3b6b1f6cac..f329f51707f 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -1078,6 +1078,7 @@ public: int reinit(); int init_execution(); void exec(); + void exec_inner(); int destroy(); void restore_tmp(); bool alloc_func_list(); -- cgit v1.2.1 From 0a08933036f0d3c499b7110ae2883fcc1796414f Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Thu, 25 Aug 2011 12:15:29 +0400 Subject: MWL#182: Explain running statements - Added TODO comments --- mysql-test/t/show_explain.test | 5 +++++ sql/my_apc.cc | 4 +++- 2 files changed, 8 insertions(+), 1 deletion(-) diff --git a/mysql-test/t/show_explain.test b/mysql-test/t/show_explain.test index fda31392e46..e8f4a646a8a 100644 --- a/mysql-test/t/show_explain.test +++ b/mysql-test/t/show_explain.test @@ -43,4 +43,9 @@ eval kill query $thr2; #insert into t1 values ('one'),('two'),('three'); +## TODO: Test this: multiple SHOW EXPLAIN calls in course of running of one select +## +## TODO: Test this: have several SHOW EXPLAIN requests be queued up for a +## thread and served together. + drop table t0,t1; diff --git a/sql/my_apc.cc b/sql/my_apc.cc index 3842947f3bb..754b7880c42 100644 --- a/sql/my_apc.cc +++ b/sql/my_apc.cc @@ -97,7 +97,9 @@ void Apc_target::dequeue_request(Call_request *qe) /* Make an apc call in another thread. The caller is responsible so that we're not calling to ourselves. - + + psergey-todo: Should waits here be KILLable? (it seems one needs + to use thd->enter_cond() calls to be killable) */ bool Apc_target::make_apc_call(apc_func_t func, void *func_arg, -- cgit v1.2.1 From d3052e225a511cd28a41eb3444f36d2789e5dd6c Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Thu, 25 Aug 2011 13:04:09 +0400 Subject: Fix windows build: add my_apc.{h,cc} to CMakeLists.txt files --- libmysqld/CMakeLists.txt | 1 + sql/CMakeLists.txt | 1 + 2 files changed, 2 insertions(+) diff --git a/libmysqld/CMakeLists.txt b/libmysqld/CMakeLists.txt index 7c5b6bd5917..cf72cedb644 100644 --- a/libmysqld/CMakeLists.txt +++ b/libmysqld/CMakeLists.txt @@ -146,6 +146,7 @@ SET(LIBMYSQLD_SOURCES libmysqld.c emb_qcache.cc lib_sql.cc ../sql/create_options.cc ../sql/rpl_utility.cc ../sql/rpl_reporting.cc ../sql/sql_expression_cache.cc + ../sql/my_apc.cc ../sql/my_apc.h ${GEN_SOURCES} ${LIB_SOURCES}) diff --git a/sql/CMakeLists.txt b/sql/CMakeLists.txt index 8d2aeca17db..597ca7d874b 100644 --- a/sql/CMakeLists.txt +++ b/sql/CMakeLists.txt @@ -83,6 +83,7 @@ SET (SQL_SOURCE opt_index_cond_pushdown.cc create_options.cc sql_expression_cache.cc + my_apc.cc my_apc.h ${CMAKE_BINARY_DIR}/sql/sql_yacc.cc ${CMAKE_BINARY_DIR}/sql/sql_yacc.h ${CMAKE_BINARY_DIR}/include/mysqld_error.h -- cgit v1.2.1 From f4dd6831f5863ea5f239ca24c5b3c03bd2575c4a Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Fri, 26 Aug 2011 16:04:30 +0400 Subject: Fix for previous csets: let set_explain_type() produce correct types for "UNION RESULT" selects --- sql/sql_lex.cc | 2 +- sql/sql_select.cc | 2 +- 2 files changed, 2 insertions(+), 2 deletions(-) diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 454331a0859..82b171789bc 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -3520,7 +3520,7 @@ void st_select_lex::set_explain_type() ((is_uncacheable & UNCACHEABLE_DEPENDENT) ? "DEPENDENT UNION": is_uncacheable ? "UNCACHEABLE UNION": - "UNION"))); + (this == master_unit()->fake_select_lex)? "UNION RESULT" : "UNION"))); options|= SELECT_DESCRIBE; } diff --git a/sql/sql_select.cc b/sql/sql_select.cc index c3d1543b363..55ca42f797b 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -20503,7 +20503,7 @@ int JOIN::print_explain(select_result_sink *result, bool on_the_fly, join->select_lex->master_unit()->derived->is_materialized_derived()) { table_map used_tables=0; - join->select_lex->set_explain_type(); //psergey + join->select_lex->set_explain_type(); //psergey-todo: this adds SELECT_DESCRIBE to options! bad for on-the-fly bool printing_materialize_nest= FALSE; uint select_id= join->select_lex->select_number; -- cgit v1.2.1 From d9045bce1ddf1f017f0e127606c809271c953ef0 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Sat, 27 Aug 2011 09:47:21 +0400 Subject: -Make show_explain.test stable - Fix st_select_lex::set_explain_type() to allow producing exactly the same EXPLAINs as it did before. SHOW EXPLAIN output may produce select_type=SIMPLE instead or select_type=PRIMARY or vice versa (which is ok because values of select_type weren't self-consistent in this regard to begin with) --- mysql-test/r/show_explain.result | 8 ++++---- mysql-test/t/show_explain.test | 12 ++++++++++++ sql/opt_subselect.cc | 5 ++++- sql/sql_lex.cc | 14 +++++++++++--- sql/sql_lex.h | 8 +++++++- sql/sql_select.cc | 10 +++++++--- 6 files changed, 45 insertions(+), 12 deletions(-) diff --git a/mysql-test/r/show_explain.result b/mysql-test/r/show_explain.result index 5aee9221d50..4ff89069c5c 100644 --- a/mysql-test/r/show_explain.result +++ b/mysql-test/r/show_explain.result @@ -5,9 +5,9 @@ create table t1 (a int); insert into t1 select A.a + 10*B.a + 100*C.a from t0 A, t0 B, t0 C; show explain for 2*1000*1000*1000; ERROR HY000: Unknown thread id: 2000000000 -show explain for 3; +SHOW explain for thr2 ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command -show explain for 2; +SHOW explain for thr1 ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command select get_lock('optimizer_done', 10); get_lock('optimizer_done', 10) @@ -16,11 +16,11 @@ select count(*) from t1 where a < 100000 and sleep(a*0 + release_lock('optimizer select get_lock('optimizer_done', 100); get_lock('optimizer_done', 100) 1 -show explain for 3; +SHOW explain for thr2 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 Using where select release_lock('optimizer_done'); release_lock('optimizer_done') 1 -kill query 3; +kill query thr2 drop table t0,t1; diff --git a/mysql-test/t/show_explain.test b/mysql-test/t/show_explain.test index e8f4a646a8a..fc83613ae0e 100644 --- a/mysql-test/t/show_explain.test +++ b/mysql-test/t/show_explain.test @@ -24,12 +24,18 @@ let $thr2=`select connection_id()`; connection default; # SHOW EXPLAIN FOR +echo SHOW explain for thr2; +--disable_query_log --error ER_ERROR_WHEN_EXECUTING_COMMAND eval show explain for $thr2; +--enable_query_log # SHOW EXPLAIN FOR +echo SHOW explain for thr1; +--disable_query_log --error ER_ERROR_WHEN_EXECUTING_COMMAND eval show explain for $thr1; +--enable_query_log # SHOW EXPLAIN FOR connection con1; @@ -37,9 +43,15 @@ select get_lock('optimizer_done', 10); send select count(*) from t1 where a < 100000 and sleep(a*0 + release_lock('optimizer_done') +1); connection default; select get_lock('optimizer_done', 100); +echo SHOW explain for thr2; +--disable_query_log eval show explain for $thr2; +--enable_query_log select release_lock('optimizer_done'); +--disable_query_log eval kill query $thr2; +--enable_query_log +echo kill query thr2; #insert into t1 values ('one'),('two'),('three'); diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 6c5e177fe1d..f8b6f80eb14 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -1346,7 +1346,8 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred) while ((ifm= li++)) parent_lex->ftfunc_list->push_front(ifm); } - + + parent_lex->have_merged_subqueries= TRUE; DBUG_RETURN(FALSE); } @@ -1458,6 +1459,8 @@ static bool convert_subq_to_jtbm(JOIN *parent_join, create_subquery_temptable_name(tbl_alias, hash_sj_engine->materialize_join-> select_lex->select_number); jtbm->alias= tbl_alias; + + parent_lex->have_merged_subqueries= TRUE; #if 0 /* Inject sj_on_expr into the parent's WHERE or ON */ if (emb_tbl_nest) diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 82b171789bc..a9f07c337fa 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -1640,7 +1640,8 @@ void st_select_lex::init_query() link_next= 0; lock_option= TL_READ_DEFAULT; is_prep_leaf_list_saved= FALSE; - + + have_merged_subqueries= FALSE; bzero((char*) expr_cache_may_be_used, sizeof(expr_cache_may_be_used)); } @@ -3119,7 +3120,7 @@ bool st_select_lex::optimize_unflattened_subqueries() if (options & SELECT_DESCRIBE) { /* Optimize the subquery in the context of EXPLAIN. */ - sl->set_explain_type(); + sl->set_explain_type(FALSE); sl->options|= SELECT_DESCRIBE; inner_join->select_options|= SELECT_DESCRIBE; } @@ -3482,7 +3483,7 @@ void SELECT_LEX::update_used_tables() Set the EXPLAIN type for this subquery. */ -void st_select_lex::set_explain_type() +void st_select_lex::set_explain_type(bool on_the_fly) { bool is_primary= FALSE; if (next_select()) @@ -3504,6 +3505,9 @@ void st_select_lex::set_explain_type() } } + if (on_the_fly && !is_primary && have_merged_subqueries) + is_primary= TRUE; + SELECT_LEX *first= master_unit()->first_select(); /* drop UNCACHEABLE_EXPLAIN, because it is for internal usage only */ uint8 is_uncacheable= (uncacheable & ~UNCACHEABLE_EXPLAIN); @@ -3521,6 +3525,10 @@ void st_select_lex::set_explain_type() "DEPENDENT UNION": is_uncacheable ? "UNCACHEABLE UNION": (this == master_unit()->fake_select_lex)? "UNION RESULT" : "UNION"))); + + if (this == master_unit()->fake_select_lex) + type= "UNION RESULT"; + options|= SELECT_DESCRIBE; } diff --git a/sql/sql_lex.h b/sql/sql_lex.h index d5dc4dc3a1f..b5131ded136 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -646,6 +646,12 @@ public: those converted to jtbm nests. The list is emptied when conversion is done. */ List sj_subselects; + + /* + Needed to correctly generate 'PRIMARY' or 'SIMPLE' for select_type column + of EXPLAIN + */ + bool have_merged_subqueries; List leaf_tables; List leaf_tables_exec; @@ -888,7 +894,7 @@ public: */ bool optimize_unflattened_subqueries(); /* Set the EXPLAIN type for this subquery. */ - void set_explain_type(); + void set_explain_type(bool on_the_fly); bool handle_derived(struct st_lex *lex, uint phases); void append_table_to_list(TABLE_LIST *TABLE_LIST::*link, TABLE_LIST *table); bool get_free_table_map(table_map *map, uint *tablenr); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 55ca42f797b..2a895bb2798 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -20430,7 +20430,9 @@ int JOIN::print_explain(select_result_sink *result, bool on_the_fly, } else if (join->select_lex == join->unit->fake_select_lex) { - join->select_lex->set_explain_type(); //psergey + //if (!join->select_lex->type) + if (on_the_fly) + join->select_lex->set_explain_type(on_the_fly); //psergey /* here we assume that the query will return at least two rows, so we show "filesort" in EXPLAIN. Of course, sometimes we'll be wrong @@ -20503,7 +20505,9 @@ int JOIN::print_explain(select_result_sink *result, bool on_the_fly, join->select_lex->master_unit()->derived->is_materialized_derived()) { table_map used_tables=0; - join->select_lex->set_explain_type(); //psergey-todo: this adds SELECT_DESCRIBE to options! bad for on-the-fly + //if (!join->select_lex->type) + if (on_the_fly) + join->select_lex->set_explain_type(on_the_fly); //psergey-todo: this adds SELECT_DESCRIBE to options! bad for on-the-fly bool printing_materialize_nest= FALSE; uint select_id= join->select_lex->select_number; @@ -21054,7 +21058,7 @@ bool mysql_explain_union(THD *thd, SELECT_LEX_UNIT *unit, select_result *result) for (SELECT_LEX *sl= first; sl; sl= sl->next_select()) { - sl->set_explain_type(); //psergey-todo: maybe remove this from here? + sl->set_explain_type(FALSE); //psergey-todo: maybe remove this from here? sl->options|= SELECT_DESCRIBE; } -- cgit v1.2.1 From 203bbfe5693a95e01c84d1e4b788b76645df2d11 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Sat, 24 Sep 2011 21:56:42 +0400 Subject: MWL#182: Explain running statements - Implement new approach to testing (the DBUG_EXECUTE_IF variant) - add an 'evalp' mysqltest command that is like 'eval' except that it prints the original query. - Fix select_describe() not to change join_tab[i]->type - More tests --- client/mysqltest.cc | 25 ++++++++++++++-- mysql-test/r/show_explain.result | 43 ++++++++++++++++---------- mysql-test/t/show_explain.test | 65 ++++++++++++++++++++++++++-------------- sql/my_apc.cc | 8 +++++ sql/my_apc.h | 6 +++- sql/mysql_priv.h | 4 +++ sql/sql_select.cc | 43 +++++++++++++++++++------- 7 files changed, 141 insertions(+), 53 deletions(-) diff --git a/client/mysqltest.cc b/client/mysqltest.cc index a3689a7b757..d33db945aee 100644 --- a/client/mysqltest.cc +++ b/client/mysqltest.cc @@ -75,6 +75,8 @@ #define QUERY_SEND_FLAG 1 #define QUERY_REAP_FLAG 2 +#define QUERY_PRINT_ORIGINAL_FLAG 4 + #ifndef HAVE_SETENV static int setenv(const char *name, const char *value, int overwrite); #endif @@ -288,7 +290,8 @@ enum enum_commands { Q_ERROR, Q_SEND, Q_REAP, Q_DIRTY_CLOSE, Q_REPLACE, Q_REPLACE_COLUMN, - Q_PING, Q_EVAL, + Q_PING, Q_EVAL, + Q_EVALP, Q_RPL_PROBE, Q_ENABLE_RPL_PARSE, Q_DISABLE_RPL_PARSE, Q_EVAL_RESULT, Q_ENABLE_QUERY_LOG, Q_DISABLE_QUERY_LOG, @@ -353,6 +356,7 @@ const char *command_names[]= "replace_column", "ping", "eval", + "evalp", "rpl_probe", "enable_rpl_parse", "disable_rpl_parse", @@ -7532,7 +7536,8 @@ void run_query(struct st_connection *cn, struct st_command *command, int flags) /* Evaluate query if this is an eval command */ - if (command->type == Q_EVAL || command->type == Q_SEND_EVAL) + if (command->type == Q_EVAL || command->type == Q_SEND_EVAL || + command->type == Q_EVALP) { init_dynamic_string(&eval_query, "", command->query_len+256, 1024); do_eval(&eval_query, command->query, command->end, FALSE); @@ -7564,10 +7569,20 @@ void run_query(struct st_connection *cn, struct st_command *command, int flags) */ if (!disable_query_log && (flags & QUERY_SEND_FLAG)) { - replace_dynstr_append_mem(ds, query, query_len); + char *print_query= query; + int print_len= query_len; + if (flags & QUERY_PRINT_ORIGINAL_FLAG) + { + print_query= command->query; + print_len= command->end - command->query; + } + replace_dynstr_append_mem(ds, print_query, print_len); dynstr_append_mem(ds, delimiter, delimiter_length); dynstr_append_mem(ds, "\n", 1); } + + /* We're done with this flag */ + flags &= ~QUERY_PRINT_ORIGINAL_FLAG; /* Write the command to the result file before we execute the query @@ -8420,6 +8435,7 @@ int main(int argc, char **argv) case Q_EVAL_RESULT: die("'eval_result' command is deprecated"); case Q_EVAL: + case Q_EVALP: case Q_QUERY_VERTICAL: case Q_QUERY_HORIZONTAL: if (command->query == command->query_buf) @@ -8447,6 +8463,9 @@ int main(int argc, char **argv) flags= QUERY_REAP_FLAG; } + if (command->type == Q_EVALP) + flags |= QUERY_PRINT_ORIGINAL_FLAG; + /* Check for special property for this query */ display_result_vertically|= (command->type == Q_QUERY_VERTICAL); diff --git a/mysql-test/r/show_explain.result b/mysql-test/r/show_explain.result index 4ff89069c5c..6a142e676fa 100644 --- a/mysql-test/r/show_explain.result +++ b/mysql-test/r/show_explain.result @@ -3,24 +3,37 @@ create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 (a int); insert into t1 select A.a + 10*B.a + 100*C.a from t0 A, t0 B, t0 C; +alter table t1 add b int, add c int, add filler char(32); +update t1 set b=a, c=a, filler='fooo'; +alter table t1 add key(a), add key(b); show explain for 2*1000*1000*1000; ERROR HY000: Unknown thread id: 2000000000 -SHOW explain for thr2 +show explain for (select max(a) from t0); +ERROR 42000: This version of MySQL doesn't yet support 'Usage of subqueries or stored function calls as part of this statement' +show explain for $thr2; ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command -SHOW explain for thr1 +show explain for $thr1; ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command -select get_lock('optimizer_done', 10); -get_lock('optimizer_done', 10) -1 -select count(*) from t1 where a < 100000 and sleep(a*0 + release_lock('optimizer_done') +1); -select get_lock('optimizer_done', 100); -get_lock('optimizer_done', 100) -1 -SHOW explain for thr2 +set debug='d,show_explain_probe_1'; +select count(*) from t1 where a < 100000; +show explain for $thr2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ALL NULL NULL NULL NULL 1000 Using where -select release_lock('optimizer_done'); -release_lock('optimizer_done') -1 -kill query thr2 +1 SIMPLE t1 index a a 5 NULL 1000 Using where; Using index +count(*) +1000 +set debug='d,show_explain_probe_1'; +select max(c) from t1 where a < 10; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 10 Using where +max(c) +9 +set optimizer_switch='index_condition_pushdown=on,mrr=on,mrr_sort_keys=on'; +set debug='d,show_explain_probe_1'; +explain select max(c) from t1 where a < 10; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 10 Using index condition; Rowid-ordered scan +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 10 Using index condition; Rowid-ordered scan drop table t0,t1; diff --git a/mysql-test/t/show_explain.test b/mysql-test/t/show_explain.test index fc83613ae0e..1ee66e3ca2c 100644 --- a/mysql-test/t/show_explain.test +++ b/mysql-test/t/show_explain.test @@ -9,6 +9,9 @@ create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 (a int); insert into t1 select A.a + 10*B.a + 100*C.a from t0 A, t0 B, t0 C; +alter table t1 add b int, add c int, add filler char(32); +update t1 set b=a, c=a, filler='fooo'; +alter table t1 add key(a), add key(b); # # Try killing a non-existent thread @@ -16,7 +19,12 @@ insert into t1 select A.a + 10*B.a + 100*C.a from t0 A, t0 B, t0 C; --error ER_NO_SUCH_THREAD show explain for 2*1000*1000*1000; +--error ER_NOT_SUPPORTED_YET +show explain for (select max(a) from t0); + +# # Setup two threads and their ids +# let $thr1=`select connection_id()`; connect (con1, localhost, root,,); connection con1; @@ -24,36 +32,47 @@ let $thr2=`select connection_id()`; connection default; # SHOW EXPLAIN FOR -echo SHOW explain for thr2; ---disable_query_log --error ER_ERROR_WHEN_EXECUTING_COMMAND -eval show explain for $thr2; ---enable_query_log +evalp show explain for $thr2; # SHOW EXPLAIN FOR -echo SHOW explain for thr1; ---disable_query_log --error ER_ERROR_WHEN_EXECUTING_COMMAND -eval show explain for $thr1; ---enable_query_log +evalp show explain for $thr1; + +let $wait_condition= select State='show_explain_trap' from information_schema.processlist where id=$thr2; + +# +# Test SHOW EXPLAIN for simple queries +# +connection con1; +set debug='d,show_explain_probe_1'; +send select count(*) from t1 where a < 100000; -# SHOW EXPLAIN FOR +connection default; +--source include/wait_condition.inc +evalp show explain for $thr2; +connection con1; +reap; + + +set debug='d,show_explain_probe_1'; +send select max(c) from t1 where a < 10; +connection default; +--source include/wait_condition.inc +evalp show explain for $thr2; connection con1; -select get_lock('optimizer_done', 10); -send select count(*) from t1 where a < 100000 and sleep(a*0 + release_lock('optimizer_done') +1); +reap; + +# We can catch EXPLAIN, too. +set optimizer_switch='index_condition_pushdown=on,mrr=on,mrr_sort_keys=on'; +set debug='d,show_explain_probe_1'; +send explain select max(c) from t1 where a < 10; connection default; -select get_lock('optimizer_done', 100); -echo SHOW explain for thr2; ---disable_query_log -eval show explain for $thr2; ---enable_query_log -select release_lock('optimizer_done'); ---disable_query_log -eval kill query $thr2; ---enable_query_log -echo kill query thr2; - -#insert into t1 values ('one'),('two'),('three'); +--source include/wait_condition.inc +evalp show explain for $thr2; +connection con1; +reap; + ## TODO: Test this: multiple SHOW EXPLAIN calls in course of running of one select ## diff --git a/sql/my_apc.cc b/sql/my_apc.cc index 754b7880c42..91559483b1f 100644 --- a/sql/my_apc.cc +++ b/sql/my_apc.cc @@ -29,6 +29,10 @@ void Apc_target::init() // todo: should use my_pthread_... functions instead? DBUG_ASSERT(!enabled); (void)pthread_mutex_init(&LOCK_apc_queue, MY_MUTEX_INIT_SLOW); + +#ifndef DBUG_OFF + n_calls_processed= 0; +#endif } @@ -217,6 +221,10 @@ void Apc_target::process_apc_requests() request->func(request->func_arg); request->what="func called by process_apc_requests"; +#ifndef DBUG_OFF + n_calls_processed++; +#endif + pthread_cond_signal(&request->COND_request); pthread_mutex_unlock(&request->LOCK_request); diff --git a/sql/my_apc.h b/sql/my_apc.h index 3783bd28f54..3906aa24408 100644 --- a/sql/my_apc.h +++ b/sql/my_apc.h @@ -62,13 +62,17 @@ public: bool make_apc_call(apc_func_t func, void *func_arg, int timeout_sec, bool *timed_out); +#ifndef DBUG_OFF + int n_calls_processed; + //int call_queue_size; +#endif private: class Call_request; int enabled; Call_request *apc_calls; pthread_mutex_t LOCK_apc_queue; - //int call_queue_size; + class Call_request { diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h index a19cca31d1c..e20702b497d 100644 --- a/sql/mysql_priv.h +++ b/sql/mysql_priv.h @@ -2404,6 +2404,10 @@ int rea_create_table(THD *thd, const char *path, int format_number(uint inputflag,uint max_length,char * pos,uint length, char * *errpos); +#ifndef DBUG_OFF +void dbug_serve_apcs(THD *thd, int n_calls); +#endif + /* table.cc */ TABLE_SHARE *alloc_table_share(TABLE_LIST *table_list, char *key, uint key_length); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 2a895bb2798..355b33fa353 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -245,6 +245,25 @@ Item_equal *find_item_equal(COND_EQUAL *cond_equal, Field *field, JOIN_TAB *first_depth_first_tab(JOIN* join); JOIN_TAB *next_depth_first_tab(JOIN* join, JOIN_TAB* tab); +#ifndef DBUG_OFF +// psergey: +void dbug_serve_apcs(THD *thd, int n_calls) +{ + // TODO how do we signal that we're SHOW-EXPLAIN-READY? + const char *save_proc_info= thd->proc_info; + thd_proc_info(thd, "show_explain_trap"); + + int n_apcs= thd->apc_target.n_calls_processed + n_calls; + while (thd->apc_target.n_calls_processed < n_apcs) + { + my_sleep(300); + if (thd->check_killed()) + break; + } + thd_proc_info(thd, save_proc_info); +} +#endif + /** This handles SELECT with and without UNION. */ @@ -2047,6 +2066,8 @@ void JOIN::exec_inner() List *columns_list= &fields_list; int tmp_error; DBUG_ENTER("JOIN::exec"); + + DBUG_EXECUTE_IF("show_explain_probe_1", dbug_serve_apcs(thd, 1);); thd_proc_info(thd, "executing"); error= 0; @@ -20430,7 +20451,6 @@ int JOIN::print_explain(select_result_sink *result, bool on_the_fly, } else if (join->select_lex == join->unit->fake_select_lex) { - //if (!join->select_lex->type) if (on_the_fly) join->select_lex->set_explain_type(on_the_fly); //psergey /* @@ -20561,6 +20581,7 @@ int JOIN::print_explain(select_result_sink *result, bool on_the_fly, join->select_lex->type; item_list.push_back(new Item_string(stype, strlen(stype), cs)); + enum join_type tab_type= tab->type; if ((tab->type == JT_ALL || tab->type == JT_HASH) && tab->select && tab->select->quick) { @@ -20569,9 +20590,9 @@ int JOIN::print_explain(select_result_sink *result, bool on_the_fly, (quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_INTERSECT) || (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT) || (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION)) - tab->type= tab->type == JT_ALL ? JT_INDEX_MERGE : JT_HASH_INDEX_MERGE; + tab_type= tab->type == JT_ALL ? JT_INDEX_MERGE : JT_HASH_INDEX_MERGE; else - tab->type= tab->type == JT_ALL ? JT_RANGE : JT_HASH_RANGE; + tab_type= tab->type == JT_ALL ? JT_RANGE : JT_HASH_RANGE; } /* table */ @@ -20620,8 +20641,8 @@ int JOIN::print_explain(select_result_sink *result, bool on_the_fly, #endif } /* "type" column */ - item_list.push_back(new Item_string(join_type_str[tab->type], - strlen(join_type_str[tab->type]), + item_list.push_back(new Item_string(join_type_str[tab_type], + strlen(join_type_str[tab_type]), cs)); /* Build "possible_keys" value and add it to item_list */ if (!tab->keys.is_clear_all()) @@ -20645,7 +20666,7 @@ int JOIN::print_explain(select_result_sink *result, bool on_the_fly, item_list.push_back(item_null); /* Build "key", "key_len", and "ref" values and add them to item_list */ - if (tab->type == JT_NEXT) + if (tab_type == JT_NEXT) { key_info= table->key_info+tab->index; key_len= key_info->key_length; @@ -20674,12 +20695,12 @@ int JOIN::print_explain(select_result_sink *result, bool on_the_fly, } } } - if (is_hj && tab->type != JT_HASH) + if (is_hj && tab_type != JT_HASH) { tmp2.append(':'); tmp3.append(':'); } - if (tab->type == JT_HASH_NEXT) + if (tab_type == JT_HASH_NEXT) { register uint length; key_info= table->key_info+tab->index; @@ -20701,7 +20722,7 @@ int JOIN::print_explain(select_result_sink *result, bool on_the_fly, item_list.push_back(new Item_string(tmp3.ptr(),tmp3.length(),cs)); else item_list.push_back(item_null); - if (key_info && tab->type != JT_NEXT) + if (key_info && tab_type != JT_NEXT) item_list.push_back(new Item_string(tmp4.ptr(),tmp4.length(),cs)); else item_list.push_back(item_null); @@ -20754,7 +20775,7 @@ int JOIN::print_explain(select_result_sink *result, bool on_the_fly, ha_rows examined_rows; if (tab->select && tab->select->quick) examined_rows= tab->select->quick->records; - else if (tab->type == JT_NEXT || tab->type == JT_ALL || is_hj) + else if (tab_type == JT_NEXT || tab_type == JT_ALL || is_hj) { if (tab->limit) examined_rows= tab->limit; @@ -20793,7 +20814,7 @@ int JOIN::print_explain(select_result_sink *result, bool on_the_fly, /* Build "Extra" field and add it to item_list. */ key_read=table->key_read; - if ((tab->type == JT_NEXT || tab->type == JT_CONST) && + if ((tab_type == JT_NEXT || tab_type == JT_CONST) && table->covering_keys.is_set(tab->index)) key_read=1; if (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT && -- cgit v1.2.1 From 27f760143c839d4ac8ecd4fd2764728e492e921c Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Sun, 25 Sep 2011 13:05:58 +0400 Subject: - Testing: add DBUG_EXECUTE_IF("show_explain_probe_2"... which fires only for selects with given select_id. - Steps towards making SHOW EXPLAIN work for UNIONs. --- mysql-test/r/show_explain.result | 3 +-- mysql-test/t/show_explain.test | 7 +++++-- sql/item_func.cc | 2 +- sql/sql_class.h | 2 ++ sql/sql_lex.cc | 3 ++- sql/sql_select.cc | 38 +++++++++++++++++++++++++++++++++++--- 6 files changed, 46 insertions(+), 9 deletions(-) diff --git a/mysql-test/r/show_explain.result b/mysql-test/r/show_explain.result index 6a142e676fa..874af6e720e 100644 --- a/mysql-test/r/show_explain.result +++ b/mysql-test/r/show_explain.result @@ -14,6 +14,7 @@ show explain for $thr2; ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command show explain for $thr1; ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command +set @show_explain_probe_select_id=1; set debug='d,show_explain_probe_1'; select count(*) from t1 where a < 100000; show explain for $thr2; @@ -21,7 +22,6 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index a a 5 NULL 1000 Using where; Using index count(*) 1000 -set debug='d,show_explain_probe_1'; select max(c) from t1 where a < 10; show explain for $thr2; id select_type table type possible_keys key key_len ref rows Extra @@ -29,7 +29,6 @@ id select_type table type possible_keys key key_len ref rows Extra max(c) 9 set optimizer_switch='index_condition_pushdown=on,mrr=on,mrr_sort_keys=on'; -set debug='d,show_explain_probe_1'; explain select max(c) from t1 where a < 10; show explain for $thr2; id select_type table type possible_keys key key_len ref rows Extra diff --git a/mysql-test/t/show_explain.test b/mysql-test/t/show_explain.test index 1ee66e3ca2c..4ed625c5bca 100644 --- a/mysql-test/t/show_explain.test +++ b/mysql-test/t/show_explain.test @@ -1,6 +1,8 @@ # # Tests for SHOW EXPLAIN FOR functionality # +--source include/have_debug.inc + --disable_warnings drop table if exists t0, t1; --enable_warnings @@ -45,6 +47,7 @@ let $wait_condition= select State='show_explain_trap' from information_schema.pr # Test SHOW EXPLAIN for simple queries # connection con1; +set @show_explain_probe_select_id=1; set debug='d,show_explain_probe_1'; send select count(*) from t1 where a < 100000; @@ -55,7 +58,6 @@ connection con1; reap; -set debug='d,show_explain_probe_1'; send select max(c) from t1 where a < 10; connection default; --source include/wait_condition.inc @@ -65,7 +67,6 @@ reap; # We can catch EXPLAIN, too. set optimizer_switch='index_condition_pushdown=on,mrr=on,mrr_sort_keys=on'; -set debug='d,show_explain_probe_1'; send explain select max(c) from t1 where a < 10; connection default; --source include/wait_condition.inc @@ -73,6 +74,8 @@ evalp show explain for $thr2; connection con1; reap; +# Let's try with a subquery + ## TODO: Test this: multiple SHOW EXPLAIN calls in course of running of one select ## diff --git a/sql/item_func.cc b/sql/item_func.cc index 033537092d8..2bca34f0a76 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -3871,7 +3871,7 @@ longlong Item_func_sleep::val_int() #define extra_size sizeof(double) -static user_var_entry *get_variable(HASH *hash, LEX_STRING &name, +user_var_entry *get_variable(HASH *hash, LEX_STRING &name, bool create_if_not_exists) { user_var_entry *entry; diff --git a/sql/sql_class.h b/sql/sql_class.h index 5d55d7182fc..cec37de6a61 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -3474,6 +3474,8 @@ class user_var_entry DTCollation collation; }; +user_var_entry *get_variable(HASH *hash, LEX_STRING &name, + bool create_if_not_exists); /* Unique -- class for unique (removing of duplicates). diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index a9f07c337fa..3d2f7013a35 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -3529,7 +3529,8 @@ void st_select_lex::set_explain_type(bool on_the_fly) if (this == master_unit()->fake_select_lex) type= "UNION RESULT"; - options|= SELECT_DESCRIBE; + if (!on_the_fly) + options|= SELECT_DESCRIBE; } diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 355b33fa353..1d2c8a10b75 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -262,8 +262,34 @@ void dbug_serve_apcs(THD *thd, int n_calls) } thd_proc_info(thd, save_proc_info); } + + +/* + Usage + + DBUG_EXECUTE_IF("show_explain_probe_2", + if (dbug_user_var_equals_int(thd, "select_id", select_id)) + dbug_serve_apcs(thd, 1); + ); + +*/ + +bool dbug_user_var_equals_int(THD *thd, const char *name, int value) +{ + user_var_entry *var; + LEX_STRING varname= {(char*)name, strlen(name)}; + if ((var= get_variable(&thd->user_vars, varname, FALSE))) + { + bool null_value; + longlong var_value= var->val_int(&null_value); + if (!null_value && var_value == value) + return TRUE; + } + return FALSE; +} #endif + /** This handles SELECT with and without UNION. */ @@ -2067,7 +2093,13 @@ void JOIN::exec_inner() int tmp_error; DBUG_ENTER("JOIN::exec"); - DBUG_EXECUTE_IF("show_explain_probe_1", dbug_serve_apcs(thd, 1);); + DBUG_EXECUTE_IF("show_explain_probe_2", dbug_serve_apcs(thd, 1);); + DBUG_EXECUTE_IF("show_explain_probe_1", + if (dbug_user_var_equals_int(thd, + "show_explain_probe_select_id", + select_lex->select_number)) + dbug_serve_apcs(thd, 1); + ); thd_proc_info(thd, "executing"); error= 0; @@ -20397,8 +20429,8 @@ void JOIN::clear() /** EXPLAIN handling. - Send a description about what how the select will be done to stdout. - + Produce lines explaining execution of *this* select (not including children + selects) @param on_the_fly TRUE <=> we're being executed on-the-fly, so don't make modifications to any select's data structures */ -- cgit v1.2.1 From 2bf31b094118059b6a488c7f1f8f97dfe3d829a3 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Mon, 26 Sep 2011 18:24:49 +0400 Subject: Don't run show_explain.test for embedded server (the patch explains why) --- mysql-test/t/show_explain.test | 22 ++++++++++++++++++++++ 1 file changed, 22 insertions(+) diff --git a/mysql-test/t/show_explain.test b/mysql-test/t/show_explain.test index 4ed625c5bca..dc54ebb9c52 100644 --- a/mysql-test/t/show_explain.test +++ b/mysql-test/t/show_explain.test @@ -7,6 +7,28 @@ drop table if exists t0, t1; --enable_warnings +# +# Testcases in this file do not work with embedded server. The reason for this +# is that we use the following commands for synchronization: +# +# set @show_explain_probe_select_id=1; +# set debug='d,show_explain_probe_1'; +# send select count(*) from t1 where a < 100000; +# +# When ran with mysqltest_embedded, this translates into: +# +# Thread1> DBUG_PUSH("d,show_explain_probe_1"); +# Thread1> create another thread for doing "send ... reap" +# Thread2> mysql_parse("select count(*) from t1 where a < 100000"); +# +# That is, "select count(*) ..." is ran in a thread for which DBUG_PUSH(...) +# has not been called. As a result, show_explain_probe_1 does not fire, and +# "select count(*) ..." does not wait till its SHOW EXPLAIN command, and the +# test fails. +# +-- source include/not_embedded.inc + + create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t1 (a int); -- cgit v1.2.1 From b7a340eeb0acceb23fbc9cb5684200aaff790f98 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Thu, 27 Oct 2011 01:12:02 +0400 Subject: Fix SHOW EXPLAIN for UNIONs. --- sql/sql_lex.cc | 5 ++ sql/sql_select.cc | 148 +++++++++++++++++++++++++++++------------------------- sql/sql_select.h | 3 ++ 3 files changed, 88 insertions(+), 68 deletions(-) diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 3d2f7013a35..f42b9d9ee0e 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -3667,6 +3667,11 @@ int st_select_lex_unit::print_explain(select_result_sink *output) if ((res= sl->print_explain(output))) break; } + if (!fake_select_lex->join) + { + res= print_fake_select_lex_join(output, TRUE /* on the fly */, + fake_select_lex, 0 /* flags */); + } return res; } diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 1d2c8a10b75..ee06ce0c8a6 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -20425,6 +20425,83 @@ void JOIN::clear() } } +int print_fake_select_lex_join(select_result_sink *result, bool on_the_fly, + SELECT_LEX *select_lex, uint8 select_options) +{ + const CHARSET_INFO *cs= system_charset_info; + Item *item_null= new Item_null(); + List item_list; + if (on_the_fly) + select_lex->set_explain_type(on_the_fly); //psergey + /* + here we assume that the query will return at least two rows, so we + show "filesort" in EXPLAIN. Of course, sometimes we'll be wrong + and no filesort will be actually done, but executing all selects in + the UNION to provide precise EXPLAIN information will hardly be + appreciated :) + */ + char table_name_buffer[SAFE_NAME_LEN]; + item_list.empty(); + /* id */ + item_list.push_back(new Item_null); + /* select_type */ + item_list.push_back(new Item_string(select_lex->type, + strlen(select_lex->type), + cs)); + /* table */ + { + SELECT_LEX *sl= select_lex->master_unit()->first_select(); + uint len= 6, lastop= 0; + memcpy(table_name_buffer, STRING_WITH_LEN("next_select()) + { + len+= lastop; + lastop= my_snprintf(table_name_buffer + len, NAME_LEN - len, + "%u,", sl->select_number); + } + if (sl || len + lastop >= NAME_LEN) + { + memcpy(table_name_buffer + len, STRING_WITH_LEN("...>") + 1); + len+= 4; + } + else + { + len+= lastop; + table_name_buffer[len - 1]= '>'; // change ',' to '>' + } + item_list.push_back(new Item_string(table_name_buffer, len, cs)); + } + /* partitions */ + if (/*join->thd->lex->describe*/ select_options & DESCRIBE_PARTITIONS) + item_list.push_back(item_null); + /* type */ + item_list.push_back(new Item_string(join_type_str[JT_ALL], + strlen(join_type_str[JT_ALL]), + cs)); + /* possible_keys */ + item_list.push_back(item_null); + /* key*/ + item_list.push_back(item_null); + /* key_len */ + item_list.push_back(item_null); + /* ref */ + item_list.push_back(item_null); + /* in_rows */ + if (select_options & DESCRIBE_EXTENDED) + item_list.push_back(item_null); + /* rows */ + item_list.push_back(item_null); + /* extra */ + if (select_lex->master_unit()->global_parameters->order_list.first) + item_list.push_back(new Item_string("Using filesort", + 14, cs)); + else + item_list.push_back(new Item_string("", 0, cs)); + + if (result->send_data(item_list)) + return 1; + return 0; +} /** EXPLAIN handling. @@ -20483,74 +20560,9 @@ int JOIN::print_explain(select_result_sink *result, bool on_the_fly, } else if (join->select_lex == join->unit->fake_select_lex) { - if (on_the_fly) - join->select_lex->set_explain_type(on_the_fly); //psergey - /* - here we assume that the query will return at least two rows, so we - show "filesort" in EXPLAIN. Of course, sometimes we'll be wrong - and no filesort will be actually done, but executing all selects in - the UNION to provide precise EXPLAIN information will hardly be - appreciated :) - */ - char table_name_buffer[SAFE_NAME_LEN]; - item_list.empty(); - /* id */ - item_list.push_back(new Item_null); - /* select_type */ - item_list.push_back(new Item_string(join->select_lex->type, - strlen(join->select_lex->type), - cs)); - /* table */ - { - SELECT_LEX *sl= join->unit->first_select(); - uint len= 6, lastop= 0; - memcpy(table_name_buffer, STRING_WITH_LEN("next_select()) - { - len+= lastop; - lastop= my_snprintf(table_name_buffer + len, NAME_LEN - len, - "%u,", sl->select_number); - } - if (sl || len + lastop >= NAME_LEN) - { - memcpy(table_name_buffer + len, STRING_WITH_LEN("...>") + 1); - len+= 4; - } - else - { - len+= lastop; - table_name_buffer[len - 1]= '>'; // change ',' to '>' - } - item_list.push_back(new Item_string(table_name_buffer, len, cs)); - } - /* partitions */ - if (join->thd->lex->describe & DESCRIBE_PARTITIONS) - item_list.push_back(item_null); - /* type */ - item_list.push_back(new Item_string(join_type_str[JT_ALL], - strlen(join_type_str[JT_ALL]), - cs)); - /* possible_keys */ - item_list.push_back(item_null); - /* key*/ - item_list.push_back(item_null); - /* key_len */ - item_list.push_back(item_null); - /* ref */ - item_list.push_back(item_null); - /* in_rows */ - if (join->thd->lex->describe & DESCRIBE_EXTENDED) - item_list.push_back(item_null); - /* rows */ - item_list.push_back(item_null); - /* extra */ - if (join->unit->global_parameters->order_list.first) - item_list.push_back(new Item_string("Using filesort", - 14, cs)); - else - item_list.push_back(new Item_string("", 0, cs)); - - if (result->send_data(item_list)) + if (print_fake_select_lex_join(result, on_the_fly, + join->select_lex, + join->thd->lex->describe)) error= 1; } else if (!join->select_lex->master_unit()->derived || diff --git a/sql/sql_select.h b/sql/sql_select.h index f329f51707f..d6379f3ea92 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -1465,6 +1465,9 @@ inline bool optimizer_flag(THD *thd, uint flag) return (thd->variables.optimizer_switch & flag); } +int print_fake_select_lex_join(select_result_sink *result, bool on_the_fly, + SELECT_LEX *select_lex, uint8 select_options); + /* Table elimination entry point function */ void eliminate_tables(JOIN *join); -- cgit v1.2.1 From ba09d25abc1fd3dbe55f1d7974cfe6a1ebda4df0 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Thu, 27 Oct 2011 21:34:41 +0400 Subject: Fix typo bug in UNION handling, add tests for SHOW EXPLAIN for UNION. --- mysql-test/r/show_explain.result | 29 +++++++++++++++++++++++++++++ mysql-test/t/show_explain.test | 26 +++++++++++++++++++++++++- sql/sql_lex.cc | 6 +++++- 3 files changed, 59 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/show_explain.result b/mysql-test/r/show_explain.result index 874af6e720e..9197cc353c6 100644 --- a/mysql-test/r/show_explain.result +++ b/mysql-test/r/show_explain.result @@ -28,6 +28,8 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 5 NULL 10 Using where max(c) 9 +# We can catch EXPLAIN, too. +set @show_expl_tmp= @@optimizer_switch; set optimizer_switch='index_condition_pushdown=on,mrr=on,mrr_sort_keys=on'; explain select max(c) from t1 where a < 10; show explain for $thr2; @@ -35,4 +37,31 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 5 NULL 10 Using index condition; Rowid-ordered scan id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 5 NULL 10 Using index condition; Rowid-ordered scan +set optimizer_switch= @show_expl_tmp; +# UNION, first branch +set @show_explain_probe_select_id=1; +set debug='d,show_explain_probe_1'; +explain select a from t0 A union select a+1 from t0 B; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY A ALL NULL NULL NULL NULL 10 +2 UNION B ALL NULL NULL NULL NULL 10 +NULL UNION RESULT ALL NULL NULL NULL NULL NULL +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY A ALL NULL NULL NULL NULL 10 +2 UNION B ALL NULL NULL NULL NULL 10 +NULL UNION RESULT ALL NULL NULL NULL NULL NULL +# UNION, second branch +set @show_explain_probe_select_id=1; +set debug='d,show_explain_probe_1'; +explain select a from t0 A union select a+1 from t0 B; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY A ALL NULL NULL NULL NULL 10 +2 UNION B ALL NULL NULL NULL NULL 10 +NULL UNION RESULT ALL NULL NULL NULL NULL NULL +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY A ALL NULL NULL NULL NULL 10 +2 UNION B ALL NULL NULL NULL NULL 10 +NULL UNION RESULT ALL NULL NULL NULL NULL NULL drop table t0,t1; diff --git a/mysql-test/t/show_explain.test b/mysql-test/t/show_explain.test index dc54ebb9c52..77d79fc78b3 100644 --- a/mysql-test/t/show_explain.test +++ b/mysql-test/t/show_explain.test @@ -87,7 +87,9 @@ evalp show explain for $thr2; connection con1; reap; -# We can catch EXPLAIN, too. + +--echo # We can catch EXPLAIN, too. +set @show_expl_tmp= @@optimizer_switch; set optimizer_switch='index_condition_pushdown=on,mrr=on,mrr_sort_keys=on'; send explain select max(c) from t1 where a < 10; connection default; @@ -95,6 +97,28 @@ connection default; evalp show explain for $thr2; connection con1; reap; +set optimizer_switch= @show_expl_tmp; + + +--echo # UNION, first branch +set @show_explain_probe_select_id=1; +set debug='d,show_explain_probe_1'; +send explain select a from t0 A union select a+1 from t0 B; +connection default; +--source include/wait_condition.inc +evalp show explain for $thr2; +connection con1; +reap; + +--echo # UNION, second branch +set @show_explain_probe_select_id=1; +set debug='d,show_explain_probe_1'; +send explain select a from t0 A union select a+1 from t0 B; +connection default; +--source include/wait_condition.inc +evalp show explain for $thr2; +connection con1; +reap; # Let's try with a subquery diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index f42b9d9ee0e..917502f2b5b 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -3667,7 +3667,11 @@ int st_select_lex_unit::print_explain(select_result_sink *output) if ((res= sl->print_explain(output))) break; } - if (!fake_select_lex->join) + + /* + Note: it could be that fake_select_lex->join == NULL still at this point + */ + if (fake_select_lex && !fake_select_lex->join) { res= print_fake_select_lex_join(output, TRUE /* on the fly */, fake_select_lex, 0 /* flags */); -- cgit v1.2.1 From ca020dfa9e8668ce52eaff92c157097bba671ec1 Mon Sep 17 00:00:00 2001 From: Sergey Petrunya Date: Fri, 28 Oct 2011 02:30:02 +0400 Subject: MWL#182: Explain running statements - Get subqueries to work, part #1. --- mysql-test/r/show_explain.result | 61 +++++++++++++++++++++++++++++++++++ mysql-test/t/show_explain.test | 69 +++++++++++++++++++++++++++++++++++++++- sql/sql_lex.cc | 17 ++++++++++ sql/sql_select.cc | 11 +++++-- 4 files changed, 155 insertions(+), 3 deletions(-) diff --git a/mysql-test/r/show_explain.result b/mysql-test/r/show_explain.result index 9197cc353c6..b8b732f9607 100644 --- a/mysql-test/r/show_explain.result +++ b/mysql-test/r/show_explain.result @@ -64,4 +64,65 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY A ALL NULL NULL NULL NULL 10 2 UNION B ALL NULL NULL NULL NULL 10 NULL UNION RESULT ALL NULL NULL NULL NULL NULL +# Uncorrelated subquery, select +set @show_explain_probe_select_id=1; +set debug='d,show_explain_probe_1'; +select a, (select max(a) from t0 B) from t0 A where a<1; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY A ALL NULL NULL NULL NULL 10 Using where +2 SUBQUERY B ALL NULL NULL NULL NULL 10 +a (select max(a) from t0 B) +0 9 +# Uncorrelated subquery, explain +set @show_explain_probe_select_id=1; +set debug='d,show_explain_probe_1'; +explain select a, (select max(a) from t0 B) from t0 A where a<1; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY A ALL NULL NULL NULL NULL 10 Using where +2 SUBQUERY B ALL NULL NULL NULL NULL 10 +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY A ALL NULL NULL NULL NULL 10 Using where +2 SUBQUERY B ALL NULL NULL NULL NULL 10 +# correlated subquery, select +set @show_explain_probe_select_id=1; +set debug='d,show_explain_probe_1'; +select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY a ALL NULL NULL NULL NULL 10 Using where +2 DEPENDENT SUBQUERY b ALL NULL NULL NULL NULL 10 Using where +a (select max(a) from t0 b where b.a+a.a<10) +0 9 +# correlated subquery, explain +set @show_explain_probe_select_id=1; +set debug='d,show_explain_probe_1'; +select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY a ALL NULL NULL NULL NULL 10 Using where +2 DEPENDENT SUBQUERY b ALL NULL NULL NULL NULL 10 Using where +a (select max(a) from t0 b where b.a+a.a<10) +0 9 +# correlated subquery, select, while inside the subquery +set @show_explain_probe_select_id=2; +set debug='d,show_explain_probe_1'; +select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY a ALL NULL NULL NULL NULL 10 Using where +2 DEPENDENT SUBQUERY b ALL NULL NULL NULL NULL 10 Using where +a (select max(a) from t0 b where b.a+a.a<10) +0 9 +# correlated subquery, explain, while inside the subquery +set @show_explain_probe_select_id=2; +set debug='d,show_explain_probe_1'; +select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY a ALL NULL NULL NULL NULL 10 Using where +2 DEPENDENT SUBQUERY b ALL NULL NULL NULL NULL 10 Using where +a (select max(a) from t0 b where b.a+a.a<10) +0 9 drop table t0,t1; diff --git a/mysql-test/t/show_explain.test b/mysql-test/t/show_explain.test index 77d79fc78b3..5ed78be1ea4 100644 --- a/mysql-test/t/show_explain.test +++ b/mysql-test/t/show_explain.test @@ -110,6 +110,7 @@ evalp show explain for $thr2; connection con1; reap; + --echo # UNION, second branch set @show_explain_probe_select_id=1; set debug='d,show_explain_probe_1'; @@ -120,7 +121,73 @@ evalp show explain for $thr2; connection con1; reap; -# Let's try with a subquery + +--echo # Uncorrelated subquery, select +set @show_explain_probe_select_id=1; +set debug='d,show_explain_probe_1'; +send select a, (select max(a) from t0 B) from t0 A where a<1; +connection default; +--source include/wait_condition.inc +evalp show explain for $thr2; +connection con1; +reap; + + +--echo # Uncorrelated subquery, explain +set @show_explain_probe_select_id=1; +set debug='d,show_explain_probe_1'; +send explain select a, (select max(a) from t0 B) from t0 A where a<1; +connection default; +--source include/wait_condition.inc +evalp show explain for $thr2; +connection con1; +reap; + +--echo # correlated subquery, select +set @show_explain_probe_select_id=1; +set debug='d,show_explain_probe_1'; +send select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1; +connection default; +--source include/wait_condition.inc +evalp show explain for $thr2; +connection con1; +reap; + +--echo # correlated subquery, explain +set @show_explain_probe_select_id=1; +set debug='d,show_explain_probe_1'; +send select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1; +connection default; +--source include/wait_condition.inc +evalp show explain for $thr2; +connection con1; +reap; + +--echo # correlated subquery, select, while inside the subquery +set @show_explain_probe_select_id=2; # <--- +set debug='d,show_explain_probe_1'; +send select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1; +connection default; +--source include/wait_condition.inc +evalp show explain for $thr2; +connection con1; +reap; + +--echo # correlated subquery, explain, while inside the subquery +set @show_explain_probe_select_id=2; +set debug='d,show_explain_probe_1'; +send select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1; +connection default; +--source include/wait_condition.inc +evalp show explain for $thr2; +connection con1; +reap; + + +# TODO: explain in the parent subuqery when the un-correlated child has been +# run (and have done irreversible cleanups) + +# TODO: hit JOIN::optimize for non-select commands: UPDATE/DELETE, SET. ## TODO: Test this: multiple SHOW EXPLAIN calls in course of running of one select diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 9aee5caeb64..3b355a312af 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -3706,6 +3706,22 @@ int st_select_lex::print_explain(select_result_sink *output) FALSE, // bool need_order, FALSE, // bool distinct, NULL); //const char *message + if (res) + goto err; + + for (SELECT_LEX_UNIT *unit= join->select_lex->first_inner_unit(); + unit; + unit= unit->next_unit()) + { + /* + Display subqueries only if they are not parts of eliminated WHERE/ON + clauses. + */ + if (!(unit->item && unit->item->eliminated)) + { + unit->print_explain(output); + } + } } else { @@ -3717,6 +3733,7 @@ int st_select_lex::print_explain(select_result_sink *output) FALSE, // bool distinct, msg); //const char *message } +err: return 0; } diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 4dc99cf006c..fcbd2615b9c 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -265,7 +265,9 @@ void dbug_serve_apcs(THD *thd, int n_calls) /* - Usage + Debugging: check if @name=value, comparing as integer + + Intended usage: DBUG_EXECUTE_IF("show_explain_probe_2", if (dbug_user_var_equals_int(thd, "select_id", select_id)) @@ -2102,7 +2104,6 @@ void JOIN::exec_inner() int tmp_error; DBUG_ENTER("JOIN::exec"); - DBUG_EXECUTE_IF("show_explain_probe_2", dbug_serve_apcs(thd, 1);); DBUG_EXECUTE_IF("show_explain_probe_1", if (dbug_user_var_equals_int(thd, "show_explain_probe_select_id", @@ -20516,6 +20517,7 @@ void JOIN::clear() } } + int print_fake_select_lex_join(select_result_sink *result, bool on_the_fly, SELECT_LEX *select_lex, uint8 select_options) { @@ -20594,6 +20596,7 @@ int print_fake_select_lex_join(select_result_sink *result, bool on_the_fly, return 0; } + /** EXPLAIN handling. @@ -21162,6 +21165,10 @@ int JOIN::print_explain(select_result_sink *result, bool on_the_fly, } +/* + See st_select_lex::print_explain() for the SHOW EXPLAIN counterpart +*/ + static void select_describe(JOIN *join, bool need_tmp_table, bool need_order, bool distinct,const char *message) { -- cgit v1.2.1