revision-id: ca43dc27f404ce83004ddcc3bb75166e3c021010 (mariadb-10.2.31-252-gca43dc2) parent(s): 29ed04cb6d8e930c2b2bad7baf4cdae02cf712cd author: Igor Babaev committer: Igor Babaev timestamp: 2020-06-05 09:37:02 -0700 message: MDEV-22042 Server crash in Item_field::print on ANALYZE FORMAT=JSON When processing a query with a recursive CTE a temporary table is used for each recursive reference of the CTE. As any temporary table it uses its own mem-root for table definition structures. Due to specifics of the current implementation of ANALYZE stmt command this mem-root can be freed only at the very of query processing. Such deallocation of mem-root memory happens in close_thread_tables(). The function looks through the list of the tmp tables rec_tables attached to the THD of the query and frees corresponding mem-roots. If the query uses a stored function then such list is created for each query of the function. When a new rec_list has to be created the old one has to be saved and then restored at the proper moment. The bug occurred because only one rec_list for the query containing CTE was created. As a result close_thread_tables() freed tmp mem-roots used for rec_tables prematurely destroying some data needed for the output produced by the ANALYZE command. --- mysql-test/r/cte_recursive.result | 162 ++++++++++++++++++++++++++++++++++++++ mysql-test/t/cte_recursive.test | 40 ++++++++++ sql/sp_head.cc | 4 + 3 files changed, 206 insertions(+) diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result index 667b8c4..51358de 100644 --- a/mysql-test/r/cte_recursive.result +++ b/mysql-test/r/cte_recursive.result @@ -3648,3 +3648,165 @@ select * from t1 as t; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t ALL NULL NULL NULL NULL 4 drop table t1,t2; +# +# MDEV-22042: ANALYZE of query using stored function and recursive CTE +# +create table t1 (a1 varchar(20),a2 varchar(20)) engine=myisam; +insert into t1 values (1,1),(2,2),(3,3); +create table t2 ( +a2 varchar(20) primary key, b1 varchar(20), key (b1) +) engine=myisam; +insert into t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7); +insert into t2 values (11,11),(12,12),(13,13),(14,14),(15,15),(16,16),(17,17); +create function f1(id varchar(20)) returns varchar(50) +begin +declare res varchar (50); +select a2 into res from t2 where a2=id and b1=1 limit 1; +return res; +end$$ +select fv +from (select t1.a1, f1(t1.a2) fv from t1) dt +where (dt.a1) in (with recursive cte as (select a2 from t2 where a2='2' + union select tt2.a2 from t2 tt2 join cte on tt2.b1=cte.a2) +select a2 from cte); +fv +NULL +explain select fv +from (select t1.a1, f1(t1.a2) fv from t1) dt +where (dt.a1) in (with recursive cte as (select a2 from t2 where a2='2' + union select tt2.a2 from t2 tt2 join cte on tt2.b1=cte.a2) +select a2 from cte); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2 +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) +3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 +4 DERIVED t2 const PRIMARY PRIMARY 22 const 1 Using index +5 RECURSIVE UNION <derived4> ALL NULL NULL NULL NULL 2 +5 RECURSIVE UNION tt2 ref b1 b1 23 cte.a2 2 +NULL UNION RESULT <union4,5> ALL NULL NULL NULL NULL NULL +analyze format=json select fv +from (select t1.a1, f1(t1.a2) fv from t1) dt +where (dt.a1) in (with recursive cte as (select a2 from t2 where a2='2' + union select tt2.a2 from t2 tt2 join cte on tt2.b1=cte.a2) +select a2 from cte); +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "<subquery3>", + "access_type": "ALL", + "possible_keys": ["distinct_key"], + "r_loops": 1, + "rows": 2, + "r_rows": 1, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 3, + "table": { + "table_name": "<derived4>", + "access_type": "ALL", + "r_loops": 1, + "rows": 2, + "r_rows": 1, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "materialized": { + "query_block": { + "recursive_union": { + "table_name": "<union4,5>", + "access_type": "ALL", + "r_loops": 0, + "r_rows": null, + "query_specifications": [ + { + "query_block": { + "select_id": 4, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "t2", + "access_type": "const", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "22", + "used_key_parts": ["a2"], + "ref": ["const"], + "r_loops": 0, + "rows": 1, + "r_rows": null, + "filtered": 100, + "r_filtered": null, + "using_index": true + } + } + }, + { + "query_block": { + "select_id": 5, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "<derived4>", + "access_type": "ALL", + "r_loops": 1, + "rows": 2, + "r_rows": 1, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + }, + "table": { + "table_name": "tt2", + "access_type": "ref", + "possible_keys": ["b1"], + "key": "b1", + "key_length": "23", + "used_key_parts": ["b1"], + "ref": ["cte.a2"], + "r_loops": 1, + "rows": 2, + "r_rows": 1, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + } + } + ] + } + } + } + } + } + } + }, + "block-nl-join": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "r_loops": 1, + "rows": 3, + "r_rows": 3, + "r_total_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "256Kb", + "join_type": "BNL", + "attached_condition": "t1.a1 = cte.a2", + "r_filtered": 33.333 + } + } +} +drop function f1; +drop table t1,t2; +End of 10.2 tests diff --git a/mysql-test/t/cte_recursive.test b/mysql-test/t/cte_recursive.test index 2c20e09..5a1d055 100644 --- a/mysql-test/t/cte_recursive.test +++ b/mysql-test/t/cte_recursive.test @@ -2536,3 +2536,43 @@ with recursive cte as select * from t1 as t; drop table t1,t2; + +--echo # +--echo # MDEV-22042: ANALYZE of query using stored function and recursive CTE +--echo # + +create table t1 (a1 varchar(20),a2 varchar(20)) engine=myisam; +insert into t1 values (1,1),(2,2),(3,3); + +create table t2 ( +a2 varchar(20) primary key, b1 varchar(20), key (b1) +) engine=myisam; +insert into t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7); +insert into t2 values (11,11),(12,12),(13,13),(14,14),(15,15),(16,16),(17,17); + +delimiter $$; +create function f1(id varchar(20)) returns varchar(50) +begin + declare res varchar (50); + select a2 into res from t2 where a2=id and b1=1 limit 1; + return res; +end$$ +delimiter ;$$ + +let q= +select fv +from (select t1.a1, f1(t1.a2) fv from t1) dt +where (dt.a1) in (with recursive cte as (select a2 from t2 where a2='2' + union select tt2.a2 from t2 tt2 join cte on tt2.b1=cte.a2) +select a2 from cte); + +eval $q; +eval explain $q; +--source include/analyze-format.inc +eval analyze format=json $q; + +drop function f1; +drop table t1,t2; + +--echo End of 10.2 tests + diff --git a/sql/sp_head.cc b/sql/sp_head.cc index 1f5c6e9..1565500 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -1123,6 +1123,7 @@ sp_head::execute(THD *thd, bool merge_da_on_success) backup_arena; query_id_t old_query_id; TABLE *old_derived_tables; + TABLE *old_rec_tables; LEX *old_lex; Item_change_list old_change_list; String old_packet; @@ -1198,6 +1199,8 @@ sp_head::execute(THD *thd, bool merge_da_on_success) old_query_id= thd->query_id; old_derived_tables= thd->derived_tables; thd->derived_tables= 0; + old_rec_tables= thd->rec_tables; + thd->rec_tables= 0; save_sql_mode= thd->variables.sql_mode; thd->variables.sql_mode= m_sql_mode; save_abort_on_warning= thd->abort_on_warning; @@ -1379,6 +1382,7 @@ sp_head::execute(THD *thd, bool merge_da_on_success) thd->set_query_id(old_query_id); DBUG_ASSERT(!thd->derived_tables); thd->derived_tables= old_derived_tables; + thd->rec_tables= old_rec_tables; thd->variables.sql_mode= save_sql_mode; thd->abort_on_warning= save_abort_on_warning; thd->m_reprepare_observer= save_reprepare_observer;