diff options
Diffstat (limited to 'mysql-test/main/intersect_all.result')
| -rw-r--r-- | mysql-test/main/intersect_all.result | 584 |
1 files changed, 329 insertions, 255 deletions
diff --git a/mysql-test/main/intersect_all.result b/mysql-test/main/intersect_all.result index 96bfb9ca1fb..6c7a9c1cdd2 100644 --- a/mysql-test/main/intersect_all.result +++ b/mysql-test/main/intersect_all.result @@ -63,36 +63,48 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 4, - "filtered": 100 - } + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 4, + "filtered": 100 + } + } + ] } }, { "query_block": { "select_id": 2, "operation": "INTERSECT", - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 4, - "filtered": 100 - } + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 4, + "filtered": 100 + } + } + ] } }, { "query_block": { "select_id": 3, "operation": "INTERSECT", - "table": { - "table_name": "t3", - "access_type": "ALL", - "rows": 4, - "filtered": 100 - } + "nested_loop": [ + { + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 4, + "filtered": 100 + } + } + ] } } ] @@ -114,17 +126,21 @@ ANALYZE "select_id": 1, "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 - } + "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 + } + } + ] } }, { @@ -133,17 +149,21 @@ ANALYZE "operation": "INTERSECT", "r_loops": 1, "r_total_time_ms": "REPLACED", - "table": { - "table_name": "t2", - "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": "t2", + "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 + } + } + ] } }, { @@ -152,17 +172,21 @@ ANALYZE "operation": "INTERSECT", "r_loops": 1, "r_total_time_ms": "REPLACED", - "table": { - "table_name": "t3", - "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": "t3", + "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 + } + } + ] } } ] @@ -176,85 +200,101 @@ ANALYZE "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", - "table": { - "table_name": "<derived2>", - "access_type": "ALL", - "r_loops": 1, - "rows": 4, - "r_rows": 2, - "r_table_time_ms": "REPLACED", - "r_other_time_ms": "REPLACED", - "filtered": 100, - "r_filtered": 100, - "materialized": { - "query_block": { - "union_result": { - "table_name": "<intersect2,3,4>", - "access_type": "ALL", - "r_loops": 1, - "r_rows": 2, - "query_specifications": [ - { - "query_block": { - "select_id": 2, - "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 - } - } - }, - { - "query_block": { - "select_id": 3, - "operation": "INTERSECT", - "r_loops": 1, - "r_total_time_ms": "REPLACED", - "table": { - "table_name": "t2", - "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": 4, - "operation": "INTERSECT", - "r_loops": 1, - "r_total_time_ms": "REPLACED", - "table": { - "table_name": "t3", - "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": "<derived2>", + "access_type": "ALL", + "r_loops": 1, + "rows": 4, + "r_rows": 2, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "materialized": { + "query_block": { + "union_result": { + "table_name": "<intersect2,3,4>", + "access_type": "ALL", + "r_loops": 1, + "r_rows": 2, + "query_specifications": [ + { + "query_block": { + "select_id": 2, + "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 + } + } + ] + } + }, + { + "query_block": { + "select_id": 3, + "operation": "INTERSECT", + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "nested_loop": [ + { + "table": { + "table_name": "t2", + "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": 4, + "operation": "INTERSECT", + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "nested_loop": [ + { + "table": { + "table_name": "t3", + "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 + } + } + ] + } } - } + ] } - ] + } } } } - } + ] } } select * from ((select a,b from t1) intersect all (select c,d from t2) intersect all (select e,f from t3)) a; @@ -331,35 +371,45 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 6, - "filtered": 100 - } + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 6, + "filtered": 100 + } + } + ] } }, { "query_block": { "select_id": 2, "operation": "INTERSECT", - "table": { - "table_name": "t3", - "access_type": "ALL", - "rows": 5, - "filtered": 100 - }, - "block-nl-join": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 7, - "filtered": 100 + "nested_loop": [ + { + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + } }, - "buffer_type": "flat", - "buffer_size": "65", - "join_type": "BNL" - } + { + "block-nl-join": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 7, + "filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "65", + "join_type": "BNL" + } + } + ] } } ] @@ -381,17 +431,21 @@ ANALYZE "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", - "table": { - "table_name": "t1", - "access_type": "ALL", - "r_loops": 1, - "rows": 6, - "r_rows": 6, - "r_table_time_ms": "REPLACED", - "r_other_time_ms": "REPLACED", - "filtered": 100, - "r_filtered": 100 - } + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "r_loops": 1, + "rows": 6, + "r_rows": 6, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + } + ] } }, { @@ -400,34 +454,40 @@ ANALYZE "operation": "INTERSECT", "r_loops": 1, "r_total_time_ms": "REPLACED", - "table": { - "table_name": "t3", - "access_type": "ALL", - "r_loops": 1, - "rows": 5, - "r_rows": 5, - "r_table_time_ms": "REPLACED", - "r_other_time_ms": "REPLACED", - "filtered": 100, - "r_filtered": 100 - }, - "block-nl-join": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "r_loops": 1, - "rows": 7, - "r_rows": 7, - "r_table_time_ms": "REPLACED", - "r_other_time_ms": "REPLACED", - "filtered": 100, - "r_filtered": 100 + "nested_loop": [ + { + "table": { + "table_name": "t3", + "access_type": "ALL", + "r_loops": 1, + "rows": 5, + "r_rows": 5, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } }, - "buffer_type": "flat", - "buffer_size": "65", - "join_type": "BNL", - "r_filtered": 100 - } + { + "block-nl-join": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "r_loops": 1, + "rows": 7, + "r_rows": 7, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "65", + "join_type": "BNL", + "r_filtered": 100 + } + } + ] } } ] @@ -441,83 +501,97 @@ ANALYZE "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", - "table": { - "table_name": "<derived2>", - "access_type": "ALL", - "r_loops": 1, - "rows": 6, - "r_rows": 3, - "r_table_time_ms": "REPLACED", - "r_other_time_ms": "REPLACED", - "filtered": 100, - "r_filtered": 100, - "materialized": { - "query_block": { - "union_result": { - "table_name": "<intersect2,3>", - "access_type": "ALL", - "r_loops": 1, - "r_rows": 3, - "query_specifications": [ - { - "query_block": { - "select_id": 2, - "r_loops": 1, - "r_total_time_ms": "REPLACED", - "table": { - "table_name": "t1", - "access_type": "ALL", - "r_loops": 1, - "rows": 6, - "r_rows": 6, - "r_table_time_ms": "REPLACED", - "r_other_time_ms": "REPLACED", - "filtered": 100, - "r_filtered": 100 - } - } - }, - { - "query_block": { - "select_id": 3, - "operation": "INTERSECT", - "r_loops": 1, - "r_total_time_ms": "REPLACED", - "table": { - "table_name": "t3", - "access_type": "ALL", - "r_loops": 1, - "rows": 5, - "r_rows": 5, - "r_table_time_ms": "REPLACED", - "r_other_time_ms": "REPLACED", - "filtered": 100, - "r_filtered": 100 + "nested_loop": [ + { + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "r_loops": 1, + "rows": 6, + "r_rows": 3, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "materialized": { + "query_block": { + "union_result": { + "table_name": "<intersect2,3>", + "access_type": "ALL", + "r_loops": 1, + "r_rows": 3, + "query_specifications": [ + { + "query_block": { + "select_id": 2, + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "r_loops": 1, + "rows": 6, + "r_rows": 6, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + } + ] + } }, - "block-nl-join": { - "table": { - "table_name": "t2", - "access_type": "ALL", + { + "query_block": { + "select_id": 3, + "operation": "INTERSECT", "r_loops": 1, - "rows": 7, - "r_rows": 7, - "r_table_time_ms": "REPLACED", - "r_other_time_ms": "REPLACED", - "filtered": 100, - "r_filtered": 100 - }, - "buffer_type": "flat", - "buffer_size": "65", - "join_type": "BNL", - "r_filtered": 100 + "r_total_time_ms": "REPLACED", + "nested_loop": [ + { + "table": { + "table_name": "t3", + "access_type": "ALL", + "r_loops": 1, + "rows": 5, + "r_rows": 5, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + }, + { + "block-nl-join": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "r_loops": 1, + "rows": 7, + "r_rows": 7, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "65", + "join_type": "BNL", + "r_filtered": 100 + } + } + ] + } } - } + ] } - ] + } } } } - } + ] } } select * from ((select a,b from t1) intersect all (select c,e from t2,t3)) a; |