From e7f289c514290d4995b0f3c2fc764e0eb11675ca Mon Sep 17 00:00:00 2001 From: Varun Gupta Date: Mon, 18 Jan 2021 12:29:12 +0530 Subject: MDEV-23646: Optimizer trace: optimize_cond() should show ON expression processing Print in the optimizer trace equality and constant propagation of the ON expression --- mysql-test/main/opt_trace.result | 234 ++++++++++++++++++++- mysql-test/main/opt_trace.test | 6 + mysql-test/main/opt_trace_index_merge.result | 7 + .../main/opt_trace_index_merge_innodb.result | 7 + mysql-test/main/opt_trace_security.result | 14 ++ mysql-test/main/subselect2.result | 4 +- sql/opt_trace.cc | 22 ++ sql/opt_trace.h | 2 + sql/sql_select.cc | 74 ++++++- sql/sys_vars.cc | 2 +- 10 files changed, 359 insertions(+), 13 deletions(-) diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index fdf2d2a338a..94bf20aaa4f 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -6,7 +6,7 @@ OPTIMIZER_TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE OPTIMIZER_TRACE INSUFFICIENT_PRIVILEGES show variables like 'optimizer_trace'; Variable_name Value -optimizer_trace enabled=off +optimizer_trace enabled=on set optimizer_trace="enabled=on"; show variables like 'optimizer_trace'; Variable_name Value @@ -88,6 +88,13 @@ select * from v1 { ] } }, + { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, { "table_dependencies": [ { @@ -239,6 +246,13 @@ select * from (select * from t1 where t1.a=1)q { ] } }, + { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, { "table_dependencies": [ { @@ -371,6 +385,13 @@ select * from v2 { "join_optimization": { "select_id": 1, "steps": [ + { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, { "join_optimization": { "select_id": 2, @@ -395,6 +416,13 @@ select * from v2 { ] } }, + { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, { "table_dependencies": [ { @@ -626,6 +654,13 @@ explain select * from v2 { "join_optimization": { "select_id": 1, "steps": [ + { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, { "table_dependencies": [ { @@ -745,10 +780,24 @@ explain select * from v1 { "join_optimization": { "select_id": 1, "steps": [ + { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, { "join_optimization": { "select_id": 2, "steps": [ + { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, { "table_dependencies": [ { @@ -969,6 +1018,13 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { ] } }, + { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, { "table_dependencies": [ { @@ -1200,6 +1256,13 @@ EXPLAIN SELECT DISTINCT a FROM t1 { "join_optimization": { "select_id": 1, "steps": [ + { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, { "table_dependencies": [ { @@ -1390,6 +1453,13 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { ] } }, + { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, { "table_dependencies": [ { @@ -1608,6 +1678,13 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id { ] } }, + { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, { "table_dependencies": [ { @@ -1804,6 +1881,13 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id { ] } }, + { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, { "table_dependencies": [ { @@ -2027,6 +2111,13 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { ] } }, + { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, { "table_dependencies": [ { @@ -2383,6 +2474,13 @@ select t1.a from t1 left join t2 on t1.a=t2.a { "join_optimization": { "select_id": 1, "steps": [ + { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": ["t1.a = t2.a"], + "transformed_expr": ["multiple equal(t1.a, t2.a)"] + } + }, { "table_dependencies": [ { @@ -2513,6 +2611,13 @@ explain select * from t1 left join t2 on t2.a=t1.a { "join_optimization": { "select_id": 1, "steps": [ + { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": ["t2.a = t1.a"], + "transformed_expr": ["multiple equal(t2.a, t1.a)"] + } + }, { "table_dependencies": [ { @@ -2678,6 +2783,15 @@ explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and "join_optimization": { "select_id": 1, "steps": [ + { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": ["t2.a = t1.a and t3.a = t1.a and t2.b = t3.b"], + "transformed_expr": [ + "multiple equal(t2.a, t1.a, t3.a) and multiple equal(t2.b, t3.b)" + ] + } + }, { "table_dependencies": [ { @@ -2916,6 +3030,13 @@ explain extended select * from t1 where a in (select pk from t10) { ] } }, + { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, { "table_dependencies": [ { @@ -3205,6 +3326,13 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { ] } }, + { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, { "table_dependencies": [ { @@ -3547,6 +3675,13 @@ select f1(a) from t1 { "join_optimization": { "select_id": 1, "steps": [ + { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, { "table_dependencies": [ { @@ -3650,6 +3785,13 @@ select f2(a) from t1 { "join_optimization": { "select_id": 1, "steps": [ + { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, { "table_dependencies": [ { @@ -3746,7 +3888,7 @@ a 2 select length(trace) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; length(trace) -2342 +2531 set optimizer_trace_max_mem_size=100; select * from t1; a @@ -3760,7 +3902,7 @@ select * from t1 { "join_preparation": { "select_id": 1, "steps": [ - 2242 0 + 2431 0 set optimizer_trace_max_mem_size=0; select * from t1; a @@ -3768,7 +3910,7 @@ a 2 select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES -select * from t1 2342 0 +select * from t1 2531 0 drop table t1; set optimizer_trace='enabled=off'; set @@optimizer_trace_max_mem_size= @save_optimizer_trace_max_mem_size; @@ -3892,6 +4034,13 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { ] } }, + { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, { "table_dependencies": [ { @@ -4262,10 +4411,24 @@ explain select * from (select rand() from t1)q { "cause": "Random function in the select" } }, + { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, { "join_optimization": { "select_id": 2, "steps": [ + { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, { "table_dependencies": [ { @@ -4513,6 +4676,13 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ ] } }, + { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, { "table_dependencies": [ { @@ -4998,6 +5168,13 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] } }, + { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, { "table_dependencies": [ { @@ -6578,6 +6755,13 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] } }, + { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, { "table_dependencies": [ { @@ -8755,6 +8939,48 @@ JSON_DETAILED(JSON_EXTRACT(trace, '$**.on_clause_after_substitution')) "t3.a = t1.a" ] ] +# +# MDEV-23646: Optimizer trace: optimize_cond() should show ON expression processing +# +SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.condition_processing')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; +JSON_DETAILED(JSON_EXTRACT(trace, '$**.condition_processing')) +[ + + { + "condition": "WHERE", + "original_condition": "t1.a = t2.a", + "steps": + [ + + { + "transformation": "equality_propagation", + "resulting_condition": "multiple equal(t1.a, t2.a)" + }, + + { + "transformation": "constant_propagation", + "resulting_condition": "multiple equal(t1.a, t2.a)" + }, + + { + "transformation": "trivial_condition_removal", + "resulting_condition": "multiple equal(t1.a, t2.a)" + } + ] + }, + + { + "condition": "ON CLAUSE", + "original_expr": + [ + "t2.a = t3.a" + ], + "transformed_expr": + [ + "multiple equal(t1.a, t2.a, t3.a)" + ] + } +] DROP TABLE t1,t2,t3; set optimizer_trace='enabled=off'; # End of 10.4 tests diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test index 99ddbae8079..9f83a596e38 100644 --- a/mysql-test/main/opt_trace.test +++ b/mysql-test/main/opt_trace.test @@ -642,6 +642,12 @@ where t1.a = t2.a; SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.where_clause_after_substitution')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.having_clause_after_substitution')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.on_clause_after_substitution')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; + +--echo # +--echo # MDEV-23646: Optimizer trace: optimize_cond() should show ON expression processing +--echo # + +SELECT JSON_DETAILED(JSON_EXTRACT(trace, '$**.condition_processing')) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; DROP TABLE t1,t2,t3; set optimizer_trace='enabled=off'; diff --git a/mysql-test/main/opt_trace_index_merge.result b/mysql-test/main/opt_trace_index_merge.result index c64758bcaa4..a938dc106fd 100644 --- a/mysql-test/main/opt_trace_index_merge.result +++ b/mysql-test/main/opt_trace_index_merge.result @@ -53,6 +53,13 @@ explain select * from t1 where a=1 or b=1 { ] } }, + { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, { "table_dependencies": [ { diff --git a/mysql-test/main/opt_trace_index_merge_innodb.result b/mysql-test/main/opt_trace_index_merge_innodb.result index 614595c380a..427384c5ec4 100644 --- a/mysql-test/main/opt_trace_index_merge_innodb.result +++ b/mysql-test/main/opt_trace_index_merge_innodb.result @@ -61,6 +61,13 @@ explain select * from t1 where pk1 != 0 and key1 = 1 { ] } }, + { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, { "table_dependencies": [ { diff --git a/mysql-test/main/opt_trace_security.result b/mysql-test/main/opt_trace_security.result index c94d7231ce0..c5386c39670 100644 --- a/mysql-test/main/opt_trace_security.result +++ b/mysql-test/main/opt_trace_security.result @@ -64,6 +64,13 @@ select * from db1.t1 { "join_optimization": { "select_id": 1, "steps": [ + { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, { "table_dependencies": [ { @@ -192,6 +199,13 @@ select * from db1.v1 { "join_optimization": { "select_id": 1, "steps": [ + { + "condition_processing": { + "condition": "ON CLAUSE", + "original_expr": [], + "transformed_expr": [] + } + }, { "table_dependencies": [ { diff --git a/mysql-test/main/subselect2.result b/mysql-test/main/subselect2.result index a3d7fda7abc..6084d7e6209 100644 --- a/mysql-test/main/subselect2.result +++ b/mysql-test/main/subselect2.result @@ -339,10 +339,10 @@ where t1.a = t2.a and ( t1.a = ( select min(a) from t1 ) or 0 ); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where 1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) -1 PRIMARY t3 ref idx idx 6 func 2 100.00 Using where; Using index +1 PRIMARY t3 ref idx idx 6 const 1 100.00 Using where; Using index 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,`test`.`t3`.`a` AS `a` from `test`.`t1` join `test`.`t2` left join `test`.`t3` on(`test`.`t3`.`a` = `test`.`t1`.`a`) where `test`.`t1`.`a` = (/* select#2 */ select min(`test`.`t1`.`a`) from `test`.`t1`) and `test`.`t2`.`a` = (/* select#2 */ select min(`test`.`t1`.`a`) from `test`.`t1`) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t2`.`a` AS `a`,`test`.`t3`.`a` AS `a` from `test`.`t1` join `test`.`t2` left join `test`.`t3` on(`test`.`t3`.`a` = (/* select#2 */ select min(`test`.`t1`.`a`) from `test`.`t1`)) where `test`.`t1`.`a` = (/* select#2 */ select min(`test`.`t1`.`a`) from `test`.`t1`) and `test`.`t2`.`a` = (/* select#2 */ select min(`test`.`t1`.`a`) from `test`.`t1`) select * from t1, t2 left join t3 on ( t2.a = t3.a ) where t1.a = t2.a and ( t1.a = ( select min(a) from t1 ) or 0 ); a a a diff --git a/sql/opt_trace.cc b/sql/opt_trace.cc index 2d14972aba5..a49273c0a9f 100644 --- a/sql/opt_trace.cc +++ b/sql/opt_trace.cc @@ -689,6 +689,28 @@ void print_on_expr(JOIN *join, Json_writer_array *trace_on_expr) } +void print_on_expr(THD *thd, List *join_list, + Json_writer_array *trace_array) +{ + if (join_list == NULL) + return; + TABLE_LIST *table; + List_iterator li(*join_list); + + while ((table= li++)) + { + if (table->on_expr) + { + List *nested_join_list= table->nested_join ? + &table->nested_join->join_list : NULL; + + trace_array->add(table->on_expr); + print_on_expr(thd, nested_join_list, trace_array); + } + } +} + + /* Introduce enum_query_type flags parameter, maybe also allow EXPLAIN also use this function. diff --git a/sql/opt_trace.h b/sql/opt_trace.h index 37053971ee3..c421d6a5036 100644 --- a/sql/opt_trace.h +++ b/sql/opt_trace.h @@ -108,6 +108,8 @@ void print_final_join_order(JOIN *join); void print_best_access_for_table(THD *thd, POSITION *pos, enum join_type type); void print_on_expr(JOIN *join, Json_writer_array *trace_on_expr); +void print_on_expr(THD *thd, List *join_list, + Json_writer_array *trace_array); /* Security related (need to add a proper comment here) */ diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 3830cc4ea25..13dce02f160 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -170,6 +170,12 @@ static COND *optimize_cond(JOIN *join, COND *conds, Item::cond_result *cond_value, COND_EQUAL **cond_equal, int flags= 0); + +static void optimize_on_expr(JOIN *join, + List *join_list, + bool ignore_on_conds, + int flags= 0); + bool const_expression_in_where(COND *conds,Item *item, Item **comp_item); static int do_select(JOIN *join, Procedure *procedure); @@ -2014,9 +2020,11 @@ JOIN::optimize_inner() ignore_on_expr= true; break; } - conds= optimize_cond(this, conds, join_list, ignore_on_expr, + conds= optimize_cond(this, conds, join_list, TRUE, &cond_value, &cond_equal, OPT_LINK_EQUAL_FIELDS); + optimize_on_expr(this, join_list, ignore_on_expr); + if (thd->is_error()) { error= 1; @@ -2472,11 +2480,19 @@ int JOIN::optimize_stage2() if (unlikely(thd->trace_started())) { - Json_writer_object trace_wrapper(thd); - trace_wrapper.add("where_clause_after_substitution", conds); - trace_wrapper.add("having_clause_after_substitution", having); - Json_writer_array trace_on_expr(thd, "on_clause_after_substitution"); - print_on_expr(this, &trace_on_expr); + if (having || conds || outer_join) + { + Json_writer_object trace_wrapper(thd); + if (conds) + trace_wrapper.add("where_clause_after_substitution", conds); + if (having) + trace_wrapper.add("having_clause_after_substitution", having); + if (outer_join) + { + Json_writer_array trace_on_expr(thd, "on_clause_after_substitution"); + print_on_expr(this, &trace_on_expr); + } + } } /* @@ -17007,6 +17023,52 @@ optimize_cond(JOIN *join, COND *conds, } +static void optimize_on_expr(JOIN *join, + List *join_list, + bool ignore_on_conds, + int flags) +{ + if (ignore_on_conds || !join_list) + return; + + THD *thd= join->thd; + Json_writer_object trace_wrapper(thd); + Json_writer_object trace_cond(thd, "condition_processing"); + trace_cond.add("condition", "ON CLAUSE"); + { + Json_writer_array trace_array(thd, "original_expr"); + print_on_expr(thd, join_list, &trace_array); + } + + if (join_list && !ignore_on_conds) + { + TABLE_LIST *table; + List_iterator li(*join_list); + + while ((table= li++)) + { + if (table->on_expr) + { + List *nested_join_list= table->nested_join ? + &table->nested_join->join_list : NULL; + /* + We can modify table->on_expr because its old value will + be restored before re-execution of PS/SP. + */ + table->on_expr= build_equal_items(join, table->on_expr, join->cond_equal, + nested_join_list, ignore_on_conds, + &table->cond_equal); + } + } + } + + { + Json_writer_array trace_array(thd, "transformed_expr"); + print_on_expr(thd, join_list, &trace_array); + } +} + + /** @brief Propagate multiple equalities to the sub-expressions of a condition diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index 5f5f9b5daf8..fea924f28fe 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -2638,7 +2638,7 @@ static Sys_var_flagset Sys_optimizer_trace( " {enabled}" " and val is one of {on, off, default}", SESSION_VAR(optimizer_trace), CMD_LINE(REQUIRED_ARG), - Opt_trace_context::flag_names, DEFAULT(Opt_trace_context::FLAG_DEFAULT)); + Opt_trace_context::flag_names, DEFAULT(Opt_trace_context::FLAG_ENABLED)); // @see set_var::is_var_optimizer_trace() export sys_var *Sys_optimizer_trace_ptr = &Sys_optimizer_trace; -- cgit v1.2.1