1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
|
--source include/big_test.inc
# Takes too long in Valgrind, for pushbuild2:
--source include/not_valgrind.inc
set cte_max_recursion_depth = 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 idx1 on edges (s);
create index idx2 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
# Skip in hypergraph mode since it chooses a very inefficient query plan,
# due to not being able to push the condition into an index yet.
--skip_if_hypergraph
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;
--skip_if_hypergraph
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 session internal_tmp_mem_storage_engine='memory';
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;
--skip_if_hypergraph
show status like 'Created_tmp_disk_tables';
set session internal_tmp_mem_storage_engine=default;
drop table edges;
|
