revision-id: b6b5429e49bc652c5344cbf48c66aa1790e24598 (mariadb-10.5.2-419-gb6b5429e49b) parent(s): 9ed08f357693875c9d2376651305aa66199d2c35 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2020-06-15 12:59:53 +0300 message: MDEV-22891: Optimizer trace: const tables are not clearly visible Make mark_join_nest_as_const() print its action into the trace. --- mysql-test/main/opt_trace.result | 31 +++++++++++++++++++++++++++++++ mysql-test/main/opt_trace.test | 23 +++++++++++++++++++++++ sql/sql_select.cc | 6 ++++++ 3 files changed, 60 insertions(+) diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index ad7bce44445..6af1c2afe9e 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -8530,5 +8530,36 @@ select count(*) from seq_1_to_10000000 { } ] } 0 0 +# +# MDEV-22891: Optimizer trace: const tables are not clearly visible +# +create table t0(a int primary key); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 (pk int primary key, a int); +insert into t1 select a,a from t0; +create table t2 (pk int primary key, a int); +insert into t2 select a,a from t0; +create table t3 (pk int primary key, a int); +insert into t3 select a,a from t0; +explain +select * from t1 left join (t2 join t3 on t3.pk=1000) on t2.a=t1.a and t2.pk is null; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 const PRIMARY NULL NULL NULL 1 Impossible ON condition +1 SIMPLE t2 const PRIMARY NULL NULL NULL 1 Impossible ON condition +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 +select JSON_DETAILED(JSON_EXTRACT(trace, '$**.mark_join_nest_as_const')) +from information_schema.optimizer_trace; +JSON_DETAILED(JSON_EXTRACT(trace, '$**.mark_join_nest_as_const')) +[ + + { + "members": + [ + "t3", + "t2" + ] + } +] +drop table t0, t1, t2, t3; # End of 10.5 tests set optimizer_trace='enabled=off'; diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test index f2d7b67983d..f7f19e38eac 100644 --- a/mysql-test/main/opt_trace.test +++ b/mysql-test/main/opt_trace.test @@ -604,5 +604,28 @@ DROP TABLE t1,t2; select count(*) from seq_1_to_10000000; select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; +--echo # +--echo # MDEV-22891: Optimizer trace: const tables are not clearly visible +--echo # +create table t0(a int primary key); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t1 (pk int primary key, a int); +insert into t1 select a,a from t0; + +create table t2 (pk int primary key, a int); +insert into t2 select a,a from t0; + +create table t3 (pk int primary key, a int); +insert into t3 select a,a from t0; + +explain +select * from t1 left join (t2 join t3 on t3.pk=1000) on t2.a=t1.a and t2.pk is null; + +select JSON_DETAILED(JSON_EXTRACT(trace, '$**.mark_join_nest_as_const')) +from information_schema.optimizer_trace; + +drop table t0, t1, t2, t3; + --echo # End of 10.5 tests set optimizer_trace='enabled=off'; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 76cb96b93fd..fc4aec08302 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -4737,6 +4737,10 @@ void mark_join_nest_as_const(JOIN *join, { List_iterator<TABLE_LIST> it(join_nest->nested_join->join_list); TABLE_LIST *tbl; + Json_writer_object emb_obj(join->thd); + Json_writer_object trace_obj(join->thd, "mark_join_nest_as_const"); + Json_writer_array trace_array(join->thd, "members"); + while ((tbl= it++)) { if (tbl->nested_join) @@ -4756,6 +4760,8 @@ void mark_join_nest_as_const(JOIN *join, *found_const_table_map|= tab->table->map; set_position(join,(*const_count)++,tab,(KEYUSE*) 0); mark_as_null_row(tab->table); // All fields are NULL + + trace_array.add_table_name(tab->table); } } }