diff options
| -rw-r--r-- | mysql-test/r/cte_recursive.result | 162 | ||||
| -rw-r--r-- | mysql-test/t/cte_recursive.test | 39 | ||||
| -rw-r--r-- | sql/sp_head.cc | 4 |
3 files changed, 205 insertions, 0 deletions
diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result index 667b8c4289d..0b8bc3fa8ab 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 2c20e095130..1c0280f065e 100644 --- a/mysql-test/t/cte_recursive.test +++ b/mysql-test/t/cte_recursive.test @@ -2536,3 +2536,42 @@ 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 464f2df1506..6a650183fb8 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -1126,6 +1126,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; @@ -1201,6 +1202,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; @@ -1468,6 +1471,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; |