diff options
Diffstat (limited to 'mysql-test/main/cte_recursive.result')
| -rw-r--r-- | mysql-test/main/cte_recursive.result | 234 |
1 files changed, 234 insertions, 0 deletions
diff --git a/mysql-test/main/cte_recursive.result b/mysql-test/main/cte_recursive.result index deb791f25a3..d8ea858ad5a 100644 --- a/mysql-test/main/cte_recursive.result +++ b/mysql-test/main/cte_recursive.result @@ -3998,6 +3998,240 @@ YEAR d1 d2 DROP PROCEDURE p; DROP TABLE t1,t2,t3,t4; # +# MDEV-23619: recursive CTE used only in the second operand of UNION +# +create table t1 ( +a bigint(10) not null auto_increment, +b int(5) not null, +c bigint(10) default null, +primary key (a) +) engine myisam; +insert into t1 values +(1,3,12), (2,7,15), (3,1,3), (4,3,1); +explain with recursive r_cte as +( select * from t1 as s +union +select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t; +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 DERIVED s ALL NULL NULL NULL NULL 4 +3 RECURSIVE UNION t1 ALL NULL NULL NULL NULL 4 Using where +3 RECURSIVE UNION <derived2> ref key0 key0 9 test.t1.c 2 +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +4 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 4 +with recursive r_cte as +( select * from t1 as s +union +select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t; +b +0 +3 +7 +1 +3 +analyze format=json with recursive r_cte as +( select * from t1 as s +union +select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t; +ANALYZE +{ + "query_block": { + "union_result": { + "table_name": "<union1,4>", + "access_type": "ALL", + "r_loops": 0, + "r_rows": null, + "query_specifications": [ + { + "query_block": { + "select_id": 1, + "table": { + "message": "No tables used" + } + } + }, + { + "query_block": { + "select_id": 4, + "operation": "UNION", + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "r_loops": 1, + "rows": 4, + "r_rows": 4, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "materialized": { + "query_block": { + "recursive_union": { + "table_name": "<union2,3>", + "access_type": "ALL", + "r_loops": 0, + "r_rows": null, + "query_specifications": [ + { + "query_block": { + "select_id": 2, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "s", + "access_type": "ALL", + "r_loops": 1, + "rows": 4, + "r_rows": 4, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + } + }, + { + "query_block": { + "select_id": 3, + "operation": "UNION", + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "t1", + "access_type": "ALL", + "r_loops": 1, + "rows": 4, + "r_rows": 4, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "attached_condition": "t1.c is not null" + }, + "table": { + "table_name": "<derived2>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "9", + "used_key_parts": ["a"], + "ref": ["test.t1.c"], + "r_loops": 4, + "rows": 2, + "r_rows": 0.5, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + } + } + ] + } + } + } + } + } + } + ] + } + } +} +prepare stmt from "with recursive r_cte as +( select * from t1 as s +union +select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t"; +execute stmt; +b +0 +3 +7 +1 +3 +execute stmt; +b +0 +3 +7 +1 +3 +deallocate prepare stmt; +#checking hanging cte that uses a recursive cte +explain with h_cte as +( with recursive r_cte as +( select * from t1 as s +union +select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t) +select * from t1 as tt; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY tt ALL NULL NULL NULL NULL 4 +with h_cte as +( with recursive r_cte as +( select * from t1 as s +union +select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t) +select * from t1 as tt; +a b c +1 3 12 +2 7 15 +3 1 3 +4 3 1 +analyze format=json with h_cte as +( with recursive r_cte as +( select * from t1 as s +union +select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t) +select * from t1 as tt; +ANALYZE +{ + "query_block": { + "select_id": 1, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "table": { + "table_name": "tt", + "access_type": "ALL", + "r_loops": 1, + "rows": 4, + "r_rows": 4, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + } +} +prepare stmt from "with h_cte as +( with recursive r_cte as +( select * from t1 as s +union +select t1.* from t1, r_cte as r where t1.c = r.a ) +select 0 as b FROM dual union all select b FROM r_cte as t) +select * from t1 as tt"; +execute stmt; +a b c +1 3 12 +2 7 15 +3 1 3 +4 3 1 +execute stmt; +a b c +1 3 12 +2 7 15 +3 1 3 +4 3 1 +deallocate prepare stmt; +drop table t1; +# # End of 10.2 tests # # |