revision-id: 5a73769ca4377e76b4019b9cecbc942401052923 (mariadb-10.5.2-488-g5a73769ca43) parent(s): 2b3fd5dff04c699a4743867f73a1bacc8549abe1 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-03-19 17:32:08 +0300 message: MDEV-23645: Optimizer trace: print conditions after substitute_for_best_equal_field Print the conditions for WHERE, HAVING, and ON. --- mysql-test/main/opt_trace.result | 236 ++++++++++++++++++--- mysql-test/main/opt_trace.test | 53 +++++ mysql-test/main/opt_trace_index_merge.result | 7 +- .../main/opt_trace_index_merge_innodb.result | 7 +- mysql-test/main/opt_trace_security.result | 2 - sql/opt_trace.cc | 12 ++ sql/opt_trace.h | 4 + sql/sql_select.cc | 15 +- 8 files changed, 301 insertions(+), 35 deletions(-) diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index 36bbea8ae78..192f9040c35 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -153,9 +153,14 @@ select * from v1 { { "best_join_order": ["t1"] }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "t1.a = 1" + } + }, { "attaching_conditions_to_tables": { - "original_condition": "t1.a = 1", "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -299,9 +304,14 @@ select * from (select * from t1 where t1.a=1)q { { "best_join_order": ["t1"] }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "t1.a = 1" + } + }, { "attaching_conditions_to_tables": { - "original_condition": "t1.a = 1", "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -452,9 +462,14 @@ select * from v2 { { "best_join_order": ["t1"] }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "t1.a = 1" + } + }, { "attaching_conditions_to_tables": { - "original_condition": "t1.a = 1", "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -520,7 +535,6 @@ select * from v2 { }, { "attaching_conditions_to_tables": { - "original_condition": null, "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -659,7 +673,6 @@ explain select * from v2 { }, { "attaching_conditions_to_tables": { - "original_condition": null, "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -779,7 +792,6 @@ explain select * from v1 { }, { "attaching_conditions_to_tables": { - "original_condition": null, "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -845,7 +857,6 @@ explain select * from v1 { }, { "attaching_conditions_to_tables": { - "original_condition": null, "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -1103,9 +1114,14 @@ explain select * from t1,t2 where t1.a=t2.b+2 and t2.a= t1.b { { "best_join_order": ["t1", "t2"] }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "t2.a = t1.b and t1.a = t2.b + 2" + } + }, { "attaching_conditions_to_tables": { - "original_condition": "t2.a = t1.b and t1.a = t2.b + 2", "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -1274,7 +1290,6 @@ EXPLAIN SELECT DISTINCT a FROM t1 { }, { "attaching_conditions_to_tables": { - "original_condition": null, "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -1465,9 +1480,14 @@ EXPLAIN SELECT MIN(d) FROM t1 where b=2 and c=3 group by a { { "best_join_order": ["t1"] }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "t1.b = 2 and t1.c = 3" + } + }, { "attaching_conditions_to_tables": { - "original_condition": "t1.b = 2 and t1.c = 3", "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -1659,9 +1679,14 @@ EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id { { "best_join_order": ["t1"] }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "t1.a >= 20010104e0" + } + }, { "attaching_conditions_to_tables": { - "original_condition": "t1.a >= 20010104e0", "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -1842,9 +1867,14 @@ EXPLAIN SELECT * FROM t1 WHERE a = 20010104e0 GROUP BY id { { "best_join_order": ["t1"] }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "t1.a = 20010104e0" + } + }, { "attaching_conditions_to_tables": { - "original_condition": "t1.a = 20010104e0", "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -2120,9 +2150,14 @@ explain select * from t1 where a=1 and b=2 order by c limit 1 { { "best_join_order": ["t1"] }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "t1.a = 1 and t1.b = 2" + } + }, { "attaching_conditions_to_tables": { - "original_condition": "t1.a = 1 and t1.b = 2", "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -2360,12 +2395,17 @@ select t1.a from t1 left join t2 on t1.a=t2.a { { "best_join_order": ["t2", "t1"] }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "1" + } + }, { "condition_on_constant_tables": "1" }, { "attaching_conditions_to_tables": { - "original_condition": "1", "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -2517,12 +2557,24 @@ explain select * from t1 left join t2 on t2.a=t1.a { { "best_join_order": ["t1", "t2"] }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "1" + } + }, + { + "substitute_best_equal": { + "condition": "ON expr", + "attached_to": "t2", + "resulting_condition": "t2.a = t1.a" + } + }, { "condition_on_constant_tables": "1" }, { "attaching_conditions_to_tables": { - "original_condition": "1", "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -2675,12 +2727,17 @@ explain select t1.a from t1 left join (t2 join t3 on t2.b=t3.b) on t2.a=t1.a and { "best_join_order": ["t3", "t2", "t1"] }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "1" + } + }, { "condition_on_constant_tables": "1" }, { "attaching_conditions_to_tables": { - "original_condition": "1", "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -2988,12 +3045,17 @@ explain extended select * from t1 where a in (select pk from t10) { { "best_join_order": ["t1", "<subquery2>"] }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "1" + } + }, { "condition_on_constant_tables": "1" }, { "attaching_conditions_to_tables": { - "original_condition": "1", "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -3349,9 +3411,14 @@ explain select * from t1 where pk = 2 and a=5 and b=1 { { "best_join_order": ["t1"] }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "t1.pk = 2 and t1.a = 5 and t1.b = 1" + } + }, { "attaching_conditions_to_tables": { - "original_condition": "t1.pk = 2 and t1.a = 5 and t1.b = 1", "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -3475,7 +3542,6 @@ select f1(a) from t1 { }, { "attaching_conditions_to_tables": { - "original_condition": null, "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -3573,7 +3639,6 @@ select f2(a) from t1 { }, { "attaching_conditions_to_tables": { - "original_condition": null, "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -3611,7 +3676,7 @@ a 2 select length(trace) from INFORMATION_SCHEMA.OPTIMIZER_TRACE; length(trace) -2183 +2141 set optimizer_trace_max_mem_size=100; select * from t1; a @@ -3625,7 +3690,7 @@ select * from t1 { "join_preparation": { "select_id": 1, "steps": [ - 2083 0 + 2041 0 set optimizer_trace_max_mem_size=0; select * from t1; a @@ -3633,7 +3698,7 @@ a 2 select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES -select * from t1 2183 0 +select * from t1 2141 0 drop table t1; set optimizer_trace='enabled=off'; set @@optimizer_trace_max_mem_size= @save_optimizer_trace_max_mem_size; @@ -4041,9 +4106,14 @@ explain delete t0,t1 from t0, t1 where t0.a=t1.a and t1.a<3 { { "best_join_order": ["t0", "t1"] }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "t1.a = t0.a and t0.a < 3" + } + }, { "attaching_conditions_to_tables": { - "original_condition": "t1.a = t0.a and t0.a < 3", "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -4180,7 +4250,6 @@ explain select * from (select rand() from t1)q { }, { "attaching_conditions_to_tables": { - "original_condition": null, "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -4246,7 +4315,6 @@ explain select * from (select rand() from t1)q { }, { "attaching_conditions_to_tables": { - "original_condition": null, "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -4686,12 +4754,17 @@ explain select * from t1 where a in (select t_inner_1.a from t1 t_inner_1, t1 t_ { "best_join_order": ["t1", "<subquery2>"] }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "1" + } + }, { "condition_on_constant_tables": "1" }, { "attaching_conditions_to_tables": { - "original_condition": "1", "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -6255,9 +6328,14 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "t_inner_3" ] }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "t_inner_1.a = t_outer_1.a and t_inner_3.a = t_outer_2.a" + } + }, { "attaching_conditions_to_tables": { - "original_condition": "t_inner_1.a = t_outer_1.a and t_inner_3.a = t_outer_2.a", "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -7332,12 +7410,17 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { "<subquery3>" ] }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "1" + } + }, { "condition_on_constant_tables": "1" }, { "attaching_conditions_to_tables": { - "original_condition": "1", "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -8644,7 +8727,6 @@ select count(*) from seq_1_to_10000000 { }, { "attaching_conditions_to_tables": { - "original_condition": null, "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -8832,4 +8914,98 @@ S "cause": "no predicate for first keypart" } drop table t1,t2,t3; +# +# MDEV-23645: Optimizer trace: print conditions after substitute_for_best_equal_field +# +create table t1 (a int, b int, c int); +insert into t1 values (1,1,1),(2,2,2); +create table t2 as select * from t1; +insert into t2 select * from t2; +create table t3 as select * from t2; +insert into t3 select * from t3; +# Check how HAVING is printed +explain +select +a,b, count(*) +from t1 +where a=3 +group by b,b +having a+b < 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where; Using temporary; Using filesort +select +json_detailed(json_extract(trace, '$**.substitute_best_equal')) +from +information_schema.optimizer_trace; +json_detailed(json_extract(trace, '$**.substitute_best_equal')) +[ + + { + "condition": "WHERE", + "resulting_condition": "t1.a = 3" + }, + + { + "condition": "HAVING", + "resulting_condition": "t1.a + t1.b < 10" + } +] +# Check ON expression +explain +select +* +from t1 left join t2 on t2.a=t1.a and t2.a<3 +where +t1.b > 5555; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) +select +json_detailed(json_extract(trace, '$**.substitute_best_equal')) +from +information_schema.optimizer_trace; +json_detailed(json_extract(trace, '$**.substitute_best_equal')) +[ + + { + "condition": "WHERE", + "resulting_condition": "t1.b > 5555" + }, + + { + "condition": "ON expr", + "attached_to": "t2", + "resulting_condition": "t2.a = t1.a and t1.a < 3" + } +] +# Check nested ON expression +explain +select +* +from t1 left join (t2,t3) on t2.a=t1.a and t3.a=t2.a and t3.a + t2.a <1000 +where +t1.b > 5555; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (incremental, BNL join) +select +json_detailed(json_extract(trace, '$**.substitute_best_equal')) +from +information_schema.optimizer_trace; +json_detailed(json_extract(trace, '$**.substitute_best_equal')) +[ + + { + "condition": "WHERE", + "resulting_condition": "t1.b > 5555" + }, + + { + "condition": "ON expr", + "attached_to": "t2", + "resulting_condition": "t2.a = t1.a and t3.a = t1.a and t1.a + t1.a < 1000" + } +] +drop table t1,t2,t3; set optimizer_trace='enabled=off'; diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test index 83fd5ac0f40..c4166774ab1 100644 --- a/mysql-test/main/opt_trace.test +++ b/mysql-test/main/opt_trace.test @@ -727,4 +727,57 @@ select drop table t1,t2,t3; +--echo # +--echo # MDEV-23645: Optimizer trace: print conditions after substitute_for_best_equal_field +--echo # +create table t1 (a int, b int, c int); +insert into t1 values (1,1,1),(2,2,2); + +create table t2 as select * from t1; +insert into t2 select * from t2; + +create table t3 as select * from t2; +insert into t3 select * from t3; + +--echo # Check how HAVING is printed +explain +select + a,b, count(*) +from t1 +where a=3 +group by b,b +having a+b < 10; + +select + json_detailed(json_extract(trace, '$**.substitute_best_equal')) +from + information_schema.optimizer_trace; + +--echo # Check ON expression +explain +select + * +from t1 left join t2 on t2.a=t1.a and t2.a<3 +where + t1.b > 5555; + +select + json_detailed(json_extract(trace, '$**.substitute_best_equal')) +from + information_schema.optimizer_trace; + +--echo # Check nested ON expression +explain +select + * +from t1 left join (t2,t3) on t2.a=t1.a and t3.a=t2.a and t3.a + t2.a <1000 +where + t1.b > 5555; +select + json_detailed(json_extract(trace, '$**.substitute_best_equal')) +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 3b79a7a44e8..f1e13586eda 100644 --- a/mysql-test/main/opt_trace_index_merge.result +++ b/mysql-test/main/opt_trace_index_merge.result @@ -229,9 +229,14 @@ explain select * from t1 where a=1 or b=1 { { "best_join_order": ["t1"] }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "t1.a = 1 or t1.b = 1" + } + }, { "attaching_conditions_to_tables": { - "original_condition": "t1.a = 1 or t1.b = 1", "attached_conditions_computation": [], "attached_conditions_summary": [ { diff --git a/mysql-test/main/opt_trace_index_merge_innodb.result b/mysql-test/main/opt_trace_index_merge_innodb.result index 509569021a5..0ddaaeae89d 100644 --- a/mysql-test/main/opt_trace_index_merge_innodb.result +++ b/mysql-test/main/opt_trace_index_merge_innodb.result @@ -234,9 +234,14 @@ explain select * from t1 where pk1 != 0 and key1 = 1 { { "best_join_order": ["t1"] }, + { + "substitute_best_equal": { + "condition": "WHERE", + "resulting_condition": "t1.key1 = 1 and t1.pk1 <> 0" + } + }, { "attaching_conditions_to_tables": { - "original_condition": "t1.key1 = 1 and t1.pk1 <> 0", "attached_conditions_computation": [], "attached_conditions_summary": [ { diff --git a/mysql-test/main/opt_trace_security.result b/mysql-test/main/opt_trace_security.result index 2c09f6c7d5a..e1937e744a4 100644 --- a/mysql-test/main/opt_trace_security.result +++ b/mysql-test/main/opt_trace_security.result @@ -117,7 +117,6 @@ select * from db1.t1 { }, { "attaching_conditions_to_tables": { - "original_condition": null, "attached_conditions_computation": [], "attached_conditions_summary": [ { @@ -240,7 +239,6 @@ select * from db1.v1 { }, { "attaching_conditions_to_tables": { - "original_condition": null, "attached_conditions_computation": [], "attached_conditions_summary": [ { diff --git a/sql/opt_trace.cc b/sql/opt_trace.cc index ddec6d5ed2d..e1f402a4d7c 100644 --- a/sql/opt_trace.cc +++ b/sql/opt_trace.cc @@ -595,6 +595,18 @@ void Json_writer::add_table_name(const TABLE *table) } +void trace_condition(THD * thd, const char *name, const char *transform_type, + Item *item, const char *table_name) +{ + Json_writer_object trace_wrapper(thd); + Json_writer_object trace_cond(thd, transform_type); + trace_cond.add("condition", name); + if (table_name) + trace_cond.add("attached_to", table_name); + trace_cond.add("resulting_condition", item); +} + + void add_table_scan_values_to_trace(THD *thd, JOIN_TAB *tab) { DBUG_ASSERT(thd->trace_started()); diff --git a/sql/opt_trace.h b/sql/opt_trace.h index 550f18c0797..101fb5f707e 100644 --- a/sql/opt_trace.h +++ b/sql/opt_trace.h @@ -108,6 +108,10 @@ void print_final_join_order(JOIN *join); void print_best_access_for_table(THD *thd, POSITION *pos, enum join_type type); +void trace_condition(THD * thd, const char *name, const char *transform_type, + Item *item, const char *table_name= nullptr); + + /* Security related (need to add a proper comment here) */ diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 5447c398b66..05dc145f5cb 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -2408,6 +2408,10 @@ int JOIN::optimize_stage2() DBUG_RETURN(1); } conds->update_used_tables(); + + if (unlikely(thd->trace_started())) + trace_condition(thd, "WHERE", "substitute_best_equal", conds); + DBUG_EXECUTE("where", print_where(conds, "after substitute_best_equal", @@ -2424,7 +2428,12 @@ int JOIN::optimize_stage2() DBUG_RETURN(1); } if (having) + { having->update_used_tables(); + if (unlikely(thd->trace_started())) + trace_condition(thd, "HAVING", "substitute_best_equal", having); + } + DBUG_EXECUTE("having", print_where(having, "after substitute_best_equal", @@ -2451,6 +2460,11 @@ int JOIN::optimize_stage2() DBUG_RETURN(1); } (*tab->on_expr_ref)->update_used_tables(); + if (unlikely(thd->trace_started())) + { + trace_condition(thd, "ON expr", "substitute_best_equal", + (*tab->on_expr_ref), tab->table->alias.c_ptr()); + } } } @@ -11479,7 +11493,6 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond) */ Json_writer_object trace_wrapper(thd); Json_writer_object trace_conditions(thd, "attaching_conditions_to_tables"); - trace_conditions.add("original_condition", cond); Json_writer_array trace_attached_comp(thd, "attached_conditions_computation"); uint i;