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

14 files changed, 6462 insertions, 0 deletions

diff --git a/mysql-test/include/with_non_recursive.inc b/mysql-test/include/with_non_recursive.inc
new file mode 100644
index 00000000000..1f169c03c6d
--- /dev/null
+++ b/mysql-test/include/with_non_recursive.inc
@@ -0,0 +1,758 @@
+
+flush STATUS;
+
+CREATE TABLE t1(a int, b int, c int);
+INSERT INTO t1 VALUES(NULL,NULL,NULL),(2,3,4);
+
+WITH qn AS (SELECT a FROM t1)
+  SELECT 1 FROM dual;
+
+--echo # two query names
+
+WITH qn AS (SELECT a FROM t1), qn2 AS (SELECT b FROM t1)
+SELECT 1 FROM dual;
+
+--echo # duplicate query names
+
+--error ER_DUP_QUERY_NAME
+WITH qn AS (SELECT a FROM t1), qn AS (SELECT b FROM t1)
+SELECT 1 FROM qn;
+
+--echo #  multiple refs
+
+WITH qn AS (SELECT b AS a FROM t1)
+SELECT qn.a, qn2.a  FROM qn, qn AS qn2;
+
+WITH qn AS (SELECT b AS a FROM t1),
+qn2 AS (SELECT c FROM t1 WHERE a IS NULL OR a>0)
+SELECT qn.a, qn2.c  FROM qn, qn2;
+
+--echo # qn2 ref qn:
+
+WITH qn AS (SELECT 10*a AS a FROM t1),
+qn2 AS (SELECT 3*a FROM qn)
+SELECT * FROM qn2;
+
+WITH qn AS (SELECT a FROM t1), qn2 AS (SELECT a FROM qn)
+SELECT * FROM qn2;
+
+let $query=
+WITH qn AS (SELECT b AS a FROM t1),
+qn2 AS (SELECT a FROM qn WHERE a IS NULL OR a>0)
+SELECT qn.a, qn2.a  FROM qn, qn2;
+
+eval $query;
+eval EXPLAIN $query;
+
+--echo # forward ref (should error)
+
+--error ER_NO_SUCH_TABLE
+WITH qn2 AS (SELECT a FROM qn WHERE a IS NULL OR a>0),
+qn AS (SELECT b AS a FROM t1)
+SELECT qn2.a  FROM qn2;
+
+--error ER_NO_SUCH_TABLE
+WITH qn1 AS (WITH qn3 AS (SELECT * FROM qn2) SELECT * FROM qn3),
+     qn2 AS (SELECT 1)
+SELECT * FROM qn1;
+
+--echo # This is valid; it is to test moving boundaries.
+--echo # When we resolve qn3, resolving qn1 moves the right bound to
+--echo # qn0, but the bound is properly restored so that we can later
+--echo # resolve qn2.
+WITH qn0 AS (SELECT 1), qn1 AS (SELECT * FROM qn0), qn2 AS (SELECT 1), qn3 AS (SELECT 1 FROM qn1, qn2) SELECT 1 FROM qn3;
+
+--echo # No ref
+
+explain WITH qn AS (SELECT 1) SELECT 2;
+WITH qn AS (SELECT 1) SELECT 2;
+
+--echo # circular ref
+
+--error ER_NO_SUCH_TABLE
+WITH qn2 AS (SELECT a FROM qn WHERE a IS NULL OR a>0),
+qn AS (SELECT b AS a FROM qn2)
+SELECT qn.a  FROM qn;
+
+--echo # recursive
+
+--error ER_NO_SUCH_TABLE
+WITH qn AS (SELECT a FROM qn)
+SELECT qn.a FROM qn;
+
+--error ER_NO_SUCH_TABLE
+WITH qn1 AS (SELECT a FROM qn3),
+qn2 AS (SELECT a FROM qn1),
+qn3 AS (SELECT a FROM t1),
+qn4 AS (SELECT a FROM qn2)
+SELECT a FROM qn4;
+
+--echo # ref from subq
+
+WITH qn AS (SELECT * FROM t1) SELECT (SELECT max(a) FROM qn);
+
+--echo # QN defined in subq
+
+SELECT (WITH qn AS (SELECT 10*a AS a FROM t1),
+        qn2 AS (SELECT 3*a AS b FROM qn)
+        SELECT * FROM qn2 LIMIT 1)
+FROM t1;
+
+SELECT *
+FROM (WITH qn AS (SELECT 10*a AS a FROM t1),
+      qn2 AS (SELECT 3*a AS b FROM qn)
+      SELECT * FROM qn2)
+AS dt;
+
+--echo # WITH in WITH
+WITH qn AS
+  (WITH qn2 AS (SELECT "qn2" AS a FROM t1) SELECT "qn", a FROM qn2)
+SELECT * FROM qn;
+
+--echo # outer ref to a table, placed in a QN in a subq (later)
+IF (0)
+{
+SELECT (WITH qn AS (SELECT t2.a*a AS a FROM t1),
+        qn2 AS (SELECT 3*a AS b FROM qn)
+        SELECT * FROM qn2 LIMIT 1)
+FROM t2 AS t1;
+
+--echo # outer ref to a QN, placed in a QN in a subq
+
+WITH qn AS (SELECT b AS a FROM t1)
+SELECT (WITH qn2 AS (SELECT a FROM qn WHERE a IS NULL OR a>0)
+        SELECT qn2.a FROM qn2) FROM qn;
+}
+
+--echo # QN defined in view
+
+CREATE VIEW v AS
+WITH qn AS (SELECT 10*a AS a FROM t1),
+      qn2 AS (SELECT 3*a AS b FROM qn)
+      SELECT * FROM qn2;
+SELECT * FROM v;
+DROP VIEW v;
+
+--echo # CREATE INSERT SELECT
+
+CREATE TABLE t2
+WITH qn AS (SELECT 10*a AS a FROM t1),
+      qn2 AS (SELECT 3*a AS b FROM qn)
+      SELECT * FROM qn2;
+SELECT * FROM t2;
+INSERT INTO t2
+WITH qn AS (SELECT 10*a AS a FROM t1),
+      qn2 AS (SELECT 3*a AS b FROM qn)
+      SELECT * FROM qn2;
+SELECT * FROM t2;
+DROP TABLE t2;
+
+--echo # Double use of QN in two subqueries.
+
+let $query=
+WITH qn AS (SELECT * FROM t1 LIMIT 10)
+SELECT (SELECT max(a) FROM qn WHERE a=0),
+       (SELECT min(b) FROM qn WHERE b=3);
+eval explain $query;
+eval $query;
+
+--echo # when QN, when table.
+CREATE TABLE qn SELECT "base";
+
+SELECT * FROM qn;
+
+WITH qn AS (SELECT "with") SELECT * FROM qn;
+
+--echo # In a non-recursive WITH, the scope of the QN doesn't extend to its
+--echo # subquery, so "qn" inside AS() is the base table.
+WITH qn AS (SELECT * FROM qn) SELECT * FROM qn;
+
+--echo # View doesn't look out to external QNs
+
+CREATE VIEW v AS SELECT * FROM qn;
+
+SELECT * FROM v;
+
+WITH qn AS (SELECT "with") SELECT * FROM v;
+
+WITH qn AS (SELECT * FROM v) SELECT * FROM qn;
+
+--echo # Even if the base table is temporarily dropped
+DROP TABLE qn;
+--error ER_VIEW_INVALID
+WITH qn AS (SELECT "with") SELECT * FROM v;
+--error ER_VIEW_INVALID
+WITH qn AS (SELECT * FROM v) SELECT * FROM qn;
+CREATE TABLE qn SELECT "base" AS a;
+
+--echo # Neither does SP
+
+CREATE FUNCTION f() RETURNS varchar(10)
+ RETURN (SELECT * FROM qn);
+
+SELECT f();
+
+WITH qn AS (SELECT "with") SELECT f();
+
+WITH qn AS (SELECT f()) SELECT * FROM qn;
+
+--echo # QN shadows tmp table
+
+CREATE TEMPORARY TABLE qn SELECT "tmp" AS a;
+
+SELECT * FROM qn;
+
+WITH qn AS (SELECT "with") SELECT * FROM qn;
+
+DROP FUNCTION f;
+DROP VIEW v;
+
+--echo # DT shadows QN:
+
+WITH qn AS (SELECT "with") SELECT * FROM (SELECT "dt") AS qn;
+
+--echo # QN of subq shadows outer QN
+WITH qn AS (SELECT "outer" AS a)
+SELECT (WITH qn AS (SELECT "inner" AS a) SELECT a FROM qn),
+       qn.a
+FROM qn;
+
+--echo # Qualified name isn't allowed after WITH:
+
+--error ER_PARSE_ERROR
+WITH test.qn AS (SELECT "with") SELECT * FROM test.qn;
+
+--echo # OTOH, db. prefix in FROM doesn't resolve to QN, which is good
+WITH qn AS (SELECT "with") SELECT * FROM qn;
+WITH qn AS (SELECT "with") SELECT * FROM test.qn;
+WITH qn AS (SELECT "with" AS a) SELECT a FROM qn;
+WITH qn AS (SELECT "with" AS a) SELECT qn.a FROM qn;
+WITH qn AS (SELECT "with" AS a) SELECT test.qn.a FROM qn;
+WITH qn AS (SELECT "with" AS a) SELECT a FROM test.qn;
+WITH qn AS (SELECT "with" AS a) SELECT qn.a FROM test.qn;
+WITH qn AS (SELECT "with" AS a) SELECT test.qn.a FROM test.qn;
+
+DROP TEMPORARY TABLE qn;
+
+WITH qn AS (SELECT "with" AS a) SELECT a FROM test.qn;
+WITH qn AS (SELECT "with" AS a) SELECT qn.a FROM test.qn;
+WITH qn AS (SELECT "with" AS a) SELECT test.qn.a FROM test.qn;
+
+DROP TABLE qn;
+
+--error ER_NO_SUCH_TABLE
+WITH qn AS (SELECT "with" AS a) SELECT a FROM test.qn;
+--error ER_NO_SUCH_TABLE
+WITH qn AS (SELECT "with" AS a) SELECT qn.a FROM test.qn;
+--error ER_NO_SUCH_TABLE
+WITH qn AS (SELECT "with" AS a) SELECT test.qn.a FROM test.qn;
+
+--echo # Unions
+
+WITH qn AS (SELECT b AS a FROM t1 UNION SELECT b+5 FROM t1),
+qn2 AS (SELECT a FROM qn WHERE a IS NULL OR a>0)
+SELECT qn.a FROM qn
+UNION SELECT qn2.a FROM qn2 WHERE qn2.a>3;
+
+--echo # No double WITH
+
+--error ER_PARSE_ERROR
+WITH qn AS (SELECT "with" AS a)
+WITH qn2 AS (SELECT "with" AS a)
+SELECT a FROM test.qn;
+--echo # with comma
+--error ER_PARSE_ERROR
+WITH qn AS (SELECT "with" AS a),
+WITH qn2 AS (SELECT "with" AS a)
+SELECT a FROM test.qn;
+
+--echo # ORDER BY removed unless there is LIMIT or single table (check "Using filesort")
+explain extended
+WITH qn AS (SELECT a FROM t1 ORDER BY 1)
+SELECT a FROM qn;
+
+explain extended
+WITH qn AS (SELECT a FROM t1 ORDER BY 1)
+SELECT qn.a FROM qn, t1 AS t2;
+
+explain extended
+WITH qn AS (SELECT a FROM t1 ORDER BY 1 LIMIT 10)
+SELECT qn.a FROM qn, t1 AS t2;
+
+--echo # FD detection
+
+WITH qn AS (SELECT a, b FROM t1)
+SELECT b FROM qn GROUP BY a;
+
+WITH qn AS (SELECT a, b FROM t1 WHERE a=b)
+SELECT b FROM qn GROUP BY a;
+
+WITH qn AS (SELECT a, sum(b) AS s FROM t1 GROUP BY a)
+SELECT s FROM qn GROUP BY a;
+
+--echo # CTEs work if used in SET
+
+SET @myvar=
+(WITH qn AS (SELECT a, sum(b) AS s FROM t1 GROUP BY a)
+SELECT s FROM qn GROUP BY a HAVING s IS NOT NULL);
+SELECT @myvar;
+
+--echo # CTE works with semijoin
+let $query=
+WITH cte AS (SELECT * FROM t1 AS t2 LIMIT 1)
+SELECT * FROM t1 WHERE t1.a IN (SELECT a+0 FROM cte);
+eval explain $query;
+eval $query;
+
+let $query=
+WITH cte AS (SELECT * FROM t1 AS t2)
+SELECT * FROM t1 WHERE t1.a IN (SELECT a+0 FROM cte);
+eval explain $query;
+eval $query;
+
+--echo # Column names
+
+--echo # empty list
+--error ER_PARSE_ERROR
+WITH qn () AS (SELECT 1) SELECT * FROM qn, qn qn1;
+--echo # Materialization
+--error ER_WITH_COL_WRONG_LIST
+WITH qn (foo, bar) AS (SELECT 1) SELECT * FROM qn, qn qn1;
+explain WITH qn (foo, bar) AS (SELECT 1, 2 FROM t1 LIMIT 2) SELECT * FROM qn, qn qn1;
+WITH qn (foo, bar) AS (SELECT 1, 2 FROM t1 LIMIT 2) SELECT * FROM qn, qn qn1;
+WITH qn (foo, bar) AS (SELECT 1 AS col, 2 AS coll FROM t1 LIMIT 2) SELECT * FROM qn, qn qn1;
+# mdev-15478 WITH qn (foo, bar) AS (SELECT 1 AS col, 2 AS coll UNION SELECT a,b FROM t1) SELECT qn1.bar FROM qn qn1;
+WITH qn (foo, bar) AS (SELECT a, b FROM t1 LIMIT 2) SELECT qn.bar,foo FROM qn;
+CREATE TABLE t3
+WITH qn (foo, bar) AS (SELECT a, b FROM t1 LIMIT 2) SELECT bar,foo FROM qn;
+DESC t3;
+DROP TABLE t3;
+
+--echo # Merge
+
+--error ER_WITH_COL_WRONG_LIST
+WITH qn (foo, bar) AS (SELECT 1 FROM t1) SELECT * FROM qn, qn qn1;
+WITH qn (foo, bar) AS (SELECT 1, 2 FROM t1) SELECT * FROM qn, qn qn1;
+explain WITH qn (foo, bar) AS (SELECT 1, 2 FROM t1) SELECT * FROM qn, qn qn1;
+WITH qn (foo, bar) AS (SELECT 1 AS col, 2 AS coll FROM t1) SELECT * FROM qn, qn qn1;
+WITH qn (foo, bar) AS (SELECT a, b FROM t1) SELECT qn1.bar,foo FROM qn qn1;
+CREATE TABLE t3
+WITH qn (foo, bar) AS (SELECT a, b FROM t1) SELECT bar,foo FROM qn;
+DESC t3;
+DROP TABLE t3;
+
+--echo # Disambiguates same-name expressions
+--error ER_DUP_FIELDNAME
+WITH qn AS (SELECT 1,1) SELECT * FROM qn;
+WITH qn (foo, bar) AS (SELECT 1,1) SELECT * FROM qn;
+--error ER_DUP_FIELDNAME
+WITH qn AS (SELECT 1,1 FROM t1) SELECT * FROM qn;
+WITH qn (foo, bar) AS (SELECT 1,1 FROM t1) SELECT * FROM qn;
+--echo # Duplicate names are forbidden
+--error ER_DUP_FIELDNAME
+WITH qn (foo, foo) AS (SELECT 1,2) SELECT * FROM qn;
+
+--echo # Column names for QN/DT are printed
+CREATE VIEW v1 AS
+ WITH qn (foo, bar) AS (SELECT 1,1) SELECT * FROM qn;
+SHOW CREATE VIEW v1;
+SHOW fields FROM v1;
+SELECT * FROM v1;
+DROP VIEW v1;
+
+CREATE VIEW v1 AS
+ WITH qn (foo, bar) AS (SELECT 1,1 FROM t1) SELECT * FROM qn;
+SHOW CREATE VIEW v1;
+SELECT * FROM v1;
+DROP VIEW v1;
+
+--error ER_VIEW_WRONG_LIST
+CREATE VIEW v1 (bar) AS
+ SELECT 1, 2 FROM t1;
+
+DROP TABLE t1;
+
+--echo # Prove that a materialized QN is shared among all references:
+CREATE TABLE t1(a int);
+INSERT INTO t1 VALUES(1),(2),(3),(4);
+flush STATUS;
+WITH qn AS (SELECT 123 AS col)
+SELECT * FROM qn;
+SHOW STATUS LIKE "handler_write";
+flush STATUS;
+WITH qn AS (SELECT 123 AS col)
+SELECT * FROM qn, qn AS qn1;
+SHOW STATUS LIKE "handler_write";
+# Contrast that WITH VIEW:
+CREATE VIEW qn AS SELECT 123 AS col;
+flush STATUS;
+SELECT * FROM qn, qn AS qn1;
+SHOW STATUS LIKE "handler_write";
+DROP VIEW qn;
+
+DROP TABLE t1;
+
+--echo # Printing of WITH to DD for view
+CREATE VIEW v AS
+SELECT (WITH qn AS (SELECT "with") SELECT * FROM qn) AS scal_subq
+FROM dual;
+SHOW CREATE VIEW v;
+SELECT * FROM v;
+DROP VIEW v;
+CREATE VIEW v AS SELECT * FROM (WITH qn AS (SELECT "with") SELECT * FROM qn) AS dt;
+SHOW CREATE VIEW v;
+SELECT * FROM v;
+DROP VIEW v;
+
+CREATE TABLE t1 (a int);
+
+let $query=
+WITH qne AS (SELECT a FROM t1),
+     qnm AS (SELECT a FROM t1),
+     qnea AS (SELECT a FROM t1),
+     qnma AS (SELECT a FROM t1)
+SELECT
+qne.a,qnm.a,alias1.a,alias2.a
+FROM qne, qnm, qnea AS alias1, qnma AS alias2;
+
+eval explain $query;
+DROP TABLE t1;
+
+--echo # Automatic index creation if materialized
+CREATE TABLE t1 (a int);
+
+INSERT INTO t1(a) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(0);
+ANALYZE TABLE t1;
+
+let $query=
+WITH tt AS (SELECT * FROM t1)
+SELECT /*+ no_merge(tt) */ tt.a
+FROM t1 STRAIGHT_JOIN tt WHERE t1.a=tt.a
+LIMIT 1;
+
+--echo # EXPLAIN should not fill the tmp table
+flush STATUS;
+
+--echo # Should use auto_key0 and ref access.
+--replace_column 10 #
+eval explain $query;
+SHOW STATUS LIKE "handler_write";
+
+flush STATUS;
+eval $query;
+SHOW STATUS LIKE "handler_write";
+
+--echo # With two references
+let $query=
+WITH tt AS (SELECT * FROM t1)
+SELECT /*+ no_merge(tt) no_merge(tt_)*/ tt.a
+FROM t1 STRAIGHT_JOIN tt STRAIGHT_JOIN tt AS tt_
+WHERE t1.a=tt.a AND tt.a=tt_.a
+LIMIT 1;
+
+eval $query;
+
+--echo # One merged, one materialized: index creation on the second
+--echo # should of course ignore the first
+WITH q AS (SELECT * FROM t1)
+SELECT /*+ merge(q) no_merge(q1) */ * FROM q, q q1 WHERE q.a=1 AND q1.a=2;
+
+DROP TABLE t1;
+
+--echo # Must not create more than 64 indexes.
+
+--disable_query_log
+CREATE TABLE `t` (
+  `c1` int(11) DEFAULT NULL,
+  `c2` int(11) DEFAULT NULL,
+  `c3` int(11) DEFAULT NULL,
+  `c4` int(11) DEFAULT NULL,
+  `c5` int(11) DEFAULT NULL,
+  `c6` int(11) DEFAULT NULL,
+  `c7` int(11) DEFAULT NULL,
+  `c8` int(11) DEFAULT NULL,
+  `c9` int(11) DEFAULT NULL,
+  `c10` int(11) DEFAULT NULL,
+  `c11` int(11) DEFAULT NULL,
+  `c12` int(11) DEFAULT NULL,
+  `c13` int(11) DEFAULT NULL,
+  `c14` int(11) DEFAULT NULL,
+  `c15` int(11) DEFAULT NULL,
+  `c16` int(11) DEFAULT NULL,
+  `c17` int(11) DEFAULT NULL,
+  `c18` int(11) DEFAULT NULL,
+  `c19` int(11) DEFAULT NULL,
+  `c20` int(11) DEFAULT NULL,
+  `c21` int(11) DEFAULT NULL,
+  `c22` int(11) DEFAULT NULL,
+  `c23` int(11) DEFAULT NULL,
+  `c24` int(11) DEFAULT NULL,
+  `c25` int(11) DEFAULT NULL,
+  `c26` int(11) DEFAULT NULL,
+  `c27` int(11) DEFAULT NULL,
+  `c28` int(11) DEFAULT NULL,
+  `c29` int(11) DEFAULT NULL,
+  `c30` int(11) DEFAULT NULL,
+  `c31` int(11) DEFAULT NULL,
+  `c32` int(11) DEFAULT NULL,
+  `c33` int(11) DEFAULT NULL,
+  `c34` int(11) DEFAULT NULL,
+  `c35` int(11) DEFAULT NULL,
+  `c36` int(11) DEFAULT NULL,
+  `c37` int(11) DEFAULT NULL,
+  `c38` int(11) DEFAULT NULL,
+  `c39` int(11) DEFAULT NULL,
+  `c40` int(11) DEFAULT NULL,
+  `c41` int(11) DEFAULT NULL,
+  `c42` int(11) DEFAULT NULL,
+  `c43` int(11) DEFAULT NULL,
+  `c44` int(11) DEFAULT NULL,
+  `c45` int(11) DEFAULT NULL,
+  `c46` int(11) DEFAULT NULL,
+  `c47` int(11) DEFAULT NULL,
+  `c48` int(11) DEFAULT NULL,
+  `c49` int(11) DEFAULT NULL,
+  `c50` int(11) DEFAULT NULL,
+  `c51` int(11) DEFAULT NULL,
+  `c52` int(11) DEFAULT NULL,
+  `c53` int(11) DEFAULT NULL,
+  `c54` int(11) DEFAULT NULL,
+  `c55` int(11) DEFAULT NULL,
+  `c56` int(11) DEFAULT NULL,
+  `c57` int(11) DEFAULT NULL,
+  `c58` int(11) DEFAULT NULL,
+  `c59` int(11) DEFAULT NULL,
+  `c60` int(11) DEFAULT NULL,
+  `c61` int(11) DEFAULT NULL,
+  `c62` int(11) DEFAULT NULL,
+  `c63` int(11) DEFAULT NULL,
+  `c64` int(11) DEFAULT NULL,
+  `c65` int(11) DEFAULT NULL,
+  `c66` int(11) DEFAULT NULL,
+  `c67` int(11) DEFAULT NULL,
+  `c68` int(11) DEFAULT NULL,
+  `c69` int(11) DEFAULT NULL,
+  `c70` int(11) DEFAULT NULL,
+  `c71` int(11) DEFAULT NULL,
+  `c72` int(11) DEFAULT NULL,
+  `c73` int(11) DEFAULT NULL,
+  `c74` int(11) DEFAULT NULL,
+  `c75` int(11) DEFAULT NULL,
+  `c76` int(11) DEFAULT NULL,
+  `c77` int(11) DEFAULT NULL,
+  `c78` int(11) DEFAULT NULL,
+  `c79` int(11) DEFAULT NULL,
+  `c80` int(11) DEFAULT NULL,
+  `c81` int(11) DEFAULT NULL,
+  `c82` int(11) DEFAULT NULL,
+  `c83` int(11) DEFAULT NULL,
+  `c84` int(11) DEFAULT NULL,
+  `c85` int(11) DEFAULT NULL,
+  `c86` int(11) DEFAULT NULL,
+  `c87` int(11) DEFAULT NULL,
+  `c88` int(11) DEFAULT NULL,
+  `c89` int(11) DEFAULT NULL,
+  `c90` int(11) DEFAULT NULL,
+  `c91` int(11) DEFAULT NULL,
+  `c92` int(11) DEFAULT NULL,
+  `c93` int(11) DEFAULT NULL,
+  `c94` int(11) DEFAULT NULL,
+  `c95` int(11) DEFAULT NULL,
+  `c96` int(11) DEFAULT NULL,
+  `c97` int(11) DEFAULT NULL,
+  `c98` int(11) DEFAULT NULL,
+  `c99` int(11) DEFAULT NULL,
+  `c100` int(11) DEFAULT NULL
+);
+
+WITH qn AS (SELECT * FROM t LIMIT 2)
+SELECT
+(SELECT max(c1) FROM qn WHERE qn.c1=1),
+(SELECT max(c2) FROM qn WHERE qn.c2=1),
+(SELECT max(c3) FROM qn WHERE qn.c3=1),
+(SELECT max(c4) FROM qn WHERE qn.c4=1),
+(SELECT max(c5) FROM qn WHERE qn.c5=1),
+(SELECT max(c6) FROM qn WHERE qn.c6=1),
+(SELECT max(c7) FROM qn WHERE qn.c7=1),
+(SELECT max(c8) FROM qn WHERE qn.c8=1),
+(SELECT max(c9) FROM qn WHERE qn.c9=1),
+(SELECT max(c10) FROM qn WHERE qn.c10=1),
+(SELECT max(c11) FROM qn WHERE qn.c11=1),
+(SELECT max(c12) FROM qn WHERE qn.c12=1),
+(SELECT max(c13) FROM qn WHERE qn.c13=1),
+(SELECT max(c14) FROM qn WHERE qn.c14=1),
+(SELECT max(c15) FROM qn WHERE qn.c15=1),
+(SELECT max(c16) FROM qn WHERE qn.c16=1),
+(SELECT max(c17) FROM qn WHERE qn.c17=1),
+(SELECT max(c18) FROM qn WHERE qn.c18=1),
+(SELECT max(c19) FROM qn WHERE qn.c19=1),
+(SELECT max(c20) FROM qn WHERE qn.c20=1),
+(SELECT max(c21) FROM qn WHERE qn.c21=1),
+(SELECT max(c22) FROM qn WHERE qn.c22=1),
+(SELECT max(c23) FROM qn WHERE qn.c23=1),
+(SELECT max(c24) FROM qn WHERE qn.c24=1),
+(SELECT max(c25) FROM qn WHERE qn.c25=1),
+(SELECT max(c26) FROM qn WHERE qn.c26=1),
+(SELECT max(c27) FROM qn WHERE qn.c27=1),
+(SELECT max(c28) FROM qn WHERE qn.c28=1),
+(SELECT max(c29) FROM qn WHERE qn.c29=1),
+(SELECT max(c30) FROM qn WHERE qn.c30=1),
+(SELECT max(c31) FROM qn WHERE qn.c31=1),
+(SELECT max(c32) FROM qn WHERE qn.c32=1),
+(SELECT max(c33) FROM qn WHERE qn.c33=1),
+(SELECT max(c34) FROM qn WHERE qn.c34=1),
+(SELECT max(c35) FROM qn WHERE qn.c35=1),
+(SELECT max(c36) FROM qn WHERE qn.c36=1),
+(SELECT max(c37) FROM qn WHERE qn.c37=1),
+(SELECT max(c38) FROM qn WHERE qn.c38=1),
+(SELECT max(c39) FROM qn WHERE qn.c39=1),
+(SELECT max(c40) FROM qn WHERE qn.c40=1),
+(SELECT max(c41) FROM qn WHERE qn.c41=1),
+(SELECT max(c42) FROM qn WHERE qn.c42=1),
+(SELECT max(c43) FROM qn WHERE qn.c43=1),
+(SELECT max(c44) FROM qn WHERE qn.c44=1),
+(SELECT max(c45) FROM qn WHERE qn.c45=1),
+(SELECT max(c46) FROM qn WHERE qn.c46=1),
+(SELECT max(c47) FROM qn WHERE qn.c47=1),
+(SELECT max(c48) FROM qn WHERE qn.c48=1),
+(SELECT max(c49) FROM qn WHERE qn.c49=1),
+(SELECT max(c50) FROM qn WHERE qn.c50=1),
+(SELECT max(c51) FROM qn WHERE qn.c51=1),
+(SELECT max(c52) FROM qn WHERE qn.c52=1),
+(SELECT max(c53) FROM qn WHERE qn.c53=1),
+(SELECT max(c54) FROM qn WHERE qn.c54=1),
+(SELECT max(c55) FROM qn WHERE qn.c55=1),
+(SELECT max(c56) FROM qn WHERE qn.c56=1),
+(SELECT max(c57) FROM qn WHERE qn.c57=1),
+(SELECT max(c58) FROM qn WHERE qn.c58=1),
+(SELECT max(c59) FROM qn WHERE qn.c59=1),
+(SELECT max(c60) FROM qn WHERE qn.c60=1),
+(SELECT max(c61) FROM qn WHERE qn.c61=1),
+(SELECT max(c62) FROM qn WHERE qn.c62=1),
+(SELECT max(c63) FROM qn WHERE qn.c63=1),
+(SELECT max(c64) FROM qn WHERE qn.c64=1),
+(SELECT max(c65) FROM qn WHERE qn.c65=1),
+(SELECT max(c66) FROM qn WHERE qn.c66=1),
+(SELECT max(c67) FROM qn WHERE qn.c67=1),
+(SELECT max(c68) FROM qn WHERE qn.c68=1),
+(SELECT max(c69) FROM qn WHERE qn.c69=1),
+(SELECT max(c70) FROM qn WHERE qn.c70=1),
+(SELECT max(c71) FROM qn WHERE qn.c71=1),
+(SELECT max(c72) FROM qn WHERE qn.c72=1),
+(SELECT max(c73) FROM qn WHERE qn.c73=1),
+(SELECT max(c74) FROM qn WHERE qn.c74=1),
+(SELECT max(c75) FROM qn WHERE qn.c75=1),
+(SELECT max(c76) FROM qn WHERE qn.c76=1),
+(SELECT max(c77) FROM qn WHERE qn.c77=1),
+(SELECT max(c78) FROM qn WHERE qn.c78=1),
+(SELECT max(c79) FROM qn WHERE qn.c79=1),
+(SELECT max(c80) FROM qn WHERE qn.c80=1),
+(SELECT max(c81) FROM qn WHERE qn.c81=1),
+(SELECT max(c82) FROM qn WHERE qn.c82=1),
+(SELECT max(c83) FROM qn WHERE qn.c83=1),
+(SELECT max(c84) FROM qn WHERE qn.c84=1),
+(SELECT max(c85) FROM qn WHERE qn.c85=1),
+(SELECT max(c86) FROM qn WHERE qn.c86=1),
+(SELECT max(c87) FROM qn WHERE qn.c87=1),
+(SELECT max(c88) FROM qn WHERE qn.c88=1),
+(SELECT max(c89) FROM qn WHERE qn.c89=1),
+(SELECT max(c90) FROM qn WHERE qn.c90=1),
+(SELECT max(c91) FROM qn WHERE qn.c91=1),
+(SELECT max(c92) FROM qn WHERE qn.c92=1),
+(SELECT max(c93) FROM qn WHERE qn.c93=1),
+(SELECT max(c94) FROM qn WHERE qn.c94=1),
+(SELECT max(c95) FROM qn WHERE qn.c95=1),
+(SELECT max(c96) FROM qn WHERE qn.c96=1),
+(SELECT max(c97) FROM qn WHERE qn.c97=1),
+(SELECT max(c98) FROM qn WHERE qn.c98=1),
+(SELECT max(c99) FROM qn WHERE qn.c99=1),
+(SELECT max(c100) FROM qn WHERE qn.c100=1)
+FROM dual;
+--enable_query_log
+
+DROP TABLE t;
+
+--echo # Choice between two auto_key:
+
+CREATE TABLE t1(a int, b int);
+INSERT INTO t1 VALUES (NULL, 6), (NULL, 10);
+let $query=
+WITH t2 AS
+ (SELECT * FROM t1)
+SELECT /*+ no_merge(t2) */ * FROM t2
+WHERE (a = a OR b <= 6) AND (a IS NULL);
+--echo # Test the covering key; note that MEMORY doesn't use a
+--echo # covering key (always reads the "data file"). But InnoDB does.
+eval EXPLAIN $query;
+eval $query;
+DROP TABLE t1;
+
+--echo # QN referencing view of same name isn't a "recursive view",
+--echo # shouldn't cause ER_VIEW_RECURSIVE
+
+CREATE VIEW v1 AS SELECT "with";
+WITH v1 AS (SELECT * FROM v1) SELECT * FROM v1;
+DROP VIEW v1;
+
+--echo # QN inside view
+
+CREATE VIEW v1 AS
+WITH qn AS (SELECT 1 AS col) SELECT * FROM qn;
+SELECT * FROM v1;
+DROP VIEW v1;
+CREATE TABLE t1(a int, b int);
+
+--echo # Alas merge hints are ignored in views (filed Bug#23017428)
+CREATE VIEW v1 AS
+WITH qn AS (SELECT a FROM t1),
+     qn2 AS (SELECT b FROM t1)
+SELECT /*+ merge(qn) no_merge(qn2) */ qn.a,qn2.b FROM qn, qn2;
+explain SELECT * FROM v1;
+DROP VIEW v1;
+
+--echo # Materializing view doesn't impose materializing query name
+CREATE algorithm=temptable VIEW v1 AS
+WITH qn AS (SELECT a FROM t1)
+SELECT qn.a  FROM qn;
+explain SELECT * FROM v1;
+DROP VIEW v1;
+
+DROP TABLE t1;
+
+--echo # CTE referenced four times, including in subqueries in other CTEs
+
+# One ROW per DAY, WITH amount sold ON that DAY:
+CREATE TABLE sales_days(day_of_sale DATE, amount INT);
+INSERT INTO sales_days VALUES
+('2015-01-02', 100), ('2015-01-05', 200),
+('2015-02-02', 10),  ('2015-02-10', 100),
+('2015-03-02', 10),  ('2015-03-18', 1);
+
+WITH
+ # FIRST CTE: one ROW per MONTH, WITH amount sold ON ALL days OF MONTH
+ sales_by_month(MONTH,total) AS
+ (SELECT MONTH(day_of_sale), sum(amount) FROM sales_days
+  WHERE year(day_of_sale)=2015
+  GROUP BY MONTH(day_of_sale)),
+ # SECOND CTE: best MONTH
+ best_month(MONTH, total, award) AS
+ (SELECT MONTH, total, "best" FROM sales_by_month
+  WHERE total=(SELECT max(total) FROM sales_by_month)),
+ # 3rd CTE: worst MONTH
+ worst_month(MONTH, total, award) AS
+ (SELECT MONTH, total, "worst" FROM sales_by_month
+  WHERE total=(SELECT min(total) FROM sales_by_month))
+ # Now SHOW results:
+ SELECT * FROM best_month UNION ALL SELECT * FROM worst_month;
+
+DROP TABLE sales_days;
+
+WITH qn AS (SELECT 1) SELECT * FROM qn, qn qn1;
+
+CREATE TABLE t1(a int);
+INSERT INTO t1 VALUES(1),(2);
+WITH qn(a) AS (SELECT 1 FROM t1 LIMIT 2)
+SELECT * FROM qn WHERE qn.a=(SELECT * FROM qn qn1 LIMIT 1) UNION SELECT 2;
+DROP TABLE t1;
+
+SHOW STATUS LIKE 'Created_tmp_disk_tables';
diff --git a/mysql-test/include/with_recursive.inc b/mysql-test/include/with_recursive.inc
new file mode 100644
index 00000000000..4f90a68177d
--- /dev/null
+++ b/mysql-test/include/with_recursive.inc
@@ -0,0 +1,1073 @@
+SET @@max_recursive_iterations = 10000;
+flush STATUS;
+
+--echo # Mutual recursion unsupported; cycles must have one node only
+--error ER_RECURSIVE_WITHOUT_ANCHORS
+WITH RECURSIVE qn AS (SELECT * FROM qn2),
+               qn2 AS (SELECT * FROM qn)
+SELECT * FROM qn;
+
+--echo # At least one anchor member, all anchors before all recursive
+
+--error ER_RECURSIVE_WITHOUT_ANCHORS
+WITH RECURSIVE qn AS
+  (SELECT 1 FROM qn)
+SELECT count(*) FROM qn;
+
+WITH RECURSIVE qn AS
+  (SELECT 1 UNION ALL SELECT 1 FROM qn UNION ALL SELECT 1)
+SELECT count(*) FROM qn;
+
+--error ER_RECURSIVE_WITHOUT_ANCHORS
+WITH RECURSIVE qn AS
+  (SELECT 1 FROM qn UNION ALL SELECT 1 FROM qn)
+SELECT * FROM qn;
+
+--echo # It's ok to have the RECURSIVE word without any recursive member
+
+WITH RECURSIVE qn AS
+  (SELECT 1 FROM dual UNION ALL
+   SELECT 1 FROM dual)
+SELECT * FROM qn;
+
+--echo # UNION DISTINCT allowed
+
+WITH RECURSIVE qn AS
+  (SELECT 1 FROM dual UNION
+   SELECT 1 FROM qn)
+SELECT * FROM qn;
+
+--echo # No aggregation on the QN
+CREATE TABLE t1(b int);
+INSERT INTO t1 VALUES(10),(20),(10);
+
+WITH RECURSIVE qn AS
+  (SELECT max(b) AS a FROM t1 UNION
+   SELECT a FROM qn)
+SELECT * FROM qn;
+
+--error ER_NOT_STANDARD_COMPLIANT_RECURSIVE
+WITH RECURSIVE qn AS
+  (SELECT b AS a FROM t1 UNION
+   SELECT max(a) FROM qn)
+SELECT * FROM qn;
+
+--echo # No window functions
+WITH RECURSIVE qn AS
+  (SELECT rank() over (ORDER BY b) AS a FROM t1 UNION
+   SELECT a FROM qn)
+SELECT * FROM qn;
+--error ER_NOT_STANDARD_COMPLIANT_RECURSIVE
+WITH RECURSIVE qn AS
+  (SELECT b AS a FROM t1 UNION
+   SELECT rank() over (ORDER BY a) FROM qn)
+SELECT * FROM qn;
+DROP TABLE t1;
+
+--error ER_NOT_STANDARD_COMPLIANT_RECURSIVE
+WITH RECURSIVE qn AS
+  (SELECT 1 AS a FROM dual UNION ALL
+   SELECT max(a) FROM qn)
+SELECT * FROM qn;
+
+WITH RECURSIVE qn AS
+  (SELECT 1 AS a FROM dual GROUP BY a UNION ALL
+   SELECT a+1 FROM qn WHERE a<3)
+SELECT * FROM qn;
+
+WITH RECURSIVE qn AS
+  (SELECT 1 AS a FROM dual UNION ALL
+   SELECT a FROM qn GROUP BY a)
+SELECT count(*)FROM qn;
+
+--echo # No subquery referencing a QN
+--error ER_NOT_STANDARD_COMPLIANT_RECURSIVE
+WITH RECURSIVE qn AS (
+SELECT 1 FROM dual UNION ALL
+SELECT 1 FROM dual WHERE 1 NOT in(SELECT * FROM qn))
+SELECT * FROM qn;
+
+--error ER_NOT_SUPPORTED_YET
+WITH RECURSIVE qn AS (
+SELECT 1 FROM dual UNION ALL
+SELECT 1 FROM qn
+ORDER BY (SELECT * FROM qn))
+SELECT count(*) FROM qn;
+
+WITH RECURSIVE qn AS (
+SELECT 1 FROM dual UNION ALL
+SELECT * FROM (SELECT * FROM qn) AS dt)
+SELECT count(*) FROM qn;
+
+--error ER_NOT_SUPPORTED_YET
+WITH RECURSIVE qn AS (
+SELECT 1 AS a FROM dual UNION ALL
+SELECT 1 FROM qn
+ORDER BY a)
+SELECT count(*) FROM qn;
+--echo # No matter if global, or attached to one recursive member.
+
+WITH RECURSIVE qn AS (
+SELECT 1 AS a FROM dual UNION ALL
+(SELECT 1 FROM qn ORDER BY a))
+SELECT count(*) FROM qn;
+
+--echo # Allowed on non-recursive query block (though pointless)
+WITH RECURSIVE qn AS (
+(SELECT 1 AS a FROM dual ORDER BY a) UNION ALL
+SELECT a+1 FROM qn WHERE a<3)
+SELECT * FROM qn;
+
+--error ER_NOT_SUPPORTED_YET
+WITH RECURSIVE qn AS (
+SELECT 1 AS a FROM dual UNION ALL
+SELECT 1 FROM qn
+LIMIT 10)
+SELECT count(*) FROM qn;
+
+WITH RECURSIVE qn AS (
+SELECT 1 AS a FROM dual UNION ALL
+(SELECT 1 FROM qn LIMIT 10))
+SELECT count(*) FROM qn;
+
+WITH RECURSIVE qn AS
+(SELECT 1 UNION ALL SELECT DISTINCT 3 FROM qn)
+SELECT count(*) FROM qn;
+
+--error 4008
+WITH RECURSIVE qn AS (SELECT 1 FROM dual UNION ALL
+                      SELECT 1 FROM dual
+                        WHERE 1 NOT in(SELECT * FROM qn))
+SELECT * FROM qn;
+
+--echo # Numbers from 123 to 130:
+
+WITH RECURSIVE qn AS (SELECT 123 AS a UNION ALL SELECT 1+a FROM qn WHERE a<130) SELECT * FROM qn;
+
+--echo # One-level recursive sequence of numbers
+WITH RECURSIVE qn AS (SELECT 1 AS n, 2 AS un UNION ALL SELECT 1+n, un*5-6 FROM qn WHERE n<10) SELECT * FROM qn;
+
+--echo # Fibonacci
+
+WITH RECURSIVE qn AS (SELECT 1 AS n, 1 AS un, 1 AS unp1 UNION ALL SELECT 1+n, unp1, un+unp1 FROM qn WHERE n<10) SELECT * FROM qn;
+
+--echo # Validate that cast(a_varchar as char) produces a varchar, not a
+--echo # char.
+CREATE TABLE t(c char(3), vc varchar(3), b binary(3), vb varbinary(3));
+CREATE TABLE u
+SELECT cast(c AS char(4)), cast(vc AS char(4)),
+       cast(b AS binary(4)), cast(vb AS binary(4)),
+       "abc" AS literal_c, cast("abc" AS char(4)),
+       _binary "abc" AS literal_b, cast(_binary "abc" AS binary(4))
+FROM t;
+SHOW CREATE TABLE u;
+DROP TABLE t,u;
+
+--echo # if it used char the 'x' would fall off due to spaces.
+WITH RECURSIVE qn AS (SELECT 1 AS n, cast('x' AS char(100)) AS un UNION ALL SELECT 1+n, concat(un,'x') FROM qn WHERE n<10) SELECT * FROM qn;
+
+--echo # String now growing at the left
+
+WITH RECURSIVE qn AS (SELECT cast("x" AS char(10)) AS a FROM dual
+UNION ALL SELECT concat("x",a) FROM qn WHERE length(a)<10) SELECT *
+FROM qn;
+
+--echo # Forgot cast-as-char(10) in anchor => qn.a column has length 1
+--echo # => concat() is cast as char(1) => truncation
+--echo # => length is always 1 => infinite loop;
+
+let $query=
+WITH RECURSIVE qn AS (SELECT "x" AS a FROM dual
+UNION ALL SELECT concat("x",a) FROM qn WHERE length(a)<10) SELECT count(*)
+FROM qn;
+eval $query;
+
+--echo # Overflow integer type INT (max 4G)
+--error ER_DATA_OUT_OF_RANGE
+WITH RECURSIVE qn AS (SELECT 1 AS a FROM dual
+UNION ALL SELECT a*2000 FROM qn WHERE a<10000000000000000000) SELECT count(*) FROM qn;
+
+--echo # Use Decimal
+WITH RECURSIVE qn AS (SELECT cast(1 AS decimal(30,0)) AS a FROM dual
+UNION ALL SELECT a*2000 FROM qn WHERE a<10000000000000000000) SELECT * FROM qn;
+
+#MDEV-16505
+WITH RECURSIVE qn AS (SELECT 123 AS a UNION #ALL 
+SELECT NULL FROM qn WHERE a IS NOT NULL) SELECT * FROM qn;
+
+--echo # Mixing really unrelated types: the goal is to report a sensible
+--echo # error and not crash.
+
+--echo # The Point becomes a string which is an invalid integer, cast string to int -> result 0:
+
+WITH RECURSIVE qn AS (
+SELECT 1 AS a,1
+UNION ALL
+SELECT a+1,ST_PointFromText('POINT(10 10)') FROM qn WHERE a<2)
+SELECT * FROM qn;
+
+--echo # POINT in anchor => BLOB in tmp table => not MEMORY engine => Innodb
+--error ER_CANT_CREATE_GEOMETRY_OBJECT
+WITH RECURSIVE qn AS (
+SELECT 1 AS a,ST_PointFromText('POINT(10 10)')
+UNION ALL
+SELECT a+1,1 FROM qn WHERE a<2)
+SELECT * FROM qn;
+
+--echo # Same number of columns in anchor and recursive members
+--error ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT
+WITH RECURSIVE qn AS
+(
+SELECT 1
+UNION ALL
+SELECT 3, 0 FROM qn
+)
+SELECT * FROM qn;
+
+--echo # Mismatch in column name and column count; problem specific of
+--echo # recursive CTE which creates tmp table earlier in preparation.
+--error ER_WITH_COL_WRONG_LIST
+WITH RECURSIVE q (b) AS (SELECT 1, 1 UNION ALL SELECT 1, 1 FROM q)
+SELECT b FROM q;
+
+--echo # Cannot have two recursive refs in FROM:
+
+--error 4008
+WITH RECURSIVE qn AS (
+SELECT 123 AS a UNION ALL
+SELECT 1+qn.a FROM qn, qn AS qn1 WHERE qn1.a<130)
+SELECT * FROM qn;
+
+--echo # Prove that a materialized QN is shared among all references:
+
+flush STATUS;
+WITH RECURSIVE qn AS (
+SELECT 123 AS a UNION ALL
+SELECT 1+a FROM qn WHERE a<125)
+SELECT * FROM qn;
+SHOW STATUS LIKE "handler_write";
+flush STATUS;
+WITH RECURSIVE qn AS (
+SELECT 123 AS a UNION ALL
+SELECT 1+a FROM qn WHERE a<125)
+SELECT * FROM qn, qn AS qn1;
+SHOW STATUS LIKE "handler_write";
+SHOW STATUS LIKE 'Created_tmp%table%';
+
+--echo # Also works if references are nested inside other query names:
+flush STATUS;
+WITH RECURSIVE inner_ AS (
+SELECT 123 AS a UNION ALL
+SELECT 1+a FROM inner_ WHERE a<125),
+outer_ AS (SELECT * FROM inner_ LIMIT 10)
+SELECT * FROM outer_, outer_ AS outer1;
+SHOW STATUS LIKE "handler_write";
+
+flush STATUS;
+WITH RECURSIVE inner_ AS (
+SELECT 123 AS a UNION ALL
+SELECT 1+a FROM inner_ WHERE a<125),
+outer_ AS
+(SELECT inner_.a, inner1.a AS a1
+FROM inner_, inner_ AS inner1 LIMIT 10)
+SELECT * FROM outer_;
+SHOW STATUS LIKE "handler_write";
+
+--echo # Even if the two query names are recursive:
+flush STATUS;
+WITH RECURSIVE inner_ AS (
+SELECT 123 AS a UNION ALL
+SELECT 1+a FROM inner_ WHERE a<125),
+outer_ AS
+(SELECT a FROM inner_ UNION ALL
+SELECT a*2 FROM outer_ WHERE a<1000)
+SELECT a FROM outer_;
+SHOW STATUS LIKE "handler_write";
+
+--echo # Optimizer must be allowed to put the recursive reference first
+
+CREATE TABLE t1(a int);
+INSERT INTO t1 VALUES(1),(2);
+
+--error ER_NOT_STANDARD_COMPLIANT_RECURSIVE
+WITH RECURSIVE qn AS
+(
+SELECT 1 FROM t1
+UNION ALL
+SELECT 1 FROM t1 LEFT JOIN qn ON 1
+)
+SELECT * FROM qn;
+
+--echo # Empty anchor
+
+WITH RECURSIVE qn AS
+(
+SELECT a FROM t1 WHERE 0
+UNION ALL
+SELECT a+1 FROM qn
+)
+SELECT * FROM qn;
+
+WITH RECURSIVE qn AS
+(
+SELECT a FROM t1 WHERE a>10
+UNION ALL
+SELECT a+1 FROM qn
+)
+SELECT * FROM qn;
+
+--echo # UNION DISTINCT in anchor parts 
+INSERT INTO t1 VALUES(1),(2);
+SET @c=0, @d=0;
+--error ER_NOT_SUPPORTED_YET
+WITH RECURSIVE qn AS
+(
+SELECT 1,0 AS col FROM t1
+UNION DISTINCT
+SELECT 1,0 FROM t1
+UNION ALL
+SELECT 3, 0*(@c:=@c+1) FROM qn WHERE @c<1
+UNION ALL
+SELECT 3, 0*(@d:=@d+1) FROM qn WHERE @d<1
+)
+SELECT * FROM qn;
+
+--echo # UNION DISTINCT affecting recursive member, followed by UNION ALL
+INSERT INTO t1 VALUES(1),(2);
+
+SET @c=0, @d=0;
+--error ER_NOT_SUPPORTED_YET
+WITH RECURSIVE qn AS
+(
+SELECT 1,0 AS col FROM t1
+UNION DISTINCT
+SELECT 3, 0*(@c:=@c+1) FROM qn WHERE @c<1
+UNION ALL
+SELECT 3, 0*(@d:=@d+1) FROM qn WHERE @d<1
+)
+SELECT * FROM qn;
+
+--echo # create select
+CREATE TABLE t2
+WITH RECURSIVE qn AS (
+SELECT 123 AS a UNION ALL SELECT 1+a FROM qn WHERE a<130)
+SELECT * FROM qn;
+SELECT * FROM t2;
+DROP TABLE t2;
+
+--echo # insert select
+DELETE FROM t1;
+INSERT INTO t1
+WITH RECURSIVE qn AS (
+SELECT 123 AS a UNION ALL SELECT 1+a FROM qn WHERE a<130)
+SELECT * FROM qn;
+SELECT * FROM t1;
+
+--echo # Using insertion target inside recursive query
+DELETE FROM t1;
+INSERT INTO t1 VALUES(1),(2);
+INSERT INTO t1
+WITH RECURSIVE qn AS (
+SELECT 123 AS a UNION ALL SELECT 1+qn.a FROM qn, t1 WHERE qn.a<125)
+SELECT * FROM qn;
+SELECT * FROM t1;
+
+DROP TABLE t1;
+
+--echo # insert into tmp table (a likely use case)
+
+CREATE TEMPORARY TABLE t1(a int);
+INSERT INTO t1
+WITH RECURSIVE qn AS (
+SELECT 123 AS a UNION ALL SELECT 1+a FROM qn WHERE a<130)
+SELECT * FROM qn;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+--echo # create view
+CREATE VIEW v1 AS
+WITH RECURSIVE qn AS (
+SELECT 123 AS a UNION ALL SELECT 1+a FROM qn WHERE a<130)
+SELECT * FROM qn;
+SELECT * FROM v1;
+DROP VIEW v1;
+
+--echo # Recursive QN can be constant (0-row or 1-row) for the
+--echo # optimizer if its members have impossible conditions:
+
+#EXPLAIN WITH RECURSIVE qn AS (SELECT 1 AS n WHERE 0 UNION ALL SELECT n+1 FROM qn WHERE 0) SELECT * FROM qn;
+#WITH RECURSIVE qn AS (SELECT 1 AS n WHERE 0 UNION ALL SELECT n+1 FROM qn WHERE 0) SELECT * FROM qn;
+
+#EXPLAIN WITH RECURSIVE qn AS (SELECT 1 AS n WHERE 1 UNION ALL SELECT n+1 FROM qn WHERE 0) SELECT * FROM qn;
+#WITH RECURSIVE qn AS (SELECT 1 AS n WHERE 0 UNION ALL SELECT n+1 FROM qn WHERE 0) SELECT * FROM qn;
+
+--echo # Recursive refs should never use indexes to read:
+--echo # first, optimization of top query creates a key on q.b;
+--echo # then optimization of scalar subquery, when it optimizes the
+--echo # recursive member, must be prevented from re-using this key
+--echo # (it was a bug that it re-used it, as the index is covering
+--echo # and adjust_access_methods() has a heuristic which converts a
+--echo # table scan to index scan, so it wrongly used an index scan).
+let $query=
+WITH RECURSIVE q (b) AS
+ (SELECT 1 UNION ALL SELECT 1+b FROM q WHERE b<10)
+ SELECT (SELECT q1.b FROM q AS q2 WHERE q2.b=3) FROM q AS q1 WHERE q1.b=3;
+eval EXPLAIN $query;
+eval $query;
+
+--echo # This is from my blog so I can use it here.
+--echo # Tests depth-first etc
+
+CREATE TABLE employees (
+ID INT PRIMARY KEY,
+NAME VARCHAR(100),
+MANAGER_ID INT,
+INDEX (MANAGER_ID),
+FOREIGN KEY (MANAGER_ID) REFERENCES employees(ID)
+);
+INSERT INTO employees VALUES
+(333, "Yasmina", NULL),
+(198, "John", 333),
+(692, "Tarek", 333),
+(29, "Pedro", 198),
+(4610, "Sarah", 29),
+(72, "Pierre", 29),
+(123, "Adil", 692);
+ANALYZE TABLE employees;
+
+--echo # Depth-first.
+
+--echo # Also test column names, and their reference in the recursive member.
+WITH RECURSIVE employees_extended(ID, NAME, PATH)
+AS
+(
+  SELECT ID, NAME, CAST(ID AS CHAR(200))
+  FROM employees
+  WHERE MANAGER_ID IS NULL
+UNION ALL
+  SELECT S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID)
+  FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID
+)
+SELECT * FROM employees_extended ORDER BY PATH;
+
+--echo # Breadth-first is likely what we get, if no ordering
+
+WITH RECURSIVE employees_extended
+AS
+(
+  SELECT ID, NAME, CAST(ID AS CHAR(200)) AS PATH
+  FROM employees
+  WHERE MANAGER_ID IS NULL
+UNION ALL
+  SELECT S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID)
+  FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID
+)
+SELECT * FROM employees_extended;
+
+--echo # But to be really sure we have breadth-first, we generate a
+--echo # numeric column SEQ. And sort by NAME, to have repeatable
+--echo # order of siblings (who have the same SEQ).
+
+WITH RECURSIVE employees_extended
+AS
+(
+  SELECT 0 AS SEQ, ID, NAME, CAST(ID AS CHAR(200)) AS PATH
+  FROM employees
+  WHERE MANAGER_ID IS NULL
+UNION ALL
+  SELECT M.SEQ+1, S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID)
+  FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID
+)
+SELECT * FROM employees_extended ORDER BY SEQ, NAME;
+
+--echo # Or, use a user variable, then all rows have different number:
+
+WITH RECURSIVE employees_extended
+AS
+(
+  SELECT (@s:=0) AS SEQ, ID, NAME, CAST(ID AS CHAR(200)) AS PATH
+  FROM employees
+  WHERE MANAGER_ID IS NULL
+UNION ALL
+  SELECT (@s:=@s+1), S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID)
+  FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID
+)
+SELECT * FROM employees_extended ORDER BY SEQ;
+
+--echo # Direct & indirect reports of John = having John in their PATH
+
+WITH RECURSIVE employees_extended
+AS
+(
+  SELECT ID, NAME, CAST(ID AS CHAR(200)) AS PATH
+  FROM employees
+  WHERE MANAGER_ID IS NULL
+UNION ALL
+  SELECT S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID)
+  FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID
+)
+SELECT * FROM employees_extended
+WHERE FIND_IN_SET((SELECT ID FROM employees WHERE NAME='John'),
+                  PATH);
+
+--echo # Exclude John, he's not a report of himself;
+--echo # bonus: use a QN to cache his ID.
+
+WITH RECURSIVE employees_extended(ID, NAME, PATH)
+AS
+(
+  SELECT ID, NAME, CAST(ID AS CHAR(200))
+  FROM employees
+  WHERE MANAGER_ID IS NULL
+UNION ALL
+  SELECT S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID)
+  FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID
+),
+JOHN_ID AS (SELECT ID FROM employees WHERE NAME='John')
+SELECT e.* FROM employees_extended e, JOHN_ID
+WHERE FIND_IN_SET(JOHN_ID.ID,
+                  PATH)
+      AND e.ID<>JOHN_ID.ID;
+
+--echo # Similar, but faster: start dive at John (and include him again).
+WITH RECURSIVE employees_extended
+AS
+(
+  SELECT ID, NAME, CAST(ID AS CHAR(200)) AS PATH
+  FROM employees
+  WHERE NAME='John'
+UNION ALL
+  SELECT S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID)
+  FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID
+)
+SELECT * FROM employees_extended;
+
+--echo # Get the management chain above Pierre:
+
+WITH RECURSIVE employees_extended
+AS
+(
+  SELECT ID, NAME, MANAGER_ID, CAST(ID AS CHAR(200)) AS PATH
+  FROM employees
+  WHERE NAME='Pierre'
+UNION ALL
+  SELECT S.ID, S.NAME, S.MANAGER_ID, CONCAT(M.PATH, ",", S.ID)
+  FROM employees_extended M JOIN employees S ON M.MANAGER_ID=S.ID
+)
+SELECT * FROM employees_extended;
+
+--echo # Get the management chain above Pierre, without PATH
+
+WITH RECURSIVE employees_extended
+AS
+(
+  SELECT ID, NAME, MANAGER_ID
+  FROM employees
+  WHERE NAME='Pierre'
+UNION ALL
+  SELECT S.ID, S.NAME, S.MANAGER_ID
+  FROM employees_extended M JOIN employees S ON M.MANAGER_ID=S.ID
+)
+SELECT * FROM employees_extended;
+
+--echo # Get the management chain above Pierre and Sarah, without PATH
+
+WITH RECURSIVE employees_extended
+AS
+(
+  SELECT ID, NAME, MANAGER_ID
+  FROM employees
+  WHERE NAME='Pierre' OR NAME='Sarah'
+UNION ALL
+  SELECT S.ID, S.NAME, S.MANAGER_ID
+  FROM employees_extended M JOIN employees S ON M.MANAGER_ID=S.ID
+)
+SELECT * FROM employees_extended;
+
+--echo # Do it without duplicates
+
+WITH RECURSIVE employees_extended
+AS
+(
+  SELECT ID, NAME, MANAGER_ID
+  FROM employees
+  WHERE NAME='Pierre' OR NAME='Sarah'
+UNION
+  SELECT S.ID, S.NAME, S.MANAGER_ID
+  FROM employees_extended M JOIN employees S ON M.MANAGER_ID=S.ID
+)
+SELECT * FROM employees_extended;
+
+--echo # Cycles. Introduce an oddity:
+
+--echo # Sarah is indirect report of John and is his manager.
+UPDATE employees SET MANAGER_ID=4610 WHERE NAME="John";
+
+--echo # Add cycle detection: the row closing a cycle is marked with
+--echo # IS_CYCLE=1, which stops the iterations. The outer SELECT
+--echo # could then want to see only that row, or only previous ones.
+WITH RECURSIVE employees_extended(ID, NAME, PATH, IS_CYCLE)
+AS
+(
+  SELECT ID, NAME, CAST(ID AS CHAR(200)), 0
+  FROM employees
+  WHERE NAME='John'
+UNION ALL
+  SELECT S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID), FIND_IN_SET(S.ID, M.PATH)
+  FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID
+  WHERE M.IS_CYCLE=0
+)
+SELECT * FROM employees_extended;
+
+DROP TABLE employees;
+
+--echo # Two recursive members.
+
+CREATE TABLE t1 (id int, name char(10), leftpar int, rightpar int);
+INSERT INTO t1 VALUES
+(1, "A", 2, 3),
+  (2, "LA", 4, 5),
+     (4, "LLA", 6, 7),
+        (6, "LLLA", NULL, NULL),
+        (7, "RLLA", NULL, NULL),
+     (5, "RLA", 8, 9),
+        (8, "LRLA", NULL, NULL),
+        (9, "RRLA", NULL, NULL),
+  (3, "RA", 10, 11),
+     (10, "LRA", 12, 13),
+     (11, "RRA", 14, 15),
+        (15, "RRRA", NULL, NULL),
+(16, "B", 17, 18),
+  (17, "LB", NULL, NULL),
+  (18, "RB", NULL, NULL)
+;
+
+--echo # Shuffle rows to make sure the algorithm works
+--echo # with any read order of rows above
+
+CREATE TABLE t2 SELECT * FROM t1 ORDER BY rand();
+
+--echo # Tree-walking query. We turn off the Query Cache: indeed
+--echo # sometimes pb2 enables Query Cache and as we run twice the
+--echo # same query the 2nd may not actually be executed so the value
+--echo # of Created_tmp_tables displayed at end becomes "one less").
+
+let $query=
+WITH RECURSIVE tree_of_a AS
+(
+SELECT *, cast(id AS char(200)) AS path FROM t2 WHERE name="A"
+UNION ALL
+SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON
+t2.id=tree_of_a.leftpar
+UNION ALL
+SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON
+t2.id=tree_of_a.rightpar
+)
+SELECT * FROM tree_of_a;
+
+--echo # Note that without ORDER BY, order of rows would be random as BNL
+--echo # implies that the randomized t2 is the driving table in the
+--echo # joining of rows.
+
+--replace_column 10 #
+eval EXPLAIN extended $query ORDER BY path;
+
+eval $query ORDER BY path;
+
+--echo # Equivalent query with one single recursive query block:
+
+WITH RECURSIVE tree_of_a AS
+(
+SELECT *, cast(id AS char(200)) AS path FROM t2 WHERE name="A"
+UNION ALL
+SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON
+(t2.id=tree_of_a.leftpar OR t2.id=tree_of_a.rightpar)
+)
+SELECT * FROM tree_of_a
+ORDER BY path;
+
+--echo # Demonstrate a case where an index is automatically created on
+--echo # the derived table and used to read this table in the outer
+--echo # query (but correctly not used to read it in the recursive
+--echo # query).
+
+let $query=
+WITH RECURSIVE tree_of_a AS
+(
+SELECT *, cast(id AS char(200)) AS path FROM t2 WHERE name="A"
+UNION ALL
+SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON
+t2.id=tree_of_a.leftpar
+UNION ALL
+SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON
+t2.id=tree_of_a.rightpar
+)
+SELECT * FROM tree_of_a WHERE id=2;
+
+--replace_column 10 #
+eval EXPLAIN $query;
+
+eval $query;
+
+DROP TABLE t1,t2;
+
+let $query=
+WITH RECURSIVE cte AS
+(
+  SELECT 1 AS n UNION ALL
+  SELECT n+1 FROM cte WHERE n<10000
+)
+SELECT sum(cte1.n*cte2.n*cte3.n)=2490508525950000
+FROM cte cte1, cte cte2, cte cte3
+WHERE cte1.n=cte2.n+10 AND cte2.n+20=cte3.n;
+
+--replace_column 10 #
+eval EXPLAIN $query;
+eval $query;
+
+--echo #
+--echo # Transitive closure
+--echo #
+
+CREATE TABLE nodes(id int);
+CREATE TABLE arcs(from_id int, to_id int);
+INSERT INTO nodes VALUES(1),(2),(3),(4),(5),(6),(7),(8);
+INSERT INTO arcs VALUES(1,3), (3,6), (1,4), (4,6), (6,2), (2,1);
+
+--echo # UNION ALL leads to infinite loop as 1 is reachable from 1;
+--echo # so we stop it with a maximum depth 8 (8 nodes in graph)
+
+WITH RECURSIVE cte AS
+(
+  SELECT id, 0 AS depth FROM nodes WHERE id=1
+  UNION ALL
+  SELECT to_id, depth+1 FROM arcs, cte
+  WHERE from_id=cte.id AND depth<8
+)
+SELECT count(*), max(depth) FROM cte;
+
+--echo # Can use cycle detection:
+
+WITH RECURSIVE cte AS
+(
+  SELECT id, cast(id AS char(200)) AS path, 0 AS is_cycle
+  FROM nodes WHERE id=1
+  UNION ALL
+  SELECT to_id, concat(cte.path, ",", to_id), find_in_set(to_id, path)
+  FROM arcs, cte
+  WHERE from_id=cte.id AND is_cycle=0
+)
+SELECT * FROM cte;
+
+--echo # It is simpler with DISTINCT:
+WITH RECURSIVE cte AS
+(
+  SELECT id FROM nodes WHERE id=1
+  UNION
+  SELECT to_id FROM arcs, cte WHERE from_id=cte.id
+)
+SELECT * FROM cte;
+
+DROP TABLE nodes, arcs;
+
+--echo # Hash field and MEMORY don't work together. Make long distinct
+--echo # key to force hash field, to see if it switches to InnoDB.
+
+--echo # Not too long key (500 bytes in latin1)
+WITH RECURSIVE cte AS
+ (
+  SELECT 1 AS n,
+  repeat('a',500) AS f, '' AS g,
+  '' AS h, '' AS i
+  UNION
+  SELECT n+1,
+  '','','',''
+  FROM cte WHERE n<100)
+SELECT sum(n) FROM cte;
+
+SHOW STATUS LIKE 'Created_tmp_disk_tables';
+
+--echo # Too long key (>3000 bytes in latin1)
+WITH RECURSIVE cte AS
+ (
+  SELECT 1 AS n,
+  repeat('a',500) AS f, repeat('a',500) AS g,
+  repeat('a',500) AS h, repeat('a',500) AS i,
+  repeat('a',500) AS j, repeat('a',500) AS k,
+  repeat('a',500) AS l, repeat('a',500) AS m
+  UNION
+  SELECT n+1,
+  '','','','','','','',''
+  FROM cte WHERE n<100)
+SELECT sum(n) FROM cte;
+
+--echo #
+--echo # In query planning, the recursive reference's row count is
+--echo # said to be the estimated row count of all non-recursive query
+--echo # blocks
+
+CREATE TABLE t1(a int);
+--echo # 15 rows:
+INSERT INTO t1 VALUES (), (), (), (), (), (), (), (), (), (), (), (),
+(), (), ();
+ANALYZE TABLE t1;
+--echo # EXPLAIN says: in non-recursive QB we'll read 15 rows of t1,
+--echo # in recursive QB we'll read 15 rows of qn, keep only 0.33
+--echo # due to WHERE, that makes 4 (due to rounding), and in the
+--echo # derived table we'll thus have 15+4=19. That ignores
+--echo # next repetitions of the recursive QB which are unpredictable.
+EXPLAIN WITH RECURSIVE qn AS
+(SELECT 1 AS a FROM t1 UNION ALL SELECT a+1 FROM qn WHERE qn.a<100)
+SELECT * FROM qn;
+EXPLAIN WITH RECURSIVE qn AS
+(SELECT 1 AS a FROM t1 UNION DISTINCT SELECT a+1 FROM qn WHERE qn.a<100)
+SELECT * FROM qn;
+DROP TABLE t1;
+
+SHOW STATUS LIKE 'Created_tmp_disk_tables';
+
+flush STATUS;
+
+WITH RECURSIVE q (b) AS
+(SELECT 1 UNION ALL SELECT 1+b FROM q WHERE b<2000)
+SELECT min(b),max(b),avg(b) FROM q;
+SHOW STATUS LIKE 'Created_tmp_disk_tables';
+
+--echo # Test when conversion to InnoDB affects recursive references which
+--echo # are not open yet (those of q1):
+flush STATUS;
+WITH RECURSIVE q (b) AS
+(SELECT 1 UNION ALL SELECT 1+b FROM q WHERE b<2000)
+SELECT min(q.b),max(q.b),avg(q.b) FROM q, q AS q1;
+SHOW STATUS LIKE 'Created_tmp_disk_tables';
+
+--echo # Same, but make q1 the writer; this is to test overflow when
+--echo # the writer isn't first in the 'tmp_tables' list
+
+flush STATUS;
+WITH RECURSIVE q (b) AS
+(SELECT 1 UNION ALL SELECT 1+b FROM q WHERE b<2000)
+SELECT min(q.b),max(q.b),avg(q.b) FROM q RIGHT JOIN q AS q1 ON 1;
+SHOW STATUS LIKE 'Created_tmp_disk_tables';
+
+--echo # Test when outer query reads CTE with an index.
+--echo # Overflow doesn't happen at same row as queries above, as this
+--echo # table has an index which makes it grow faster.
+
+let $query=
+WITH RECURSIVE q (b) AS
+(SELECT 1 UNION ALL SELECT 1+b FROM q WHERE b<2000)
+SELECT min(b),max(b),avg(b) FROM q WHERE b=300;
+
+eval EXPLAIN $query;
+SHOW STATUS LIKE 'Created_tmp_disk_tables';
+eval $query;
+SHOW STATUS LIKE 'Created_tmp_disk_tables';
+
+--echo # Verify that rows come out in insertion order.
+--echo # If they didn't, the sequences of @c and of 'b'
+--echo # would not be identical and the sum wouldn't be
+--echo # 1^2 + ... + 2000^2 = n(n+1)(2n+1)/6 = 2668667000
+
+SET @c:=1;
+flush STATUS;
+WITH RECURSIVE q (b, c) AS
+(SELECT 1, 1 UNION ALL SELECT (1+b), (@c:=(@c+1)) FROM q WHERE b<2000)
+SELECT sum(b*c) FROM q;
+SHOW STATUS LIKE 'Created_tmp_disk_tables';
+
+--echo #
+--echo # Bug#23495283 WL3634:ASSERTION `0' FAILED IN FIELD* ITEM::TMP_TABLE_FIELD_FROM_FIELD_TYPE
+--echo #
+
+CREATE TABLE t1(c1 DATETIME, c2 INT, KEY(c1));
+
+--error ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT
+WITH RECURSIVE cte AS ( SELECT a.c1 AS field1, 0 AS cycle FROM (t1 AS a)
+UNION ALL SELECT b.c2 FROM cte AS a JOIN t1 AS b) SELECT * FROM cte;
+
+DROP TABLE t1;
+
+--echo #
+--echo # Bug#23645090 WL3634: INVALID WRITE AND READ VALGRIND ERRORS
+--echo #
+
+CREATE TABLE A (
+  col_date date DEFAULT NULL,
+  col_datetime_key datetime DEFAULT NULL,
+  col_time_key time DEFAULT NULL,
+  col_varchar_key varchar(1) DEFAULT NULL,
+  col_int_key int(11) DEFAULT NULL,
+  col_blob_key blob,
+  col_varchar varchar(1) DEFAULT NULL,
+  col_date_key date DEFAULT NULL,
+  col_time time DEFAULT NULL,
+  col_blob blob,
+  pk int(11) NOT NULL AUTO_INCREMENT,
+  col_int int(11) DEFAULT NULL,
+  col_datetime datetime DEFAULT NULL,
+  PRIMARY KEY (pk),
+  KEY col_datetime_key (col_datetime_key),
+  KEY col_time_key (col_time_key),
+  KEY col_varchar_key (col_varchar_key),
+  KEY col_int_key (col_int_key),
+  KEY col_blob_key (col_blob_key(255)),
+  KEY col_date_key (col_date_key)
+) DEFAULT CHARSET=latin1;
+
+CREATE TABLE AA (
+  col_varchar varchar(1) DEFAULT NULL,
+  col_date date DEFAULT NULL,
+  col_varchar_key varchar(1) DEFAULT NULL,
+  col_date_key date DEFAULT NULL,
+  col_datetime_key datetime DEFAULT NULL,
+  col_time_key time DEFAULT NULL,
+  pk int(11) NOT NULL AUTO_INCREMENT,
+  col_time time DEFAULT NULL,
+  col_int_key int(11) DEFAULT NULL,
+  col_datetime datetime DEFAULT NULL,
+  col_int int(11) DEFAULT NULL,
+  col_blob blob,
+  col_blob_key blob,
+  PRIMARY KEY (pk),
+  KEY col_varchar_key (col_varchar_key),
+  KEY col_date_key (col_date_key),
+  KEY col_datetime_key (col_datetime_key),
+  KEY col_time_key (col_time_key),
+  KEY col_int_key (col_int_key),
+  KEY col_blob_key (col_blob_key(255))
+) DEFAULT CHARSET=latin1;
+
+CREATE TABLE BB (
+  col_date date DEFAULT NULL,
+  col_blob_key blob,
+  col_time time DEFAULT NULL,
+  col_varchar_key varchar(1) DEFAULT NULL,
+  col_varchar varchar(1) DEFAULT NULL,
+  col_blob blob,
+  pk int(11) NOT NULL AUTO_INCREMENT,
+  col_int_key int(11) DEFAULT NULL,
+  col_datetime datetime DEFAULT NULL,
+  col_time_key time DEFAULT NULL,
+  col_datetime_key datetime DEFAULT NULL,
+  col_date_key date DEFAULT NULL,
+  col_int int(11) DEFAULT NULL,
+  PRIMARY KEY (pk),
+  KEY col_blob_key (col_blob_key(255)),
+  KEY col_varchar_key (col_varchar_key),
+  KEY col_int_key (col_int_key),
+  KEY col_time_key (col_time_key),
+  KEY col_datetime_key (col_datetime_key),
+  KEY col_date_key (col_date_key)
+) AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
+
+CREATE TABLE D (
+  col_varchar_key varchar(1) DEFAULT NULL,
+  col_datetime datetime DEFAULT NULL,
+  col_date_key date DEFAULT NULL,
+  col_int int(11) DEFAULT NULL,
+  col_time time DEFAULT NULL,
+  col_blob blob,
+  col_int_key int(11) DEFAULT NULL,
+  col_blob_key blob,
+  col_varchar varchar(1) DEFAULT NULL,
+  col_datetime_key datetime DEFAULT NULL,
+  col_date date DEFAULT NULL,
+  col_time_key time DEFAULT NULL,
+  pk int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (pk),
+  KEY col_varchar_key (col_varchar_key),
+  KEY col_date_key (col_date_key),
+  KEY col_int_key (col_int_key),
+  KEY col_blob_key (col_blob_key(255)),
+  KEY col_datetime_key (col_datetime_key),
+  KEY col_time_key (col_time_key)
+) DEFAULT CHARSET=latin1;
+
+WITH RECURSIVE cte AS (
+SELECT    alias1 . `col_blob_key` AS field1, 0 AS cycle
+FROM ( BB AS alias1 , ( D AS alias2 , AA AS alias3 ) )
+WHERE (
+alias1 . pk = 225
+OR ( alias1 . col_int_key = 69 AND alias1 . col_blob_key = 'p' )
+)
+UNION ALL
+SELECT t1.pk, t2.cycle
+FROM cte AS t2 JOIN A AS t1
+WHERE t2.field1 = t1.`col_int_key`
+AND t2.cycle =1 ) SELECT  * FROM cte;
+
+DROP TABLE IF EXISTS A, AA, BB, D;
+
+--echo #
+--echo # Bug#24962600 WL3634: SIG 11 IN HEAP_RRND AT STORAGE/HEAP/HP_RRND.C
+--echo #
+
+create table t1(a int);
+
+# empty table
+with recursive cte as (select * from t1 union select * from cte)
+ select * from cte;
+insert into t1 values(1),(2);
+# always-false WHERE
+with recursive cte as (select * from t1 where 0 union select * from cte)
+ select * from cte;
+# no matching rows
+with recursive cte as (select * from t1 where a>3 union select * from cte)
+ select * from cte;
+
+drop table t1;
+
+
+--echo #
+--echo # Bug#26501463 WL10792: ASSERTION `!TABLE->HAS_NULL_ROW()' FAILED
+--echo #
+
+CREATE TABLE D (col_int INT);
+
+CREATE TABLE C (
+  col_int2 INT,
+  pk INT NOT NULL,
+  col_int INT,
+  PRIMARY KEY (pk)
+);
+INSERT INTO C VALUES
+(7,1,3),(7,2,3),(5,3,4),(1,4,6),(5,5,2),
+(5,6,9),(4,7,9),(7,8,3),(3,9,0),(5,10,3);
+
+CREATE TABLE BB (
+  pk INT NOT NULL,
+  col_int INT,
+  PRIMARY KEY (pk)
+);
+INSERT INTO BB VALUES (1,0),(2,6),(3,2),(4,5),(5,0);
+
+WITH RECURSIVE cte AS (
+SELECT alias2 . col_int2 AS field1 FROM
+D AS alias1 RIGHT  JOIN
+  ( ( C AS alias2 LEFT  JOIN BB AS alias3
+      ON (( alias3 . pk = alias2 . col_int ) AND ( alias3 . pk = alias2 . pk ) ) ) )
+ON (alias3 . col_int <> alias2 . col_int2 )
+HAVING field1 <= 0
+UNION
+SELECT cte.field1 FROM cte
+)
+SELECT * FROM cte;
+
+DROP TABLE BB,C,D;
+
+--echo #
+--echo # Bug#26556025 ASSERTION `!SELECT_LEX->IS_RECURSIVE() || !TMP_TABLES' FAILED.
+--echo #
+
+SET SQL_BUFFER_RESULT = 1;
+
+WITH RECURSIVE cte AS
+ (SELECT 1 AS n UNION SELECT n+1 FROM cte WHERE n<3)
+SELECT * FROM cte;
+
+WITH RECURSIVE cte AS
+ (SELECT 1 AS n UNION ALL SELECT n+1 FROM cte WHERE n<3)
+SELECT * FROM cte;
+
+SET SQL_BUFFER_RESULT = DEFAULT;
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;
diff --git a/mysql-test/r/with_grant.result b/mysql-test/r/with_grant.result
new file mode 100644
index 00000000000..fd6bf420521
--- /dev/null
+++ b/mysql-test/r/with_grant.result
@@ -0,0 +1,53 @@
+# privileges
+create database mysqltest1;
+use mysqltest1;
+create table t1(pub int, priv int);
+insert into t1 values(1,2);
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+mysqltest1.t1	analyze	status	OK
+CREATE USER user1@localhost;
+GRANT SELECT (pub) ON mysqltest1.t1 TO user1@localhost;
+connect  user1, localhost, user1, ,;
+connection user1;
+use mysqltest1;
+select pub from t1;
+pub
+1
+select priv from t1;
+ERROR 42000: SELECT command denied to user 'user1'@'localhost' for column 'priv' in table 't1'
+select * from (select pub from t1) as dt;
+pub
+1
+explain select * from (select pub from t1) as dt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
+select /*+ merge(dt) */ * from (select priv from t1) as dt;
+ERROR 42000: SELECT command denied to user 'user1'@'localhost' for column 'priv' in table 't1'
+select /*+ no_merge(dt) */ * from (select priv from t1) as dt;
+ERROR 42000: SELECT command denied to user 'user1'@'localhost' for column 'priv' in table 't1'
+explain select * from (select priv from t1) as dt;
+ERROR 42000: SELECT command denied to user 'user1'@'localhost' for column 'priv' in table 't1'
+with qn as (select pub from t1) select * from qn;
+pub
+1
+explain with qn as (select pub from t1) select * from qn;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
+with qn as (select priv from t1) select /*+ merge(qn) */ * from qn;
+ERROR 42000: SELECT command denied to user 'user1'@'localhost' for column 'priv' in table 't1'
+with qn as (select priv from t1) select /*+ no_merge(qn) */ * from qn;
+ERROR 42000: SELECT command denied to user 'user1'@'localhost' for column 'priv' in table 't1'
+explain with qn as (select priv from t1) select * from qn;
+ERROR 42000: SELECT command denied to user 'user1'@'localhost' for column 'priv' in table 't1'
+with qn2 as (with qn as (select pub from t1) select * from qn)
+select * from qn2;
+pub
+1
+with qn2 as (with qn as (select priv from t1) select * from qn)
+select * from qn2;
+ERROR 42000: SELECT command denied to user 'user1'@'localhost' for column 'priv' in table 't1'
+connection default;
+disconnect user1;
+drop user user1@localhost;
+drop database mysqltest1;
diff --git a/mysql-test/r/with_non_recursive.result b/mysql-test/r/with_non_recursive.result
new file mode 100644
index 00000000000..75755675618
--- /dev/null
+++ b/mysql-test/r/with_non_recursive.result
@@ -0,0 +1,1364 @@
+# In-memory tmp tables
+set big_tables=0;
+flush STATUS;
+CREATE TABLE t1(a int, b int, c int);
+INSERT INTO t1 VALUES(NULL,NULL,NULL),(2,3,4);
+WITH qn AS (SELECT a FROM t1)
+SELECT 1 FROM dual;
+1
+1
+# two query names
+WITH qn AS (SELECT a FROM t1), qn2 AS (SELECT b FROM t1)
+SELECT 1 FROM dual;
+1
+1
+# duplicate query names
+WITH qn AS (SELECT a FROM t1), qn AS (SELECT b FROM t1)
+SELECT 1 FROM qn;
+ERROR HY000: Duplicate query name in WITH clause 'qn'
+#  multiple refs
+WITH qn AS (SELECT b AS a FROM t1)
+SELECT qn.a, qn2.a  FROM qn, qn AS qn2;
+a	a
+NULL	NULL
+3	NULL
+NULL	3
+3	3
+WITH qn AS (SELECT b AS a FROM t1),
+qn2 AS (SELECT c FROM t1 WHERE a IS NULL OR a>0)
+SELECT qn.a, qn2.c  FROM qn, qn2;
+a	c
+NULL	NULL
+3	NULL
+NULL	4
+3	4
+# qn2 ref qn:
+WITH qn AS (SELECT 10*a AS a FROM t1),
+qn2 AS (SELECT 3*a FROM qn)
+SELECT * FROM qn2;
+3*a
+NULL
+60
+WITH qn AS (SELECT a FROM t1), qn2 AS (SELECT a FROM qn)
+SELECT * FROM qn2;
+a
+NULL
+2
+WITH qn AS (SELECT b AS a FROM t1),
+qn2 AS (SELECT a FROM qn WHERE a IS NULL OR a>0)
+SELECT qn.a, qn2.a  FROM qn, qn2;
+a	a
+NULL	NULL
+3	NULL
+NULL	3
+3	3
+EXPLAIN WITH qn AS (SELECT b AS a FROM t1),
+qn2 AS (SELECT a FROM qn WHERE a IS NULL OR a>0)
+SELECT qn.a, qn2.a  FROM qn, qn2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
+# forward ref (should error)
+WITH qn2 AS (SELECT a FROM qn WHERE a IS NULL OR a>0),
+qn AS (SELECT b AS a FROM t1)
+SELECT qn2.a  FROM qn2;
+ERROR 42S02: Table 'test.qn' doesn't exist
+WITH qn1 AS (WITH qn3 AS (SELECT * FROM qn2) SELECT * FROM qn3),
+qn2 AS (SELECT 1)
+SELECT * FROM qn1;
+ERROR 42S02: Table 'test.qn2' doesn't exist
+# This is valid; it is to test moving boundaries.
+# When we resolve qn3, resolving qn1 moves the right bound to
+# qn0, but the bound is properly restored so that we can later
+# resolve qn2.
+WITH qn0 AS (SELECT 1), qn1 AS (SELECT * FROM qn0), qn2 AS (SELECT 1), qn3 AS (SELECT 1 FROM qn1, qn2) SELECT 1 FROM qn3;
+1
+1
+# No ref
+explain WITH qn AS (SELECT 1) SELECT 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+WITH qn AS (SELECT 1) SELECT 2;
+2
+2
+# circular ref
+WITH qn2 AS (SELECT a FROM qn WHERE a IS NULL OR a>0),
+qn AS (SELECT b AS a FROM qn2)
+SELECT qn.a  FROM qn;
+ERROR 42S02: Table 'test.qn' doesn't exist
+# recursive
+WITH qn AS (SELECT a FROM qn)
+SELECT qn.a FROM qn;
+ERROR 42S02: Table 'test.qn' doesn't exist
+WITH qn1 AS (SELECT a FROM qn3),
+qn2 AS (SELECT a FROM qn1),
+qn3 AS (SELECT a FROM t1),
+qn4 AS (SELECT a FROM qn2)
+SELECT a FROM qn4;
+ERROR 42S02: Table 'test.qn3' doesn't exist
+# ref from subq
+WITH qn AS (SELECT * FROM t1) SELECT (SELECT max(a) FROM qn);
+(SELECT max(a) FROM qn)
+2
+# QN defined in subq
+SELECT (WITH qn AS (SELECT 10*a AS a FROM t1),
+qn2 AS (SELECT 3*a AS b FROM qn)
+SELECT * FROM qn2 LIMIT 1)
+FROM t1;
+(WITH qn AS (SELECT 10*a AS a FROM t1),
+qn2 AS (SELECT 3*a AS b FROM qn)
+SELECT * FROM qn2 LIMIT 1)
+NULL
+NULL
+SELECT *
+FROM (WITH qn AS (SELECT 10*a AS a FROM t1),
+qn2 AS (SELECT 3*a AS b FROM qn)
+SELECT * FROM qn2)
+AS dt;
+b
+NULL
+60
+# WITH in WITH
+WITH qn AS
+(WITH qn2 AS (SELECT "qn2" AS a FROM t1) SELECT "qn", a FROM qn2)
+SELECT * FROM qn;
+qn	a
+qn	qn2
+qn	qn2
+# outer ref to a table, placed in a QN in a subq (later)
+# QN defined in view
+CREATE VIEW v AS
+WITH qn AS (SELECT 10*a AS a FROM t1),
+qn2 AS (SELECT 3*a AS b FROM qn)
+SELECT * FROM qn2;
+SELECT * FROM v;
+b
+NULL
+60
+DROP VIEW v;
+# CREATE INSERT SELECT
+CREATE TABLE t2
+WITH qn AS (SELECT 10*a AS a FROM t1),
+qn2 AS (SELECT 3*a AS b FROM qn)
+SELECT * FROM qn2;
+SELECT * FROM t2;
+b
+NULL
+60
+INSERT INTO t2
+WITH qn AS (SELECT 10*a AS a FROM t1),
+qn2 AS (SELECT 3*a AS b FROM qn)
+SELECT * FROM qn2;
+SELECT * FROM t2;
+b
+NULL
+60
+NULL
+60
+DROP TABLE t2;
+# Double use of QN in two subqueries.
+explain WITH qn AS (SELECT * FROM t1 LIMIT 10)
+SELECT (SELECT max(a) FROM qn WHERE a=0),
+(SELECT min(b) FROM qn WHERE b=3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+4	SUBQUERY	<derived5>	ALL	NULL	NULL	NULL	NULL	2	Using where
+5	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	
+3	SUBQUERY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	
+WITH qn AS (SELECT * FROM t1 LIMIT 10)
+SELECT (SELECT max(a) FROM qn WHERE a=0),
+(SELECT min(b) FROM qn WHERE b=3);
+(SELECT max(a) FROM qn WHERE a=0)	(SELECT min(b) FROM qn WHERE b=3)
+NULL	3
+# when QN, when table.
+CREATE TABLE qn SELECT "base";
+SELECT * FROM qn;
+base
+base
+WITH qn AS (SELECT "with") SELECT * FROM qn;
+with
+with
+# In a non-recursive WITH, the scope of the QN doesn't extend to its
+# subquery, so "qn" inside AS() is the base table.
+WITH qn AS (SELECT * FROM qn) SELECT * FROM qn;
+base
+base
+# View doesn't look out to external QNs
+CREATE VIEW v AS SELECT * FROM qn;
+SELECT * FROM v;
+base
+base
+WITH qn AS (SELECT "with") SELECT * FROM v;
+base
+base
+WITH qn AS (SELECT * FROM v) SELECT * FROM qn;
+base
+base
+# Even if the base table is temporarily dropped
+DROP TABLE qn;
+WITH qn AS (SELECT "with") SELECT * FROM v;
+ERROR HY000: View 'test.v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
+WITH qn AS (SELECT * FROM v) SELECT * FROM qn;
+ERROR HY000: View 'test.v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
+CREATE TABLE qn SELECT "base" AS a;
+# Neither does SP
+CREATE FUNCTION f() RETURNS varchar(10)
+RETURN (SELECT * FROM qn);
+SELECT f();
+f()
+base
+WITH qn AS (SELECT "with") SELECT f();
+f()
+base
+WITH qn AS (SELECT f()) SELECT * FROM qn;
+f()
+base
+# QN shadows tmp table
+CREATE TEMPORARY TABLE qn SELECT "tmp" AS a;
+SELECT * FROM qn;
+a
+tmp
+WITH qn AS (SELECT "with") SELECT * FROM qn;
+with
+with
+DROP FUNCTION f;
+DROP VIEW v;
+# DT shadows QN:
+WITH qn AS (SELECT "with") SELECT * FROM (SELECT "dt") AS qn;
+dt
+dt
+# QN of subq shadows outer QN
+WITH qn AS (SELECT "outer" AS a)
+SELECT (WITH qn AS (SELECT "inner" AS a) SELECT a FROM qn),
+qn.a
+FROM qn;
+(WITH qn AS (SELECT "inner" AS a) SELECT a FROM qn)	a
+inner	outer
+# Qualified name isn't allowed after WITH:
+WITH test.qn AS (SELECT "with") SELECT * FROM test.qn;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.qn AS (SELECT "with") SELECT * FROM test.qn' at line 1
+# OTOH, db. prefix in FROM doesn't resolve to QN, which is good
+WITH qn AS (SELECT "with") SELECT * FROM qn;
+with
+with
+WITH qn AS (SELECT "with") SELECT * FROM test.qn;
+a
+tmp
+WITH qn AS (SELECT "with" AS a) SELECT a FROM qn;
+a
+with
+WITH qn AS (SELECT "with" AS a) SELECT qn.a FROM qn;
+a
+with
+WITH qn AS (SELECT "with" AS a) SELECT test.qn.a FROM qn;
+a
+with
+WITH qn AS (SELECT "with" AS a) SELECT a FROM test.qn;
+a
+tmp
+WITH qn AS (SELECT "with" AS a) SELECT qn.a FROM test.qn;
+a
+tmp
+WITH qn AS (SELECT "with" AS a) SELECT test.qn.a FROM test.qn;
+a
+tmp
+DROP TEMPORARY TABLE qn;
+WITH qn AS (SELECT "with" AS a) SELECT a FROM test.qn;
+a
+base
+WITH qn AS (SELECT "with" AS a) SELECT qn.a FROM test.qn;
+a
+base
+WITH qn AS (SELECT "with" AS a) SELECT test.qn.a FROM test.qn;
+a
+base
+DROP TABLE qn;
+WITH qn AS (SELECT "with" AS a) SELECT a FROM test.qn;
+ERROR 42S02: Table 'test.qn' doesn't exist
+WITH qn AS (SELECT "with" AS a) SELECT qn.a FROM test.qn;
+ERROR 42S02: Table 'test.qn' doesn't exist
+WITH qn AS (SELECT "with" AS a) SELECT test.qn.a FROM test.qn;
+ERROR 42S02: Table 'test.qn' doesn't exist
+# Unions
+WITH qn AS (SELECT b AS a FROM t1 UNION SELECT b+5 FROM t1),
+qn2 AS (SELECT a FROM qn WHERE a IS NULL OR a>0)
+SELECT qn.a FROM qn
+UNION SELECT qn2.a FROM qn2 WHERE qn2.a>3;
+a
+NULL
+3
+8
+# No double WITH
+WITH qn AS (SELECT "with" AS a)
+WITH qn2 AS (SELECT "with" AS a)
+SELECT a FROM test.qn;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'qn2 AS (SELECT "with" AS a)
+SELECT a FROM test.qn' at line 2
+# with comma
+WITH qn AS (SELECT "with" AS a),
+WITH qn2 AS (SELECT "with" AS a)
+SELECT a FROM test.qn;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'qn2 AS (SELECT "with" AS a)
+SELECT a FROM test.qn' at line 2
+# ORDER BY removed unless there is LIMIT or single table (check "Using filesort")
+explain extended
+WITH qn AS (SELECT a FROM t1 ORDER BY 1)
+SELECT a FROM qn;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+Warnings:
+Note	1003	with qn as (select `test`.`t1`.`a` AS `a` from `test`.`t1` order by 1)select `test`.`t1`.`a` AS `a` from `test`.`t1`
+explain extended
+WITH qn AS (SELECT a FROM t1 ORDER BY 1)
+SELECT qn.a FROM qn, t1 AS t2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using join buffer (flat, BNL join)
+Warnings:
+Note	1003	with qn as (select `test`.`t1`.`a` AS `a` from `test`.`t1` order by 1)select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t1` `t2`
+explain extended
+WITH qn AS (SELECT a FROM t1 ORDER BY 1 LIMIT 10)
+SELECT qn.a FROM qn, t1 AS t2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using join buffer (flat, BNL join)
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using filesort
+Warnings:
+Note	1003	with qn as (select `test`.`t1`.`a` AS `a` from `test`.`t1` order by 1 limit 10)select `qn`.`a` AS `a` from `qn` join `test`.`t1` `t2`
+# FD detection
+WITH qn AS (SELECT a, b FROM t1)
+SELECT b FROM qn GROUP BY a;
+b
+NULL
+3
+WITH qn AS (SELECT a, b FROM t1 WHERE a=b)
+SELECT b FROM qn GROUP BY a;
+b
+WITH qn AS (SELECT a, sum(b) AS s FROM t1 GROUP BY a)
+SELECT s FROM qn GROUP BY a;
+s
+NULL
+3
+# CTEs work if used in SET
+SET @myvar=
+(WITH qn AS (SELECT a, sum(b) AS s FROM t1 GROUP BY a)
+SELECT s FROM qn GROUP BY a HAVING s IS NOT NULL);
+SELECT @myvar;
+@myvar
+3
+# CTE works with semijoin
+explain WITH cte AS (SELECT * FROM t1 AS t2 LIMIT 1)
+SELECT * FROM t1 WHERE t1.a IN (SELECT a+0 FROM cte);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	8	func	1	Using where
+3	MATERIALIZED	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
+2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	2	
+WITH cte AS (SELECT * FROM t1 AS t2 LIMIT 1)
+SELECT * FROM t1 WHERE t1.a IN (SELECT a+0 FROM cte);
+a	b	c
+explain WITH cte AS (SELECT * FROM t1 AS t2)
+SELECT * FROM t1 WHERE t1.a IN (SELECT a+0 FROM cte);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	8	func	1	Using where
+3	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	
+WITH cte AS (SELECT * FROM t1 AS t2)
+SELECT * FROM t1 WHERE t1.a IN (SELECT a+0 FROM cte);
+a	b	c
+2	3	4
+# Column names
+# empty list
+WITH qn () AS (SELECT 1) SELECT * FROM qn, qn qn1;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') AS (SELECT 1) SELECT * FROM qn, qn qn1' at line 1
+# Materialization
+WITH qn (foo, bar) AS (SELECT 1) SELECT * FROM qn, qn qn1;
+ERROR HY000: WITH column list and SELECT field list have different column counts
+explain WITH qn (foo, bar) AS (SELECT 1, 2 FROM t1 LIMIT 2) SELECT * 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	<derived3>	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (flat, BNL join)
+3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	
+WITH qn (foo, bar) AS (SELECT 1, 2 FROM t1 LIMIT 2) SELECT * FROM qn, qn qn1;
+foo	bar	foo	bar
+1	2	1	2
+1	2	1	2
+1	2	1	2
+1	2	1	2
+WITH qn (foo, bar) AS (SELECT 1 AS col, 2 AS coll FROM t1 LIMIT 2) SELECT * FROM qn, qn qn1;
+foo	bar	foo	bar
+1	2	1	2
+1	2	1	2
+1	2	1	2
+1	2	1	2
+WITH qn (foo, bar) AS (SELECT a, b FROM t1 LIMIT 2) SELECT qn.bar,foo FROM qn;
+bar	foo
+NULL	NULL
+3	2
+CREATE TABLE t3
+WITH qn (foo, bar) AS (SELECT a, b FROM t1 LIMIT 2) SELECT bar,foo FROM qn;
+DESC t3;
+Field	Type	Null	Key	Default	Extra
+bar	int(11)	YES		NULL	
+foo	int(11)	YES		NULL	
+DROP TABLE t3;
+# Merge
+WITH qn (foo, bar) AS (SELECT 1 FROM t1) SELECT * FROM qn, qn qn1;
+ERROR HY000: WITH column list and SELECT field list have different column counts
+WITH qn (foo, bar) AS (SELECT 1, 2 FROM t1) SELECT * FROM qn, qn qn1;
+foo	bar	foo	bar
+1	2	1	2
+1	2	1	2
+1	2	1	2
+1	2	1	2
+explain WITH qn (foo, bar) AS (SELECT 1, 2 FROM t1) SELECT * FROM qn, qn qn1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (flat, BNL join)
+WITH qn (foo, bar) AS (SELECT 1 AS col, 2 AS coll FROM t1) SELECT * FROM qn, qn qn1;
+foo	bar	foo	bar
+1	2	1	2
+1	2	1	2
+1	2	1	2
+1	2	1	2
+WITH qn (foo, bar) AS (SELECT a, b FROM t1) SELECT qn1.bar,foo FROM qn qn1;
+bar	foo
+NULL	NULL
+3	2
+CREATE TABLE t3
+WITH qn (foo, bar) AS (SELECT a, b FROM t1) SELECT bar,foo FROM qn;
+DESC t3;
+Field	Type	Null	Key	Default	Extra
+bar	int(11)	YES		NULL	
+foo	int(11)	YES		NULL	
+DROP TABLE t3;
+# Disambiguates same-name expressions
+WITH qn AS (SELECT 1,1) SELECT * FROM qn;
+ERROR 42S21: Duplicate column name '1'
+WITH qn (foo, bar) AS (SELECT 1,1) SELECT * FROM qn;
+foo	bar
+1	1
+WITH qn AS (SELECT 1,1 FROM t1) SELECT * FROM qn;
+ERROR 42S21: Duplicate column name '1'
+WITH qn (foo, bar) AS (SELECT 1,1 FROM t1) SELECT * FROM qn;
+foo	bar
+1	1
+1	1
+# Duplicate names are forbidden
+WITH qn (foo, foo) AS (SELECT 1,2) SELECT * FROM qn;
+ERROR 42S21: Duplicate column name 'foo'
+# Column names for QN/DT are printed
+CREATE VIEW v1 AS
+WITH qn (foo, bar) AS (SELECT 1,1) SELECT * FROM qn;
+SHOW CREATE VIEW v1;
+View	Create View	character_set_client	collation_connection
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with qn as (select 1 AS `foo`,1 AS `bar`)select `qn`.`foo` AS `foo`,`qn`.`bar` AS `bar` from `qn`	latin1	latin1_swedish_ci
+SHOW fields FROM v1;
+Field	Type	Null	Key	Default	Extra
+foo	int(1)	NO		0	
+bar	int(1)	NO		0	
+SELECT * FROM v1;
+foo	bar
+1	1
+DROP VIEW v1;
+CREATE VIEW v1 AS
+WITH qn (foo, bar) AS (SELECT 1,1 FROM t1) SELECT * FROM qn;
+SHOW CREATE VIEW v1;
+View	Create View	character_set_client	collation_connection
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with qn as (select 1 AS `foo`,1 AS `bar` from `t1`)select `qn`.`foo` AS `foo`,`qn`.`bar` AS `bar` from `qn`	latin1	latin1_swedish_ci
+SELECT * FROM v1;
+foo	bar
+1	1
+1	1
+DROP VIEW v1;
+CREATE VIEW v1 (bar) AS
+SELECT 1, 2 FROM t1;
+ERROR HY000: View's SELECT and view's field list have different column counts
+DROP TABLE t1;
+# Prove that a materialized QN is shared among all references:
+CREATE TABLE t1(a int);
+INSERT INTO t1 VALUES(1),(2),(3),(4);
+flush STATUS;
+WITH qn AS (SELECT 123 AS col)
+SELECT * FROM qn;
+col
+123
+SHOW STATUS LIKE "handler_write";
+Variable_name	Value
+Handler_write	0
+flush STATUS;
+WITH qn AS (SELECT 123 AS col)
+SELECT * FROM qn, qn AS qn1;
+col	col
+123	123
+SHOW STATUS LIKE "handler_write";
+Variable_name	Value
+Handler_write	0
+CREATE VIEW qn AS SELECT 123 AS col;
+flush STATUS;
+SELECT * FROM qn, qn AS qn1;
+col	col
+123	123
+SHOW STATUS LIKE "handler_write";
+Variable_name	Value
+Handler_write	0
+DROP VIEW qn;
+DROP TABLE t1;
+# Printing of WITH to DD for view
+CREATE VIEW v AS
+SELECT (WITH qn AS (SELECT "with") SELECT * FROM qn) AS scal_subq
+FROM dual;
+SHOW CREATE VIEW v;
+View	Create View	character_set_client	collation_connection
+v	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select (with qn as (select 'with' AS `with`)select `qn`.`with` from `qn`) AS `scal_subq`	latin1	latin1_swedish_ci
+SELECT * FROM v;
+scal_subq
+with
+DROP VIEW v;
+CREATE VIEW v AS SELECT * FROM (WITH qn AS (SELECT "with") SELECT * FROM qn) AS dt;
+SHOW CREATE VIEW v;
+View	Create View	character_set_client	collation_connection
+v	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `dt`.`with` AS `with` from (with qn as (select 'with' AS `with`)select `qn`.`with` AS `with` from `qn`) `dt`	latin1	latin1_swedish_ci
+SELECT * FROM v;
+with
+with
+DROP VIEW v;
+CREATE TABLE t1 (a int);
+explain WITH qne AS (SELECT a FROM t1),
+qnm AS (SELECT a FROM t1),
+qnea AS (SELECT a FROM t1),
+qnma AS (SELECT a FROM t1)
+SELECT
+qne.a,qnm.a,alias1.a,alias2.a
+FROM qne, qnm, qnea AS alias1, qnma AS alias2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+DROP TABLE t1;
+# Automatic index creation if materialized
+CREATE TABLE t1 (a int);
+INSERT INTO t1(a) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(0);
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+# EXPLAIN should not fill the tmp table
+flush STATUS;
+# Should use auto_key0 and ref access.
+explain WITH tt AS (SELECT * FROM t1)
+SELECT /*+ no_merge(tt) */ tt.a
+FROM t1 STRAIGHT_JOIN tt WHERE t1.a=tt.a
+LIMIT 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	#
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	#
+SHOW STATUS LIKE "handler_write";
+Variable_name	Value
+Handler_write	0
+flush STATUS;
+WITH tt AS (SELECT * FROM t1)
+SELECT /*+ no_merge(tt) */ tt.a
+FROM t1 STRAIGHT_JOIN tt WHERE t1.a=tt.a
+LIMIT 1;
+a
+1
+SHOW STATUS LIKE "handler_write";
+Variable_name	Value
+Handler_write	0
+# With two references
+WITH tt AS (SELECT * FROM t1)
+SELECT /*+ no_merge(tt) no_merge(tt_)*/ tt.a
+FROM t1 STRAIGHT_JOIN tt STRAIGHT_JOIN tt AS tt_
+WHERE t1.a=tt.a AND tt.a=tt_.a
+LIMIT 1;
+a
+1
+# One merged, one materialized: index creation on the second
+# should of course ignore the first
+WITH q AS (SELECT * FROM t1)
+SELECT /*+ merge(q) no_merge(q1) */ * FROM q, q q1 WHERE q.a=1 AND q1.a=2;
+a	a
+1	2
+DROP TABLE t1;
+# Must not create more than 64 indexes.
+(SELECT max(c1) FROM qn WHERE qn.c1=1)	(SELECT max(c2) FROM qn WHERE qn.c2=1)	(SELECT max(c3) FROM qn WHERE qn.c3=1)	(SELECT max(c4) FROM qn WHERE qn.c4=1)	(SELECT max(c5) FROM qn WHERE qn.c5=1)	(SELECT max(c6) FROM qn WHERE qn.c6=1)	(SELECT max(c7) FROM qn WHERE qn.c7=1)	(SELECT max(c8) FROM qn WHERE qn.c8=1)	(SELECT max(c9) FROM qn WHERE qn.c9=1)	(SELECT max(c10) FROM qn WHERE qn.c10=1)	(SELECT max(c11) FROM qn WHERE qn.c11=1)	(SELECT max(c12) FROM qn WHERE qn.c12=1)	(SELECT max(c13) FROM qn WHERE qn.c13=1)	(SELECT max(c14) FROM qn WHERE qn.c14=1)	(SELECT max(c15) FROM qn WHERE qn.c15=1)	(SELECT max(c16) FROM qn WHERE qn.c16=1)	(SELECT max(c17) FROM qn WHERE qn.c17=1)	(SELECT max(c18) FROM qn WHERE qn.c18=1)	(SELECT max(c19) FROM qn WHERE qn.c19=1)	(SELECT max(c20) FROM qn WHERE qn.c20=1)	(SELECT max(c21) FROM qn WHERE qn.c21=1)	(SELECT max(c22) FROM qn WHERE qn.c22=1)	(SELECT max(c23) FROM qn WHERE qn.c23=1)	(SELECT max(c24) FROM qn WHERE qn.c24=1)	(SELECT max(c25) FROM qn WHERE qn.c25=1)	(SELECT max(c26) FROM qn WHERE qn.c26=1)	(SELECT max(c27) FROM qn WHERE qn.c27=1)	(SELECT max(c28) FROM qn WHERE qn.c28=1)	(SELECT max(c29) FROM qn WHERE qn.c29=1)	(SELECT max(c30) FROM qn WHERE qn.c30=1)	(SELECT max(c31) FROM qn WHERE qn.c31=1)	(SELECT max(c32) FROM qn WHERE qn.c32=1)	(SELECT max(c33) FROM qn WHERE qn.c33=1)	(SELECT max(c34) FROM qn WHERE qn.c34=1)	(SELECT max(c35) FROM qn WHERE qn.c35=1)	(SELECT max(c36) FROM qn WHERE qn.c36=1)	(SELECT max(c37) FROM qn WHERE qn.c37=1)	(SELECT max(c38) FROM qn WHERE qn.c38=1)	(SELECT max(c39) FROM qn WHERE qn.c39=1)	(SELECT max(c40) FROM qn WHERE qn.c40=1)	(SELECT max(c41) FROM qn WHERE qn.c41=1)	(SELECT max(c42) FROM qn WHERE qn.c42=1)	(SELECT max(c43) FROM qn WHERE qn.c43=1)	(SELECT max(c44) FROM qn WHERE qn.c44=1)	(SELECT max(c45) FROM qn WHERE qn.c45=1)	(SELECT max(c46) FROM qn WHERE qn.c46=1)	(SELECT max(c47) FROM qn WHERE qn.c47=1)	(SELECT max(c48) FROM qn WHERE qn.c48=1)	(SELECT max(c49) FROM qn WHERE qn.c49=1)	(SELECT max(c50) FROM qn WHERE qn.c50=1)	(SELECT max(c51) FROM qn WHERE qn.c51=1)	(SELECT max(c52) FROM qn WHERE qn.c52=1)	(SELECT max(c53) FROM qn WHERE qn.c53=1)	(SELECT max(c54) FROM qn WHERE qn.c54=1)	(SELECT max(c55) FROM qn WHERE qn.c55=1)	(SELECT max(c56) FROM qn WHERE qn.c56=1)	(SELECT max(c57) FROM qn WHERE qn.c57=1)	(SELECT max(c58) FROM qn WHERE qn.c58=1)	(SELECT max(c59) FROM qn WHERE qn.c59=1)	(SELECT max(c60) FROM qn WHERE qn.c60=1)	(SELECT max(c61) FROM qn WHERE qn.c61=1)	(SELECT max(c62) FROM qn WHERE qn.c62=1)	(SELECT max(c63) FROM qn WHERE qn.c63=1)	(SELECT max(c64) FROM qn WHERE qn.c64=1)	(SELECT max(c65) FROM qn WHERE qn.c65=1)	(SELECT max(c66) FROM qn WHERE qn.c66=1)	(SELECT max(c67) FROM qn WHERE qn.c67=1)	(SELECT max(c68) FROM qn WHERE qn.c68=1)	(SELECT max(c69) FROM qn WHERE qn.c69=1)	(SELECT max(c70) FROM qn WHERE qn.c70=1)	(SELECT max(c71) FROM qn WHERE qn.c71=1)	(SELECT max(c72) FROM qn WHERE qn.c72=1)	(SELECT max(c73) FROM qn WHERE qn.c73=1)	(SELECT max(c74) FROM qn WHERE qn.c74=1)	(SELECT max(c75) FROM qn WHERE qn.c75=1)	(SELECT max(c76) FROM qn WHERE qn.c76=1)	(SELECT max(c77) FROM qn WHERE qn.c77=1)	(SELECT max(c78) FROM qn WHERE qn.c78=1)	(SELECT max(c79) FROM qn WHERE qn.c79=1)	(SELECT max(c80) FROM qn WHERE qn.c80=1)	(SELECT max(c81) FROM qn WHERE qn.c81=1)	(SELECT max(c82) FROM qn WHERE qn.c82=1)	(SELECT max(c83) FROM qn WHERE qn.c83=1)	(SELECT max(c84) FROM qn WHERE qn.c84=1)	(SELECT max(c85) FROM qn WHERE qn.c85=1)	(SELECT max(c86) FROM qn WHERE qn.c86=1)	(SELECT max(c87) FROM qn WHERE qn.c87=1)	(SELECT max(c88) FROM qn WHERE qn.c88=1)	(SELECT max(c89) FROM qn WHERE qn.c89=1)	(SELECT max(c90) FROM qn WHERE qn.c90=1)	(SELECT max(c91) FROM qn WHERE qn.c91=1)	(SELECT max(c92) FROM qn WHERE qn.c92=1)	(SELECT max(c93) FROM qn WHERE qn.c93=1)	(SELECT max(c94) FROM qn WHERE qn.c94=1)	(SELECT max(c95) FROM qn WHERE qn.c95=1)	(SELECT max(c96) FROM qn WHERE qn.c96=1)	(SELECT max(c97) FROM qn WHERE qn.c97=1)	(SELECT max(c98) FROM qn WHERE qn.c98=1)	(SELECT max(c99) FROM qn WHERE qn.c99=1)	(SELECT max(c100) FROM qn WHERE qn.c100=1)
+NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
+DROP TABLE t;
+# Choice between two auto_key:
+CREATE TABLE t1(a int, b int);
+INSERT INTO t1 VALUES (NULL, 6), (NULL, 10);
+# Test the covering key; note that MEMORY doesn't use a
+# covering key (always reads the "data file"). But InnoDB does.
+EXPLAIN WITH t2 AS
+(SELECT * FROM t1)
+SELECT /*+ no_merge(t2) */ * FROM t2
+WHERE (a = a OR b <= 6) AND (a IS NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+WITH t2 AS
+(SELECT * FROM t1)
+SELECT /*+ no_merge(t2) */ * FROM t2
+WHERE (a = a OR b <= 6) AND (a IS NULL);
+a	b
+NULL	6
+DROP TABLE t1;
+# QN referencing view of same name isn't a "recursive view",
+# shouldn't cause ER_VIEW_RECURSIVE
+CREATE VIEW v1 AS SELECT "with";
+WITH v1 AS (SELECT * FROM v1) SELECT * FROM v1;
+with
+with
+DROP VIEW v1;
+# QN inside view
+CREATE VIEW v1 AS
+WITH qn AS (SELECT 1 AS col) SELECT * FROM qn;
+SELECT * FROM v1;
+col
+1
+DROP VIEW v1;
+CREATE TABLE t1(a int, b int);
+# Alas merge hints are ignored in views (filed Bug#23017428)
+CREATE VIEW v1 AS
+WITH qn AS (SELECT a FROM t1),
+qn2 AS (SELECT b FROM t1)
+SELECT /*+ merge(qn) no_merge(qn2) */ qn.a,qn2.b FROM qn, qn2;
+explain SELECT * FROM v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+DROP VIEW v1;
+# Materializing view doesn't impose materializing query name
+CREATE algorithm=temptable VIEW v1 AS
+WITH qn AS (SELECT a FROM t1)
+SELECT qn.a  FROM qn;
+explain SELECT * FROM v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	0	const row not found
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+DROP VIEW v1;
+DROP TABLE t1;
+# CTE referenced four times, including in subqueries in other CTEs
+CREATE TABLE sales_days(day_of_sale DATE, amount INT);
+INSERT INTO sales_days VALUES
+('2015-01-02', 100), ('2015-01-05', 200),
+('2015-02-02', 10),  ('2015-02-10', 100),
+('2015-03-02', 10),  ('2015-03-18', 1);
+WITH
+# FIRST CTE: one ROW per MONTH, WITH amount sold ON ALL days OF MONTH
+sales_by_month(MONTH,total) AS
+(SELECT MONTH(day_of_sale), sum(amount) FROM sales_days
+WHERE year(day_of_sale)=2015
+GROUP BY MONTH(day_of_sale)),
+# SECOND CTE: best MONTH
+best_month(MONTH, total, award) AS
+(SELECT MONTH, total, "best" FROM sales_by_month
+WHERE total=(SELECT max(total) FROM sales_by_month)),
+# 3rd CTE: worst MONTH
+worst_month(MONTH, total, award) AS
+(SELECT MONTH, total, "worst" FROM sales_by_month
+WHERE total=(SELECT min(total) FROM sales_by_month))
+# Now SHOW results:
+SELECT * FROM best_month UNION ALL SELECT * FROM worst_month;
+MONTH	total	award
+1	300	best
+3	11	worst
+DROP TABLE sales_days;
+WITH qn AS (SELECT 1) SELECT * FROM qn, qn qn1;
+1	1
+1	1
+CREATE TABLE t1(a int);
+INSERT INTO t1 VALUES(1),(2);
+WITH qn(a) AS (SELECT 1 FROM t1 LIMIT 2)
+SELECT * FROM qn WHERE qn.a=(SELECT * FROM qn qn1 LIMIT 1) UNION SELECT 2;
+a
+1
+2
+DROP TABLE t1;
+SHOW STATUS LIKE 'Created_tmp_disk_tables';
+Variable_name	Value
+Created_tmp_disk_tables	0
+# On-disk tmp tables
+set big_tables=1;
+flush STATUS;
+CREATE TABLE t1(a int, b int, c int);
+INSERT INTO t1 VALUES(NULL,NULL,NULL),(2,3,4);
+WITH qn AS (SELECT a FROM t1)
+SELECT 1 FROM dual;
+1
+1
+# two query names
+WITH qn AS (SELECT a FROM t1), qn2 AS (SELECT b FROM t1)
+SELECT 1 FROM dual;
+1
+1
+# duplicate query names
+WITH qn AS (SELECT a FROM t1), qn AS (SELECT b FROM t1)
+SELECT 1 FROM qn;
+ERROR HY000: Duplicate query name in WITH clause 'qn'
+#  multiple refs
+WITH qn AS (SELECT b AS a FROM t1)
+SELECT qn.a, qn2.a  FROM qn, qn AS qn2;
+a	a
+NULL	NULL
+3	NULL
+NULL	3
+3	3
+WITH qn AS (SELECT b AS a FROM t1),
+qn2 AS (SELECT c FROM t1 WHERE a IS NULL OR a>0)
+SELECT qn.a, qn2.c  FROM qn, qn2;
+a	c
+NULL	NULL
+3	NULL
+NULL	4
+3	4
+# qn2 ref qn:
+WITH qn AS (SELECT 10*a AS a FROM t1),
+qn2 AS (SELECT 3*a FROM qn)
+SELECT * FROM qn2;
+3*a
+NULL
+60
+WITH qn AS (SELECT a FROM t1), qn2 AS (SELECT a FROM qn)
+SELECT * FROM qn2;
+a
+NULL
+2
+WITH qn AS (SELECT b AS a FROM t1),
+qn2 AS (SELECT a FROM qn WHERE a IS NULL OR a>0)
+SELECT qn.a, qn2.a  FROM qn, qn2;
+a	a
+NULL	NULL
+3	NULL
+NULL	3
+3	3
+EXPLAIN WITH qn AS (SELECT b AS a FROM t1),
+qn2 AS (SELECT a FROM qn WHERE a IS NULL OR a>0)
+SELECT qn.a, qn2.a  FROM qn, qn2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (flat, BNL join)
+# forward ref (should error)
+WITH qn2 AS (SELECT a FROM qn WHERE a IS NULL OR a>0),
+qn AS (SELECT b AS a FROM t1)
+SELECT qn2.a  FROM qn2;
+ERROR 42S02: Table 'test.qn' doesn't exist
+WITH qn1 AS (WITH qn3 AS (SELECT * FROM qn2) SELECT * FROM qn3),
+qn2 AS (SELECT 1)
+SELECT * FROM qn1;
+ERROR 42S02: Table 'test.qn2' doesn't exist
+# This is valid; it is to test moving boundaries.
+# When we resolve qn3, resolving qn1 moves the right bound to
+# qn0, but the bound is properly restored so that we can later
+# resolve qn2.
+WITH qn0 AS (SELECT 1), qn1 AS (SELECT * FROM qn0), qn2 AS (SELECT 1), qn3 AS (SELECT 1 FROM qn1, qn2) SELECT 1 FROM qn3;
+1
+1
+# No ref
+explain WITH qn AS (SELECT 1) SELECT 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+WITH qn AS (SELECT 1) SELECT 2;
+2
+2
+# circular ref
+WITH qn2 AS (SELECT a FROM qn WHERE a IS NULL OR a>0),
+qn AS (SELECT b AS a FROM qn2)
+SELECT qn.a  FROM qn;
+ERROR 42S02: Table 'test.qn' doesn't exist
+# recursive
+WITH qn AS (SELECT a FROM qn)
+SELECT qn.a FROM qn;
+ERROR 42S02: Table 'test.qn' doesn't exist
+WITH qn1 AS (SELECT a FROM qn3),
+qn2 AS (SELECT a FROM qn1),
+qn3 AS (SELECT a FROM t1),
+qn4 AS (SELECT a FROM qn2)
+SELECT a FROM qn4;
+ERROR 42S02: Table 'test.qn3' doesn't exist
+# ref from subq
+WITH qn AS (SELECT * FROM t1) SELECT (SELECT max(a) FROM qn);
+(SELECT max(a) FROM qn)
+2
+# QN defined in subq
+SELECT (WITH qn AS (SELECT 10*a AS a FROM t1),
+qn2 AS (SELECT 3*a AS b FROM qn)
+SELECT * FROM qn2 LIMIT 1)
+FROM t1;
+(WITH qn AS (SELECT 10*a AS a FROM t1),
+qn2 AS (SELECT 3*a AS b FROM qn)
+SELECT * FROM qn2 LIMIT 1)
+NULL
+NULL
+SELECT *
+FROM (WITH qn AS (SELECT 10*a AS a FROM t1),
+qn2 AS (SELECT 3*a AS b FROM qn)
+SELECT * FROM qn2)
+AS dt;
+b
+NULL
+60
+# WITH in WITH
+WITH qn AS
+(WITH qn2 AS (SELECT "qn2" AS a FROM t1) SELECT "qn", a FROM qn2)
+SELECT * FROM qn;
+qn	a
+qn	qn2
+qn	qn2
+# outer ref to a table, placed in a QN in a subq (later)
+# QN defined in view
+CREATE VIEW v AS
+WITH qn AS (SELECT 10*a AS a FROM t1),
+qn2 AS (SELECT 3*a AS b FROM qn)
+SELECT * FROM qn2;
+SELECT * FROM v;
+b
+NULL
+60
+DROP VIEW v;
+# CREATE INSERT SELECT
+CREATE TABLE t2
+WITH qn AS (SELECT 10*a AS a FROM t1),
+qn2 AS (SELECT 3*a AS b FROM qn)
+SELECT * FROM qn2;
+SELECT * FROM t2;
+b
+NULL
+60
+INSERT INTO t2
+WITH qn AS (SELECT 10*a AS a FROM t1),
+qn2 AS (SELECT 3*a AS b FROM qn)
+SELECT * FROM qn2;
+SELECT * FROM t2;
+b
+NULL
+60
+NULL
+60
+DROP TABLE t2;
+# Double use of QN in two subqueries.
+explain WITH qn AS (SELECT * FROM t1 LIMIT 10)
+SELECT (SELECT max(a) FROM qn WHERE a=0),
+(SELECT min(b) FROM qn WHERE b=3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+4	SUBQUERY	<derived5>	ALL	NULL	NULL	NULL	NULL	2	Using where
+5	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	
+3	SUBQUERY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	
+WITH qn AS (SELECT * FROM t1 LIMIT 10)
+SELECT (SELECT max(a) FROM qn WHERE a=0),
+(SELECT min(b) FROM qn WHERE b=3);
+(SELECT max(a) FROM qn WHERE a=0)	(SELECT min(b) FROM qn WHERE b=3)
+NULL	3
+# when QN, when table.
+CREATE TABLE qn SELECT "base";
+SELECT * FROM qn;
+base
+base
+WITH qn AS (SELECT "with") SELECT * FROM qn;
+with
+with
+# In a non-recursive WITH, the scope of the QN doesn't extend to its
+# subquery, so "qn" inside AS() is the base table.
+WITH qn AS (SELECT * FROM qn) SELECT * FROM qn;
+base
+base
+# View doesn't look out to external QNs
+CREATE VIEW v AS SELECT * FROM qn;
+SELECT * FROM v;
+base
+base
+WITH qn AS (SELECT "with") SELECT * FROM v;
+base
+base
+WITH qn AS (SELECT * FROM v) SELECT * FROM qn;
+base
+base
+# Even if the base table is temporarily dropped
+DROP TABLE qn;
+WITH qn AS (SELECT "with") SELECT * FROM v;
+ERROR HY000: View 'test.v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
+WITH qn AS (SELECT * FROM v) SELECT * FROM qn;
+ERROR HY000: View 'test.v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
+CREATE TABLE qn SELECT "base" AS a;
+# Neither does SP
+CREATE FUNCTION f() RETURNS varchar(10)
+RETURN (SELECT * FROM qn);
+SELECT f();
+f()
+base
+WITH qn AS (SELECT "with") SELECT f();
+f()
+base
+WITH qn AS (SELECT f()) SELECT * FROM qn;
+f()
+base
+# QN shadows tmp table
+CREATE TEMPORARY TABLE qn SELECT "tmp" AS a;
+SELECT * FROM qn;
+a
+tmp
+WITH qn AS (SELECT "with") SELECT * FROM qn;
+with
+with
+DROP FUNCTION f;
+DROP VIEW v;
+# DT shadows QN:
+WITH qn AS (SELECT "with") SELECT * FROM (SELECT "dt") AS qn;
+dt
+dt
+# QN of subq shadows outer QN
+WITH qn AS (SELECT "outer" AS a)
+SELECT (WITH qn AS (SELECT "inner" AS a) SELECT a FROM qn),
+qn.a
+FROM qn;
+(WITH qn AS (SELECT "inner" AS a) SELECT a FROM qn)	a
+inner	outer
+# Qualified name isn't allowed after WITH:
+WITH test.qn AS (SELECT "with") SELECT * FROM test.qn;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.qn AS (SELECT "with") SELECT * FROM test.qn' at line 1
+# OTOH, db. prefix in FROM doesn't resolve to QN, which is good
+WITH qn AS (SELECT "with") SELECT * FROM qn;
+with
+with
+WITH qn AS (SELECT "with") SELECT * FROM test.qn;
+a
+tmp
+WITH qn AS (SELECT "with" AS a) SELECT a FROM qn;
+a
+with
+WITH qn AS (SELECT "with" AS a) SELECT qn.a FROM qn;
+a
+with
+WITH qn AS (SELECT "with" AS a) SELECT test.qn.a FROM qn;
+a
+with
+WITH qn AS (SELECT "with" AS a) SELECT a FROM test.qn;
+a
+tmp
+WITH qn AS (SELECT "with" AS a) SELECT qn.a FROM test.qn;
+a
+tmp
+WITH qn AS (SELECT "with" AS a) SELECT test.qn.a FROM test.qn;
+a
+tmp
+DROP TEMPORARY TABLE qn;
+WITH qn AS (SELECT "with" AS a) SELECT a FROM test.qn;
+a
+base
+WITH qn AS (SELECT "with" AS a) SELECT qn.a FROM test.qn;
+a
+base
+WITH qn AS (SELECT "with" AS a) SELECT test.qn.a FROM test.qn;
+a
+base
+DROP TABLE qn;
+WITH qn AS (SELECT "with" AS a) SELECT a FROM test.qn;
+ERROR 42S02: Table 'test.qn' doesn't exist
+WITH qn AS (SELECT "with" AS a) SELECT qn.a FROM test.qn;
+ERROR 42S02: Table 'test.qn' doesn't exist
+WITH qn AS (SELECT "with" AS a) SELECT test.qn.a FROM test.qn;
+ERROR 42S02: Table 'test.qn' doesn't exist
+# Unions
+WITH qn AS (SELECT b AS a FROM t1 UNION SELECT b+5 FROM t1),
+qn2 AS (SELECT a FROM qn WHERE a IS NULL OR a>0)
+SELECT qn.a FROM qn
+UNION SELECT qn2.a FROM qn2 WHERE qn2.a>3;
+a
+NULL
+3
+8
+# No double WITH
+WITH qn AS (SELECT "with" AS a)
+WITH qn2 AS (SELECT "with" AS a)
+SELECT a FROM test.qn;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'qn2 AS (SELECT "with" AS a)
+SELECT a FROM test.qn' at line 2
+# with comma
+WITH qn AS (SELECT "with" AS a),
+WITH qn2 AS (SELECT "with" AS a)
+SELECT a FROM test.qn;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'qn2 AS (SELECT "with" AS a)
+SELECT a FROM test.qn' at line 2
+# ORDER BY removed unless there is LIMIT or single table (check "Using filesort")
+explain extended
+WITH qn AS (SELECT a FROM t1 ORDER BY 1)
+SELECT a FROM qn;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+Warnings:
+Note	1003	with qn as (select `test`.`t1`.`a` AS `a` from `test`.`t1` order by 1)select `test`.`t1`.`a` AS `a` from `test`.`t1`
+explain extended
+WITH qn AS (SELECT a FROM t1 ORDER BY 1)
+SELECT qn.a FROM qn, t1 AS t2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using join buffer (flat, BNL join)
+Warnings:
+Note	1003	with qn as (select `test`.`t1`.`a` AS `a` from `test`.`t1` order by 1)select `test`.`t1`.`a` AS `a` from `test`.`t1` join `test`.`t1` `t2`
+explain extended
+WITH qn AS (SELECT a FROM t1 ORDER BY 1 LIMIT 10)
+SELECT qn.a FROM qn, t1 AS t2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using join buffer (flat, BNL join)
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using filesort
+Warnings:
+Note	1003	with qn as (select `test`.`t1`.`a` AS `a` from `test`.`t1` order by 1 limit 10)select `qn`.`a` AS `a` from `qn` join `test`.`t1` `t2`
+# FD detection
+WITH qn AS (SELECT a, b FROM t1)
+SELECT b FROM qn GROUP BY a;
+b
+NULL
+3
+WITH qn AS (SELECT a, b FROM t1 WHERE a=b)
+SELECT b FROM qn GROUP BY a;
+b
+WITH qn AS (SELECT a, sum(b) AS s FROM t1 GROUP BY a)
+SELECT s FROM qn GROUP BY a;
+s
+NULL
+3
+# CTEs work if used in SET
+SET @myvar=
+(WITH qn AS (SELECT a, sum(b) AS s FROM t1 GROUP BY a)
+SELECT s FROM qn GROUP BY a HAVING s IS NOT NULL);
+SELECT @myvar;
+@myvar
+3
+# CTE works with semijoin
+explain WITH cte AS (SELECT * FROM t1 AS t2 LIMIT 1)
+SELECT * FROM t1 WHERE t1.a IN (SELECT a+0 FROM cte);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	8	func	1	Using where
+3	MATERIALIZED	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
+2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	2	
+WITH cte AS (SELECT * FROM t1 AS t2 LIMIT 1)
+SELECT * FROM t1 WHERE t1.a IN (SELECT a+0 FROM cte);
+a	b	c
+explain WITH cte AS (SELECT * FROM t1 AS t2)
+SELECT * FROM t1 WHERE t1.a IN (SELECT a+0 FROM cte);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	<subquery3>	eq_ref	distinct_key	distinct_key	8	func	1	Using where
+3	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	2	
+WITH cte AS (SELECT * FROM t1 AS t2)
+SELECT * FROM t1 WHERE t1.a IN (SELECT a+0 FROM cte);
+a	b	c
+2	3	4
+# Column names
+# empty list
+WITH qn () AS (SELECT 1) SELECT * FROM qn, qn qn1;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') AS (SELECT 1) SELECT * FROM qn, qn qn1' at line 1
+# Materialization
+WITH qn (foo, bar) AS (SELECT 1) SELECT * FROM qn, qn qn1;
+ERROR HY000: WITH column list and SELECT field list have different column counts
+explain WITH qn (foo, bar) AS (SELECT 1, 2 FROM t1 LIMIT 2) SELECT * 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	<derived3>	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (flat, BNL join)
+3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	
+WITH qn (foo, bar) AS (SELECT 1, 2 FROM t1 LIMIT 2) SELECT * FROM qn, qn qn1;
+foo	bar	foo	bar
+1	2	1	2
+1	2	1	2
+1	2	1	2
+1	2	1	2
+WITH qn (foo, bar) AS (SELECT 1 AS col, 2 AS coll FROM t1 LIMIT 2) SELECT * FROM qn, qn qn1;
+foo	bar	foo	bar
+1	2	1	2
+1	2	1	2
+1	2	1	2
+1	2	1	2
+WITH qn (foo, bar) AS (SELECT a, b FROM t1 LIMIT 2) SELECT qn.bar,foo FROM qn;
+bar	foo
+NULL	NULL
+3	2
+CREATE TABLE t3
+WITH qn (foo, bar) AS (SELECT a, b FROM t1 LIMIT 2) SELECT bar,foo FROM qn;
+DESC t3;
+Field	Type	Null	Key	Default	Extra
+bar	int(11)	YES		NULL	
+foo	int(11)	YES		NULL	
+DROP TABLE t3;
+# Merge
+WITH qn (foo, bar) AS (SELECT 1 FROM t1) SELECT * FROM qn, qn qn1;
+ERROR HY000: WITH column list and SELECT field list have different column counts
+WITH qn (foo, bar) AS (SELECT 1, 2 FROM t1) SELECT * FROM qn, qn qn1;
+foo	bar	foo	bar
+1	2	1	2
+1	2	1	2
+1	2	1	2
+1	2	1	2
+explain WITH qn (foo, bar) AS (SELECT 1, 2 FROM t1) SELECT * FROM qn, qn qn1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (flat, BNL join)
+WITH qn (foo, bar) AS (SELECT 1 AS col, 2 AS coll FROM t1) SELECT * FROM qn, qn qn1;
+foo	bar	foo	bar
+1	2	1	2
+1	2	1	2
+1	2	1	2
+1	2	1	2
+WITH qn (foo, bar) AS (SELECT a, b FROM t1) SELECT qn1.bar,foo FROM qn qn1;
+bar	foo
+NULL	NULL
+3	2
+CREATE TABLE t3
+WITH qn (foo, bar) AS (SELECT a, b FROM t1) SELECT bar,foo FROM qn;
+DESC t3;
+Field	Type	Null	Key	Default	Extra
+bar	int(11)	YES		NULL	
+foo	int(11)	YES		NULL	
+DROP TABLE t3;
+# Disambiguates same-name expressions
+WITH qn AS (SELECT 1,1) SELECT * FROM qn;
+ERROR 42S21: Duplicate column name '1'
+WITH qn (foo, bar) AS (SELECT 1,1) SELECT * FROM qn;
+foo	bar
+1	1
+WITH qn AS (SELECT 1,1 FROM t1) SELECT * FROM qn;
+ERROR 42S21: Duplicate column name '1'
+WITH qn (foo, bar) AS (SELECT 1,1 FROM t1) SELECT * FROM qn;
+foo	bar
+1	1
+1	1
+# Duplicate names are forbidden
+WITH qn (foo, foo) AS (SELECT 1,2) SELECT * FROM qn;
+ERROR 42S21: Duplicate column name 'foo'
+# Column names for QN/DT are printed
+CREATE VIEW v1 AS
+WITH qn (foo, bar) AS (SELECT 1,1) SELECT * FROM qn;
+SHOW CREATE VIEW v1;
+View	Create View	character_set_client	collation_connection
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with qn as (select 1 AS `foo`,1 AS `bar`)select `qn`.`foo` AS `foo`,`qn`.`bar` AS `bar` from `qn`	latin1	latin1_swedish_ci
+SHOW fields FROM v1;
+Field	Type	Null	Key	Default	Extra
+foo	int(1)	NO		0	
+bar	int(1)	NO		0	
+SELECT * FROM v1;
+foo	bar
+1	1
+DROP VIEW v1;
+CREATE VIEW v1 AS
+WITH qn (foo, bar) AS (SELECT 1,1 FROM t1) SELECT * FROM qn;
+SHOW CREATE VIEW v1;
+View	Create View	character_set_client	collation_connection
+v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS with qn as (select 1 AS `foo`,1 AS `bar` from `t1`)select `qn`.`foo` AS `foo`,`qn`.`bar` AS `bar` from `qn`	latin1	latin1_swedish_ci
+SELECT * FROM v1;
+foo	bar
+1	1
+1	1
+DROP VIEW v1;
+CREATE VIEW v1 (bar) AS
+SELECT 1, 2 FROM t1;
+ERROR HY000: View's SELECT and view's field list have different column counts
+DROP TABLE t1;
+# Prove that a materialized QN is shared among all references:
+CREATE TABLE t1(a int);
+INSERT INTO t1 VALUES(1),(2),(3),(4);
+flush STATUS;
+WITH qn AS (SELECT 123 AS col)
+SELECT * FROM qn;
+col
+123
+SHOW STATUS LIKE "handler_write";
+Variable_name	Value
+Handler_write	0
+flush STATUS;
+WITH qn AS (SELECT 123 AS col)
+SELECT * FROM qn, qn AS qn1;
+col	col
+123	123
+SHOW STATUS LIKE "handler_write";
+Variable_name	Value
+Handler_write	0
+CREATE VIEW qn AS SELECT 123 AS col;
+flush STATUS;
+SELECT * FROM qn, qn AS qn1;
+col	col
+123	123
+SHOW STATUS LIKE "handler_write";
+Variable_name	Value
+Handler_write	0
+DROP VIEW qn;
+DROP TABLE t1;
+# Printing of WITH to DD for view
+CREATE VIEW v AS
+SELECT (WITH qn AS (SELECT "with") SELECT * FROM qn) AS scal_subq
+FROM dual;
+SHOW CREATE VIEW v;
+View	Create View	character_set_client	collation_connection
+v	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select (with qn as (select 'with' AS `with`)select `qn`.`with` from `qn`) AS `scal_subq`	latin1	latin1_swedish_ci
+SELECT * FROM v;
+scal_subq
+with
+DROP VIEW v;
+CREATE VIEW v AS SELECT * FROM (WITH qn AS (SELECT "with") SELECT * FROM qn) AS dt;
+SHOW CREATE VIEW v;
+View	Create View	character_set_client	collation_connection
+v	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `dt`.`with` AS `with` from (with qn as (select 'with' AS `with`)select `qn`.`with` AS `with` from `qn`) `dt`	latin1	latin1_swedish_ci
+SELECT * FROM v;
+with
+with
+DROP VIEW v;
+CREATE TABLE t1 (a int);
+explain WITH qne AS (SELECT a FROM t1),
+qnm AS (SELECT a FROM t1),
+qnea AS (SELECT a FROM t1),
+qnma AS (SELECT a FROM t1)
+SELECT
+qne.a,qnm.a,alias1.a,alias2.a
+FROM qne, qnm, qnea AS alias1, qnma AS alias2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+DROP TABLE t1;
+# Automatic index creation if materialized
+CREATE TABLE t1 (a int);
+INSERT INTO t1(a) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(0);
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+# EXPLAIN should not fill the tmp table
+flush STATUS;
+# Should use auto_key0 and ref access.
+explain WITH tt AS (SELECT * FROM t1)
+SELECT /*+ no_merge(tt) */ tt.a
+FROM t1 STRAIGHT_JOIN tt WHERE t1.a=tt.a
+LIMIT 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	#
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	#
+SHOW STATUS LIKE "handler_write";
+Variable_name	Value
+Handler_write	0
+flush STATUS;
+WITH tt AS (SELECT * FROM t1)
+SELECT /*+ no_merge(tt) */ tt.a
+FROM t1 STRAIGHT_JOIN tt WHERE t1.a=tt.a
+LIMIT 1;
+a
+1
+SHOW STATUS LIKE "handler_write";
+Variable_name	Value
+Handler_write	0
+# With two references
+WITH tt AS (SELECT * FROM t1)
+SELECT /*+ no_merge(tt) no_merge(tt_)*/ tt.a
+FROM t1 STRAIGHT_JOIN tt STRAIGHT_JOIN tt AS tt_
+WHERE t1.a=tt.a AND tt.a=tt_.a
+LIMIT 1;
+a
+1
+# One merged, one materialized: index creation on the second
+# should of course ignore the first
+WITH q AS (SELECT * FROM t1)
+SELECT /*+ merge(q) no_merge(q1) */ * FROM q, q q1 WHERE q.a=1 AND q1.a=2;
+a	a
+1	2
+DROP TABLE t1;
+# Must not create more than 64 indexes.
+(SELECT max(c1) FROM qn WHERE qn.c1=1)	(SELECT max(c2) FROM qn WHERE qn.c2=1)	(SELECT max(c3) FROM qn WHERE qn.c3=1)	(SELECT max(c4) FROM qn WHERE qn.c4=1)	(SELECT max(c5) FROM qn WHERE qn.c5=1)	(SELECT max(c6) FROM qn WHERE qn.c6=1)	(SELECT max(c7) FROM qn WHERE qn.c7=1)	(SELECT max(c8) FROM qn WHERE qn.c8=1)	(SELECT max(c9) FROM qn WHERE qn.c9=1)	(SELECT max(c10) FROM qn WHERE qn.c10=1)	(SELECT max(c11) FROM qn WHERE qn.c11=1)	(SELECT max(c12) FROM qn WHERE qn.c12=1)	(SELECT max(c13) FROM qn WHERE qn.c13=1)	(SELECT max(c14) FROM qn WHERE qn.c14=1)	(SELECT max(c15) FROM qn WHERE qn.c15=1)	(SELECT max(c16) FROM qn WHERE qn.c16=1)	(SELECT max(c17) FROM qn WHERE qn.c17=1)	(SELECT max(c18) FROM qn WHERE qn.c18=1)	(SELECT max(c19) FROM qn WHERE qn.c19=1)	(SELECT max(c20) FROM qn WHERE qn.c20=1)	(SELECT max(c21) FROM qn WHERE qn.c21=1)	(SELECT max(c22) FROM qn WHERE qn.c22=1)	(SELECT max(c23) FROM qn WHERE qn.c23=1)	(SELECT max(c24) FROM qn WHERE qn.c24=1)	(SELECT max(c25) FROM qn WHERE qn.c25=1)	(SELECT max(c26) FROM qn WHERE qn.c26=1)	(SELECT max(c27) FROM qn WHERE qn.c27=1)	(SELECT max(c28) FROM qn WHERE qn.c28=1)	(SELECT max(c29) FROM qn WHERE qn.c29=1)	(SELECT max(c30) FROM qn WHERE qn.c30=1)	(SELECT max(c31) FROM qn WHERE qn.c31=1)	(SELECT max(c32) FROM qn WHERE qn.c32=1)	(SELECT max(c33) FROM qn WHERE qn.c33=1)	(SELECT max(c34) FROM qn WHERE qn.c34=1)	(SELECT max(c35) FROM qn WHERE qn.c35=1)	(SELECT max(c36) FROM qn WHERE qn.c36=1)	(SELECT max(c37) FROM qn WHERE qn.c37=1)	(SELECT max(c38) FROM qn WHERE qn.c38=1)	(SELECT max(c39) FROM qn WHERE qn.c39=1)	(SELECT max(c40) FROM qn WHERE qn.c40=1)	(SELECT max(c41) FROM qn WHERE qn.c41=1)	(SELECT max(c42) FROM qn WHERE qn.c42=1)	(SELECT max(c43) FROM qn WHERE qn.c43=1)	(SELECT max(c44) FROM qn WHERE qn.c44=1)	(SELECT max(c45) FROM qn WHERE qn.c45=1)	(SELECT max(c46) FROM qn WHERE qn.c46=1)	(SELECT max(c47) FROM qn WHERE qn.c47=1)	(SELECT max(c48) FROM qn WHERE qn.c48=1)	(SELECT max(c49) FROM qn WHERE qn.c49=1)	(SELECT max(c50) FROM qn WHERE qn.c50=1)	(SELECT max(c51) FROM qn WHERE qn.c51=1)	(SELECT max(c52) FROM qn WHERE qn.c52=1)	(SELECT max(c53) FROM qn WHERE qn.c53=1)	(SELECT max(c54) FROM qn WHERE qn.c54=1)	(SELECT max(c55) FROM qn WHERE qn.c55=1)	(SELECT max(c56) FROM qn WHERE qn.c56=1)	(SELECT max(c57) FROM qn WHERE qn.c57=1)	(SELECT max(c58) FROM qn WHERE qn.c58=1)	(SELECT max(c59) FROM qn WHERE qn.c59=1)	(SELECT max(c60) FROM qn WHERE qn.c60=1)	(SELECT max(c61) FROM qn WHERE qn.c61=1)	(SELECT max(c62) FROM qn WHERE qn.c62=1)	(SELECT max(c63) FROM qn WHERE qn.c63=1)	(SELECT max(c64) FROM qn WHERE qn.c64=1)	(SELECT max(c65) FROM qn WHERE qn.c65=1)	(SELECT max(c66) FROM qn WHERE qn.c66=1)	(SELECT max(c67) FROM qn WHERE qn.c67=1)	(SELECT max(c68) FROM qn WHERE qn.c68=1)	(SELECT max(c69) FROM qn WHERE qn.c69=1)	(SELECT max(c70) FROM qn WHERE qn.c70=1)	(SELECT max(c71) FROM qn WHERE qn.c71=1)	(SELECT max(c72) FROM qn WHERE qn.c72=1)	(SELECT max(c73) FROM qn WHERE qn.c73=1)	(SELECT max(c74) FROM qn WHERE qn.c74=1)	(SELECT max(c75) FROM qn WHERE qn.c75=1)	(SELECT max(c76) FROM qn WHERE qn.c76=1)	(SELECT max(c77) FROM qn WHERE qn.c77=1)	(SELECT max(c78) FROM qn WHERE qn.c78=1)	(SELECT max(c79) FROM qn WHERE qn.c79=1)	(SELECT max(c80) FROM qn WHERE qn.c80=1)	(SELECT max(c81) FROM qn WHERE qn.c81=1)	(SELECT max(c82) FROM qn WHERE qn.c82=1)	(SELECT max(c83) FROM qn WHERE qn.c83=1)	(SELECT max(c84) FROM qn WHERE qn.c84=1)	(SELECT max(c85) FROM qn WHERE qn.c85=1)	(SELECT max(c86) FROM qn WHERE qn.c86=1)	(SELECT max(c87) FROM qn WHERE qn.c87=1)	(SELECT max(c88) FROM qn WHERE qn.c88=1)	(SELECT max(c89) FROM qn WHERE qn.c89=1)	(SELECT max(c90) FROM qn WHERE qn.c90=1)	(SELECT max(c91) FROM qn WHERE qn.c91=1)	(SELECT max(c92) FROM qn WHERE qn.c92=1)	(SELECT max(c93) FROM qn WHERE qn.c93=1)	(SELECT max(c94) FROM qn WHERE qn.c94=1)	(SELECT max(c95) FROM qn WHERE qn.c95=1)	(SELECT max(c96) FROM qn WHERE qn.c96=1)	(SELECT max(c97) FROM qn WHERE qn.c97=1)	(SELECT max(c98) FROM qn WHERE qn.c98=1)	(SELECT max(c99) FROM qn WHERE qn.c99=1)	(SELECT max(c100) FROM qn WHERE qn.c100=1)
+NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
+DROP TABLE t;
+# Choice between two auto_key:
+CREATE TABLE t1(a int, b int);
+INSERT INTO t1 VALUES (NULL, 6), (NULL, 10);
+# Test the covering key; note that MEMORY doesn't use a
+# covering key (always reads the "data file"). But InnoDB does.
+EXPLAIN WITH t2 AS
+(SELECT * FROM t1)
+SELECT /*+ no_merge(t2) */ * FROM t2
+WHERE (a = a OR b <= 6) AND (a IS NULL);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+WITH t2 AS
+(SELECT * FROM t1)
+SELECT /*+ no_merge(t2) */ * FROM t2
+WHERE (a = a OR b <= 6) AND (a IS NULL);
+a	b
+NULL	6
+DROP TABLE t1;
+# QN referencing view of same name isn't a "recursive view",
+# shouldn't cause ER_VIEW_RECURSIVE
+CREATE VIEW v1 AS SELECT "with";
+WITH v1 AS (SELECT * FROM v1) SELECT * FROM v1;
+with
+with
+DROP VIEW v1;
+# QN inside view
+CREATE VIEW v1 AS
+WITH qn AS (SELECT 1 AS col) SELECT * FROM qn;
+SELECT * FROM v1;
+col
+1
+DROP VIEW v1;
+CREATE TABLE t1(a int, b int);
+# Alas merge hints are ignored in views (filed Bug#23017428)
+CREATE VIEW v1 AS
+WITH qn AS (SELECT a FROM t1),
+qn2 AS (SELECT b FROM t1)
+SELECT /*+ merge(qn) no_merge(qn2) */ qn.a,qn2.b FROM qn, qn2;
+explain SELECT * FROM v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+DROP VIEW v1;
+# Materializing view doesn't impose materializing query name
+CREATE algorithm=temptable VIEW v1 AS
+WITH qn AS (SELECT a FROM t1)
+SELECT qn.a  FROM qn;
+explain SELECT * FROM v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	0	const row not found
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+DROP VIEW v1;
+DROP TABLE t1;
+# CTE referenced four times, including in subqueries in other CTEs
+CREATE TABLE sales_days(day_of_sale DATE, amount INT);
+INSERT INTO sales_days VALUES
+('2015-01-02', 100), ('2015-01-05', 200),
+('2015-02-02', 10),  ('2015-02-10', 100),
+('2015-03-02', 10),  ('2015-03-18', 1);
+WITH
+# FIRST CTE: one ROW per MONTH, WITH amount sold ON ALL days OF MONTH
+sales_by_month(MONTH,total) AS
+(SELECT MONTH(day_of_sale), sum(amount) FROM sales_days
+WHERE year(day_of_sale)=2015
+GROUP BY MONTH(day_of_sale)),
+# SECOND CTE: best MONTH
+best_month(MONTH, total, award) AS
+(SELECT MONTH, total, "best" FROM sales_by_month
+WHERE total=(SELECT max(total) FROM sales_by_month)),
+# 3rd CTE: worst MONTH
+worst_month(MONTH, total, award) AS
+(SELECT MONTH, total, "worst" FROM sales_by_month
+WHERE total=(SELECT min(total) FROM sales_by_month))
+# Now SHOW results:
+SELECT * FROM best_month UNION ALL SELECT * FROM worst_month;
+MONTH	total	award
+1	300	best
+3	11	worst
+DROP TABLE sales_days;
+WITH qn AS (SELECT 1) SELECT * FROM qn, qn qn1;
+1	1
+1	1
+CREATE TABLE t1(a int);
+INSERT INTO t1 VALUES(1),(2);
+WITH qn(a) AS (SELECT 1 FROM t1 LIMIT 2)
+SELECT * FROM qn WHERE qn.a=(SELECT * FROM qn qn1 LIMIT 1) UNION SELECT 2;
+a
+1
+2
+DROP TABLE t1;
+SHOW STATUS LIKE 'Created_tmp_disk_tables';
+Variable_name	Value
+Created_tmp_disk_tables	116
diff --git a/mysql-test/r/with_recursive.result b/mysql-test/r/with_recursive.result
new file mode 100644
index 00000000000..226439c180c
--- /dev/null
+++ b/mysql-test/r/with_recursive.result
@@ -0,0 +1,2706 @@
+# In-memory tmp tables
+set big_tables=0;
+SET @@max_recursive_iterations = 10000;
+flush STATUS;
+# Mutual recursion unsupported; cycles must have one node only
+WITH RECURSIVE qn AS (SELECT * FROM qn2),
+qn2 AS (SELECT * FROM qn)
+SELECT * FROM qn;
+ERROR HY000: No anchors for recursive WITH element 'qn'
+# At least one anchor member, all anchors before all recursive
+WITH RECURSIVE qn AS
+(SELECT 1 FROM qn)
+SELECT count(*) FROM qn;
+ERROR HY000: No anchors for recursive WITH element 'qn'
+WITH RECURSIVE qn AS
+(SELECT 1 UNION ALL SELECT 1 FROM qn UNION ALL SELECT 1)
+SELECT count(*) FROM qn;
+count(*)
+20002
+WITH RECURSIVE qn AS
+(SELECT 1 FROM qn UNION ALL SELECT 1 FROM qn)
+SELECT * FROM qn;
+ERROR HY000: No anchors for recursive WITH element 'qn'
+# It's ok to have the RECURSIVE word without any recursive member
+WITH RECURSIVE qn AS
+(SELECT 1 FROM dual UNION ALL
+SELECT 1 FROM dual)
+SELECT * FROM qn;
+1
+1
+1
+# UNION DISTINCT allowed
+WITH RECURSIVE qn AS
+(SELECT 1 FROM dual UNION
+SELECT 1 FROM qn)
+SELECT * FROM qn;
+1
+1
+# No aggregation on the QN
+CREATE TABLE t1(b int);
+INSERT INTO t1 VALUES(10),(20),(10);
+WITH RECURSIVE qn AS
+(SELECT max(b) AS a FROM t1 UNION
+SELECT a FROM qn)
+SELECT * FROM qn;
+a
+20
+WITH RECURSIVE qn AS
+(SELECT b AS a FROM t1 UNION
+SELECT max(a) FROM qn)
+SELECT * FROM qn;
+ERROR HY000: Restrictions imposed on recursive definitions are violated for table 'qn'
+# No window functions
+WITH RECURSIVE qn AS
+(SELECT rank() over (ORDER BY b) AS a FROM t1 UNION
+SELECT a FROM qn)
+SELECT * FROM qn;
+a
+1
+3
+WITH RECURSIVE qn AS
+(SELECT b AS a FROM t1 UNION
+SELECT rank() over (ORDER BY a) FROM qn)
+SELECT * FROM qn;
+ERROR HY000: Restrictions imposed on recursive definitions are violated for table 'qn'
+DROP TABLE t1;
+WITH RECURSIVE qn AS
+(SELECT 1 AS a FROM dual UNION ALL
+SELECT max(a) FROM qn)
+SELECT * FROM qn;
+ERROR HY000: Restrictions imposed on recursive definitions are violated for table 'qn'
+WITH RECURSIVE qn AS
+(SELECT 1 AS a FROM dual GROUP BY a UNION ALL
+SELECT a+1 FROM qn WHERE a<3)
+SELECT * FROM qn;
+a
+1
+2
+3
+WITH RECURSIVE qn AS
+(SELECT 1 AS a FROM dual UNION ALL
+SELECT a FROM qn GROUP BY a)
+SELECT count(*)FROM qn;
+count(*)
+10001
+# No subquery referencing a QN
+WITH RECURSIVE qn AS (
+SELECT 1 FROM dual UNION ALL
+SELECT 1 FROM dual WHERE 1 NOT in(SELECT * FROM qn))
+SELECT * FROM qn;
+ERROR HY000: Restrictions imposed on recursive definitions are violated for table 'qn'
+WITH RECURSIVE qn AS (
+SELECT 1 FROM dual UNION ALL
+SELECT 1 FROM qn
+ORDER BY (SELECT * FROM qn))
+SELECT count(*) FROM qn;
+ERROR 42000: This version of MariaDB doesn't yet support 'global ORDER_BY/LIMIT in recursive CTE spec'
+WITH RECURSIVE qn AS (
+SELECT 1 FROM dual UNION ALL
+SELECT * FROM (SELECT * FROM qn) AS dt)
+SELECT count(*) FROM qn;
+count(*)
+10001
+WITH RECURSIVE qn AS (
+SELECT 1 AS a FROM dual UNION ALL
+SELECT 1 FROM qn
+ORDER BY a)
+SELECT count(*) FROM qn;
+ERROR 42000: This version of MariaDB doesn't yet support 'global ORDER_BY/LIMIT in recursive CTE spec'
+# No matter if global, or attached to one recursive member.
+WITH RECURSIVE qn AS (
+SELECT 1 AS a FROM dual UNION ALL
+(SELECT 1 FROM qn ORDER BY a))
+SELECT count(*) FROM qn;
+count(*)
+10001
+# Allowed on non-recursive query block (though pointless)
+WITH RECURSIVE qn AS (
+(SELECT 1 AS a FROM dual ORDER BY a) UNION ALL
+SELECT a+1 FROM qn WHERE a<3)
+SELECT * FROM qn;
+a
+1
+2
+3
+WITH RECURSIVE qn AS (
+SELECT 1 AS a FROM dual UNION ALL
+SELECT 1 FROM qn
+LIMIT 10)
+SELECT count(*) FROM qn;
+ERROR 42000: This version of MariaDB doesn't yet support 'global ORDER_BY/LIMIT in recursive CTE spec'
+WITH RECURSIVE qn AS (
+SELECT 1 AS a FROM dual UNION ALL
+(SELECT 1 FROM qn LIMIT 10))
+SELECT count(*) FROM qn;
+count(*)
+10001
+WITH RECURSIVE qn AS
+(SELECT 1 UNION ALL SELECT DISTINCT 3 FROM qn)
+SELECT count(*) FROM qn;
+count(*)
+10001
+WITH RECURSIVE qn AS (SELECT 1 FROM dual UNION ALL
+SELECT 1 FROM dual
+WHERE 1 NOT in(SELECT * FROM qn))
+SELECT * FROM qn;
+ERROR HY000: Restrictions imposed on recursive definitions are violated for table 'qn'
+# Numbers from 123 to 130:
+WITH RECURSIVE qn AS (SELECT 123 AS a UNION ALL SELECT 1+a FROM qn WHERE a<130) SELECT * FROM qn;
+a
+123
+124
+125
+126
+127
+128
+129
+130
+# One-level recursive sequence of numbers
+WITH RECURSIVE qn AS (SELECT 1 AS n, 2 AS un UNION ALL SELECT 1+n, un*5-6 FROM qn WHERE n<10) SELECT * FROM qn;
+n	un
+1	2
+2	4
+3	14
+4	64
+5	314
+6	1564
+7	7814
+8	39064
+9	195314
+10	976564
+# Fibonacci
+WITH RECURSIVE qn AS (SELECT 1 AS n, 1 AS un, 1 AS unp1 UNION ALL SELECT 1+n, unp1, un+unp1 FROM qn WHERE n<10) SELECT * FROM qn;
+n	un	unp1
+1	1	1
+2	1	2
+3	2	3
+4	3	5
+5	5	8
+6	8	13
+7	13	21
+8	21	34
+9	34	55
+10	55	89
+# Validate that cast(a_varchar as char) produces a varchar, not a
+# char.
+CREATE TABLE t(c char(3), vc varchar(3), b binary(3), vb varbinary(3));
+CREATE TABLE u
+SELECT cast(c AS char(4)), cast(vc AS char(4)),
+cast(b AS binary(4)), cast(vb AS binary(4)),
+"abc" AS literal_c, cast("abc" AS char(4)),
+_binary "abc" AS literal_b, cast(_binary "abc" AS binary(4))
+FROM t;
+SHOW CREATE TABLE u;
+Table	Create Table
+u	CREATE TABLE `u` (
+  `cast(c AS char(4))` varchar(4) DEFAULT NULL,
+  `cast(vc AS char(4))` varchar(4) DEFAULT NULL,
+  `cast(b AS binary(4))` varbinary(4) DEFAULT NULL,
+  `cast(vb AS binary(4))` varbinary(4) DEFAULT NULL,
+  `literal_c` varchar(3) NOT NULL,
+  `cast("abc" AS char(4))` varchar(4) DEFAULT NULL,
+  `literal_b` varbinary(3) NOT NULL,
+  `cast(_binary "abc" AS binary(4))` varbinary(4) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t,u;
+# if it used char the 'x' would fall off due to spaces.
+WITH RECURSIVE qn AS (SELECT 1 AS n, cast('x' AS char(100)) AS un UNION ALL SELECT 1+n, concat(un,'x') FROM qn WHERE n<10) SELECT * FROM qn;
+n	un
+1	x
+2	xx
+3	xxx
+4	xxxx
+5	xxxxx
+6	xxxxxx
+7	xxxxxxx
+8	xxxxxxxx
+9	xxxxxxxxx
+10	xxxxxxxxxx
+# String now growing at the left
+WITH RECURSIVE qn AS (SELECT cast("x" AS char(10)) AS a FROM dual
+UNION ALL SELECT concat("x",a) FROM qn WHERE length(a)<10) SELECT *
+FROM qn;
+a
+x
+xx
+xxx
+xxxx
+xxxxx
+xxxxxx
+xxxxxxx
+xxxxxxxx
+xxxxxxxxx
+xxxxxxxxxx
+# Forgot cast-as-char(10) in anchor => qn.a column has length 1
+# => concat() is cast as char(1) => truncation
+# => length is always 1 => infinite loop;
+WITH RECURSIVE qn AS (SELECT "x" AS a FROM dual
+UNION ALL SELECT concat("x",a) FROM qn WHERE length(a)<10) SELECT count(*)
+FROM qn;
+count(*)
+10001
+# Overflow integer type INT (max 4G)
+WITH RECURSIVE qn AS (SELECT 1 AS a FROM dual
+UNION ALL SELECT a*2000 FROM qn WHERE a<10000000000000000000) SELECT count(*) FROM qn;
+ERROR 22003: BIGINT value is out of range in '`qn`.`a` * 2000'
+# Use Decimal
+WITH RECURSIVE qn AS (SELECT cast(1 AS decimal(30,0)) AS a FROM dual
+UNION ALL SELECT a*2000 FROM qn WHERE a<10000000000000000000) SELECT * FROM qn;
+a
+1
+2000
+4000000
+8000000000
+16000000000000
+32000000000000000
+64000000000000000000
+WITH RECURSIVE qn AS (SELECT 123 AS a UNION #ALL 
+SELECT NULL FROM qn WHERE a IS NOT NULL) SELECT * FROM qn;
+a
+123
+0
+# Mixing really unrelated types: the goal is to report a sensible
+# error and not crash.
+# The Point becomes a string which is an invalid integer, cast string to int -> result 0:
+WITH RECURSIVE qn AS (
+SELECT 1 AS a,1
+UNION ALL
+SELECT a+1,ST_PointFromText('POINT(10 10)') FROM qn WHERE a<2)
+SELECT * FROM qn;
+a	1
+1	1
+2	0
+# POINT in anchor => BLOB in tmp table => not MEMORY engine => Innodb
+WITH RECURSIVE qn AS (
+SELECT 1 AS a,ST_PointFromText('POINT(10 10)')
+UNION ALL
+SELECT a+1,1 FROM qn WHERE a<2)
+SELECT * FROM qn;
+ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
+# Same number of columns in anchor and recursive members
+WITH RECURSIVE qn AS
+(
+SELECT 1
+UNION ALL
+SELECT 3, 0 FROM qn
+)
+SELECT * FROM qn;
+ERROR 21000: The used SELECT statements have a different number of columns
+# Mismatch in column name and column count; problem specific of
+# recursive CTE which creates tmp table earlier in preparation.
+WITH RECURSIVE q (b) AS (SELECT 1, 1 UNION ALL SELECT 1, 1 FROM q)
+SELECT b FROM q;
+ERROR HY000: WITH column list and SELECT field list have different column counts
+# Cannot have two recursive refs in FROM:
+WITH RECURSIVE qn AS (
+SELECT 123 AS a UNION ALL
+SELECT 1+qn.a FROM qn, qn AS qn1 WHERE qn1.a<130)
+SELECT * FROM qn;
+ERROR HY000: Restrictions imposed on recursive definitions are violated for table 'qn'
+# Prove that a materialized QN is shared among all references:
+flush STATUS;
+WITH RECURSIVE qn AS (
+SELECT 123 AS a UNION ALL
+SELECT 1+a FROM qn WHERE a<125)
+SELECT * FROM qn;
+a
+123
+124
+125
+SHOW STATUS LIKE "handler_write";
+Variable_name	Value
+Handler_write	0
+flush STATUS;
+WITH RECURSIVE qn AS (
+SELECT 123 AS a UNION ALL
+SELECT 1+a FROM qn WHERE a<125)
+SELECT * FROM qn, qn AS qn1;
+a	a
+123	123
+124	123
+125	123
+123	124
+124	124
+125	124
+123	125
+124	125
+125	125
+SHOW STATUS LIKE "handler_write";
+Variable_name	Value
+Handler_write	0
+SHOW STATUS LIKE 'Created_tmp%table%';
+Variable_name	Value
+Created_tmp_disk_tables	0
+Created_tmp_tables	5
+# Also works if references are nested inside other query names:
+flush STATUS;
+WITH RECURSIVE inner_ AS (
+SELECT 123 AS a UNION ALL
+SELECT 1+a FROM inner_ WHERE a<125),
+outer_ AS (SELECT * FROM inner_ LIMIT 10)
+SELECT * FROM outer_, outer_ AS outer1;
+a	a
+123	123
+124	123
+125	123
+123	124
+124	124
+125	124
+123	125
+124	125
+125	125
+SHOW STATUS LIKE "handler_write";
+Variable_name	Value
+Handler_write	0
+flush STATUS;
+WITH RECURSIVE inner_ AS (
+SELECT 123 AS a UNION ALL
+SELECT 1+a FROM inner_ WHERE a<125),
+outer_ AS
+(SELECT inner_.a, inner1.a AS a1
+FROM inner_, inner_ AS inner1 LIMIT 10)
+SELECT * FROM outer_;
+a	a1
+123	123
+124	123
+125	123
+123	124
+124	124
+125	124
+123	125
+124	125
+125	125
+SHOW STATUS LIKE "handler_write";
+Variable_name	Value
+Handler_write	0
+# Even if the two query names are recursive:
+flush STATUS;
+WITH RECURSIVE inner_ AS (
+SELECT 123 AS a UNION ALL
+SELECT 1+a FROM inner_ WHERE a<125),
+outer_ AS
+(SELECT a FROM inner_ UNION ALL
+SELECT a*2 FROM outer_ WHERE a<1000)
+SELECT a FROM outer_;
+a
+123
+124
+125
+246
+248
+250
+492
+496
+500
+984
+992
+1000
+1968
+1984
+SHOW STATUS LIKE "handler_write";
+Variable_name	Value
+Handler_write	0
+# Optimizer must be allowed to put the recursive reference first
+CREATE TABLE t1(a int);
+INSERT INTO t1 VALUES(1),(2);
+WITH RECURSIVE qn AS
+(
+SELECT 1 FROM t1
+UNION ALL
+SELECT 1 FROM t1 LEFT JOIN qn ON 1
+)
+SELECT * FROM qn;
+ERROR HY000: Restrictions imposed on recursive definitions are violated for table 'qn'
+# Empty anchor
+WITH RECURSIVE qn AS
+(
+SELECT a FROM t1 WHERE 0
+UNION ALL
+SELECT a+1 FROM qn
+)
+SELECT * FROM qn;
+a
+WITH RECURSIVE qn AS
+(
+SELECT a FROM t1 WHERE a>10
+UNION ALL
+SELECT a+1 FROM qn
+)
+SELECT * FROM qn;
+a
+# UNION DISTINCT in anchor parts 
+INSERT INTO t1 VALUES(1),(2);
+SET @c=0, @d=0;
+WITH RECURSIVE qn AS
+(
+SELECT 1,0 AS col FROM t1
+UNION DISTINCT
+SELECT 1,0 FROM t1
+UNION ALL
+SELECT 3, 0*(@c:=@c+1) FROM qn WHERE @c<1
+UNION ALL
+SELECT 3, 0*(@d:=@d+1) FROM qn WHERE @d<1
+)
+SELECT * FROM qn;
+ERROR 42000: This version of MariaDB doesn't yet support 'mix of ALL and DISTINCT UNION operations in recursive CTE spec'
+# UNION DISTINCT affecting recursive member, followed by UNION ALL
+INSERT INTO t1 VALUES(1),(2);
+SET @c=0, @d=0;
+WITH RECURSIVE qn AS
+(
+SELECT 1,0 AS col FROM t1
+UNION DISTINCT
+SELECT 3, 0*(@c:=@c+1) FROM qn WHERE @c<1
+UNION ALL
+SELECT 3, 0*(@d:=@d+1) FROM qn WHERE @d<1
+)
+SELECT * FROM qn;
+ERROR 42000: This version of MariaDB doesn't yet support 'mix of ALL and DISTINCT UNION operations in recursive CTE spec'
+# create select
+CREATE TABLE t2
+WITH RECURSIVE qn AS (
+SELECT 123 AS a UNION ALL SELECT 1+a FROM qn WHERE a<130)
+SELECT * FROM qn;
+SELECT * FROM t2;
+a
+123
+124
+125
+126
+127
+128
+129
+130
+DROP TABLE t2;
+# insert select
+DELETE FROM t1;
+INSERT INTO t1
+WITH RECURSIVE qn AS (
+SELECT 123 AS a UNION ALL SELECT 1+a FROM qn WHERE a<130)
+SELECT * FROM qn;
+SELECT * FROM t1;
+a
+123
+124
+125
+126
+127
+128
+129
+130
+# Using insertion target inside recursive query
+DELETE FROM t1;
+INSERT INTO t1 VALUES(1),(2);
+INSERT INTO t1
+WITH RECURSIVE qn AS (
+SELECT 123 AS a UNION ALL SELECT 1+qn.a FROM qn, t1 WHERE qn.a<125)
+SELECT * FROM qn;
+SELECT * FROM t1;
+a
+1
+2
+123
+124
+124
+125
+125
+125
+125
+DROP TABLE t1;
+# insert into tmp table (a likely use case)
+CREATE TEMPORARY TABLE t1(a int);
+INSERT INTO t1
+WITH RECURSIVE qn AS (
+SELECT 123 AS a UNION ALL SELECT 1+a FROM qn WHERE a<130)
+SELECT * FROM qn;
+SELECT * FROM t1;
+a
+123
+124
+125
+126
+127
+128
+129
+130
+DROP TABLE t1;
+# create view
+CREATE VIEW v1 AS
+WITH RECURSIVE qn AS (
+SELECT 123 AS a UNION ALL SELECT 1+a FROM qn WHERE a<130)
+SELECT * FROM qn;
+SELECT * FROM v1;
+a
+123
+124
+125
+126
+127
+128
+129
+130
+DROP VIEW v1;
+# Recursive QN can be constant (0-row or 1-row) for the
+# optimizer if its members have impossible conditions:
+# Recursive refs should never use indexes to read:
+# first, optimization of top query creates a key on q.b;
+# then optimization of scalar subquery, when it optimizes the
+# recursive member, must be prevented from re-using this key
+# (it was a bug that it re-used it, as the index is covering
+# and adjust_access_methods() has a heuristic which converts a
+# table scan to index scan, so it wrongly used an index scan).
+EXPLAIN WITH RECURSIVE q (b) AS
+(SELECT 1 UNION ALL SELECT 1+b FROM q WHERE b<10)
+SELECT (SELECT q1.b FROM q AS q2 WHERE q2.b=3) FROM q AS q1 WHERE q1.b=3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	Using where
+4	DEPENDENT SUBQUERY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	RECURSIVE UNION	<derived2>	ALL	NULL	NULL	NULL	NULL	2	Using where
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+WITH RECURSIVE q (b) AS
+(SELECT 1 UNION ALL SELECT 1+b FROM q WHERE b<10)
+SELECT (SELECT q1.b FROM q AS q2 WHERE q2.b=3) FROM q AS q1 WHERE q1.b=3;
+(SELECT q1.b FROM q AS q2 WHERE q2.b=3)
+3
+# This is from my blog so I can use it here.
+# Tests depth-first etc
+CREATE TABLE employees (
+ID INT PRIMARY KEY,
+NAME VARCHAR(100),
+MANAGER_ID INT,
+INDEX (MANAGER_ID),
+FOREIGN KEY (MANAGER_ID) REFERENCES employees(ID)
+);
+INSERT INTO employees VALUES
+(333, "Yasmina", NULL),
+(198, "John", 333),
+(692, "Tarek", 333),
+(29, "Pedro", 198),
+(4610, "Sarah", 29),
+(72, "Pierre", 29),
+(123, "Adil", 692);
+ANALYZE TABLE employees;
+Table	Op	Msg_type	Msg_text
+test.employees	analyze	status	OK
+# Depth-first.
+# Also test column names, and their reference in the recursive member.
+WITH RECURSIVE employees_extended(ID, NAME, PATH)
+AS
+(
+SELECT ID, NAME, CAST(ID AS CHAR(200))
+FROM employees
+WHERE MANAGER_ID IS NULL
+UNION ALL
+SELECT S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID)
+FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID
+)
+SELECT * FROM employees_extended ORDER BY PATH;
+ID	NAME	PATH
+333	Yasmina	333
+198	John	333,198
+29	Pedro	333,198,29
+4610	Sarah	333,198,29,4610
+72	Pierre	333,198,29,72
+692	Tarek	333,692
+123	Adil	333,692,123
+# Breadth-first is likely what we get, if no ordering
+WITH RECURSIVE employees_extended
+AS
+(
+SELECT ID, NAME, CAST(ID AS CHAR(200)) AS PATH
+FROM employees
+WHERE MANAGER_ID IS NULL
+UNION ALL
+SELECT S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID)
+FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID
+)
+SELECT * FROM employees_extended;
+ID	NAME	PATH
+333	Yasmina	333
+198	John	333,198
+692	Tarek	333,692
+29	Pedro	333,198,29
+123	Adil	333,692,123
+4610	Sarah	333,198,29,4610
+72	Pierre	333,198,29,72
+# But to be really sure we have breadth-first, we generate a
+# numeric column SEQ. And sort by NAME, to have repeatable
+# order of siblings (who have the same SEQ).
+WITH RECURSIVE employees_extended
+AS
+(
+SELECT 0 AS SEQ, ID, NAME, CAST(ID AS CHAR(200)) AS PATH
+FROM employees
+WHERE MANAGER_ID IS NULL
+UNION ALL
+SELECT M.SEQ+1, S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID)
+FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID
+)
+SELECT * FROM employees_extended ORDER BY SEQ, NAME;
+SEQ	ID	NAME	PATH
+0	333	Yasmina	333
+1	198	John	333,198
+1	692	Tarek	333,692
+2	123	Adil	333,692,123
+2	29	Pedro	333,198,29
+3	72	Pierre	333,198,29,72
+3	4610	Sarah	333,198,29,4610
+# Or, use a user variable, then all rows have different number:
+WITH RECURSIVE employees_extended
+AS
+(
+SELECT (@s:=0) AS SEQ, ID, NAME, CAST(ID AS CHAR(200)) AS PATH
+FROM employees
+WHERE MANAGER_ID IS NULL
+UNION ALL
+SELECT (@s:=@s+1), S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID)
+FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID
+)
+SELECT * FROM employees_extended ORDER BY SEQ;
+SEQ	ID	NAME	PATH
+0	333	Yasmina	333
+1	198	John	333,198
+2	692	Tarek	333,692
+3	29	Pedro	333,198,29
+4	123	Adil	333,692,123
+5	4610	Sarah	333,198,29,4610
+6	72	Pierre	333,198,29,72
+# Direct & indirect reports of John = having John in their PATH
+WITH RECURSIVE employees_extended
+AS
+(
+SELECT ID, NAME, CAST(ID AS CHAR(200)) AS PATH
+FROM employees
+WHERE MANAGER_ID IS NULL
+UNION ALL
+SELECT S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID)
+FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID
+)
+SELECT * FROM employees_extended
+WHERE FIND_IN_SET((SELECT ID FROM employees WHERE NAME='John'),
+PATH);
+ID	NAME	PATH
+198	John	333,198
+29	Pedro	333,198,29
+4610	Sarah	333,198,29,4610
+72	Pierre	333,198,29,72
+# Exclude John, he's not a report of himself;
+# bonus: use a QN to cache his ID.
+WITH RECURSIVE employees_extended(ID, NAME, PATH)
+AS
+(
+SELECT ID, NAME, CAST(ID AS CHAR(200))
+FROM employees
+WHERE MANAGER_ID IS NULL
+UNION ALL
+SELECT S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID)
+FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID
+),
+JOHN_ID AS (SELECT ID FROM employees WHERE NAME='John')
+SELECT e.* FROM employees_extended e, JOHN_ID
+WHERE FIND_IN_SET(JOHN_ID.ID,
+PATH)
+AND e.ID<>JOHN_ID.ID;
+ID	NAME	PATH
+29	Pedro	333,198,29
+4610	Sarah	333,198,29,4610
+72	Pierre	333,198,29,72
+# Similar, but faster: start dive at John (and include him again).
+WITH RECURSIVE employees_extended
+AS
+(
+SELECT ID, NAME, CAST(ID AS CHAR(200)) AS PATH
+FROM employees
+WHERE NAME='John'
+UNION ALL
+SELECT S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID)
+FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID
+)
+SELECT * FROM employees_extended;
+ID	NAME	PATH
+198	John	198
+29	Pedro	198,29
+4610	Sarah	198,29,4610
+72	Pierre	198,29,72
+# Get the management chain above Pierre:
+WITH RECURSIVE employees_extended
+AS
+(
+SELECT ID, NAME, MANAGER_ID, CAST(ID AS CHAR(200)) AS PATH
+FROM employees
+WHERE NAME='Pierre'
+UNION ALL
+SELECT S.ID, S.NAME, S.MANAGER_ID, CONCAT(M.PATH, ",", S.ID)
+FROM employees_extended M JOIN employees S ON M.MANAGER_ID=S.ID
+)
+SELECT * FROM employees_extended;
+ID	NAME	MANAGER_ID	PATH
+72	Pierre	29	72
+29	Pedro	198	72,29
+198	John	333	72,29,198
+333	Yasmina	NULL	72,29,198,333
+# Get the management chain above Pierre, without PATH
+WITH RECURSIVE employees_extended
+AS
+(
+SELECT ID, NAME, MANAGER_ID
+FROM employees
+WHERE NAME='Pierre'
+UNION ALL
+SELECT S.ID, S.NAME, S.MANAGER_ID
+FROM employees_extended M JOIN employees S ON M.MANAGER_ID=S.ID
+)
+SELECT * FROM employees_extended;
+ID	NAME	MANAGER_ID
+72	Pierre	29
+29	Pedro	198
+198	John	333
+333	Yasmina	NULL
+# Get the management chain above Pierre and Sarah, without PATH
+WITH RECURSIVE employees_extended
+AS
+(
+SELECT ID, NAME, MANAGER_ID
+FROM employees
+WHERE NAME='Pierre' OR NAME='Sarah'
+UNION ALL
+SELECT S.ID, S.NAME, S.MANAGER_ID
+FROM employees_extended M JOIN employees S ON M.MANAGER_ID=S.ID
+)
+SELECT * FROM employees_extended;
+ID	NAME	MANAGER_ID
+4610	Sarah	29
+72	Pierre	29
+29	Pedro	198
+29	Pedro	198
+198	John	333
+198	John	333
+333	Yasmina	NULL
+333	Yasmina	NULL
+# Do it without duplicates
+WITH RECURSIVE employees_extended
+AS
+(
+SELECT ID, NAME, MANAGER_ID
+FROM employees
+WHERE NAME='Pierre' OR NAME='Sarah'
+UNION
+SELECT S.ID, S.NAME, S.MANAGER_ID
+FROM employees_extended M JOIN employees S ON M.MANAGER_ID=S.ID
+)
+SELECT * FROM employees_extended;
+ID	NAME	MANAGER_ID
+4610	Sarah	29
+72	Pierre	29
+29	Pedro	198
+198	John	333
+333	Yasmina	NULL
+# Cycles. Introduce an oddity:
+# Sarah is indirect report of John and is his manager.
+UPDATE employees SET MANAGER_ID=4610 WHERE NAME="John";
+# Add cycle detection: the row closing a cycle is marked with
+# IS_CYCLE=1, which stops the iterations. The outer SELECT
+# could then want to see only that row, or only previous ones.
+WITH RECURSIVE employees_extended(ID, NAME, PATH, IS_CYCLE)
+AS
+(
+SELECT ID, NAME, CAST(ID AS CHAR(200)), 0
+FROM employees
+WHERE NAME='John'
+UNION ALL
+SELECT S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID), FIND_IN_SET(S.ID, M.PATH)
+FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID
+WHERE M.IS_CYCLE=0
+)
+SELECT * FROM employees_extended;
+ID	NAME	PATH	IS_CYCLE
+198	John	198	0
+29	Pedro	198,29	0
+4610	Sarah	198,29,4610	0
+72	Pierre	198,29,72	0
+198	John	198,29,4610,198	1
+DROP TABLE employees;
+# Two recursive members.
+CREATE TABLE t1 (id int, name char(10), leftpar int, rightpar int);
+INSERT INTO t1 VALUES
+(1, "A", 2, 3),
+(2, "LA", 4, 5),
+(4, "LLA", 6, 7),
+(6, "LLLA", NULL, NULL),
+(7, "RLLA", NULL, NULL),
+(5, "RLA", 8, 9),
+(8, "LRLA", NULL, NULL),
+(9, "RRLA", NULL, NULL),
+(3, "RA", 10, 11),
+(10, "LRA", 12, 13),
+(11, "RRA", 14, 15),
+(15, "RRRA", NULL, NULL),
+(16, "B", 17, 18),
+(17, "LB", NULL, NULL),
+(18, "RB", NULL, NULL)
+;
+# Shuffle rows to make sure the algorithm works
+# with any read order of rows above
+CREATE TABLE t2 SELECT * FROM t1 ORDER BY rand();
+# Tree-walking query. We turn off the Query Cache: indeed
+# sometimes pb2 enables Query Cache and as we run twice the
+# same query the 2nd may not actually be executed so the value
+# of Created_tmp_tables displayed at end becomes "one less").
+# Note that without ORDER BY, order of rows would be random as BNL
+# implies that the randomized t2 is the driving table in the
+# joining of rows.
+EXPLAIN extended WITH RECURSIVE tree_of_a AS
+(
+SELECT *, cast(id AS char(200)) AS path FROM t2 WHERE name="A"
+UNION ALL
+SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON
+t2.id=tree_of_a.leftpar
+UNION ALL
+SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON
+t2.id=tree_of_a.rightpar
+)
+SELECT * FROM tree_of_a ORDER BY path;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	15	#	Using filesort
+2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	15	#	Using where
+3	RECURSIVE UNION	t2	ALL	NULL	NULL	NULL	NULL	15	#	Using where
+3	RECURSIVE UNION	<derived2>	ref	key0	key0	5	test.t2.id	2	#	
+4	RECURSIVE UNION	t2	ALL	NULL	NULL	NULL	NULL	15	#	Using where
+4	RECURSIVE UNION	<derived2>	ref	key0	key0	5	test.t2.id	2	#	
+NULL	UNION RESULT	<union2,3,4>	ALL	NULL	NULL	NULL	NULL	NULL	#	
+Warnings:
+Note	1003	with recursive tree_of_a as (select `test`.`t2`.`id` AS `id`,`test`.`t2`.`name` AS `name`,`test`.`t2`.`leftpar` AS `leftpar`,`test`.`t2`.`rightpar` AS `rightpar`,cast(`test`.`t2`.`id` as char(200) charset latin1) AS `path` from `test`.`t2` where `test`.`t2`.`name` = 'A' union all select `test`.`t2`.`id` AS `id`,`test`.`t2`.`name` AS `name`,`test`.`t2`.`leftpar` AS `leftpar`,`test`.`t2`.`rightpar` AS `rightpar`,concat(`tree_of_a`.`path`,',',`test`.`t2`.`id`) AS `concat(tree_of_a.path,",",t2.id)` from `test`.`t2` join `tree_of_a` where `tree_of_a`.`leftpar` = `test`.`t2`.`id` union all select `test`.`t2`.`id` AS `id`,`test`.`t2`.`name` AS `name`,`test`.`t2`.`leftpar` AS `leftpar`,`test`.`t2`.`rightpar` AS `rightpar`,concat(`tree_of_a`.`path`,',',`test`.`t2`.`id`) AS `concat(tree_of_a.path,",",t2.id)` from `test`.`t2` join `tree_of_a` where `tree_of_a`.`rightpar` = `test`.`t2`.`id`)select `tree_of_a`.`id` AS `id`,`tree_of_a`.`name` AS `name`,`tree_of_a`.`leftpar` AS `leftpar`,`tree_of_a`.`rightpar` AS `rightpar`,`tree_of_a`.`path` AS `path` from `tree_of_a` order by `tree_of_a`.`path`
+WITH RECURSIVE tree_of_a AS
+(
+SELECT *, cast(id AS char(200)) AS path FROM t2 WHERE name="A"
+UNION ALL
+SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON
+t2.id=tree_of_a.leftpar
+UNION ALL
+SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON
+t2.id=tree_of_a.rightpar
+)
+SELECT * FROM tree_of_a ORDER BY path;
+id	name	leftpar	rightpar	path
+1	A	2	3	1
+2	LA	4	5	1,2
+4	LLA	6	7	1,2,4
+6	LLLA	NULL	NULL	1,2,4,6
+7	RLLA	NULL	NULL	1,2,4,7
+5	RLA	8	9	1,2,5
+8	LRLA	NULL	NULL	1,2,5,8
+9	RRLA	NULL	NULL	1,2,5,9
+3	RA	10	11	1,3
+10	LRA	12	13	1,3,10
+11	RRA	14	15	1,3,11
+15	RRRA	NULL	NULL	1,3,11,15
+# Equivalent query with one single recursive query block:
+WITH RECURSIVE tree_of_a AS
+(
+SELECT *, cast(id AS char(200)) AS path FROM t2 WHERE name="A"
+UNION ALL
+SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON
+(t2.id=tree_of_a.leftpar OR t2.id=tree_of_a.rightpar)
+)
+SELECT * FROM tree_of_a
+ORDER BY path;
+id	name	leftpar	rightpar	path
+1	A	2	3	1
+2	LA	4	5	1,2
+4	LLA	6	7	1,2,4
+6	LLLA	NULL	NULL	1,2,4,6
+7	RLLA	NULL	NULL	1,2,4,7
+5	RLA	8	9	1,2,5
+8	LRLA	NULL	NULL	1,2,5,8
+9	RRLA	NULL	NULL	1,2,5,9
+3	RA	10	11	1,3
+10	LRA	12	13	1,3,10
+11	RRA	14	15	1,3,11
+15	RRRA	NULL	NULL	1,3,11,15
+# Demonstrate a case where an index is automatically created on
+# the derived table and used to read this table in the outer
+# query (but correctly not used to read it in the recursive
+# query).
+EXPLAIN WITH RECURSIVE tree_of_a AS
+(
+SELECT *, cast(id AS char(200)) AS path FROM t2 WHERE name="A"
+UNION ALL
+SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON
+t2.id=tree_of_a.leftpar
+UNION ALL
+SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON
+t2.id=tree_of_a.rightpar
+)
+SELECT * FROM tree_of_a WHERE id=2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	15	#
+2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	15	#
+3	RECURSIVE UNION	t2	ALL	NULL	NULL	NULL	NULL	15	#
+3	RECURSIVE UNION	<derived2>	ref	key0	key0	5	test.t2.id	2	#
+4	RECURSIVE UNION	t2	ALL	NULL	NULL	NULL	NULL	15	#
+4	RECURSIVE UNION	<derived2>	ref	key0	key0	5	test.t2.id	2	#
+NULL	UNION RESULT	<union2,3,4>	ALL	NULL	NULL	NULL	NULL	NULL	#
+WITH RECURSIVE tree_of_a AS
+(
+SELECT *, cast(id AS char(200)) AS path FROM t2 WHERE name="A"
+UNION ALL
+SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON
+t2.id=tree_of_a.leftpar
+UNION ALL
+SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON
+t2.id=tree_of_a.rightpar
+)
+SELECT * FROM tree_of_a WHERE id=2;
+id	name	leftpar	rightpar	path
+2	LA	4	5	1,2
+DROP TABLE t1,t2;
+EXPLAIN WITH RECURSIVE cte AS
+(
+SELECT 1 AS n UNION ALL
+SELECT n+1 FROM cte WHERE n<10000
+)
+SELECT sum(cte1.n*cte2.n*cte3.n)=2490508525950000
+FROM cte cte1, cte cte2, cte cte3
+WHERE cte1.n=cte2.n+10 AND cte2.n+20=cte3.n;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	#
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	#
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	#
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	#
+3	RECURSIVE UNION	<derived2>	ALL	NULL	NULL	NULL	NULL	2	#
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	#
+WITH RECURSIVE cte AS
+(
+SELECT 1 AS n UNION ALL
+SELECT n+1 FROM cte WHERE n<10000
+)
+SELECT sum(cte1.n*cte2.n*cte3.n)=2490508525950000
+FROM cte cte1, cte cte2, cte cte3
+WHERE cte1.n=cte2.n+10 AND cte2.n+20=cte3.n;
+sum(cte1.n*cte2.n*cte3.n)=2490508525950000
+1
+#
+# Transitive closure
+#
+CREATE TABLE nodes(id int);
+CREATE TABLE arcs(from_id int, to_id int);
+INSERT INTO nodes VALUES(1),(2),(3),(4),(5),(6),(7),(8);
+INSERT INTO arcs VALUES(1,3), (3,6), (1,4), (4,6), (6,2), (2,1);
+# UNION ALL leads to infinite loop as 1 is reachable from 1;
+# so we stop it with a maximum depth 8 (8 nodes in graph)
+WITH RECURSIVE cte AS
+(
+SELECT id, 0 AS depth FROM nodes WHERE id=1
+UNION ALL
+SELECT to_id, depth+1 FROM arcs, cte
+WHERE from_id=cte.id AND depth<8
+)
+SELECT count(*), max(depth) FROM cte;
+count(*)	max(depth)
+25	8
+# Can use cycle detection:
+WITH RECURSIVE cte AS
+(
+SELECT id, cast(id AS char(200)) AS path, 0 AS is_cycle
+FROM nodes WHERE id=1
+UNION ALL
+SELECT to_id, concat(cte.path, ",", to_id), find_in_set(to_id, path)
+FROM arcs, cte
+WHERE from_id=cte.id AND is_cycle=0
+)
+SELECT * FROM cte;
+id	path	is_cycle
+1	1	0
+3	1,3	0
+4	1,4	0
+6	1,3,6	0
+6	1,4,6	0
+2	1,4,6,2	0
+2	1,3,6,2	0
+1	1,3,6,2,1	1
+1	1,4,6,2,1	1
+# It is simpler with DISTINCT:
+WITH RECURSIVE cte AS
+(
+SELECT id FROM nodes WHERE id=1
+UNION
+SELECT to_id FROM arcs, cte WHERE from_id=cte.id
+)
+SELECT * FROM cte;
+id
+1
+3
+4
+6
+2
+DROP TABLE nodes, arcs;
+# Hash field and MEMORY don't work together. Make long distinct
+# key to force hash field, to see if it switches to InnoDB.
+# Not too long key (500 bytes in latin1)
+WITH RECURSIVE cte AS
+(
+SELECT 1 AS n,
+repeat('a',500) AS f, '' AS g,
+'' AS h, '' AS i
+UNION
+SELECT n+1,
+'','','',''
+  FROM cte WHERE n<100)
+SELECT sum(n) FROM cte;
+sum(n)
+5050
+SHOW STATUS LIKE 'Created_tmp_disk_tables';
+Variable_name	Value
+Created_tmp_disk_tables	0
+# Too long key (>3000 bytes in latin1)
+WITH RECURSIVE cte AS
+(
+SELECT 1 AS n,
+repeat('a',500) AS f, repeat('a',500) AS g,
+repeat('a',500) AS h, repeat('a',500) AS i,
+repeat('a',500) AS j, repeat('a',500) AS k,
+repeat('a',500) AS l, repeat('a',500) AS m
+UNION
+SELECT n+1,
+'','','','','','','',''
+  FROM cte WHERE n<100)
+SELECT sum(n) FROM cte;
+sum(n)
+5050
+#
+# In query planning, the recursive reference's row count is
+# said to be the estimated row count of all non-recursive query
+# blocks
+CREATE TABLE t1(a int);
+# 15 rows:
+INSERT INTO t1 VALUES (), (), (), (), (), (), (), (), (), (), (), (),
+(), (), ();
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+# EXPLAIN says: in non-recursive QB we'll read 15 rows of t1,
+# in recursive QB we'll read 15 rows of qn, keep only 0.33
+# due to WHERE, that makes 4 (due to rounding), and in the
+# derived table we'll thus have 15+4=19. That ignores
+# next repetitions of the recursive QB which are unpredictable.
+EXPLAIN WITH RECURSIVE qn AS
+(SELECT 1 AS a FROM t1 UNION ALL SELECT a+1 FROM qn WHERE qn.a<100)
+SELECT * FROM qn;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	15	
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	15	
+3	RECURSIVE UNION	<derived2>	ALL	NULL	NULL	NULL	NULL	15	Using where
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+EXPLAIN WITH RECURSIVE qn AS
+(SELECT 1 AS a FROM t1 UNION DISTINCT SELECT a+1 FROM qn WHERE qn.a<100)
+SELECT * FROM qn;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	15	
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	15	
+3	RECURSIVE UNION	<derived2>	ALL	NULL	NULL	NULL	NULL	15	Using where
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+DROP TABLE t1;
+SHOW STATUS LIKE 'Created_tmp_disk_tables';
+Variable_name	Value
+Created_tmp_disk_tables	0
+flush STATUS;
+WITH RECURSIVE q (b) AS
+(SELECT 1 UNION ALL SELECT 1+b FROM q WHERE b<2000)
+SELECT min(b),max(b),avg(b) FROM q;
+min(b)	max(b)	avg(b)
+1	2000	1000.5000
+SHOW STATUS LIKE 'Created_tmp_disk_tables';
+Variable_name	Value
+Created_tmp_disk_tables	0
+# Test when conversion to InnoDB affects recursive references which
+# are not open yet (those of q1):
+flush STATUS;
+WITH RECURSIVE q (b) AS
+(SELECT 1 UNION ALL SELECT 1+b FROM q WHERE b<2000)
+SELECT min(q.b),max(q.b),avg(q.b) FROM q, q AS q1;
+min(q.b)	max(q.b)	avg(q.b)
+1	2000	1000.5000
+SHOW STATUS LIKE 'Created_tmp_disk_tables';
+Variable_name	Value
+Created_tmp_disk_tables	0
+# Same, but make q1 the writer; this is to test overflow when
+# the writer isn't first in the 'tmp_tables' list
+flush STATUS;
+WITH RECURSIVE q (b) AS
+(SELECT 1 UNION ALL SELECT 1+b FROM q WHERE b<2000)
+SELECT min(q.b),max(q.b),avg(q.b) FROM q RIGHT JOIN q AS q1 ON 1;
+min(q.b)	max(q.b)	avg(q.b)
+1	2000	1000.5000
+SHOW STATUS LIKE 'Created_tmp_disk_tables';
+Variable_name	Value
+Created_tmp_disk_tables	0
+# Test when outer query reads CTE with an index.
+# Overflow doesn't happen at same row as queries above, as this
+# table has an index which makes it grow faster.
+EXPLAIN WITH RECURSIVE q (b) AS
+(SELECT 1 UNION ALL SELECT 1+b FROM q WHERE b<2000)
+SELECT min(b),max(b),avg(b) FROM q WHERE b=300;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	RECURSIVE UNION	<derived2>	ALL	NULL	NULL	NULL	NULL	2	Using where
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+SHOW STATUS LIKE 'Created_tmp_disk_tables';
+Variable_name	Value
+Created_tmp_disk_tables	0
+WITH RECURSIVE q (b) AS
+(SELECT 1 UNION ALL SELECT 1+b FROM q WHERE b<2000)
+SELECT min(b),max(b),avg(b) FROM q WHERE b=300;
+min(b)	max(b)	avg(b)
+300	300	300.0000
+SHOW STATUS LIKE 'Created_tmp_disk_tables';
+Variable_name	Value
+Created_tmp_disk_tables	0
+# Verify that rows come out in insertion order.
+# If they didn't, the sequences of @c and of 'b'
+# would not be identical and the sum wouldn't be
+# 1^2 + ... + 2000^2 = n(n+1)(2n+1)/6 = 2668667000
+SET @c:=1;
+flush STATUS;
+WITH RECURSIVE q (b, c) AS
+(SELECT 1, 1 UNION ALL SELECT (1+b), (@c:=(@c+1)) FROM q WHERE b<2000)
+SELECT sum(b*c) FROM q;
+sum(b*c)
+2668667000
+SHOW STATUS LIKE 'Created_tmp_disk_tables';
+Variable_name	Value
+Created_tmp_disk_tables	0
+#
+# Bug#23495283 WL3634:ASSERTION `0' FAILED IN FIELD* ITEM::TMP_TABLE_FIELD_FROM_FIELD_TYPE
+#
+CREATE TABLE t1(c1 DATETIME, c2 INT, KEY(c1));
+WITH RECURSIVE cte AS ( SELECT a.c1 AS field1, 0 AS cycle FROM (t1 AS a)
+UNION ALL SELECT b.c2 FROM cte AS a JOIN t1 AS b) SELECT * FROM cte;
+ERROR 21000: The used SELECT statements have a different number of columns
+DROP TABLE t1;
+#
+# Bug#23645090 WL3634: INVALID WRITE AND READ VALGRIND ERRORS
+#
+CREATE TABLE A (
+col_date date DEFAULT NULL,
+col_datetime_key datetime DEFAULT NULL,
+col_time_key time DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_int_key int(11) DEFAULT NULL,
+col_blob_key blob,
+col_varchar varchar(1) DEFAULT NULL,
+col_date_key date DEFAULT NULL,
+col_time time DEFAULT NULL,
+col_blob blob,
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_int int(11) DEFAULT NULL,
+col_datetime datetime DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_datetime_key (col_datetime_key),
+KEY col_time_key (col_time_key),
+KEY col_varchar_key (col_varchar_key),
+KEY col_int_key (col_int_key),
+KEY col_blob_key (col_blob_key(255)),
+KEY col_date_key (col_date_key)
+) DEFAULT CHARSET=latin1;
+CREATE TABLE AA (
+col_varchar varchar(1) DEFAULT NULL,
+col_date date DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_date_key date DEFAULT NULL,
+col_datetime_key datetime DEFAULT NULL,
+col_time_key time DEFAULT NULL,
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_time time DEFAULT NULL,
+col_int_key int(11) DEFAULT NULL,
+col_datetime datetime DEFAULT NULL,
+col_int int(11) DEFAULT NULL,
+col_blob blob,
+col_blob_key blob,
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key),
+KEY col_date_key (col_date_key),
+KEY col_datetime_key (col_datetime_key),
+KEY col_time_key (col_time_key),
+KEY col_int_key (col_int_key),
+KEY col_blob_key (col_blob_key(255))
+) DEFAULT CHARSET=latin1;
+CREATE TABLE BB (
+col_date date DEFAULT NULL,
+col_blob_key blob,
+col_time time DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar varchar(1) DEFAULT NULL,
+col_blob blob,
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_int_key int(11) DEFAULT NULL,
+col_datetime datetime DEFAULT NULL,
+col_time_key time DEFAULT NULL,
+col_datetime_key datetime DEFAULT NULL,
+col_date_key date DEFAULT NULL,
+col_int int(11) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_blob_key (col_blob_key(255)),
+KEY col_varchar_key (col_varchar_key),
+KEY col_int_key (col_int_key),
+KEY col_time_key (col_time_key),
+KEY col_datetime_key (col_datetime_key),
+KEY col_date_key (col_date_key)
+) AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
+CREATE TABLE D (
+col_varchar_key varchar(1) DEFAULT NULL,
+col_datetime datetime DEFAULT NULL,
+col_date_key date DEFAULT NULL,
+col_int int(11) DEFAULT NULL,
+col_time time DEFAULT NULL,
+col_blob blob,
+col_int_key int(11) DEFAULT NULL,
+col_blob_key blob,
+col_varchar varchar(1) DEFAULT NULL,
+col_datetime_key datetime DEFAULT NULL,
+col_date date DEFAULT NULL,
+col_time_key time DEFAULT NULL,
+pk int(11) NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key),
+KEY col_date_key (col_date_key),
+KEY col_int_key (col_int_key),
+KEY col_blob_key (col_blob_key(255)),
+KEY col_datetime_key (col_datetime_key),
+KEY col_time_key (col_time_key)
+) DEFAULT CHARSET=latin1;
+WITH RECURSIVE cte AS (
+SELECT    alias1 . `col_blob_key` AS field1, 0 AS cycle
+FROM ( BB AS alias1 , ( D AS alias2 , AA AS alias3 ) )
+WHERE (
+alias1 . pk = 225
+OR ( alias1 . col_int_key = 69 AND alias1 . col_blob_key = 'p' )
+)
+UNION ALL
+SELECT t1.pk, t2.cycle
+FROM cte AS t2 JOIN A AS t1
+WHERE t2.field1 = t1.`col_int_key`
+AND t2.cycle =1 ) SELECT  * FROM cte;
+field1	cycle
+DROP TABLE IF EXISTS A, AA, BB, D;
+#
+# Bug#24962600 WL3634: SIG 11 IN HEAP_RRND AT STORAGE/HEAP/HP_RRND.C
+#
+create table t1(a int);
+with recursive cte as (select * from t1 union select * from cte)
+select * from cte;
+a
+insert into t1 values(1),(2);
+with recursive cte as (select * from t1 where 0 union select * from cte)
+select * from cte;
+a
+with recursive cte as (select * from t1 where a>3 union select * from cte)
+select * from cte;
+a
+drop table t1;
+#
+# Bug#26501463 WL10792: ASSERTION `!TABLE->HAS_NULL_ROW()' FAILED
+#
+CREATE TABLE D (col_int INT);
+CREATE TABLE C (
+col_int2 INT,
+pk INT NOT NULL,
+col_int INT,
+PRIMARY KEY (pk)
+);
+INSERT INTO C VALUES
+(7,1,3),(7,2,3),(5,3,4),(1,4,6),(5,5,2),
+(5,6,9),(4,7,9),(7,8,3),(3,9,0),(5,10,3);
+CREATE TABLE BB (
+pk INT NOT NULL,
+col_int INT,
+PRIMARY KEY (pk)
+);
+INSERT INTO BB VALUES (1,0),(2,6),(3,2),(4,5),(5,0);
+WITH RECURSIVE cte AS (
+SELECT alias2 . col_int2 AS field1 FROM
+D AS alias1 RIGHT  JOIN
+( ( C AS alias2 LEFT  JOIN BB AS alias3
+ON (( alias3 . pk = alias2 . col_int ) AND ( alias3 . pk = alias2 . pk ) ) ) )
+ON (alias3 . col_int <> alias2 . col_int2 )
+HAVING field1 <= 0
+UNION
+SELECT cte.field1 FROM cte
+)
+SELECT * FROM cte;
+field1
+DROP TABLE BB,C,D;
+#
+# Bug#26556025 ASSERTION `!SELECT_LEX->IS_RECURSIVE() || !TMP_TABLES' FAILED.
+#
+SET SQL_BUFFER_RESULT = 1;
+WITH RECURSIVE cte AS
+(SELECT 1 AS n UNION SELECT n+1 FROM cte WHERE n<3)
+SELECT * FROM cte;
+n
+1
+2
+3
+WITH RECURSIVE cte AS
+(SELECT 1 AS n UNION ALL SELECT n+1 FROM cte WHERE n<3)
+SELECT * FROM cte;
+n
+1
+2
+3
+SET SQL_BUFFER_RESULT = DEFAULT;
+# On-disk tmp tables
+set big_tables=1;
+SET @@max_recursive_iterations = 10000;
+flush STATUS;
+# Mutual recursion unsupported; cycles must have one node only
+WITH RECURSIVE qn AS (SELECT * FROM qn2),
+qn2 AS (SELECT * FROM qn)
+SELECT * FROM qn;
+ERROR HY000: No anchors for recursive WITH element 'qn'
+# At least one anchor member, all anchors before all recursive
+WITH RECURSIVE qn AS
+(SELECT 1 FROM qn)
+SELECT count(*) FROM qn;
+ERROR HY000: No anchors for recursive WITH element 'qn'
+WITH RECURSIVE qn AS
+(SELECT 1 UNION ALL SELECT 1 FROM qn UNION ALL SELECT 1)
+SELECT count(*) FROM qn;
+count(*)
+20002
+WITH RECURSIVE qn AS
+(SELECT 1 FROM qn UNION ALL SELECT 1 FROM qn)
+SELECT * FROM qn;
+ERROR HY000: No anchors for recursive WITH element 'qn'
+# It's ok to have the RECURSIVE word without any recursive member
+WITH RECURSIVE qn AS
+(SELECT 1 FROM dual UNION ALL
+SELECT 1 FROM dual)
+SELECT * FROM qn;
+1
+1
+1
+# UNION DISTINCT allowed
+WITH RECURSIVE qn AS
+(SELECT 1 FROM dual UNION
+SELECT 1 FROM qn)
+SELECT * FROM qn;
+1
+1
+# No aggregation on the QN
+CREATE TABLE t1(b int);
+INSERT INTO t1 VALUES(10),(20),(10);
+WITH RECURSIVE qn AS
+(SELECT max(b) AS a FROM t1 UNION
+SELECT a FROM qn)
+SELECT * FROM qn;
+a
+20
+WITH RECURSIVE qn AS
+(SELECT b AS a FROM t1 UNION
+SELECT max(a) FROM qn)
+SELECT * FROM qn;
+ERROR HY000: Restrictions imposed on recursive definitions are violated for table 'qn'
+# No window functions
+WITH RECURSIVE qn AS
+(SELECT rank() over (ORDER BY b) AS a FROM t1 UNION
+SELECT a FROM qn)
+SELECT * FROM qn;
+a
+1
+3
+WITH RECURSIVE qn AS
+(SELECT b AS a FROM t1 UNION
+SELECT rank() over (ORDER BY a) FROM qn)
+SELECT * FROM qn;
+ERROR HY000: Restrictions imposed on recursive definitions are violated for table 'qn'
+DROP TABLE t1;
+WITH RECURSIVE qn AS
+(SELECT 1 AS a FROM dual UNION ALL
+SELECT max(a) FROM qn)
+SELECT * FROM qn;
+ERROR HY000: Restrictions imposed on recursive definitions are violated for table 'qn'
+WITH RECURSIVE qn AS
+(SELECT 1 AS a FROM dual GROUP BY a UNION ALL
+SELECT a+1 FROM qn WHERE a<3)
+SELECT * FROM qn;
+a
+1
+2
+3
+WITH RECURSIVE qn AS
+(SELECT 1 AS a FROM dual UNION ALL
+SELECT a FROM qn GROUP BY a)
+SELECT count(*)FROM qn;
+count(*)
+10001
+# No subquery referencing a QN
+WITH RECURSIVE qn AS (
+SELECT 1 FROM dual UNION ALL
+SELECT 1 FROM dual WHERE 1 NOT in(SELECT * FROM qn))
+SELECT * FROM qn;
+ERROR HY000: Restrictions imposed on recursive definitions are violated for table 'qn'
+WITH RECURSIVE qn AS (
+SELECT 1 FROM dual UNION ALL
+SELECT 1 FROM qn
+ORDER BY (SELECT * FROM qn))
+SELECT count(*) FROM qn;
+ERROR 42000: This version of MariaDB doesn't yet support 'global ORDER_BY/LIMIT in recursive CTE spec'
+WITH RECURSIVE qn AS (
+SELECT 1 FROM dual UNION ALL
+SELECT * FROM (SELECT * FROM qn) AS dt)
+SELECT count(*) FROM qn;
+count(*)
+10001
+WITH RECURSIVE qn AS (
+SELECT 1 AS a FROM dual UNION ALL
+SELECT 1 FROM qn
+ORDER BY a)
+SELECT count(*) FROM qn;
+ERROR 42000: This version of MariaDB doesn't yet support 'global ORDER_BY/LIMIT in recursive CTE spec'
+# No matter if global, or attached to one recursive member.
+WITH RECURSIVE qn AS (
+SELECT 1 AS a FROM dual UNION ALL
+(SELECT 1 FROM qn ORDER BY a))
+SELECT count(*) FROM qn;
+count(*)
+10001
+# Allowed on non-recursive query block (though pointless)
+WITH RECURSIVE qn AS (
+(SELECT 1 AS a FROM dual ORDER BY a) UNION ALL
+SELECT a+1 FROM qn WHERE a<3)
+SELECT * FROM qn;
+a
+1
+2
+3
+WITH RECURSIVE qn AS (
+SELECT 1 AS a FROM dual UNION ALL
+SELECT 1 FROM qn
+LIMIT 10)
+SELECT count(*) FROM qn;
+ERROR 42000: This version of MariaDB doesn't yet support 'global ORDER_BY/LIMIT in recursive CTE spec'
+WITH RECURSIVE qn AS (
+SELECT 1 AS a FROM dual UNION ALL
+(SELECT 1 FROM qn LIMIT 10))
+SELECT count(*) FROM qn;
+count(*)
+10001
+WITH RECURSIVE qn AS
+(SELECT 1 UNION ALL SELECT DISTINCT 3 FROM qn)
+SELECT count(*) FROM qn;
+count(*)
+10001
+WITH RECURSIVE qn AS (SELECT 1 FROM dual UNION ALL
+SELECT 1 FROM dual
+WHERE 1 NOT in(SELECT * FROM qn))
+SELECT * FROM qn;
+ERROR HY000: Restrictions imposed on recursive definitions are violated for table 'qn'
+# Numbers from 123 to 130:
+WITH RECURSIVE qn AS (SELECT 123 AS a UNION ALL SELECT 1+a FROM qn WHERE a<130) SELECT * FROM qn;
+a
+123
+124
+125
+126
+127
+128
+129
+130
+# One-level recursive sequence of numbers
+WITH RECURSIVE qn AS (SELECT 1 AS n, 2 AS un UNION ALL SELECT 1+n, un*5-6 FROM qn WHERE n<10) SELECT * FROM qn;
+n	un
+1	2
+2	4
+3	14
+4	64
+5	314
+6	1564
+7	7814
+8	39064
+9	195314
+10	976564
+# Fibonacci
+WITH RECURSIVE qn AS (SELECT 1 AS n, 1 AS un, 1 AS unp1 UNION ALL SELECT 1+n, unp1, un+unp1 FROM qn WHERE n<10) SELECT * FROM qn;
+n	un	unp1
+1	1	1
+2	1	2
+3	2	3
+4	3	5
+5	5	8
+6	8	13
+7	13	21
+8	21	34
+9	34	55
+10	55	89
+# Validate that cast(a_varchar as char) produces a varchar, not a
+# char.
+CREATE TABLE t(c char(3), vc varchar(3), b binary(3), vb varbinary(3));
+CREATE TABLE u
+SELECT cast(c AS char(4)), cast(vc AS char(4)),
+cast(b AS binary(4)), cast(vb AS binary(4)),
+"abc" AS literal_c, cast("abc" AS char(4)),
+_binary "abc" AS literal_b, cast(_binary "abc" AS binary(4))
+FROM t;
+SHOW CREATE TABLE u;
+Table	Create Table
+u	CREATE TABLE `u` (
+  `cast(c AS char(4))` varchar(4) DEFAULT NULL,
+  `cast(vc AS char(4))` varchar(4) DEFAULT NULL,
+  `cast(b AS binary(4))` varbinary(4) DEFAULT NULL,
+  `cast(vb AS binary(4))` varbinary(4) DEFAULT NULL,
+  `literal_c` varchar(3) NOT NULL,
+  `cast("abc" AS char(4))` varchar(4) DEFAULT NULL,
+  `literal_b` varbinary(3) NOT NULL,
+  `cast(_binary "abc" AS binary(4))` varbinary(4) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t,u;
+# if it used char the 'x' would fall off due to spaces.
+WITH RECURSIVE qn AS (SELECT 1 AS n, cast('x' AS char(100)) AS un UNION ALL SELECT 1+n, concat(un,'x') FROM qn WHERE n<10) SELECT * FROM qn;
+n	un
+1	x
+2	xx
+3	xxx
+4	xxxx
+5	xxxxx
+6	xxxxxx
+7	xxxxxxx
+8	xxxxxxxx
+9	xxxxxxxxx
+10	xxxxxxxxxx
+# String now growing at the left
+WITH RECURSIVE qn AS (SELECT cast("x" AS char(10)) AS a FROM dual
+UNION ALL SELECT concat("x",a) FROM qn WHERE length(a)<10) SELECT *
+FROM qn;
+a
+x
+xx
+xxx
+xxxx
+xxxxx
+xxxxxx
+xxxxxxx
+xxxxxxxx
+xxxxxxxxx
+xxxxxxxxxx
+# Forgot cast-as-char(10) in anchor => qn.a column has length 1
+# => concat() is cast as char(1) => truncation
+# => length is always 1 => infinite loop;
+WITH RECURSIVE qn AS (SELECT "x" AS a FROM dual
+UNION ALL SELECT concat("x",a) FROM qn WHERE length(a)<10) SELECT count(*)
+FROM qn;
+count(*)
+10001
+# Overflow integer type INT (max 4G)
+WITH RECURSIVE qn AS (SELECT 1 AS a FROM dual
+UNION ALL SELECT a*2000 FROM qn WHERE a<10000000000000000000) SELECT count(*) FROM qn;
+ERROR 22003: BIGINT value is out of range in '`qn`.`a` * 2000'
+# Use Decimal
+WITH RECURSIVE qn AS (SELECT cast(1 AS decimal(30,0)) AS a FROM dual
+UNION ALL SELECT a*2000 FROM qn WHERE a<10000000000000000000) SELECT * FROM qn;
+a
+1
+2000
+4000000
+8000000000
+16000000000000
+32000000000000000
+64000000000000000000
+WITH RECURSIVE qn AS (SELECT 123 AS a UNION #ALL 
+SELECT NULL FROM qn WHERE a IS NOT NULL) SELECT * FROM qn;
+a
+123
+0
+# Mixing really unrelated types: the goal is to report a sensible
+# error and not crash.
+# The Point becomes a string which is an invalid integer, cast string to int -> result 0:
+WITH RECURSIVE qn AS (
+SELECT 1 AS a,1
+UNION ALL
+SELECT a+1,ST_PointFromText('POINT(10 10)') FROM qn WHERE a<2)
+SELECT * FROM qn;
+a	1
+1	1
+2	0
+# POINT in anchor => BLOB in tmp table => not MEMORY engine => Innodb
+WITH RECURSIVE qn AS (
+SELECT 1 AS a,ST_PointFromText('POINT(10 10)')
+UNION ALL
+SELECT a+1,1 FROM qn WHERE a<2)
+SELECT * FROM qn;
+ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
+# Same number of columns in anchor and recursive members
+WITH RECURSIVE qn AS
+(
+SELECT 1
+UNION ALL
+SELECT 3, 0 FROM qn
+)
+SELECT * FROM qn;
+ERROR 21000: The used SELECT statements have a different number of columns
+# Mismatch in column name and column count; problem specific of
+# recursive CTE which creates tmp table earlier in preparation.
+WITH RECURSIVE q (b) AS (SELECT 1, 1 UNION ALL SELECT 1, 1 FROM q)
+SELECT b FROM q;
+ERROR HY000: WITH column list and SELECT field list have different column counts
+# Cannot have two recursive refs in FROM:
+WITH RECURSIVE qn AS (
+SELECT 123 AS a UNION ALL
+SELECT 1+qn.a FROM qn, qn AS qn1 WHERE qn1.a<130)
+SELECT * FROM qn;
+ERROR HY000: Restrictions imposed on recursive definitions are violated for table 'qn'
+# Prove that a materialized QN is shared among all references:
+flush STATUS;
+WITH RECURSIVE qn AS (
+SELECT 123 AS a UNION ALL
+SELECT 1+a FROM qn WHERE a<125)
+SELECT * FROM qn;
+a
+123
+124
+125
+SHOW STATUS LIKE "handler_write";
+Variable_name	Value
+Handler_write	0
+flush STATUS;
+WITH RECURSIVE qn AS (
+SELECT 123 AS a UNION ALL
+SELECT 1+a FROM qn WHERE a<125)
+SELECT * FROM qn, qn AS qn1;
+a	a
+123	123
+124	123
+125	123
+123	124
+124	124
+125	124
+123	125
+124	125
+125	125
+SHOW STATUS LIKE "handler_write";
+Variable_name	Value
+Handler_write	0
+SHOW STATUS LIKE 'Created_tmp%table%';
+Variable_name	Value
+Created_tmp_disk_tables	5
+Created_tmp_tables	5
+# Also works if references are nested inside other query names:
+flush STATUS;
+WITH RECURSIVE inner_ AS (
+SELECT 123 AS a UNION ALL
+SELECT 1+a FROM inner_ WHERE a<125),
+outer_ AS (SELECT * FROM inner_ LIMIT 10)
+SELECT * FROM outer_, outer_ AS outer1;
+a	a
+123	123
+124	123
+125	123
+123	124
+124	124
+125	124
+123	125
+124	125
+125	125
+SHOW STATUS LIKE "handler_write";
+Variable_name	Value
+Handler_write	0
+flush STATUS;
+WITH RECURSIVE inner_ AS (
+SELECT 123 AS a UNION ALL
+SELECT 1+a FROM inner_ WHERE a<125),
+outer_ AS
+(SELECT inner_.a, inner1.a AS a1
+FROM inner_, inner_ AS inner1 LIMIT 10)
+SELECT * FROM outer_;
+a	a1
+123	123
+124	123
+125	123
+123	124
+124	124
+125	124
+123	125
+124	125
+125	125
+SHOW STATUS LIKE "handler_write";
+Variable_name	Value
+Handler_write	0
+# Even if the two query names are recursive:
+flush STATUS;
+WITH RECURSIVE inner_ AS (
+SELECT 123 AS a UNION ALL
+SELECT 1+a FROM inner_ WHERE a<125),
+outer_ AS
+(SELECT a FROM inner_ UNION ALL
+SELECT a*2 FROM outer_ WHERE a<1000)
+SELECT a FROM outer_;
+a
+123
+124
+125
+246
+248
+250
+492
+496
+500
+984
+992
+1000
+1968
+1984
+SHOW STATUS LIKE "handler_write";
+Variable_name	Value
+Handler_write	0
+# Optimizer must be allowed to put the recursive reference first
+CREATE TABLE t1(a int);
+INSERT INTO t1 VALUES(1),(2);
+WITH RECURSIVE qn AS
+(
+SELECT 1 FROM t1
+UNION ALL
+SELECT 1 FROM t1 LEFT JOIN qn ON 1
+)
+SELECT * FROM qn;
+ERROR HY000: Restrictions imposed on recursive definitions are violated for table 'qn'
+# Empty anchor
+WITH RECURSIVE qn AS
+(
+SELECT a FROM t1 WHERE 0
+UNION ALL
+SELECT a+1 FROM qn
+)
+SELECT * FROM qn;
+a
+WITH RECURSIVE qn AS
+(
+SELECT a FROM t1 WHERE a>10
+UNION ALL
+SELECT a+1 FROM qn
+)
+SELECT * FROM qn;
+a
+# UNION DISTINCT in anchor parts 
+INSERT INTO t1 VALUES(1),(2);
+SET @c=0, @d=0;
+WITH RECURSIVE qn AS
+(
+SELECT 1,0 AS col FROM t1
+UNION DISTINCT
+SELECT 1,0 FROM t1
+UNION ALL
+SELECT 3, 0*(@c:=@c+1) FROM qn WHERE @c<1
+UNION ALL
+SELECT 3, 0*(@d:=@d+1) FROM qn WHERE @d<1
+)
+SELECT * FROM qn;
+ERROR 42000: This version of MariaDB doesn't yet support 'mix of ALL and DISTINCT UNION operations in recursive CTE spec'
+# UNION DISTINCT affecting recursive member, followed by UNION ALL
+INSERT INTO t1 VALUES(1),(2);
+SET @c=0, @d=0;
+WITH RECURSIVE qn AS
+(
+SELECT 1,0 AS col FROM t1
+UNION DISTINCT
+SELECT 3, 0*(@c:=@c+1) FROM qn WHERE @c<1
+UNION ALL
+SELECT 3, 0*(@d:=@d+1) FROM qn WHERE @d<1
+)
+SELECT * FROM qn;
+ERROR 42000: This version of MariaDB doesn't yet support 'mix of ALL and DISTINCT UNION operations in recursive CTE spec'
+# create select
+CREATE TABLE t2
+WITH RECURSIVE qn AS (
+SELECT 123 AS a UNION ALL SELECT 1+a FROM qn WHERE a<130)
+SELECT * FROM qn;
+SELECT * FROM t2;
+a
+123
+124
+125
+126
+127
+128
+129
+130
+DROP TABLE t2;
+# insert select
+DELETE FROM t1;
+INSERT INTO t1
+WITH RECURSIVE qn AS (
+SELECT 123 AS a UNION ALL SELECT 1+a FROM qn WHERE a<130)
+SELECT * FROM qn;
+SELECT * FROM t1;
+a
+123
+124
+125
+126
+127
+128
+129
+130
+# Using insertion target inside recursive query
+DELETE FROM t1;
+INSERT INTO t1 VALUES(1),(2);
+INSERT INTO t1
+WITH RECURSIVE qn AS (
+SELECT 123 AS a UNION ALL SELECT 1+qn.a FROM qn, t1 WHERE qn.a<125)
+SELECT * FROM qn;
+SELECT * FROM t1;
+a
+1
+2
+123
+124
+124
+125
+125
+125
+125
+DROP TABLE t1;
+# insert into tmp table (a likely use case)
+CREATE TEMPORARY TABLE t1(a int);
+INSERT INTO t1
+WITH RECURSIVE qn AS (
+SELECT 123 AS a UNION ALL SELECT 1+a FROM qn WHERE a<130)
+SELECT * FROM qn;
+SELECT * FROM t1;
+a
+123
+124
+125
+126
+127
+128
+129
+130
+DROP TABLE t1;
+# create view
+CREATE VIEW v1 AS
+WITH RECURSIVE qn AS (
+SELECT 123 AS a UNION ALL SELECT 1+a FROM qn WHERE a<130)
+SELECT * FROM qn;
+SELECT * FROM v1;
+a
+123
+124
+125
+126
+127
+128
+129
+130
+DROP VIEW v1;
+# Recursive QN can be constant (0-row or 1-row) for the
+# optimizer if its members have impossible conditions:
+# Recursive refs should never use indexes to read:
+# first, optimization of top query creates a key on q.b;
+# then optimization of scalar subquery, when it optimizes the
+# recursive member, must be prevented from re-using this key
+# (it was a bug that it re-used it, as the index is covering
+# and adjust_access_methods() has a heuristic which converts a
+# table scan to index scan, so it wrongly used an index scan).
+EXPLAIN WITH RECURSIVE q (b) AS
+(SELECT 1 UNION ALL SELECT 1+b FROM q WHERE b<10)
+SELECT (SELECT q1.b FROM q AS q2 WHERE q2.b=3) FROM q AS q1 WHERE q1.b=3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	Using where
+4	DEPENDENT SUBQUERY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	RECURSIVE UNION	<derived2>	ALL	NULL	NULL	NULL	NULL	2	Using where
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+WITH RECURSIVE q (b) AS
+(SELECT 1 UNION ALL SELECT 1+b FROM q WHERE b<10)
+SELECT (SELECT q1.b FROM q AS q2 WHERE q2.b=3) FROM q AS q1 WHERE q1.b=3;
+(SELECT q1.b FROM q AS q2 WHERE q2.b=3)
+3
+# This is from my blog so I can use it here.
+# Tests depth-first etc
+CREATE TABLE employees (
+ID INT PRIMARY KEY,
+NAME VARCHAR(100),
+MANAGER_ID INT,
+INDEX (MANAGER_ID),
+FOREIGN KEY (MANAGER_ID) REFERENCES employees(ID)
+);
+INSERT INTO employees VALUES
+(333, "Yasmina", NULL),
+(198, "John", 333),
+(692, "Tarek", 333),
+(29, "Pedro", 198),
+(4610, "Sarah", 29),
+(72, "Pierre", 29),
+(123, "Adil", 692);
+ANALYZE TABLE employees;
+Table	Op	Msg_type	Msg_text
+test.employees	analyze	status	OK
+# Depth-first.
+# Also test column names, and their reference in the recursive member.
+WITH RECURSIVE employees_extended(ID, NAME, PATH)
+AS
+(
+SELECT ID, NAME, CAST(ID AS CHAR(200))
+FROM employees
+WHERE MANAGER_ID IS NULL
+UNION ALL
+SELECT S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID)
+FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID
+)
+SELECT * FROM employees_extended ORDER BY PATH;
+ID	NAME	PATH
+333	Yasmina	333
+198	John	333,198
+29	Pedro	333,198,29
+4610	Sarah	333,198,29,4610
+72	Pierre	333,198,29,72
+692	Tarek	333,692
+123	Adil	333,692,123
+# Breadth-first is likely what we get, if no ordering
+WITH RECURSIVE employees_extended
+AS
+(
+SELECT ID, NAME, CAST(ID AS CHAR(200)) AS PATH
+FROM employees
+WHERE MANAGER_ID IS NULL
+UNION ALL
+SELECT S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID)
+FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID
+)
+SELECT * FROM employees_extended;
+ID	NAME	PATH
+333	Yasmina	333
+198	John	333,198
+692	Tarek	333,692
+29	Pedro	333,198,29
+123	Adil	333,692,123
+4610	Sarah	333,198,29,4610
+72	Pierre	333,198,29,72
+# But to be really sure we have breadth-first, we generate a
+# numeric column SEQ. And sort by NAME, to have repeatable
+# order of siblings (who have the same SEQ).
+WITH RECURSIVE employees_extended
+AS
+(
+SELECT 0 AS SEQ, ID, NAME, CAST(ID AS CHAR(200)) AS PATH
+FROM employees
+WHERE MANAGER_ID IS NULL
+UNION ALL
+SELECT M.SEQ+1, S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID)
+FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID
+)
+SELECT * FROM employees_extended ORDER BY SEQ, NAME;
+SEQ	ID	NAME	PATH
+0	333	Yasmina	333
+1	198	John	333,198
+1	692	Tarek	333,692
+2	123	Adil	333,692,123
+2	29	Pedro	333,198,29
+3	72	Pierre	333,198,29,72
+3	4610	Sarah	333,198,29,4610
+# Or, use a user variable, then all rows have different number:
+WITH RECURSIVE employees_extended
+AS
+(
+SELECT (@s:=0) AS SEQ, ID, NAME, CAST(ID AS CHAR(200)) AS PATH
+FROM employees
+WHERE MANAGER_ID IS NULL
+UNION ALL
+SELECT (@s:=@s+1), S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID)
+FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID
+)
+SELECT * FROM employees_extended ORDER BY SEQ;
+SEQ	ID	NAME	PATH
+0	333	Yasmina	333
+1	198	John	333,198
+2	692	Tarek	333,692
+3	29	Pedro	333,198,29
+4	123	Adil	333,692,123
+5	4610	Sarah	333,198,29,4610
+6	72	Pierre	333,198,29,72
+# Direct & indirect reports of John = having John in their PATH
+WITH RECURSIVE employees_extended
+AS
+(
+SELECT ID, NAME, CAST(ID AS CHAR(200)) AS PATH
+FROM employees
+WHERE MANAGER_ID IS NULL
+UNION ALL
+SELECT S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID)
+FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID
+)
+SELECT * FROM employees_extended
+WHERE FIND_IN_SET((SELECT ID FROM employees WHERE NAME='John'),
+PATH);
+ID	NAME	PATH
+198	John	333,198
+29	Pedro	333,198,29
+4610	Sarah	333,198,29,4610
+72	Pierre	333,198,29,72
+# Exclude John, he's not a report of himself;
+# bonus: use a QN to cache his ID.
+WITH RECURSIVE employees_extended(ID, NAME, PATH)
+AS
+(
+SELECT ID, NAME, CAST(ID AS CHAR(200))
+FROM employees
+WHERE MANAGER_ID IS NULL
+UNION ALL
+SELECT S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID)
+FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID
+),
+JOHN_ID AS (SELECT ID FROM employees WHERE NAME='John')
+SELECT e.* FROM employees_extended e, JOHN_ID
+WHERE FIND_IN_SET(JOHN_ID.ID,
+PATH)
+AND e.ID<>JOHN_ID.ID;
+ID	NAME	PATH
+29	Pedro	333,198,29
+4610	Sarah	333,198,29,4610
+72	Pierre	333,198,29,72
+# Similar, but faster: start dive at John (and include him again).
+WITH RECURSIVE employees_extended
+AS
+(
+SELECT ID, NAME, CAST(ID AS CHAR(200)) AS PATH
+FROM employees
+WHERE NAME='John'
+UNION ALL
+SELECT S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID)
+FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID
+)
+SELECT * FROM employees_extended;
+ID	NAME	PATH
+198	John	198
+29	Pedro	198,29
+4610	Sarah	198,29,4610
+72	Pierre	198,29,72
+# Get the management chain above Pierre:
+WITH RECURSIVE employees_extended
+AS
+(
+SELECT ID, NAME, MANAGER_ID, CAST(ID AS CHAR(200)) AS PATH
+FROM employees
+WHERE NAME='Pierre'
+UNION ALL
+SELECT S.ID, S.NAME, S.MANAGER_ID, CONCAT(M.PATH, ",", S.ID)
+FROM employees_extended M JOIN employees S ON M.MANAGER_ID=S.ID
+)
+SELECT * FROM employees_extended;
+ID	NAME	MANAGER_ID	PATH
+72	Pierre	29	72
+29	Pedro	198	72,29
+198	John	333	72,29,198
+333	Yasmina	NULL	72,29,198,333
+# Get the management chain above Pierre, without PATH
+WITH RECURSIVE employees_extended
+AS
+(
+SELECT ID, NAME, MANAGER_ID
+FROM employees
+WHERE NAME='Pierre'
+UNION ALL
+SELECT S.ID, S.NAME, S.MANAGER_ID
+FROM employees_extended M JOIN employees S ON M.MANAGER_ID=S.ID
+)
+SELECT * FROM employees_extended;
+ID	NAME	MANAGER_ID
+72	Pierre	29
+29	Pedro	198
+198	John	333
+333	Yasmina	NULL
+# Get the management chain above Pierre and Sarah, without PATH
+WITH RECURSIVE employees_extended
+AS
+(
+SELECT ID, NAME, MANAGER_ID
+FROM employees
+WHERE NAME='Pierre' OR NAME='Sarah'
+UNION ALL
+SELECT S.ID, S.NAME, S.MANAGER_ID
+FROM employees_extended M JOIN employees S ON M.MANAGER_ID=S.ID
+)
+SELECT * FROM employees_extended;
+ID	NAME	MANAGER_ID
+4610	Sarah	29
+72	Pierre	29
+29	Pedro	198
+29	Pedro	198
+198	John	333
+198	John	333
+333	Yasmina	NULL
+333	Yasmina	NULL
+# Do it without duplicates
+WITH RECURSIVE employees_extended
+AS
+(
+SELECT ID, NAME, MANAGER_ID
+FROM employees
+WHERE NAME='Pierre' OR NAME='Sarah'
+UNION
+SELECT S.ID, S.NAME, S.MANAGER_ID
+FROM employees_extended M JOIN employees S ON M.MANAGER_ID=S.ID
+)
+SELECT * FROM employees_extended;
+ID	NAME	MANAGER_ID
+4610	Sarah	29
+72	Pierre	29
+29	Pedro	198
+198	John	333
+333	Yasmina	NULL
+# Cycles. Introduce an oddity:
+# Sarah is indirect report of John and is his manager.
+UPDATE employees SET MANAGER_ID=4610 WHERE NAME="John";
+# Add cycle detection: the row closing a cycle is marked with
+# IS_CYCLE=1, which stops the iterations. The outer SELECT
+# could then want to see only that row, or only previous ones.
+WITH RECURSIVE employees_extended(ID, NAME, PATH, IS_CYCLE)
+AS
+(
+SELECT ID, NAME, CAST(ID AS CHAR(200)), 0
+FROM employees
+WHERE NAME='John'
+UNION ALL
+SELECT S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID), FIND_IN_SET(S.ID, M.PATH)
+FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID
+WHERE M.IS_CYCLE=0
+)
+SELECT * FROM employees_extended;
+ID	NAME	PATH	IS_CYCLE
+198	John	198	0
+29	Pedro	198,29	0
+4610	Sarah	198,29,4610	0
+72	Pierre	198,29,72	0
+198	John	198,29,4610,198	1
+DROP TABLE employees;
+# Two recursive members.
+CREATE TABLE t1 (id int, name char(10), leftpar int, rightpar int);
+INSERT INTO t1 VALUES
+(1, "A", 2, 3),
+(2, "LA", 4, 5),
+(4, "LLA", 6, 7),
+(6, "LLLA", NULL, NULL),
+(7, "RLLA", NULL, NULL),
+(5, "RLA", 8, 9),
+(8, "LRLA", NULL, NULL),
+(9, "RRLA", NULL, NULL),
+(3, "RA", 10, 11),
+(10, "LRA", 12, 13),
+(11, "RRA", 14, 15),
+(15, "RRRA", NULL, NULL),
+(16, "B", 17, 18),
+(17, "LB", NULL, NULL),
+(18, "RB", NULL, NULL)
+;
+# Shuffle rows to make sure the algorithm works
+# with any read order of rows above
+CREATE TABLE t2 SELECT * FROM t1 ORDER BY rand();
+# Tree-walking query. We turn off the Query Cache: indeed
+# sometimes pb2 enables Query Cache and as we run twice the
+# same query the 2nd may not actually be executed so the value
+# of Created_tmp_tables displayed at end becomes "one less").
+# Note that without ORDER BY, order of rows would be random as BNL
+# implies that the randomized t2 is the driving table in the
+# joining of rows.
+EXPLAIN extended WITH RECURSIVE tree_of_a AS
+(
+SELECT *, cast(id AS char(200)) AS path FROM t2 WHERE name="A"
+UNION ALL
+SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON
+t2.id=tree_of_a.leftpar
+UNION ALL
+SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON
+t2.id=tree_of_a.rightpar
+)
+SELECT * FROM tree_of_a ORDER BY path;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	15	#	Using filesort
+2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	15	#	Using where
+3	RECURSIVE UNION	t2	ALL	NULL	NULL	NULL	NULL	15	#	Using where
+3	RECURSIVE UNION	<derived2>	ref	key0	key0	5	test.t2.id	2	#	
+4	RECURSIVE UNION	t2	ALL	NULL	NULL	NULL	NULL	15	#	Using where
+4	RECURSIVE UNION	<derived2>	ref	key0	key0	5	test.t2.id	2	#	
+NULL	UNION RESULT	<union2,3,4>	ALL	NULL	NULL	NULL	NULL	NULL	#	
+Warnings:
+Note	1003	with recursive tree_of_a as (select `test`.`t2`.`id` AS `id`,`test`.`t2`.`name` AS `name`,`test`.`t2`.`leftpar` AS `leftpar`,`test`.`t2`.`rightpar` AS `rightpar`,cast(`test`.`t2`.`id` as char(200) charset latin1) AS `path` from `test`.`t2` where `test`.`t2`.`name` = 'A' union all select `test`.`t2`.`id` AS `id`,`test`.`t2`.`name` AS `name`,`test`.`t2`.`leftpar` AS `leftpar`,`test`.`t2`.`rightpar` AS `rightpar`,concat(`tree_of_a`.`path`,',',`test`.`t2`.`id`) AS `concat(tree_of_a.path,",",t2.id)` from `test`.`t2` join `tree_of_a` where `tree_of_a`.`leftpar` = `test`.`t2`.`id` union all select `test`.`t2`.`id` AS `id`,`test`.`t2`.`name` AS `name`,`test`.`t2`.`leftpar` AS `leftpar`,`test`.`t2`.`rightpar` AS `rightpar`,concat(`tree_of_a`.`path`,',',`test`.`t2`.`id`) AS `concat(tree_of_a.path,",",t2.id)` from `test`.`t2` join `tree_of_a` where `tree_of_a`.`rightpar` = `test`.`t2`.`id`)select `tree_of_a`.`id` AS `id`,`tree_of_a`.`name` AS `name`,`tree_of_a`.`leftpar` AS `leftpar`,`tree_of_a`.`rightpar` AS `rightpar`,`tree_of_a`.`path` AS `path` from `tree_of_a` order by `tree_of_a`.`path`
+WITH RECURSIVE tree_of_a AS
+(
+SELECT *, cast(id AS char(200)) AS path FROM t2 WHERE name="A"
+UNION ALL
+SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON
+t2.id=tree_of_a.leftpar
+UNION ALL
+SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON
+t2.id=tree_of_a.rightpar
+)
+SELECT * FROM tree_of_a ORDER BY path;
+id	name	leftpar	rightpar	path
+1	A	2	3	1
+2	LA	4	5	1,2
+4	LLA	6	7	1,2,4
+6	LLLA	NULL	NULL	1,2,4,6
+7	RLLA	NULL	NULL	1,2,4,7
+5	RLA	8	9	1,2,5
+8	LRLA	NULL	NULL	1,2,5,8
+9	RRLA	NULL	NULL	1,2,5,9
+3	RA	10	11	1,3
+10	LRA	12	13	1,3,10
+11	RRA	14	15	1,3,11
+15	RRRA	NULL	NULL	1,3,11,15
+# Equivalent query with one single recursive query block:
+WITH RECURSIVE tree_of_a AS
+(
+SELECT *, cast(id AS char(200)) AS path FROM t2 WHERE name="A"
+UNION ALL
+SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON
+(t2.id=tree_of_a.leftpar OR t2.id=tree_of_a.rightpar)
+)
+SELECT * FROM tree_of_a
+ORDER BY path;
+id	name	leftpar	rightpar	path
+1	A	2	3	1
+2	LA	4	5	1,2
+4	LLA	6	7	1,2,4
+6	LLLA	NULL	NULL	1,2,4,6
+7	RLLA	NULL	NULL	1,2,4,7
+5	RLA	8	9	1,2,5
+8	LRLA	NULL	NULL	1,2,5,8
+9	RRLA	NULL	NULL	1,2,5,9
+3	RA	10	11	1,3
+10	LRA	12	13	1,3,10
+11	RRA	14	15	1,3,11
+15	RRRA	NULL	NULL	1,3,11,15
+# Demonstrate a case where an index is automatically created on
+# the derived table and used to read this table in the outer
+# query (but correctly not used to read it in the recursive
+# query).
+EXPLAIN WITH RECURSIVE tree_of_a AS
+(
+SELECT *, cast(id AS char(200)) AS path FROM t2 WHERE name="A"
+UNION ALL
+SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON
+t2.id=tree_of_a.leftpar
+UNION ALL
+SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON
+t2.id=tree_of_a.rightpar
+)
+SELECT * FROM tree_of_a WHERE id=2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	15	#
+2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	15	#
+3	RECURSIVE UNION	t2	ALL	NULL	NULL	NULL	NULL	15	#
+3	RECURSIVE UNION	<derived2>	ref	key0	key0	5	test.t2.id	2	#
+4	RECURSIVE UNION	t2	ALL	NULL	NULL	NULL	NULL	15	#
+4	RECURSIVE UNION	<derived2>	ref	key0	key0	5	test.t2.id	2	#
+NULL	UNION RESULT	<union2,3,4>	ALL	NULL	NULL	NULL	NULL	NULL	#
+WITH RECURSIVE tree_of_a AS
+(
+SELECT *, cast(id AS char(200)) AS path FROM t2 WHERE name="A"
+UNION ALL
+SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON
+t2.id=tree_of_a.leftpar
+UNION ALL
+SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON
+t2.id=tree_of_a.rightpar
+)
+SELECT * FROM tree_of_a WHERE id=2;
+id	name	leftpar	rightpar	path
+2	LA	4	5	1,2
+DROP TABLE t1,t2;
+EXPLAIN WITH RECURSIVE cte AS
+(
+SELECT 1 AS n UNION ALL
+SELECT n+1 FROM cte WHERE n<10000
+)
+SELECT sum(cte1.n*cte2.n*cte3.n)=2490508525950000
+FROM cte cte1, cte cte2, cte cte3
+WHERE cte1.n=cte2.n+10 AND cte2.n+20=cte3.n;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	#
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	#
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	#
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	#
+3	RECURSIVE UNION	<derived2>	ALL	NULL	NULL	NULL	NULL	2	#
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	#
+WITH RECURSIVE cte AS
+(
+SELECT 1 AS n UNION ALL
+SELECT n+1 FROM cte WHERE n<10000
+)
+SELECT sum(cte1.n*cte2.n*cte3.n)=2490508525950000
+FROM cte cte1, cte cte2, cte cte3
+WHERE cte1.n=cte2.n+10 AND cte2.n+20=cte3.n;
+sum(cte1.n*cte2.n*cte3.n)=2490508525950000
+1
+#
+# Transitive closure
+#
+CREATE TABLE nodes(id int);
+CREATE TABLE arcs(from_id int, to_id int);
+INSERT INTO nodes VALUES(1),(2),(3),(4),(5),(6),(7),(8);
+INSERT INTO arcs VALUES(1,3), (3,6), (1,4), (4,6), (6,2), (2,1);
+# UNION ALL leads to infinite loop as 1 is reachable from 1;
+# so we stop it with a maximum depth 8 (8 nodes in graph)
+WITH RECURSIVE cte AS
+(
+SELECT id, 0 AS depth FROM nodes WHERE id=1
+UNION ALL
+SELECT to_id, depth+1 FROM arcs, cte
+WHERE from_id=cte.id AND depth<8
+)
+SELECT count(*), max(depth) FROM cte;
+count(*)	max(depth)
+25	8
+# Can use cycle detection:
+WITH RECURSIVE cte AS
+(
+SELECT id, cast(id AS char(200)) AS path, 0 AS is_cycle
+FROM nodes WHERE id=1
+UNION ALL
+SELECT to_id, concat(cte.path, ",", to_id), find_in_set(to_id, path)
+FROM arcs, cte
+WHERE from_id=cte.id AND is_cycle=0
+)
+SELECT * FROM cte;
+id	path	is_cycle
+1	1	0
+3	1,3	0
+4	1,4	0
+6	1,3,6	0
+6	1,4,6	0
+2	1,3,6,2	0
+2	1,4,6,2	0
+1	1,3,6,2,1	1
+1	1,4,6,2,1	1
+# It is simpler with DISTINCT:
+WITH RECURSIVE cte AS
+(
+SELECT id FROM nodes WHERE id=1
+UNION
+SELECT to_id FROM arcs, cte WHERE from_id=cte.id
+)
+SELECT * FROM cte;
+id
+1
+3
+4
+6
+2
+DROP TABLE nodes, arcs;
+# Hash field and MEMORY don't work together. Make long distinct
+# key to force hash field, to see if it switches to InnoDB.
+# Not too long key (500 bytes in latin1)
+WITH RECURSIVE cte AS
+(
+SELECT 1 AS n,
+repeat('a',500) AS f, '' AS g,
+'' AS h, '' AS i
+UNION
+SELECT n+1,
+'','','',''
+  FROM cte WHERE n<100)
+SELECT sum(n) FROM cte;
+sum(n)
+5050
+SHOW STATUS LIKE 'Created_tmp_disk_tables';
+Variable_name	Value
+Created_tmp_disk_tables	132
+# Too long key (>3000 bytes in latin1)
+WITH RECURSIVE cte AS
+(
+SELECT 1 AS n,
+repeat('a',500) AS f, repeat('a',500) AS g,
+repeat('a',500) AS h, repeat('a',500) AS i,
+repeat('a',500) AS j, repeat('a',500) AS k,
+repeat('a',500) AS l, repeat('a',500) AS m
+UNION
+SELECT n+1,
+'','','','','','','',''
+  FROM cte WHERE n<100)
+SELECT sum(n) FROM cte;
+sum(n)
+5050
+#
+# In query planning, the recursive reference's row count is
+# said to be the estimated row count of all non-recursive query
+# blocks
+CREATE TABLE t1(a int);
+# 15 rows:
+INSERT INTO t1 VALUES (), (), (), (), (), (), (), (), (), (), (), (),
+(), (), ();
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+# EXPLAIN says: in non-recursive QB we'll read 15 rows of t1,
+# in recursive QB we'll read 15 rows of qn, keep only 0.33
+# due to WHERE, that makes 4 (due to rounding), and in the
+# derived table we'll thus have 15+4=19. That ignores
+# next repetitions of the recursive QB which are unpredictable.
+EXPLAIN WITH RECURSIVE qn AS
+(SELECT 1 AS a FROM t1 UNION ALL SELECT a+1 FROM qn WHERE qn.a<100)
+SELECT * FROM qn;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	15	
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	15	
+3	RECURSIVE UNION	<derived2>	ALL	NULL	NULL	NULL	NULL	15	Using where
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+EXPLAIN WITH RECURSIVE qn AS
+(SELECT 1 AS a FROM t1 UNION DISTINCT SELECT a+1 FROM qn WHERE qn.a<100)
+SELECT * FROM qn;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	15	
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	15	
+3	RECURSIVE UNION	<derived2>	ALL	NULL	NULL	NULL	NULL	15	Using where
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+DROP TABLE t1;
+SHOW STATUS LIKE 'Created_tmp_disk_tables';
+Variable_name	Value
+Created_tmp_disk_tables	138
+flush STATUS;
+WITH RECURSIVE q (b) AS
+(SELECT 1 UNION ALL SELECT 1+b FROM q WHERE b<2000)
+SELECT min(b),max(b),avg(b) FROM q;
+min(b)	max(b)	avg(b)
+1	2000	1000.5000
+SHOW STATUS LIKE 'Created_tmp_disk_tables';
+Variable_name	Value
+Created_tmp_disk_tables	4
+# Test when conversion to InnoDB affects recursive references which
+# are not open yet (those of q1):
+flush STATUS;
+WITH RECURSIVE q (b) AS
+(SELECT 1 UNION ALL SELECT 1+b FROM q WHERE b<2000)
+SELECT min(q.b),max(q.b),avg(q.b) FROM q, q AS q1;
+min(q.b)	max(q.b)	avg(q.b)
+1	2000	1000.5000
+SHOW STATUS LIKE 'Created_tmp_disk_tables';
+Variable_name	Value
+Created_tmp_disk_tables	5
+# Same, but make q1 the writer; this is to test overflow when
+# the writer isn't first in the 'tmp_tables' list
+flush STATUS;
+WITH RECURSIVE q (b) AS
+(SELECT 1 UNION ALL SELECT 1+b FROM q WHERE b<2000)
+SELECT min(q.b),max(q.b),avg(q.b) FROM q RIGHT JOIN q AS q1 ON 1;
+min(q.b)	max(q.b)	avg(q.b)
+1	2000	1000.5000
+SHOW STATUS LIKE 'Created_tmp_disk_tables';
+Variable_name	Value
+Created_tmp_disk_tables	5
+# Test when outer query reads CTE with an index.
+# Overflow doesn't happen at same row as queries above, as this
+# table has an index which makes it grow faster.
+EXPLAIN WITH RECURSIVE q (b) AS
+(SELECT 1 UNION ALL SELECT 1+b FROM q WHERE b<2000)
+SELECT min(b),max(b),avg(b) FROM q WHERE b=300;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	RECURSIVE UNION	<derived2>	ALL	NULL	NULL	NULL	NULL	2	Using where
+NULL	UNION RESULT	<union2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+SHOW STATUS LIKE 'Created_tmp_disk_tables';
+Variable_name	Value
+Created_tmp_disk_tables	6
+WITH RECURSIVE q (b) AS
+(SELECT 1 UNION ALL SELECT 1+b FROM q WHERE b<2000)
+SELECT min(b),max(b),avg(b) FROM q WHERE b=300;
+min(b)	max(b)	avg(b)
+300	300	300.0000
+SHOW STATUS LIKE 'Created_tmp_disk_tables';
+Variable_name	Value
+Created_tmp_disk_tables	10
+# Verify that rows come out in insertion order.
+# If they didn't, the sequences of @c and of 'b'
+# would not be identical and the sum wouldn't be
+# 1^2 + ... + 2000^2 = n(n+1)(2n+1)/6 = 2668667000
+SET @c:=1;
+flush STATUS;
+WITH RECURSIVE q (b, c) AS
+(SELECT 1, 1 UNION ALL SELECT (1+b), (@c:=(@c+1)) FROM q WHERE b<2000)
+SELECT sum(b*c) FROM q;
+sum(b*c)
+2668667000
+SHOW STATUS LIKE 'Created_tmp_disk_tables';
+Variable_name	Value
+Created_tmp_disk_tables	4
+#
+# Bug#23495283 WL3634:ASSERTION `0' FAILED IN FIELD* ITEM::TMP_TABLE_FIELD_FROM_FIELD_TYPE
+#
+CREATE TABLE t1(c1 DATETIME, c2 INT, KEY(c1));
+WITH RECURSIVE cte AS ( SELECT a.c1 AS field1, 0 AS cycle FROM (t1 AS a)
+UNION ALL SELECT b.c2 FROM cte AS a JOIN t1 AS b) SELECT * FROM cte;
+ERROR 21000: The used SELECT statements have a different number of columns
+DROP TABLE t1;
+#
+# Bug#23645090 WL3634: INVALID WRITE AND READ VALGRIND ERRORS
+#
+CREATE TABLE A (
+col_date date DEFAULT NULL,
+col_datetime_key datetime DEFAULT NULL,
+col_time_key time DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_int_key int(11) DEFAULT NULL,
+col_blob_key blob,
+col_varchar varchar(1) DEFAULT NULL,
+col_date_key date DEFAULT NULL,
+col_time time DEFAULT NULL,
+col_blob blob,
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_int int(11) DEFAULT NULL,
+col_datetime datetime DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_datetime_key (col_datetime_key),
+KEY col_time_key (col_time_key),
+KEY col_varchar_key (col_varchar_key),
+KEY col_int_key (col_int_key),
+KEY col_blob_key (col_blob_key(255)),
+KEY col_date_key (col_date_key)
+) DEFAULT CHARSET=latin1;
+CREATE TABLE AA (
+col_varchar varchar(1) DEFAULT NULL,
+col_date date DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_date_key date DEFAULT NULL,
+col_datetime_key datetime DEFAULT NULL,
+col_time_key time DEFAULT NULL,
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_time time DEFAULT NULL,
+col_int_key int(11) DEFAULT NULL,
+col_datetime datetime DEFAULT NULL,
+col_int int(11) DEFAULT NULL,
+col_blob blob,
+col_blob_key blob,
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key),
+KEY col_date_key (col_date_key),
+KEY col_datetime_key (col_datetime_key),
+KEY col_time_key (col_time_key),
+KEY col_int_key (col_int_key),
+KEY col_blob_key (col_blob_key(255))
+) DEFAULT CHARSET=latin1;
+CREATE TABLE BB (
+col_date date DEFAULT NULL,
+col_blob_key blob,
+col_time time DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar varchar(1) DEFAULT NULL,
+col_blob blob,
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_int_key int(11) DEFAULT NULL,
+col_datetime datetime DEFAULT NULL,
+col_time_key time DEFAULT NULL,
+col_datetime_key datetime DEFAULT NULL,
+col_date_key date DEFAULT NULL,
+col_int int(11) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_blob_key (col_blob_key(255)),
+KEY col_varchar_key (col_varchar_key),
+KEY col_int_key (col_int_key),
+KEY col_time_key (col_time_key),
+KEY col_datetime_key (col_datetime_key),
+KEY col_date_key (col_date_key)
+) AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
+CREATE TABLE D (
+col_varchar_key varchar(1) DEFAULT NULL,
+col_datetime datetime DEFAULT NULL,
+col_date_key date DEFAULT NULL,
+col_int int(11) DEFAULT NULL,
+col_time time DEFAULT NULL,
+col_blob blob,
+col_int_key int(11) DEFAULT NULL,
+col_blob_key blob,
+col_varchar varchar(1) DEFAULT NULL,
+col_datetime_key datetime DEFAULT NULL,
+col_date date DEFAULT NULL,
+col_time_key time DEFAULT NULL,
+pk int(11) NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key),
+KEY col_date_key (col_date_key),
+KEY col_int_key (col_int_key),
+KEY col_blob_key (col_blob_key(255)),
+KEY col_datetime_key (col_datetime_key),
+KEY col_time_key (col_time_key)
+) DEFAULT CHARSET=latin1;
+WITH RECURSIVE cte AS (
+SELECT    alias1 . `col_blob_key` AS field1, 0 AS cycle
+FROM ( BB AS alias1 , ( D AS alias2 , AA AS alias3 ) )
+WHERE (
+alias1 . pk = 225
+OR ( alias1 . col_int_key = 69 AND alias1 . col_blob_key = 'p' )
+)
+UNION ALL
+SELECT t1.pk, t2.cycle
+FROM cte AS t2 JOIN A AS t1
+WHERE t2.field1 = t1.`col_int_key`
+AND t2.cycle =1 ) SELECT  * FROM cte;
+field1	cycle
+DROP TABLE IF EXISTS A, AA, BB, D;
+#
+# Bug#24962600 WL3634: SIG 11 IN HEAP_RRND AT STORAGE/HEAP/HP_RRND.C
+#
+create table t1(a int);
+with recursive cte as (select * from t1 union select * from cte)
+select * from cte;
+a
+insert into t1 values(1),(2);
+with recursive cte as (select * from t1 where 0 union select * from cte)
+select * from cte;
+a
+with recursive cte as (select * from t1 where a>3 union select * from cte)
+select * from cte;
+a
+drop table t1;
+#
+# Bug#26501463 WL10792: ASSERTION `!TABLE->HAS_NULL_ROW()' FAILED
+#
+CREATE TABLE D (col_int INT);
+CREATE TABLE C (
+col_int2 INT,
+pk INT NOT NULL,
+col_int INT,
+PRIMARY KEY (pk)
+);
+INSERT INTO C VALUES
+(7,1,3),(7,2,3),(5,3,4),(1,4,6),(5,5,2),
+(5,6,9),(4,7,9),(7,8,3),(3,9,0),(5,10,3);
+CREATE TABLE BB (
+pk INT NOT NULL,
+col_int INT,
+PRIMARY KEY (pk)
+);
+INSERT INTO BB VALUES (1,0),(2,6),(3,2),(4,5),(5,0);
+WITH RECURSIVE cte AS (
+SELECT alias2 . col_int2 AS field1 FROM
+D AS alias1 RIGHT  JOIN
+( ( C AS alias2 LEFT  JOIN BB AS alias3
+ON (( alias3 . pk = alias2 . col_int ) AND ( alias3 . pk = alias2 . pk ) ) ) )
+ON (alias3 . col_int <> alias2 . col_int2 )
+HAVING field1 <= 0
+UNION
+SELECT cte.field1 FROM cte
+)
+SELECT * FROM cte;
+field1
+DROP TABLE BB,C,D;
+#
+# Bug#26556025 ASSERTION `!SELECT_LEX->IS_RECURSIVE() || !TMP_TABLES' FAILED.
+#
+SET SQL_BUFFER_RESULT = 1;
+WITH RECURSIVE cte AS
+(SELECT 1 AS n UNION SELECT n+1 FROM cte WHERE n<3)
+SELECT * FROM cte;
+n
+1
+2
+3
+WITH RECURSIVE cte AS
+(SELECT 1 AS n UNION ALL SELECT n+1 FROM cte WHERE n<3)
+SELECT * FROM cte;
+n
+1
+2
+3
+SET SQL_BUFFER_RESULT = DEFAULT;
diff --git a/mysql-test/r/with_recursive_closure.result b/mysql-test/r/with_recursive_closure.result
new file mode 100644
index 00000000000..4b3e619e9d2
--- /dev/null
+++ b/mysql-test/r/with_recursive_closure.result
@@ -0,0 +1,49 @@
+SET @@max_recursive_iterations = 1000000;
+SET @node_count=100000;
+SET @edge_count=floor(@node_count*2.4);
+CREATE TABLE edges(s int, e int)
+WITH RECURSIVE tmp(s,e,d) AS
+(
+SELECT 1, 2, 1
+UNION ALL
+SELECT floor(1+rand(3565659)*@node_count),
+floor(1+rand(2344291)*@node_count),
+d+1
+FROM tmp
+WHERE d<@edge_count
+)
+SELECT s,e FROM tmp;
+CREATE INDEX a ON edges(s);
+CREATE INDEX b ON edges(e);
+flush status;
+SET @start_node=60308;
+SELECT * FROM edges WHERE s=@start_node ORDER BY e;
+s	e
+60308	387
+60308	28766
+60308	83490
+WITH RECURSIVE closure AS
+(SELECT @start_node AS n UNION SELECT e FROM edges, closure WHERE s=closure.n)
+SELECT count(*),sum(n),sum(floor(n/20)*(n%20)) FROM closure;
+count(*)	sum(n)	sum(floor(n/20)*(n%20))
+87889	4389446208	2085971176
+WITH RECURSIVE closure AS (SELECT @start_node AS n UNION SELECT CASE WHEN s=closure.n THEN e ELSE s END FROM edges, closure WHERE s=closure.n OR e=closure.n) SELECT count(*),sum(n),sum(floor(n/20)*(n%20)) FROM closure;
+count(*)	sum(n)	sum(floor(n/20)*(n%20))
+99178	4959579855	2355269804
+WITH RECURSIVE closure AS (SELECT @start_node AS n UNION SELECT e FROM edges, closure WHERE s=closure.n UNION SELECT s FROM edges, closure WHERE e=closure.n) SELECT count(*),sum(n),sum(floor(n/20)*(n%20)) FROM closure;
+count(*)	sum(n)	sum(floor(n/20)*(n%20))
+99178	4959579855	2355269804
+SHOW status LIKE 'Created_tmp_disk_tables';
+Variable_name	Value
+Created_tmp_disk_tables	6
+SET @@tmp_table_size=1024,@@max_heap_table_size=16384;
+SET big_tables=0;
+WITH RECURSIVE closure AS
+(SELECT @start_node AS n UNION SELECT e FROM edges, closure WHERE s=closure.n)
+SELECT count(*),sum(n),sum(floor(n/20)*(n%20)) FROM closure;
+count(*)	sum(n)	sum(floor(n/20)*(n%20))
+87889	4389446208	2085971176
+SHOW status LIKE 'Created_tmp_disk_tables';
+Variable_name	Value
+Created_tmp_disk_tables	10
+DROP TABLE edges;
diff --git a/mysql-test/r/with_recursive_kill.result b/mysql-test/r/with_recursive_kill.result
new file mode 100644
index 00000000000..363af33af8e
--- /dev/null
+++ b/mysql-test/r/with_recursive_kill.result
@@ -0,0 +1,30 @@
+connect  con1, localhost, root,,;
+connect  con2, localhost, root,,;
+connection con1;
+connection con2;
+# Test that infinite WITH RECURSIVE can be killed
+connection con1;
+SET DEBUG_SYNC='in_WITH_RECURSIVE SIGNAL with_recursive_has_started';
+with recursive q (num, mark) as (
+select 1, "a"
+ union all select 1+num, "b" from q where mark="a"
+ union all select 1+num, "a" from q where mark="b"
+)
+select num from q;
+connection con2;
+SET DEBUG_SYNC='now WAIT_FOR with_recursive_has_started';
+Warnings:
+Warning	1639	debug sync point wait timed out
+KILL QUERY @id;
+connection con1;
+ERROR 70100: Query execution was interrupted
+SET DEBUG_SYNC= 'RESET';
+SELECT 1;
+1
+1
+connection con2;
+SET DEBUG_SYNC= 'RESET';
+connection con1;
+connection default;
+disconnect con1;
+disconnect con2;
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;
diff --git a/mysql-test/t/with_grant.test b/mysql-test/t/with_grant.test
new file mode 100644
index 00000000000..5ccbc9e91a7
--- /dev/null
+++ b/mysql-test/t/with_grant.test
@@ -0,0 +1,47 @@
+
+--echo # privileges
+
+create database mysqltest1;
+use mysqltest1;
+create table t1(pub int, priv int);
+insert into t1 values(1,2);
+analyze table t1;
+CREATE USER user1@localhost;
+GRANT SELECT (pub) ON mysqltest1.t1 TO user1@localhost;
+
+connect (user1, localhost, user1, ,);
+connection user1;
+
+use mysqltest1;
+select pub from t1;
+--error 1143
+select priv from t1;
+
+select * from (select pub from t1) as dt;
+explain select * from (select pub from t1) as dt;
+--error 1143
+select /*+ merge(dt) */ * from (select priv from t1) as dt;
+--error 1143
+select /*+ no_merge(dt) */ * from (select priv from t1) as dt;
+--error 1143
+explain select * from (select priv from t1) as dt;
+
+with qn as (select pub from t1) select * from qn;
+explain with qn as (select pub from t1) select * from qn;
+--error 1143
+with qn as (select priv from t1) select /*+ merge(qn) */ * from qn;
+--error 1143
+with qn as (select priv from t1) select /*+ no_merge(qn) */ * from qn;
+--error 1143
+explain with qn as (select priv from t1) select * from qn;
+
+with qn2 as (with qn as (select pub from t1) select * from qn)
+select * from qn2;
+--error 1143
+with qn2 as (with qn as (select priv from t1) select * from qn)
+select * from qn2;
+
+connection default;
+disconnect user1;
+drop user user1@localhost;
+drop database mysqltest1;
diff --git a/mysql-test/t/with_non_recursive.test b/mysql-test/t/with_non_recursive.test
new file mode 100644
index 00000000000..b771cae13bf
--- /dev/null
+++ b/mysql-test/t/with_non_recursive.test
@@ -0,0 +1,8 @@
+
+--echo # In-memory tmp tables
+set big_tables=0;
+--source include/with_non_recursive.inc
+
+--echo # On-disk tmp tables
+set big_tables=1;
+--source include/with_non_recursive.inc
diff --git a/mysql-test/t/with_recursive.test b/mysql-test/t/with_recursive.test
new file mode 100644
index 00000000000..49281451840
--- /dev/null
+++ b/mysql-test/t/with_recursive.test
@@ -0,0 +1,7 @@
+--echo # In-memory tmp tables
+set big_tables=0;
+--source include/with_recursive.inc
+
+--echo # On-disk tmp tables
+set big_tables=1;
+--source include/with_recursive.inc
diff --git a/mysql-test/t/with_recursive_closure.test b/mysql-test/t/with_recursive_closure.test
new file mode 100644
index 00000000000..f3cf4b39306
--- /dev/null
+++ b/mysql-test/t/with_recursive_closure.test
@@ -0,0 +1,64 @@
+--source include/big_test.inc
+# Takes too long IN Valgrind, FOR pushbuild2:
+--source include/not_valgrind.inc
+
+SET @@max_recursive_iterations = 1000000;
+
+# This builds a graph OF randomly connected nodes (random number
+# generator IS USING a seed FOR repeatability). THEN it computes the
+# transitive closure OF a node. The RESULT has been validated against
+# another DBMS.
+
+SET @node_count=100000;
+SET @edge_count=floor(@node_count*2.4);
+
+CREATE TABLE edges(s int, e int)
+WITH RECURSIVE tmp(s,e,d) AS
+(
+SELECT 1, 2, 1
+UNION ALL
+SELECT floor(1+rand(3565659)*@node_count),
+       floor(1+rand(2344291)*@node_count),
+       d+1
+FROM tmp
+WHERE d<@edge_count
+)
+SELECT s,e FROM tmp;
+
+CREATE INDEX a ON edges(s);
+CREATE INDEX b ON edges(e);
+
+flush status;
+
+SET @start_node=60308;
+SELECT * FROM edges WHERE s=@start_node ORDER BY e;
+
+# uni-directional edges.
+# The sums ARE used AS digests OF the thousand-ROWS RESULT.
+
+WITH RECURSIVE closure AS
+(SELECT @start_node AS n UNION SELECT e FROM edges, closure WHERE s=closure.n)
+SELECT count(*),sum(n),sum(floor(n/20)*(n%20)) FROM closure;
+
+# bi-directional edges
+
+WITH RECURSIVE closure AS (SELECT @start_node AS n UNION SELECT CASE WHEN s=closure.n THEN e ELSE s END FROM edges, closure WHERE s=closure.n OR e=closure.n) SELECT count(*),sum(n),sum(floor(n/20)*(n%20)) FROM closure;
+
+# equivalent query WITH two RECURSIVE members
+
+WITH RECURSIVE closure AS (SELECT @start_node AS n UNION SELECT e FROM edges, closure WHERE s=closure.n UNION SELECT s FROM edges, closure WHERE e=closure.n) SELECT count(*),sum(n),sum(floor(n/20)*(n%20)) FROM closure;
+
+SHOW status LIKE 'Created_tmp_disk_tables';
+
+# uni-directional edges, again, just TO test overflow-TO-disk: we
+# START WITH a low LIMIT ON the MEMORY TABLE.
+
+SET @@tmp_table_size=1024,@@max_heap_table_size=16384;
+SET big_tables=0;
+WITH RECURSIVE closure AS
+(SELECT @start_node AS n UNION SELECT e FROM edges, closure WHERE s=closure.n)
+SELECT count(*),sum(n),sum(floor(n/20)*(n%20)) FROM closure;
+
+SHOW status LIKE 'Created_tmp_disk_tables';
+
+DROP TABLE edges;
diff --git a/mysql-test/t/with_recursive_kill.test b/mysql-test/t/with_recursive_kill.test
new file mode 100644
index 00000000000..36da35c09db
--- /dev/null
+++ b/mysql-test/t/with_recursive_kill.test
@@ -0,0 +1,45 @@
+-- source include/have_debug_sync.inc
+-- source include/not_threadpool.inc
+
+connect (con1, localhost, root,,);
+connect (con2, localhost, root,,);
+
+# Save id of con1
+connection con1;
+--disable_reconnect
+let $ID= `SELECT @id := CONNECTION_ID()`;
+connection con2;
+let $ignore= `SELECT @id := $ID`;
+
+--echo # Test that infinite WITH RECURSIVE can be killed
+connection con1;
+
+SET DEBUG_SYNC='in_WITH_RECURSIVE SIGNAL with_recursive_has_started';
+
+send
+with recursive q (num, mark) as (
+ select 1, "a"
+ union all select 1+num, "b" from q where mark="a"
+ union all select 1+num, "a" from q where mark="b"
+)
+select num from q;
+
+connection con2;
+
+# Wait until the above SELECT is in WITH-RECURSIVE algorithm
+SET DEBUG_SYNC='now WAIT_FOR with_recursive_has_started';
+
+KILL QUERY @id;
+connection con1;
+--error ER_QUERY_INTERRUPTED
+reap;
+SET DEBUG_SYNC= 'RESET';
+SELECT 1;
+
+connection con2;
+SET DEBUG_SYNC= 'RESET';
+
+connection con1;
+connection default;
+disconnect con1;
+disconnect con2;