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
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
|
#
# Tests for window functions over big datasets.
# "Big" here is "big enough so that filesort result doesn't fit in a
# memory buffer".
#
#
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1(a int);
insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C;
create table t10 (a int, b int, c int);
insert into t10
select
A.a + 1000*B.a,
A.a + 1000*B.a,
A.a + 1000*B.a
from t1 A, t0 B
order by A.a+1000*B.a;
--echo #################################################################
--echo ## Try a basic example
flush status;
create table t21 as
select
sum(b) over (order by a rows between 2 preceding and 2 following) as SUM_B
from
t10;
select variable_name,
case when variable_value > 0 then 'WITH PASSES' else 'NO PASSES' end as result
from information_schema.session_status
where variable_name like 'Sort_merge_passes';
set sort_buffer_size=1024;
flush status;
create table t22 as
select
sum(b) over (order by a rows between 2 preceding and 2 following) as SUM_B
from
t10;
select variable_name,
case when variable_value > 0 then 'WITH PASSES' else 'NO PASSES' end as result
from information_schema.session_status
where variable_name like 'Sort_merge_passes';
let $diff_tables= t21, t22;
source include/diff_tables.inc;
drop table t21, t22;
--echo #################################################################
--echo # Try many cursors
set sort_buffer_size=default;
flush status;
create table t21 as
select
sum(b) over (order by a rows between 2 preceding and 2 following) as SUM_B1,
sum(b) over (order by a rows between 5 preceding and 5 following) as SUM_B2,
sum(b) over (order by a rows between 20 preceding and 20 following) as SUM_B3
from
t10;
select variable_name,
case when variable_value > 0 then 'WITH PASSES' else 'NO PASSES' end as result
from information_schema.session_status
where variable_name like 'Sort_merge_passes';
set sort_buffer_size=1024;
flush status;
create table t22 as
select
sum(b) over (order by a rows between 2 preceding and 2 following) as SUM_B1,
sum(b) over (order by a rows between 5 preceding and 5 following) as SUM_B2,
sum(b) over (order by a rows between 20 preceding and 20 following) as SUM_B3
from
t10;
select variable_name,
case when variable_value > 0 then 'WITH PASSES' else 'NO PASSES' end as result
from information_schema.session_status
where variable_name like 'Sort_merge_passes';
let $diff_tables= t21, t22;
source include/diff_tables.inc;
drop table t21, t22;
--echo #################################################################
--echo # Try having cursors pointing at different IO_CACHE pages
--echo # in the IO_CACHE
set sort_buffer_size=default;
flush status;
create table t21 as
select
a,
sum(b) over (order by a range between 5000 preceding and 5000 following) as SUM_B1
from
t10;
select variable_name,
case when variable_value > 0 then 'WITH PASSES' else 'NO PASSES' end as result
from information_schema.session_status
where variable_name like 'Sort_merge_passes';
set sort_buffer_size=1024;
flush status;
create table t22 as
select
a,
sum(b) over (order by a range between 5000 preceding and 5000 following) as SUM_B1
from
t10;
select variable_name,
case when variable_value > 0 then 'WITH PASSES' else 'NO PASSES' end as result
from information_schema.session_status
where variable_name like 'Sort_merge_passes';
let $diff_tables= t21, t22;
source include/diff_tables.inc;
drop table t21, t22;
--echo #################################################################
drop table t10;
drop table t0,t1;
|
