diff options
Diffstat (limited to 'mysql-test/r/with_recursive.result')
| -rw-r--r-- | mysql-test/r/with_recursive.result | 2706 |
1 files changed, 2706 insertions, 0 deletions
diff --git a/mysql-test/r/with_recursive.result b/mysql-test/r/with_recursive.result new file mode 100644 index 00000000000..226439c180c --- /dev/null +++ b/mysql-test/r/with_recursive.result @@ -0,0 +1,2706 @@ +# In-memory tmp tables +set big_tables=0; +SET @@max_recursive_iterations = 10000; +flush STATUS; +# Mutual recursion unsupported; cycles must have one node only +WITH RECURSIVE qn AS (SELECT * FROM qn2), +qn2 AS (SELECT * FROM qn) +SELECT * FROM qn; +ERROR HY000: No anchors for recursive WITH element 'qn' +# At least one anchor member, all anchors before all recursive +WITH RECURSIVE qn AS +(SELECT 1 FROM qn) +SELECT count(*) FROM qn; +ERROR HY000: No anchors for recursive WITH element 'qn' +WITH RECURSIVE qn AS +(SELECT 1 UNION ALL SELECT 1 FROM qn UNION ALL SELECT 1) +SELECT count(*) FROM qn; +count(*) +20002 +WITH RECURSIVE qn AS +(SELECT 1 FROM qn UNION ALL SELECT 1 FROM qn) +SELECT * FROM qn; +ERROR HY000: No anchors for recursive WITH element 'qn' +# It's ok to have the RECURSIVE word without any recursive member +WITH RECURSIVE qn AS +(SELECT 1 FROM dual UNION ALL +SELECT 1 FROM dual) +SELECT * FROM qn; +1 +1 +1 +# UNION DISTINCT allowed +WITH RECURSIVE qn AS +(SELECT 1 FROM dual UNION +SELECT 1 FROM qn) +SELECT * FROM qn; +1 +1 +# No aggregation on the QN +CREATE TABLE t1(b int); +INSERT INTO t1 VALUES(10),(20),(10); +WITH RECURSIVE qn AS +(SELECT max(b) AS a FROM t1 UNION +SELECT a FROM qn) +SELECT * FROM qn; +a +20 +WITH RECURSIVE qn AS +(SELECT b AS a FROM t1 UNION +SELECT max(a) FROM qn) +SELECT * FROM qn; +ERROR HY000: Restrictions imposed on recursive definitions are violated for table 'qn' +# No window functions +WITH RECURSIVE qn AS +(SELECT rank() over (ORDER BY b) AS a FROM t1 UNION +SELECT a FROM qn) +SELECT * FROM qn; +a +1 +3 +WITH RECURSIVE qn AS +(SELECT b AS a FROM t1 UNION +SELECT rank() over (ORDER BY a) FROM qn) +SELECT * FROM qn; +ERROR HY000: Restrictions imposed on recursive definitions are violated for table 'qn' +DROP TABLE t1; +WITH RECURSIVE qn AS +(SELECT 1 AS a FROM dual UNION ALL +SELECT max(a) FROM qn) +SELECT * FROM qn; +ERROR HY000: Restrictions imposed on recursive definitions are violated for table 'qn' +WITH RECURSIVE qn AS +(SELECT 1 AS a FROM dual GROUP BY a UNION ALL +SELECT a+1 FROM qn WHERE a<3) +SELECT * FROM qn; +a +1 +2 +3 +WITH RECURSIVE qn AS +(SELECT 1 AS a FROM dual UNION ALL +SELECT a FROM qn GROUP BY a) +SELECT count(*)FROM qn; +count(*) +10001 +# No subquery referencing a QN +WITH RECURSIVE qn AS ( +SELECT 1 FROM dual UNION ALL +SELECT 1 FROM dual WHERE 1 NOT in(SELECT * FROM qn)) +SELECT * FROM qn; +ERROR HY000: Restrictions imposed on recursive definitions are violated for table 'qn' +WITH RECURSIVE qn AS ( +SELECT 1 FROM dual UNION ALL +SELECT 1 FROM qn +ORDER BY (SELECT * FROM qn)) +SELECT count(*) FROM qn; +ERROR 42000: This version of MariaDB doesn't yet support 'global ORDER_BY/LIMIT in recursive CTE spec' +WITH RECURSIVE qn AS ( +SELECT 1 FROM dual UNION ALL +SELECT * FROM (SELECT * FROM qn) AS dt) +SELECT count(*) FROM qn; +count(*) +10001 +WITH RECURSIVE qn AS ( +SELECT 1 AS a FROM dual UNION ALL +SELECT 1 FROM qn +ORDER BY a) +SELECT count(*) FROM qn; +ERROR 42000: This version of MariaDB doesn't yet support 'global ORDER_BY/LIMIT in recursive CTE spec' +# No matter if global, or attached to one recursive member. +WITH RECURSIVE qn AS ( +SELECT 1 AS a FROM dual UNION ALL +(SELECT 1 FROM qn ORDER BY a)) +SELECT count(*) FROM qn; +count(*) +10001 +# Allowed on non-recursive query block (though pointless) +WITH RECURSIVE qn AS ( +(SELECT 1 AS a FROM dual ORDER BY a) UNION ALL +SELECT a+1 FROM qn WHERE a<3) +SELECT * FROM qn; +a +1 +2 +3 +WITH RECURSIVE qn AS ( +SELECT 1 AS a FROM dual UNION ALL +SELECT 1 FROM qn +LIMIT 10) +SELECT count(*) FROM qn; +ERROR 42000: This version of MariaDB doesn't yet support 'global ORDER_BY/LIMIT in recursive CTE spec' +WITH RECURSIVE qn AS ( +SELECT 1 AS a FROM dual UNION ALL +(SELECT 1 FROM qn LIMIT 10)) +SELECT count(*) FROM qn; +count(*) +10001 +WITH RECURSIVE qn AS +(SELECT 1 UNION ALL SELECT DISTINCT 3 FROM qn) +SELECT count(*) FROM qn; +count(*) +10001 +WITH RECURSIVE qn AS (SELECT 1 FROM dual UNION ALL +SELECT 1 FROM dual +WHERE 1 NOT in(SELECT * FROM qn)) +SELECT * FROM qn; +ERROR HY000: Restrictions imposed on recursive definitions are violated for table 'qn' +# Numbers from 123 to 130: +WITH RECURSIVE qn AS (SELECT 123 AS a UNION ALL SELECT 1+a FROM qn WHERE a<130) SELECT * FROM qn; +a +123 +124 +125 +126 +127 +128 +129 +130 +# One-level recursive sequence of numbers +WITH RECURSIVE qn AS (SELECT 1 AS n, 2 AS un UNION ALL SELECT 1+n, un*5-6 FROM qn WHERE n<10) SELECT * FROM qn; +n un +1 2 +2 4 +3 14 +4 64 +5 314 +6 1564 +7 7814 +8 39064 +9 195314 +10 976564 +# Fibonacci +WITH RECURSIVE qn AS (SELECT 1 AS n, 1 AS un, 1 AS unp1 UNION ALL SELECT 1+n, unp1, un+unp1 FROM qn WHERE n<10) SELECT * FROM qn; +n un unp1 +1 1 1 +2 1 2 +3 2 3 +4 3 5 +5 5 8 +6 8 13 +7 13 21 +8 21 34 +9 34 55 +10 55 89 +# Validate that cast(a_varchar as char) produces a varchar, not a +# char. +CREATE TABLE t(c char(3), vc varchar(3), b binary(3), vb varbinary(3)); +CREATE TABLE u +SELECT cast(c AS char(4)), cast(vc AS char(4)), +cast(b AS binary(4)), cast(vb AS binary(4)), +"abc" AS literal_c, cast("abc" AS char(4)), +_binary "abc" AS literal_b, cast(_binary "abc" AS binary(4)) +FROM t; +SHOW CREATE TABLE u; +Table Create Table +u CREATE TABLE `u` ( + `cast(c AS char(4))` varchar(4) DEFAULT NULL, + `cast(vc AS char(4))` varchar(4) DEFAULT NULL, + `cast(b AS binary(4))` varbinary(4) DEFAULT NULL, + `cast(vb AS binary(4))` varbinary(4) DEFAULT NULL, + `literal_c` varchar(3) NOT NULL, + `cast("abc" AS char(4))` varchar(4) DEFAULT NULL, + `literal_b` varbinary(3) NOT NULL, + `cast(_binary "abc" AS binary(4))` varbinary(4) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t,u; +# if it used char the 'x' would fall off due to spaces. +WITH RECURSIVE qn AS (SELECT 1 AS n, cast('x' AS char(100)) AS un UNION ALL SELECT 1+n, concat(un,'x') FROM qn WHERE n<10) SELECT * FROM qn; +n un +1 x +2 xx +3 xxx +4 xxxx +5 xxxxx +6 xxxxxx +7 xxxxxxx +8 xxxxxxxx +9 xxxxxxxxx +10 xxxxxxxxxx +# String now growing at the left +WITH RECURSIVE qn AS (SELECT cast("x" AS char(10)) AS a FROM dual +UNION ALL SELECT concat("x",a) FROM qn WHERE length(a)<10) SELECT * +FROM qn; +a +x +xx +xxx +xxxx +xxxxx +xxxxxx +xxxxxxx +xxxxxxxx +xxxxxxxxx +xxxxxxxxxx +# Forgot cast-as-char(10) in anchor => qn.a column has length 1 +# => concat() is cast as char(1) => truncation +# => length is always 1 => infinite loop; +WITH RECURSIVE qn AS (SELECT "x" AS a FROM dual +UNION ALL SELECT concat("x",a) FROM qn WHERE length(a)<10) SELECT count(*) +FROM qn; +count(*) +10001 +# Overflow integer type INT (max 4G) +WITH RECURSIVE qn AS (SELECT 1 AS a FROM dual +UNION ALL SELECT a*2000 FROM qn WHERE a<10000000000000000000) SELECT count(*) FROM qn; +ERROR 22003: BIGINT value is out of range in '`qn`.`a` * 2000' +# Use Decimal +WITH RECURSIVE qn AS (SELECT cast(1 AS decimal(30,0)) AS a FROM dual +UNION ALL SELECT a*2000 FROM qn WHERE a<10000000000000000000) SELECT * FROM qn; +a +1 +2000 +4000000 +8000000000 +16000000000000 +32000000000000000 +64000000000000000000 +WITH RECURSIVE qn AS (SELECT 123 AS a UNION #ALL +SELECT NULL FROM qn WHERE a IS NOT NULL) SELECT * FROM qn; +a +123 +0 +# Mixing really unrelated types: the goal is to report a sensible +# error and not crash. +# The Point becomes a string which is an invalid integer, cast string to int -> result 0: +WITH RECURSIVE qn AS ( +SELECT 1 AS a,1 +UNION ALL +SELECT a+1,ST_PointFromText('POINT(10 10)') FROM qn WHERE a<2) +SELECT * FROM qn; +a 1 +1 1 +2 0 +# POINT in anchor => BLOB in tmp table => not MEMORY engine => Innodb +WITH RECURSIVE qn AS ( +SELECT 1 AS a,ST_PointFromText('POINT(10 10)') +UNION ALL +SELECT a+1,1 FROM qn WHERE a<2) +SELECT * FROM qn; +ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field +# Same number of columns in anchor and recursive members +WITH RECURSIVE qn AS +( +SELECT 1 +UNION ALL +SELECT 3, 0 FROM qn +) +SELECT * FROM qn; +ERROR 21000: The used SELECT statements have a different number of columns +# Mismatch in column name and column count; problem specific of +# recursive CTE which creates tmp table earlier in preparation. +WITH RECURSIVE q (b) AS (SELECT 1, 1 UNION ALL SELECT 1, 1 FROM q) +SELECT b FROM q; +ERROR HY000: WITH column list and SELECT field list have different column counts +# Cannot have two recursive refs in FROM: +WITH RECURSIVE qn AS ( +SELECT 123 AS a UNION ALL +SELECT 1+qn.a FROM qn, qn AS qn1 WHERE qn1.a<130) +SELECT * FROM qn; +ERROR HY000: Restrictions imposed on recursive definitions are violated for table 'qn' +# Prove that a materialized QN is shared among all references: +flush STATUS; +WITH RECURSIVE qn AS ( +SELECT 123 AS a UNION ALL +SELECT 1+a FROM qn WHERE a<125) +SELECT * FROM qn; +a +123 +124 +125 +SHOW STATUS LIKE "handler_write"; +Variable_name Value +Handler_write 0 +flush STATUS; +WITH RECURSIVE qn AS ( +SELECT 123 AS a UNION ALL +SELECT 1+a FROM qn WHERE a<125) +SELECT * FROM qn, qn AS qn1; +a a +123 123 +124 123 +125 123 +123 124 +124 124 +125 124 +123 125 +124 125 +125 125 +SHOW STATUS LIKE "handler_write"; +Variable_name Value +Handler_write 0 +SHOW STATUS LIKE 'Created_tmp%table%'; +Variable_name Value +Created_tmp_disk_tables 0 +Created_tmp_tables 5 +# Also works if references are nested inside other query names: +flush STATUS; +WITH RECURSIVE inner_ AS ( +SELECT 123 AS a UNION ALL +SELECT 1+a FROM inner_ WHERE a<125), +outer_ AS (SELECT * FROM inner_ LIMIT 10) +SELECT * FROM outer_, outer_ AS outer1; +a a +123 123 +124 123 +125 123 +123 124 +124 124 +125 124 +123 125 +124 125 +125 125 +SHOW STATUS LIKE "handler_write"; +Variable_name Value +Handler_write 0 +flush STATUS; +WITH RECURSIVE inner_ AS ( +SELECT 123 AS a UNION ALL +SELECT 1+a FROM inner_ WHERE a<125), +outer_ AS +(SELECT inner_.a, inner1.a AS a1 +FROM inner_, inner_ AS inner1 LIMIT 10) +SELECT * FROM outer_; +a a1 +123 123 +124 123 +125 123 +123 124 +124 124 +125 124 +123 125 +124 125 +125 125 +SHOW STATUS LIKE "handler_write"; +Variable_name Value +Handler_write 0 +# Even if the two query names are recursive: +flush STATUS; +WITH RECURSIVE inner_ AS ( +SELECT 123 AS a UNION ALL +SELECT 1+a FROM inner_ WHERE a<125), +outer_ AS +(SELECT a FROM inner_ UNION ALL +SELECT a*2 FROM outer_ WHERE a<1000) +SELECT a FROM outer_; +a +123 +124 +125 +246 +248 +250 +492 +496 +500 +984 +992 +1000 +1968 +1984 +SHOW STATUS LIKE "handler_write"; +Variable_name Value +Handler_write 0 +# Optimizer must be allowed to put the recursive reference first +CREATE TABLE t1(a int); +INSERT INTO t1 VALUES(1),(2); +WITH RECURSIVE qn AS +( +SELECT 1 FROM t1 +UNION ALL +SELECT 1 FROM t1 LEFT JOIN qn ON 1 +) +SELECT * FROM qn; +ERROR HY000: Restrictions imposed on recursive definitions are violated for table 'qn' +# Empty anchor +WITH RECURSIVE qn AS +( +SELECT a FROM t1 WHERE 0 +UNION ALL +SELECT a+1 FROM qn +) +SELECT * FROM qn; +a +WITH RECURSIVE qn AS +( +SELECT a FROM t1 WHERE a>10 +UNION ALL +SELECT a+1 FROM qn +) +SELECT * FROM qn; +a +# UNION DISTINCT in anchor parts +INSERT INTO t1 VALUES(1),(2); +SET @c=0, @d=0; +WITH RECURSIVE qn AS +( +SELECT 1,0 AS col FROM t1 +UNION DISTINCT +SELECT 1,0 FROM t1 +UNION ALL +SELECT 3, 0*(@c:=@c+1) FROM qn WHERE @c<1 +UNION ALL +SELECT 3, 0*(@d:=@d+1) FROM qn WHERE @d<1 +) +SELECT * FROM qn; +ERROR 42000: This version of MariaDB doesn't yet support 'mix of ALL and DISTINCT UNION operations in recursive CTE spec' +# UNION DISTINCT affecting recursive member, followed by UNION ALL +INSERT INTO t1 VALUES(1),(2); +SET @c=0, @d=0; +WITH RECURSIVE qn AS +( +SELECT 1,0 AS col FROM t1 +UNION DISTINCT +SELECT 3, 0*(@c:=@c+1) FROM qn WHERE @c<1 +UNION ALL +SELECT 3, 0*(@d:=@d+1) FROM qn WHERE @d<1 +) +SELECT * FROM qn; +ERROR 42000: This version of MariaDB doesn't yet support 'mix of ALL and DISTINCT UNION operations in recursive CTE spec' +# create select +CREATE TABLE t2 +WITH RECURSIVE qn AS ( +SELECT 123 AS a UNION ALL SELECT 1+a FROM qn WHERE a<130) +SELECT * FROM qn; +SELECT * FROM t2; +a +123 +124 +125 +126 +127 +128 +129 +130 +DROP TABLE t2; +# insert select +DELETE FROM t1; +INSERT INTO t1 +WITH RECURSIVE qn AS ( +SELECT 123 AS a UNION ALL SELECT 1+a FROM qn WHERE a<130) +SELECT * FROM qn; +SELECT * FROM t1; +a +123 +124 +125 +126 +127 +128 +129 +130 +# Using insertion target inside recursive query +DELETE FROM t1; +INSERT INTO t1 VALUES(1),(2); +INSERT INTO t1 +WITH RECURSIVE qn AS ( +SELECT 123 AS a UNION ALL SELECT 1+qn.a FROM qn, t1 WHERE qn.a<125) +SELECT * FROM qn; +SELECT * FROM t1; +a +1 +2 +123 +124 +124 +125 +125 +125 +125 +DROP TABLE t1; +# insert into tmp table (a likely use case) +CREATE TEMPORARY TABLE t1(a int); +INSERT INTO t1 +WITH RECURSIVE qn AS ( +SELECT 123 AS a UNION ALL SELECT 1+a FROM qn WHERE a<130) +SELECT * FROM qn; +SELECT * FROM t1; +a +123 +124 +125 +126 +127 +128 +129 +130 +DROP TABLE t1; +# create view +CREATE VIEW v1 AS +WITH RECURSIVE qn AS ( +SELECT 123 AS a UNION ALL SELECT 1+a FROM qn WHERE a<130) +SELECT * FROM qn; +SELECT * FROM v1; +a +123 +124 +125 +126 +127 +128 +129 +130 +DROP VIEW v1; +# Recursive QN can be constant (0-row or 1-row) for the +# optimizer if its members have impossible conditions: +# Recursive refs should never use indexes to read: +# first, optimization of top query creates a key on q.b; +# then optimization of scalar subquery, when it optimizes the +# recursive member, must be prevented from re-using this key +# (it was a bug that it re-used it, as the index is covering +# and adjust_access_methods() has a heuristic which converts a +# table scan to index scan, so it wrongly used an index scan). +EXPLAIN WITH RECURSIVE q (b) AS +(SELECT 1 UNION ALL SELECT 1+b FROM q WHERE b<10) +SELECT (SELECT q1.b FROM q AS q2 WHERE q2.b=3) FROM q AS q1 WHERE q1.b=3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using where +4 DEPENDENT SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 Using where +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +WITH RECURSIVE q (b) AS +(SELECT 1 UNION ALL SELECT 1+b FROM q WHERE b<10) +SELECT (SELECT q1.b FROM q AS q2 WHERE q2.b=3) FROM q AS q1 WHERE q1.b=3; +(SELECT q1.b FROM q AS q2 WHERE q2.b=3) +3 +# This is from my blog so I can use it here. +# Tests depth-first etc +CREATE TABLE employees ( +ID INT PRIMARY KEY, +NAME VARCHAR(100), +MANAGER_ID INT, +INDEX (MANAGER_ID), +FOREIGN KEY (MANAGER_ID) REFERENCES employees(ID) +); +INSERT INTO employees VALUES +(333, "Yasmina", NULL), +(198, "John", 333), +(692, "Tarek", 333), +(29, "Pedro", 198), +(4610, "Sarah", 29), +(72, "Pierre", 29), +(123, "Adil", 692); +ANALYZE TABLE employees; +Table Op Msg_type Msg_text +test.employees analyze status OK +# Depth-first. +# Also test column names, and their reference in the recursive member. +WITH RECURSIVE employees_extended(ID, NAME, PATH) +AS +( +SELECT ID, NAME, CAST(ID AS CHAR(200)) +FROM employees +WHERE MANAGER_ID IS NULL +UNION ALL +SELECT S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID) +FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID +) +SELECT * FROM employees_extended ORDER BY PATH; +ID NAME PATH +333 Yasmina 333 +198 John 333,198 +29 Pedro 333,198,29 +4610 Sarah 333,198,29,4610 +72 Pierre 333,198,29,72 +692 Tarek 333,692 +123 Adil 333,692,123 +# Breadth-first is likely what we get, if no ordering +WITH RECURSIVE employees_extended +AS +( +SELECT ID, NAME, CAST(ID AS CHAR(200)) AS PATH +FROM employees +WHERE MANAGER_ID IS NULL +UNION ALL +SELECT S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID) +FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID +) +SELECT * FROM employees_extended; +ID NAME PATH +333 Yasmina 333 +198 John 333,198 +692 Tarek 333,692 +29 Pedro 333,198,29 +123 Adil 333,692,123 +4610 Sarah 333,198,29,4610 +72 Pierre 333,198,29,72 +# But to be really sure we have breadth-first, we generate a +# numeric column SEQ. And sort by NAME, to have repeatable +# order of siblings (who have the same SEQ). +WITH RECURSIVE employees_extended +AS +( +SELECT 0 AS SEQ, ID, NAME, CAST(ID AS CHAR(200)) AS PATH +FROM employees +WHERE MANAGER_ID IS NULL +UNION ALL +SELECT M.SEQ+1, S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID) +FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID +) +SELECT * FROM employees_extended ORDER BY SEQ, NAME; +SEQ ID NAME PATH +0 333 Yasmina 333 +1 198 John 333,198 +1 692 Tarek 333,692 +2 123 Adil 333,692,123 +2 29 Pedro 333,198,29 +3 72 Pierre 333,198,29,72 +3 4610 Sarah 333,198,29,4610 +# Or, use a user variable, then all rows have different number: +WITH RECURSIVE employees_extended +AS +( +SELECT (@s:=0) AS SEQ, ID, NAME, CAST(ID AS CHAR(200)) AS PATH +FROM employees +WHERE MANAGER_ID IS NULL +UNION ALL +SELECT (@s:=@s+1), S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID) +FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID +) +SELECT * FROM employees_extended ORDER BY SEQ; +SEQ ID NAME PATH +0 333 Yasmina 333 +1 198 John 333,198 +2 692 Tarek 333,692 +3 29 Pedro 333,198,29 +4 123 Adil 333,692,123 +5 4610 Sarah 333,198,29,4610 +6 72 Pierre 333,198,29,72 +# Direct & indirect reports of John = having John in their PATH +WITH RECURSIVE employees_extended +AS +( +SELECT ID, NAME, CAST(ID AS CHAR(200)) AS PATH +FROM employees +WHERE MANAGER_ID IS NULL +UNION ALL +SELECT S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID) +FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID +) +SELECT * FROM employees_extended +WHERE FIND_IN_SET((SELECT ID FROM employees WHERE NAME='John'), +PATH); +ID NAME PATH +198 John 333,198 +29 Pedro 333,198,29 +4610 Sarah 333,198,29,4610 +72 Pierre 333,198,29,72 +# Exclude John, he's not a report of himself; +# bonus: use a QN to cache his ID. +WITH RECURSIVE employees_extended(ID, NAME, PATH) +AS +( +SELECT ID, NAME, CAST(ID AS CHAR(200)) +FROM employees +WHERE MANAGER_ID IS NULL +UNION ALL +SELECT S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID) +FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID +), +JOHN_ID AS (SELECT ID FROM employees WHERE NAME='John') +SELECT e.* FROM employees_extended e, JOHN_ID +WHERE FIND_IN_SET(JOHN_ID.ID, +PATH) +AND e.ID<>JOHN_ID.ID; +ID NAME PATH +29 Pedro 333,198,29 +4610 Sarah 333,198,29,4610 +72 Pierre 333,198,29,72 +# Similar, but faster: start dive at John (and include him again). +WITH RECURSIVE employees_extended +AS +( +SELECT ID, NAME, CAST(ID AS CHAR(200)) AS PATH +FROM employees +WHERE NAME='John' +UNION ALL +SELECT S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID) +FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID +) +SELECT * FROM employees_extended; +ID NAME PATH +198 John 198 +29 Pedro 198,29 +4610 Sarah 198,29,4610 +72 Pierre 198,29,72 +# Get the management chain above Pierre: +WITH RECURSIVE employees_extended +AS +( +SELECT ID, NAME, MANAGER_ID, CAST(ID AS CHAR(200)) AS PATH +FROM employees +WHERE NAME='Pierre' +UNION ALL +SELECT S.ID, S.NAME, S.MANAGER_ID, CONCAT(M.PATH, ",", S.ID) +FROM employees_extended M JOIN employees S ON M.MANAGER_ID=S.ID +) +SELECT * FROM employees_extended; +ID NAME MANAGER_ID PATH +72 Pierre 29 72 +29 Pedro 198 72,29 +198 John 333 72,29,198 +333 Yasmina NULL 72,29,198,333 +# Get the management chain above Pierre, without PATH +WITH RECURSIVE employees_extended +AS +( +SELECT ID, NAME, MANAGER_ID +FROM employees +WHERE NAME='Pierre' +UNION ALL +SELECT S.ID, S.NAME, S.MANAGER_ID +FROM employees_extended M JOIN employees S ON M.MANAGER_ID=S.ID +) +SELECT * FROM employees_extended; +ID NAME MANAGER_ID +72 Pierre 29 +29 Pedro 198 +198 John 333 +333 Yasmina NULL +# Get the management chain above Pierre and Sarah, without PATH +WITH RECURSIVE employees_extended +AS +( +SELECT ID, NAME, MANAGER_ID +FROM employees +WHERE NAME='Pierre' OR NAME='Sarah' +UNION ALL +SELECT S.ID, S.NAME, S.MANAGER_ID +FROM employees_extended M JOIN employees S ON M.MANAGER_ID=S.ID +) +SELECT * FROM employees_extended; +ID NAME MANAGER_ID +4610 Sarah 29 +72 Pierre 29 +29 Pedro 198 +29 Pedro 198 +198 John 333 +198 John 333 +333 Yasmina NULL +333 Yasmina NULL +# Do it without duplicates +WITH RECURSIVE employees_extended +AS +( +SELECT ID, NAME, MANAGER_ID +FROM employees +WHERE NAME='Pierre' OR NAME='Sarah' +UNION +SELECT S.ID, S.NAME, S.MANAGER_ID +FROM employees_extended M JOIN employees S ON M.MANAGER_ID=S.ID +) +SELECT * FROM employees_extended; +ID NAME MANAGER_ID +4610 Sarah 29 +72 Pierre 29 +29 Pedro 198 +198 John 333 +333 Yasmina NULL +# Cycles. Introduce an oddity: +# Sarah is indirect report of John and is his manager. +UPDATE employees SET MANAGER_ID=4610 WHERE NAME="John"; +# Add cycle detection: the row closing a cycle is marked with +# IS_CYCLE=1, which stops the iterations. The outer SELECT +# could then want to see only that row, or only previous ones. +WITH RECURSIVE employees_extended(ID, NAME, PATH, IS_CYCLE) +AS +( +SELECT ID, NAME, CAST(ID AS CHAR(200)), 0 +FROM employees +WHERE NAME='John' +UNION ALL +SELECT S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID), FIND_IN_SET(S.ID, M.PATH) +FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID +WHERE M.IS_CYCLE=0 +) +SELECT * FROM employees_extended; +ID NAME PATH IS_CYCLE +198 John 198 0 +29 Pedro 198,29 0 +4610 Sarah 198,29,4610 0 +72 Pierre 198,29,72 0 +198 John 198,29,4610,198 1 +DROP TABLE employees; +# Two recursive members. +CREATE TABLE t1 (id int, name char(10), leftpar int, rightpar int); +INSERT INTO t1 VALUES +(1, "A", 2, 3), +(2, "LA", 4, 5), +(4, "LLA", 6, 7), +(6, "LLLA", NULL, NULL), +(7, "RLLA", NULL, NULL), +(5, "RLA", 8, 9), +(8, "LRLA", NULL, NULL), +(9, "RRLA", NULL, NULL), +(3, "RA", 10, 11), +(10, "LRA", 12, 13), +(11, "RRA", 14, 15), +(15, "RRRA", NULL, NULL), +(16, "B", 17, 18), +(17, "LB", NULL, NULL), +(18, "RB", NULL, NULL) +; +# Shuffle rows to make sure the algorithm works +# with any read order of rows above +CREATE TABLE t2 SELECT * FROM t1 ORDER BY rand(); +# Tree-walking query. We turn off the Query Cache: indeed +# sometimes pb2 enables Query Cache and as we run twice the +# same query the 2nd may not actually be executed so the value +# of Created_tmp_tables displayed at end becomes "one less"). +# Note that without ORDER BY, order of rows would be random as BNL +# implies that the randomized t2 is the driving table in the +# joining of rows. +EXPLAIN extended WITH RECURSIVE tree_of_a AS +( +SELECT *, cast(id AS char(200)) AS path FROM t2 WHERE name="A" +UNION ALL +SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON +t2.id=tree_of_a.leftpar +UNION ALL +SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON +t2.id=tree_of_a.rightpar +) +SELECT * FROM tree_of_a ORDER BY path; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 15 # Using filesort +2 DERIVED t2 ALL NULL NULL NULL NULL 15 # Using where +3 RECURSIVE UNION t2 ALL NULL NULL NULL NULL 15 # Using where +3 RECURSIVE UNION <derived2> ref key0 key0 5 test.t2.id 2 # +4 RECURSIVE UNION t2 ALL NULL NULL NULL NULL 15 # Using where +4 RECURSIVE UNION <derived2> ref key0 key0 5 test.t2.id 2 # +NULL UNION RESULT <union2,3,4> ALL NULL NULL NULL NULL NULL # +Warnings: +Note 1003 with recursive tree_of_a as (select `test`.`t2`.`id` AS `id`,`test`.`t2`.`name` AS `name`,`test`.`t2`.`leftpar` AS `leftpar`,`test`.`t2`.`rightpar` AS `rightpar`,cast(`test`.`t2`.`id` as char(200) charset latin1) AS `path` from `test`.`t2` where `test`.`t2`.`name` = 'A' union all select `test`.`t2`.`id` AS `id`,`test`.`t2`.`name` AS `name`,`test`.`t2`.`leftpar` AS `leftpar`,`test`.`t2`.`rightpar` AS `rightpar`,concat(`tree_of_a`.`path`,',',`test`.`t2`.`id`) AS `concat(tree_of_a.path,",",t2.id)` from `test`.`t2` join `tree_of_a` where `tree_of_a`.`leftpar` = `test`.`t2`.`id` union all select `test`.`t2`.`id` AS `id`,`test`.`t2`.`name` AS `name`,`test`.`t2`.`leftpar` AS `leftpar`,`test`.`t2`.`rightpar` AS `rightpar`,concat(`tree_of_a`.`path`,',',`test`.`t2`.`id`) AS `concat(tree_of_a.path,",",t2.id)` from `test`.`t2` join `tree_of_a` where `tree_of_a`.`rightpar` = `test`.`t2`.`id`)select `tree_of_a`.`id` AS `id`,`tree_of_a`.`name` AS `name`,`tree_of_a`.`leftpar` AS `leftpar`,`tree_of_a`.`rightpar` AS `rightpar`,`tree_of_a`.`path` AS `path` from `tree_of_a` order by `tree_of_a`.`path` +WITH RECURSIVE tree_of_a AS +( +SELECT *, cast(id AS char(200)) AS path FROM t2 WHERE name="A" +UNION ALL +SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON +t2.id=tree_of_a.leftpar +UNION ALL +SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON +t2.id=tree_of_a.rightpar +) +SELECT * FROM tree_of_a ORDER BY path; +id name leftpar rightpar path +1 A 2 3 1 +2 LA 4 5 1,2 +4 LLA 6 7 1,2,4 +6 LLLA NULL NULL 1,2,4,6 +7 RLLA NULL NULL 1,2,4,7 +5 RLA 8 9 1,2,5 +8 LRLA NULL NULL 1,2,5,8 +9 RRLA NULL NULL 1,2,5,9 +3 RA 10 11 1,3 +10 LRA 12 13 1,3,10 +11 RRA 14 15 1,3,11 +15 RRRA NULL NULL 1,3,11,15 +# Equivalent query with one single recursive query block: +WITH RECURSIVE tree_of_a AS +( +SELECT *, cast(id AS char(200)) AS path FROM t2 WHERE name="A" +UNION ALL +SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON +(t2.id=tree_of_a.leftpar OR t2.id=tree_of_a.rightpar) +) +SELECT * FROM tree_of_a +ORDER BY path; +id name leftpar rightpar path +1 A 2 3 1 +2 LA 4 5 1,2 +4 LLA 6 7 1,2,4 +6 LLLA NULL NULL 1,2,4,6 +7 RLLA NULL NULL 1,2,4,7 +5 RLA 8 9 1,2,5 +8 LRLA NULL NULL 1,2,5,8 +9 RRLA NULL NULL 1,2,5,9 +3 RA 10 11 1,3 +10 LRA 12 13 1,3,10 +11 RRA 14 15 1,3,11 +15 RRRA NULL NULL 1,3,11,15 +# Demonstrate a case where an index is automatically created on +# the derived table and used to read this table in the outer +# query (but correctly not used to read it in the recursive +# query). +EXPLAIN WITH RECURSIVE tree_of_a AS +( +SELECT *, cast(id AS char(200)) AS path FROM t2 WHERE name="A" +UNION ALL +SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON +t2.id=tree_of_a.leftpar +UNION ALL +SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON +t2.id=tree_of_a.rightpar +) +SELECT * FROM tree_of_a WHERE id=2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 15 # +2 DERIVED t2 ALL NULL NULL NULL NULL 15 # +3 RECURSIVE UNION t2 ALL NULL NULL NULL NULL 15 # +3 RECURSIVE UNION <derived2> ref key0 key0 5 test.t2.id 2 # +4 RECURSIVE UNION t2 ALL NULL NULL NULL NULL 15 # +4 RECURSIVE UNION <derived2> ref key0 key0 5 test.t2.id 2 # +NULL UNION RESULT <union2,3,4> ALL NULL NULL NULL NULL NULL # +WITH RECURSIVE tree_of_a AS +( +SELECT *, cast(id AS char(200)) AS path FROM t2 WHERE name="A" +UNION ALL +SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON +t2.id=tree_of_a.leftpar +UNION ALL +SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON +t2.id=tree_of_a.rightpar +) +SELECT * FROM tree_of_a WHERE id=2; +id name leftpar rightpar path +2 LA 4 5 1,2 +DROP TABLE t1,t2; +EXPLAIN WITH RECURSIVE cte AS +( +SELECT 1 AS n UNION ALL +SELECT n+1 FROM cte WHERE n<10000 +) +SELECT sum(cte1.n*cte2.n*cte3.n)=2490508525950000 +FROM cte cte1, cte cte2, cte cte3 +WHERE cte1.n=cte2.n+10 AND cte2.n+20=cte3.n; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 # +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 # +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 # +2 DERIVED NULL NULL NULL NULL NULL NULL NULL # +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 # +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL # +WITH RECURSIVE cte AS +( +SELECT 1 AS n UNION ALL +SELECT n+1 FROM cte WHERE n<10000 +) +SELECT sum(cte1.n*cte2.n*cte3.n)=2490508525950000 +FROM cte cte1, cte cte2, cte cte3 +WHERE cte1.n=cte2.n+10 AND cte2.n+20=cte3.n; +sum(cte1.n*cte2.n*cte3.n)=2490508525950000 +1 +# +# Transitive closure +# +CREATE TABLE nodes(id int); +CREATE TABLE arcs(from_id int, to_id int); +INSERT INTO nodes VALUES(1),(2),(3),(4),(5),(6),(7),(8); +INSERT INTO arcs VALUES(1,3), (3,6), (1,4), (4,6), (6,2), (2,1); +# UNION ALL leads to infinite loop as 1 is reachable from 1; +# so we stop it with a maximum depth 8 (8 nodes in graph) +WITH RECURSIVE cte AS +( +SELECT id, 0 AS depth FROM nodes WHERE id=1 +UNION ALL +SELECT to_id, depth+1 FROM arcs, cte +WHERE from_id=cte.id AND depth<8 +) +SELECT count(*), max(depth) FROM cte; +count(*) max(depth) +25 8 +# Can use cycle detection: +WITH RECURSIVE cte AS +( +SELECT id, cast(id AS char(200)) AS path, 0 AS is_cycle +FROM nodes WHERE id=1 +UNION ALL +SELECT to_id, concat(cte.path, ",", to_id), find_in_set(to_id, path) +FROM arcs, cte +WHERE from_id=cte.id AND is_cycle=0 +) +SELECT * FROM cte; +id path is_cycle +1 1 0 +3 1,3 0 +4 1,4 0 +6 1,3,6 0 +6 1,4,6 0 +2 1,4,6,2 0 +2 1,3,6,2 0 +1 1,3,6,2,1 1 +1 1,4,6,2,1 1 +# It is simpler with DISTINCT: +WITH RECURSIVE cte AS +( +SELECT id FROM nodes WHERE id=1 +UNION +SELECT to_id FROM arcs, cte WHERE from_id=cte.id +) +SELECT * FROM cte; +id +1 +3 +4 +6 +2 +DROP TABLE nodes, arcs; +# Hash field and MEMORY don't work together. Make long distinct +# key to force hash field, to see if it switches to InnoDB. +# Not too long key (500 bytes in latin1) +WITH RECURSIVE cte AS +( +SELECT 1 AS n, +repeat('a',500) AS f, '' AS g, +'' AS h, '' AS i +UNION +SELECT n+1, +'','','','' + FROM cte WHERE n<100) +SELECT sum(n) FROM cte; +sum(n) +5050 +SHOW STATUS LIKE 'Created_tmp_disk_tables'; +Variable_name Value +Created_tmp_disk_tables 0 +# Too long key (>3000 bytes in latin1) +WITH RECURSIVE cte AS +( +SELECT 1 AS n, +repeat('a',500) AS f, repeat('a',500) AS g, +repeat('a',500) AS h, repeat('a',500) AS i, +repeat('a',500) AS j, repeat('a',500) AS k, +repeat('a',500) AS l, repeat('a',500) AS m +UNION +SELECT n+1, +'','','','','','','','' + FROM cte WHERE n<100) +SELECT sum(n) FROM cte; +sum(n) +5050 +# +# In query planning, the recursive reference's row count is +# said to be the estimated row count of all non-recursive query +# blocks +CREATE TABLE t1(a int); +# 15 rows: +INSERT INTO t1 VALUES (), (), (), (), (), (), (), (), (), (), (), (), +(), (), (); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +# EXPLAIN says: in non-recursive QB we'll read 15 rows of t1, +# in recursive QB we'll read 15 rows of qn, keep only 0.33 +# due to WHERE, that makes 4 (due to rounding), and in the +# derived table we'll thus have 15+4=19. That ignores +# next repetitions of the recursive QB which are unpredictable. +EXPLAIN WITH RECURSIVE qn AS +(SELECT 1 AS a FROM t1 UNION ALL SELECT a+1 FROM qn WHERE qn.a<100) +SELECT * FROM qn; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 15 +2 DERIVED t1 ALL NULL NULL NULL NULL 15 +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 15 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +EXPLAIN WITH RECURSIVE qn AS +(SELECT 1 AS a FROM t1 UNION DISTINCT SELECT a+1 FROM qn WHERE qn.a<100) +SELECT * FROM qn; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 15 +2 DERIVED t1 ALL NULL NULL NULL NULL 15 +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 15 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +DROP TABLE t1; +SHOW STATUS LIKE 'Created_tmp_disk_tables'; +Variable_name Value +Created_tmp_disk_tables 0 +flush STATUS; +WITH RECURSIVE q (b) AS +(SELECT 1 UNION ALL SELECT 1+b FROM q WHERE b<2000) +SELECT min(b),max(b),avg(b) FROM q; +min(b) max(b) avg(b) +1 2000 1000.5000 +SHOW STATUS LIKE 'Created_tmp_disk_tables'; +Variable_name Value +Created_tmp_disk_tables 0 +# Test when conversion to InnoDB affects recursive references which +# are not open yet (those of q1): +flush STATUS; +WITH RECURSIVE q (b) AS +(SELECT 1 UNION ALL SELECT 1+b FROM q WHERE b<2000) +SELECT min(q.b),max(q.b),avg(q.b) FROM q, q AS q1; +min(q.b) max(q.b) avg(q.b) +1 2000 1000.5000 +SHOW STATUS LIKE 'Created_tmp_disk_tables'; +Variable_name Value +Created_tmp_disk_tables 0 +# Same, but make q1 the writer; this is to test overflow when +# the writer isn't first in the 'tmp_tables' list +flush STATUS; +WITH RECURSIVE q (b) AS +(SELECT 1 UNION ALL SELECT 1+b FROM q WHERE b<2000) +SELECT min(q.b),max(q.b),avg(q.b) FROM q RIGHT JOIN q AS q1 ON 1; +min(q.b) max(q.b) avg(q.b) +1 2000 1000.5000 +SHOW STATUS LIKE 'Created_tmp_disk_tables'; +Variable_name Value +Created_tmp_disk_tables 0 +# Test when outer query reads CTE with an index. +# Overflow doesn't happen at same row as queries above, as this +# table has an index which makes it grow faster. +EXPLAIN WITH RECURSIVE q (b) AS +(SELECT 1 UNION ALL SELECT 1+b FROM q WHERE b<2000) +SELECT min(b),max(b),avg(b) FROM q WHERE b=300; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using where +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +SHOW STATUS LIKE 'Created_tmp_disk_tables'; +Variable_name Value +Created_tmp_disk_tables 0 +WITH RECURSIVE q (b) AS +(SELECT 1 UNION ALL SELECT 1+b FROM q WHERE b<2000) +SELECT min(b),max(b),avg(b) FROM q WHERE b=300; +min(b) max(b) avg(b) +300 300 300.0000 +SHOW STATUS LIKE 'Created_tmp_disk_tables'; +Variable_name Value +Created_tmp_disk_tables 0 +# Verify that rows come out in insertion order. +# If they didn't, the sequences of @c and of 'b' +# would not be identical and the sum wouldn't be +# 1^2 + ... + 2000^2 = n(n+1)(2n+1)/6 = 2668667000 +SET @c:=1; +flush STATUS; +WITH RECURSIVE q (b, c) AS +(SELECT 1, 1 UNION ALL SELECT (1+b), (@c:=(@c+1)) FROM q WHERE b<2000) +SELECT sum(b*c) FROM q; +sum(b*c) +2668667000 +SHOW STATUS LIKE 'Created_tmp_disk_tables'; +Variable_name Value +Created_tmp_disk_tables 0 +# +# Bug#23495283 WL3634:ASSERTION `0' FAILED IN FIELD* ITEM::TMP_TABLE_FIELD_FROM_FIELD_TYPE +# +CREATE TABLE t1(c1 DATETIME, c2 INT, KEY(c1)); +WITH RECURSIVE cte AS ( SELECT a.c1 AS field1, 0 AS cycle FROM (t1 AS a) +UNION ALL SELECT b.c2 FROM cte AS a JOIN t1 AS b) SELECT * FROM cte; +ERROR 21000: The used SELECT statements have a different number of columns +DROP TABLE t1; +# +# Bug#23645090 WL3634: INVALID WRITE AND READ VALGRIND ERRORS +# +CREATE TABLE A ( +col_date date DEFAULT NULL, +col_datetime_key datetime DEFAULT NULL, +col_time_key time DEFAULT NULL, +col_varchar_key varchar(1) DEFAULT NULL, +col_int_key int(11) DEFAULT NULL, +col_blob_key blob, +col_varchar varchar(1) DEFAULT NULL, +col_date_key date DEFAULT NULL, +col_time time DEFAULT NULL, +col_blob blob, +pk int(11) NOT NULL AUTO_INCREMENT, +col_int int(11) DEFAULT NULL, +col_datetime datetime DEFAULT NULL, +PRIMARY KEY (pk), +KEY col_datetime_key (col_datetime_key), +KEY col_time_key (col_time_key), +KEY col_varchar_key (col_varchar_key), +KEY col_int_key (col_int_key), +KEY col_blob_key (col_blob_key(255)), +KEY col_date_key (col_date_key) +) DEFAULT CHARSET=latin1; +CREATE TABLE AA ( +col_varchar varchar(1) DEFAULT NULL, +col_date date DEFAULT NULL, +col_varchar_key varchar(1) DEFAULT NULL, +col_date_key date DEFAULT NULL, +col_datetime_key datetime DEFAULT NULL, +col_time_key time DEFAULT NULL, +pk int(11) NOT NULL AUTO_INCREMENT, +col_time time DEFAULT NULL, +col_int_key int(11) DEFAULT NULL, +col_datetime datetime DEFAULT NULL, +col_int int(11) DEFAULT NULL, +col_blob blob, +col_blob_key blob, +PRIMARY KEY (pk), +KEY col_varchar_key (col_varchar_key), +KEY col_date_key (col_date_key), +KEY col_datetime_key (col_datetime_key), +KEY col_time_key (col_time_key), +KEY col_int_key (col_int_key), +KEY col_blob_key (col_blob_key(255)) +) DEFAULT CHARSET=latin1; +CREATE TABLE BB ( +col_date date DEFAULT NULL, +col_blob_key blob, +col_time time DEFAULT NULL, +col_varchar_key varchar(1) DEFAULT NULL, +col_varchar varchar(1) DEFAULT NULL, +col_blob blob, +pk int(11) NOT NULL AUTO_INCREMENT, +col_int_key int(11) DEFAULT NULL, +col_datetime datetime DEFAULT NULL, +col_time_key time DEFAULT NULL, +col_datetime_key datetime DEFAULT NULL, +col_date_key date DEFAULT NULL, +col_int int(11) DEFAULT NULL, +PRIMARY KEY (pk), +KEY col_blob_key (col_blob_key(255)), +KEY col_varchar_key (col_varchar_key), +KEY col_int_key (col_int_key), +KEY col_time_key (col_time_key), +KEY col_datetime_key (col_datetime_key), +KEY col_date_key (col_date_key) +) AUTO_INCREMENT=11 DEFAULT CHARSET=latin1; +CREATE TABLE D ( +col_varchar_key varchar(1) DEFAULT NULL, +col_datetime datetime DEFAULT NULL, +col_date_key date DEFAULT NULL, +col_int int(11) DEFAULT NULL, +col_time time DEFAULT NULL, +col_blob blob, +col_int_key int(11) DEFAULT NULL, +col_blob_key blob, +col_varchar varchar(1) DEFAULT NULL, +col_datetime_key datetime DEFAULT NULL, +col_date date DEFAULT NULL, +col_time_key time DEFAULT NULL, +pk int(11) NOT NULL AUTO_INCREMENT, +PRIMARY KEY (pk), +KEY col_varchar_key (col_varchar_key), +KEY col_date_key (col_date_key), +KEY col_int_key (col_int_key), +KEY col_blob_key (col_blob_key(255)), +KEY col_datetime_key (col_datetime_key), +KEY col_time_key (col_time_key) +) DEFAULT CHARSET=latin1; +WITH RECURSIVE cte AS ( +SELECT alias1 . `col_blob_key` AS field1, 0 AS cycle +FROM ( BB AS alias1 , ( D AS alias2 , AA AS alias3 ) ) +WHERE ( +alias1 . pk = 225 +OR ( alias1 . col_int_key = 69 AND alias1 . col_blob_key = 'p' ) +) +UNION ALL +SELECT t1.pk, t2.cycle +FROM cte AS t2 JOIN A AS t1 +WHERE t2.field1 = t1.`col_int_key` +AND t2.cycle =1 ) SELECT * FROM cte; +field1 cycle +DROP TABLE IF EXISTS A, AA, BB, D; +# +# Bug#24962600 WL3634: SIG 11 IN HEAP_RRND AT STORAGE/HEAP/HP_RRND.C +# +create table t1(a int); +with recursive cte as (select * from t1 union select * from cte) +select * from cte; +a +insert into t1 values(1),(2); +with recursive cte as (select * from t1 where 0 union select * from cte) +select * from cte; +a +with recursive cte as (select * from t1 where a>3 union select * from cte) +select * from cte; +a +drop table t1; +# +# Bug#26501463 WL10792: ASSERTION `!TABLE->HAS_NULL_ROW()' FAILED +# +CREATE TABLE D (col_int INT); +CREATE TABLE C ( +col_int2 INT, +pk INT NOT NULL, +col_int INT, +PRIMARY KEY (pk) +); +INSERT INTO C VALUES +(7,1,3),(7,2,3),(5,3,4),(1,4,6),(5,5,2), +(5,6,9),(4,7,9),(7,8,3),(3,9,0),(5,10,3); +CREATE TABLE BB ( +pk INT NOT NULL, +col_int INT, +PRIMARY KEY (pk) +); +INSERT INTO BB VALUES (1,0),(2,6),(3,2),(4,5),(5,0); +WITH RECURSIVE cte AS ( +SELECT alias2 . col_int2 AS field1 FROM +D AS alias1 RIGHT JOIN +( ( C AS alias2 LEFT JOIN BB AS alias3 +ON (( alias3 . pk = alias2 . col_int ) AND ( alias3 . pk = alias2 . pk ) ) ) ) +ON (alias3 . col_int <> alias2 . col_int2 ) +HAVING field1 <= 0 +UNION +SELECT cte.field1 FROM cte +) +SELECT * FROM cte; +field1 +DROP TABLE BB,C,D; +# +# Bug#26556025 ASSERTION `!SELECT_LEX->IS_RECURSIVE() || !TMP_TABLES' FAILED. +# +SET SQL_BUFFER_RESULT = 1; +WITH RECURSIVE cte AS +(SELECT 1 AS n UNION SELECT n+1 FROM cte WHERE n<3) +SELECT * FROM cte; +n +1 +2 +3 +WITH RECURSIVE cte AS +(SELECT 1 AS n UNION ALL SELECT n+1 FROM cte WHERE n<3) +SELECT * FROM cte; +n +1 +2 +3 +SET SQL_BUFFER_RESULT = DEFAULT; +# On-disk tmp tables +set big_tables=1; +SET @@max_recursive_iterations = 10000; +flush STATUS; +# Mutual recursion unsupported; cycles must have one node only +WITH RECURSIVE qn AS (SELECT * FROM qn2), +qn2 AS (SELECT * FROM qn) +SELECT * FROM qn; +ERROR HY000: No anchors for recursive WITH element 'qn' +# At least one anchor member, all anchors before all recursive +WITH RECURSIVE qn AS +(SELECT 1 FROM qn) +SELECT count(*) FROM qn; +ERROR HY000: No anchors for recursive WITH element 'qn' +WITH RECURSIVE qn AS +(SELECT 1 UNION ALL SELECT 1 FROM qn UNION ALL SELECT 1) +SELECT count(*) FROM qn; +count(*) +20002 +WITH RECURSIVE qn AS +(SELECT 1 FROM qn UNION ALL SELECT 1 FROM qn) +SELECT * FROM qn; +ERROR HY000: No anchors for recursive WITH element 'qn' +# It's ok to have the RECURSIVE word without any recursive member +WITH RECURSIVE qn AS +(SELECT 1 FROM dual UNION ALL +SELECT 1 FROM dual) +SELECT * FROM qn; +1 +1 +1 +# UNION DISTINCT allowed +WITH RECURSIVE qn AS +(SELECT 1 FROM dual UNION +SELECT 1 FROM qn) +SELECT * FROM qn; +1 +1 +# No aggregation on the QN +CREATE TABLE t1(b int); +INSERT INTO t1 VALUES(10),(20),(10); +WITH RECURSIVE qn AS +(SELECT max(b) AS a FROM t1 UNION +SELECT a FROM qn) +SELECT * FROM qn; +a +20 +WITH RECURSIVE qn AS +(SELECT b AS a FROM t1 UNION +SELECT max(a) FROM qn) +SELECT * FROM qn; +ERROR HY000: Restrictions imposed on recursive definitions are violated for table 'qn' +# No window functions +WITH RECURSIVE qn AS +(SELECT rank() over (ORDER BY b) AS a FROM t1 UNION +SELECT a FROM qn) +SELECT * FROM qn; +a +1 +3 +WITH RECURSIVE qn AS +(SELECT b AS a FROM t1 UNION +SELECT rank() over (ORDER BY a) FROM qn) +SELECT * FROM qn; +ERROR HY000: Restrictions imposed on recursive definitions are violated for table 'qn' +DROP TABLE t1; +WITH RECURSIVE qn AS +(SELECT 1 AS a FROM dual UNION ALL +SELECT max(a) FROM qn) +SELECT * FROM qn; +ERROR HY000: Restrictions imposed on recursive definitions are violated for table 'qn' +WITH RECURSIVE qn AS +(SELECT 1 AS a FROM dual GROUP BY a UNION ALL +SELECT a+1 FROM qn WHERE a<3) +SELECT * FROM qn; +a +1 +2 +3 +WITH RECURSIVE qn AS +(SELECT 1 AS a FROM dual UNION ALL +SELECT a FROM qn GROUP BY a) +SELECT count(*)FROM qn; +count(*) +10001 +# No subquery referencing a QN +WITH RECURSIVE qn AS ( +SELECT 1 FROM dual UNION ALL +SELECT 1 FROM dual WHERE 1 NOT in(SELECT * FROM qn)) +SELECT * FROM qn; +ERROR HY000: Restrictions imposed on recursive definitions are violated for table 'qn' +WITH RECURSIVE qn AS ( +SELECT 1 FROM dual UNION ALL +SELECT 1 FROM qn +ORDER BY (SELECT * FROM qn)) +SELECT count(*) FROM qn; +ERROR 42000: This version of MariaDB doesn't yet support 'global ORDER_BY/LIMIT in recursive CTE spec' +WITH RECURSIVE qn AS ( +SELECT 1 FROM dual UNION ALL +SELECT * FROM (SELECT * FROM qn) AS dt) +SELECT count(*) FROM qn; +count(*) +10001 +WITH RECURSIVE qn AS ( +SELECT 1 AS a FROM dual UNION ALL +SELECT 1 FROM qn +ORDER BY a) +SELECT count(*) FROM qn; +ERROR 42000: This version of MariaDB doesn't yet support 'global ORDER_BY/LIMIT in recursive CTE spec' +# No matter if global, or attached to one recursive member. +WITH RECURSIVE qn AS ( +SELECT 1 AS a FROM dual UNION ALL +(SELECT 1 FROM qn ORDER BY a)) +SELECT count(*) FROM qn; +count(*) +10001 +# Allowed on non-recursive query block (though pointless) +WITH RECURSIVE qn AS ( +(SELECT 1 AS a FROM dual ORDER BY a) UNION ALL +SELECT a+1 FROM qn WHERE a<3) +SELECT * FROM qn; +a +1 +2 +3 +WITH RECURSIVE qn AS ( +SELECT 1 AS a FROM dual UNION ALL +SELECT 1 FROM qn +LIMIT 10) +SELECT count(*) FROM qn; +ERROR 42000: This version of MariaDB doesn't yet support 'global ORDER_BY/LIMIT in recursive CTE spec' +WITH RECURSIVE qn AS ( +SELECT 1 AS a FROM dual UNION ALL +(SELECT 1 FROM qn LIMIT 10)) +SELECT count(*) FROM qn; +count(*) +10001 +WITH RECURSIVE qn AS +(SELECT 1 UNION ALL SELECT DISTINCT 3 FROM qn) +SELECT count(*) FROM qn; +count(*) +10001 +WITH RECURSIVE qn AS (SELECT 1 FROM dual UNION ALL +SELECT 1 FROM dual +WHERE 1 NOT in(SELECT * FROM qn)) +SELECT * FROM qn; +ERROR HY000: Restrictions imposed on recursive definitions are violated for table 'qn' +# Numbers from 123 to 130: +WITH RECURSIVE qn AS (SELECT 123 AS a UNION ALL SELECT 1+a FROM qn WHERE a<130) SELECT * FROM qn; +a +123 +124 +125 +126 +127 +128 +129 +130 +# One-level recursive sequence of numbers +WITH RECURSIVE qn AS (SELECT 1 AS n, 2 AS un UNION ALL SELECT 1+n, un*5-6 FROM qn WHERE n<10) SELECT * FROM qn; +n un +1 2 +2 4 +3 14 +4 64 +5 314 +6 1564 +7 7814 +8 39064 +9 195314 +10 976564 +# Fibonacci +WITH RECURSIVE qn AS (SELECT 1 AS n, 1 AS un, 1 AS unp1 UNION ALL SELECT 1+n, unp1, un+unp1 FROM qn WHERE n<10) SELECT * FROM qn; +n un unp1 +1 1 1 +2 1 2 +3 2 3 +4 3 5 +5 5 8 +6 8 13 +7 13 21 +8 21 34 +9 34 55 +10 55 89 +# Validate that cast(a_varchar as char) produces a varchar, not a +# char. +CREATE TABLE t(c char(3), vc varchar(3), b binary(3), vb varbinary(3)); +CREATE TABLE u +SELECT cast(c AS char(4)), cast(vc AS char(4)), +cast(b AS binary(4)), cast(vb AS binary(4)), +"abc" AS literal_c, cast("abc" AS char(4)), +_binary "abc" AS literal_b, cast(_binary "abc" AS binary(4)) +FROM t; +SHOW CREATE TABLE u; +Table Create Table +u CREATE TABLE `u` ( + `cast(c AS char(4))` varchar(4) DEFAULT NULL, + `cast(vc AS char(4))` varchar(4) DEFAULT NULL, + `cast(b AS binary(4))` varbinary(4) DEFAULT NULL, + `cast(vb AS binary(4))` varbinary(4) DEFAULT NULL, + `literal_c` varchar(3) NOT NULL, + `cast("abc" AS char(4))` varchar(4) DEFAULT NULL, + `literal_b` varbinary(3) NOT NULL, + `cast(_binary "abc" AS binary(4))` varbinary(4) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t,u; +# if it used char the 'x' would fall off due to spaces. +WITH RECURSIVE qn AS (SELECT 1 AS n, cast('x' AS char(100)) AS un UNION ALL SELECT 1+n, concat(un,'x') FROM qn WHERE n<10) SELECT * FROM qn; +n un +1 x +2 xx +3 xxx +4 xxxx +5 xxxxx +6 xxxxxx +7 xxxxxxx +8 xxxxxxxx +9 xxxxxxxxx +10 xxxxxxxxxx +# String now growing at the left +WITH RECURSIVE qn AS (SELECT cast("x" AS char(10)) AS a FROM dual +UNION ALL SELECT concat("x",a) FROM qn WHERE length(a)<10) SELECT * +FROM qn; +a +x +xx +xxx +xxxx +xxxxx +xxxxxx +xxxxxxx +xxxxxxxx +xxxxxxxxx +xxxxxxxxxx +# Forgot cast-as-char(10) in anchor => qn.a column has length 1 +# => concat() is cast as char(1) => truncation +# => length is always 1 => infinite loop; +WITH RECURSIVE qn AS (SELECT "x" AS a FROM dual +UNION ALL SELECT concat("x",a) FROM qn WHERE length(a)<10) SELECT count(*) +FROM qn; +count(*) +10001 +# Overflow integer type INT (max 4G) +WITH RECURSIVE qn AS (SELECT 1 AS a FROM dual +UNION ALL SELECT a*2000 FROM qn WHERE a<10000000000000000000) SELECT count(*) FROM qn; +ERROR 22003: BIGINT value is out of range in '`qn`.`a` * 2000' +# Use Decimal +WITH RECURSIVE qn AS (SELECT cast(1 AS decimal(30,0)) AS a FROM dual +UNION ALL SELECT a*2000 FROM qn WHERE a<10000000000000000000) SELECT * FROM qn; +a +1 +2000 +4000000 +8000000000 +16000000000000 +32000000000000000 +64000000000000000000 +WITH RECURSIVE qn AS (SELECT 123 AS a UNION #ALL +SELECT NULL FROM qn WHERE a IS NOT NULL) SELECT * FROM qn; +a +123 +0 +# Mixing really unrelated types: the goal is to report a sensible +# error and not crash. +# The Point becomes a string which is an invalid integer, cast string to int -> result 0: +WITH RECURSIVE qn AS ( +SELECT 1 AS a,1 +UNION ALL +SELECT a+1,ST_PointFromText('POINT(10 10)') FROM qn WHERE a<2) +SELECT * FROM qn; +a 1 +1 1 +2 0 +# POINT in anchor => BLOB in tmp table => not MEMORY engine => Innodb +WITH RECURSIVE qn AS ( +SELECT 1 AS a,ST_PointFromText('POINT(10 10)') +UNION ALL +SELECT a+1,1 FROM qn WHERE a<2) +SELECT * FROM qn; +ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field +# Same number of columns in anchor and recursive members +WITH RECURSIVE qn AS +( +SELECT 1 +UNION ALL +SELECT 3, 0 FROM qn +) +SELECT * FROM qn; +ERROR 21000: The used SELECT statements have a different number of columns +# Mismatch in column name and column count; problem specific of +# recursive CTE which creates tmp table earlier in preparation. +WITH RECURSIVE q (b) AS (SELECT 1, 1 UNION ALL SELECT 1, 1 FROM q) +SELECT b FROM q; +ERROR HY000: WITH column list and SELECT field list have different column counts +# Cannot have two recursive refs in FROM: +WITH RECURSIVE qn AS ( +SELECT 123 AS a UNION ALL +SELECT 1+qn.a FROM qn, qn AS qn1 WHERE qn1.a<130) +SELECT * FROM qn; +ERROR HY000: Restrictions imposed on recursive definitions are violated for table 'qn' +# Prove that a materialized QN is shared among all references: +flush STATUS; +WITH RECURSIVE qn AS ( +SELECT 123 AS a UNION ALL +SELECT 1+a FROM qn WHERE a<125) +SELECT * FROM qn; +a +123 +124 +125 +SHOW STATUS LIKE "handler_write"; +Variable_name Value +Handler_write 0 +flush STATUS; +WITH RECURSIVE qn AS ( +SELECT 123 AS a UNION ALL +SELECT 1+a FROM qn WHERE a<125) +SELECT * FROM qn, qn AS qn1; +a a +123 123 +124 123 +125 123 +123 124 +124 124 +125 124 +123 125 +124 125 +125 125 +SHOW STATUS LIKE "handler_write"; +Variable_name Value +Handler_write 0 +SHOW STATUS LIKE 'Created_tmp%table%'; +Variable_name Value +Created_tmp_disk_tables 5 +Created_tmp_tables 5 +# Also works if references are nested inside other query names: +flush STATUS; +WITH RECURSIVE inner_ AS ( +SELECT 123 AS a UNION ALL +SELECT 1+a FROM inner_ WHERE a<125), +outer_ AS (SELECT * FROM inner_ LIMIT 10) +SELECT * FROM outer_, outer_ AS outer1; +a a +123 123 +124 123 +125 123 +123 124 +124 124 +125 124 +123 125 +124 125 +125 125 +SHOW STATUS LIKE "handler_write"; +Variable_name Value +Handler_write 0 +flush STATUS; +WITH RECURSIVE inner_ AS ( +SELECT 123 AS a UNION ALL +SELECT 1+a FROM inner_ WHERE a<125), +outer_ AS +(SELECT inner_.a, inner1.a AS a1 +FROM inner_, inner_ AS inner1 LIMIT 10) +SELECT * FROM outer_; +a a1 +123 123 +124 123 +125 123 +123 124 +124 124 +125 124 +123 125 +124 125 +125 125 +SHOW STATUS LIKE "handler_write"; +Variable_name Value +Handler_write 0 +# Even if the two query names are recursive: +flush STATUS; +WITH RECURSIVE inner_ AS ( +SELECT 123 AS a UNION ALL +SELECT 1+a FROM inner_ WHERE a<125), +outer_ AS +(SELECT a FROM inner_ UNION ALL +SELECT a*2 FROM outer_ WHERE a<1000) +SELECT a FROM outer_; +a +123 +124 +125 +246 +248 +250 +492 +496 +500 +984 +992 +1000 +1968 +1984 +SHOW STATUS LIKE "handler_write"; +Variable_name Value +Handler_write 0 +# Optimizer must be allowed to put the recursive reference first +CREATE TABLE t1(a int); +INSERT INTO t1 VALUES(1),(2); +WITH RECURSIVE qn AS +( +SELECT 1 FROM t1 +UNION ALL +SELECT 1 FROM t1 LEFT JOIN qn ON 1 +) +SELECT * FROM qn; +ERROR HY000: Restrictions imposed on recursive definitions are violated for table 'qn' +# Empty anchor +WITH RECURSIVE qn AS +( +SELECT a FROM t1 WHERE 0 +UNION ALL +SELECT a+1 FROM qn +) +SELECT * FROM qn; +a +WITH RECURSIVE qn AS +( +SELECT a FROM t1 WHERE a>10 +UNION ALL +SELECT a+1 FROM qn +) +SELECT * FROM qn; +a +# UNION DISTINCT in anchor parts +INSERT INTO t1 VALUES(1),(2); +SET @c=0, @d=0; +WITH RECURSIVE qn AS +( +SELECT 1,0 AS col FROM t1 +UNION DISTINCT +SELECT 1,0 FROM t1 +UNION ALL +SELECT 3, 0*(@c:=@c+1) FROM qn WHERE @c<1 +UNION ALL +SELECT 3, 0*(@d:=@d+1) FROM qn WHERE @d<1 +) +SELECT * FROM qn; +ERROR 42000: This version of MariaDB doesn't yet support 'mix of ALL and DISTINCT UNION operations in recursive CTE spec' +# UNION DISTINCT affecting recursive member, followed by UNION ALL +INSERT INTO t1 VALUES(1),(2); +SET @c=0, @d=0; +WITH RECURSIVE qn AS +( +SELECT 1,0 AS col FROM t1 +UNION DISTINCT +SELECT 3, 0*(@c:=@c+1) FROM qn WHERE @c<1 +UNION ALL +SELECT 3, 0*(@d:=@d+1) FROM qn WHERE @d<1 +) +SELECT * FROM qn; +ERROR 42000: This version of MariaDB doesn't yet support 'mix of ALL and DISTINCT UNION operations in recursive CTE spec' +# create select +CREATE TABLE t2 +WITH RECURSIVE qn AS ( +SELECT 123 AS a UNION ALL SELECT 1+a FROM qn WHERE a<130) +SELECT * FROM qn; +SELECT * FROM t2; +a +123 +124 +125 +126 +127 +128 +129 +130 +DROP TABLE t2; +# insert select +DELETE FROM t1; +INSERT INTO t1 +WITH RECURSIVE qn AS ( +SELECT 123 AS a UNION ALL SELECT 1+a FROM qn WHERE a<130) +SELECT * FROM qn; +SELECT * FROM t1; +a +123 +124 +125 +126 +127 +128 +129 +130 +# Using insertion target inside recursive query +DELETE FROM t1; +INSERT INTO t1 VALUES(1),(2); +INSERT INTO t1 +WITH RECURSIVE qn AS ( +SELECT 123 AS a UNION ALL SELECT 1+qn.a FROM qn, t1 WHERE qn.a<125) +SELECT * FROM qn; +SELECT * FROM t1; +a +1 +2 +123 +124 +124 +125 +125 +125 +125 +DROP TABLE t1; +# insert into tmp table (a likely use case) +CREATE TEMPORARY TABLE t1(a int); +INSERT INTO t1 +WITH RECURSIVE qn AS ( +SELECT 123 AS a UNION ALL SELECT 1+a FROM qn WHERE a<130) +SELECT * FROM qn; +SELECT * FROM t1; +a +123 +124 +125 +126 +127 +128 +129 +130 +DROP TABLE t1; +# create view +CREATE VIEW v1 AS +WITH RECURSIVE qn AS ( +SELECT 123 AS a UNION ALL SELECT 1+a FROM qn WHERE a<130) +SELECT * FROM qn; +SELECT * FROM v1; +a +123 +124 +125 +126 +127 +128 +129 +130 +DROP VIEW v1; +# Recursive QN can be constant (0-row or 1-row) for the +# optimizer if its members have impossible conditions: +# Recursive refs should never use indexes to read: +# first, optimization of top query creates a key on q.b; +# then optimization of scalar subquery, when it optimizes the +# recursive member, must be prevented from re-using this key +# (it was a bug that it re-used it, as the index is covering +# and adjust_access_methods() has a heuristic which converts a +# table scan to index scan, so it wrongly used an index scan). +EXPLAIN WITH RECURSIVE q (b) AS +(SELECT 1 UNION ALL SELECT 1+b FROM q WHERE b<10) +SELECT (SELECT q1.b FROM q AS q2 WHERE q2.b=3) FROM q AS q1 WHERE q1.b=3; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using where +4 DEPENDENT SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 Using where +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +WITH RECURSIVE q (b) AS +(SELECT 1 UNION ALL SELECT 1+b FROM q WHERE b<10) +SELECT (SELECT q1.b FROM q AS q2 WHERE q2.b=3) FROM q AS q1 WHERE q1.b=3; +(SELECT q1.b FROM q AS q2 WHERE q2.b=3) +3 +# This is from my blog so I can use it here. +# Tests depth-first etc +CREATE TABLE employees ( +ID INT PRIMARY KEY, +NAME VARCHAR(100), +MANAGER_ID INT, +INDEX (MANAGER_ID), +FOREIGN KEY (MANAGER_ID) REFERENCES employees(ID) +); +INSERT INTO employees VALUES +(333, "Yasmina", NULL), +(198, "John", 333), +(692, "Tarek", 333), +(29, "Pedro", 198), +(4610, "Sarah", 29), +(72, "Pierre", 29), +(123, "Adil", 692); +ANALYZE TABLE employees; +Table Op Msg_type Msg_text +test.employees analyze status OK +# Depth-first. +# Also test column names, and their reference in the recursive member. +WITH RECURSIVE employees_extended(ID, NAME, PATH) +AS +( +SELECT ID, NAME, CAST(ID AS CHAR(200)) +FROM employees +WHERE MANAGER_ID IS NULL +UNION ALL +SELECT S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID) +FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID +) +SELECT * FROM employees_extended ORDER BY PATH; +ID NAME PATH +333 Yasmina 333 +198 John 333,198 +29 Pedro 333,198,29 +4610 Sarah 333,198,29,4610 +72 Pierre 333,198,29,72 +692 Tarek 333,692 +123 Adil 333,692,123 +# Breadth-first is likely what we get, if no ordering +WITH RECURSIVE employees_extended +AS +( +SELECT ID, NAME, CAST(ID AS CHAR(200)) AS PATH +FROM employees +WHERE MANAGER_ID IS NULL +UNION ALL +SELECT S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID) +FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID +) +SELECT * FROM employees_extended; +ID NAME PATH +333 Yasmina 333 +198 John 333,198 +692 Tarek 333,692 +29 Pedro 333,198,29 +123 Adil 333,692,123 +4610 Sarah 333,198,29,4610 +72 Pierre 333,198,29,72 +# But to be really sure we have breadth-first, we generate a +# numeric column SEQ. And sort by NAME, to have repeatable +# order of siblings (who have the same SEQ). +WITH RECURSIVE employees_extended +AS +( +SELECT 0 AS SEQ, ID, NAME, CAST(ID AS CHAR(200)) AS PATH +FROM employees +WHERE MANAGER_ID IS NULL +UNION ALL +SELECT M.SEQ+1, S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID) +FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID +) +SELECT * FROM employees_extended ORDER BY SEQ, NAME; +SEQ ID NAME PATH +0 333 Yasmina 333 +1 198 John 333,198 +1 692 Tarek 333,692 +2 123 Adil 333,692,123 +2 29 Pedro 333,198,29 +3 72 Pierre 333,198,29,72 +3 4610 Sarah 333,198,29,4610 +# Or, use a user variable, then all rows have different number: +WITH RECURSIVE employees_extended +AS +( +SELECT (@s:=0) AS SEQ, ID, NAME, CAST(ID AS CHAR(200)) AS PATH +FROM employees +WHERE MANAGER_ID IS NULL +UNION ALL +SELECT (@s:=@s+1), S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID) +FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID +) +SELECT * FROM employees_extended ORDER BY SEQ; +SEQ ID NAME PATH +0 333 Yasmina 333 +1 198 John 333,198 +2 692 Tarek 333,692 +3 29 Pedro 333,198,29 +4 123 Adil 333,692,123 +5 4610 Sarah 333,198,29,4610 +6 72 Pierre 333,198,29,72 +# Direct & indirect reports of John = having John in their PATH +WITH RECURSIVE employees_extended +AS +( +SELECT ID, NAME, CAST(ID AS CHAR(200)) AS PATH +FROM employees +WHERE MANAGER_ID IS NULL +UNION ALL +SELECT S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID) +FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID +) +SELECT * FROM employees_extended +WHERE FIND_IN_SET((SELECT ID FROM employees WHERE NAME='John'), +PATH); +ID NAME PATH +198 John 333,198 +29 Pedro 333,198,29 +4610 Sarah 333,198,29,4610 +72 Pierre 333,198,29,72 +# Exclude John, he's not a report of himself; +# bonus: use a QN to cache his ID. +WITH RECURSIVE employees_extended(ID, NAME, PATH) +AS +( +SELECT ID, NAME, CAST(ID AS CHAR(200)) +FROM employees +WHERE MANAGER_ID IS NULL +UNION ALL +SELECT S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID) +FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID +), +JOHN_ID AS (SELECT ID FROM employees WHERE NAME='John') +SELECT e.* FROM employees_extended e, JOHN_ID +WHERE FIND_IN_SET(JOHN_ID.ID, +PATH) +AND e.ID<>JOHN_ID.ID; +ID NAME PATH +29 Pedro 333,198,29 +4610 Sarah 333,198,29,4610 +72 Pierre 333,198,29,72 +# Similar, but faster: start dive at John (and include him again). +WITH RECURSIVE employees_extended +AS +( +SELECT ID, NAME, CAST(ID AS CHAR(200)) AS PATH +FROM employees +WHERE NAME='John' +UNION ALL +SELECT S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID) +FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID +) +SELECT * FROM employees_extended; +ID NAME PATH +198 John 198 +29 Pedro 198,29 +4610 Sarah 198,29,4610 +72 Pierre 198,29,72 +# Get the management chain above Pierre: +WITH RECURSIVE employees_extended +AS +( +SELECT ID, NAME, MANAGER_ID, CAST(ID AS CHAR(200)) AS PATH +FROM employees +WHERE NAME='Pierre' +UNION ALL +SELECT S.ID, S.NAME, S.MANAGER_ID, CONCAT(M.PATH, ",", S.ID) +FROM employees_extended M JOIN employees S ON M.MANAGER_ID=S.ID +) +SELECT * FROM employees_extended; +ID NAME MANAGER_ID PATH +72 Pierre 29 72 +29 Pedro 198 72,29 +198 John 333 72,29,198 +333 Yasmina NULL 72,29,198,333 +# Get the management chain above Pierre, without PATH +WITH RECURSIVE employees_extended +AS +( +SELECT ID, NAME, MANAGER_ID +FROM employees +WHERE NAME='Pierre' +UNION ALL +SELECT S.ID, S.NAME, S.MANAGER_ID +FROM employees_extended M JOIN employees S ON M.MANAGER_ID=S.ID +) +SELECT * FROM employees_extended; +ID NAME MANAGER_ID +72 Pierre 29 +29 Pedro 198 +198 John 333 +333 Yasmina NULL +# Get the management chain above Pierre and Sarah, without PATH +WITH RECURSIVE employees_extended +AS +( +SELECT ID, NAME, MANAGER_ID +FROM employees +WHERE NAME='Pierre' OR NAME='Sarah' +UNION ALL +SELECT S.ID, S.NAME, S.MANAGER_ID +FROM employees_extended M JOIN employees S ON M.MANAGER_ID=S.ID +) +SELECT * FROM employees_extended; +ID NAME MANAGER_ID +4610 Sarah 29 +72 Pierre 29 +29 Pedro 198 +29 Pedro 198 +198 John 333 +198 John 333 +333 Yasmina NULL +333 Yasmina NULL +# Do it without duplicates +WITH RECURSIVE employees_extended +AS +( +SELECT ID, NAME, MANAGER_ID +FROM employees +WHERE NAME='Pierre' OR NAME='Sarah' +UNION +SELECT S.ID, S.NAME, S.MANAGER_ID +FROM employees_extended M JOIN employees S ON M.MANAGER_ID=S.ID +) +SELECT * FROM employees_extended; +ID NAME MANAGER_ID +4610 Sarah 29 +72 Pierre 29 +29 Pedro 198 +198 John 333 +333 Yasmina NULL +# Cycles. Introduce an oddity: +# Sarah is indirect report of John and is his manager. +UPDATE employees SET MANAGER_ID=4610 WHERE NAME="John"; +# Add cycle detection: the row closing a cycle is marked with +# IS_CYCLE=1, which stops the iterations. The outer SELECT +# could then want to see only that row, or only previous ones. +WITH RECURSIVE employees_extended(ID, NAME, PATH, IS_CYCLE) +AS +( +SELECT ID, NAME, CAST(ID AS CHAR(200)), 0 +FROM employees +WHERE NAME='John' +UNION ALL +SELECT S.ID, S.NAME, CONCAT(M.PATH, ",", S.ID), FIND_IN_SET(S.ID, M.PATH) +FROM employees_extended M STRAIGHT_JOIN employees S ON M.ID=S.MANAGER_ID +WHERE M.IS_CYCLE=0 +) +SELECT * FROM employees_extended; +ID NAME PATH IS_CYCLE +198 John 198 0 +29 Pedro 198,29 0 +4610 Sarah 198,29,4610 0 +72 Pierre 198,29,72 0 +198 John 198,29,4610,198 1 +DROP TABLE employees; +# Two recursive members. +CREATE TABLE t1 (id int, name char(10), leftpar int, rightpar int); +INSERT INTO t1 VALUES +(1, "A", 2, 3), +(2, "LA", 4, 5), +(4, "LLA", 6, 7), +(6, "LLLA", NULL, NULL), +(7, "RLLA", NULL, NULL), +(5, "RLA", 8, 9), +(8, "LRLA", NULL, NULL), +(9, "RRLA", NULL, NULL), +(3, "RA", 10, 11), +(10, "LRA", 12, 13), +(11, "RRA", 14, 15), +(15, "RRRA", NULL, NULL), +(16, "B", 17, 18), +(17, "LB", NULL, NULL), +(18, "RB", NULL, NULL) +; +# Shuffle rows to make sure the algorithm works +# with any read order of rows above +CREATE TABLE t2 SELECT * FROM t1 ORDER BY rand(); +# Tree-walking query. We turn off the Query Cache: indeed +# sometimes pb2 enables Query Cache and as we run twice the +# same query the 2nd may not actually be executed so the value +# of Created_tmp_tables displayed at end becomes "one less"). +# Note that without ORDER BY, order of rows would be random as BNL +# implies that the randomized t2 is the driving table in the +# joining of rows. +EXPLAIN extended WITH RECURSIVE tree_of_a AS +( +SELECT *, cast(id AS char(200)) AS path FROM t2 WHERE name="A" +UNION ALL +SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON +t2.id=tree_of_a.leftpar +UNION ALL +SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON +t2.id=tree_of_a.rightpar +) +SELECT * FROM tree_of_a ORDER BY path; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 15 # Using filesort +2 DERIVED t2 ALL NULL NULL NULL NULL 15 # Using where +3 RECURSIVE UNION t2 ALL NULL NULL NULL NULL 15 # Using where +3 RECURSIVE UNION <derived2> ref key0 key0 5 test.t2.id 2 # +4 RECURSIVE UNION t2 ALL NULL NULL NULL NULL 15 # Using where +4 RECURSIVE UNION <derived2> ref key0 key0 5 test.t2.id 2 # +NULL UNION RESULT <union2,3,4> ALL NULL NULL NULL NULL NULL # +Warnings: +Note 1003 with recursive tree_of_a as (select `test`.`t2`.`id` AS `id`,`test`.`t2`.`name` AS `name`,`test`.`t2`.`leftpar` AS `leftpar`,`test`.`t2`.`rightpar` AS `rightpar`,cast(`test`.`t2`.`id` as char(200) charset latin1) AS `path` from `test`.`t2` where `test`.`t2`.`name` = 'A' union all select `test`.`t2`.`id` AS `id`,`test`.`t2`.`name` AS `name`,`test`.`t2`.`leftpar` AS `leftpar`,`test`.`t2`.`rightpar` AS `rightpar`,concat(`tree_of_a`.`path`,',',`test`.`t2`.`id`) AS `concat(tree_of_a.path,",",t2.id)` from `test`.`t2` join `tree_of_a` where `tree_of_a`.`leftpar` = `test`.`t2`.`id` union all select `test`.`t2`.`id` AS `id`,`test`.`t2`.`name` AS `name`,`test`.`t2`.`leftpar` AS `leftpar`,`test`.`t2`.`rightpar` AS `rightpar`,concat(`tree_of_a`.`path`,',',`test`.`t2`.`id`) AS `concat(tree_of_a.path,",",t2.id)` from `test`.`t2` join `tree_of_a` where `tree_of_a`.`rightpar` = `test`.`t2`.`id`)select `tree_of_a`.`id` AS `id`,`tree_of_a`.`name` AS `name`,`tree_of_a`.`leftpar` AS `leftpar`,`tree_of_a`.`rightpar` AS `rightpar`,`tree_of_a`.`path` AS `path` from `tree_of_a` order by `tree_of_a`.`path` +WITH RECURSIVE tree_of_a AS +( +SELECT *, cast(id AS char(200)) AS path FROM t2 WHERE name="A" +UNION ALL +SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON +t2.id=tree_of_a.leftpar +UNION ALL +SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON +t2.id=tree_of_a.rightpar +) +SELECT * FROM tree_of_a ORDER BY path; +id name leftpar rightpar path +1 A 2 3 1 +2 LA 4 5 1,2 +4 LLA 6 7 1,2,4 +6 LLLA NULL NULL 1,2,4,6 +7 RLLA NULL NULL 1,2,4,7 +5 RLA 8 9 1,2,5 +8 LRLA NULL NULL 1,2,5,8 +9 RRLA NULL NULL 1,2,5,9 +3 RA 10 11 1,3 +10 LRA 12 13 1,3,10 +11 RRA 14 15 1,3,11 +15 RRRA NULL NULL 1,3,11,15 +# Equivalent query with one single recursive query block: +WITH RECURSIVE tree_of_a AS +( +SELECT *, cast(id AS char(200)) AS path FROM t2 WHERE name="A" +UNION ALL +SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON +(t2.id=tree_of_a.leftpar OR t2.id=tree_of_a.rightpar) +) +SELECT * FROM tree_of_a +ORDER BY path; +id name leftpar rightpar path +1 A 2 3 1 +2 LA 4 5 1,2 +4 LLA 6 7 1,2,4 +6 LLLA NULL NULL 1,2,4,6 +7 RLLA NULL NULL 1,2,4,7 +5 RLA 8 9 1,2,5 +8 LRLA NULL NULL 1,2,5,8 +9 RRLA NULL NULL 1,2,5,9 +3 RA 10 11 1,3 +10 LRA 12 13 1,3,10 +11 RRA 14 15 1,3,11 +15 RRRA NULL NULL 1,3,11,15 +# Demonstrate a case where an index is automatically created on +# the derived table and used to read this table in the outer +# query (but correctly not used to read it in the recursive +# query). +EXPLAIN WITH RECURSIVE tree_of_a AS +( +SELECT *, cast(id AS char(200)) AS path FROM t2 WHERE name="A" +UNION ALL +SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON +t2.id=tree_of_a.leftpar +UNION ALL +SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON +t2.id=tree_of_a.rightpar +) +SELECT * FROM tree_of_a WHERE id=2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 15 # +2 DERIVED t2 ALL NULL NULL NULL NULL 15 # +3 RECURSIVE UNION t2 ALL NULL NULL NULL NULL 15 # +3 RECURSIVE UNION <derived2> ref key0 key0 5 test.t2.id 2 # +4 RECURSIVE UNION t2 ALL NULL NULL NULL NULL 15 # +4 RECURSIVE UNION <derived2> ref key0 key0 5 test.t2.id 2 # +NULL UNION RESULT <union2,3,4> ALL NULL NULL NULL NULL NULL # +WITH RECURSIVE tree_of_a AS +( +SELECT *, cast(id AS char(200)) AS path FROM t2 WHERE name="A" +UNION ALL +SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON +t2.id=tree_of_a.leftpar +UNION ALL +SELECT t2.*, concat(tree_of_a.path,",",t2.id) FROM t2 JOIN tree_of_a ON +t2.id=tree_of_a.rightpar +) +SELECT * FROM tree_of_a WHERE id=2; +id name leftpar rightpar path +2 LA 4 5 1,2 +DROP TABLE t1,t2; +EXPLAIN WITH RECURSIVE cte AS +( +SELECT 1 AS n UNION ALL +SELECT n+1 FROM cte WHERE n<10000 +) +SELECT sum(cte1.n*cte2.n*cte3.n)=2490508525950000 +FROM cte cte1, cte cte2, cte cte3 +WHERE cte1.n=cte2.n+10 AND cte2.n+20=cte3.n; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 # +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 # +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 # +2 DERIVED NULL NULL NULL NULL NULL NULL NULL # +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 # +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL # +WITH RECURSIVE cte AS +( +SELECT 1 AS n UNION ALL +SELECT n+1 FROM cte WHERE n<10000 +) +SELECT sum(cte1.n*cte2.n*cte3.n)=2490508525950000 +FROM cte cte1, cte cte2, cte cte3 +WHERE cte1.n=cte2.n+10 AND cte2.n+20=cte3.n; +sum(cte1.n*cte2.n*cte3.n)=2490508525950000 +1 +# +# Transitive closure +# +CREATE TABLE nodes(id int); +CREATE TABLE arcs(from_id int, to_id int); +INSERT INTO nodes VALUES(1),(2),(3),(4),(5),(6),(7),(8); +INSERT INTO arcs VALUES(1,3), (3,6), (1,4), (4,6), (6,2), (2,1); +# UNION ALL leads to infinite loop as 1 is reachable from 1; +# so we stop it with a maximum depth 8 (8 nodes in graph) +WITH RECURSIVE cte AS +( +SELECT id, 0 AS depth FROM nodes WHERE id=1 +UNION ALL +SELECT to_id, depth+1 FROM arcs, cte +WHERE from_id=cte.id AND depth<8 +) +SELECT count(*), max(depth) FROM cte; +count(*) max(depth) +25 8 +# Can use cycle detection: +WITH RECURSIVE cte AS +( +SELECT id, cast(id AS char(200)) AS path, 0 AS is_cycle +FROM nodes WHERE id=1 +UNION ALL +SELECT to_id, concat(cte.path, ",", to_id), find_in_set(to_id, path) +FROM arcs, cte +WHERE from_id=cte.id AND is_cycle=0 +) +SELECT * FROM cte; +id path is_cycle +1 1 0 +3 1,3 0 +4 1,4 0 +6 1,3,6 0 +6 1,4,6 0 +2 1,3,6,2 0 +2 1,4,6,2 0 +1 1,3,6,2,1 1 +1 1,4,6,2,1 1 +# It is simpler with DISTINCT: +WITH RECURSIVE cte AS +( +SELECT id FROM nodes WHERE id=1 +UNION +SELECT to_id FROM arcs, cte WHERE from_id=cte.id +) +SELECT * FROM cte; +id +1 +3 +4 +6 +2 +DROP TABLE nodes, arcs; +# Hash field and MEMORY don't work together. Make long distinct +# key to force hash field, to see if it switches to InnoDB. +# Not too long key (500 bytes in latin1) +WITH RECURSIVE cte AS +( +SELECT 1 AS n, +repeat('a',500) AS f, '' AS g, +'' AS h, '' AS i +UNION +SELECT n+1, +'','','','' + FROM cte WHERE n<100) +SELECT sum(n) FROM cte; +sum(n) +5050 +SHOW STATUS LIKE 'Created_tmp_disk_tables'; +Variable_name Value +Created_tmp_disk_tables 132 +# Too long key (>3000 bytes in latin1) +WITH RECURSIVE cte AS +( +SELECT 1 AS n, +repeat('a',500) AS f, repeat('a',500) AS g, +repeat('a',500) AS h, repeat('a',500) AS i, +repeat('a',500) AS j, repeat('a',500) AS k, +repeat('a',500) AS l, repeat('a',500) AS m +UNION +SELECT n+1, +'','','','','','','','' + FROM cte WHERE n<100) +SELECT sum(n) FROM cte; +sum(n) +5050 +# +# In query planning, the recursive reference's row count is +# said to be the estimated row count of all non-recursive query +# blocks +CREATE TABLE t1(a int); +# 15 rows: +INSERT INTO t1 VALUES (), (), (), (), (), (), (), (), (), (), (), (), +(), (), (); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +# EXPLAIN says: in non-recursive QB we'll read 15 rows of t1, +# in recursive QB we'll read 15 rows of qn, keep only 0.33 +# due to WHERE, that makes 4 (due to rounding), and in the +# derived table we'll thus have 15+4=19. That ignores +# next repetitions of the recursive QB which are unpredictable. +EXPLAIN WITH RECURSIVE qn AS +(SELECT 1 AS a FROM t1 UNION ALL SELECT a+1 FROM qn WHERE qn.a<100) +SELECT * FROM qn; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 15 +2 DERIVED t1 ALL NULL NULL NULL NULL 15 +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 15 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +EXPLAIN WITH RECURSIVE qn AS +(SELECT 1 AS a FROM t1 UNION DISTINCT SELECT a+1 FROM qn WHERE qn.a<100) +SELECT * FROM qn; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 15 +2 DERIVED t1 ALL NULL NULL NULL NULL 15 +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 15 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +DROP TABLE t1; +SHOW STATUS LIKE 'Created_tmp_disk_tables'; +Variable_name Value +Created_tmp_disk_tables 138 +flush STATUS; +WITH RECURSIVE q (b) AS +(SELECT 1 UNION ALL SELECT 1+b FROM q WHERE b<2000) +SELECT min(b),max(b),avg(b) FROM q; +min(b) max(b) avg(b) +1 2000 1000.5000 +SHOW STATUS LIKE 'Created_tmp_disk_tables'; +Variable_name Value +Created_tmp_disk_tables 4 +# Test when conversion to InnoDB affects recursive references which +# are not open yet (those of q1): +flush STATUS; +WITH RECURSIVE q (b) AS +(SELECT 1 UNION ALL SELECT 1+b FROM q WHERE b<2000) +SELECT min(q.b),max(q.b),avg(q.b) FROM q, q AS q1; +min(q.b) max(q.b) avg(q.b) +1 2000 1000.5000 +SHOW STATUS LIKE 'Created_tmp_disk_tables'; +Variable_name Value +Created_tmp_disk_tables 5 +# Same, but make q1 the writer; this is to test overflow when +# the writer isn't first in the 'tmp_tables' list +flush STATUS; +WITH RECURSIVE q (b) AS +(SELECT 1 UNION ALL SELECT 1+b FROM q WHERE b<2000) +SELECT min(q.b),max(q.b),avg(q.b) FROM q RIGHT JOIN q AS q1 ON 1; +min(q.b) max(q.b) avg(q.b) +1 2000 1000.5000 +SHOW STATUS LIKE 'Created_tmp_disk_tables'; +Variable_name Value +Created_tmp_disk_tables 5 +# Test when outer query reads CTE with an index. +# Overflow doesn't happen at same row as queries above, as this +# table has an index which makes it grow faster. +EXPLAIN WITH RECURSIVE q (b) AS +(SELECT 1 UNION ALL SELECT 1+b FROM q WHERE b<2000) +SELECT min(b),max(b),avg(b) FROM q WHERE b=300; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using where +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 Using where +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +SHOW STATUS LIKE 'Created_tmp_disk_tables'; +Variable_name Value +Created_tmp_disk_tables 6 +WITH RECURSIVE q (b) AS +(SELECT 1 UNION ALL SELECT 1+b FROM q WHERE b<2000) +SELECT min(b),max(b),avg(b) FROM q WHERE b=300; +min(b) max(b) avg(b) +300 300 300.0000 +SHOW STATUS LIKE 'Created_tmp_disk_tables'; +Variable_name Value +Created_tmp_disk_tables 10 +# Verify that rows come out in insertion order. +# If they didn't, the sequences of @c and of 'b' +# would not be identical and the sum wouldn't be +# 1^2 + ... + 2000^2 = n(n+1)(2n+1)/6 = 2668667000 +SET @c:=1; +flush STATUS; +WITH RECURSIVE q (b, c) AS +(SELECT 1, 1 UNION ALL SELECT (1+b), (@c:=(@c+1)) FROM q WHERE b<2000) +SELECT sum(b*c) FROM q; +sum(b*c) +2668667000 +SHOW STATUS LIKE 'Created_tmp_disk_tables'; +Variable_name Value +Created_tmp_disk_tables 4 +# +# Bug#23495283 WL3634:ASSERTION `0' FAILED IN FIELD* ITEM::TMP_TABLE_FIELD_FROM_FIELD_TYPE +# +CREATE TABLE t1(c1 DATETIME, c2 INT, KEY(c1)); +WITH RECURSIVE cte AS ( SELECT a.c1 AS field1, 0 AS cycle FROM (t1 AS a) +UNION ALL SELECT b.c2 FROM cte AS a JOIN t1 AS b) SELECT * FROM cte; +ERROR 21000: The used SELECT statements have a different number of columns +DROP TABLE t1; +# +# Bug#23645090 WL3634: INVALID WRITE AND READ VALGRIND ERRORS +# +CREATE TABLE A ( +col_date date DEFAULT NULL, +col_datetime_key datetime DEFAULT NULL, +col_time_key time DEFAULT NULL, +col_varchar_key varchar(1) DEFAULT NULL, +col_int_key int(11) DEFAULT NULL, +col_blob_key blob, +col_varchar varchar(1) DEFAULT NULL, +col_date_key date DEFAULT NULL, +col_time time DEFAULT NULL, +col_blob blob, +pk int(11) NOT NULL AUTO_INCREMENT, +col_int int(11) DEFAULT NULL, +col_datetime datetime DEFAULT NULL, +PRIMARY KEY (pk), +KEY col_datetime_key (col_datetime_key), +KEY col_time_key (col_time_key), +KEY col_varchar_key (col_varchar_key), +KEY col_int_key (col_int_key), +KEY col_blob_key (col_blob_key(255)), +KEY col_date_key (col_date_key) +) DEFAULT CHARSET=latin1; +CREATE TABLE AA ( +col_varchar varchar(1) DEFAULT NULL, +col_date date DEFAULT NULL, +col_varchar_key varchar(1) DEFAULT NULL, +col_date_key date DEFAULT NULL, +col_datetime_key datetime DEFAULT NULL, +col_time_key time DEFAULT NULL, +pk int(11) NOT NULL AUTO_INCREMENT, +col_time time DEFAULT NULL, +col_int_key int(11) DEFAULT NULL, +col_datetime datetime DEFAULT NULL, +col_int int(11) DEFAULT NULL, +col_blob blob, +col_blob_key blob, +PRIMARY KEY (pk), +KEY col_varchar_key (col_varchar_key), +KEY col_date_key (col_date_key), +KEY col_datetime_key (col_datetime_key), +KEY col_time_key (col_time_key), +KEY col_int_key (col_int_key), +KEY col_blob_key (col_blob_key(255)) +) DEFAULT CHARSET=latin1; +CREATE TABLE BB ( +col_date date DEFAULT NULL, +col_blob_key blob, +col_time time DEFAULT NULL, +col_varchar_key varchar(1) DEFAULT NULL, +col_varchar varchar(1) DEFAULT NULL, +col_blob blob, +pk int(11) NOT NULL AUTO_INCREMENT, +col_int_key int(11) DEFAULT NULL, +col_datetime datetime DEFAULT NULL, +col_time_key time DEFAULT NULL, +col_datetime_key datetime DEFAULT NULL, +col_date_key date DEFAULT NULL, +col_int int(11) DEFAULT NULL, +PRIMARY KEY (pk), +KEY col_blob_key (col_blob_key(255)), +KEY col_varchar_key (col_varchar_key), +KEY col_int_key (col_int_key), +KEY col_time_key (col_time_key), +KEY col_datetime_key (col_datetime_key), +KEY col_date_key (col_date_key) +) AUTO_INCREMENT=11 DEFAULT CHARSET=latin1; +CREATE TABLE D ( +col_varchar_key varchar(1) DEFAULT NULL, +col_datetime datetime DEFAULT NULL, +col_date_key date DEFAULT NULL, +col_int int(11) DEFAULT NULL, +col_time time DEFAULT NULL, +col_blob blob, +col_int_key int(11) DEFAULT NULL, +col_blob_key blob, +col_varchar varchar(1) DEFAULT NULL, +col_datetime_key datetime DEFAULT NULL, +col_date date DEFAULT NULL, +col_time_key time DEFAULT NULL, +pk int(11) NOT NULL AUTO_INCREMENT, +PRIMARY KEY (pk), +KEY col_varchar_key (col_varchar_key), +KEY col_date_key (col_date_key), +KEY col_int_key (col_int_key), +KEY col_blob_key (col_blob_key(255)), +KEY col_datetime_key (col_datetime_key), +KEY col_time_key (col_time_key) +) DEFAULT CHARSET=latin1; +WITH RECURSIVE cte AS ( +SELECT alias1 . `col_blob_key` AS field1, 0 AS cycle +FROM ( BB AS alias1 , ( D AS alias2 , AA AS alias3 ) ) +WHERE ( +alias1 . pk = 225 +OR ( alias1 . col_int_key = 69 AND alias1 . col_blob_key = 'p' ) +) +UNION ALL +SELECT t1.pk, t2.cycle +FROM cte AS t2 JOIN A AS t1 +WHERE t2.field1 = t1.`col_int_key` +AND t2.cycle =1 ) SELECT * FROM cte; +field1 cycle +DROP TABLE IF EXISTS A, AA, BB, D; +# +# Bug#24962600 WL3634: SIG 11 IN HEAP_RRND AT STORAGE/HEAP/HP_RRND.C +# +create table t1(a int); +with recursive cte as (select * from t1 union select * from cte) +select * from cte; +a +insert into t1 values(1),(2); +with recursive cte as (select * from t1 where 0 union select * from cte) +select * from cte; +a +with recursive cte as (select * from t1 where a>3 union select * from cte) +select * from cte; +a +drop table t1; +# +# Bug#26501463 WL10792: ASSERTION `!TABLE->HAS_NULL_ROW()' FAILED +# +CREATE TABLE D (col_int INT); +CREATE TABLE C ( +col_int2 INT, +pk INT NOT NULL, +col_int INT, +PRIMARY KEY (pk) +); +INSERT INTO C VALUES +(7,1,3),(7,2,3),(5,3,4),(1,4,6),(5,5,2), +(5,6,9),(4,7,9),(7,8,3),(3,9,0),(5,10,3); +CREATE TABLE BB ( +pk INT NOT NULL, +col_int INT, +PRIMARY KEY (pk) +); +INSERT INTO BB VALUES (1,0),(2,6),(3,2),(4,5),(5,0); +WITH RECURSIVE cte AS ( +SELECT alias2 . col_int2 AS field1 FROM +D AS alias1 RIGHT JOIN +( ( C AS alias2 LEFT JOIN BB AS alias3 +ON (( alias3 . pk = alias2 . col_int ) AND ( alias3 . pk = alias2 . pk ) ) ) ) +ON (alias3 . col_int <> alias2 . col_int2 ) +HAVING field1 <= 0 +UNION +SELECT cte.field1 FROM cte +) +SELECT * FROM cte; +field1 +DROP TABLE BB,C,D; +# +# Bug#26556025 ASSERTION `!SELECT_LEX->IS_RECURSIVE() || !TMP_TABLES' FAILED. +# +SET SQL_BUFFER_RESULT = 1; +WITH RECURSIVE cte AS +(SELECT 1 AS n UNION SELECT n+1 FROM cte WHERE n<3) +SELECT * FROM cte; +n +1 +2 +3 +WITH RECURSIVE cte AS +(SELECT 1 AS n UNION ALL SELECT n+1 FROM cte WHERE n<3) +SELECT * FROM cte; +n +1 +2 +3 +SET SQL_BUFFER_RESULT = DEFAULT; |