1#
2# Tests for window functions over big datasets.
3#  "Big" here is "big enough so that filesort result doesn't fit in a
4#   memory  buffer".
5#
6#
7
8create table t0 (a int);
9insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
10
11create table t1(a int);
12insert into t1 select A.a + B.a* 10 + C.a * 100 from t0 A, t0 B, t0 C;
13
14create table t10 (a int, b int, c int);
15insert into t10
16select
17  A.a + 1000*B.a,
18  A.a + 1000*B.a,
19  A.a + 1000*B.a
20from t1 A, t0 B
21order by A.a+1000*B.a;
22
23--echo #################################################################
24--echo ## Try a basic example
25flush status;
26create table t21 as
27select
28  sum(b) over (order by a rows between 2 preceding and 2 following) as SUM_B
29from
30  t10;
31select variable_name,
32       case when variable_value > 0 then 'WITH PASSES' else 'NO PASSES' end
33from information_schema.session_status
34where variable_name like 'Sort_merge_passes';
35
36set sort_buffer_size=1024;
37flush status;
38create table t22 as
39select
40  sum(b) over (order by a rows between 2 preceding and 2 following) as SUM_B
41from
42  t10;
43select variable_name,
44       case when variable_value > 0 then 'WITH PASSES' else 'NO PASSES' end
45from information_schema.session_status
46where variable_name like 'Sort_merge_passes';
47
48let $diff_tables= t21, t22;
49source include/diff_tables.inc;
50drop table t21, t22;
51
52--echo #################################################################
53--echo # Try many cursors
54set sort_buffer_size=default;
55flush status;
56create table t21 as
57select
58  sum(b) over (order by a rows between 2 preceding and 2 following) as SUM_B1,
59  sum(b) over (order by a rows between 5 preceding and 5 following) as SUM_B2,
60  sum(b) over (order by a rows between 20 preceding and 20 following) as SUM_B3
61from
62  t10;
63select variable_name,
64       case when variable_value > 0 then 'WITH PASSES' else 'NO PASSES' end
65from information_schema.session_status
66where variable_name like 'Sort_merge_passes';
67
68set sort_buffer_size=1024;
69flush status;
70create table t22 as
71select
72  sum(b) over (order by a rows between 2 preceding and 2 following) as SUM_B1,
73  sum(b) over (order by a rows between 5 preceding and 5 following) as SUM_B2,
74  sum(b) over (order by a rows between 20 preceding and 20 following) as SUM_B3
75from
76  t10;
77select variable_name,
78       case when variable_value > 0 then 'WITH PASSES' else 'NO PASSES' end
79from information_schema.session_status
80where variable_name like 'Sort_merge_passes';
81
82let $diff_tables= t21, t22;
83source include/diff_tables.inc;
84drop table t21, t22;
85
86--echo #################################################################
87--echo # Try having cursors pointing at different IO_CACHE pages
88--echo # in the IO_CACHE
89set sort_buffer_size=default;
90flush status;
91create table t21 as
92select
93  a,
94  sum(b) over (order by a range between 5000 preceding and 5000 following) as SUM_B1
95from
96  t10;
97
98select variable_name,
99       case when variable_value > 0 then 'WITH PASSES' else 'NO PASSES' end
100from information_schema.session_status
101where variable_name like 'Sort_merge_passes';
102
103set sort_buffer_size=1024;
104flush status;
105create table t22 as
106select
107  a,
108  sum(b) over (order by a range between 5000 preceding and 5000 following) as SUM_B1
109from
110  t10;
111select variable_name,
112       case when variable_value > 0 then 'WITH PASSES' else 'NO PASSES' end
113from information_schema.session_status
114where variable_name like 'Sort_merge_passes';
115
116let $diff_tables= t21, t22;
117source include/diff_tables.inc;
118drop table t21, t22;
119--echo #################################################################
120
121drop table t10;
122drop table t0,t1;
123
124