diff options
Diffstat (limited to 'mysql-test/r/cte_recursive.result')
| -rw-r--r-- | mysql-test/r/cte_recursive.result | 31 |
1 files changed, 13 insertions, 18 deletions
diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result index 946ba16ac5c..a4f32927cf1 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#2 */ 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#3 */ 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#4 */ 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#5 */ 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#1 */ 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 @@ -1353,7 +1353,6 @@ EXPLAIN { "query_block": { "select_id": 4, - "operation": "UNION", "table": { "table_name": "folks", "access_type": "ALL", @@ -1382,7 +1381,6 @@ EXPLAIN { "query_block": { "select_id": 3, - "operation": "UNION", "table": { "table_name": "folks", "access_type": "ALL", @@ -1489,7 +1487,6 @@ EXPLAIN { "query_block": { "select_id": 3, - "operation": "UNION", "table": { "table_name": "v", "access_type": "ALL", @@ -1757,7 +1754,6 @@ EXPLAIN { "query_block": { "select_id": 2, - "operation": "UNION", "table": { "table_name": "t1", "access_type": "ALL", @@ -1840,7 +1836,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 @@ -2387,7 +2383,6 @@ ANALYZE { "query_block": { "select_id": 2, - "operation": "UNION", "table": { "message": "No tables used" } @@ -2794,7 +2789,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 @@ -2812,7 +2807,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 |