CTE tests (from MySQL 8.0)bb-MDEV-15447

1 files changed, 40 insertions, 0 deletions

diff --git a/mysql-test/t/with_explain.test b/mysql-test/t/with_explain.test
new file mode 100644
index 00000000000..214b14e0110
--- /dev/null
+++ b/mysql-test/t/with_explain.test
@@ -0,0 +1,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;