revision-id: f3eafa5e05c924bfcf152a3200009d3f3c5819fc (mariadb-10.3.26-79-gf3eafa5) parent(s): da88e1ec12b0ba39552bf54367c1bb3b89eac4a8 author: Igor Babaev committer: Igor Babaev timestamp: 2021-02-21 22:01:24 -0800 message: MDEV-24936 EXPLAIN for query based on table value constructor lacks info on used subqueries If a query was based on a table value constructor that contained subqueries then EXPLAIN for such query did not contain any lines explaining the execution plans of the subqueries. This happened because - no optimize() method was called for any subquery used by the table value constructor when EXPLAIN command for the query was processed; - EXPLAIN node created for the table value constructor itself did not assume that some child nodes could be attached to it. Approved by Oleksandr Byelkin <sanja@mariadb.com> --- mysql-test/main/table_value_constr.result | 33 +++++++++++++++++++++++++++++++ mysql-test/main/table_value_constr.test | 25 +++++++++++++++++++++++ sql/sql_tvc.cc | 16 +++++++++++++-- 3 files changed, 72 insertions(+), 2 deletions(-) diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result index d7b3286..e112aca 100644 --- a/mysql-test/main/table_value_constr.result +++ b/mysql-test/main/table_value_constr.result @@ -2742,4 +2742,37 @@ NULL UNION RESULT <union4,5> ALL NULL NULL NULL NULL NULL NULL Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <nop>(<in_optimizer>(`test`.`t1`.`a`,<min>(/* select#4 */ select `tvc_0`.`7` from (values (7)) `tvc_0` union /* select#5 */ select `tvc_0`.`8` from (values (8)) `tvc_0`) <= <cache>(`test`.`t1`.`a`))) drop table t1; +# +# MDEV-24934:EXPLAIN for queries based on TVC using subqueries +# +create table t1 (a int); +insert into t1 values (3), (7), (1); +values (8), ((select * from t1 where a between 2 and 4)); +8 +8 +3 +explain values (8), ((select * from t1 where a between 2 and 4)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +values ((select * from t1 where a between 2 and 4)), +((select * from t1 where a > 10)); +(select * from t1 where a between 2 and 4) +3 +NULL +explain values ((select * from t1 where a between 2 and 4)), +((select * from t1 where a > 10)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +3 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +values (10,11), ((select * from t1 where a = 7) + 1, 21); +10 11 +10 11 +8 21 +explain values (10,11), ((select * from t1 where a = 7) + 1, 21); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +drop table t1; End of 10.3 tests diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test index bac85ff..0a78fd9 100644 --- a/mysql-test/main/table_value_constr.test +++ b/mysql-test/main/table_value_constr.test @@ -1434,4 +1434,29 @@ eval explain extended $q; drop table t1; +--echo # +--echo # MDEV-24934:EXPLAIN for queries based on TVC using subqueries +--echo # + +create table t1 (a int); +insert into t1 values (3), (7), (1); + +let $q1= +values (8), ((select * from t1 where a between 2 and 4)); +eval $q1; +eval explain $q1; + +let $q2= +values ((select * from t1 where a between 2 and 4)), + ((select * from t1 where a > 10)); +eval $q2; +eval explain $q2; + +let $q3= +values (10,11), ((select * from t1 where a = 7) + 1, 21); +eval $q3; +eval explain $q3; + +drop table t1; + --echo End of 10.3 tests diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc index 78607b6..0a5f668 100644 --- a/sql/sql_tvc.cc +++ b/sql/sql_tvc.cc @@ -342,6 +342,13 @@ int table_value_constr::save_explain_data_intern(THD *thd, if (select_lex->master_unit()->derived) explain->connection_type= Explain_node::EXPLAIN_NODE_DERIVED; + for (SELECT_LEX_UNIT *unit= select_lex->first_inner_unit(); + unit; + unit= unit->next_unit()) + { + explain->add_child(unit->first_select()->select_number); + } + output->add_node(explain); if (select_lex->is_top_level_node()) @@ -366,9 +373,14 @@ bool table_value_constr::optimize(THD *thd) thd->lex->explain && // for "SET" command in SPs. (!thd->lex->explain->get_select(select_lex->select_number))) { - return save_explain_data_intern(thd, thd->lex->explain); + if (save_explain_data_intern(thd, thd->lex->explain)) + return true; } - return 0; + + if (select_lex->optimize_unflattened_subqueries(true)) + return true; + + return false; }