diff options
Diffstat (limited to 'mysql-test/include/with_recursive.inc')
| -rw-r--r-- | mysql-test/include/with_recursive.inc | 1073 |
1 files changed, 1073 insertions, 0 deletions
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; |