diff options
Diffstat (limited to 'mysql-test/r/with_explain.result')
| -rw-r--r-- | mysql-test/r/with_explain.result | 218 |
1 files changed, 218 insertions, 0 deletions
diff --git a/mysql-test/r/with_explain.result b/mysql-test/r/with_explain.result new file mode 100644 index 00000000000..c948010873d --- /dev/null +++ b/mysql-test/r/with_explain.result @@ -0,0 +1,218 @@ +# Verifying the CTE-specific output of EXPLAIN +CREATE TABLE t1(a int); +INSERT INTO t1 VALUES(1),(2); +# In JSON "materialized_from_subquery" for the 2 last references +# points to 1st reference: no duplication. In TRADITIONAL, +# The 2 last references are 1) not expanded (underlying tables +# are not shown) 2) shown as <derivedN> where N is ID of 1st +# reference. So users understand we have single materialization. +explain format=json WITH qn(a) AS (SELECT 1 FROM t1 LIMIT 2) +SELECT * FROM qn WHERE qn.a=(SELECT * FROM qn qn1 LIMIT 1) ; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "rows": 2, + "filtered": 100, + "attached_condition": "qn.a = (subquery#3)", + "materialized": { + "query_block": { + "select_id": 2, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 2, + "filtered": 100 + } + } + } + }, + "subqueries": [ + { + "query_block": { + "select_id": 3, + "table": { + "table_name": "<derived4>", + "access_type": "ALL", + "rows": 2, + "filtered": 100, + "materialized": { + "query_block": { + "select_id": 4, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 2, + "filtered": 100 + } + } + } + } + } + } + ] + } +} +explain format=traditional WITH qn(a) AS (SELECT 1 FROM t1 LIMIT 2) +SELECT * FROM qn WHERE qn.a=(SELECT * FROM qn qn1 LIMIT 1) ; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using where +3 SUBQUERY <derived4> ALL NULL NULL NULL NULL 2 +4 DERIVED t1 ALL NULL NULL NULL NULL 2 +2 DERIVED t1 ALL NULL NULL NULL NULL 2 +explain format=json WITH qn AS (SELECT cast("x" AS char(100)) AS a FROM t1 LIMIT 2) +SELECT (SELECT * FROM qn) FROM qn, qn qn1; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "rows": 2, + "filtered": 100, + "materialized": { + "query_block": { + "select_id": 2, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 2, + "filtered": 100 + } + } + } + }, + "block-nl-join": { + "table": { + "table_name": "<derived5>", + "access_type": "ALL", + "rows": 2, + "filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "256Kb", + "join_type": "BNL", + "materialized": { + "query_block": { + "select_id": 5, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 2, + "filtered": 100 + } + } + } + }, + "subqueries": [ + { + "query_block": { + "select_id": 3, + "table": { + "table_name": "<derived4>", + "access_type": "ALL", + "rows": 2, + "filtered": 100, + "materialized": { + "query_block": { + "select_id": 4, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 2, + "filtered": 100 + } + } + } + } + } + } + ] + } +} +explain format=traditional WITH qn AS (SELECT cast("x" AS char(100)) AS a FROM t1 LIMIT 2) +SELECT (SELECT * FROM qn) FROM qn, qn qn1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 +1 PRIMARY <derived5> ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +5 DERIVED t1 ALL NULL NULL NULL NULL 2 +3 SUBQUERY <derived4> ALL NULL NULL NULL NULL 2 +4 DERIVED t1 ALL NULL NULL NULL NULL 2 +2 DERIVED t1 ALL NULL NULL NULL NULL 2 +# Recursive query block has a mark: +# "recursive":true in JSON, "Recursive" on its first table in +# TRADITIONAL. +explain format=json WITH RECURSIVE qn AS (SELECT cast("x" AS char(100)) AS a FROM dual +UNION ALL +SELECT concat("x",qn.a) FROM qn,t1 WHERE +length(qn.a)<10) +SELECT * FROM qn; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "rows": 2, + "filtered": 100, + "materialized": { + "query_block": { + "recursive_union": { + "table_name": "<union2,3>", + "access_type": "ALL", + "query_specifications": [ + { + "query_block": { + "select_id": 2, + "table": { + "message": "No tables used" + } + } + }, + { + "query_block": { + "select_id": 3, + "table": { + "table_name": "<derived2>", + "access_type": "ALL", + "rows": 2, + "filtered": 100, + "attached_condition": "length(qn.a) < 10" + }, + "block-nl-join": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 2, + "filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "256Kb", + "join_type": "BNL" + } + } + } + ] + } + } + } + } + } +} +explain format=traditional WITH RECURSIVE qn AS (SELECT cast("x" AS char(100)) AS a FROM dual +UNION ALL +SELECT concat("x",qn.a) FROM qn,t1 WHERE +length(qn.a)<10) +SELECT * FROM qn; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 Using where +3 RECURSIVE UNION t1 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join) +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +DROP TABLE t1; |