CTE testsbb-10.3-MDEV-15447

10 files changed, 4482 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..1220e5c35c5
--- /dev/null
+++ b/mysql-test/include/with_non_recursive.inc
@@ -0,0 +1,529 @@
+
+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;
+WITH qn AS (SELECT a FROM t1), qn2 AS (SELECT b FROM t1) SELECT 1 FROM dual;
+
+--error ER_DUP_QUERY_NAME
+WITH qn AS (SELECT a FROM t1), qn AS (SELECT b FROM t1) SELECT 1 FROM qn;
+
+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;
+
+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;
+
+--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;
+
+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;
+
+explain WITH qn AS (SELECT 1) SELECT 2;
+WITH qn AS (SELECT 1) SELECT 2;
+
+--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;
+
+--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;
+
+WITH qn AS (SELECT * FROM t1) SELECT (SELECT max(a) FROM qn);
+
+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;
+
+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;
+}
+
+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;
+
+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;
+
+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;
+
+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;
+
+WITH qn AS (SELECT "with") SELECT * FROM (SELECT "dt") AS qn;
+
+WITH qn AS (SELECT "outer" AS a) 
+  SELECT (WITH qn AS (SELECT "inner" AS a) SELECT a FROM qn), qn.a FROM qn;
+
+--error ER_PARSE_ERROR
+WITH test.qn AS (SELECT "with") SELECT * FROM test.qn;
+
+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;
+
+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;
+
+--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;
+
+--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;
+
+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;
+
+--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;
+--error ER_DUP_FIELDNAME
+WITH qn (foo, foo) AS (SELECT 1,2) SELECT * FROM qn;
+
+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";
+
+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;
+
+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;
+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;
+
+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, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int, c9 int, c10 int,
+ c11 int, c12 int, c13 int, c14 int, c15 int, c16 int, c17 int, c18 int, c19 int, c20 int, 
+ c21 int, c22 int, c23 int, c24 int, c25 int, c26 int, c27 int, c28 int, c29 int, c30 int, 
+ c31 int, c32 int, c33 int, c34 int, c35 int, c36 int, c37 int, c38 int, c39 int, c40 int, 
+ c41 int, c42 int, c43 int, c44 int, c45 int, c46 int, c47 int, c48 int, c49 int, c50 int, 
+ c51 int, c52 int, c53 int, c54 int, c55 int, c56 int, c57 int, c58 int, c59 int, c60 int, 
+ c61 int, c62 int, c63 int, c64 int, c65 int, c66 int, c67 int, c68 int, c69 int, c70 int, 
+ c71 int, c72 int, c73 int, c74 int, c75 int, c76 int, c77 int, c78 int, c79 int, c80 int, 
+ c81 int, c82 int, c83 int, c84 int, c85 int, c86 int, c87 int, c88 int, c89 int, c90 int, 
+ c91 int, c92 int, c93 int, c94 int, c95 int, c96 int, c97 int, c98 int, c99 int, c100 int);
+
+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 covering key (always reads the "data file"). But InnoDB does.
+eval EXPLAIN $query;
+eval $query;
+DROP TABLE t1;
+
+CREATE VIEW v1 AS SELECT "with";
+WITH v1 AS (SELECT * FROM v1) SELECT * FROM v1;
+DROP VIEW v1;
+
+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);
+
+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
+
+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..debbf6748db
--- /dev/null
+++ b/mysql-test/include/with_recursive.inc
@@ -0,0 +1,482 @@
+SET @@max_recursive_iterations = 10000;
+flush STATUS;
+
+--error ER_RECURSIVE_WITHOUT_ANCHORS
+WITH RECURSIVE qn AS (SELECT * FROM qn2), qn2 AS (SELECT * FROM qn)
+SELECT * FROM qn;
+
+--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;
+
+WITH RECURSIVE qn AS (SELECT 1 FROM dual UNION ALL SELECT 1 FROM dual)
+SELECT * FROM qn;
+
+WITH RECURSIVE qn AS (SELECT 1 FROM dual UNION SELECT 1 FROM qn)
+SELECT * FROM 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;
+
+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;
+
+--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;
+
+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 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;
+
+--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 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)
+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;
+
+WITH RECURSIVE qn AS (SELECT 123 AS a UNION ALL SELECT NULL FROM qn WHERE a IS NOT NULL) 
+SELECT * FROM qn;
+
+--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;
+
+--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;
+
+--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;
+--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;
+
+--error ER_NOT_STANDARD_COMPLIANT_RECURSIVE
+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;
+
+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";
+
+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;
+
+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;
+
+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 # 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 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;
+
+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 case where an index is automatically created on the derived table and used to read this table in the outer 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 # 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);
+
+--echo # UNION ALL leads to infinite loop as 1 is reachable from 1, so we stop it with a maximum depth 8 
+
+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;
+
+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;
+
+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 # In query planning, the recursive reference's row count is
+--echo # said to be the estimated row count of all non-recursive query blocks
+
+CREATE TABLE t1(a int);
+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 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 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 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';
+
diff --git a/mysql-test/main/with_.result b/mysql-test/main/with_.result
new file mode 100644
index 00000000000..9d9ad14a730
--- /dev/null
+++ b/mysql-test/main/with_.result
@@ -0,0 +1,437 @@
+# 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;
+# Verifying the CTE-specific output of EXPLAIN
+use test;
+CREATE TABLE t1(a int);
+INSERT INTO t1 VALUES(1),(2);
+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	
+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,
+                  "operation": "UNION",
+                  "table": {
+                    "table_name": "<derived2>",
+                    "access_type": "ALL",
+                    "rows": 2,
+                    "filtered": 100,
+                    "attached_condition": "octet_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;
+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;
+CREATE TABLE A (
+col_date date,
+col_datetime_key datetime,
+col_time_key time,
+col_varchar_key varchar(1),
+col_int_key int(11),
+col_blob_key blob,
+col_varchar varchar(1),
+col_date_key date,
+col_time time,
+col_blob blob,
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_int int(11),
+col_datetime datetime,
+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),
+col_date date,
+col_varchar_key varchar(1),
+col_date_key date,
+col_datetime_key datetime,
+col_time_key time,
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_time time,
+col_int_key int(11),
+col_datetime datetime,
+col_int int(11),
+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,
+col_blob_key blob,
+col_time time,
+col_varchar_key varchar(1),
+col_varchar varchar(1),
+col_blob blob,
+pk int(11) NOT NULL AUTO_INCREMENT,
+col_int_key int(11),
+col_datetime datetime,
+col_time_key time,
+col_datetime_key datetime,
+col_date_key date,
+col_int int(11),
+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),
+col_datetime datetime,
+col_date_key date,
+col_int int(11),
+col_time time,
+col_blob blob,
+col_int_key int(11),
+col_blob_key blob,
+col_varchar varchar(1),
+col_datetime_key datetime,
+col_date date,
+col_time_key time,
+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 A, AA, BB, D;
+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;
+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;
+#
+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;
+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/main/with_.test b/mysql-test/main/with_.test
new file mode 100644
index 00000000000..ef6d2f36c65
--- /dev/null
+++ b/mysql-test/main/with_.test
@@ -0,0 +1,274 @@
+--source include/big_test.inc
+
+--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 ER_COLUMNACCESS_DENIED_ERROR
+select priv from t1;
+
+select * from (select pub from t1) as dt;
+explain select * from (select pub from t1) as dt;
+--error ER_COLUMNACCESS_DENIED_ERROR
+select /*+ merge(dt) */ * from (select priv from t1) as dt;
+--error ER_COLUMNACCESS_DENIED_ERROR
+select /*+ no_merge(dt) */ * from (select priv from t1) as dt;
+--error ER_COLUMNACCESS_DENIED_ERROR
+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 ER_COLUMNACCESS_DENIED_ERROR
+with qn as (select priv from t1) select /*+ merge(qn) */ * from qn;
+--error ER_COLUMNACCESS_DENIED_ERROR
+with qn as (select priv from t1) select /*+ no_merge(qn) */ * from qn;
+--error ER_COLUMNACCESS_DENIED_ERROR
+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 ER_COLUMNACCESS_DENIED_ERROR
+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;
+
+--echo # Verifying the CTE-specific output of EXPLAIN
+use test;
+
+CREATE TABLE t1(a int);
+INSERT INTO t1 VALUES(1),(2);
+
+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;
+
+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;
+
+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;
+
+CREATE TABLE A (
+  col_date date,
+  col_datetime_key datetime,
+  col_time_key time,
+  col_varchar_key varchar(1),
+  col_int_key int(11),
+  col_blob_key blob,
+  col_varchar varchar(1),
+  col_date_key date,
+  col_time time,
+  col_blob blob,
+  pk int(11) NOT NULL AUTO_INCREMENT,
+  col_int int(11),
+  col_datetime datetime,
+  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),
+  col_date date,
+  col_varchar_key varchar(1),
+  col_date_key date,
+  col_datetime_key datetime,
+  col_time_key time,
+  pk int(11) NOT NULL AUTO_INCREMENT,
+  col_time time,
+  col_int_key int(11),
+  col_datetime datetime,
+  col_int int(11),
+  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,
+  col_blob_key blob,
+  col_time time,
+  col_varchar_key varchar(1),
+  col_varchar varchar(1),
+  col_blob blob,
+  pk int(11) NOT NULL AUTO_INCREMENT,
+  col_int_key int(11),
+  col_datetime datetime,
+  col_time_key time,
+  col_datetime_key datetime,
+  col_date_key date,
+  col_int int(11),
+  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),
+  col_datetime datetime,
+  col_date_key date,
+  col_int int(11),
+  col_time time,
+  col_blob blob,
+  col_int_key int(11),
+  col_blob_key blob,
+  col_varchar varchar(1),
+  col_datetime_key datetime,
+  col_date date,
+  col_time_key time,
+  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 A, AA, BB, D;
+
+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;
+
+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 #
+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;
+
+-- 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;
diff --git a/mysql-test/main/with_non_recursive.result b/mysql-test/main/with_non_recursive.result
new file mode 100644
index 00000000000..41430216a12
--- /dev/null
+++ b/mysql-test/main/with_non_recursive.result
@@ -0,0 +1,1172 @@
+# 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
+WITH qn AS (SELECT a FROM t1), qn2 AS (SELECT b FROM t1) SELECT 1 FROM dual;
+1
+1
+WITH qn AS (SELECT a FROM t1), qn AS (SELECT b FROM t1) SELECT 1 FROM qn;
+ERROR HY000: Duplicate query name `qn` in WITH clause
+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
+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)
+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
+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
+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
+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
+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
+WITH qn AS (SELECT * FROM t1) SELECT (SELECT max(a) FROM qn);
+(SELECT max(a) FROM qn)
+2
+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 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)
+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 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;
+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
+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;
+WITH qn AS (SELECT "with") SELECT * FROM (SELECT "dt") AS qn;
+dt
+dt
+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
+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
+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
+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
+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 'WITH 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 'WITH 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#2 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` order by 1 limit 10)/* select#1 */ 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
+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 1 AS col, 2 AS coll UNION SELECT a,b FROM t1) SELECT qn1.bar FROM qn qn1;
+bar
+2
+NULL
+3
+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;
+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
+WITH qn (foo, foo) AS (SELECT 1,2) SELECT * FROM qn;
+ERROR 42S21: Duplicate column name 'foo'
+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;
+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
+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
+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;
+CREATE VIEW v1 AS SELECT "with";
+WITH v1 AS (SELECT * FROM v1) SELECT * FROM v1;
+with
+with
+DROP VIEW v1;
+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);
+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
+WITH qn AS (SELECT a FROM t1), qn2 AS (SELECT b FROM t1) SELECT 1 FROM dual;
+1
+1
+WITH qn AS (SELECT a FROM t1), qn AS (SELECT b FROM t1) SELECT 1 FROM qn;
+ERROR HY000: Duplicate query name `qn` in WITH clause
+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
+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)
+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
+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
+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
+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
+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
+WITH qn AS (SELECT * FROM t1) SELECT (SELECT max(a) FROM qn);
+(SELECT max(a) FROM qn)
+2
+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 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)
+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 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;
+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
+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;
+WITH qn AS (SELECT "with") SELECT * FROM (SELECT "dt") AS qn;
+dt
+dt
+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
+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
+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
+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
+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 'WITH 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 'WITH 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#2 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` order by 1 limit 10)/* select#1 */ 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
+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 1 AS col, 2 AS coll UNION SELECT a,b FROM t1) SELECT qn1.bar FROM qn qn1;
+bar
+2
+NULL
+3
+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;
+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
+WITH qn (foo, foo) AS (SELECT 1,2) SELECT * FROM qn;
+ERROR 42S21: Duplicate column name 'foo'
+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;
+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
+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
+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;
+CREATE VIEW v1 AS SELECT "with";
+WITH v1 AS (SELECT * FROM v1) SELECT * FROM v1;
+with
+with
+DROP VIEW v1;
+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);
+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/main/with_non_recursive.test b/mysql-test/main/with_non_recursive.test
new file mode 100644
index 00000000000..b771cae13bf
--- /dev/null
+++ b/mysql-test/main/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/main/with_recursive.result b/mysql-test/main/with_recursive.result
new file mode 100644
index 00000000000..a045fe38a02
--- /dev/null
+++ b/mysql-test/main/with_recursive.result
@@ -0,0 +1,1472 @@
+# In-memory tmp tables
+set big_tables=0;
+SET @@max_recursive_iterations = 10000;
+flush STATUS;
+WITH RECURSIVE qn AS (SELECT * FROM qn2), qn2 AS (SELECT * FROM qn)
+SELECT * FROM qn;
+ERROR HY000: No anchors for recursive WITH element 'qn'
+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'
+WITH RECURSIVE qn AS (SELECT 1 FROM dual UNION ALL SELECT 1 FROM dual)
+SELECT * FROM qn;
+1
+1
+1
+WITH RECURSIVE qn AS (SELECT 1 FROM dual UNION SELECT 1 FROM qn)
+SELECT * FROM qn;
+1
+1
+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'
+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
+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'
+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;
+count(*)
+10001
+# 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
+NULL
+# 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
+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
+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;
+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
+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'
+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
+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'
+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;
+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
+# 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#2 */ 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#3 */ 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#4 */ 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#1 */ 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
+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
+case where an index is automatically created on the derived table and used to read this table in the outer 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 
+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
+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
+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);
+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
+# On-disk tmp tables
+set big_tables=1;
+SET @@max_recursive_iterations = 10000;
+flush STATUS;
+WITH RECURSIVE qn AS (SELECT * FROM qn2), qn2 AS (SELECT * FROM qn)
+SELECT * FROM qn;
+ERROR HY000: No anchors for recursive WITH element 'qn'
+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'
+WITH RECURSIVE qn AS (SELECT 1 FROM dual UNION ALL SELECT 1 FROM dual)
+SELECT * FROM qn;
+1
+1
+1
+WITH RECURSIVE qn AS (SELECT 1 FROM dual UNION SELECT 1 FROM qn)
+SELECT * FROM qn;
+1
+1
+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'
+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
+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'
+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;
+count(*)
+10001
+# 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
+NULL
+# 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
+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
+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;
+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
+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'
+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
+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'
+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;
+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
+# 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#2 */ 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#3 */ 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#4 */ 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#1 */ 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
+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
+case where an index is automatically created on the derived table and used to read this table in the outer 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 
+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
+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
+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	84
+# 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);
+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	90
+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
diff --git a/mysql-test/main/with_recursive.test b/mysql-test/main/with_recursive.test
new file mode 100644
index 00000000000..49281451840
--- /dev/null
+++ b/mysql-test/main/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/main/with_recursive_closure.result b/mysql-test/main/with_recursive_closure.result
new file mode 100644
index 00000000000..15febf99497
--- /dev/null
+++ b/mysql-test/main/with_recursive_closure.result
@@ -0,0 +1,45 @@
+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/main/with_recursive_closure.test b/mysql-test/main/with_recursive_closure.test
new file mode 100644
index 00000000000..542f5e4943d
--- /dev/null
+++ b/mysql-test/main/with_recursive_closure.test
@@ -0,0 +1,56 @@
+--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;