revision-id: b3c470a3c7e8bb497bca5bc9fca4cf52cfc9e88e (mariadb-10.5.2-495-gb3c470a3c7e) parent(s): b9a45ba40fbf251f5635ecebad6ea7414be39d41 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-03-19 18:12:26 +0300 message: MDEV-23646: Optimizer trace: optimize_cond() should show ON expression processing Print the build_equal_items() step for ON expression processing --- mysql-test/main/opt_trace.result | 62 ++++++++++++++++++++++++++++++++++++++++ mysql-test/main/opt_trace.test | 8 ++++++ sql/sql_select.cc | 10 +++++++ 3 files changed, 80 insertions(+) diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index 192f9040c35..4d00e32c4a7 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -2319,6 +2319,13 @@ select t1.a from t1 left join t2 on t1.a=t2.a { "join_optimization": { "select_id": 1, "steps": [ + { + "build_equal_items": { + "condition": "ON expr", + "attached_to": "t2", + "resulting_condition": "multiple equal(t1.a, t2.a)" + } + }, { "table_dependencies": [ { @@ -2449,6 +2456,13 @@ explain select * from t1 left join t2 on t2.a=t1.a { "join_optimization": { "select_id": 1, "steps": [ + { + "build_equal_items": { + "condition": "ON expr", + "attached_to": "t2", + "resulting_condition": "multiple equal(t2.a, t1.a)" + } + }, { "table_dependencies": [ { @@ -2621,6 +2635,13 @@ 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": [ + { + "build_equal_items": { + "condition": "ON expr", + "attached_to": "t3", + "resulting_condition": "multiple equal(t2.a, t1.a, t3.a) and multiple equal(t2.b, t3.b)" + } + }, { "table_dependencies": [ { @@ -9007,5 +9028,46 @@ json_detailed(json_extract(trace, '$**.substitute_best_equal')) "resulting_condition": "t2.a = t1.a and t3.a = t1.a and t1.a + t1.a < 1000" } ] +# The next query is test for: +# 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.b > 5555", + "steps": + [ + + { + "build_equal_items": + { + "condition": "ON expr", + "attached_to": "t3", + "resulting_condition": "t3.a + t2.a < 1000 and multiple equal(t2.a, t1.a, t3.a)" + } + }, + + { + "transformation": "equality_propagation", + "resulting_condition": "t1.b > 5555" + }, + + { + "transformation": "constant_propagation", + "resulting_condition": "t1.b > 5555" + }, + + { + "transformation": "trivial_condition_removal", + "resulting_condition": "t1.b > 5555" + } + ] + } +] 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 c4166774ab1..b351699c7a6 100644 --- a/mysql-test/main/opt_trace.test +++ b/mysql-test/main/opt_trace.test @@ -773,11 +773,19 @@ 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; +--echo # The next query is test for: +--echo # MDEV-23646: Optimizer trace: optimize_cond() should show ON expression processing +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/sql/sql_select.cc b/sql/sql_select.cc index 05dc145f5cb..93f5d3591ed 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -15488,6 +15488,16 @@ static COND *build_equal_items(JOIN *join, COND *cond, table->on_expr= build_equal_items(join, table->on_expr, inherited, nested_join_list, ignore_on_conds, &table->cond_equal); + if (unlikely(join->thd->trace_started())) + { + const char *table_name; + if (table->nested_join) + table_name= table->nested_join->join_list.head()->alias.str; + else + table_name= table->alias.str; + trace_condition(join->thd, "ON expr", "build_equal_items", + table->on_expr, table_name); + } } } }