Allowed to use WITH clauses before SELECT in CREATE ... SELECTbb-10.2-mdev9864

and INSERT ... SELECT.
Added test cases.

1 files changed, 38 insertions, 0 deletions

diff --git a/mysql-test/t/cte_recursive.test b/mysql-test/t/cte_recursive.test
index 5eb84bae4fb..c2c02a6680d 100644
--- a/mysql-test/t/cte_recursive.test
+++ b/mysql-test/t/cte_recursive.test
@@ -1162,6 +1162,44 @@ select h_name, h_dob, w_name, w_dob
   from ancestor_couples;
 
 
+create table my_ancestors
+with recursive
+ancestor_ids (id)
+as
+(
+  select father from folks where name = 'Me'
+  union
+  select mother from folks where name = 'Me'
+  union
+  select father from folks, ancestor_ids a  where folks.id = a.id
+  union
+  select mother from folks, ancestor_ids a  where folks.id = a.id
+)
+select p.* from folks as p, ancestor_ids as a where p.id = a.id;
+
+select * from my_ancestors;
+
+delete from my_ancestors;
+
+insert into my_ancestors 
+with recursive
+ancestor_ids (id)
+as
+(
+  select father from folks where name = 'Me'
+  union
+  select mother from folks where name = 'Me'
+  union
+  select father from folks, ancestor_ids a  where folks.id = a.id
+  union
+  select mother from folks, ancestor_ids a  where folks.id = a.id
+)
+select p.* from folks as p, ancestor_ids as a where p.id = a.id;
+
+select * from my_ancestors;
+
+drop table my_ancestors;
+
 drop table folks;
 
 --echo #