--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;