1set cte_max_recursion_depth=5000;
2
3flush status;
4with recursive q (b) as
5(select 1 union all select 1+b from q where b<2000)
6select min(b),max(b),avg(b) from q;
7show status like 'Created_tmp_disk_tables';
8
9--echo # Test when conversion to InnoDB affects recursive references which
10--echo # are not open yet (those of q1):
11flush status;
12with recursive q (b) as
13(select 1 union all select 1+b from q where b<2000)
14select min(q.b),max(q.b),avg(q.b) from q, q as q1;
15show status like 'Created_tmp_disk_tables';
16
17--echo # Same, but make q1 the writer; this is to test overflow when
18--echo # the writer isn't first in the 'tmp_tables' list
19
20flush status;
21with recursive q (b) as
22(select 1 union all select 1+b from q where b<2000)
23select min(q.b),max(q.b),avg(q.b) from q right join q as q1 on 1;
24show status like 'Created_tmp_disk_tables';
25
26--echo # Test when outer query reads CTE with an index.
27--echo # Overflow doesn't happen at same row as queries above, as this
28--echo # table has an index which makes it grow faster.
29
30let $query=
31with recursive q (b) as
32(select 1 union all select 1+b from q where b<2000)
33select min(b),max(b),avg(b) from q where b=300;
34
35eval explain $query;
36show status like 'Created_tmp_disk_tables';
37eval $query;
38show status like 'Created_tmp_disk_tables';
39
40--echo # Verify that rows come out in insertion order.
41--echo # If they didn't, the sequences of @c and of 'b'
42--echo # would not be identical and the sum wouldn't be
43--echo # 1^2 + ... + 2000^2 = n(n+1)(2n+1)/6 = 2668667000
44
45set @c:=1;
46flush status;
47with recursive q (b, c) as
48(select 1, 1 union all select (1+b), (@c:=(@c+1)) from q where b<2000)
49select sum(b*c) from q;
50show status like 'Created_tmp_disk_tables';
51