diff options
Diffstat (limited to 'mysql-test/include')
| -rw-r--r-- | mysql-test/include/with_non_recursive.inc | 758 | ||||
| -rw-r--r-- | mysql-test/include/with_recursive.inc | 1073 |
2 files changed, 1831 insertions, 0 deletions
diff --git a/mysql-test/include/with_non_recursive.inc b/mysql-test/include/with_non_recursive.inc new file mode 100644 index 00000000000..1f169c03c6d --- /dev/null +++ b/mysql-test/include/with_non_recursive.inc @@ -0,0 +1,758 @@ + +flush STATUS; + +CREATE TABLE t1(a int, b int, c int); +INSERT INTO t1 VALUES(NULL,NULL,NULL),(2,3,4); + +WITH qn AS (SELECT a FROM t1) + SELECT 1 FROM dual; + +--echo # two query names + +WITH qn AS (SELECT a FROM t1), qn2 AS (SELECT b FROM t1) +SELECT 1 FROM dual; + +--echo # duplicate query names + +--error ER_DUP_QUERY_NAME +WITH qn AS (SELECT a FROM t1), qn AS (SELECT b FROM t1) +SELECT 1 FROM qn; + +--echo # multiple refs + +WITH qn AS (SELECT b AS a FROM t1) +SELECT qn.a, qn2.a FROM qn, qn AS qn2; + +WITH qn AS (SELECT b AS a FROM t1), +qn2 AS (SELECT c FROM t1 WHERE a IS NULL OR a>0) +SELECT qn.a, qn2.c FROM qn, qn2; + +--echo # qn2 ref qn: + +WITH qn AS (SELECT 10*a AS a FROM t1), +qn2 AS (SELECT 3*a FROM qn) +SELECT * FROM qn2; + +WITH qn AS (SELECT a FROM t1), qn2 AS (SELECT a FROM qn) +SELECT * FROM qn2; + +let $query= +WITH qn AS (SELECT b AS a FROM t1), +qn2 AS (SELECT a FROM qn WHERE a IS NULL OR a>0) +SELECT qn.a, qn2.a FROM qn, qn2; + +eval $query; +eval EXPLAIN $query; + +--echo # forward ref (should error) + +--error ER_NO_SUCH_TABLE +WITH qn2 AS (SELECT a FROM qn WHERE a IS NULL OR a>0), +qn AS (SELECT b AS a FROM t1) +SELECT qn2.a FROM qn2; + +--error ER_NO_SUCH_TABLE +WITH qn1 AS (WITH qn3 AS (SELECT * FROM qn2) SELECT * FROM qn3), + qn2 AS (SELECT 1) +SELECT * FROM qn1; + +--echo # This is valid; it is to test moving boundaries. +--echo # When we resolve qn3, resolving qn1 moves the right bound to +--echo # qn0, but the bound is properly restored so that we can later +--echo # resolve qn2. +WITH qn0 AS (SELECT 1), qn1 AS (SELECT * FROM qn0), qn2 AS (SELECT 1), qn3 AS (SELECT 1 FROM qn1, qn2) SELECT 1 FROM qn3; + +--echo # No ref + +explain WITH qn AS (SELECT 1) SELECT 2; +WITH qn AS (SELECT 1) SELECT 2; + +--echo # circular ref + +--error ER_NO_SUCH_TABLE +WITH qn2 AS (SELECT a FROM qn WHERE a IS NULL OR a>0), +qn AS (SELECT b AS a FROM qn2) +SELECT qn.a FROM qn; + +--echo # recursive + +--error ER_NO_SUCH_TABLE +WITH qn AS (SELECT a FROM qn) +SELECT qn.a FROM qn; + +--error ER_NO_SUCH_TABLE +WITH qn1 AS (SELECT a FROM qn3), +qn2 AS (SELECT a FROM qn1), +qn3 AS (SELECT a FROM t1), +qn4 AS (SELECT a FROM qn2) +SELECT a FROM qn4; + +--echo # ref from subq + +WITH qn AS (SELECT * FROM t1) SELECT (SELECT max(a) FROM qn); + +--echo # QN defined in subq + +SELECT (WITH qn AS (SELECT 10*a AS a FROM t1), + qn2 AS (SELECT 3*a AS b FROM qn) + SELECT * FROM qn2 LIMIT 1) +FROM t1; + +SELECT * +FROM (WITH qn AS (SELECT 10*a AS a FROM t1), + qn2 AS (SELECT 3*a AS b FROM qn) + SELECT * FROM qn2) +AS dt; + +--echo # WITH in WITH +WITH qn AS + (WITH qn2 AS (SELECT "qn2" AS a FROM t1) SELECT "qn", a FROM qn2) +SELECT * FROM qn; + +--echo # outer ref to a table, placed in a QN in a subq (later) +IF (0) +{ +SELECT (WITH qn AS (SELECT t2.a*a AS a FROM t1), + qn2 AS (SELECT 3*a AS b FROM qn) + SELECT * FROM qn2 LIMIT 1) +FROM t2 AS t1; + +--echo # outer ref to a QN, placed in a QN in a subq + +WITH qn AS (SELECT b AS a FROM t1) +SELECT (WITH qn2 AS (SELECT a FROM qn WHERE a IS NULL OR a>0) + SELECT qn2.a FROM qn2) FROM qn; +} + +--echo # QN defined in view + +CREATE VIEW v AS +WITH qn AS (SELECT 10*a AS a FROM t1), + qn2 AS (SELECT 3*a AS b FROM qn) + SELECT * FROM qn2; +SELECT * FROM v; +DROP VIEW v; + +--echo # CREATE INSERT SELECT + +CREATE TABLE t2 +WITH qn AS (SELECT 10*a AS a FROM t1), + qn2 AS (SELECT 3*a AS b FROM qn) + SELECT * FROM qn2; +SELECT * FROM t2; +INSERT INTO t2 +WITH qn AS (SELECT 10*a AS a FROM t1), + qn2 AS (SELECT 3*a AS b FROM qn) + SELECT * FROM qn2; +SELECT * FROM t2; +DROP TABLE t2; + +--echo # Double use of QN in two subqueries. + +let $query= +WITH qn AS (SELECT * FROM t1 LIMIT 10) +SELECT (SELECT max(a) FROM qn WHERE a=0), + (SELECT min(b) FROM qn WHERE b=3); +eval explain $query; +eval $query; + +--echo # when QN, when table. +CREATE TABLE qn SELECT "base"; + +SELECT * FROM qn; + +WITH qn AS (SELECT "with") SELECT * FROM qn; + +--echo # In a non-recursive WITH, the scope of the QN doesn't extend to its +--echo # subquery, so "qn" inside AS() is the base table. +WITH qn AS (SELECT * FROM qn) SELECT * FROM qn; + +--echo # View doesn't look out to external QNs + +CREATE VIEW v AS SELECT * FROM qn; + +SELECT * FROM v; + +WITH qn AS (SELECT "with") SELECT * FROM v; + +WITH qn AS (SELECT * FROM v) SELECT * FROM qn; + +--echo # Even if the base table is temporarily dropped +DROP TABLE qn; +--error ER_VIEW_INVALID +WITH qn AS (SELECT "with") SELECT * FROM v; +--error ER_VIEW_INVALID +WITH qn AS (SELECT * FROM v) SELECT * FROM qn; +CREATE TABLE qn SELECT "base" AS a; + +--echo # Neither does SP + +CREATE FUNCTION f() RETURNS varchar(10) + RETURN (SELECT * FROM qn); + +SELECT f(); + +WITH qn AS (SELECT "with") SELECT f(); + +WITH qn AS (SELECT f()) SELECT * FROM qn; + +--echo # QN shadows tmp table + +CREATE TEMPORARY TABLE qn SELECT "tmp" AS a; + +SELECT * FROM qn; + +WITH qn AS (SELECT "with") SELECT * FROM qn; + +DROP FUNCTION f; +DROP VIEW v; + +--echo # DT shadows QN: + +WITH qn AS (SELECT "with") SELECT * FROM (SELECT "dt") AS qn; + +--echo # QN of subq shadows outer QN +WITH qn AS (SELECT "outer" AS a) +SELECT (WITH qn AS (SELECT "inner" AS a) SELECT a FROM qn), + qn.a +FROM qn; + +--echo # Qualified name isn't allowed after WITH: + +--error ER_PARSE_ERROR +WITH test.qn AS (SELECT "with") SELECT * FROM test.qn; + +--echo # OTOH, db. prefix in FROM doesn't resolve to QN, which is good +WITH qn AS (SELECT "with") SELECT * FROM qn; +WITH qn AS (SELECT "with") SELECT * FROM test.qn; +WITH qn AS (SELECT "with" AS a) SELECT a FROM qn; +WITH qn AS (SELECT "with" AS a) SELECT qn.a FROM qn; +WITH qn AS (SELECT "with" AS a) SELECT test.qn.a FROM qn; +WITH qn AS (SELECT "with" AS a) SELECT a FROM test.qn; +WITH qn AS (SELECT "with" AS a) SELECT qn.a FROM test.qn; +WITH qn AS (SELECT "with" AS a) SELECT test.qn.a FROM test.qn; + +DROP TEMPORARY TABLE qn; + +WITH qn AS (SELECT "with" AS a) SELECT a FROM test.qn; +WITH qn AS (SELECT "with" AS a) SELECT qn.a FROM test.qn; +WITH qn AS (SELECT "with" AS a) SELECT test.qn.a FROM test.qn; + +DROP TABLE qn; + +--error ER_NO_SUCH_TABLE +WITH qn AS (SELECT "with" AS a) SELECT a FROM test.qn; +--error ER_NO_SUCH_TABLE +WITH qn AS (SELECT "with" AS a) SELECT qn.a FROM test.qn; +--error ER_NO_SUCH_TABLE +WITH qn AS (SELECT "with" AS a) SELECT test.qn.a FROM test.qn; + +--echo # Unions + +WITH qn AS (SELECT b AS a FROM t1 UNION SELECT b+5 FROM t1), +qn2 AS (SELECT a FROM qn WHERE a IS NULL OR a>0) +SELECT qn.a FROM qn +UNION SELECT qn2.a FROM qn2 WHERE qn2.a>3; + +--echo # No double WITH + +--error ER_PARSE_ERROR +WITH qn AS (SELECT "with" AS a) +WITH qn2 AS (SELECT "with" AS a) +SELECT a FROM test.qn; +--echo # with comma +--error ER_PARSE_ERROR +WITH qn AS (SELECT "with" AS a), +WITH qn2 AS (SELECT "with" AS a) +SELECT a FROM test.qn; + +--echo # ORDER BY removed unless there is LIMIT or single table (check "Using filesort") +explain extended +WITH qn AS (SELECT a FROM t1 ORDER BY 1) +SELECT a FROM qn; + +explain extended +WITH qn AS (SELECT a FROM t1 ORDER BY 1) +SELECT qn.a FROM qn, t1 AS t2; + +explain extended +WITH qn AS (SELECT a FROM t1 ORDER BY 1 LIMIT 10) +SELECT qn.a FROM qn, t1 AS t2; + +--echo # FD detection + +WITH qn AS (SELECT a, b FROM t1) +SELECT b FROM qn GROUP BY a; + +WITH qn AS (SELECT a, b FROM t1 WHERE a=b) +SELECT b FROM qn GROUP BY a; + +WITH qn AS (SELECT a, sum(b) AS s FROM t1 GROUP BY a) +SELECT s FROM qn GROUP BY a; + +--echo # CTEs work if used in SET + +SET @myvar= +(WITH qn AS (SELECT a, sum(b) AS s FROM t1 GROUP BY a) +SELECT s FROM qn GROUP BY a HAVING s IS NOT NULL); +SELECT @myvar; + +--echo # CTE works with semijoin +let $query= +WITH cte AS (SELECT * FROM t1 AS t2 LIMIT 1) +SELECT * FROM t1 WHERE t1.a IN (SELECT a+0 FROM cte); +eval explain $query; +eval $query; + +let $query= +WITH cte AS (SELECT * FROM t1 AS t2) +SELECT * FROM t1 WHERE t1.a IN (SELECT a+0 FROM cte); +eval explain $query; +eval $query; + +--echo # Column names + +--echo # empty list +--error ER_PARSE_ERROR +WITH qn () AS (SELECT 1) SELECT * FROM qn, qn qn1; +--echo # Materialization +--error ER_WITH_COL_WRONG_LIST +WITH qn (foo, bar) AS (SELECT 1) SELECT * FROM qn, qn qn1; +explain WITH qn (foo, bar) AS (SELECT 1, 2 FROM t1 LIMIT 2) SELECT * FROM qn, qn qn1; +WITH qn (foo, bar) AS (SELECT 1, 2 FROM t1 LIMIT 2) SELECT * FROM qn, qn qn1; +WITH qn (foo, bar) AS (SELECT 1 AS col, 2 AS coll FROM t1 LIMIT 2) SELECT * FROM qn, qn qn1; +# mdev-15478 WITH qn (foo, bar) AS (SELECT 1 AS col, 2 AS coll UNION SELECT a,b FROM t1) SELECT qn1.bar FROM qn qn1; +WITH qn (foo, bar) AS (SELECT a, b FROM t1 LIMIT 2) SELECT qn.bar,foo FROM qn; +CREATE TABLE t3 +WITH qn (foo, bar) AS (SELECT a, b FROM t1 LIMIT 2) SELECT bar,foo FROM qn; +DESC t3; +DROP TABLE t3; + +--echo # Merge + +--error ER_WITH_COL_WRONG_LIST +WITH qn (foo, bar) AS (SELECT 1 FROM t1) SELECT * FROM qn, qn qn1; +WITH qn (foo, bar) AS (SELECT 1, 2 FROM t1) SELECT * FROM qn, qn qn1; +explain WITH qn (foo, bar) AS (SELECT 1, 2 FROM t1) SELECT * FROM qn, qn qn1; +WITH qn (foo, bar) AS (SELECT 1 AS col, 2 AS coll FROM t1) SELECT * FROM qn, qn qn1; +WITH qn (foo, bar) AS (SELECT a, b FROM t1) SELECT qn1.bar,foo FROM qn qn1; +CREATE TABLE t3 +WITH qn (foo, bar) AS (SELECT a, b FROM t1) SELECT bar,foo FROM qn; +DESC t3; +DROP TABLE t3; + +--echo # Disambiguates same-name expressions +--error ER_DUP_FIELDNAME +WITH qn AS (SELECT 1,1) SELECT * FROM qn; +WITH qn (foo, bar) AS (SELECT 1,1) SELECT * FROM qn; +--error ER_DUP_FIELDNAME +WITH qn AS (SELECT 1,1 FROM t1) SELECT * FROM qn; +WITH qn (foo, bar) AS (SELECT 1,1 FROM t1) SELECT * FROM qn; +--echo # Duplicate names are forbidden +--error ER_DUP_FIELDNAME +WITH qn (foo, foo) AS (SELECT 1,2) SELECT * FROM qn; + +--echo # Column names for QN/DT are printed +CREATE VIEW v1 AS + WITH qn (foo, bar) AS (SELECT 1,1) SELECT * FROM qn; +SHOW CREATE VIEW v1; +SHOW fields FROM v1; +SELECT * FROM v1; +DROP VIEW v1; + +CREATE VIEW v1 AS + WITH qn (foo, bar) AS (SELECT 1,1 FROM t1) SELECT * FROM qn; +SHOW CREATE VIEW v1; +SELECT * FROM v1; +DROP VIEW v1; + +--error ER_VIEW_WRONG_LIST +CREATE VIEW v1 (bar) AS + SELECT 1, 2 FROM t1; + +DROP TABLE t1; + +--echo # Prove that a materialized QN is shared among all references: +CREATE TABLE t1(a int); +INSERT INTO t1 VALUES(1),(2),(3),(4); +flush STATUS; +WITH qn AS (SELECT 123 AS col) +SELECT * FROM qn; +SHOW STATUS LIKE "handler_write"; +flush STATUS; +WITH qn AS (SELECT 123 AS col) +SELECT * FROM qn, qn AS qn1; +SHOW STATUS LIKE "handler_write"; +# Contrast that WITH VIEW: +CREATE VIEW qn AS SELECT 123 AS col; +flush STATUS; +SELECT * FROM qn, qn AS qn1; +SHOW STATUS LIKE "handler_write"; +DROP VIEW qn; + +DROP TABLE t1; + +--echo # Printing of WITH to DD for view +CREATE VIEW v AS +SELECT (WITH qn AS (SELECT "with") SELECT * FROM qn) AS scal_subq +FROM dual; +SHOW CREATE VIEW v; +SELECT * FROM v; +DROP VIEW v; +CREATE VIEW v AS SELECT * FROM (WITH qn AS (SELECT "with") SELECT * FROM qn) AS dt; +SHOW CREATE VIEW v; +SELECT * FROM v; +DROP VIEW v; + +CREATE TABLE t1 (a int); + +let $query= +WITH qne AS (SELECT a FROM t1), + qnm AS (SELECT a FROM t1), + qnea AS (SELECT a FROM t1), + qnma AS (SELECT a FROM t1) +SELECT +qne.a,qnm.a,alias1.a,alias2.a +FROM qne, qnm, qnea AS alias1, qnma AS alias2; + +eval explain $query; +DROP TABLE t1; + +--echo # Automatic index creation if materialized +CREATE TABLE t1 (a int); + +INSERT INTO t1(a) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(0); +ANALYZE TABLE t1; + +let $query= +WITH tt AS (SELECT * FROM t1) +SELECT /*+ no_merge(tt) */ tt.a +FROM t1 STRAIGHT_JOIN tt WHERE t1.a=tt.a +LIMIT 1; + +--echo # EXPLAIN should not fill the tmp table +flush STATUS; + +--echo # Should use auto_key0 and ref access. +--replace_column 10 # +eval explain $query; +SHOW STATUS LIKE "handler_write"; + +flush STATUS; +eval $query; +SHOW STATUS LIKE "handler_write"; + +--echo # With two references +let $query= +WITH tt AS (SELECT * FROM t1) +SELECT /*+ no_merge(tt) no_merge(tt_)*/ tt.a +FROM t1 STRAIGHT_JOIN tt STRAIGHT_JOIN tt AS tt_ +WHERE t1.a=tt.a AND tt.a=tt_.a +LIMIT 1; + +eval $query; + +--echo # One merged, one materialized: index creation on the second +--echo # should of course ignore the first +WITH q AS (SELECT * FROM t1) +SELECT /*+ merge(q) no_merge(q1) */ * FROM q, q q1 WHERE q.a=1 AND q1.a=2; + +DROP TABLE t1; + +--echo # Must not create more than 64 indexes. + +--disable_query_log +CREATE TABLE `t` ( + `c1` int(11) DEFAULT NULL, + `c2` int(11) DEFAULT NULL, + `c3` int(11) DEFAULT NULL, + `c4` int(11) DEFAULT NULL, + `c5` int(11) DEFAULT NULL, + `c6` int(11) DEFAULT NULL, + `c7` int(11) DEFAULT NULL, + `c8` int(11) DEFAULT NULL, + `c9` int(11) DEFAULT NULL, + `c10` int(11) DEFAULT NULL, + `c11` int(11) DEFAULT NULL, + `c12` int(11) DEFAULT NULL, + `c13` int(11) DEFAULT NULL, + `c14` int(11) DEFAULT NULL, + `c15` int(11) DEFAULT NULL, + `c16` int(11) DEFAULT NULL, + `c17` int(11) DEFAULT NULL, + `c18` int(11) DEFAULT NULL, + `c19` int(11) DEFAULT NULL, + `c20` int(11) DEFAULT NULL, + `c21` int(11) DEFAULT NULL, + `c22` int(11) DEFAULT NULL, + `c23` int(11) DEFAULT NULL, + `c24` int(11) DEFAULT NULL, + `c25` int(11) DEFAULT NULL, + `c26` int(11) DEFAULT NULL, + `c27` int(11) DEFAULT NULL, + `c28` int(11) DEFAULT NULL, + `c29` int(11) DEFAULT NULL, + `c30` int(11) DEFAULT NULL, + `c31` int(11) DEFAULT NULL, + `c32` int(11) DEFAULT NULL, + `c33` int(11) DEFAULT NULL, + `c34` int(11) DEFAULT NULL, + `c35` int(11) DEFAULT NULL, + `c36` int(11) DEFAULT NULL, + `c37` int(11) DEFAULT NULL, + `c38` int(11) DEFAULT NULL, + `c39` int(11) DEFAULT NULL, + `c40` int(11) DEFAULT NULL, + `c41` int(11) DEFAULT NULL, + `c42` int(11) DEFAULT NULL, + `c43` int(11) DEFAULT NULL, + `c44` int(11) DEFAULT NULL, + `c45` int(11) DEFAULT NULL, + `c46` int(11) DEFAULT NULL, + `c47` int(11) DEFAULT NULL, + `c48` int(11) DEFAULT NULL, + `c49` int(11) DEFAULT NULL, + `c50` int(11) DEFAULT NULL, + `c51` int(11) DEFAULT NULL, + `c52` int(11) DEFAULT NULL, + `c53` int(11) DEFAULT NULL, + `c54` int(11) DEFAULT NULL, + `c55` int(11) DEFAULT NULL, + `c56` int(11) DEFAULT NULL, + `c57` int(11) DEFAULT NULL, + `c58` int(11) DEFAULT NULL, + `c59` int(11) DEFAULT NULL, + `c60` int(11) DEFAULT NULL, + `c61` int(11) DEFAULT NULL, + `c62` int(11) DEFAULT NULL, + `c63` int(11) DEFAULT NULL, + `c64` int(11) DEFAULT NULL, + `c65` int(11) DEFAULT NULL, + `c66` int(11) DEFAULT NULL, + `c67` int(11) DEFAULT NULL, + `c68` int(11) DEFAULT NULL, + `c69` int(11) DEFAULT NULL, + `c70` int(11) DEFAULT NULL, + `c71` int(11) DEFAULT NULL, + `c72` int(11) DEFAULT NULL, + `c73` int(11) DEFAULT NULL, + `c74` int(11) DEFAULT NULL, + `c75` int(11) DEFAULT NULL, + `c76` int(11) DEFAULT NULL, + `c77` int(11) DEFAULT NULL, + `c78` int(11) DEFAULT NULL, + `c79` int(11) DEFAULT NULL, + `c80` int(11) DEFAULT NULL, + `c81` int(11) DEFAULT NULL, + `c82` int(11) DEFAULT NULL, + `c83` int(11) DEFAULT NULL, + `c84` int(11) DEFAULT NULL, + `c85` int(11) DEFAULT NULL, + `c86` int(11) DEFAULT NULL, + `c87` int(11) DEFAULT NULL, + `c88` int(11) DEFAULT NULL, + `c89` int(11) DEFAULT NULL, + `c90` int(11) DEFAULT NULL, + `c91` int(11) DEFAULT NULL, + `c92` int(11) DEFAULT NULL, + `c93` int(11) DEFAULT NULL, + `c94` int(11) DEFAULT NULL, + `c95` int(11) DEFAULT NULL, + `c96` int(11) DEFAULT NULL, + `c97` int(11) DEFAULT NULL, + `c98` int(11) DEFAULT NULL, + `c99` int(11) DEFAULT NULL, + `c100` int(11) DEFAULT NULL +); + +WITH qn AS (SELECT * FROM t LIMIT 2) +SELECT +(SELECT max(c1) FROM qn WHERE qn.c1=1), +(SELECT max(c2) FROM qn WHERE qn.c2=1), +(SELECT max(c3) FROM qn WHERE qn.c3=1), +(SELECT max(c4) FROM qn WHERE qn.c4=1), +(SELECT max(c5) FROM qn WHERE qn.c5=1), +(SELECT max(c6) FROM qn WHERE qn.c6=1), +(SELECT max(c7) FROM qn WHERE qn.c7=1), +(SELECT max(c8) FROM qn WHERE qn.c8=1), +(SELECT max(c9) FROM qn WHERE qn.c9=1), +(SELECT max(c10) FROM qn WHERE qn.c10=1), +(SELECT max(c11) FROM qn WHERE qn.c11=1), +(SELECT max(c12) FROM qn WHERE qn.c12=1), +(SELECT max(c13) FROM qn WHERE qn.c13=1), +(SELECT max(c14) FROM qn WHERE qn.c14=1), +(SELECT max(c15) FROM qn WHERE qn.c15=1), +(SELECT max(c16) FROM qn WHERE qn.c16=1), +(SELECT max(c17) FROM qn WHERE qn.c17=1), +(SELECT max(c18) FROM qn WHERE qn.c18=1), +(SELECT max(c19) FROM qn WHERE qn.c19=1), +(SELECT max(c20) FROM qn WHERE qn.c20=1), +(SELECT max(c21) FROM qn WHERE qn.c21=1), +(SELECT max(c22) FROM qn WHERE qn.c22=1), +(SELECT max(c23) FROM qn WHERE qn.c23=1), +(SELECT max(c24) FROM qn WHERE qn.c24=1), +(SELECT max(c25) FROM qn WHERE qn.c25=1), +(SELECT max(c26) FROM qn WHERE qn.c26=1), +(SELECT max(c27) FROM qn WHERE qn.c27=1), +(SELECT max(c28) FROM qn WHERE qn.c28=1), +(SELECT max(c29) FROM qn WHERE qn.c29=1), +(SELECT max(c30) FROM qn WHERE qn.c30=1), +(SELECT max(c31) FROM qn WHERE qn.c31=1), +(SELECT max(c32) FROM qn WHERE qn.c32=1), +(SELECT max(c33) FROM qn WHERE qn.c33=1), +(SELECT max(c34) FROM qn WHERE qn.c34=1), +(SELECT max(c35) FROM qn WHERE qn.c35=1), +(SELECT max(c36) FROM qn WHERE qn.c36=1), +(SELECT max(c37) FROM qn WHERE qn.c37=1), +(SELECT max(c38) FROM qn WHERE qn.c38=1), +(SELECT max(c39) FROM qn WHERE qn.c39=1), +(SELECT max(c40) FROM qn WHERE qn.c40=1), +(SELECT max(c41) FROM qn WHERE qn.c41=1), +(SELECT max(c42) FROM qn WHERE qn.c42=1), +(SELECT max(c43) FROM qn WHERE qn.c43=1), +(SELECT max(c44) FROM qn WHERE qn.c44=1), +(SELECT max(c45) FROM qn WHERE qn.c45=1), +(SELECT max(c46) FROM qn WHERE qn.c46=1), +(SELECT max(c47) FROM qn WHERE qn.c47=1), +(SELECT max(c48) FROM qn WHERE qn.c48=1), +(SELECT max(c49) FROM qn WHERE qn.c49=1), +(SELECT max(c50) FROM qn WHERE qn.c50=1), +(SELECT max(c51) FROM qn WHERE qn.c51=1), +(SELECT max(c52) FROM qn WHERE qn.c52=1), +(SELECT max(c53) FROM qn WHERE qn.c53=1), +(SELECT max(c54) FROM qn WHERE qn.c54=1), +(SELECT max(c55) FROM qn WHERE qn.c55=1), +(SELECT max(c56) FROM qn WHERE qn.c56=1), +(SELECT max(c57) FROM qn WHERE qn.c57=1), +(SELECT max(c58) FROM qn WHERE qn.c58=1), +(SELECT max(c59) FROM qn WHERE qn.c59=1), +(SELECT max(c60) FROM qn WHERE qn.c60=1), +(SELECT max(c61) FROM qn WHERE qn.c61=1), +(SELECT max(c62) FROM qn WHERE qn.c62=1), +(SELECT max(c63) FROM qn WHERE qn.c63=1), +(SELECT max(c64) FROM qn WHERE qn.c64=1), +(SELECT max(c65) FROM qn WHERE qn.c65=1), +(SELECT max(c66) FROM qn WHERE qn.c66=1), +(SELECT max(c67) FROM qn WHERE qn.c67=1), +(SELECT max(c68) FROM qn WHERE qn.c68=1), +(SELECT max(c69) FROM qn WHERE qn.c69=1), +(SELECT max(c70) FROM qn WHERE qn.c70=1), +(SELECT max(c71) FROM qn WHERE qn.c71=1), +(SELECT max(c72) FROM qn WHERE qn.c72=1), +(SELECT max(c73) FROM qn WHERE qn.c73=1), +(SELECT max(c74) FROM qn WHERE qn.c74=1), +(SELECT max(c75) FROM qn WHERE qn.c75=1), +(SELECT max(c76) FROM qn WHERE qn.c76=1), +(SELECT max(c77) FROM qn WHERE qn.c77=1), +(SELECT max(c78) FROM qn WHERE qn.c78=1), +(SELECT max(c79) FROM qn WHERE qn.c79=1), +(SELECT max(c80) FROM qn WHERE qn.c80=1), +(SELECT max(c81) FROM qn WHERE qn.c81=1), +(SELECT max(c82) FROM qn WHERE qn.c82=1), +(SELECT max(c83) FROM qn WHERE qn.c83=1), +(SELECT max(c84) FROM qn WHERE qn.c84=1), +(SELECT max(c85) FROM qn WHERE qn.c85=1), +(SELECT max(c86) FROM qn WHERE qn.c86=1), +(SELECT max(c87) FROM qn WHERE qn.c87=1), +(SELECT max(c88) FROM qn WHERE qn.c88=1), +(SELECT max(c89) FROM qn WHERE qn.c89=1), +(SELECT max(c90) FROM qn WHERE qn.c90=1), +(SELECT max(c91) FROM qn WHERE qn.c91=1), +(SELECT max(c92) FROM qn WHERE qn.c92=1), +(SELECT max(c93) FROM qn WHERE qn.c93=1), +(SELECT max(c94) FROM qn WHERE qn.c94=1), +(SELECT max(c95) FROM qn WHERE qn.c95=1), +(SELECT max(c96) FROM qn WHERE qn.c96=1), +(SELECT max(c97) FROM qn WHERE qn.c97=1), +(SELECT max(c98) FROM qn WHERE qn.c98=1), +(SELECT max(c99) FROM qn WHERE qn.c99=1), +(SELECT max(c100) FROM qn WHERE qn.c100=1) +FROM dual; +--enable_query_log + +DROP TABLE t; + +--echo # Choice between two auto_key: + +CREATE TABLE t1(a int, b int); +INSERT INTO t1 VALUES (NULL, 6), (NULL, 10); +let $query= +WITH t2 AS + (SELECT * FROM t1) +SELECT /*+ no_merge(t2) */ * FROM t2 +WHERE (a = a OR b <= 6) AND (a IS NULL); +--echo # Test the covering key; note that MEMORY doesn't use a +--echo # covering key (always reads the "data file"). But InnoDB does. +eval EXPLAIN $query; +eval $query; +DROP TABLE t1; + +--echo # QN referencing view of same name isn't a "recursive view", +--echo # shouldn't cause ER_VIEW_RECURSIVE + +CREATE VIEW v1 AS SELECT "with"; +WITH v1 AS (SELECT * FROM v1) SELECT * FROM v1; +DROP VIEW v1; + +--echo # QN inside view + +CREATE VIEW v1 AS +WITH qn AS (SELECT 1 AS col) SELECT * FROM qn; +SELECT * FROM v1; +DROP VIEW v1; +CREATE TABLE t1(a int, b int); + +--echo # Alas merge hints are ignored in views (filed Bug#23017428) +CREATE VIEW v1 AS +WITH qn AS (SELECT a FROM t1), + qn2 AS (SELECT b FROM t1) +SELECT /*+ merge(qn) no_merge(qn2) */ qn.a,qn2.b FROM qn, qn2; +explain SELECT * FROM v1; +DROP VIEW v1; + +--echo # Materializing view doesn't impose materializing query name +CREATE algorithm=temptable VIEW v1 AS +WITH qn AS (SELECT a FROM t1) +SELECT qn.a FROM qn; +explain SELECT * FROM v1; +DROP VIEW v1; + +DROP TABLE t1; + +--echo # CTE referenced four times, including in subqueries in other CTEs + +# One ROW per DAY, WITH amount sold ON that DAY: +CREATE TABLE sales_days(day_of_sale DATE, amount INT); +INSERT INTO sales_days VALUES +('2015-01-02', 100), ('2015-01-05', 200), +('2015-02-02', 10), ('2015-02-10', 100), +('2015-03-02', 10), ('2015-03-18', 1); + +WITH + # FIRST CTE: one ROW per MONTH, WITH amount sold ON ALL days OF MONTH + sales_by_month(MONTH,total) AS + (SELECT MONTH(day_of_sale), sum(amount) FROM sales_days + WHERE year(day_of_sale)=2015 + GROUP BY MONTH(day_of_sale)), + # SECOND CTE: best MONTH + best_month(MONTH, total, award) AS + (SELECT MONTH, total, "best" FROM sales_by_month + WHERE total=(SELECT max(total) FROM sales_by_month)), + # 3rd CTE: worst MONTH + worst_month(MONTH, total, award) AS + (SELECT MONTH, total, "worst" FROM sales_by_month + WHERE total=(SELECT min(total) FROM sales_by_month)) + # Now SHOW results: + SELECT * FROM best_month UNION ALL SELECT * FROM worst_month; + +DROP TABLE sales_days; + +WITH qn AS (SELECT 1) SELECT * FROM qn, qn qn1; + +CREATE TABLE t1(a int); +INSERT INTO t1 VALUES(1),(2); +WITH qn(a) AS (SELECT 1 FROM t1 LIMIT 2) +SELECT * FROM qn WHERE qn.a=(SELECT * FROM qn qn1 LIMIT 1) UNION SELECT 2; +DROP TABLE t1; + +SHOW STATUS LIKE 'Created_tmp_disk_tables'; diff --git a/mysql-test/include/with_recursive.inc b/mysql-test/include/with_recursive.inc new file mode 100644 index 00000000000..4f90a68177d --- /dev/null +++ b/mysql-test/include/with_recursive.inc @@ -0,0 +1,1073 @@ +SET @@max_recursive_iterations = 10000; +flush STATUS; + +--echo # Mutual recursion unsupported; cycles must have one node only +--error ER_RECURSIVE_WITHOUT_ANCHORS +WITH RECURSIVE qn AS (SELECT * FROM qn2), + qn2 AS (SELECT * FROM qn) +SELECT * FROM qn; + +--echo # At least one anchor member, all anchors before all recursive + +--error ER_RECURSIVE_WITHOUT_ANCHORS +WITH RECURSIVE qn AS + (SELECT 1 FROM qn) +SELECT count(*) FROM qn; + +WITH RECURSIVE qn AS + (SELECT 1 UNION ALL SELECT 1 FROM qn UNION ALL SELECT 1) +SELECT count(*) FROM qn; + +--error ER_RECURSIVE_WITHOUT_ANCHORS +WITH RECURSIVE qn AS + (SELECT 1 FROM qn UNION ALL SELECT 1 FROM qn) +SELECT * FROM qn; + +--echo # It's ok to have the RECURSIVE word without any recursive member + +WITH RECURSIVE qn AS + (SELECT 1 FROM dual UNION ALL + SELECT 1 FROM dual) +SELECT * FROM qn; + +--echo # UNION DISTINCT allowed + +WITH RECURSIVE qn AS + (SELECT 1 FROM dual UNION + SELECT 1 FROM qn) +SELECT * FROM qn; + +--echo # No aggregation on the QN +CREATE TABLE t1(b int); +INSERT INTO t1 VALUES(10),(20),(10); + +WITH RECURSIVE qn AS + (SELECT max(b) AS a FROM t1 UNION + SELECT a FROM qn) +SELECT * FROM qn; + +--error ER_NOT_STANDARD_COMPLIANT_RECURSIVE +WITH RECURSIVE qn AS + (SELECT b AS a FROM t1 UNION + SELECT max(a) FROM qn) +SELECT * FROM qn; + +--echo # No window functions +WITH RECURSIVE qn AS + (SELECT rank() over (ORDER BY b) AS a FROM t1 UNION + SELECT a FROM qn) +SELECT * FROM qn; +--error ER_NOT_STANDARD_COMPLIANT_RECURSIVE +WITH RECURSIVE qn AS + (SELECT b AS a FROM t1 UNION + SELECT rank() over (ORDER BY a) FROM qn) +SELECT * FROM qn; +DROP TABLE t1; + +--error ER_NOT_STANDARD_COMPLIANT_RECURSIVE +WITH RECURSIVE qn AS + (SELECT 1 AS a FROM dual UNION ALL + SELECT max(a) FROM qn) +SELECT * FROM qn; + +WITH RECURSIVE qn AS + (SELECT 1 AS a FROM dual GROUP BY a UNION ALL + SELECT a+1 FROM qn WHERE a<3) +SELECT * FROM qn; + +WITH RECURSIVE qn AS + (SELECT 1 AS a FROM dual UNION ALL + SELECT a FROM qn GROUP BY a) +SELECT count(*)FROM qn; + +--echo # No subquery referencing a QN +--error ER_NOT_STANDARD_COMPLIANT_RECURSIVE +WITH RECURSIVE qn AS ( +SELECT 1 FROM dual UNION ALL +SELECT 1 FROM dual WHERE 1 NOT in(SELECT * FROM qn)) +SELECT * FROM qn; + +--error ER_NOT_SUPPORTED_YET +WITH RECURSIVE qn AS ( +SELECT 1 FROM dual UNION ALL +SELECT 1 FROM qn +ORDER BY (SELECT * FROM qn)) +SELECT count(*) FROM qn; + +WITH RECURSIVE qn AS ( +SELECT 1 FROM dual UNION ALL +SELECT * FROM (SELECT * FROM qn) AS dt) +SELECT count(*) FROM qn; + +--error ER_NOT_SUPPORTED_YET +WITH RECURSIVE qn AS ( +SELECT 1 AS a FROM dual UNION ALL +SELECT 1 FROM qn +ORDER BY a) +SELECT count(*) FROM qn; +--echo # No matter if global, or attached to one recursive member. + +WITH RECURSIVE qn AS ( +SELECT 1 AS a FROM dual UNION ALL +(SELECT 1 FROM qn ORDER BY a)) +SELECT count(*) FROM qn; + +--echo # Allowed on non-recursive query block (though pointless) +WITH RECURSIVE qn AS ( +(SELECT 1 AS a FROM dual ORDER BY a) UNION ALL +SELECT a+1 FROM qn WHERE a<3) +SELECT * FROM qn; + +--error ER_NOT_SUPPORTED_YET +WITH RECURSIVE qn AS ( +SELECT 1 AS a FROM dual UNION ALL +SELECT 1 FROM qn +LIMIT 10) +SELECT count(*) FROM qn; + +WITH RECURSIVE qn AS ( +SELECT 1 AS a FROM dual UNION ALL +(SELECT 1 FROM qn LIMIT 10)) +SELECT count(*) FROM qn; + +WITH RECURSIVE qn AS +(SELECT 1 UNION ALL SELECT DISTINCT 3 FROM qn) +SELECT count(*) FROM qn; + +--error 4008 +WITH RECURSIVE qn AS (SELECT 1 FROM dual UNION ALL + SELECT 1 FROM dual + WHERE 1 NOT in(SELECT * FROM qn)) +SELECT * FROM qn; + +--echo # Numbers from 123 to 130: + +WITH RECURSIVE qn AS (SELECT 123 AS a UNION ALL SELECT 1+a FROM qn WHERE a<130) SELECT * FROM qn; + +--echo # One-level recursive sequence of numbers +WITH RECURSIVE qn AS (SELECT 1 AS n, 2 AS un UNION ALL SELECT 1+n, un*5-6 FROM qn WHERE n<10) SELECT * FROM qn; + +--echo # Fibonacci + +WITH RECURSIVE qn AS (SELECT 1 AS n, 1 AS un, 1 AS unp1 UNION ALL SELECT 1+n, unp1, un+unp1 FROM qn WHERE n<10) SELECT * FROM qn; + +--echo # Validate that cast(a_varchar as char) produces a varchar, not a +--echo # char. +CREATE TABLE t(c char(3), vc varchar(3), b binary(3), vb varbinary(3)); +CREATE TABLE u +SELECT cast(c AS char(4)), cast(vc AS char(4)), + cast(b AS binary(4)), cast(vb AS binary(4)), + "abc" AS literal_c, cast("abc" AS char(4)), + _binary "abc" AS literal_b, cast(_binary "abc" AS binary(4)) +FROM t; +SHOW CREATE TABLE u; +DROP TABLE t,u; + +--echo # if it used char the 'x' would fall off due to spaces. +WITH RECURSIVE qn AS (SELECT 1 AS n, cast('x' AS char(100)) AS un UNION ALL SELECT 1+n, concat(un,'x') FROM qn WHERE n<10) SELECT * FROM qn; + +--echo # String now growing at the left + +WITH RECURSIVE qn AS (SELECT cast("x" AS char(10)) AS a FROM dual +UNION ALL SELECT concat("x",a) FROM qn WHERE length(a)<10) SELECT * +FROM qn; + +--echo # Forgot cast-as-char(10) in anchor => qn.a column has length 1 +--echo # => concat() is cast as char(1) => truncation +--echo # => length is always 1 => infinite loop; + +let $query= +WITH RECURSIVE qn AS (SELECT "x" AS a FROM dual +UNION ALL SELECT concat("x",a) FROM qn WHERE length(a)<10) SELECT count(*) +FROM qn; +eval $query; + +--echo # Overflow integer type INT (max 4G) +--error ER_DATA_OUT_OF_RANGE +WITH RECURSIVE qn AS (SELECT 1 AS a FROM dual +UNION ALL SELECT a*2000 FROM qn WHERE a<10000000000000000000) SELECT count(*) FROM qn; + +--echo # Use Decimal +WITH RECURSIVE qn AS (SELECT cast(1 AS decimal(30,0)) AS a FROM dual +UNION ALL SELECT a*2000 FROM qn WHERE a<10000000000000000000) SELECT * FROM qn; + +#MDEV-16505 +WITH RECURSIVE qn AS (SELECT 123 AS a UNION #ALL +SELECT NULL FROM qn WHERE a IS NOT NULL) SELECT * FROM qn; + +--echo # Mixing really unrelated types: the goal is to report a sensible +--echo # error and not crash. + +--echo # The Point becomes a string which is an invalid integer, cast string to int -> result 0: + +WITH RECURSIVE qn AS ( +SELECT 1 AS a,1 +UNION ALL +SELECT a+1,ST_PointFromText('POINT(10 10)') FROM qn WHERE a<2) +SELECT * FROM qn; + +--echo # POINT in anchor => BLOB in tmp table => not MEMORY engine => Innodb +--error ER_CANT_CREATE_GEOMETRY_OBJECT +WITH RECURSIVE qn AS ( +SELECT 1 AS a,ST_PointFromText('POINT(10 10)') +UNION ALL +SELECT a+1,1 FROM qn WHERE a<2) +SELECT * FROM qn; + +--echo # Same number of columns in anchor and recursive members +--error ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT +WITH RECURSIVE qn AS +( +SELECT 1 +UNION ALL +SELECT 3, 0 FROM qn +) +SELECT * FROM qn; + +--echo # Mismatch in column name and column count; problem specific of +--echo # recursive CTE which creates tmp table earlier in preparation. +--error ER_WITH_COL_WRONG_LIST +WITH RECURSIVE q (b) AS (SELECT 1, 1 UNION ALL SELECT 1, 1 FROM q) +SELECT b FROM q; + +--echo # Cannot have two recursive refs in FROM: + +--error 4008 +WITH RECURSIVE qn AS ( +SELECT 123 AS a UNION ALL +SELECT 1+qn.a FROM qn, qn AS qn1 WHERE qn1.a<130) +SELECT * FROM qn; + +--echo # Prove that a materialized QN is shared among all references: + +flush STATUS; +WITH RECURSIVE qn AS ( +SELECT 123 AS a UNION ALL +SELECT 1+a FROM qn WHERE a<125) +SELECT * FROM qn; +SHOW STATUS LIKE "handler_write"; +flush STATUS; +WITH RECURSIVE qn AS ( +SELECT 123 AS a UNION ALL +SELECT 1+a FROM qn WHERE a<125) +SELECT * FROM qn, qn AS qn1; +SHOW STATUS LIKE "handler_write"; +SHOW STATUS LIKE 'Created_tmp%table%'; + +--echo # Also works if references are nested inside other query names: +flush STATUS; +WITH RECURSIVE inner_ AS ( +SELECT 123 AS a UNION ALL +SELECT 1+a FROM inner_ WHERE a<125), +outer_ AS (SELECT * FROM inner_ LIMIT 10) +SELECT * FROM outer_, outer_ AS outer1; +SHOW STATUS LIKE "handler_write"; + +flush STATUS; +WITH RECURSIVE inner_ AS ( +SELECT 123 AS a UNION ALL +SELECT 1+a FROM inner_ WHERE a<125), +outer_ AS +(SELECT inner_.a, inner1.a AS a1 +FROM inner_, inner_ AS inner1 LIMIT 10) +SELECT * FROM outer_; +SHOW STATUS LIKE "handler_write"; + +--echo # Even if the two query names are recursive: +flush STATUS; +WITH RECURSIVE inner_ AS ( +SELECT 123 AS a UNION ALL +SELECT 1+a FROM inner_ WHERE a<125), +outer_ AS +(SELECT a FROM inner_ UNION ALL +SELECT a*2 FROM outer_ WHERE a<1000) +SELECT a FROM outer_; +SHOW STATUS LIKE "handler_write"; + +--echo # Optimizer must be allowed to put the recursive reference first + +CREATE TABLE t1(a int); +INSERT INTO t1 VALUES(1),(2); + +--error ER_NOT_STANDARD_COMPLIANT_RECURSIVE +WITH RECURSIVE qn AS +( +SELECT 1 FROM t1 +UNION ALL +SELECT 1 FROM t1 LEFT JOIN qn ON 1 +) +SELECT * FROM qn; + +--echo # Empty anchor + +WITH RECURSIVE qn AS +( +SELECT a FROM t1 WHERE 0 +UNION ALL +SELECT a+1 FROM qn +) +SELECT * FROM qn; + +WITH RECURSIVE qn AS +( +SELECT a FROM t1 WHERE a>10 +UNION ALL +SELECT a+1 FROM qn +) +SELECT * FROM qn; + +--echo # UNION DISTINCT in anchor parts +INSERT INTO t1 VALUES(1),(2); +SET @c=0, @d=0; +--error ER_NOT_SUPPORTED_YET +WITH RECURSIVE qn AS +( +SELECT 1,0 AS col FROM t1 +UNION DISTINCT +SELECT 1,0 FROM t1 +UNION ALL +SELECT 3, 0*(@c:=@c+1) FROM qn WHERE @c<1 +UNION ALL +SELECT 3, 0*(@d:=@d+1) FROM qn WHERE @d<1 +) +SELECT * FROM qn; + +--echo # UNION DISTINCT affecting recursive member, followed by UNION ALL +INSERT INTO t1 VALUES(1),(2); + +SET @c=0, @d=0; +--error ER_NOT_SUPPORTED_YET +WITH RECURSIVE qn AS +( +SELECT 1,0 AS col FROM t1 +UNION DISTINCT +SELECT 3, 0*(@c:=@c+1) FROM qn WHERE @c<1 +UNION ALL +SELECT 3, 0*(@d:=@d+1) FROM qn WHERE @d<1 +) +SELECT * FROM qn; + +--echo # create select +CREATE TABLE t2 +WITH RECURSIVE qn AS ( +SELECT 123 AS a UNION ALL SELECT 1+a FROM qn WHERE a<130) +SELECT * FROM qn; +SELECT * FROM t2; +DROP TABLE t2; + +--echo # insert select +DELETE FROM t1; +INSERT INTO t1 +WITH RECURSIVE qn AS ( +SELECT 123 AS a UNION ALL SELECT 1+a FROM qn WHERE a<130) +SELECT * FROM qn; +SELECT * FROM t1; + +--echo # Using insertion target inside recursive query +DELETE FROM t1; +INSERT INTO t1 VALUES(1),(2); +INSERT INTO t1 +WITH RECURSIVE qn AS ( +SELECT 123 AS a UNION ALL SELECT 1+qn.a FROM qn, t1 WHERE qn.a<125) +SELECT * FROM qn; +SELECT * FROM t1; + +DROP TABLE t1; + +--echo # insert into tmp table (a likely use case) + +CREATE TEMPORARY TABLE t1(a int); +INSERT INTO t1 +WITH RECURSIVE qn AS ( +SELECT 123 AS a UNION ALL SELECT 1+a FROM qn WHERE a<130) +SELECT * FROM qn; +SELECT * FROM t1; +DROP TABLE t1; + +--echo # create view +CREATE VIEW v1 AS +WITH RECURSIVE qn AS ( +SELECT 123 AS a UNION ALL SELECT 1+a FROM qn WHERE a<130) +SELECT * FROM qn; +SELECT * FROM v1; +DROP VIEW v1; + +--echo # Recursive QN can be constant (0-row or 1-row) for the +--echo # optimizer if its members have impossible conditions: + +#EXPLAIN WITH RECURSIVE qn AS (SELECT 1 AS n WHERE 0 UNION ALL SELECT n+1 FROM qn WHERE 0) SELECT * FROM qn; +#WITH RECURSIVE qn AS (SELECT 1 AS n WHERE 0 UNION ALL SELECT n+1 FROM qn WHERE 0) SELECT * FROM qn; + +#EXPLAIN WITH RECURSIVE qn AS (SELECT 1 AS n WHERE 1 UNION ALL SELECT n+1 FROM qn WHERE 0) SELECT * FROM qn; +#WITH RECURSIVE qn AS (SELECT 1 AS n WHERE 0 UNION ALL SELECT n+1 FROM qn WHERE 0) SELECT * FROM qn; + +--echo # Recursive refs should never use indexes to read: +--echo # first, optimization of top query creates a key on q.b; +--echo # then optimization of scalar subquery, when it optimizes the +--echo # recursive member, must be prevented from re-using this key +--echo # (it was a bug that it re-used it, as the index is covering +--echo # and adjust_access_methods() has a heuristic which converts a +--echo # table scan to index scan, so it wrongly used an index scan). +let $query= +WITH RECURSIVE q (b) AS + (SELECT 1 UNION ALL SELECT 1+b FROM q WHERE b<10) + SELECT (SELECT q1.b FROM q AS q2 WHERE q2.b=3) FROM q AS q1 WHERE q1.b=3; +eval EXPLAIN $query; +eval $query; + +--echo # This is from my blog so I can use it here. +--echo # Tests depth-first etc + +CREATE TABLE employees ( +ID INT PRIMARY KEY, +NAME VARCHAR(100), +MANAGER_ID INT, +INDEX (MANAGER_ID), +FOREIGN KEY (MANAGER_ID) REFERENCES employees(ID) +); +INSERT INTO employees VALUES +(333, "Yasmina", NULL), +(198, "John", 333), +(692, "Tarek", 333), +(29, "Pedro", 198), +(4610, "Sarah", 29), +(72, "Pierre", 29), +(123, "Adil", 692); +ANALYZE TABLE employees; + +--echo # Depth-first. + +--echo # Also test column names, and their reference in the recursive member. +WITH RECURSIVE employees_extended(ID, NAME, PATH) +AS +( + SELECT ID, NAME, CAST(ID AS CHAR(200)) + FROM employees + WHERE MANAGER_ID IS NULL +UNION ALL + SELECT S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID) + FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID +) +SELECT * FROM employees_extended ORDER BY PATH; + +--echo # Breadth-first is likely what we get, if no ordering + +WITH RECURSIVE employees_extended +AS +( + SELECT ID, NAME, CAST(ID AS CHAR(200)) AS PATH + FROM employees + WHERE MANAGER_ID IS NULL +UNION ALL + SELECT S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID) + FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID +) +SELECT * FROM employees_extended; + +--echo # But to be really sure we have breadth-first, we generate a +--echo # numeric column SEQ. And sort by NAME, to have repeatable +--echo # order of siblings (who have the same SEQ). + +WITH RECURSIVE employees_extended +AS +( + SELECT 0 AS SEQ, ID, NAME, CAST(ID AS CHAR(200)) AS PATH + FROM employees + WHERE MANAGER_ID IS NULL +UNION ALL + SELECT M.SEQ+1, S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID) + FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID +) +SELECT * FROM employees_extended ORDER BY SEQ, NAME; + +--echo # Or, use a user variable, then all rows have different number: + +WITH RECURSIVE employees_extended +AS +( + SELECT (@s:=0) AS SEQ, ID, NAME, CAST(ID AS CHAR(200)) AS PATH + FROM employees + WHERE MANAGER_ID IS NULL +UNION ALL + SELECT (@s:=@s+1), S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID) + FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID +) +SELECT * FROM employees_extended ORDER BY SEQ; + +--echo # Direct & indirect reports of John = having John in their PATH + +WITH RECURSIVE employees_extended +AS +( + SELECT ID, NAME, CAST(ID AS CHAR(200)) AS PATH + FROM employees + WHERE MANAGER_ID IS NULL +UNION ALL + SELECT S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID) + FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID +) +SELECT * FROM employees_extended +WHERE FIND_IN_SET((SELECT ID FROM employees WHERE NAME='John'), + PATH); + +--echo # Exclude John, he's not a report of himself; +--echo # bonus: use a QN to cache his ID. + +WITH RECURSIVE employees_extended(ID, NAME, PATH) +AS +( + SELECT ID, NAME, CAST(ID AS CHAR(200)) + FROM employees + WHERE MANAGER_ID IS NULL +UNION ALL + SELECT S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID) + FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID +), +JOHN_ID AS (SELECT ID FROM employees WHERE NAME='John') +SELECT e.* FROM employees_extended e, JOHN_ID +WHERE FIND_IN_SET(JOHN_ID.ID, + PATH) + AND e.ID<>JOHN_ID.ID; + +--echo # Similar, but faster: start dive at John (and include him again). +WITH RECURSIVE employees_extended +AS +( + SELECT ID, NAME, CAST(ID AS CHAR(200)) AS PATH + FROM employees + WHERE NAME='John' +UNION ALL + SELECT S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID) + FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID +) +SELECT * FROM employees_extended; + +--echo # Get the management chain above Pierre: + +WITH RECURSIVE employees_extended +AS +( + SELECT ID, NAME, MANAGER_ID, CAST(ID AS CHAR(200)) AS PATH + FROM employees + WHERE NAME='Pierre' +UNION ALL + SELECT S.ID, S.NAME, S.MANAGER_ID, CONCAT(M.PATH, ",", S.ID) + FROM employees_extended M JOIN employees S ON M.MANAGER_ID=S.ID +) +SELECT * FROM employees_extended; + +--echo # Get the management chain above Pierre, without PATH + +WITH RECURSIVE employees_extended +AS +( + SELECT ID, NAME, MANAGER_ID + FROM employees + WHERE NAME='Pierre' +UNION ALL + SELECT S.ID, S.NAME, S.MANAGER_ID + FROM employees_extended M JOIN employees S ON M.MANAGER_ID=S.ID +) +SELECT * FROM employees_extended; + +--echo # Get the management chain above Pierre and Sarah, without PATH + +WITH RECURSIVE employees_extended +AS +( + SELECT ID, NAME, MANAGER_ID + FROM employees + WHERE NAME='Pierre' OR NAME='Sarah' +UNION ALL + SELECT S.ID, S.NAME, S.MANAGER_ID + FROM employees_extended M JOIN employees S ON M.MANAGER_ID=S.ID +) +SELECT * FROM employees_extended; + +--echo # Do it without duplicates + +WITH RECURSIVE employees_extended +AS +( + SELECT ID, NAME, MANAGER_ID + FROM employees + WHERE NAME='Pierre' OR NAME='Sarah' +UNION + SELECT S.ID, S.NAME, S.MANAGER_ID + FROM employees_extended M JOIN employees S ON M.MANAGER_ID=S.ID +) +SELECT * FROM employees_extended; + +--echo # Cycles. Introduce an oddity: + +--echo # Sarah is indirect report of John and is his manager. +UPDATE employees SET MANAGER_ID=4610 WHERE NAME="John"; + +--echo # Add cycle detection: the row closing a cycle is marked with +--echo # IS_CYCLE=1, which stops the iterations. The outer SELECT +--echo # could then want to see only that row, or only previous ones. +WITH RECURSIVE employees_extended(ID, NAME, PATH, IS_CYCLE) +AS +( + SELECT ID, NAME, CAST(ID AS CHAR(200)), 0 + FROM employees + WHERE NAME='John' +UNION ALL + SELECT S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID), FIND_IN_SET(S.ID, M.PATH) + FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID + WHERE M.IS_CYCLE=0 +) +SELECT * FROM employees_extended; + +DROP TABLE employees; + +--echo # Two recursive members. + +CREATE TABLE t1 (id int, name char(10), leftpar int, rightpar int); +INSERT INTO t1 VALUES +(1, "A", 2, 3), + (2, "LA", 4, 5), + (4, "LLA", 6, 7), + (6, "LLLA", NULL, NULL), + (7, "RLLA", NULL, NULL), + (5, "RLA", 8, 9), + (8, "LRLA", NULL, NULL), + (9, "RRLA", NULL, NULL), + (3, "RA", 10, 11), + (10, "LRA", 12, 13), + (11, "RRA", 14, 15), + (15, "RRRA", NULL, NULL), +(16, "B", 17, 18), + (17, "LB", NULL, NULL), + (18, "RB", NULL, NULL) +; + +--echo # Shuffle rows to make sure the algorithm works +--echo # with any read order of rows above + +CREATE TABLE t2 SELECT * FROM t1 ORDER BY rand(); + +--echo # Tree-walking query. We turn off the Query Cache: indeed +--echo # sometimes pb2 enables Query Cache and as we run twice the +--echo # same query the 2nd may not actually be executed so the value +--echo # of Created_tmp_tables displayed at end becomes "one less"). + +let $query= +WITH RECURSIVE tree_of_a AS +( +SELECT *, cast(id AS char(200)) AS path FROM t2 WHERE name="A" +UNION ALL +SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON +t2.id=tree_of_a.leftpar +UNION ALL +SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON +t2.id=tree_of_a.rightpar +) +SELECT * FROM tree_of_a; + +--echo # Note that without ORDER BY, order of rows would be random as BNL +--echo # implies that the randomized t2 is the driving table in the +--echo # joining of rows. + +--replace_column 10 # +eval EXPLAIN extended $query ORDER BY path; + +eval $query ORDER BY path; + +--echo # Equivalent query with one single recursive query block: + +WITH RECURSIVE tree_of_a AS +( +SELECT *, cast(id AS char(200)) AS path FROM t2 WHERE name="A" +UNION ALL +SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON +(t2.id=tree_of_a.leftpar OR t2.id=tree_of_a.rightpar) +) +SELECT * FROM tree_of_a +ORDER BY path; + +--echo # Demonstrate a case where an index is automatically created on +--echo # the derived table and used to read this table in the outer +--echo # query (but correctly not used to read it in the recursive +--echo # query). + +let $query= +WITH RECURSIVE tree_of_a AS +( +SELECT *, cast(id AS char(200)) AS path FROM t2 WHERE name="A" +UNION ALL +SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON +t2.id=tree_of_a.leftpar +UNION ALL +SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON +t2.id=tree_of_a.rightpar +) +SELECT * FROM tree_of_a WHERE id=2; + +--replace_column 10 # +eval EXPLAIN $query; + +eval $query; + +DROP TABLE t1,t2; + +let $query= +WITH RECURSIVE cte AS +( + SELECT 1 AS n UNION ALL + SELECT n+1 FROM cte WHERE n<10000 +) +SELECT sum(cte1.n*cte2.n*cte3.n)=2490508525950000 +FROM cte cte1, cte cte2, cte cte3 +WHERE cte1.n=cte2.n+10 AND cte2.n+20=cte3.n; + +--replace_column 10 # +eval EXPLAIN $query; +eval $query; + +--echo # +--echo # Transitive closure +--echo # + +CREATE TABLE nodes(id int); +CREATE TABLE arcs(from_id int, to_id int); +INSERT INTO nodes VALUES(1),(2),(3),(4),(5),(6),(7),(8); +INSERT INTO arcs VALUES(1,3), (3,6), (1,4), (4,6), (6,2), (2,1); + +--echo # UNION ALL leads to infinite loop as 1 is reachable from 1; +--echo # so we stop it with a maximum depth 8 (8 nodes in graph) + +WITH RECURSIVE cte AS +( + SELECT id, 0 AS depth FROM nodes WHERE id=1 + UNION ALL + SELECT to_id, depth+1 FROM arcs, cte + WHERE from_id=cte.id AND depth<8 +) +SELECT count(*), max(depth) FROM cte; + +--echo # Can use cycle detection: + +WITH RECURSIVE cte AS +( + SELECT id, cast(id AS char(200)) AS path, 0 AS is_cycle + FROM nodes WHERE id=1 + UNION ALL + SELECT to_id, concat(cte.path, ",", to_id), find_in_set(to_id, path) + FROM arcs, cte + WHERE from_id=cte.id AND is_cycle=0 +) +SELECT * FROM cte; + +--echo # It is simpler with DISTINCT: +WITH RECURSIVE cte AS +( + SELECT id FROM nodes WHERE id=1 + UNION + SELECT to_id FROM arcs, cte WHERE from_id=cte.id +) +SELECT * FROM cte; + +DROP TABLE nodes, arcs; + +--echo # Hash field and MEMORY don't work together. Make long distinct +--echo # key to force hash field, to see if it switches to InnoDB. + +--echo # Not too long key (500 bytes in latin1) +WITH RECURSIVE cte AS + ( + SELECT 1 AS n, + repeat('a',500) AS f, '' AS g, + '' AS h, '' AS i + UNION + SELECT n+1, + '','','','' + FROM cte WHERE n<100) +SELECT sum(n) FROM cte; + +SHOW STATUS LIKE 'Created_tmp_disk_tables'; + +--echo # Too long key (>3000 bytes in latin1) +WITH RECURSIVE cte AS + ( + SELECT 1 AS n, + repeat('a',500) AS f, repeat('a',500) AS g, + repeat('a',500) AS h, repeat('a',500) AS i, + repeat('a',500) AS j, repeat('a',500) AS k, + repeat('a',500) AS l, repeat('a',500) AS m + UNION + SELECT n+1, + '','','','','','','','' + FROM cte WHERE n<100) +SELECT sum(n) FROM cte; + +--echo # +--echo # In query planning, the recursive reference's row count is +--echo # said to be the estimated row count of all non-recursive query +--echo # blocks + +CREATE TABLE t1(a int); +--echo # 15 rows: +INSERT INTO t1 VALUES (), (), (), (), (), (), (), (), (), (), (), (), +(), (), (); +ANALYZE TABLE t1; +--echo # EXPLAIN says: in non-recursive QB we'll read 15 rows of t1, +--echo # in recursive QB we'll read 15 rows of qn, keep only 0.33 +--echo # due to WHERE, that makes 4 (due to rounding), and in the +--echo # derived table we'll thus have 15+4=19. That ignores +--echo # next repetitions of the recursive QB which are unpredictable. +EXPLAIN WITH RECURSIVE qn AS +(SELECT 1 AS a FROM t1 UNION ALL SELECT a+1 FROM qn WHERE qn.a<100) +SELECT * FROM qn; +EXPLAIN WITH RECURSIVE qn AS +(SELECT 1 AS a FROM t1 UNION DISTINCT SELECT a+1 FROM qn WHERE qn.a<100) +SELECT * FROM qn; +DROP TABLE t1; + +SHOW STATUS LIKE 'Created_tmp_disk_tables'; + +flush STATUS; + +WITH RECURSIVE q (b) AS +(SELECT 1 UNION ALL SELECT 1+b FROM q WHERE b<2000) +SELECT min(b),max(b),avg(b) FROM q; +SHOW STATUS LIKE 'Created_tmp_disk_tables'; + +--echo # Test when conversion to InnoDB affects recursive references which +--echo # are not open yet (those of q1): +flush STATUS; +WITH RECURSIVE q (b) AS +(SELECT 1 UNION ALL SELECT 1+b FROM q WHERE b<2000) +SELECT min(q.b),max(q.b),avg(q.b) FROM q, q AS q1; +SHOW STATUS LIKE 'Created_tmp_disk_tables'; + +--echo # Same, but make q1 the writer; this is to test overflow when +--echo # the writer isn't first in the 'tmp_tables' list + +flush STATUS; +WITH RECURSIVE q (b) AS +(SELECT 1 UNION ALL SELECT 1+b FROM q WHERE b<2000) +SELECT min(q.b),max(q.b),avg(q.b) FROM q RIGHT JOIN q AS q1 ON 1; +SHOW STATUS LIKE 'Created_tmp_disk_tables'; + +--echo # Test when outer query reads CTE with an index. +--echo # Overflow doesn't happen at same row as queries above, as this +--echo # table has an index which makes it grow faster. + +let $query= +WITH RECURSIVE q (b) AS +(SELECT 1 UNION ALL SELECT 1+b FROM q WHERE b<2000) +SELECT min(b),max(b),avg(b) FROM q WHERE b=300; + +eval EXPLAIN $query; +SHOW STATUS LIKE 'Created_tmp_disk_tables'; +eval $query; +SHOW STATUS LIKE 'Created_tmp_disk_tables'; + +--echo # Verify that rows come out in insertion order. +--echo # If they didn't, the sequences of @c and of 'b' +--echo # would not be identical and the sum wouldn't be +--echo # 1^2 + ... + 2000^2 = n(n+1)(2n+1)/6 = 2668667000 + +SET @c:=1; +flush STATUS; +WITH RECURSIVE q (b, c) AS +(SELECT 1, 1 UNION ALL SELECT (1+b), (@c:=(@c+1)) FROM q WHERE b<2000) +SELECT sum(b*c) FROM q; +SHOW STATUS LIKE 'Created_tmp_disk_tables'; + +--echo # +--echo # Bug#23495283 WL3634:ASSERTION `0' FAILED IN FIELD* ITEM::TMP_TABLE_FIELD_FROM_FIELD_TYPE +--echo # + +CREATE TABLE t1(c1 DATETIME, c2 INT, KEY(c1)); + +--error ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT +WITH RECURSIVE cte AS ( SELECT a.c1 AS field1, 0 AS cycle FROM (t1 AS a) +UNION ALL SELECT b.c2 FROM cte AS a JOIN t1 AS b) SELECT * FROM cte; + +DROP TABLE t1; + +--echo # +--echo # Bug#23645090 WL3634: INVALID WRITE AND READ VALGRIND ERRORS +--echo # + +CREATE TABLE A ( + col_date date DEFAULT NULL, + col_datetime_key datetime DEFAULT NULL, + col_time_key time DEFAULT NULL, + col_varchar_key varchar(1) DEFAULT NULL, + col_int_key int(11) DEFAULT NULL, + col_blob_key blob, + col_varchar varchar(1) DEFAULT NULL, + col_date_key date DEFAULT NULL, + col_time time DEFAULT NULL, + col_blob blob, + pk int(11) NOT NULL AUTO_INCREMENT, + col_int int(11) DEFAULT NULL, + col_datetime datetime DEFAULT NULL, + PRIMARY KEY (pk), + KEY col_datetime_key (col_datetime_key), + KEY col_time_key (col_time_key), + KEY col_varchar_key (col_varchar_key), + KEY col_int_key (col_int_key), + KEY col_blob_key (col_blob_key(255)), + KEY col_date_key (col_date_key) +) DEFAULT CHARSET=latin1; + +CREATE TABLE AA ( + col_varchar varchar(1) DEFAULT NULL, + col_date date DEFAULT NULL, + col_varchar_key varchar(1) DEFAULT NULL, + col_date_key date DEFAULT NULL, + col_datetime_key datetime DEFAULT NULL, + col_time_key time DEFAULT NULL, + pk int(11) NOT NULL AUTO_INCREMENT, + col_time time DEFAULT NULL, + col_int_key int(11) DEFAULT NULL, + col_datetime datetime DEFAULT NULL, + col_int int(11) DEFAULT NULL, + col_blob blob, + col_blob_key blob, + PRIMARY KEY (pk), + KEY col_varchar_key (col_varchar_key), + KEY col_date_key (col_date_key), + KEY col_datetime_key (col_datetime_key), + KEY col_time_key (col_time_key), + KEY col_int_key (col_int_key), + KEY col_blob_key (col_blob_key(255)) +) DEFAULT CHARSET=latin1; + +CREATE TABLE BB ( + col_date date DEFAULT NULL, + col_blob_key blob, + col_time time DEFAULT NULL, + col_varchar_key varchar(1) DEFAULT NULL, + col_varchar varchar(1) DEFAULT NULL, + col_blob blob, + pk int(11) NOT NULL AUTO_INCREMENT, + col_int_key int(11) DEFAULT NULL, + col_datetime datetime DEFAULT NULL, + col_time_key time DEFAULT NULL, + col_datetime_key datetime DEFAULT NULL, + col_date_key date DEFAULT NULL, + col_int int(11) DEFAULT NULL, + PRIMARY KEY (pk), + KEY col_blob_key (col_blob_key(255)), + KEY col_varchar_key (col_varchar_key), + KEY col_int_key (col_int_key), + KEY col_time_key (col_time_key), + KEY col_datetime_key (col_datetime_key), + KEY col_date_key (col_date_key) +) AUTO_INCREMENT=11 DEFAULT CHARSET=latin1; + +CREATE TABLE D ( + col_varchar_key varchar(1) DEFAULT NULL, + col_datetime datetime DEFAULT NULL, + col_date_key date DEFAULT NULL, + col_int int(11) DEFAULT NULL, + col_time time DEFAULT NULL, + col_blob blob, + col_int_key int(11) DEFAULT NULL, + col_blob_key blob, + col_varchar varchar(1) DEFAULT NULL, + col_datetime_key datetime DEFAULT NULL, + col_date date DEFAULT NULL, + col_time_key time DEFAULT NULL, + pk int(11) NOT NULL AUTO_INCREMENT, + PRIMARY KEY (pk), + KEY col_varchar_key (col_varchar_key), + KEY col_date_key (col_date_key), + KEY col_int_key (col_int_key), + KEY col_blob_key (col_blob_key(255)), + KEY col_datetime_key (col_datetime_key), + KEY col_time_key (col_time_key) +) DEFAULT CHARSET=latin1; + +WITH RECURSIVE cte AS ( +SELECT alias1 . `col_blob_key` AS field1, 0 AS cycle +FROM ( BB AS alias1 , ( D AS alias2 , AA AS alias3 ) ) +WHERE ( +alias1 . pk = 225 +OR ( alias1 . col_int_key = 69 AND alias1 . col_blob_key = 'p' ) +) +UNION ALL +SELECT t1.pk, t2.cycle +FROM cte AS t2 JOIN A AS t1 +WHERE t2.field1 = t1.`col_int_key` +AND t2.cycle =1 ) SELECT * FROM cte; + +DROP TABLE IF EXISTS A, AA, BB, D; + +--echo # +--echo # Bug#24962600 WL3634: SIG 11 IN HEAP_RRND AT STORAGE/HEAP/HP_RRND.C +--echo # + +create table t1(a int); + +# empty table +with recursive cte as (select * from t1 union select * from cte) + select * from cte; +insert into t1 values(1),(2); +# always-false WHERE +with recursive cte as (select * from t1 where 0 union select * from cte) + select * from cte; +# no matching rows +with recursive cte as (select * from t1 where a>3 union select * from cte) + select * from cte; + +drop table t1; + + +--echo # +--echo # Bug#26501463 WL10792: ASSERTION `!TABLE->HAS_NULL_ROW()' FAILED +--echo # + +CREATE TABLE D (col_int INT); + +CREATE TABLE C ( + col_int2 INT, + pk INT NOT NULL, + col_int INT, + PRIMARY KEY (pk) +); +INSERT INTO C VALUES +(7,1,3),(7,2,3),(5,3,4),(1,4,6),(5,5,2), +(5,6,9),(4,7,9),(7,8,3),(3,9,0),(5,10,3); + +CREATE TABLE BB ( + pk INT NOT NULL, + col_int INT, + PRIMARY KEY (pk) +); +INSERT INTO BB VALUES (1,0),(2,6),(3,2),(4,5),(5,0); + +WITH RECURSIVE cte AS ( +SELECT alias2 . col_int2 AS field1 FROM +D AS alias1 RIGHT JOIN + ( ( C AS alias2 LEFT JOIN BB AS alias3 + ON (( alias3 . pk = alias2 . col_int ) AND ( alias3 . pk = alias2 . pk ) ) ) ) +ON (alias3 . col_int <> alias2 . col_int2 ) +HAVING field1 <= 0 +UNION +SELECT cte.field1 FROM cte +) +SELECT * FROM cte; + +DROP TABLE BB,C,D; + +--echo # +--echo # Bug#26556025 ASSERTION `!SELECT_LEX->IS_RECURSIVE() || !TMP_TABLES' FAILED. +--echo # + +SET SQL_BUFFER_RESULT = 1; + +WITH RECURSIVE cte AS + (SELECT 1 AS n UNION SELECT n+1 FROM cte WHERE n<3) +SELECT * FROM cte; + +WITH RECURSIVE cte AS + (SELECT 1 AS n UNION ALL SELECT n+1 FROM cte WHERE n<3) +SELECT * FROM cte; + +SET SQL_BUFFER_RESULT = DEFAULT; |