Fixed the bug mdev-13796.

A reference to a CTE may occur not in the master of the CTE
specification. In this case if the reference to the CTE is
the first one the specification should be detached from its
master and attached to the referencing select.

Also fixed the TYPE column in the lines of the EXPLAIN output
created for CTE tables.

1 files changed, 13 insertions, 13 deletions

diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result
index 42ba01e42de..057e3fe034c 100644
--- a/mysql-test/r/cte_recursive.result
+++ b/mysql-test/r/cte_recursive.result
@@ -86,7 +86,7 @@ select t2.a from t1,t2 where t1.a+1=t2.a
 select * from t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	30	
-2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
 3	UNION	t1	ALL	NULL	NULL	NULL	NULL	5	
 3	UNION	t2	ALL	NULL	NULL	NULL	NULL	5	Using where; Using join buffer (flat, BNL join)
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
@@ -114,7 +114,7 @@ select t2.a from t1,t2 where t1.a+1=t2.a
 select * from t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	5	
-2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
 3	RECURSIVE UNION	<derived2>	ALL	NULL	NULL	NULL	NULL	5	
 3	RECURSIVE UNION	t2	ALL	NULL	NULL	NULL	NULL	5	Using where; Using join buffer (flat, BNL join)
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
@@ -691,13 +691,13 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
 1	PRIMARY	<derived3>	ref	key0	key0	5	c.h_id	2	100.00	
 1	PRIMARY	<derived3>	ref	key0	key0	5	c.w_id	2	100.00	
-3	SUBQUERY	folks	ALL	NULL	NULL	NULL	NULL	12	100.00	Using where
+3	DERIVED	folks	ALL	NULL	NULL	NULL	NULL	12	100.00	Using where
 4	RECURSIVE UNION	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	
 4	RECURSIVE UNION	p	ALL	NULL	NULL	NULL	NULL	12	100.00	Using where; Using join buffer (flat, BNL join)
 5	RECURSIVE UNION	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	
 5	RECURSIVE UNION	p	ALL	NULL	NULL	NULL	NULL	12	100.00	Using where; Using join buffer (flat, BNL join)
 NULL	UNION RESULT	<union3,4,5>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
-2	UNCACHEABLE SUBQUERY	<derived3>	ALL	NULL	NULL	NULL	NULL	12	100.00	Using where
+2	DERIVED	<derived3>	ALL	NULL	NULL	NULL	NULL	12	100.00	Using where
 Warnings:
 Note	1003	with recursive ancestor_couple_ids as (select `a`.`father` AS `h_id`,`a`.`mother` AS `w_id` from `coupled_ancestors` `a` where `a`.`father` is not null and `a`.`mother` is not null), coupled_ancestors as (select `test`.`folks`.`id` AS `id`,`test`.`folks`.`name` AS `name`,`test`.`folks`.`dob` AS `dob`,`test`.`folks`.`father` AS `father`,`test`.`folks`.`mother` AS `mother` from `test`.`folks` where `test`.`folks`.`name` = 'Me' union all select `test`.`p`.`id` AS `id`,`test`.`p`.`name` AS `name`,`test`.`p`.`dob` AS `dob`,`test`.`p`.`father` AS `father`,`test`.`p`.`mother` AS `mother` from `test`.`folks` `p` join `ancestor_couple_ids` `fa` where `test`.`p`.`id` = `fa`.`h_id` union all select `test`.`p`.`id` AS `id`,`test`.`p`.`name` AS `name`,`test`.`p`.`dob` AS `dob`,`test`.`p`.`father` AS `father`,`test`.`p`.`mother` AS `mother` from `test`.`folks` `p` join `ancestor_couple_ids` `ma` where `test`.`p`.`id` = `ma`.`w_id`)select `h`.`name` AS `name`,`h`.`dob` AS `dob`,`w`.`name` AS `name`,`w`.`dob` AS `dob` from `ancestor_couple_ids` `c` join `coupled_ancestors` `h` join `coupled_ancestors` `w` where `h`.`id` = `c`.`h_id` and `w`.`id` = `c`.`w_id`
 # simple mutual recursion
@@ -877,7 +877,7 @@ where p.id = a.father or p.id = a.mother
 select * from ancestors;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	12	100.00	
-2	SUBQUERY	folks	ALL	NULL	NULL	NULL	NULL	12	100.00	Using where
+2	DERIVED	folks	ALL	NULL	NULL	NULL	NULL	12	100.00	Using where
 3	RECURSIVE UNION	p	ALL	NULL	NULL	NULL	NULL	12	100.00	
 3	RECURSIVE UNION	<derived2>	ALL	NULL	NULL	NULL	NULL	12	100.00	Using where; Using join buffer (flat, BNL join)
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	NULL	
@@ -1236,7 +1236,7 @@ where p.id = ma.mother
 select * from ancestors;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	12	
-2	SUBQUERY	folks	ALL	NULL	NULL	NULL	NULL	12	Using where
+2	DERIVED	folks	ALL	NULL	NULL	NULL	NULL	12	Using where
 3	RECURSIVE UNION	p	ALL	PRIMARY	NULL	NULL	NULL	12	
 3	RECURSIVE UNION	<derived2>	ref	key0	key0	5	test.p.id	2	
 4	RECURSIVE UNION	p	ALL	PRIMARY	NULL	NULL	NULL	12	
@@ -1300,14 +1300,14 @@ from prev_gen
 select ancestors.name, ancestors.dob from ancestors;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	<derived4>	ALL	NULL	NULL	NULL	NULL	24	
-4	SUBQUERY	folks	ALL	NULL	NULL	NULL	NULL	12	Using where
+4	DERIVED	folks	ALL	NULL	NULL	NULL	NULL	12	Using where
 6	RECURSIVE UNION	<derived3>	ALL	NULL	NULL	NULL	NULL	12	
-5	RECURSIVE UNION	<derived4>	ALL	NULL	NULL	NULL	NULL	24	
-NULL	UNION RESULT	<union4,6,5>	ALL	NULL	NULL	NULL	NULL	NULL	
-3	SUBQUERY	folks	ALL	NULL	NULL	NULL	NULL	12	Using where
+3	DERIVED	folks	ALL	NULL	NULL	NULL	NULL	12	Using where
 2	RECURSIVE UNION	folks	ALL	PRIMARY	NULL	NULL	NULL	12	
 2	RECURSIVE UNION	<derived3>	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (flat, BNL join)
 NULL	UNION RESULT	<union3,2>	ALL	NULL	NULL	NULL	NULL	NULL	
+5	RECURSIVE UNION	<derived4>	ALL	NULL	NULL	NULL	NULL	24	
+NULL	UNION RESULT	<union4,6,5>	ALL	NULL	NULL	NULL	NULL	NULL	
 explain FORMAT=JSON
 with recursive 
 prev_gen
@@ -1831,7 +1831,7 @@ select t2.a from t1,t2 where t1.a+1=t2.a
 select * from t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	5	
-2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
+2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	5	Using where
 4	RECURSIVE UNION	<derived2>	ALL	NULL	NULL	NULL	NULL	5	
 4	RECURSIVE UNION	t2	ALL	NULL	NULL	NULL	NULL	5	Using where; Using join buffer (flat, BNL join)
 NULL	UNION RESULT	<union2,4>	ALL	NULL	NULL	NULL	NULL	NULL	
@@ -2783,7 +2783,7 @@ SELECT c1 FROM t, cte
 ) SELECT COUNT(*) FROM cte;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
 1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	4	4.00	100.00	100.00	
-2	SUBQUERY	t	ALL	NULL	NULL	NULL	NULL	4	4.00	100.00	100.00	
+2	DERIVED	t	ALL	NULL	NULL	NULL	NULL	4	4.00	100.00	100.00	
 3	RECURSIVE UNION	t	ALL	NULL	NULL	NULL	NULL	4	4.00	100.00	100.00	
 3	RECURSIVE UNION	<derived2>	ALL	NULL	NULL	NULL	NULL	4	4.00	100.00	100.00	Using join buffer (flat, BNL join)
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	0.00	NULL	NULL	
@@ -2801,7 +2801,7 @@ SELECT c2 FROM t, cte
 ) SELECT COUNT(*) FROM cte;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
 1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	4	4.00	100.00	100.00	
-2	SUBQUERY	t	ALL	NULL	NULL	NULL	NULL	4	4.00	100.00	100.00	
+2	DERIVED	t	ALL	NULL	NULL	NULL	NULL	4	4.00	100.00	100.00	
 3	RECURSIVE UNION	t	ALL	NULL	NULL	NULL	NULL	4	4.00	100.00	100.00	
 3	RECURSIVE UNION	<derived2>	ALL	NULL	NULL	NULL	NULL	4	4.00	100.00	100.00	Using join buffer (flat, BNL join)
 NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	0.00	NULL	NULL