diff options
author | Sergei Petrunia <psergey@askmonty.org> | 2021-03-19 17:32:08 +0300 |
---|---|---|
committer | Sergei Petrunia <psergey@askmonty.org> | 2021-03-19 17:37:38 +0300 |
commit | b9a45ba40fbf251f5635ecebad6ea7414be39d41 (patch) | |
tree | 465a1936ddced015b9a3f327a0e27b792e16f929 | |
parent | 00528a04457d33210baceba1a79e82ea126b48bd (diff) | |
download | mariadb-git-b9a45ba40fbf251f5635ecebad6ea7414be39d41.tar.gz |
MDEV-23645: Optimizer trace: print conditions after substitute_for_best_equal_field
Print the conditions for WHERE, HAVING, and ON.
-rw-r--r-- | mysql-test/main/opt_trace.result | 236 | ||||
-rw-r--r-- | mysql-test/main/opt_trace.test | 53 | ||||
-rw-r--r-- | mysql-test/main/opt_trace_index_merge.result | 7 | ||||
-rw-r--r-- | mysql-test/main/opt_trace_index_merge_innodb.result | 7 | ||||
-rw-r--r-- | mysql-test/main/opt_trace_security.result | 2 | ||||
-rw-r--r-- | sql/opt_trace.cc | 12 | ||||
-rw-r--r-- | sql/opt_trace.h | 4 | ||||
-rw-r--r-- | 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 @@ -154,8 +154,13 @@ 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": [ { @@ -300,8 +305,13 @@ 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": [ { @@ -453,8 +463,13 @@ 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": [ { @@ -1104,8 +1115,13 @@ 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": [ { @@ -1466,8 +1481,13 @@ 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": [ { @@ -1660,8 +1680,13 @@ 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": [ { @@ -1843,8 +1868,13 @@ 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": [ { @@ -2121,8 +2151,13 @@ 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": [ { @@ -2361,11 +2396,16 @@ 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": [ { @@ -2518,11 +2558,23 @@ 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": [ { @@ -2676,11 +2728,16 @@ 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": [ { @@ -2989,11 +3046,16 @@ 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": [ { @@ -3350,8 +3412,13 @@ 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; @@ -4042,8 +4107,13 @@ 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": [ { @@ -4687,11 +4755,16 @@ 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": [ { @@ -6256,8 +6329,13 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { + "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": [ { @@ -7333,11 +7411,16 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { ] }, { + "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 @@ -230,8 +230,13 @@ 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 @@ -235,8 +235,13 @@ 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; |