1 files changed, 64 insertions, 0 deletions
diff --git a/mysql-test/t/with_recursive_closure.test b/mysql-test/t/with_recursive_closure.test
new file mode 100644
index 00000000000..f3cf4b39306
--- /dev/null
+++ b/mysql-test/t/with_recursive_closure.test
@@ -0,0 +1,64 @@
+--source include/big_test.inc
+# Takes too long IN Valgrind, FOR pushbuild2:
+--source include/not_valgrind.inc
+
+SET @@max_recursive_iterations = 1000000;
+
+# This builds a graph OF randomly connected nodes (random number
+# generator IS USING a seed FOR repeatability). THEN it computes the
+# transitive closure OF a node. The RESULT has been validated against
+# another DBMS.
+
+SET @node_count=100000;
+SET @edge_count=floor(@node_count*2.4);
+
+CREATE TABLE edges(s int, e int)
+WITH RECURSIVE tmp(s,e,d) AS
+(
+SELECT 1, 2, 1
+UNION ALL
+SELECT floor(1+rand(3565659)*@node_count),
+       floor(1+rand(2344291)*@node_count),
+       d+1
+FROM tmp
+WHERE d<@edge_count
+)
+SELECT s,e FROM tmp;
+
+CREATE INDEX a ON edges(s);
+CREATE INDEX b ON edges(e);
+
+flush status;
+
+SET @start_node=60308;
+SELECT * FROM edges WHERE s=@start_node ORDER BY e;
+
+# uni-directional edges.
+# The sums ARE used AS digests OF the thousand-ROWS RESULT.
+
+WITH RECURSIVE closure AS
+(SELECT @start_node AS n UNION SELECT e FROM edges, closure WHERE s=closure.n)
+SELECT count(*),sum(n),sum(floor(n/20)*(n%20)) FROM closure;
+
+# bi-directional edges
+
+WITH RECURSIVE closure AS (SELECT @start_node AS n UNION SELECT CASE WHEN s=closure.n THEN e ELSE s END FROM edges, closure WHERE s=closure.n OR e=closure.n) SELECT count(*),sum(n),sum(floor(n/20)*(n%20)) FROM closure;
+
+# equivalent query WITH two RECURSIVE members
+
+WITH RECURSIVE closure AS (SELECT @start_node AS n UNION SELECT e FROM edges, closure WHERE s=closure.n UNION SELECT s FROM edges, closure WHERE e=closure.n) SELECT count(*),sum(n),sum(floor(n/20)*(n%20)) FROM closure;
+
+SHOW status LIKE 'Created_tmp_disk_tables';
+
+# uni-directional edges, again, just TO test overflow-TO-disk: we
+# START WITH a low LIMIT ON the MEMORY TABLE.
+
+SET @@tmp_table_size=1024,@@max_heap_table_size=16384;
+SET big_tables=0;
+WITH RECURSIVE closure AS
+(SELECT @start_node AS n UNION SELECT e FROM edges, closure WHERE s=closure.n)
+SELECT count(*),sum(n),sum(floor(n/20)*(n%20)) FROM closure;
+
+SHOW status LIKE 'Created_tmp_disk_tables';
+
+DROP TABLE edges;