diff options
| -rw-r--r-- | mysql-test/r/cte_recursive.result | 24 | ||||
| -rw-r--r-- | mysql-test/t/cte_recursive.test | 14 |
2 files changed, 38 insertions, 0 deletions
diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result index 5769948203e..cb1c2439a13 100644 --- a/mysql-test/r/cte_recursive.result +++ b/mysql-test/r/cte_recursive.result @@ -230,6 +230,30 @@ id name dob father mother 7 Grandma Sally 1943-08-23 NULL 6 8 Grandpa Ben 1940-10-21 NULL NULL 6 Grandgrandma Martha 1923-05-17 NULL NULL +# simple recursion with or in anchor and or in recursive part +with recursive +ancestors +as +( +select * +from folks +where name = 'Me' or name='Sister Amy' + union +select p.* +from folks as p, ancestors as a +where p.id = a.father or p.id = a.mother +) +select * from ancestors; +id name dob father mother +100 Me 2000-01-01 20 30 +98 Sister Amy 2001-06-20 20 30 +20 Dad 1970-02-02 10 9 +30 Mom 1975-03-03 8 7 +10 Grandpa Bill 1940-04-05 NULL NULL +9 Grandma Ann 1941-10-15 NULL NULL +7 Grandma Sally 1943-08-23 NULL 6 +8 Grandpa Ben 1940-10-21 NULL NULL +6 Grandgrandma Martha 1923-05-17 NULL NULL # two recursive definition, one uses another with recursive prev_gen diff --git a/mysql-test/t/cte_recursive.test b/mysql-test/t/cte_recursive.test index d795ea81b23..4ca931f326c 100644 --- a/mysql-test/t/cte_recursive.test +++ b/mysql-test/t/cte_recursive.test @@ -204,6 +204,20 @@ as ) select * from ancestors; +--echo # simple recursion with or in anchor and or in recursive part +with recursive +ancestors +as +( + select * + from folks + where name = 'Me' or name='Sister Amy' + union + select p.* + from folks as p, ancestors as a + where p.id = a.father or p.id = a.mother +) +select * from ancestors; --echo # two recursive definition, one uses another with recursive |