diff options
Diffstat (limited to 'mysql-test/main/except_all.result')
| -rw-r--r-- | mysql-test/main/except_all.result | 710 |
1 files changed, 393 insertions, 317 deletions
diff --git a/mysql-test/main/except_all.result b/mysql-test/main/except_all.result index befd7e0f15a..ac032221cc5 100644 --- a/mysql-test/main/except_all.result +++ b/mysql-test/main/except_all.result @@ -65,24 +65,32 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 7, - "filtered": 100 - } + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 7, + "filtered": 100 + } + } + ] } }, { "query_block": { "select_id": 2, "operation": "EXCEPT", - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 7, - "filtered": 100 - } + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 7, + "filtered": 100 + } + } + ] } } ] @@ -111,66 +119,78 @@ ANALYZE "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", - "table": { - "table_name": "<derived2>", - "access_type": "ALL", - "r_loops": 1, - "rows": 7, - "r_rows": 4, - "r_table_time_ms": "REPLACED", - "r_other_time_ms": "REPLACED", - "filtered": 100, - "r_filtered": 100, - "materialized": { - "query_block": { - "union_result": { - "table_name": "<except2,3>", - "access_type": "ALL", - "r_loops": 1, - "r_rows": 4, - "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": 7, - "r_rows": 7, - "r_table_time_ms": "REPLACED", - "r_other_time_ms": "REPLACED", - "filtered": 100, - "r_filtered": 100 - } - } - }, - { - "query_block": { - "select_id": 3, - "operation": "EXCEPT", - "r_loops": 1, - "r_total_time_ms": "REPLACED", - "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": "<derived2>", + "access_type": "ALL", + "r_loops": 1, + "rows": 7, + "r_rows": 4, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "materialized": { + "query_block": { + "union_result": { + "table_name": "<except2,3>", + "access_type": "ALL", + "r_loops": 1, + "r_rows": 4, + "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": 7, + "r_rows": 7, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + } + ] + } + }, + { + "query_block": { + "select_id": 3, + "operation": "EXCEPT", + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "nested_loop": [ + { + "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 + } + } + ] + } } - } + ] } - ] + } } } } - } + ] } } ANALYZE format=json select * from ((select a from t1) except all (select c from t2)) a; @@ -180,66 +200,78 @@ ANALYZE "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", - "table": { - "table_name": "<derived2>", - "access_type": "ALL", - "r_loops": 1, - "rows": 7, - "r_rows": 4, - "r_table_time_ms": "REPLACED", - "r_other_time_ms": "REPLACED", - "filtered": 100, - "r_filtered": 100, - "materialized": { - "query_block": { - "union_result": { - "table_name": "<except2,3>", - "access_type": "ALL", - "r_loops": 1, - "r_rows": 4, - "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": 7, - "r_rows": 7, - "r_table_time_ms": "REPLACED", - "r_other_time_ms": "REPLACED", - "filtered": 100, - "r_filtered": 100 - } - } - }, - { - "query_block": { - "select_id": 3, - "operation": "EXCEPT", - "r_loops": 1, - "r_total_time_ms": "REPLACED", - "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": "<derived2>", + "access_type": "ALL", + "r_loops": 1, + "rows": 7, + "r_rows": 4, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "materialized": { + "query_block": { + "union_result": { + "table_name": "<except2,3>", + "access_type": "ALL", + "r_loops": 1, + "r_rows": 4, + "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": 7, + "r_rows": 7, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + } + ] + } + }, + { + "query_block": { + "select_id": 3, + "operation": "EXCEPT", + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "nested_loop": [ + { + "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 + } + } + ] + } } - } + ] } - ] + } } } } - } + ] } } select * from ((select a from t1) except all (select c from t2)) a; @@ -349,67 +381,83 @@ EXPLAIN { "query_block": { "select_id": 1, - "table": { - "table_name": "<derived2>", - "access_type": "ALL", - "rows": 9, - "filtered": 100, - "materialized": { - "query_block": { - "union_result": { - "table_name": "<except2,3>", - "access_type": "ALL", - "query_specifications": [ - { - "query_block": { - "select_id": 2, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 3, - "filtered": 100 - }, - "block-nl-join": { - "table": { - "table_name": "t3", - "access_type": "ALL", - "rows": 3, - "filtered": 100 - }, - "buffer_type": "flat", - "buffer_size": "119", - "join_type": "BNL" - } - } - }, - { - "query_block": { - "select_id": 3, - "operation": "EXCEPT", - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 2, - "filtered": 100 + "nested_loop": [ + { + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "rows": 9, + "filtered": 100, + "materialized": { + "query_block": { + "union_result": { + "table_name": "<except2,3>", + "access_type": "ALL", + "query_specifications": [ + { + "query_block": { + "select_id": 2, + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 3, + "filtered": 100 + } + }, + { + "block-nl-join": { + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 3, + "filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "119", + "join_type": "BNL" + } + } + ] + } }, - "block-nl-join": { - "table": { - "table_name": "t4", - "access_type": "ALL", - "rows": 3, - "filtered": 100 - }, - "buffer_type": "flat", - "buffer_size": "119", - "join_type": "BNL" + { + "query_block": { + "select_id": 3, + "operation": "EXCEPT", + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 2, + "filtered": 100 + } + }, + { + "block-nl-join": { + "table": { + "table_name": "t4", + "access_type": "ALL", + "rows": 3, + "filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "119", + "join_type": "BNL" + } + } + ] + } } - } + ] } - ] + } } } } - } + ] } } ANALYZE format=json (select a,b,e,f from t1,t3) except all (select c,d,g,h from t2,t4); @@ -427,34 +475,40 @@ 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 - }, - "block-nl-join": { - "table": { - "table_name": "t3", - "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 + "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 + } }, - "buffer_type": "flat", - "buffer_size": "119", - "join_type": "BNL", - "r_filtered": 100 - } + { + "block-nl-join": { + "table": { + "table_name": "t3", + "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 + }, + "buffer_type": "flat", + "buffer_size": "119", + "join_type": "BNL", + "r_filtered": 100 + } + } + ] } }, { @@ -463,34 +517,40 @@ ANALYZE "operation": "EXCEPT", "r_loops": 1, "r_total_time_ms": "REPLACED", - "table": { - "table_name": "t2", - "access_type": "ALL", - "r_loops": 1, - "rows": 2, - "r_rows": 2, - "r_table_time_ms": "REPLACED", - "r_other_time_ms": "REPLACED", - "filtered": 100, - "r_filtered": 100 - }, - "block-nl-join": { - "table": { - "table_name": "t4", - "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 + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "r_loops": 1, + "rows": 2, + "r_rows": 2, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } }, - "buffer_type": "flat", - "buffer_size": "119", - "join_type": "BNL", - "r_filtered": 100 - } + { + "block-nl-join": { + "table": { + "table_name": "t4", + "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 + }, + "buffer_type": "flat", + "buffer_size": "119", + "join_type": "BNL", + "r_filtered": 100 + } + } + ] } } ] @@ -504,100 +564,116 @@ ANALYZE "select_id": 1, "r_loops": 1, "r_total_time_ms": "REPLACED", - "table": { - "table_name": "<derived2>", - "access_type": "ALL", - "r_loops": 1, - "rows": 9, - "r_rows": 7, - "r_table_time_ms": "REPLACED", - "r_other_time_ms": "REPLACED", - "filtered": 100, - "r_filtered": 100, - "materialized": { - "query_block": { - "union_result": { - "table_name": "<except2,3>", - "access_type": "ALL", - "r_loops": 1, - "r_rows": 7, - "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": 3, - "r_rows": 3, - "r_table_time_ms": "REPLACED", - "r_other_time_ms": "REPLACED", - "filtered": 100, - "r_filtered": 100 - }, - "block-nl-join": { - "table": { - "table_name": "t3", - "access_type": "ALL", + "nested_loop": [ + { + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "r_loops": 1, + "rows": 9, + "r_rows": 7, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100, + "materialized": { + "query_block": { + "union_result": { + "table_name": "<except2,3>", + "access_type": "ALL", + "r_loops": 1, + "r_rows": 7, + "query_specifications": [ + { + "query_block": { + "select_id": 2, "r_loops": 1, - "rows": 3, - "r_rows": 3, - "r_table_time_ms": "REPLACED", - "r_other_time_ms": "REPLACED", - "filtered": 100, - "r_filtered": 100 - }, - "buffer_type": "flat", - "buffer_size": "119", - "join_type": "BNL", - "r_filtered": 100 - } - } - }, - { - "query_block": { - "select_id": 3, - "operation": "EXCEPT", - "r_loops": 1, - "r_total_time_ms": "REPLACED", - "table": { - "table_name": "t2", - "access_type": "ALL", - "r_loops": 1, - "rows": 2, - "r_rows": 2, - "r_table_time_ms": "REPLACED", - "r_other_time_ms": "REPLACED", - "filtered": 100, - "r_filtered": 100 + "r_total_time_ms": "REPLACED", + "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 + } + }, + { + "block-nl-join": { + "table": { + "table_name": "t3", + "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 + }, + "buffer_type": "flat", + "buffer_size": "119", + "join_type": "BNL", + "r_filtered": 100 + } + } + ] + } }, - "block-nl-join": { - "table": { - "table_name": "t4", - "access_type": "ALL", + { + "query_block": { + "select_id": 3, + "operation": "EXCEPT", "r_loops": 1, - "rows": 3, - "r_rows": 3, - "r_table_time_ms": "REPLACED", - "r_other_time_ms": "REPLACED", - "filtered": 100, - "r_filtered": 100 - }, - "buffer_type": "flat", - "buffer_size": "119", - "join_type": "BNL", - "r_filtered": 100 + "r_total_time_ms": "REPLACED", + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "r_loops": 1, + "rows": 2, + "r_rows": 2, + "r_table_time_ms": "REPLACED", + "r_other_time_ms": "REPLACED", + "filtered": 100, + "r_filtered": 100 + } + }, + { + "block-nl-join": { + "table": { + "table_name": "t4", + "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 + }, + "buffer_type": "flat", + "buffer_size": "119", + "join_type": "BNL", + "r_filtered": 100 + } + } + ] + } } - } + ] } - ] + } } } } - } + ] } } prepare stmt from "(select a,b,e,f from t1,t3) except all (select c,d,g,h from t2,t4)"; |