From: Jorgen Loland Date: November 11 2010 1:25pm Subject: bzr commit into mysql-next-mr-bugfixing branch (jorgen.loland:3232) List-Archive: http://lists.mysql.com/commits/123617 Message-Id: <20101111132556.581F2169D@atum21.norway.sun.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit #At file:///export/home/jl208045/mysql/wl4800/mysql-next-mr-opt-backporting-wl4800-patchcleanup/ based on revid:jorgen.loland@stripped 3232 Jorgen Loland 2010-11-11 [merge] local merge modified: WL4800_TODO.txt mysql-test/include/optimizer_trace.inc mysql-test/r/mysqld--help-notwin.result mysql-test/r/mysqld--help-win.result mysql-test/r/optimizer_trace_ps_prot.result sql/opt_trace.cc sql/opt_trace.h sql/sql_parse.cc sql/sql_select.cc sql/sql_select.h sql/sys_vars.cc === modified file 'WL4800_TODO.txt' --- a/WL4800_TODO.txt 2010-10-15 12:46:54 +0000 +++ b/WL4800_TODO.txt 2010-11-05 10:20:12 +0000 @@ -29,7 +29,6 @@ changes? any little change? maybe). Coding style: Make sure long code lines are wrapped. -Change true/false to TRUE/FALSE Sort out any charset-related problems (security-wise, for example: can I find a proper combination of client/server charsets so that creating @@ -38,5 +37,7 @@ opt_trace.cc. Review comments left: - in doc, mention DUMPFILE instead of OUTFILE +- in changelog, mention /*select#*/; in doc, mention use of this (to +decrypt what "id" is about in EXPLAIN). - more checks for non-unique keys? === modified file 'mysql-test/include/optimizer_trace.inc' --- a/mysql-test/include/optimizer_trace.inc 2010-10-22 13:28:18 +0000 +++ b/mysql-test/include/optimizer_trace.inc 2010-11-11 13:25:53 +0000 @@ -481,7 +481,7 @@ select * from information_schema.OPTIMIZ drop view v1; # I_S tables -select * from information_schema.SESSION_VARIABLES where +select * from information_schema.session_variables where VARIABLE_NAME="optimizer_trace"; select * from information_schema.OPTIMIZER_TRACE; @@ -496,6 +496,11 @@ select 1 union select 2; --cat_file $file --remove_file $file +# Test for crashing bug +--error ER_WRONG_VALUE_FOR_VAR +set optimizer_switch='default,index_merge=on,index_merge=off,default'; +select @@optimizer_switch; + drop table t1,t2; DROP TABLE t5,t6; set optimizer_trace=default; === modified file 'mysql-test/r/mysqld--help-notwin.result' --- a/mysql-test/r/mysqld--help-notwin.result 2010-10-09 15:04:30 +0000 +++ b/mysql-test/r/mysqld--help-notwin.result 2010-11-05 10:20:12 +0000 @@ -423,8 +423,7 @@ The following options may be given as th --optimizer-trace-limit=# Maximum number of shown optimizer traces --optimizer-trace-max-mem-size=# - maximum allowed cumulated size of remembered optimizer - traces + Maximum allowed cumulated size of stored optimizer traces --optimizer-trace-offset=# Offset of first optimizer trace to show; see manual --performance-schema === modified file 'mysql-test/r/mysqld--help-win.result' --- a/mysql-test/r/mysqld--help-win.result 2010-10-22 09:05:11 +0000 +++ b/mysql-test/r/mysqld--help-win.result 2010-11-05 10:20:12 +0000 @@ -423,7 +423,7 @@ The following options may be given as th --optimizer-trace-limit=# Maximum number of shown optimizer traces --optimizer-trace-max-mem-size=# - maximum allowed cumulated size of remembered optimizer + Maximum allowed cumulated size of stored optimizer traces --optimizer-trace-offset=# Offset of first optimizer trace to show; see manual === modified file 'mysql-test/r/optimizer_trace_ps_prot.result' --- a/mysql-test/r/optimizer_trace_ps_prot.result 2010-10-22 09:05:11 +0000 +++ b/mysql-test/r/optimizer_trace_ps_prot.result 2010-11-06 09:56:51 +0000 @@ -5553,17 +5553,17 @@ explain select * from v1 where id="b" { ] /* steps */ } 0 0 drop view v1; -select * from information_schema.SESSION_VARIABLES where +select * from information_schema.session_variables where VARIABLE_NAME="optimizer_trace"; VARIABLE_NAME VARIABLE_VALUE OPTIMIZER_TRACE enabled=on,end_marker=on,one_line=off select * from information_schema.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OS_MALLOC_ERROR -select * from information_schema.SESSION_VARIABLES where +select * from information_schema.session_variables where VARIABLE_NAME="optimizer_trace" { "steps": [ { - "expanded_query": "/* select#1 */ select `information_schema`.`SESSION_VARIABLES`.`VARIABLE_NAME` AS `VARIABLE_NAME`,`information_schema`.`SESSION_VARIABLES`.`VARIABLE_VALUE` AS `VARIABLE_VALUE` from `information_schema`.`SESSION_VARIABLES` where (`VARIABLE_NAME` = 'optimizer_trace')" + "expanded_query": "/* select#1 */ select `information_schema`.`session_variables`.`VARIABLE_NAME` AS `VARIABLE_NAME`,`information_schema`.`session_variables`.`VARIABLE_VALUE` AS `VARIABLE_VALUE` from `information_schema`.`session_variables` where (`VARIABLE_NAME` = 'optimizer_trace')" }, { "join_preparation": { @@ -5579,10 +5579,10 @@ VARIABLE_NAME="optimizer_trace" { { "condition_processing": { "condition": "WHERE", - "original_condition": "(`information_schema`.`SESSION_VARIABLES`.`VARIABLE_NAME` = 'optimizer_trace')", - "after_equality_propagation": "(`information_schema`.`SESSION_VARIABLES`.`VARIABLE_NAME` = 'optimizer_trace')", - "after_constant_propagation": "(`information_schema`.`SESSION_VARIABLES`.`VARIABLE_NAME` = 'optimizer_trace')", - "after_trivial_conditions_removal": "(`information_schema`.`SESSION_VARIABLES`.`VARIABLE_NAME` = 'optimizer_trace')" + "original_condition": "(`information_schema`.`session_variables`.`VARIABLE_NAME` = 'optimizer_trace')", + "after_equality_propagation": "(`information_schema`.`session_variables`.`VARIABLE_NAME` = 'optimizer_trace')", + "after_constant_propagation": "(`information_schema`.`session_variables`.`VARIABLE_NAME` = 'optimizer_trace')", + "after_trivial_conditions_removal": "(`information_schema`.`session_variables`.`VARIABLE_NAME` = 'optimizer_trace')" } /* condition_processing */ }, { @@ -5594,7 +5594,7 @@ VARIABLE_NAME="optimizer_trace" { ] /* constant_tables */, "records_estimation": [ { - "table": "SESSION_VARIABLES", + "table": "session_variables", "table_scan": { "records": 2, "cost": 10 @@ -5605,7 +5605,7 @@ VARIABLE_NAME="optimizer_trace" { { "considered_execution_plans": [ { - "table": "SESSION_VARIABLES", + "table": "session_variables", "best_access_path": { "considered_access_paths": [ { @@ -5624,11 +5624,11 @@ VARIABLE_NAME="optimizer_trace" { }, { "attaching_conditions_to_tables": { - "original_condition": "(`information_schema`.`SESSION_VARIABLES`.`VARIABLE_NAME` = 'optimizer_trace')", + "original_condition": "(`information_schema`.`session_variables`.`VARIABLE_NAME` = 'optimizer_trace')", "attached_conditions": [ { - "table": "SESSION_VARIABLES", - "attached": "(`information_schema`.`SESSION_VARIABLES`.`VARIABLE_NAME` = 'optimizer_trace')" + "table": "session_variables", + "attached": "(`information_schema`.`session_variables`.`VARIABLE_NAME` = 'optimizer_trace')" } ] /* attached_conditions */ } /* attaching_conditions_to_tables */ @@ -5736,6 +5736,11 @@ select TRACE into dumpfile 'MYSQLTEST_VA } } ] -}drop table t1,t2; +}set optimizer_switch='default,index_merge=on,index_merge=off,default'; +ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'index_merge=off,default' +select @@optimizer_switch; +@@optimizer_switch +index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,mrr=on,mrr_cost_based=off,index_condition_pushdown=on +drop table t1,t2; DROP TABLE t5,t6; set optimizer_trace=default; === modified file 'sql/opt_trace.cc' --- a/sql/opt_trace.cc 2010-10-22 08:31:43 +0000 +++ b/sql/opt_trace.cc 2010-11-11 13:25:53 +0000 @@ -203,6 +203,7 @@ void Opt_trace_struct::do_destruct(void) } } stmt->support_I_S= save_stmt_support_I_S; + started= false; } @@ -828,7 +829,9 @@ void Opt_trace_stmt::next_line(void) return; buffer.append('\n'); char spaces[]= " "; + // 2 spaces per nesting level const int nb= depth * 2, spaces_len= sizeof(spaces) - 1; + // add spaces in chunks of spaces_len, better than many append(' ') for (int i= 0; i < nb/spaces_len; i++) buffer.append(spaces, spaces_len); buffer.append(spaces, (nb % spaces_len)); === modified file 'sql/opt_trace.h' --- a/sql/opt_trace.h 2010-10-22 08:31:43 +0000 +++ b/sql/opt_trace.h 2010-11-11 13:25:53 +0000 @@ -951,6 +951,12 @@ public: return *this; return do_add(NULL, value); } + /** + The exception to RAII: this function is an explicit way to end a + structure before it goes out of scope. Don't use it unless RAII mandates + a new scope which mandates re-indenting lots of code lines. + */ + void end(void) { if (unlikely(started)) do_destruct(); } private: /** Full initialization. @sa Opt_trace_struct::Opt_trace_struct */ @@ -1298,6 +1304,7 @@ int make_optimizer_trace_table_for_show( /* all empty */ +/** Empty implementation used when optimizer trace is not compiled in */ class Opt_trace_context { public: @@ -1305,6 +1312,7 @@ public: enum feature_value { MISC= 1, GREEDY_SEARCH= 2 }; }; +/** Empty implementation used when optimizer trace is not compiled in */ class Opt_trace_object { public: @@ -1328,6 +1336,7 @@ public: Opt_trace_object& add(const char *key, double value) { return *this; } }; +/** Empty implementation used when optimizer trace is not compiled in */ class Opt_trace_array { public: @@ -1349,6 +1358,7 @@ public: Opt_trace_array& add(double value) { return *this; } }; +/** Empty implementation used when optimizer trace is not compiled in */ class Opt_trace_disable_I_S { public: === modified file 'sql/sql_parse.cc' --- a/sql/sql_parse.cc 2010-10-09 15:04:30 +0000 +++ b/sql/sql_parse.cc 2010-11-06 09:56:51 +0000 @@ -2072,13 +2072,8 @@ mysql_execute_command(THD *thd) */ } - { - /* - need to open scope because oto/ota must be destroyed before calling - opt_trace_end(). - */ - Opt_trace_object oto(thd->opt_trace); - Opt_trace_array ota(thd->opt_trace, "steps"); + Opt_trace_object trace_command(thd->opt_trace); + Opt_trace_array trace_command_steps(thd->opt_trace, "steps"); DBUG_ASSERT(thd->transaction.stmt.modified_non_trans_table == FALSE); @@ -4455,8 +4450,6 @@ create_sp_error: goto finish; - } // end of scope for opt_trace structures - error: res= TRUE; @@ -4530,6 +4523,8 @@ finish: thd->mdl_context.release_transactional_locks(); } + trace_command_steps.end(); + trace_command.end(); // must be closed before trace is ended below opt_trace_end(thd, started_optimizer_trace); DBUG_RETURN(res || thd->is_error()); === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2010-10-22 08:31:43 +0000 +++ b/sql/sql_select.cc 2010-11-11 13:25:53 +0000 @@ -1801,8 +1801,11 @@ static int clear_sj_tmp_tables(JOIN *joi int JOIN::optimize() { + bool need_distinct; + ulonglong select_opts_for_readinfo; + uint no_jbuf_after; + DBUG_ENTER("JOIN::optimize"); - Opt_trace_context * const trace= thd->opt_trace; // to prevent double initialization on EXPLAIN if (optimized) DBUG_RETURN(0); @@ -1810,44 +1813,11 @@ JOIN::optimize() thd_proc_info(thd, "optimizing"); + Opt_trace_context * const trace= thd->opt_trace; Opt_trace_object trace_wrapper(trace); Opt_trace_object trace_optimize(trace, "join_optimization"); opt_trace_add_select_number(&trace_optimize, select_lex->select_number); - - int rc= JOIN::optimize_steps(); - - if (zero_result_cause != NULL) - { - Opt_trace_object oto2(trace, "empty_result"); - oto2.add_str("cause", zero_result_cause); - /* - Even with zero matching rows, subqueries in the HAVING clause may - need to be evaluated if there are aggregate functions in the - query. If we have planned to materialize the subquery, we need to - set it up properly before prematurely leaving optimize(). - */ - if (setup_subquery_materialization()) - rc= 1; - else - error= rc= 0; - } - - DBUG_RETURN(rc); -} - -/** - All steps of JOIN::optimize() - @retval 0 ok - @retval !=0 error -*/ -int JOIN::optimize_steps() -{ - DBUG_ENTER("JOIN::optimize_steps"); - Opt_trace_context * const trace= thd->opt_trace; Opt_trace_array trace_steps(trace, "steps"); - bool need_distinct; - ulonglong select_opts_for_readinfo; - uint no_jbuf_after; /* dump_TABLE_LIST_graph(select_lex, select_lex->leaf_tables); */ if (flatten_subqueries()) @@ -1937,7 +1907,7 @@ int JOIN::optimize_steps() zero_result_cause= select_lex->having_value == Item::COND_FALSE ? "Impossible HAVING" : "Impossible WHERE"; tables= 0; - goto end; + goto setup_subq_exit; } } @@ -1987,7 +1957,7 @@ int JOIN::optimize_steps() DBUG_PRINT("info",("No matching min/max row")); zero_result_cause= "No matching min/max row"; tables= 0; - goto end; + goto setup_subq_exit; } if (res > 1) { @@ -2000,7 +1970,7 @@ int JOIN::optimize_steps() DBUG_PRINT("info",("No matching min/max row")); zero_result_cause= "No matching min/max row"; tables= 0; - goto end; + goto setup_subq_exit; } DBUG_PRINT("info",("Select tables optimized away")); zero_result_cause= "Select tables optimized away"; @@ -2026,14 +1996,14 @@ int JOIN::optimize_steps() QT_ORDINARY);); conds= table_independent_conds; } - goto end; + goto setup_subq_exit; } } if (!tables_list) { DBUG_PRINT("info",("No tables")); error= 0; - goto end; + DBUG_RETURN(0); } error= -1; // Error is sent to client sort_by_table= get_sort_by_table(order, group_list, select_lex->leaf_tables); @@ -2075,7 +2045,7 @@ int JOIN::optimize_steps() { zero_result_cause= "no matching row in const table"; DBUG_PRINT("error",("Error: %s", zero_result_cause)); - goto end; + goto setup_subq_exit; } if (!(thd->variables.option_bits & OPTION_BIG_SELECTS) && best_read > (double) thd->variables.max_join_size && @@ -2139,7 +2109,7 @@ int JOIN::optimize_steps() { zero_result_cause= "Impossible WHERE noticed after reading const tables"; - goto end; + goto setup_subq_exit; } error= -1; /* if goto err */ @@ -2410,7 +2380,7 @@ int JOIN::optimize_steps() having= new Item_int((longlong) 0,1); zero_result_cause= "Impossible HAVING noticed after reading const tables"; error= 0; - goto end; + DBUG_RETURN(0); } } @@ -2487,13 +2457,11 @@ int JOIN::optimize_steps() for (uint i = const_tables; i < tables; i++) join_tab[i].table->prepare_for_position(); } - { - DBUG_EXECUTE("info", - { - Opt_trace_disable_I_S otd(trace, true); - TEST_join(this); - }); - } + DBUG_EXECUTE("info", + { + Opt_trace_disable_I_S otd(trace, true); + TEST_join(this); + }); if (const_tables != tables) { @@ -2549,7 +2517,7 @@ int JOIN::optimize_steps() if (select_options & SELECT_DESCRIBE) { error= 0; - goto end; + DBUG_RETURN(0); } having= 0; @@ -2691,7 +2659,23 @@ int JOIN::optimize_steps() } error= 0; -end: + DBUG_RETURN(0); + +setup_subq_exit: + + DBUG_ASSERT(zero_result_cause != NULL); + trace_steps.end(); // because all steps are done + Opt_trace_object trace_empty_result(trace, "empty_result"); + trace_empty_result.add_str("cause", zero_result_cause); + /* + Even with zero matching rows, subqueries in the HAVING clause may + need to be evaluated if there are aggregate functions in the + query. If we have planned to materialize the subquery, we need to + set it up properly before prematurely leaving optimize(). + */ + if (setup_subquery_materialization()) + DBUG_RETURN(1); + error= 0; DBUG_RETURN(0); } === modified file 'sql/sql_select.h' --- a/sql/sql_select.h 2010-09-27 09:26:39 +0000 +++ b/sql/sql_select.h 2010-11-06 09:56:51 +0000 @@ -1959,7 +1959,6 @@ private: */ bool implicit_grouping; bool make_simple_join(JOIN *join, TABLE *tmp_table); - int optimize_steps(); }; === modified file 'sql/sys_vars.cc' --- a/sql/sys_vars.cc 2010-10-09 15:04:30 +0000 +++ b/sql/sys_vars.cc 2010-11-05 10:20:12 +0000 @@ -1462,7 +1462,7 @@ static Sys_var_long Sys_optimizer_trace_ static Sys_var_ulong Sys_optimizer_trace_max_mem_size( "optimizer_trace_max_mem_size", - "maximum allowed cumulated size of remembered optimizer traces", + "Maximum allowed cumulated size of stored optimizer traces", SESSION_VAR(optimizer_trace_max_mem_size), CMD_LINE(REQUIRED_ARG), VALID_RANGE(0, ULONG_MAX), DEFAULT(1024*16), BLOCK_SIZE(1)); No bundle (reason: revision is a merge).