diff options
Diffstat (limited to 'mysql-test/t/cte_recursive.test')
| -rw-r--r-- | mysql-test/t/cte_recursive.test | 38 |
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 # |