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
71
72
73
74
|
--source include/have_64bit.inc
--source include/no_valgrind_without_big.inc
set session internal_tmp_mem_storage_engine='memory';
--echo # This specifically tests WITH RECURSIVE queries which have
--echo # required changes in InnoDB (done as WL#9248).
create table t1(a int);
insert into t1 values(1),(2),(3);
--echo # Force InnoDB tmp table, not MEMORY
set big_tables=1;
--echo # Problem required this:
set optimizer_switch='block_nested_loop=off';
--echo # with one reader, there was no problem:
with qn as (select * from t1 limit 3) select * from qn;
--echo # The problems were in the following scenarios.
--echo # First all writes are done then two readers read
--sorted_result
with qn as (select * from t1 limit 3) select * from qn, qn qn1;
--echo # Second, with one reader and one writer (reader is the query block
--echo # after UNION ALL, writer is the derived materialization code)
with recursive qn as (
select 0 as n
union all
select n+1 from qn where n<10)
select * from qn;
--echo # With two readers (the two recursive query blocks after UNION ALL)
--echo # and one writer (the derived materialization code).
with recursive qn as (
select 0 as n
union all
select 2*n+2 from qn where n<50
union all
select 2*n+1 from qn where n<50
)
select count(n),max(n) from qn;
set optimizer_switch=default;
--echo # Test overflow from MEMORY engine to INNODB:
set big_tables=1;
--echo # Set baseline, disk-based results:
--source include/with_recursive_wl9248.inc
set big_tables=0;
--echo # Set different limits, to have different overflow scenarios;
--echo # we have overflow at record #122, #1481, #937 depending on
--echo # limits and queries.
set @@tmp_table_size=1024,@@max_heap_table_size=16384;
--source include/with_recursive_wl9248.inc
set @@tmp_table_size=30000,@@max_heap_table_size=30000;
--source include/with_recursive_wl9248.inc
set @@tmp_table_size=60000,@@max_heap_table_size=60000;
--source include/with_recursive_wl9248.inc
--echo # cleanup
drop table t1;
set session internal_tmp_mem_storage_engine=default;
|
