diff options
Diffstat (limited to 'mysql-test/main/cte_recursive.result')
| -rw-r--r-- | mysql-test/main/cte_recursive.result | 948 |
1 files changed, 524 insertions, 424 deletions
diff --git a/mysql-test/main/cte_recursive.result b/mysql-test/main/cte_recursive.result index dd82ef46229..de9a979dd94 100644 --- a/mysql-test/main/cte_recursive.result +++ b/mysql-test/main/cte_recursive.result @@ -1339,107 +1339,133 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "<derived4>", - "access_type": "ALL", - "rows": 24, - "filtered": 100, - "materialized": { - "query_block": { - "recursive_union": { - "table_name": "<union4,6,5>", - "access_type": "ALL", - "query_specifications": [ - { - "query_block": { - "select_id": 4, - "table": { - "table_name": "folks", - "access_type": "ALL", - "rows": 12, - "filtered": 100, - "attached_condition": "folks.`name` = 'Me2'" - } - } - }, - { - "query_block": { - "select_id": 6, - "operation": "UNION", - "table": { - "table_name": "<derived3>", - "access_type": "ALL", - "rows": 12, - "filtered": 100, - "attached_condition": "prev_gen.`id` < 345", - "materialized": { - "query_block": { - "recursive_union": { - "table_name": "<union3,2>", - "access_type": "ALL", - "query_specifications": [ - { - "query_block": { - "select_id": 3, - "table": { - "table_name": "folks", - "access_type": "ALL", - "rows": 12, - "filtered": 100, - "attached_condition": "folks.`name` = 'Me'" - } - } - }, - { + "nested_loop": [ + { + "table": { + "table_name": "<derived4>", + "access_type": "ALL", + "rows": 24, + "filtered": 100, + "materialized": { + "query_block": { + "recursive_union": { + "table_name": "<union4,6,5>", + "access_type": "ALL", + "query_specifications": [ + { + "query_block": { + "select_id": 4, + "nested_loop": [ + { + "table": { + "table_name": "folks", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "folks.`name` = 'Me2'" + } + } + ] + } + }, + { + "query_block": { + "select_id": 6, + "operation": "UNION", + "nested_loop": [ + { + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "prev_gen.`id` < 345", + "materialized": { "query_block": { - "select_id": 2, - "operation": "UNION", - "table": { - "table_name": "folks", + "recursive_union": { + "table_name": "<union3,2>", "access_type": "ALL", - "possible_keys": ["PRIMARY"], - "rows": 12, - "filtered": 100 - }, - "block-nl-join": { - "table": { - "table_name": "<derived3>", - "access_type": "ALL", - "rows": 12, - "filtered": 100 - }, - "buffer_type": "flat", - "buffer_size": "686", - "join_type": "BNL", - "attached_condition": "prev_gen.father = folks.`id` or prev_gen.mother = folks.`id`" + "query_specifications": [ + { + "query_block": { + "select_id": 3, + "nested_loop": [ + { + "table": { + "table_name": "folks", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "folks.`name` = 'Me'" + } + } + ] + } + }, + { + "query_block": { + "select_id": 2, + "operation": "UNION", + "nested_loop": [ + { + "table": { + "table_name": "folks", + "access_type": "ALL", + "possible_keys": ["PRIMARY"], + "rows": 12, + "filtered": 100 + } + }, + { + "block-nl-join": { + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 12, + "filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "686", + "join_type": "BNL", + "attached_condition": "prev_gen.father = folks.`id` or prev_gen.mother = folks.`id`" + } + } + ] + } + } + ] } } } - ] + } } - } + ] + } + }, + { + "query_block": { + "select_id": 5, + "operation": "UNION", + "nested_loop": [ + { + "table": { + "table_name": "<derived4>", + "access_type": "ALL", + "rows": 24, + "filtered": 100, + "attached_condition": "ancestors.`id` < 234" + } + } + ] } } - } - }, - { - "query_block": { - "select_id": 5, - "operation": "UNION", - "table": { - "table_name": "<derived4>", - "access_type": "ALL", - "rows": 24, - "filtered": 100, - "attached_condition": "ancestors.`id` < 234" - } - } + ] } - ] + } } } } - } + ] } } # @@ -1473,91 +1499,111 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "<derived3>", - "access_type": "ALL", - "rows": 12, - "filtered": 100, - "materialized": { - "query_block": { - "recursive_union": { - "table_name": "<union3,2>", - "access_type": "ALL", - "query_specifications": [ - { - "query_block": { - "select_id": 3, - "table": { - "table_name": "v", - "access_type": "ALL", - "rows": 12, - "filtered": 100, - "attached_condition": "v.`name` = 'Me' and v.father is not null and v.mother is not null" - }, - "table": { - "table_name": "h", - "access_type": "eq_ref", - "possible_keys": ["PRIMARY"], - "key": "PRIMARY", - "key_length": "4", - "used_key_parts": ["id"], - "ref": ["test.v.father"], - "rows": 1, - "filtered": 100 - }, - "table": { - "table_name": "w", - "access_type": "eq_ref", - "possible_keys": ["PRIMARY"], - "key": "PRIMARY", - "key_length": "4", - "used_key_parts": ["id"], - "ref": ["test.v.mother"], - "rows": 1, - "filtered": 100 - } - } - }, - { - "query_block": { - "select_id": 2, - "operation": "UNION", - "table": { - "table_name": "<derived4>", - "access_type": "ALL", - "rows": 2, - "filtered": 100, - "attached_condition": "a.father is not null and a.mother is not null" - }, - "table": { - "table_name": "h", - "access_type": "eq_ref", - "possible_keys": ["PRIMARY"], - "key": "PRIMARY", - "key_length": "4", - "used_key_parts": ["id"], - "ref": ["a.father"], - "rows": 1, - "filtered": 100 + "nested_loop": [ + { + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "materialized": { + "query_block": { + "recursive_union": { + "table_name": "<union3,2>", + "access_type": "ALL", + "query_specifications": [ + { + "query_block": { + "select_id": 3, + "nested_loop": [ + { + "table": { + "table_name": "v", + "access_type": "ALL", + "rows": 12, + "filtered": 100, + "attached_condition": "v.`name` = 'Me' and v.father is not null and v.mother is not null" + } + }, + { + "table": { + "table_name": "h", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["id"], + "ref": ["test.v.father"], + "rows": 1, + "filtered": 100 + } + }, + { + "table": { + "table_name": "w", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["id"], + "ref": ["test.v.mother"], + "rows": 1, + "filtered": 100 + } + } + ] + } }, - "table": { - "table_name": "w", - "access_type": "eq_ref", - "possible_keys": ["PRIMARY"], - "key": "PRIMARY", - "key_length": "4", - "used_key_parts": ["id"], - "ref": ["a.mother"], - "rows": 1, - "filtered": 100 + { + "query_block": { + "select_id": 2, + "operation": "UNION", + "nested_loop": [ + { + "table": { + "table_name": "<derived4>", + "access_type": "ALL", + "rows": 2, + "filtered": 100, + "attached_condition": "a.father is not null and a.mother is not null" + } + }, + { + "table": { + "table_name": "h", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["id"], + "ref": ["a.father"], + "rows": 1, + "filtered": 100 + } + }, + { + "table": { + "table_name": "w", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["id"], + "ref": ["a.mother"], + "rows": 1, + "filtered": 100 + } + } + ] + } } - } + ] } - ] + } } } } - } + ] } } create table my_ancestors @@ -1778,46 +1824,58 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "<derived2>", - "access_type": "ALL", - "rows": 10, - "filtered": 100, - "materialized": { - "query_block": { - "recursive_union": { - "table_name": "<union2,3>", - "access_type": "ALL", - "query_specifications": [ - { - "query_block": { - "select_id": 2, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 10, - "filtered": 100 - } - } - }, - { - "query_block": { - "select_id": 3, - "operation": "UNION", - "table": { - "table_name": "<derived2>", - "access_type": "ALL", - "rows": 10, - "filtered": 100, - "attached_condition": "t.a < 1000" + "nested_loop": [ + { + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "rows": 10, + "filtered": 100, + "materialized": { + "query_block": { + "recursive_union": { + "table_name": "<union2,3>", + "access_type": "ALL", + "query_specifications": [ + { + "query_block": { + "select_id": 2, + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 10, + "filtered": 100 + } + } + ] + } + }, + { + "query_block": { + "select_id": 3, + "operation": "UNION", + "nested_loop": [ + { + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "rows": 10, + "filtered": 100, + "attached_condition": "t.a < 1000" + } + } + ] + } } - } + ] } - ] + } } } } - } + ] } } drop table t1; @@ -2401,57 +2459,65 @@ ANALYZE "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", - "table": { - "table_name": "<derived2>", - "access_type": "ALL", - "r_loops": 1, - "rows": 2, - "r_rows": 10, - "r_table_time_ms": "REPLACED", - "r_other_time_ms": "REPLACED", - "filtered": 100, - "r_filtered": 100, - "materialized": { - "query_block": { - "recursive_union": { - "table_name": "<union2,3>", - "access_type": "ALL", - "r_loops": 0, - "r_rows": null, - "query_specifications": [ - { - "query_block": { - "select_id": 2, - "table": { - "message": "No tables used" - } - } - }, - { - "query_block": { - "select_id": 3, - "operation": "UNION", - "r_loops": 10, - "r_total_time_ms": "REPLACED", - "table": { - "table_name": "<derived2>", - "access_type": "ALL", - "r_loops": 10, - "rows": 2, - "r_rows": 1, - "r_table_time_ms": "REPLACED", - "r_other_time_ms": "REPLACED", - "filtered": 100, - "r_filtered": 90, - "attached_condition": "src.counter < 10" + "nested_loop": [ + { + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "r_loops": 1, + "rows": 2, + "r_rows": 10, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "materialized": { + "query_block": { + "recursive_union": { + "table_name": "<union2,3>", + "access_type": "ALL", + "r_loops": 0, + "r_rows": null, + "query_specifications": [ + { + "query_block": { + "select_id": 2, + "table": { + "message": "No tables used" + } + } + }, + { + "query_block": { + "select_id": 3, + "operation": "UNION", + "r_loops": 10, + "r_total_time_ms": "REPLACED", + "nested_loop": [ + { + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "r_loops": 10, + "rows": 2, + "r_rows": 1, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 90, + "attached_condition": "src.counter < 10" + } + } + ] + } } - } + ] } - ] + } } } } - } + ] } } # @@ -3799,105 +3865,121 @@ ANALYZE "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", - "table": { - "table_name": "t1", - "access_type": "ALL", - "r_loops": 1, - "rows": 3, - "r_rows": 3, - "r_table_time_ms": "REPLACED", - "r_other_time_ms": "REPLACED", - "filtered": 100, - "r_filtered": 100, - "attached_condition": "t1.a1 is not null" - }, - "table": { - "table_name": "<derived3>", - "access_type": "ref", - "possible_keys": ["key0"], - "key": "key0", - "key_length": "23", - "used_key_parts": ["a2"], - "ref": ["test.t1.a1"], - "r_loops": 3, - "rows": 1, - "r_rows": 0.333333333, - "r_table_time_ms": "REPLACED", - "r_other_time_ms": "REPLACED", - "filtered": 100, - "r_filtered": 100, - "first_match": "t1", - "materialized": { - "query_block": { - "recursive_union": { - "table_name": "<union3,4>", - "access_type": "ALL", - "r_loops": 0, - "r_rows": null, - "query_specifications": [ - { - "query_block": { - "select_id": 3, - "r_loops": 1, - "r_total_time_ms": "REPLACED", - "table": { - "table_name": "t2", - "access_type": "const", - "possible_keys": ["PRIMARY"], - "key": "PRIMARY", - "key_length": "22", - "used_key_parts": ["a2"], - "ref": ["const"], - "r_loops": 0, - "rows": 1, - "r_rows": null, - "filtered": 100, - "r_filtered": null, - "using_index": true - } - } - }, - { - "query_block": { - "select_id": 4, - "operation": "UNION", - "r_loops": 1, - "r_total_time_ms": "REPLACED", - "table": { - "table_name": "<derived3>", - "access_type": "ALL", - "r_loops": 1, - "rows": 2, - "r_rows": 1, - "r_table_time_ms": "REPLACED", - "r_other_time_ms": "REPLACED", - "filtered": 100, - "r_filtered": 100, - "attached_condition": "cte.a2 is not null" + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "r_loops": 1, + "rows": 3, + "r_rows": 3, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "attached_condition": "t1.a1 is not null" + } + }, + { + "table": { + "table_name": "<derived3>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "23", + "used_key_parts": ["a2"], + "ref": ["test.t1.a1"], + "r_loops": 3, + "rows": 1, + "r_rows": 0.333333333, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "first_match": "t1", + "materialized": { + "query_block": { + "recursive_union": { + "table_name": "<union3,4>", + "access_type": "ALL", + "r_loops": 0, + "r_rows": null, + "query_specifications": [ + { + "query_block": { + "select_id": 3, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "const", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "22", + "used_key_parts": ["a2"], + "ref": ["const"], + "r_loops": 0, + "rows": 1, + "r_rows": null, + "filtered": 100, + "r_filtered": null, + "using_index": true + } + } + ] + } }, - "table": { - "table_name": "tt2", - "access_type": "ref", - "possible_keys": ["b1"], - "key": "b1", - "key_length": "23", - "used_key_parts": ["b1"], - "ref": ["cte.a2"], - "r_loops": 1, - "rows": 2, - "r_rows": 1, - "r_table_time_ms": "REPLACED", - "r_other_time_ms": "REPLACED", - "filtered": 100, - "r_filtered": 100 + { + "query_block": { + "select_id": 4, + "operation": "UNION", + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "nested_loop": [ + { + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "r_loops": 1, + "rows": 2, + "r_rows": 1, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "attached_condition": "cte.a2 is not null" + } + }, + { + "table": { + "table_name": "tt2", + "access_type": "ref", + "possible_keys": ["b1"], + "key": "b1", + "key_length": "23", + "used_key_parts": ["b1"], + "ref": ["cte.a2"], + "r_loops": 1, + "rows": 2, + "r_rows": 1, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + } + ] + } } - } + ] } - ] + } } } } - } + ] } } drop function f1; @@ -4055,83 +4137,97 @@ ANALYZE "operation": "UNION", "r_loops": 1, "r_total_time_ms": "REPLACED", - "table": { - "table_name": "<derived2>", - "access_type": "ALL", - "r_loops": 1, - "rows": 4, - "r_rows": 4, - "r_table_time_ms": "REPLACED", - "r_other_time_ms": "REPLACED", - "filtered": 100, - "r_filtered": 100, - "materialized": { - "query_block": { - "recursive_union": { - "table_name": "<union2,3>", - "access_type": "ALL", - "r_loops": 0, - "r_rows": null, - "query_specifications": [ - { - "query_block": { - "select_id": 2, - "r_loops": 1, - "r_total_time_ms": "REPLACED", - "table": { - "table_name": "s", - "access_type": "ALL", - "r_loops": 1, - "rows": 4, - "r_rows": 4, - "r_table_time_ms": "REPLACED", - "r_other_time_ms": "REPLACED", - "filtered": 100, - "r_filtered": 100 - } - } - }, - { - "query_block": { - "select_id": 3, - "operation": "UNION", - "r_loops": 1, - "r_total_time_ms": "REPLACED", - "table": { - "table_name": "t1", - "access_type": "ALL", - "r_loops": 1, - "rows": 4, - "r_rows": 4, - "r_table_time_ms": "REPLACED", - "r_other_time_ms": "REPLACED", - "filtered": 100, - "r_filtered": 100, - "attached_condition": "t1.c is not null" + "nested_loop": [ + { + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "r_loops": 1, + "rows": 4, + "r_rows": 4, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "materialized": { + "query_block": { + "recursive_union": { + "table_name": "<union2,3>", + "access_type": "ALL", + "r_loops": 0, + "r_rows": null, + "query_specifications": [ + { + "query_block": { + "select_id": 2, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "nested_loop": [ + { + "table": { + "table_name": "s", + "access_type": "ALL", + "r_loops": 1, + "rows": 4, + "r_rows": 4, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + } + ] + } }, - "table": { - "table_name": "<derived2>", - "access_type": "ref", - "possible_keys": ["key0"], - "key": "key0", - "key_length": "9", - "used_key_parts": ["a"], - "ref": ["test.t1.c"], - "r_loops": 4, - "rows": 2, - "r_rows": 0.5, - "r_table_time_ms": "REPLACED", - "r_other_time_ms": "REPLACED", - "filtered": 100, - "r_filtered": 100 + { + "query_block": { + "select_id": 3, + "operation": "UNION", + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "r_loops": 1, + "rows": 4, + "r_rows": 4, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "attached_condition": "t1.c is not null" + } + }, + { + "table": { + "table_name": "<derived2>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "9", + "used_key_parts": ["a"], + "ref": ["test.t1.c"], + "r_loops": 4, + "rows": 2, + "r_rows": 0.5, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + } + ] + } } - } + ] } - ] + } } } } - } + ] } } ] @@ -4193,17 +4289,21 @@ ANALYZE "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", - "table": { - "table_name": "tt", - "access_type": "ALL", - "r_loops": 1, - "rows": 4, - "r_rows": 4, - "r_table_time_ms": "REPLACED", - "r_other_time_ms": "REPLACED", - "filtered": 100, - "r_filtered": 100 - } + "nested_loop": [ + { + "table": { + "table_name": "tt", + "access_type": "ALL", + "r_loops": 1, + "rows": 4, + "r_rows": 4, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + } + ] } } prepare stmt from "with h_cte as |