blob: 214b14e0110999840799f283f28e86606fc57de1 (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
|
--echo # Verifying the CTE-specific output of EXPLAIN
CREATE TABLE t1(a int);
INSERT INTO t1 VALUES(1),(2);
--echo # In JSON "materialized_from_subquery" for the 2 last references
--echo # points to 1st reference: no duplication. In TRADITIONAL,
--echo # The 2 last references are 1) not expanded (underlying tables
--echo # are not shown) 2) shown as <derivedN> where N is ID of 1st
--echo # reference. So users understand we have single materialization.
let $query=
WITH qn(a) AS (SELECT 1 FROM t1 LIMIT 2)
SELECT * FROM qn WHERE qn.a=(SELECT * FROM qn qn1 LIMIT 1) ;
eval explain format=json $query;
eval explain format=traditional $query;
let $query=
WITH qn AS (SELECT cast("x" AS char(100)) AS a FROM t1 LIMIT 2)
SELECT (SELECT * FROM qn) FROM qn, qn qn1;
eval explain format=json $query;
eval explain format=traditional $query;
--echo # Recursive query block has a mark:
--echo # "recursive":true in JSON, "Recursive" on its first table in
--echo # TRADITIONAL.
let $query=
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;
eval explain format=json $query;
eval explain format=traditional $query;
DROP TABLE t1;
|
